The latest from the MySQL community

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

…

Ever since Peter over at Percona wrote about MySQL and swap, I’ve been meaning to write this post. But after I saw Dathan Pattishall’s post on the subject, I knew I’d better actually do it.

There’s a nasty problem with Linux 2.6 even when you have a ton of RAM. No matter what you do, including setting /proc/sys/vm/swappiness = 0, your OS is going to prefer swapping stuff out rather than freeing up system cache. On a single-use machine, where the application is better at utilizing RAM than the system is, this is incredibly stupid. Our MySQL boxes are a perfect example – they run only MySQL and we want InnoDB to have a lot of RAM (32-64GB …

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 figure out how much demand there is, so it doesn’t sell out and make you wait for your own copy.

Zack Urlocker says MySQL 5.1 has zero bugs. He may have been misquoted, or quoted out of context, but there it is. I’ll quote enough of it that you can’t take it out of context twice: Mickos also said MySQL 5.1 has upgraded its reliability and ease of use over 2005′s v5.0. "Now we can admit it, but this version is much improved over 5.0, which we weren't totally happy with,"

2008 was announced as the year of the *change*.

On February I started work on performance tuning features and I planned those two new Editions of HoneyMonitor (Pro and Audit Pro).

On March I returned back to Trieste - my University town - after a two-year-full-immersion experience at HoneySoftware, in Caltagirone, where I forgot all the job-not-related aspects of the human life.

On April, meeting my (old and new) friends, going to cinema and doing things like those, I recovered my social life.

I removed the password protection from my blog, I announced HoneyMonitor Audit Pro (publishing 150+ screenshots of that Edition), I removed some out-of-date pages of our site …