MySQL Performance Blog

As you might noticed there are no recent MySQL Community versions available for download from MySQL Download Area This applies both to binaries (which is expected with new polices) but also to the source files which were promised to be available.

So what is if you would like to use recent MySQL code while staying with community version ? I chatted with Monty on this topic today.

Download sources from MySQL FTP Site I have no idea why this location is not advertised on download pages but it really has sources for all recent releases, both for Unix and Windows.

Use MySQL Supplied by Distribution Vendor. Some vendors already offering MySQL 5.0.30 - Gentoo, Ubuntu and Debian at the time of this writing. Fedora might also get update soon.

Use Bitkeeper Tree If you would like absolutely recent version this is a way to go. The address for MySQL 5.0 is
bk://mysql.bkbits.net/mysql-5.0 (HowTo Docs) Note version you find where is untested and might be broken so be careful. In theory you can also pull all previous releases by their tags but in practice it requires licensed BitKeeper version which few people have access to.

What is about free MySQL Enterprise Version ? Well at the time of this writing as far as I know source tree for Enterprise and Community version is the same so there is no difference. Once split will really happen we’ll see if it would be available.

What is about Binaries ? As I mentioned some Linux distributions already include up to date MySQL versions, this obviously means binaries are available as well. If you’re using other Linux distribution, other Unix or Windows operation system you should ether build one yourself, find binaries from truster third party or hire someone, (for example us) to build ones for you.

MySQL has two ways to find tune execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly into the query to change how query is executed for example
SELECT STRAIGHT_JOIN * FROM A FORCE INDEX(A) JOIN B

The other part is session variable. If you know query is going to require large sort you can do SET sort_buffer_size=50000000 before executing query and SET sort_buffer_size=DEFAULT after executing the query.

I noticed in production hints are used much more frequently than setting session variables for given query execution even though it also can be quite helpful and I believe the problem is it is more complicated. It also requires more round trips to the server but it is not that critical as it is complicated queries which normally need special values.

Especially if you’re patching third party application it is much easier to simply change the query (especially if it is not created dynamically some complex way) - adding extra MySQL calls with possibly their own error control is more complicated. Many people even dislike doing it in their own code.

The great solution I think would be to allow per session variables to be changed only for single query execution and returned to their old values after query is executed, so I could do something like:

SELECT SQL_SORT_BUFFER_SIZE=50000000 NAME FROM LARGE_TABLE ORDER BY NAME DESC LIMIT 10

This would also make it much easier to track - settings would show up in the query log (ie slow queries log) and SHOW PROCESSLIST so once could take it as a single query and profile rather than figuring our what custom session based settings it could have.

This topic may look boring and obvious but it is extremely important for MySQL Performance Optimization. In fact I probably have to touch it in every second MySQL Consulting work or even more frequently.

IO Bound workload is quite different from CPU bound one, which happens when your working set (normally only fraction of your database) fits in memory. What is fast when data is in memory can be extremely slow if it does not. For example if you have the query which analyzes 10000 rows it often would take fraction of the second with fully in memory workload, however if you would need to go to the disk, lets say even only in 10% of the cases and so perform 1000 possibly random reads you will have query taking at least 5-10 second, or more under the load which is already way more than you should target for web applications.

So designing your application think what kind of application are you designing ? Can you make it CPU bound/In memory ? If yes the whole class of the problems may not exist and you might be able to use solutions which are easier to implement. But beware if you design your application as CPU bound and when it scales so much you can’t afford to get enough memory any more, you might have very sharp performance drop and complex changes may be required to get your application back to speed.

The cases when going from CPU bound to IO bound hits the most is there a lot of rows are analyzed - count queries, group by, order by without indexes, search queries etc. Basically as soon as you have more than 100 rows analyzed by the query and these rows are “random access” to large tables (so chances they would require physical IO are high) I would highlight this query as possibly having performance problems.

Also do not look just at “typical” case - in many cases worst 5% would be responsible for majority of performance problems.

