MySQL Performance Blog

I’m leaving MySQL starting 1st of August. This is surely not news for my collegues and friends who knew this is comming.

We’re starting our own company which will provide MySQL Consulting services. We’ll focus on MySQL Performance Optimization but will be able to help pretty much with all MySQL related issues. We can also act as your remote MySQL DBA or help you scaling your LAMP Application.

In addition to that I’ll get back on Entrepreneurial track and work on few Web Projects. Some of you know before joining MySQL I was co-founder and CTO of well known Web Statistics company in Russia and I loved it a lot. I will keep you posted as soon as I have something to show.

I will continue being part of MySQL Community, hopefully getting even more time for this. I will update this blog, hang around on forum, speak on conference as well as work on couple of MySQL related Open Source projects.

If you have any referrals or leads for my new Consulting Venture let me know. If you worked with me personally good testimonials would also be great. I’ve got to do some Sales and Marketing now

MySQL is known for its stability but as any other application it has bugs so it may crash sometime. Also operation system may be flawed, hardware has problems or simply power can go down which all mean similar things - MySQL Shutdown is unexpected and there could be various inconsistences. And this is not only problem as we’ll see.

MySQL has angel process mysqld_safe which will restart MySQL Server in most cases. It is great, unless you have run into some bug which causes it to crash again - such crashes qucikly following one another are kind of worse because they explore many less tested code paths in MySQL and so problem potential is larger.

So lets look at the problem which happen during the crash which might need to take care of or which may seriously affect MySQL Performance.

MyISAM Corruption - If you’re writting to MyISAM tables there is very large chance of them becoming corrupted during the crash. Note corruption may be hidden and do not expose itself instantly - you may notice wrong query results days after crash. Sometimes corrupted tables may be reason for further crashes or hangs, and corruption may spread itself further in the table. You probably do not want any of these so it is very good idea to run MySQL with myisam_recover option which will make sure all improperly closed MyISAM tables are checked first time it is accessed. This option is however rather painful to use with web applications - users may issue different queries which may trigger check/repair running for many tables at onces, which typically make system extremely slow and also can use up all allowed connections or run out of memory ( myisam_sort_buffer_size is normally set pretty lage). If this becomes the problem I use tiny script which moves out all MyISAM tables out of MySQL database directory, checks them with MyISAMchk and moves them back to running server. This looks scary but it works great - until table is checked and ready application gets error rather than stalling forever which allows application to become partially functional as soon as possible. This hack is needed only in some cases - in most cases using Innodb for tables which you need to be recovered fast is better solution.

Innodb Recovery - Unless you have some hardware problems (99%) or found new Innodb bug (1%) Innodb recovery should be automatic and bring your database to consistent state. Depending on innodb_flush_lot_at_trx_commit setting you may lose few last committed transactions but it is it. It is Performance of this process which may cause the problems. As I already wrote innodb_log_file_size and innodb_buffer_pool_size affect recovery time significantly as well as your workload. I should also mention if you have innodb_file_per_table=1 your recovery speed will depend on number of Innodb tables you have, as well as many other operations, so beware.

Binary log corruption - Binary log may become corrupted and out of sync with database content. This will sometimes break replication but if you’re just planning on using binary log for point in time recovery it can go unnoticed. sync_binlog Is helping by syncing binary log, but at performance penalty. If using Innodb you also might with to use innodb-safe-binlog option in MySQL 4.1 so your Innodb log and binary log are synchronized. In MySQL 5.0 XA is taking care of this synchronization.

.frm Corruption - Few people know MySQL is not really ACID even with Innodb tables, at least not for DDL statements.
There is a chance of failing for example during CREATE statement with table created in Innodb dictionary but .frm not created or not completely written. Partially written .frm files or .frm being unsync with internal Innodb dictionary may cause MySQL to fail with wierd error messages. In MySQL 4.1 sync_frm option was added which reduces this problem as time window when it can happen is much less. Still if failure happens just during writting .frm file nasty things may happen, not to mention such potentially multiple operation DDL statements as RENAME TABLE - these are most vulnerable.

