"What cache hit rate is good for optimal MySQL Performance" is typical question I'm asked. It could by MyISAM key_buffer or Innodb innodb_buffer_pool it does not really matter. In both cases trying to come up with constant "good" hit rate is looking for trouble. Well of course you can name 100% hit rate and all data fits in memory as perfect case but that is not the answer people are looking for.

First thing which needs to be understand is - cache hit ratio can be computed differently for different engines. For example for Innodb page hits are counted for all subsequent page accesses if page is scanned not just once. This means if you would have completely IO bound full table scan and you have 100 rows per page you will get 1 miss per page and 99 hits, making your hit ratio 99% for what would be 0% in many peoples mind.

But what is even more important is hit ratio is not directly relevant for performance. Take the following example …

Some weeks back I posted two diagrams with the architecture for SQLbusRT. One was for one database instance, and one was for multiple databases.

After reading a bit on about the triggers and events in MySQL 5.x, I decided to revise the architecture slightly. In my previous architecture, I had taken event handling outside of the database, but since MySQL supports triggers and events, there is no need to do this anymore.

I've not only taken out the event handling, I've also enhanced the readability of the diagram. To sum it up, these changes have been made:

• Event handling is now handled inside the database, and has therefore been taken out of the diagram
  
• The edges are now labeled for clarification
  
• Grey boxes have been added to show the process boundaries; every box is one single process
  

These changes result in the following diagram (click to …

As mentioned in this CNET article, Tuesday at OSBC London I opened the conference with a suggestion: Europe, the birthplace and cradle of the open source revolution, needs to reassert itself as the center of the open source phenomenon. Linux, MySQL, JBoss (Well, Marc is French with influences of Spain in him... :-), Trolltech, etc. These early open source leaders all came out of Europe.

As open source has commercially matured, however, the United States has taken over. Silicon Valley has funded the next round of open source, and we're not necessarily the better for it. There is an ethos in the projects and startups that emerged from the social democracies of Europe that one doesn't necessarily find in the capitalism-spawned companies.

Let's be clear: I am an unabashed open source capitalist. I live in the US and think …

two interesting posts arrived on the memcached list which might be interesting to performance people.

The first was a comparison of The fastest lanugage binding on which ‘P’ language performed better. To make a note the PHP version actually uses libmemcache a ‘C’ library which goes a bit of the way to explain the wild disparity in speeds.

The 2nd more interesting one (to me) was the discussion of how Digg switched from using mysql to memcached with v3 of their new interface to handle storing sessions, due to a hardware crash on their mysql server.

others mentioned using InnoDB for this instead of MyISAM, with the biggest issue …

The Patently-O blog reported yesterday that a software company named FireStar has sued Red Hat over an alleged patent infringement. Patently-O also provides the complaint and the patent document, and quotes from Red Hat’s patent policy. The FireStar suit relates to a piece of software that Red Hat acquired as part of JBoss Inc.’s intellectual property.

It seems to me that the FireStar patent is quite broad, and if it is upheld, it will affect other companies as well. While I know that certain parts of the free and open source software (FOSS) community don’t like to hear this, I have repeatedly stated that FOSS projects and products are particularly threatened by software patents. In this specific case, however, the fact that an open source program is at the center of …

Growing up in the world of linux uptime was always considered a good thing. On IRC every once in a while someone would post an uptime. Everyone else in that channel would then check their uptime and if it was greater or close they would post it in the channel. Most of these systems were home linux boxes used for compiling random programs or maybe hosting a webserver for experimenting. It was fun to see how long we could keep them running for. Since those days I have come to realize that high uptimes are a bad thing.
Keeping a server up for months or even years means that you aren’t maintaining it. It hasn’t been kept up to date with new kernels that have fixes for security holes. It doesn’t have new packages or new tools that can help it run more efficiently and have features that can make using it easier. It’s also not up to date with new servers that are being deployed which means that people logging into your server with a high …

cjcollier@norseth:~/Desktop$ sudo dd if=/dev/zero of=/dev/hda3

Time to return the computer to MySQL

Thanks for letting me keep it long enough to clean it up, folks!

Assume that you have a list of numeric values mixed with numeric 
values appended with characters - like 2a, 12, 5b, 5a, 10, 11, 1, 4b.



Sorting on a list like this would usually end up so:

mysql> SELECT version
-> FROM version_sorting
-> ORDER BY version;
+---------+
| version |
+---------+
| 1       |
| 10      |
| 11      |
| 12      |
| 2a      |
| 4b      |
| 5a      |
| 5b      |
+---------+
8 rows in set (0.03 sec)

To sort the list based on the numeric values, simply do this:

mysql> SELECT version
-> FROM version_sorting
-> ORDER BY CAST(version AS UNSIGNED), version;
+---------+
| version |
+---------+
| 1       |
| 2a      |
| 4b …

I've seen a lot of SQL code that uses an IN clause as a place to put a variable number of parameters, allowing the query to be more flexible. There are several downsides to this technique. This article discusses those downsides and explains how to avoid them.

Finally got to capturing, editing, and exporting the video from the June Boston MySQL meetup. This month Sheeri did a presentation on storing images in MySQL.

The video is up on Google, and here and here in Quicktime.

Thanks to Martin at kbglob for donating bandwidth.