17

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.

Improve this question

5 Answers 5

Reset to default
7

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!

Also:

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.

Improve this answer
1
  • Had a similar issue like this, and it was directly related to slow hard disk speeds. Performing disk speed analysis showed that writing and reading to the hard disk was extremely slow. This had a knock on affect to MySQL, and that is why the "Freeing items" states (with the same queries) were showing for a long time in the process list. Killing the other processes that were slowing the hard disk speed fixed the issue.
    – Navigatron
    Commented Aug 2, 2016 at 15:20
5

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.

Improve this answer
2
  • Thank you for zeroing in what 'freeing items' is. Are there any specific documents detailing it, or is this just a Google exercise for me ?
    – RolandoMySQLDBA
    Commented Aug 17, 2011 at 15:11
  • Or browse source code excercise! :)
    – Derek Downey
    Commented Mar 1, 2012 at 15:21
4

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.

Improve this answer
2

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.

Improve this answer
1
  • You need much bigger log files. In fact, the biggest log files allowed with innodb_log_files_in_group default (which is 2)is 2047M. You should shutdown mysql, rm -f /var/lib/ib_logfile[01], make innodb_log_file_size=2047M in /etc/my.cnf and start mysql. Of course, get a bigger disk !!!
    – RolandoMySQLDBA
    Commented Oct 28, 2011 at 2:58
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.