You are here

MySQL Performance Blog

MySQL Performance Blog
Checked: 2 hours 24 min ago
Updated: 2 hours 24 min ago
Update every: 2 hours

Subscribe to MySQL Performance Blog

Talking MySQL to Sphinx

MySQL Performance Blog - Mon, 20/04/2009 - 5:34am

In the recently released Sphinx version 0.9.9-rc2 there is a support for MySQL wire protocol and SphinxQL - SQL-like language to query Sphinx indexes. This support is currently in its early preview stage but it is still fun to play with.

A thing to mention - unlike MySQL Storage Engines, some of which as InfoBright or KickFire take over execution after parsing, Sphinx MySQL support has nothing to do with MySQL - it is implementation of the wire protocol from scratch.

How to decrease InnoDB shutdown times

MySQL Performance Blog - Wed, 15/04/2009 - 3:21pm

Sometimes a MySQL server running InnoDB takes a long time to shut down. The usual culprit is flushing dirty pages from the buffer pool. These are pages that have been modified in memory, but not on disk.

If you kill the server before it finishes this process, it will just go through the recovery phase on startup, which can be even slower in stock InnoDB than the shutdown process, for a variety of reasons.

One way to decrease the shutdown time is to pre-flush the dirty pages, like this:

XtraDB storage engine release 1.0.3-4 codename Sakura

MySQL Performance Blog - Thu, 09/04/2009 - 4:22am

Today we glad to announce release 1.0.3-4 of our XtraDB storage engine.

Here is a list of enhancements in this release:

xtrabackup-0.5, bugfixes, incremental backup introduction

MySQL Performance Blog - Wed, 08/04/2009 - 3:23am

I am happy to announce next build of our backup tool. This version contains several bugfixes and introduces initial implementation of incremental backup.

Incremental backup works in next way. When you do regular backup, at the end of procedure you can see output:


MySQL Performance Blog - Tue, 07/04/2009 - 6:40am

No, this is not about Sun and IBM :) This is about MySQL. If you download latest 5.1.33 source code you may find there storage/ibmdb2i directory, which obviously is IBM DB2 related. Interesting that there is no mentioning of new engine in Announcement
Quick look into source code says

My “hot” list for next InnoDB features

MySQL Performance Blog - Tue, 31/03/2009 - 12:04am

InnoDB scalability problems seem fixed in InnoDB-plugin-1.0.3 and I expect InnoDB-plugin will run fine on 16-24 cores boxes for time being. And now it is time to look on systems with 32GB+ of RAM which are not rare nowadays. Working with real customer systems I have wish-list of features I would like to see soon:

Adjusting Innodb for Memory resident workload

MySQL Performance Blog - Thu, 26/03/2009 - 3:06am

As larger and larger amount of memory become common (512GB is something you can fit into relatively commodity server this day) many customers select to build their application so all or most of their database (frequently Innodb) fits into memory.

If all tables fit in Innodb buffer pool the performance for reads will be quite good however writes will still suffer because Innodb will do a lot of random IO during fuzzy checkpoint operation which often will become bottleneck. This problem makes some customers not concerned with persistence run Innodb of ram drive

2009 Percona Performance Conference Schedule

MySQL Performance Blog - Tue, 24/03/2009 - 10:37pm

The schedule for the 2009 Percona Performance Conference has been released. Take a look, see what interests you, and (optionally) register to come. We look forward to seeing you all there!

Entry posted by Ryan Lowe | No comment

Sphinx Technologies launches Sphinx Support

MySQL Performance Blog - Tue, 24/03/2009 - 9:31pm

A couple of weeks ago Sphinx Technologies, a company behind Sphinx Full Text Search Engine launched Sphinx Support Packages which I think is a great value for everyone using Sphinx in Production. This is also a great way to support the project and get something in return - even if you’re not actively using support it looks better than donation for accounting people.


MySQL Performance Blog - Tue, 24/03/2009 - 5:37am

Quite common task during schema review is to find the optimal data type for the column value - for example column is defined as INT but is it really needed or may be SMALLINT or even TINYINT will do instead. Does it contain any NULLs or it can be defined NOT NULL which reduces space needed and speeds up processing in most cases ?

These and similar tasks are often done by bunch of SQL queries while really MySQL has a native feature to perform this task - PROCEDURE ANALYSE

Another ingenious piece of Sun Marketing

MySQL Performance Blog - Fri, 20/03/2009 - 6:46pm

So a while ago I wrote about fun post about MySQL Scalability to 256 way….

Besides discussion on the thread itself I had a lot of private comments in my mail from Sun/MySQL employees which tended to agree with me on this being the a large stretch.

MySQL random freezes could be the query cache

MySQL Performance Blog - Fri, 20/03/2009 - 2:46am

I feel like I’ve been seeing this a lot lately.

