MySQL Conference 2007 1.2

So Shirley sent me an SMS to let me know my rehearsal was scheduled, and I managed to set up my (Linux) laptop with the projector, so I’m all set. Special thanks to Lauren from O’Reilly conferences who is awesome with computers and projectors, and didn’t blink an eye when I told him we might have a bit of issue. As it turned out, we solved it quite smoothly with the NVidia set-up utility, but we did have to plug in a local monitor to fool it into setting the right resolution. For some reason, it detected the projector as a TV and only allowed me to set 640×480.

The sessions themselves are awesome, there’s just so much information out here. It’s hard to choose which session to go to and which one to miss. Oh well, I guess there’s always a next time.

The session on MySQL stored procedures was interesting, if a little dry — the original presenter could not make it and the substitute was too obviously not familiar with the presentation.

As I learned at the session on technology at Digg.com, the digg (digg.com) site uses a lot of intelligent caching to generate dynamic pages for individual users with minimal hit on the database. Interestingly enough, their slave servers have more horsepower than the master, so they know that should they have to promote a slave, they will still be in good shape. Quite a refreshing change from the usual set up where slaves/standby servers are less capable than the main db.

I had an interesting lunch discussion with Jay Pipes all about the roadmap for Falcon, InnoDB and MySQL, with some neat tools and features thrown in. Carsten has apparently written a PHP/ncurses-based command-line tool for MySQL (I will try to get you the link to it later) which can do a bunch of queries and switch between the results on the fly with tabs and such. Jay called it “vim for the MySQL CLI”. Sounds intriguing!

Meanwhile my presentation went well, I had a packed room, no standing room either. It is quite interesting that in spite of the fact that there have been a few tuning presentations already, there’s still interest in more. A quick question revealed the room was quite evenly split between DBAs and Developers..

Some of the questions I got asked:

• How do we know if we’re getting close to using up our “buffers” — i.e. if a sort for a query is getting close to the sort_buffer_size. The short answer — we don’t. Unfortunately, MySQL has very little metrics for us to use, so the only time we will know is when we do go over the allocated buffer and it spills to disk, causing a noticeable performance drop.
• What is the best database engine? Or, which one would I recommend? Quite frankly, thats almost like choosing a car — it depends on what you want it to do, how much you can spend, what you want out of the engine, and what appeals to you. In other words, take a good hard look at your needs, your application, the benefits and limitations of each engine and then decide.
• The recommended InnoDB flush method on Linux: O_DIRECT
• The way InnoDB keeps track of data also generated a lot of interest. The fact that datafiles are only written to at a checkpoint was a revelation to most people, so I’ll explain it again: all commits are synced to disk but only to the InnoDB log files. It is only at a checkpoint that the changed data is written to the data files. This reduces I/O to the data files and avoids a lot of small, random writes to the data files. However, data is never lost, since a commit does generate a write to the log file and it is synced to disk. A checkpoint is forced when the log files switch, so it is important to have a big log file size, usually 25%-50% of the InnoDB buffer pool size.

I’ll explain it a little more in detail in a separate post. Meanwhile this one is going out a little late — I got back late last night, the BoF sessions (Birds of a Feather) were very interesting and continued way past their schedule. Heikki Tuuri of Innobase was brilliant, and then the BoF on tuning just brought out the usual common pain points with MySQL and PHP.

Stay tuned!

–Raj.