Why do MySQL threads often show “freeing items” status when the query cache is disabled?

Posted on

Question :

When I run SHOW PROCESSLIST, there are often a large number of INSERT/UPDATE threads in the “freeing items” state.

The MySQL manual suggests that at least part of the reason for a thread to be in this state involves the query cache – presumably it would be invalidating cached queries due to the changed data.

However, my query_cache_size is set to 0 which should disable the query cache altogether.

What other reasons would there be to have so many threads in this state?

The relevant tables use the InnoDB storage engine.

Answer :

Check the value of innodb_thread_concurrency.

For my system increasing the value from 8 to 32, per the guidelines in the MySql documentation, caused a discernible decrease in the number of threads concurrently reporting the “freeing items” state. Also, the obesrved average query time dropped by an order of magnitude.

While this made a large difference in overall server performance, it was not the “freeing items” silver bullet. My hardware ecosystem leads me to hypothesize that this state is mostly seen on systems with “slow” disks (2x10k disks raid 1), and is less prevalent on systems with faster storage (12x15k disks raid 10). So, a check of disk performance may also be warranted.

Good Luck!


It is worth noting that the default value of innodb_thread_concurrency is radically different depending on what 5.0 point release is being used.

The default value has changed several times: 8 before MySQL 5.0.8, 20 (infinite) from 5.0.8 through 5.0.18, 0 (infinite) from 5.0.19 to 5.0.20, and 8 (finite) from 5.0.21 on. — source

This means that a seemingly innocuous upgrade from 5.0.20 to 5.0.21 changed the default from infinite to 8, and brought along with it the performance ramifications.

Freeing items is a query execution stage where temporary structures, buffers, etc are deallocated. Some query cache work is done in this stage, but that isn’t the only thing that happens there. I would suggest using SHOW PROFILES to see how long this stage is taking relative to other stages, and if the time consumed ends up being a concern, troubleshooting with tools such as poor man’s profiler and oprofile.

There was a bug report about this issue concerning “freeing items” and the query cache. Although the bug is closed, there was no mention of innodb_thread_concurrency.

Conincidently, I spoke with Ronald Bradford at Percona Live NYC back in May. I told him of a situation where I tweeked innodb_thread_concurrency because MySQL 5.5’s multiple InnoDB Buffer Pool produced a tons of thread locking and I suspected that cached data I need most likely had spread amongst the multiple buffer pools.

He plainly told me that I should never set values against innodb_thread_concurrency. Let it always be the default value, which is now zero(0). By doing so, you let the InnoDB storage decide how many innodb_concurrency_tickets to generate on its own. This is what infinite concurrency does.

‘Freeing items’ most likely occurs more often when we impose limits on innodb_thread_concurrency. That should always be zero(0). I would take a risk and raise innodb_concurrency_tickets and see if it helps or not.

We had a problem with exactly the same symptoms. It turned out that the disk with the InnoDB log files has filled up. Worth checking.

Another hint: Might be innodb fsync(). Try setting

innodb_flush_log_at_trx_commit = 2

In my.cnf

The innodb logfile is then flushed to disk every 1-2 seconds, instead ob at every commit. Slight penalty to data integrity, great gain in speed.

Leave a Reply

Your email address will not be published. Required fields are marked *