Let me illustrate it on simple case. Assuming you have Web application which has some form of messaging between users. You may want to display to the user number of unread messages as well as use total number of messages in the mailbox at least to draw “pager”. Easy solution is to do select count(*) from messages where user_id=134 or use SQL_CALC_FOUND_ROWS flag for your main select query. If you’re having CPU bound application it is as much as you might need to go especially if you have some form of caching on top of that. For IO bound application you however will run in trouble pretty soon even with 1000 messages in mailbox may start slowing things down.

Now you may get few percent of very active users who will get both extreme number of messages in their mailbox and will spend a lot of time on the site - generating much more load than average user, so their contribution to the load my be significant plus you do not want to piss of your most loyal users with slow page load times.

So for IO bound applications you will need to add counts for all messages, read messages etc, make sure they are updated (ie use triggers) and make sure all selects use index for ORDER BY .. LIMIT.

For IO bound applications Clustering (data locality) also becomes very important - if Innodb tables are used having simply auto_increment id on messages would likely be much slower than (user_id,sub_id) combined primary key as this one will cluster messages for same user_id and normally allow to fetch them all using only few physical IOs.

You may argue you still will have the problems with such design in CPU bound case - yes you will but it will happen with 100.000 messages not 100 messages as in IO bound case which is large enough for many application classes not to think about it.

Today I was working with application which uses Innodb and foreign keys and got into locking problems possibly due to foreign keys, so I did a little investigation on that matter.

Interesting enough it looks like most people do not think about foreign keys overhead in terms of locking. The overhead about checking referenced table is usually considered but not locking which also might be important.

So lets talk how locks seems to work with foreign keys in Innodb. I’ve only done a quick check so could be missing some details.

All Innodb Foreign Key related operations happen on data modification. So for example if you do SELECT FOR UPDATE on CHILD table it will not lock associated rows in PARENT table and so you can run into lock waits if you do updates to CHILD table which change parents because this is when row in PARENT table will be locked to perform update.

The checks are performed “originating” from the table which you modify - if you update row in CHILD table and parent information is changed lookup will be performed in PARENT table and row will be locked. Same applies to PARENT table - if you will try to delete row in PARENT table lockup in CHILD table will be performed with row lock performed.

Innodb is smart enough to detect which updates are affecting foreign key relationships. So if you will update any column which is not part of FOREIGN KEY constraint - no foreign key originated locks will happen. However if you update PRIMARY KEY value in the child table the lock will happen as it is internally implemented as special form of DELETE+INSERT. But this is something you should not be doing anyway.

So how can you suffer from locks originating from Foreign Keys ?

Imagine you have users table and messages table. If you would use bulk inserts (for performance) to messages table you will have significant number of user ids locked in the user table which can stall queries working with users table or other tables which have foreign keys to this table. The statements which even work with different tables and traditionally would not affect each other with locks now can because of foreign keys.

So implementing foreign keys do not forget to think about locking overhead among other things.

Linux and Unixes have excellent metric of system load called “loadavg”. In fact load average is is 3 numbers which correspond to “load average” calculated for one five and 15 minutes. It is computed as exponential moving average so most recent load have more weight in the value than old one.

What does Load Average corresponds to ? At least on Linux it is number of processes which are in “running” state or in “uninterruptable sleep” state which typically corresponds to disk IO. You can also map LoadAvg to VMSTAT output - it is something like moving average of sum of “r” and “b” columns from VMSTAT.

Obviously minimum value for LoadAvg is zero which corresponds to completely idle system, and there is no maximum

First thing to understand about LoadAvg it does not really tell you if it is CPU bound load or IO bound load. For example if you have LoadAvg of 10 it may mean there are 10 processes/threads actively consuming CPU or it could be same 10 processes waiting on disk IO and you can see CPU utilization being close to zero.

Second thing is to understand LoadAvg values are relative to your system size. If you have single CPU and 1 disk loadavg of 2 can be considered significant, while if you have 16 CPUs and 2 disks Load of 4 can be light if it is CPU bound - because the system can execute much more CPU bound tasks in parallel or High if it is Disk Bound LoadAvg.