occasionally, seemingly innocuous selects take unacceptably long.


Over the past few weeks, we’ve been having bizarre outages during which everything seems to grind to a halt… and then fixes itself within 5 minutes. We’ve got plenty of memory, we’re not running into swap, and we can’t find any queries that would seem to trigger outages: just tons of simple session requests all hung up for no obvious reason.

Converting Character Sets

MySQL Performance Blog - Tue, 17/03/2009 - 11:38pm

The web is going the way of utf8.  Drizzle has chosen it as the default character set, most back-ends to websites use it to store text data, and those who are still using latin1 have begun to migrate their databases to utf8.  Googling for "mysql convert charset to utf8" results in a plethora of sites, each with a slightly different approach, and each broken in some respect.  I'll outline those approaches here and show why they don't work, and then present a script that can generically be used to convert a database (or set of tables) to a target character set and collation.

Compression for InnoDB backup

MySQL Performance Blog - Mon, 16/03/2009 - 10:34pm

Playing with last version of xtrabackup and compress it I noticed that gzip is unacceptable slow for both compression and decompression operations. Actually Peter wrote about it some time ago, but I wanted to review that data having some new information.

xtrabackup-0.3, binaries and stream backup

MySQL Performance Blog - Fri, 13/03/2009 - 9:34pm

We are coming with next version of xtrabackup - online backup solution for MySQL 5.0 / 5.1 and InnoDB standard version, plugin modification and XtraDB. We still consider it as alpha version, though it shows perfect stable results in our tests.

Percona at PHP Quebec 09

MySQL Performance Blog - Tue, 10/03/2009 - 8:29pm

Percona presented two talks at PHP Quebec last week - one on A Tour of MySQL High Availability, and another on Performance Tuning MySQL. There was a great reaction to showcasing some of the quick-wins that can be found by using the Percona patches. Unfortunately, the one thing that I forgot to mention in the slides is that the patches are Open Source and free to use.

When would you use SAN with MySQL ?

MySQL Performance Blog - Mon, 09/03/2009 - 10:49pm

One question which comes up very often is when one should use SAN with MySQL, which is especially popular among people got used to Oracle or other Enterprise database systems which are quite commonly deployed on SAN.

My question in such case is always what exactly are you trying to get by using SAN ?

Depending on the goals the advice could be a lot different.

Debian / Ubuntu Percona builds

MySQL Performance Blog - Mon, 09/03/2009 - 6:29pm

We had too many requests for deb builds of Percona releases so I could not ignore that and added scripts to build binaries on our Ubuntu 8.10 box. It's going to be only 64bit releases (32bit is dead, isn't it?), and I tested binaries on Debian Lenny system and it seems working fine.

What does Using filesort mean in MySQL?

MySQL Performance Blog - Thu, 05/03/2009 - 8:36pm

If you were interviewing to work at Percona, and I asked you "what does Using filesort mean in EXPLAIN," what would you say?

I have asked this question in a bunch of interviews so far, with smart people, and not one person has gotten it right. So I consider it to be a bad interview question, and I'm going to put the answer here. If anyone gets it wrong from now on, I know they don't read this blog!

Xtrabackup, doing once…

MySQL Performance Blog - Thu, 05/03/2009 - 8:18pm

After some sprint coding and testing I am more than happy to declare Xtrabackup getting into alpha stage. And alpha means more that we have some dirty hacks in code / not fully scripted builds / not fully tested all MySQL versions, but backups work just fine! I am able to backup our production box and restore it!

Making replication a bit more reliable

MySQL Performance Blog - Thu, 05/03/2009 - 12:10am

Running MySQL slave is quite common and regular task which we do every day, taking backups from slave is often recommended solution. However the current state of MySQL replication makes restoring slave a bit tricky (if possible at all). The main problem is that InnoDB transaction state and replication state are not synchronized. If we speak about backup and you can execute SHOW SLAVE STATUS command you can get reliable information about current state, but some solutions does not allow that.


MySQL Performance Blog - Sun, 01/03/2009 - 7:08am

When I visit customers quite often they tell me about number of creative techniques they heard on the conferences, read on the blogs, forums and Internet articles and they ask me if they should use them. My advice is frequently - do not. It is fun to be creative but creative solutions also means unproven and people who had to become creative with their system often did that because they had no choice. Of course when they came to the bunch of conferences and told their story which resonated across the Internet sticking to the people mind as a good practice.

XtraBackup: Open Source Alternative for Innodb Hot Backup call for ideas

MySQL Performance Blog - Wed, 25/02/2009 - 4:11am

For long time as main backup solution for MySQL on Linux we have been using LVM snapshots. Performance concerns from performance critical envinronment caused us to do LVM performance research which showed horrible results forcing us to look at more performing alternatives.


Subscribe to MySQL Performance Blog