I’ve been thinking about the query cache since last years user conference. One of the features of the query cache is that it’s completely transparent to client. It achieves this by clearing cache entries for tables as soon as the tables are updated. This makes the cache inefficient for tables that are constantly updating.

While thinking about this also thought about slaves and non critical reads. In a replication setup reads that are sent to slaves expect to get data back that might not be the most current. In this situation it doesn’t make sense to expire the cache for every single update because queries running against the slave already know that they are going to get back slightly old data. I thought, “Why not add a time to live to cache entries instead of clearing them for every update?” So, I did.

I added an option to select called sql_cache_ttl. This option instructs the query cache hold the entry in the cache for …

Do you want to say hi? Pick up a free bottle opener (I haven’t seen them yet but Jeremy says they are high quality)? Sway my opinion on DRBD? Most of my time at the user conference this year will be spent at the Proven Scaling corner of the Solid booth. I also have the privilege of participating in Meet the Experts “Replication in the Real World” event at the Solid booth during lunch on Wednesday.

I’ve noticed a few blog posts recently about people saying how great DRBD is as a fail over mechanism for MySQL. My experience with DRBD has been the complete opposite. It offers almost no benefit over binary log replication for typical MySQL setups and prevents a few things that are possible with binary log replication.

Kaj Arnö has written an excellent blog post on the basics of DRBD. DRBD has one great feature that binary log replication doesn’t have. It can ensure that a write is synced to disk on two different hosts before allowing the application to continue. This is great for data redundancy but it introduces potential for instability in the setup. In a good fail over scenario a problem on the backup master should never cause an issue on the primary master. With DRBD the second master lagging behind because of a degraded raid, network issue, operator error, name …

It?s simply too inaccurate to be useful. I?ve switched to using google with firefox bookmark keywords instead. Here is how to set it up. In firefox click on Organize Bookmarks under the Bookmarks menu. Click the New Bookmark button and fill in the following values.

• Name: MySQL Manual
• Location: http://www.google.com/search?hl=en&q=site:dev.mysql.com%20%s
• Keyword: mysql
• Description: Keyword bookmark for using Google to search the MySQL manual.

Click ok and close the bookmark menu. Now instead of typing mysql.com/foo or using the search box on mysql.com you can simply type mysql foo into the address bar. Firefox will use your keyword to load the location in the bookmark replacing %s with foo resulting in results from the mysql manual. This can be done with any search engine. Simply do a test search and replace the search term with %s for the bookmark url.

One small fact about backup table and restore table that isn’t listed in the manual is that these commands lose the auto increment value if rows at the head of the table are deleted. For example if you have auto increment values of 1,2,3,4 in a table the auto increment value is 5. If you delete row 4 the next auto increment will still be 5. If you backup/restore the table the auto increment will be reset to 4. The auto increment value in myisam is stored in the MYI file. Since this file isn’t backed up myisam restores the auto increment value from the highest existing value in the table. This value may or may not be the actual value of auto increment. As the manual says these commands should not be used. Instead use mysqlhotcopy.

Innochecksum is a small undocumented (at least in the manual) utility that verifies the checksum of ibdata file pages. I stumbled across it the other day while wandering through the source tree. It has a small bug that prevents it from being able to check files larger than 4G. This patch fixes it in linux. Someone that knows more about large file support in different OSs please comment on the patch.

It lives in the extras folder in the MySQL source. Here is the credits comment:

/*
InnoDB offline file checksum utility. 85% of the code in this file
was taken wholesale fron the InnoDB codebase.

The final 15% was originally written by Mark Smith of Danga
Interactive, Inc.

Published with a permission.
*/

I think as a community we need to make sure that tools like this are kept in the forge and actively …

When Jeremy left yahoo a lot of people were left wondering “who would be the next Jeremy?” some people thought it would be me. Since then I have been asked several times why I’m not going to take a turn in the ivory tower. Now that things are in place the secret can be let out. Jeremy’s startup Proven Scaling is not just Jeremy’s startup but our startup. We have decided to take our MySQL skills and apply them to the problems of several companies.

Eric Bergen
MySQL Geek / Owner
Proven Scaling L.L.C.
eric@provenscaling.com

At the first ever MySQL Camp Proven Scaling is holding a session/BOF on replication. We want to hear what you like and don’t like about replication. What better to get conversation going but FREE BEER!

There is one tiny problem. We don’t know how much to buy or what kind. If you’re going to the camp help us out by putting your name and your favorite brew (brand and type) on the MySQL Replibeertion page.

If you can remember back to mid ‘05 when MySQL 5.0 was being released there was something missing. I have been thinking about writing about MySQL 5.0 being released too soon but I don’t think that was the case. Was 5.1 released too late? Maybe. Was something wrong with the 5.0 -> 5.1 schedule and feature set? Oh yeah.

Looking back at the change logs for 4.1, 5.0, and 5.1 I noticed something that I hadn’t though about before. I knew there was a difference in the release dates for 5.0->5.1 from 4.1->5.0 but I didn’t realize how drastic the difference was until I drew it out (on paper, sorry) today. Here is a summary of the major milestones from 4.1 Alpha to today:

• Apr 03 - 4.1 Alpha
• Dec 03 - 5.0 Alpha
• Jul 04 - 4.1 Beta
• Aug 04 - 4.1 Gamma
• Oct 04 - 4.1 Release
• Mar 05 - 5.0 Beta (Where is 5.1 Alpha?)
• Sep 05 - 5.0 Gamma.
• Nov …

I’m not the first person to run into this and I certainly won’t be the last. This is also already covered in: Bug #19093 I just want to say again how annoying it is that the default for show status is session instead of global. It bit me again yesterday. I ran show status and was confused because most of Com_* is 0 yet uptime was a few days. Then it hit like a brick to the face that I was working on 5.0 instead of 4.1 and the default for show status is session. I know the default has changed and it still bites me. This is going to cause people hours of confusion the first time they do what I just did.

If you want the default changed please go to Bug #19093 and add a comment.