My previous blog entry on MySQL scalability on the T5440 is now completed by a Sun BluePrint that you can find here.

See you next time in the wonderful world of benchmarking....

How many fat marathon runners do you know? I have yet to see anyone run the Boston marathon who looks like me. What about a fat man competing in the 100 yard dash in the the Olympics? no? It seems to make sense then that maybe a fat/bloated index or primary key may not be able to compete for the performance crown.  As you may have read,  One of my common performance mistakes is using incorrect or bloated datatypes in your table design ( shameless plug: stop by the MySQLCamp unconference at the UC and hear me babel on about common PT mistakes). Unfortunately even wise datatype selection can result in suboptimal indexes. Raise your hand if you have heard someone tell you not to use a char/varchar as a PK in innodb?

Not everyone has. Two weeks in a row makes a trend, and a trend means I should probably blog about this. Ran into a pair of clients who are using char/varchar primary key fields in Innodb. I strongly urge my clients …

Yesterday I posted on the curse of the autocommit when using jdbc, but as sholmi commented you can also see a ton of “show variables”  statements as the connector J tries to figure what settings the database is using.   I have seen this before as well and  you can reduce these calls as well, to do so try setting the cacheServerConfiguration paramter to true, which caches the variables instead of rechecking.  The official doc says:

“Should the driver cache the results of ‘SHOW VARIABLES’ and ‘SHOW COLLATION’ on a per-URL basis?”

In my travels I do a lot of analysis of the slow query and the general query logs. In many Java shops I end up seeing the most frequently called command is

set autocommit=1;

Sometimes this statement is called 300-400x more then any other statement. What’s surprising to most of these shops, is that they are leaving autocommit on… so logically they are baffled why the statement is appearing over and over again. You see by default the autocommit statement wraps the calls to the database. So if you trace the calls you will see:

set autocommit=1;
select * from sometable
set autocommit=1;

Yep, even select statements see this behavior. So what, Its a small set statement right? . But that small set statement has to traverse the network, be parsed, be executed, then return something. Lets assume that adds 2ms to your transaction, over the course of a day that’s going to really add up. Over a million queries that …

For those of you interested in high availability for databases, this week brought a whole flurry of DRBD related posts in the MySQL blogosphere.

To kick off the week, and perhaps surprisingly for some, Peter Zaitsev blogs about the superiority of DRBD over SAN based high availability for MySQL. The Percona folks seem to like our recently introduced support for Dolphin Express replication.

Then, Charlie Schluting kicks off a series of articles …

I am happy to say that I found a kernel level way to join a ramdisk (actually not a tmpfs strictly speaking) with a normal file system. This cut my motivation for the MiniWheatFS project by 99.999%. I recall that the goal of the MiniWheatFS project was to provide and efficient filesystem for the the “tmpdir”, where MySQL puts its temporary files and tables. The trick uses the tendancy of ext2 (and probably ext3 and 4) to use the first available block from its bitmap and LVM to join a ramdisk with a normal device. Here are my steps.

1. Give a ramdisk to LVM

root@yves-laptop:/home/yves# pvcreate /dev/ram0
  Physical volume "/dev/ram0" successfully created

By default, my Ubuntu laptop creates 16 ramdisk of 64 MB each. RAM is not allocated until used. To create bigger one, you need to add a ramdisk_size=SizeInKB to the kernel command line in Grub menu.lst or …

Salle gave the first answer. Converting the tables to MyISAM would only exacerbate the problem. Instead of having one file per table, the …

NDB Cluster is the only integrated sharding framework that I know of (educate me if I am wrong) but it is known to have issues with large joins. These days, large databases that would benefit from a sharding framework are often for social networking type applications that requires large joins.

Actually it is not NDB that is the root cause of the joins problem, it is the way MySQL executes joins. Instead of asking the cluster for a large number of rows for the secondary table it joins to, the current version of MySQL does ask one row at a time. NDB cluster answers those queries very rapidly but, the time to hop over the network kills performance. The MySQL-6.0 branch will implement the Batch Key Access (BKA) algorithm which will solve that issue and might create database application killer with NDB cluster.

Although right now BKA is not available, there are ways to execute those queries in an efficient way by rewriting them. The …

My favorite Linux tool in DB work is ‘iostat -x’ (and I really really want to see whenever I’m doing any kind of performance analysis), yet I had to learn its limitations and properties. For example, I took 1s snapshot from a slightly overloaded 16-disk database box:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           8.12    0.00    2.57   21.65    0.00   67.66

Device:  rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s \
sda     7684.00    19.00 2420.00  498.00 81848.00  5287.00 \

        avgrq-sz avgqu-sz   await  svctm  %util
           29.86    32.99   11.17   0.34 100.00

I pasted this somewhere on IRC, and got “doesn’t look too healthy” and that it is disk-bound. Now, to understand if it really is, one has to understand what iostat tells here.

First line of numbers shows that we’ve got plenty of CPU resources (thats because nowadays it is quite difficult to get a box with not enough CPU power, …

Why should you care about the latest “early adopter” release of the InnoDB Plugin, version 1.0.3?   One word: performance! The release introduces these features:

• Enhanced concurrency & scalability: the “Google SMP patch” using atomic instructions for mutexing
• More efficient memory allocation: ability to use more scalable platform memory allocator
• Improved out-of-the-box scalability: unlimited concurrent thread execution by default
• Dynamic tuning: at run-time, enable or disable insert buffering and adaptive hash indexing

These new performance features can yield up to twice the throughput or more, depending on your workload, platform and other tuning considerations. In another post, we explore some details about these changes, but first, what do these enhancements mean for performance and scalability?

In brief, we’ve tested …