MySQL Performance Blog

When doing performance analyzes you often would want to see 95 percentile, 99 percentile and similar values. The “average” is the evil of performance optimization and often as helpful as “average patient temperature in the hospital”.

Lets set you have 10000 page views or queries and have average response time of 1 second. What does it mean ? Really nothing – may be one page view was 10000 seconds and the rest was in low milliseconds or may be you had every single page view taking 1 second, which are completely different.
[read more...]

Working with number of hosting providers I always wonder how do they manage to keep things up given MySQL gives you so little ways to really restrict how much resources single user can consume. I have written over a year ago about 10+ ways to crash or overload MySQL and since that people have come to me and suggested more ways to do the same.

This is huge hole in MySQL design, thinking little about users isolations and resource quotas and interesting enough I have not seen significant changes in fresh our MySQL 5.1 GA or even something major on the roadmap for future MySQL versions. May be Drizzle will give it a thought ? This surely would help adoption by (especially low end) Hosting Providers and remember this exactly where a lot of kids start to develop their first sites and play with web technologies.
[read more...]

There are all sorts of different interfaces to memcached, but you don’t need any of them to make requests from the command line, because its protocol is so simple. Try this, assuming it’s running on the usual port on the local machine:

[read more...]

Happy Thanksgiving and little holiday challenge for you.
Say you have a trigger on the slave which you would like to work differently, depending on whenever update is executed via replication thread vs updating table locally ? This can be helpful for example for auditing updates which were done directly instead of coming from the master and some other cases.
Suggest how you would do it by commenting

I have written before – MyISAM Does Not Scale, or it does quite well – two main things stopping you is table locks and global mutex on the KeyCache.

Table Locks are not the issue for Read Only workload and write intensive workloads can be dealt with by using with many tables but Key Cache Mutex will still hunt you. If you aware of MySQL history you may think Key Cache scalability was fixed with new Key Cache in MySQL 4.1, and indeed previously it did not even scale with one CPU as global lock was held during IO duration, In MySQL 4.1 the lock is held only when key block (1KB to 4KB) is being copied from Key Cache to thread local buffer, which is terrible contention spot in particular on systems with many CPU cores.

Happily there is solution, or at least half of it.
[read more...]

Our customers or prospective customers often ask us how we do a performance audit (it’s our most popular service). I thought I should write a blog post that will both answer their question, so I can just reply “read all about it at this URL” and share our methodology with readers a little bit. This fits well with our philosophy of openness. It also shocks people sometimes — “you’re giving away the golden goose!” Not really. What you hire us for is our experience, not a recipe that anyone can follow.

A full performance audit is far more extensive than this article can cover, and might wander into Apache, networking config, caching layers, etc. Wherever the problem is, we’ll track it down. I won’t talk about that. That’s not because I want to keep secrets from you. To the contrary, I’d love to share it all with you. But that’s a huge job; it will take many pages, and I’m not going to write that much.

[read more...]

INFORMATION_SCHEMA, in particular by favorite TABLES table is not only helpful to understand tables you have on the system, but I have also found it to be very helpful as a scripting language for variety of database administration tasks. It can be more straightforward compared to using shell or Perl when the operation is database specific.

For example if you would like to MySQLDump only Innodb table in one file per database you can do the following:
[read more...]

Peter wrote a post a while ago about choosing a good InnoDB log file size.  Not to pick on Peter, but the post actually kind of talks about a lot of things and then doesn’t tell you how to choose a good log file size!  So I thought I’d clarify it a little.

The basic point is that your log file needs to be big enough to let InnoDB optimize its I/O, but not so big that recovery takes a long time.  That much Peter covered really well.  But how do you choose that size? I’ll show you a rule of thumb that works pretty well.

[read more...]

After some discussions on the OpenSQLCamp 2008 conference we decided to move our development to Launchpad, to be in stream with other MySQL related projects.

We published our patches there https://code.launchpad.net/percona-patches, it is supposed to be main repository for the patches.

We advise to use Launchpad bug system to report bugs and also for feature requests.

We made new binaries for MySQL 5.0.67 build 7 which include patches we recently announced.

The -percona release includes:

and -percona-highperf release additionaly includes

You can download RPMs for RedHat / CentOS 4.x and 5.x for x86_64, binaries, sources and patches there