If you’re waiting for High Performance MySQL Second Edition to hit the shelf, you’re not the only one. I am too! I can’t wait to actually hold it in my hands.

But you don’t have to wait idly. No, not at all! You can pre-order it and then you’ll get it as soon as possible. Plus your pre-order will help them …

I’m headed to the OpenSolaris Developers Summit right now.

After that, I’ll be at CommunityOne, with the rest of the MySQL team. Just check out the schedule (its in PDF format though). Its truly packed, so if you’ve not signed up to come, do so, immediately. I’m also particularly interested in participating in the RedMonk Unconference. All in all, I think CommunityOne looks like it would be well suited for a week-long event, rather than a day-long event - so many places to be, so little time!

And after that, JavaOne! Its going to be packed all week long, but the good news is that I’ll be in downtown San …

On July 12 2007 I posted Bug #29768 - ‘ALTER EVENT .. RENAME TO .. drops the event if event_scheduler is ON’ - for MySQL® 5.1.20-beta and Miguel verified it one hour after my post with MySQL® 5.1.21-beta.

Some months later, on Oct 19 2007, Damien cannot reproduce that bug with the latest versions 5.1.23-rc and 5.2.6-alpha, but unfortunately I was still able to reproduce it with version 5.1.23-rc, as I posted on Feb 16 2008.

Now it seem that I can’t reproduce it with the new 5.1.24-rc. Maybe it has been fixed by fixing other bugs. The problem is that I am still able to reproduce it with version 6.0.4-alpha.

Just to summarize:
5.1.23-rc - VERIFIED
5.1.24-rc - CAN’T REPEAT
6.0.4-alpha - VERIFIED

What do you think? What about version 6.0.5-alpha?
Here’s the exact Script for reproducing it with old …

Creating table symlinks in MySQL is very easy and it is probably one of the features a lot of MySQL users overlook or never think about. You may ask, why would I want to use symlinks? Some of the reasons you would want symlinks is if you are running low on disk space on the partition where your data generally is and/or you want to move one (or more) table(s) on to a different disk/partition for performance reasons.

One of the things worth mentioning is that MySQL documentation states: “Symlinks are fully supported only for MyISAM tables. For files used by tables for other storage engines, you may get strange problems if you try to use symbolic links.” Keeping that in mind, if you have a innodb table and would like to create symlinks for, you should change the engine type to myisam before symlinking. Although, I have heard and personally used symlinks for innodb databases with no side affects.

First let us find out if your MySQL …

In the previous post I mentioned a way I use to preload Clustered Index (data) for Innodb tables. Though I thought this topic would benefit from a bit more information.

But lest first start with feature request for Innodb Team: All ways I mention here are hacks and they can't be as efficient as native support. It would be great if Innodb would implement command to preload table to Innodb buffer pool, which would simply go through .ibd file sequentially and inject pages in the buffer pool. This would make preload done using sequential file scan even if indexed suffered a lot of page splits.

Now lets continue to the hacks

So As I mentioned you can load Innodb Table Clustered Index in the buffer pool pretty efficiently by using something like SELECT count(*) FROM tbl WHERE …

Recently I was working with the customer who need quick warmup - to get Innodb table fetched in memory as fast as possible to get good in memory access performance.

To do it I run the query: "SELECT count(*) FROM tbl WHERE non_idx_col=0" I use this particular form of query because it will do full table scan - running count(*) without where clause may pick to scan some small index instead.

If your table is not fragmented one of two things should happen - either you should be reading at your hard drive sequential read rate or you would see MySQL becoming CPU bound if IO subsystem is too fast.

In this case however I saw neither - The vmstat showed read speed less than 10MB/sec which is very low for this system which had 6 15K SAS hard drives in RAID10.

Another indication of bad fragmentation was average IO size seen in SHOW INNODB STATUS output. It …

• MySQL :: MySQL 5.1 partitions in practice

What an odd comparison, you might think! I'd have thought so too, but let's explore this for a minute...

Last month, Akash Metha did an excellent review of GUI tools at the Brisbane MySQL Group meeting.
Among the numerous OS specific apps (Windows, Mac, Linux) some are cross platform, but the most prolific ones appear to be Windows specific, like HeidiSQL and Webyog.
And as it turns out, everybody uses phpMyAdmin at least sometimes. Other web-based tools include snazzy Flash or Ajax/JavaScript magic, but while they look nice, the attendees reckon it's just a tad indulgent and more simple stuff like phpMyAdmin is generally preferred - In 2007 …

The VM for Linux prefers system cache over application memory. What does this mean? The best way I can explain is by example.

Imagine you have 32 GB of RAM
MySQL is set to take 20 GB of RAM for a process based buffer and up to 6M for the various thread buffers.

Over a period of time the box swaps. The only thing that is running is mysql and its memory size is around 21GB for resident memory. Why does swap grow when there is plenty of memory? The reason is when a memory alloc is needed (thread based buffer is tickled) the VM will choose to use swap over allocating from the system cache, when there is not enough free memory.

DO NOT TURN OFF SWAP to prevent this. Your box will crawl, kswapd will chew up a lot of the processor, Linux needs swap enabled, lets just hope its not used.

So how do you stop Nagios pages because of swap usage? Well if you have a few choices.

…

I needed to uniquely identify a request to Pentaho (one particular action sequence request). Found a pretty darn easy way to do this with the help from Java RMI classes.

- Insert a Javascript data source

- Enter the following script

function getGUID() {
var VMID = new Packages.java.rmi.dgc.VMID();
return VMID.toString();
}
getGUID();

- Set return type as “string” for a new value

- Add it to your response

…