master.info corruption - If slave happens to crash you can also have relay logs corruption and master.info being corrupted. Not to mention MyISAM tables can contain partially completed statements as well as some of updates totally lost. The safe approach it to reclone the slaves if they crash or you can take the risks and try to continue. Sometimes you might be able to manually find appropriate position even if master.info file is out of sync but I would not be basing my failure handling scenarios.

Cold Start - If you restart MySQL server its caches (key_buffer, innodb_buffer_pool, query_cache,table_cache) are cleaned, so may be OS caches. This may reduce performance dramatically. So if you’re bringing server back after crash you might want to populate caches. For MyISAM key_cache this can be done by using LOAD INDEX INTO CACHE statement, for other storage engines it can be done by issuing large index scan queries. Full table scan queries allow to preload table data ether in storage engine caches or in OS cache. You can save these into .sql file and use –init-file to make sure it is run on startup. The other approach is to prime server with real servers (ie clone queries from other slave) before putting traffic to it.
In case application is not highly available so there is only one server you might with to start serving only some users initially (returning error to others) and gradually increase the load as server warms up. This may sound strange but makes a lot of sense as not only waiting for pages which never load is more frustrating for users than getting honest “try again later” message, but also - warmup takes longer time on extreme load.

Innodb statistics - Unlike MyISAM Innodb does not store index cardinality in tables, instead it computes them on first table access after startup. This may take significant time if you have very large number of tables (Some users have hundreds of thousands of tables per database host). This one is pretty much part of cold start problems but I wanted to point out it separately. To warmup this data you might run select 1 from _table_ limit 1 for each table or any other statement - it is table open which is important.

There are other problems which you may experience related to MySQL Crash Recovery - Restoring data from backup, corrupted Innodb tablespace recovery etc but I should write about them some other time.

I’m again returning to InnoDB scalability and related bug #15815 as it hurts many users and customers using multi-cpu servers.
Short intro into problem:
On 4-CPU box 1 thread executes full-table scan select query for 8 sec,
but with 4 threads - each thread executes query for 240 sec.
It is very strange as threads use only SELECT queries and ideally there should be no
any problem in concurrent enviroment, especially for CPU-bound workload.

I did the profiling which show the problem is with “buffer pool” mutex, which protects innodb_buffer_pool.
In details, for each scanned row InnoDB calls block_get / block_release functions
which aquire/release the block related to the current row. And the problem functions
block_get / block_release use mutex_lock(buffer_pool_mutex) / mutex_unlock(buffer_pool_mutex)
calls. So global mutex is accessed for each scanned rows, what in multi-CPU/multi-threading
enviroment results in “mutex ping-pong” problem.

Looking in source code of block_get / block_release function I don’t see obviosly reasons
to use global lock which can not be weaken to block level. I tried to replace buffer_pool_mutex in
these place to block_mutex and I got impressive results: now
each of 4 threads executes query for 11 sec.
So performance is increased by 240/11 ~= 21 times, and if before we had negative scalability, currently
scalabilty factor is 2.9 (the result with 4 threads = 2.9 * result with 1 thread).

The current patch can not be considered as stable as it touches many InnoDB subsystems,
but it looks like right direction to solve problem.

MySQL has a great feature called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of things you need to know.

First let me clarify what MySQL Query Cache is - I’ve seen number of people being confused, thinking MySQL Query Cache is the same as Oracle Query Cache - meaning cache where execution plans are cached. MySQL Query Cache is not. It does not cache the plan but full result sets. This means it is much more efficient as query which required processing millions of rows now can be instantly summoned from query cache. It also means query has to be exactly the same and deterministic, so hit rate would generally be less. In any case it is completely different.

Query cache is great for certain applications, typically simple applications deployed on limited scale or applications dealing with small data sets. For example I’m using Query Cache on server which runs this blog. Updates are rather rare so per-table granularity is not the problem, I have only one server and number of queries is small so cache duplication is not the problem. Finally I do not want to hack wordpress to support eaccelerator cache or memcached. Well honestly speaking if performance would be problem I should have started with full page caching rather than MySQL level caching but it is other story.

Lets talk a bit about features and limitations of Query Cache:

Transparent Caching - Caching is fully transparent to the application, and what is very important it does not change semantics of the queries - you always get actual query results. Really there are some chevats - if you’re not using query_cache_wlock_invalidate=ON locking table for write would not invalidate query cache so you can get results even
if table is locked and is being prepared to be updated. So if you’re using query cache in default configuration you can’t assume locking table for write will mean no one will be able to read it - results still can come from query cache unless you enable query_cache_wlock_invalidate=ON.

Caching full queries only - Meaning it does not work for subselects, inline views, parts of the UNION. This is also common missunderstanding.

Works on packet level - This is one of the reason for previous item. Query cache catches network packets as they sent from client to the server, which means it can serve responses very fast doing no extra conversion or processing.

Works before parsing - One more reason for high performance is Query Cache performs query lookup in the cache before query parsing, so if result is served from query cache, query parsing step is saved.

Queries must be absolutely the same As no parsing is done before lookup queries are not normalized (would require parsing) before cache lookup, so they have to match byte by byte for cache hit to happen. This means if you would place dynamic comments in the query, have extra space or use different case - these would be different queries for query cache.

Only SELECT queries are cached SHOW commands or stored procedure calls are not, even if stored procedure would simply preform select to retrieve data from table.

Avoid comment (and space) in the start of the query - Query Cache does simple optimization to check if query can be cached. As I mentioned only SELECT queries are cached - so it looks at first letter of the query and if it is “S” it proceeds with query lookup in cache if not - skips it.

Does not support prepared statements and cursors Query Cache works with query text and want full result set at once. In prepared statements there is query with placeholders and additional parameter values which would need extra care - it is not implemented. Cursors get data in chunks so it is even harder to implement.

Might not work with transactions - Different transactions may see different states of the database, depending on the updates they have performed and even depending on snapshot they are working on. If you’re using statements outside of transaction you have best chance for them to be cached.

Query must be deterministic - Query might provide same result no matter how many times it is run, if data remains the same. So if query works with current data, uses non-deterministic functions such as UUID(), RAND(), CONNECTION_ID() etc it will not be cached.

Table level granularity in invalidation - If table gets modification all queries derived from this table are invalidated at once. Most of them quite likely would not have change their result set but MySQL has no way to identify which one of them would so it gets rid of all of them. This is one of the main features which limits query cache effectiveness - if you have high write application such as forums, query cache efficiency might be pretty low due to this. There is also way to set minimal TTL or anything like it which is allowed by other caching systems. Also note - all queries are removed from cache on table modifications - if there are a lot of queries being cached this might reduce update speed a bit.

Fragmentation over time - Over time Query Cache might get fragmented, which reduces performance. This can be seen as large value of Qcache_free_blocks relatively to Qcache_free_memory. FLUSH QUERY CACHE command can be used for query cache defragmentation but it may block query cache for rather long time for large query caches, which might be unsuitable for online applications.

Limited amount of usable memory - Queries are constantly being invalidated from query cache by table updates, this means number of queries in cache and memory used can’t grow forever even if your have very large amount of different queries being run. Of course in some cases you have tables which are never modified which would flood query cahe but it unusual. So you might want to set query cache to certain value and watch Qcache_free_memory and Qcache_lowmem_prunes - If you’re not getting much of lowmem_prunes and free_memory stays high you can reduce query_cache appropriately. Otherwise you might wish to increase it and see if efficiency increases.

Demand operating mode If you just enable qury cache it will operate in “Cache everything” mode. In certain caches you might want to cache only some of the queries - in this case you can set query_cache_type to “DEMAND” and use only SQL_CACHE hint for queries which you want to have cached - such as SELECT SQL_CACHE col from foo where id=5. If you run in default mode you can also use SQL_NO_CACHE to block caching for certain queries, which you know do not need to be cached.

