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.
Best 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!
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.
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.