Low Load Average does not mean there are no performance problems, for example if you run single batch job on the server with MySQL, Load Average is likely to be close to 1 even if there are a lot of CPUs and Disks - system may be quite idle and performance still poor because application is not parallel enough. Similar situations can happen if there is a lot of network IO involved or if there are a lot of locks (table/row level locks) or other limiting factors such as innodb_thread_concurrency.

The most interesting question I think is how LoadAvg represent box load in terms of how much load it can handle before it becomes to slow down or being completely unable to handle the load, and it is tricky question. Both for CPUs and for Disk there are two stages request can be. It can be ether currently executing or queued for further execution. The time which is needed to complete request is sum of time it was really executed and the time it was spent in queue. As the system is loaded response time starts to increase mainly because of time requests spend waiting in various queues and waiting on locks, the time of true execution may well remain constant. This is a bit of simplifications as there are number of other effects coming in play but good enough for sake of explanation.

What does it mean from LoadAvg standpoint ? You need to understand where parallel execution continues and where waiting in the queue starts. If you have fully CPU bound workload which is rather parallel (ie many queries will run at once) and you have 4CPUs until your LoadAvg is below 4 you have low time spend waiting for CPUs to be free to do the work. There is some wait but not much. So if you have LoadAvg of 1 and your workload scales linearly with number of connections and CPUs (ie there are no row waits involved) you can assume box can handle up to 3-4 times more load before response time starts to suffer.

If however the LoadAvg is 4 already it may take rather insignificant increase to take it up to 8 and you will see some delays due to queuing. If there are 4CPUs (Cores) and loadavg is 16 for CPU bound workload it often means requests should take 4 times more to complete than they would on idle box due to waiting in the queue.

Same true for pure Disk IO bound workload with small difference of disk not being replaceable (if you’re waiting on one drive you can’t use another drive instead), and the fact disks can optimize multiple outstanding requests a bit better compared to requests coming one after another.

For mixed workload, which is what we usually see in practice you have to do some assumptions guesses or further analyzes if you want good estimates. Ie you may want to check mpstat, vmstat and iostat to see where load comes from. But the general rule remains the same - until you’re able to explore parallel abilities of the box it will perform well as soon as you need to do a lot of queuing performance starts to suffer.

Let us clarify last point - how much more load the box can handle before it overloads, loadavg skyrockets and it becomes as good as down. First for many applications request inflow is not constant - ie web site gets poor response time and users do not spend so much time on it any more so load drops. This is however temporary relive only as there are stubborn users which would not go away even with slow responding site until their browsers timeout, which is as good as site is down. There are too many variables to come with exact numbers but generally as soon as you have long queuing started it may take just 10-20% extra load to overload system, so it is better to keep loadavg low - below number of CPUs and/or disks you have.

I must note - LoadAvg is not perfect tool for the task. It is just almost always available unlike other metrics. It is best to have profiling information so you can see as response time for your requests starts to grow. As soon as it becomes to grow with no good reason I would start to worry whatever LoadAvg shows.

P.S I acknowledge some of explanations are simplifying things for explanation purposes.

I guess note number one about MyISAM to Innodb migration is warning what Innodb is very slow in COUNT(*) queries. The part which I often however see omitted is fact it only applies to COUNT(*) queries without WHERE clause.

So if you have query like SELECT COUNT(*) FROM USER It will be much faster for MyISAM (MEMORY and some others) tables because they would simply read number of rows in the table from stored value. Innodb will however need to perform full table scan or full index scan because it does not have such counter, it also can’t be solved by simple singe counter for Innodb tables as different transactions may see different number of rows in the table.

If you have query like SELECT COUNT(*) FROM IMAGE WHERE USER_ID=5 this query will be executed same way both for MyISAM and Innodb tables by performing index rage scan. This can be faster or slower both for MyISAM and Innodb depending on various conditions.

In real applications there are much more queries of second type rather than first type so it is typically not as bad problem as it may look. Most typically count of rows is needed by admin tools which may show it in table statistics, it may also be used in application stats to show something like “We have 123.345 users which have uploaded 1.344.656 images” but these are normally easy to remove.

So remember Innodb is not slow for ALL COUNT(*) queries but only for very specific case of COUNT(*) query without WHERE clause. It does not mean I would not like to see it fixed though, it is pretty annoying.