Counting query cache efficiency There are few ways you can look at query_cache efficiency. First looking at number of your selects - Com_select and see how many of them are cached. Query Cache efficiency would be Qcache_hits/(Com_select+Qcache_hits). As you can see we have to add Qcache_hits to Com_select to get total number of queries as if query cache hit happens Com_select is not incremented. But if you have just 20% Cache hit rate does it mean it is not worth it ? Not really it depends on which queries are cached, as well as overhead query cache provides. One portion of query cache overhead is of course inserts so you can see how much of inserted queries are used: Qcache_hits/Qcache_inserts Other portion of overhead comes from modification statements which you can calculate by (Com_insert+Com_delete+Com_update+Com_replace)/Qcache_hits
. These are some numbers you can play with but it is hard to tell what is good or bad as a lot depends on statement complexity as well as how much work is saved by query cache.

Now lets speak a bit about Query Cache configuration and mainance. MySQL Manual is pretty good on this: Query Cache Query Cache Status Query Cache Configuration

I would just mention couple of points - as protection from one query wiping your all query cache option query_cache_limit was implemented which limits result set which can be stored in query cache. If you need larger queries to be cached you might increase it, if you most important queries are smaller you can decrease it. The other one is Qcache_lowmem_prunes - This one is used to identify if you have enough memory for query cache. Note however due to fragmentation lowmem_prunes can be triggered even if there is some free space, just badly fragmented.

Looking at performance I’ve seen query cache offering about double performance for simple queries with select done by primary key, obviously there is no upper boundary - Very complex queries producing small result set will be offering best speed up.

So when it is good idea to use query cache ?

Third party application - You can’t change how it works with MySQL to add caching but you can enable query cache so it works faster.

Low load applications - If you’re building application which is not designed for extreme load, like many personal application query cache might be all you need. Especially if it is mostly read only scenario.

Why Look for alternatives ?

There are few reasons why Query Cache might be not cache for your application:

It caches queries Application objects might need several queries to compose so it is efficient to cache whole objects rather than individual queries.

No control on invalidation Table granularity is often too bad. With other caches you may implement version based or timeout based invalidation which can offer much better hit ratio for certain application.

It is not that fast Query Cache is fast compared to running the queries but it is still not as fast as specially designed systems such as memcached or local shared memory.

It can’t retrieve multiple objects at the same time You have to query cache object by object which adds latency, there is no way you can request all objects you need to be retrieved at the same time (again memcached has it)

It is not distributed If you have 10 slaves and use query cache on all of them cache content will likely be the same, so you have multiple copies of the same data in cache effectively wasting memory. Distirbuted caching systems can effectively use memory on multiple systems so there is no duplication.

Memcached is probably the most popular distributed caching system and it works great. I should write an article comparing performance of various caching systems some time.

One nice feature added for EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It should be used together with SHOW WARNINGS to get information about how query looks after transformation as well as what other notes optimizer may wish to tell us. It is best seen by example:

As you see after query transformation "*" was expanded to full column list, each column was fully quantified plus MySQL optimizer converted
c="a" and pad=c; to c="a" and pad="a". MySQL optimizer could not however catch id>5 and id>6 can be converted just to id>6.

Lets see another example:

In this case we can see table t1 is accessed using "const" access type, which means it should pre-read value for "t1.k" and place it into the query. This transformation however is not shown by EXTENDED EXPLAIN for some reason, while it would be quite helpful.

Finally lets look at third example:

As you see in this case there is planty of extra information in the message. We can see query is using , it is converted to using . And there is some in being done.

Unfortnuately there is yet not too much does available about what each of these means so you should guess from names or take a look at the sources.

To note about last query - even if it looks like there is a whole big deal of optimization being done query is executed extremely poor, completing in 16 seconds, while if you just run this subquery manually and use IN (1,2,3,...10) it completes in tiny fraction of the second.

Anyway EXPLAIN EXTENDED is very valuable addition to EXPLAIN for MySQL Performance troubleshooting.

Running EXPLAIN for problematic queries is very powerful tool for MySQL Performance optimization. If you've been using this tool a lot you probably noticed it is not always provide adequate information. Here is list of things you may wish to watch out.

