MySQL Performance Blog

We prepared RPMs of our release for RedHat 5 / CentOS 5 x86_64 platform.
http://www.mysqlperformanceblog.com/mysql/RPM/RHEL5/5.0.62/

There was question what patcheset includes and if there is manuals.
We have:

• microsecond resolution in slow-log
• extended query plan in slow-log and InnoDB statistics. You can read more here http://www.mysqlperformanceblog.com/2008/04/20/updated-msl-microslow-patch-installation-walk-through/
• User / Table / Index statistics (Google’s patch). More here http://www.mysqlperformanceblog.com/2008/04/24/mysql-extensions-for-hosting/

I use Maatkit for a lot of grunt work and thought you might appreciate this quick tip. Suppose you have a bazillion tables to convert from MyISAM to InnoDB, but they are mixed in with other tables that are already InnoDB, or are another storage engine that you don't want to touch.

Here's a bonus tip, while I'm at it. I had a client a while back whose application creates tables as needed, so they had about 90,000 tables in a bunch of different databases, all named things like user_123_456_friends. I wanted to add an index to them -- but not to the ones named friends_123_456_user.

Boy, is that a lot easier than adding indexes to 90k tables by hand!

So I'm back from OSCON 2008. As usually it is fun conference to attend. Though I could only come for couple of days (Wed/Thu) as things are too busy here.

Same as previous year I would note the conference is not as much backend and high performance focused as MySQL Conference or even Velocity, though this year there are number of interesting talks for example about Hypertable and lucidDB.
[read more...]

This article is not meant to malign hosting providers, but I want to point out something you should be aware of if you're getting someone else to build and host your servers for you.

Most hosting providers -- even the big names -- continue to install 32-bit GNU/Linux operating systems on 64-bit hardware. This is a serious mistake.

You have to tell them to install a 64-bit operating system. If you don't then you will come to a point where your needs grow and you want to use more memory -- and they will gladly install 8 or 16GB of memory for you, but MySQL can't use it because it runs in a single process, which is limited to about 2.5GB of memory. And then you have to rebuild the whole operating system from scratch. But you don't want any downtime, so you have to buy another server, set it up as a slave, switch your site to use it, and then rebuild the old server. That 32-bit OS turned into a pretty expensive mistake.

I do not know why the hosting providers keep doing this. Just yesterday I got a quote from a hosting provider for a medium-high-end system with 8GB of RAM, and forgot to tell them 64-bit OS, and they actually listed 32-bit explicitly on the quote -- useless! I would estimate about half of all the hosted systems I've seen so far have this mismatch. I don't know why they do this -- maybe there is a reason, but I don't know it and it looks pretty silly to me. 64-bit hardware and operating systems aren't new anymore. In fact, 32-bit is hard to find in server-class hardware these days. So it certainly looks like the hosting companies need to change what they're doing, but maybe there's a different reason.

Daniel Nichter, the author of several very useful MySQL tools, has joined with Percona to continue to improve Maatkit -- the "other" toolkit we rely on daily. Daniel is a skilled Perl programmer who understands MySQL. He is in a perfect position to move the project forward on a more regular basis than I've been able to commit time to since I've taken on a leadership role in Percona.

Right now he's working on an unreleased tool we will use to make our performance audits more efficient. As a result, we'll be able to deliver more value to our customers and assess servers more quickly in the event of an emergency. (By the way, if you'd like to sponsor features you need for a Maatkit tool, contact us and we'll be glad to discuss it with you.)

Welcome, Daniel! And a toast to Maatkit's continued improvement and usefulness!

As there were many requests for Debian binary packages in our announcement of MySQL releases with custom patchsets, we decided to play with it and built .deb, which you can find there:
http://www.mysqlperformanceblog.com/mysql/debian/

You can also add the following repositories into your sources.list:

deb http://mysqlperformanceblog.com/mysql/debian/5.0/ binary-amd64/
deb http://mysqlperformanceblog.com/mysql/debian/5.0/ source/
deb http://mysqlperformanceblog.com/mysql/debian/5.1/ binary-amd64/
deb http://mysqlperformanceblog.com/mysql/debian/5.1/ source/

Also we have generic RPMs for x86-64 architecture:
http://www.mysqlperformanceblog.com/mysql/RPM/. They are based on .spec file provided in MySQL source tree and should be compatible with SuSe, CentOS and RedHat.

Your comments are welcome!

As you probably already seen in a post by Baron, Sphinx Release 0.9.8 is finally out, just in time for OSCON 2008. Even though it is "minor release" if you look at the number, it is major release in practice (and you can view snapshots as minor releases). The changes since 0.9.7 are dramatic with over 70 new features corresponding to over 15 months of work. With zero in front it still looks like "beta" release though it is very stable and widely used.

Myself I would have already named it 1.3.0 or something like it (with 3rd number used for minor releases) and use version 2.0.0 as a target for full live updates. Though it looks like Andrew has set his goal on naming it 1.0 only when dynamic updates work and starting from 0.9.1 it did not allow too much version flexibility.

Sphinx will be presented this year on OSCON as .ORG Exhibitor, with me running the show - it was too expensive for Andrew to come from Russia, especially as he did not get a session at OSCON.
[read more...]

As Baron writes it is not the number of rows returned by the query but number of rows accessed by the query will most likely be defining query performance. Of course not all row accessed are created equal (such as full table scan row accesses may be much faster than random index lookups row accesses in the same table) but this is very valuable data point to optimize query anyway.
[read more...]

MySQL 5.1 was in RC stage for around 10 months now finally planned to be released as GA soon. As Monty Says MySQL 5.1.26 will be renamed as GA if no serious bugs are discovered.

Lets help MySQL to ensure GA release will be indeed GA quality and support Monty in his call to ensure this RC candidate works well for you.
[read more...]

Percona has a strong team of MySQL developers and consultants on board, so we decided to prepare MySQL releases with our patches and third-party patches which we think are very useful. We actually use these internally and for our customers.

Current releases include:

• microslow patch (enables microsecond resolution in slow logs)
• execution plan (show info about query execution in slow log)
• InnoDB statistic (show extended InnoDB usage during query execution in slow log)
• Google's patches with user statistics, and statistics about tables and indexes usage

You can find more info about some of our patches here:
http://www.mysqlperformanceblog.com/2008/04/20/updated-msl-microslow-patch-installation-walk-through/
Some features were sponsored by customers and we are allowed to publish them under GPL and some we developed for internal needs.

We prepared 5.0.62 release (based on MySQL Enterprise 5.0.62 source, taken from http://mirror.provenscaling.com/mysql/enterprise/source/5.0/) and 5.1.26-rc release, and they are currently available only as source .tar.gz, but we will provide public access to our YUM repository with binaries, RedHat/CentOS and Suse RPMS / SRPMS when they're available. Here are links to the source releases:

mysql-5.0.62-percona.tar.bz2 md5:0d8a37e09eca93cde6170866293a82ff
mysql-5.1.26-percona.tar.bz2 md5:0574b38755786a97bc476058051cbe82

You can find the individual patches here:
http://www.mysqlperformanceblog.com/mysql-patches/

Besides standard 5.1, we are going to make releases with the InnoDB plugin version compiled in (not as a plugin), as the current system with independent InnoDB binaries does not seem handy for us.

We have couple of ideas what else we can develop and include in our releases, but also your comments are very welcome.

We are still investigating Launchpad features, there is our -percona branch, but it is outdated at this moment. Probably our next releases will be based on Launchpad branches, we still need to decide how it fits into our development model.

Disclaimer: Although we use tested releases as best as we can, and use them internally, we do not claim they are bug-free, and you should use them only if you know what you are doing.