I got a serendipitous call (thanks!) yesterday asking what would be needed to tune[1] a database for better performance. It is a question that I hear often, but I never thought about answering it in public. Here’s a consolidated version of what I explained during our conversation.
How InnoDB handles REDO logging
February 3, 2011 By 19 Comments
Xaprb (Baron) recently blogged about how InnoDB performs a checkpoint , I thought it might be useful to explain another important mechanism that affects both response time and throughput – The transaction log.
MySQL 5.5.8 and Percona Server on Fast Flash card (Virident tachIOn)
December 21, 2010 By 11 Comments
This is to follow up on my previous post and show the results for MySQL 5.5.8 and Percona Server on the fastest hardware I have in our lab:Â a Cisco UCS C250 server with 384GB of RAM, powered by a Virident tachIOn 400GB SLC card. To see different I/O patterns, I used different innodb_buffer_pool_size settings: 13G, [...]
How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
Understanding how well your tables and indexes fit to buffer pool are often very helpful to understand why some queries are IO bound and others not – it may be because the tables and indexes they are accessing are not in cache, for example being washed away by other queries. MySQL Server does not provide [...]
The Doom of Multiple Storage Engines
May 8, 2010 By 22 Comments
One of the big “Selling Points” of MySQL is support for Multiple Storage engines, and from the glance view it is indeed great to provide users with same top level SQL interface allowing them to store their data many different way. As nice as it sounds the in theory this benefit comes at very significant [...]
MongoDB Approach to Availability
April 30, 2010 By 2 Comments
Another thing I find interesting about MongoDB is its approach to Durability, Data Consistency and Availability. It is very relaxed and will not work for some applications but for others it can be usable in current form. Let me explain some concepts and compare it to technologies in MySQL space. First I think MongoDB is [...]
Why you should ignore MySQL’s key cache hit ratio
February 28, 2010 By 28 Comments
I have not caused a fist fight in a while, so it’s time to take off the gloves. I claim that somewhere around of 99% of advice about tuning MySQL’s key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key buffer hit ratio, and [...]
table_cache negative scalability
November 16, 2009 By 23 Comments
Couple of months ago there was a post by FreshBooks on getting great performance improvements by lowering table_cache variable. So I decided to investigate what is really happening here. The “common sense” approach to tuning caches is to get them as large as you can if you have enough resources (such as memory). With MySQL [...]
Tuning for heavy writing workloads
October 14, 2009 By 9 Comments
For the my previous post, there was comment to suggest to test db_STRESS benchmark on XtraDB by Dimitri. And I tested and tuned for the benchmark. I will show you the tunings. It should be also tuning procedure for general heavy writing workloads. At first, <tuning peak performance>. The next, <tuning purge operation> to stabilize [...]
The perils of InnoDB with Debian and startup scripts
January 28, 2009 By 13 Comments
Are you running MySQL on Debian or Ubuntu with InnoDB? You might want to disable /etc/mysql/debian-start. When you run /etc/init.d/mysql start it runs this script, which runs mysqlcheck, which can destroy performance. It can happen on a server with MyISAM tables, if there are enough tables, but it is far worse on InnoDB. There are [...]