EXPLAIN can be wrong - this does not happen very often but it does. EXPLAIN may not be telling you the truth, or full truth about query execution. The reason is - MySQL does not really have the special "plan generating" step, instead plan is generated as a part of query execution, sometimes being dynamic based on data. With EXPLAIN MySQL kind of simulates execution but it obviously does not access the data so does not have access to this dynamic component. If you suspect EXPLAIN is lieing you you can use SHOW STATUS "Handler" statistics to see if number of operations match.

EXPLAIN works for SELECT only This is in works to be fixed in some of next version but it is so as of MySQL 5.0 The workaround is to modify statement to become SELECT with appropriate where clause. This however would not tell you full story, especially now as MySQL has triggers which can make updates very different. Also there is no way to tell you how ALTER table would be executed - will in add index live as it can now do on Cluster ? Is it expected to rebuild index by sort of key_cache as it can for MyISAM.

EXPLAIN may take long time In MySQL 5.0 with addition of greedy join the problem on looking at too many table join combinations in joins with very large tables is elevated, however there are still cases when it may take too long. Most practically important one is if Derived tables (selects in FROM clause) are used. MySQL will execute these subselects on explain to provide plan for top most statement, so if you made an error in select in from clause EXPLAIN might never complete. Solution is to run EXPLAIN for statements in FROM clause separately before looking at main query.

Estimated number of rows may be very inaccurate It is well known this number is aproximate but I guess not everyone knows how much wrong it could be. If number of rows is estimated performing btree dive with certainly structured tree you still can get number of rows off 3-10 times. It gets even worse if cardinality is used. In this case if data distribution is skewed and you statement looks at portion of data with different distribution results can be off by 10-100 times and even more.

Computing rows to be examined is more complicated It is frequent approach to take number of rows estimated from each row in a join and multiply them to see how many rows will be examined in total. This can be rather wrong. One thing to consider is number of rows analyzed for given table might not be the same as passed to the next step - there could be some non-index based filtering applied. The other reason for difference is join_buffer which reduces number of passes MySQL had to perform and so number of row combinations really examined.

LIMIT is not taken into account while estimating number of rows Even if you have LIMIT which restricts how many rows will be examined MySQL will still print full number. Here is example:

This statement obviously will not scan more than 10 rows but EXPLAIN shows it will scan over 1000. This is not the problem if you examine your queries manually but if you have automatic tool which catches possibly complex queries it can give you head ache.

filesort, using temporary are always in the first line So it is impossible to tell at which stage MySQL actually perform the sort or creates temporary table and so how much rows will be stored to temporary table. Sometimes it is enough to perform sorting or use temporary table with partial result set (partial rows) and than compose full rows by reading data from other tables.

No information about using disk for sort or temporary tables MySQL always calls it "filesort" even if it is done in memory. Also it would not tell you if temporary table will be required on disk. In many cases it is impossible to tell as we do not know how much data we'll need to sort or accumulate. This information is not available even if it is well known - for example BLOB fields can't work with in memory temporary table. The other thing - since MySQL 4.1 sorting can be done with storing row data in the sort file, not only key values and row pointers. If this is going to be used in particular sort is not seen in explain.

Limited cost information Yes, number of rows to be scanned is shown but it does not tell much about cost of the operation - full table scan of table in memory may be done at speed of millions of rows per second, while retrieval of large rows from uncached table by random index values (or even range) may be perfoming at some 100 rows/sec.

No info about functions cost Since MySQL 5.0 you may use user functions in your query which may also perform queries themselves. These actions are totally hidden in EXPLAIN which may seriously affect the outcome. The cost of functions is also not used in optimization which may produce suboptimal plans if stored functions are used in queries.

In general in my opinion EXPLAIN needs serious overhaul so it can be used with GUI tool to provide tree like structure of opertions applied to the data, methods used, costs etc. Simple table like output format is good but with growing MySQL Server complexity it is getting impossible to present all relevant information this way, so we need at least an alternative view.

If you want to find more information about understanding EXPLAIN output, MySQL manual EXPLAIN page is good place to start.

