MySQL Performance Blog

Many 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 many workloads. 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:

• Fast recovery. Both recovery after crash and recovery from backup can take unacceptable long time, especially if you crashed with full 32GB buffer_pool. There is reported bug http://bugs.mysql.com/bug.php?id=29847, with ETA MySQL-6.0
• Preload table / index into buffer_pool. You can use custom queries by primary / secondary key to “warm up” part of table, but this solution is ugly and may be slow due to random logical I/O. Implementing preload of full .ibd file with sequential read would be much better solution. This is actually more important feature than it may appear at first look – for example if you put load on slave which is not warmed up properly – slave may never catch up slave, but with small load warm up may take hours to complete, so basically it adds several hours for operations team to complete task which requires restart of slave
• Copy single .ibd table from one server to different or (basically the same) restore single table from backup, possibly on different server (different slave). It’s all about time – copying whole 500GB backup while you need to restore only single 20GB table is very non-productive
• Open InnoDB tables in parallel. Currently opening table is serialized, and it is especially bad at start time, when InnoDB takes probes during opening table, as it is slow operation. See also http://www.mysqlperformanceblog.com/2006/11/21/opening-tables-scalability/. Partially it can be fixed by recent patches by enabling / disabling probes and changing count of probes, but still the solution is far from perfect

As you see the list list is not about performance but mostly about operations tasks, but with current amount of data and memory on servers they become critical. I do not know what is InnoDB plans about it and would like to hear if this is will be implemented anytime soon or never. Anyway I was asked what are our current plans about XtraDB with recent InnoDB-pluging release, as performance improvements in plugin may make XtraDB out of game – so I consider list above as roadmap for XtraDB and hope some or all features are implemented this year.

Do you have any other features you miss in current InnoDB ?

We added some new features to our backup tool, namely:

• Now we can handle log file taken during backup with size exceeding 4GB, it may happen if backup takes too long and you have a lot of updates on InnoDB tables
• Progress meter during recovery step, now you can see what percentage of log file was handled

Also we did extensive testing and fixed bugs, so now we consider current state as feature complete and with beta-release stability.

You can download current binaries RPM for RHEL4 and RHEL5 (compatible with CentOS also) and DEB for Debian/Ubuntu there
http://www.percona.com/mysql/xtrabackup/0.4/.
By the same link you can find general .tar.gz with binaries which can be run on any modern Linux distribution.
By the same link you can download source code if you do not want to deal with bazaar and Launchpad.

The project lives on Launchpad : https://launchpad.net/percona-xtrabackup and you can report bug to Launchpad bug system:
https://launchpad.net/percona-xtrabackup/+filebug. The documentation is available on our Wiki

For general questions use our Pecona-discussions group, and for development question Percona-dev group.

For support, commercial and sponsorship inquiries contact Percona

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
[read more...]

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!

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.
[read more...]

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
[read more...]

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.
[read more...]

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

occasionally, seemingly innocuous selects take unacceptably long.

Or

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.

Problems like this are always hard to debug. If it happens twice a week for 5 minutes at a time, your chance of getting someone logged onto the machine to watch it in action are pretty slim. And of course, when they do look at it, they see nothing wrong on the surface; it takes some very clever, very fast work with OS-level debugging and tracing utilities to really prove what's happening.

The two cases mentioned above were caused by scalability/concurrency/locking problems in the query cache. (One was on Windows, and we fixed it by guessing. The other was on GNU/Linux and Maciek isolated it with his elite skillz.) So if you're having random lockups, you might try disabling the query cache, and see if that solves it.

Hopefully this blog post will show up on Google and save someone time and money!

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.

[read more...]

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. In current multi-core word the compression utility should utilize several CPU to speedup operation, and another my requirement was the ability to work with stdin / stdout, so I could do scripting something like: innobackupex --stream | compressor | network_copy.

My research gave me next list: pigz (parallel gzip), pbzip2 (parallel bzip2), qpress ( command line utility for QuickLZ) and I wanted to try LZO (as lzop 1.03 command line + LZO 2 libraries). Actually lzop does not support parallel operations, but it is know to have good decompression speed even with 1 thread. UPDATE 17-Mar-2009: I added lzma results also by request from comments.

[read more...]