InnoDB executes a lot of transactions for MySQL users. And users and technical experts in the InnoDB community exchange lots of information about InnoDB … transacting in ideas … all the time. A great opportunity to participate in some of these transactions is the upcoming MySQL Conference and Expo.

Does your InnoDB-based application need more transactions to execute more quickly and reliably? Come hear these noted speakers present information that can help you get the most out of InnoDB:

• Distributed InnoDB Caching w/memcached — Matthew Yonkovit & Yves Trudeau (MySQL), Tue 2pm
• InnoDB database recovery techniques — Peter Zaitsev (MySQL Performance Blog), Wed 3:05pm
• …

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....

Well I am still not happy with a lot of the code, but I have had some people ask me to help test my attempt at a new MySQL benchmark. So I cleaned up the code a bit and pushed what I had to LaunchPad. It’s way to early for this to be ready for prime time, I am using it to stress flash, but as I do i am still finding issues, so use at your own risk. My goal is to make this more generic and allow user customizable SQL files to be loaded in…. something like:

1 | select * from table where a =?  |  rand | a | 1000
2 | select * from table1,table2 where table1.id=table2.id and c = ? and d=? | rand,1-200 | c,d | 10

This would mean execute query #1 , using a random value from a 1000 times in a loop before exiting… and then execute query 2, 10 times using a rand value from c, and a value between 1-200 for d….

But that is a ways off.

For now I support 4 tests which are hard coded in:

mx = mixed wokload of …

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....

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 …