We are going to release several patches which are not included in official MySQL releases.
First one is automatically stack trace for x86_64 systems. Currently MySQL resolves stack in crash only for x86 boxes.
You can download patch for 5.0.22 source tree here.
How to use: place stack64.diff into main source dir; execute patch -p1 < stack64.diff.
One important one: flag -fno-omit-frame-pointer must be added to CFLAGS and CXXFLAGS, by default GCC uses
-fomit-frame-pointer on x86_64 systems (on x86 by default -fno-omit-frame-pointer).

Many people asked me to publish a walk through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance.

To start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics, information about transaction processing and all kinds of other things. In MySQL 5 number of Innodb performance counters were exported and now available in SHOW STATUS output. Most of them are same as you previously could find in SHOW INNODB STATUS, there are however few which were not available before.

In SHOW INNODB STATUS many values are per second. If you're planning to use these values make sure they are sampled over decent period of time. In the very start of printout Innodb will print:

[read more...]

Have you seen recent eWeek benchmarks which test OpenSource stacks and compare them to .NET ? Here is what was compared and here are results.

Results make me ask number of questions - why WAMP would perform 6 times better than LAMP ? Why Python would be faster than PHP on Windows but slower on Linux ? Without having such basic questions answered I can't accept results as valid. If you run benchmark and something unexpected pops up drill it down - quite frequently it would be configuration problem, bug or something similar.

Well, this is however not my main concern with this benchmark. Here is to start:

No Specifications - Good benchmark starts with specifications. What exactly bechmarked system has to be doing ? Normally you would try to optimize system to do only what needs to be done and strip everything else and configure it appropriately. For example different CMS could be doing different things internally - some could be doing live full text search indexing on update, page access statistics storage etc.

No configuration published At least I could not find them - how exactly all of the stacks were configured, what hardware was used etc. Full disclosure report is very important component of any serious benchmark. It should be detailed enough for third party to be able to repeat same results.

Missleading benchmark title - Results are called "stack comparison" while I would rather call it benchmark of different content management systems. Why XOOPS is choosen for PHP or Plone for Python ? Different projects have different quality and different performance goals.

Getting back to results - I'm most surprised about WAMP vs LAMP results (and similar results for Python) - Typically CMS would be CPU bound and unless there are configuration differences, such as acceleratior (eaccelerator, APC) installed on one system but not on the other, so results should be close. There could be 20% difference because of different compliers and OS efficiency but it should not be that high.

If someone knows what really was done let me know. I would be quite curious to take a look at least at WAMP vs LAMP results

After playing yesterday a bit with INSERT ... SELECT I decided to check is Innodb locks are relly as efficient in terms of low resource usage as they are advertised.

Lets start with a bit of background - in Innodb row level locks are implemented by having special lock table, located in the buffer pool where small record allocated for each hash and for each row locked on that page bit can be set. This in theory can give overhead as low as few bits per row, so lets see how it looks in practice:

I used the same sample table as yesterday:

Table with rather small row size, containing 1638400 rows.

Now lets lock all rows in this table and see how long does it take:

Looking at SHOW INNODB STATUS we can see:

History list length 5
Total number of lock structs in row lock hash table 6092
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 42306982, ACTIVE 89 sec, process no 10099, OS thread id 1878960
6093 lock struct(s), heap size 503104

So 503104 bytes are used to lock 1638400 rows, making it less than 3 bits per locked row;

Now lets see how much exclusinve locks take:

Interestingly enough this statement already takes 1.5 seconds longer (I repeated runs many time to ensure it is repeatable) so exclusive locks seems to be more expensive to set.

On other hand it still consumes exactly same amount of memory:

History list length 5
Total number of lock structs in row lock hash table 6092
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 42306989, ACTIVE 195 sec, process no 10099, OS thread id 1878960
6093 lock struct(s), heap size 503104
MySQL thread id 7429, query id 24542 localhost root
show innodb status

Note, running statement in consistent read mode which does not set any locks is going to be faster than any of locking modes, however we can see locking overhead is not that large:

So we can lock 1638400 rows in 2.5 seconds on this pretty old box, which means locking speed will be few millions per second on modern systems.

So we have tested how much memory is using locking all the rows - so all rows are locked from the page. In other test we'll lock small random portion of rows (about 1/16) with random distribution to the pages (column j was populated by md5(rand()) values) :