Today seems to be Innodb day in our Blog, but well this is the question which pops ups quite frequently in Innodb talks and during consulting engagements.

It is well known to get better performance you should normally set innodb_log_file_size large. We however usually recommend caution as it may significantly increase recovery time if Innodb needs to do crash recovery.

I'm often something like what recovery time would expect for 512MB total log file size. This is however question with no exact answer as there are many things which affect recovery time.

For example Paul mentioned YouTube has something like 4 hours recovery time even if logs are of reasonable size and wikipedia has 40 minutes or so with innodb_log_file_size=256MB. In other cases I know same log file time may have recovery time of 5-10 minutes.

To understand things affecting recovery time you better to understand how Innodb recovery …

It was long overdue, but now it has happened: Planet MySQL now provides a section to aggregate french blogs about MySQL! Thanks a lot to Jay for updating the code and Pascal Borghino for the localization. If you are a MySQL enthusiast located in France (or any other french-speaking country) and you enjoy writing about your passion in your native language, submit your feed now!

Eric Bergen from Proven Scaling (which I had the pleasure to meet in person during the MySQL Conference & Expo in Santa Clara last month) was kind enough to send me a patch for the mylmbackup tool, which justifies a new release:

Attached is a patch file for mylvmbackup that adds the ability to use
lvm version 2 and perform innodb recovery on the snapshot prior to
creating a tar ball. The option is named --innodb-recover.

I've also fixed a bug with default value handling for command line
options. In version 0.4 if a config file was specified default values
in the script were all changed to blank. This means that the config
file had to supply values for every variable instead of just the
values that need to be changed from default.

This …

It might look like it is too late to write about stuff happened at Users Conference but I'm just starting find bits of time from processing accumulated backlog. The Theme of this Users Conference was surely Storage Engines both looking at number of third party storage engine presented, main marketing message - Storage Engine partnership with IBM, "Clash of Database Egos" - Storage Engine developers showcase as one of keynotes.

Today let me start with most popular transactional storage engine for MySQL - Innodb.

Innodb Storage Engine was covered in a lot of talks, many of them done by Innodb users. I found these items forth interest

Innodb Zip Page Compression This feature was in development for a while. I believe it is at least 3rd Users Conference it is being talked about, but now it is working enough to do some tests. There seems to be still fair amount of work required to make it work well such as having …

The weather today was wonderful, time for the kilt.

I met up with sierrascape at her studio, picked up sushi at Hah-Nah, and ate lunch outdoors on the grass at Cal Anderson Park.

This afternoon I met up with krow at Victrola Cafe, and watched and assisted as he wrote up a skeleton UDF for MySQL. The idea is that I use the UDF mechanism to load my SNMP agent for MySQL into 4.0, 4.1, and 5.0.

This evening Paul came over, and we did Knuth, with Eric on Skype.

And now, Kidde keeps coming over and yelling at me to come to bed.

Just a little heads-up and a bit of MySQL-related technical content for all of you still out there following along…

At Proven Scaling, we take on MySQL performance problems pretty regularly, I’m often in need of good tools to characterize current performance and find any issues. In the database world, you’re really looking for a few things of interest related to I/O: throughput in bytes, requests, and latency. The typical tool to get this information on Linux is iostat. You would normally run it like iostat -dx 1 sda and its output would be something like this, repeating every 1 second:


Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 8.00 0.00 4.00 0.00 96.00 0.00 48.00 24.00 0.06 15.75 15.75 6.30


Most of the output of iostat is interesting and reasonable for its intended …

If you're at JavaOne this week, please do visit the "Solaris + AMP" pod (#976). We are demoing the use of SMF and dtrace on Cool Stack. You can see how dtrace can be used to debug and trace the code path through your entire application, starting from the Javascript in the browser, through PHP and finally to MySQL at the back-end. We are also distributing Cool Stack 1.1 on a DVD at the pod.

I will be there on Thursday between 11:00 - 3:00 PM, so please do stop by and say hi. 

Shanti 

If you've used the UNIX find command for more than a trivial find-and-print, you know how powerful it is; it's almost a miniature programming environment to find and manipulate files and directories. What if you could do the same thing with MySQL tables and databases? That was the inspiration for writing this tool. I was about to write several other tools to do some MySQL administrative jobs when I realized I could generalize and make something much more useful and powerful.

I’ve been looking for an open source project to collaborate for some time now, and given the time I’m spending with MySQL lately and the expertise I’m gaining thanks to MySQL training, it looked like an obvious choice.

During the last advanced bootcamp, Tobias found bug #27894, which apparently was a simple fix. Dates in binlog were formatted as 736 instead of 070306 (for 2007-03-06). During the bootcamp I used my lonely nights at the hotel and came up with a patch, and some days later my first contribution was going into the main MySQL code.

The problem

Now I had to find something bigger. One of the things that most annoys me of MySQL is the lack of some way to abort a procedure or …

MySQL replication is cool. Almost everyone, when asked what they like best about MySQL, will mention replication. There can be no doubt that many web companies, Yahoo included, owes much to MySQL replication. How else can you scale the reading capacity of your database so easily?

It's not all rosy, replication has its problems too. It's very simplistic (I like the term brittle, if any errors happen when executing the SQL, it just stops), it can be inefficient in some places (think UPDATE that does a table scan to change one value, though 5.1 starts to address this with row-based replication), and it is single-threaded.

Single Threadedness
Let me stop here for a second. A lot of people spend good money on a nice Master DB, …