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 …

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.

In most cases, when people give you a formula for choosing a configuration setting, you should look at it with skepticism.  But in this case you can calculate a reasonable value, believe it or not.  Run these queries at your server's peak usage time:

…

Do you know that there are two limits about dirty (modified but not flushed to disk) blocks of InnoDB buffer pool? One is the limit of "amount". The other is the limit of "age".

-- limit of "amount" --

As you know, buffer pool of InnoDB works as write-back cache of its datafiles. If the buffer pool is filled by dirty blocks, InnoDB cannot allocate new blocks without flushing the dirty blocks and the performance would get worse. This is the limit of dirty block "amount". We can avoid this limit by setting 'innodb_max_dirty_pages_pct' smaller or setting the larger buffer pool size. We might be never at a loss about the limit.

The another limit we should understand is limit of dirty block "age".

-- limit of "age" --

As you know again, because InnoDB write the modifies of datafile to transaction log file synchronously, InnoDB is allowed to treat its buffer pool as write-back …

We have written before about the importance of using numeric types as keys, but maybe you've inherited a schema that you can't change or have chosen string types as keys for a specific reason. Either way, the character sets used on joined columns can have a significant impact on the performance of your queries.

Take the following example, using the InnoDB storage engine:

PLAIN TEXT SQL:

1. CREATE TABLE `t1` (
2. `char_id` char(6) NOT NULL,
3. `v` varchar(128) NOT NULL,
4. PRIMARY KEY (`char_id`)
5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
6.  
7. CREATE TABLE `t2` (
8. `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
…

The unsung heroes of InnoDB are the logfiles. They are what makes InnoDB automatic crash recovery possible.

Database administrators of other DBMS may be familiar with the concept of a “redo” log. When data is changed, affected data pages are changed in the innodb_buffer_pool. Then, the change is written to the redo log, which in MySQL is the InnoDB logfile (ib_logfile0 and ib_logfile1). The pages are marked as “dirty”, and eventually get flushed and written to disk.

If MySQL crashes, there may be data that is changed that has not been written to disk. Those data pages were marked as “dirty” in the innodb_buffer_pool, but after a MySQL crash the innodb_buffer_pool no longer exists. However, they were written to the redo log. On crash recovery, MySQL can read the redo log (InnoDB log files) and apply any changes that were not written to disk.

That is the basic functionality of the InnoDB log files. Given this, …

When optimizing queries for customers, the first thing I do with a slow query is figure out what it's trying to do. You can't fully optimize a query unless you know how to consider alternative ways to write it, and you can't do that unless you know what the query "means." I frequently run into a situation where I'm forced to stop and ask the developers what they were trying to do with COUNT(). This is database-agnostic, not related to MySQL.

The problem is when the COUNT() contains a column name, like this:

PLAIN TEXT SQL:

1. SELECT count(col1) FROM TABLE;

If you know your SQL well, you know COUNT() has two meanings. 1) count the number of rows 2) count the number of values. Sometimes, but not always, these are the same thing. COUNT(*) always counts the number of rows in the result. If …

We are going to show the effects of the new patches applied to Percona HighPerf release. As you see from the following graphs, there is significant difference to normal version when the data bigger than buffer pool (right graph shows CPU usage)

The workload emulates TPC-C and has a same characteristic to DBT-2 (it is not DBT-2, but custom scripts, we will publish them eventually). There are no delays between transactions (no thinking time, no keying time), it uses MySQL C API and the server side prepared statement.

The server has 8core CPU and RAID storage (RAID10 / 6 disks). The data population is along to the scale factor 40WH (:=~4GB). It is enough bigger than the data cache of the storage.

main common settings

innodb_buffer_pool_size = 2048M
innodb_thread_concurrency = 0 …

In my 14 years in development I learned that outside of poor schema design, nothing drains the performance of an application more than poorly performing SQL code. Even code that ran well on day one of production would sometimes come back to bite at the worst possible times. Even worse, as a DBA I was consistently asked to bail out a development team that was either tuning their code before the rush to production or that was trying to finger code that had fallen victim to a dropped or changed index. Never fun.

As a Product Manager with MySQL I have learned from meeting with friends/customers that this experience hasn't really changed much since I left the field. I hear things like:

- MySQL is not well instrumented for tracking code level performance metrics
- Logs are OK, but not centralized and too low-level for easy navigation
- We need help identifying "good code gone bad" and "bad code gone worse" …

Revision: 8 - Last Update: September 03 2008

This is the first part of a series of short articles with a how-to approach about MySQL® Performance Monitoring, Tuning & Auditing. We will see the question from a GUI prospective. In particular we will describe which monitoring-oriented features HoneyMonitor, a GUI for MySQL® currently in alpha development, implements.

I will explain how HoneyMonitor let you

1. install an audit database on your server, without the need of using 3th Party Agents nor using remote repository databases
2. enable the auditing and start monitoring your server
3. tuning your server changing a few suggested list of variables to get better performance.

We will use only the 5.1.x series of the Server as we use some Scheduled Events and the Event Scheduler has been added only in the 5.1 branch. In particular we will use MySQL® 5.1.24-rc. We will also do some tests …

At the 2008 MySQL Conference and Expo, The Pythian Group gave away EXPLAIN cheatsheets. They were very nice, printed in full color and laminated to ensure you can spill your coffee* on it and it will survive.

For those not at the conference, or those that want to make more, the file is downloadable as a 136Kb PDF at explain-diagram.pdf

* or tea, for those of us in the civilized world.