I was actually exploring inotify-tools for something else, but they can also be handy for seeing what goes on below a mysqld process. inotify hooks into the filesystem handlers, and sees which files are accessed. You can then set triggers, or just display a tally over a certain period.

It has been a standard Linux kernel module since 2.6.13 (2005, wow that’s a long time ago already) and can be used through calls or the inotify-tools (commandline). So with the instrumentation already in the kernel, apt-get install inotify-tools is all you need to get started.

# inotifywatch -v -t 20 -r /var/lib/mysql/* /var/lib/mysql/zabbix/*
Establishing watches...
Setting up watch(es) on /var/lib/mysql/mysql/user.frm
OK, /var/lib/mysql/mysql/user.frm is now being watched.
[...]
Total of 212 watches.
Finished establishing watches, now collecting …

Intuit launches open source project. SFLC on Microsoft GPL violation accusations.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

# Intuit launched open source projects and community to develop apps based on its Intuit Partner Platform, while Savio Rodrigues declared Intuit’s open source play is all business.

# SFLC’s Bradley Kuhn told SDTimes Microsoft was in violation of the GPL.

# MySQL and Memcached-based appliance vendor Schooner Info Tech has raised $20m in Series B funding.

# …

iostat -x is very useful to check disk i/o activities. Sometimes it is said that "check %util is less than 100%" or "check svctm is less than 50ms", but please do not fully trust these numbers. For example, the following two cases (DBT-2 load on MySQL) used same disks (two HDD disks, RAID1) and reached almost 100% util, but performance numbers were very different (no.2 was about twice as fast as no.1).

# iostat -xm 10
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
       21.16    0.00    6.14   29.77    0.00   42.93

Device: rqm/s   wrqm/s   r/s   w/s    rMB/s    wMB/s
sdb    2.60 389.01  283.12 47.35     4.86     2.19
avgrq-sz avgqu-sz   await  svctm  %util
43.67     4.89   14.76   3.02  99.83

# iostat -xm 10
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
       40.03    0.00   16.51   16.52    0.00   26.94

Device:         rrqm/s   wrqm/s   r/s …

Gearman is an open source generic framework for distributed processing. At OSCON 2009 I attended the Gearman: Build Your Own Distributed Platform in 3 Hours tutorial.

While it’s very easy to install Gearman, and follow the first example, if you missed the all important additional PHP steps listed on just one slide you may be left with the “‘Class ‘GearmanClient’ not found” error.

The following are detailed instructions for the installation and configuration of Gearman and PHP on Ubuntu 9.04 Jaunty.

Add the Drizzle PPA to get pre-packaged versions of Gearman.

cp /etc/apt/sources.list /etc/apt/sources.list.orig
echo "deb http://ppa.launchpad.net/drizzle-developers/ppa/ubuntu intrepid main
deb-src http://ppa.launchpad.net/drizzle-developers/ppa/ubuntu intrepid main" …

You can do all sorts of magic with LVM, like backing up MySQL using file system snapshots, but in order to do so you need to set it first. When installing a new computer with Suse Linux I found that this was harder than expected, unless you know what to do. Needless to say, I didn't, but some friends gave me a hand on IRC, and now I do. :-)

So I wrote a little article about this which you can find here, in an effort to save others some time. The article is about setting up LVM with Suse Yast, but you can probably use it for other flavors of Linux, too.

You can do all sorts of magic with LVM, like backing up MySQL using file system snapshots, but in order to do so you need to set it first. When installing a new computer with Suse Linux I found that this was harder than expected, unless you know what to do. Needless to say, I didn't, but some friends gave me a hand on IRC, and now I do. :-)

So I wrote a little article about this which you can find here, in an effort to save others some time. The article is about setting up LVM with Suse Yast, but you can probably use it for other flavors of Linux, too.

Sysbench is a open source product that enables you to perform various system benchmarks including databases. Drizzles performs regression testing of every trunk revision with a branched version of sysbench within Drizzle Automation.

A pending branch https://code.launchpad.net/~elambert/sysbench/trunk_drizzle_merge by Eric Lambert now enables side by side testing with MySQL and Drizzle. On a system running MySQL and Drizzle I was able install this sysbench branch with the following commands.

cd bzr
bzr branch lp:~elambert/sysbench/trunk_drizzle_merge
cd trunk_drizzle_merge/
./autogen.sh
./configure
make
sudo make install

Running the default lua tests supplied required me to ensure …

If your compiling a product that includes a dependency of MySQL, you can easily get the error

configure: error: mysql_config executable not found

I generally don’t see this problem, because I use MySQL binary tar files, however if you use MySQL packages, such as Ubuntu, you can easily miss the required dependency.

My currently installed MySQL packages on this Ubuntu machine are:

$ sudo dpkg -l | grep mysql
ii  libdbd-mysql                               0.8.2-1-4.1                               MySQL database server driver for libdbi
ii  libdbd-mysql-perl                          4.008-1                                   A Perl5 database interface to the MySQL data
ii  libmysqlclient15off                        5.1.30really5.0.75-0ubuntu10.2            MySQL database client library
ii  libmysqlclient16                           5.1.31-1ubuntu2                           MySQL database client library
ii  libqt4-sql-mysql …

It is important to know and understand that while indexing columns in MySQL will generally improve performance, using the appropriate type of index can make a greater impact on performance.

There are four general index types to consider when creating an appropriate index to optimize SQL queries.

• Column Index
• Concatenated Index
• Covering Index
• Partial Index

For the purpose of this discussion I am excluding other specialized index types such as fulltext, spatial and hash in memory engine.

Example Table

For the following examples, I will use this test table structure.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(20) NOT NULL,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  external_id INT UNSIGNED NOT NULL,
  country_id SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY(id)
) …

Microsoft contributes to Linux. Acquia raises $8m. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

Microsoft contributes to Linux
Microsoft announced that it is to contribute device driver code to the Linux kernel under the GPLv2. Prompting us to publish a CAOS Theory Q&A. Answering one questioning we failed to ask, ZDnet reported that Microsoft’s Linux contributions should find their way into the 2.6.32 release.

Acquia raises $8m
Mass High Tech …