Drupal sites extremely slow due to cache SELECT-queries I have multiple drupal 7 sites running on my shared hosting. Suddenly almost all of my drupal sites began running very slow. After some searching I found that I had 132k slow queries of the 140M queries executes.

So I turned on de debug query option in the Devel module and got this:

Executed 230 queries in 18868.24 ms. Queries exceeding 5 ms are highlighted. 
Page execution time was 21632.34 ms. 
Memory used at: devel_boot()=2.61 MB, devel_shutdown()=47.54 MB, PHP peak=48 MB.


After looking at the SELECT-query list I noticed that there are a lot of cache-queries that run slowly:

SELECT cid, data, created, expire, serialized FROM cache_views WHERE cid IN (:cids_0)


Not only for 'cache_views' but also other cache-tables:


cache
cache_field
cache_filter
cache_menu
cache_bootstrap
...


In PHPMYADMIN I also notice that a lot of queries have been removed from the cache:

Qcache_lowmem_prunes: 35.2M (of 143M queries)


I already checked if there were modules enabled in the DB but deleted on the drupal site.

I hope this is enough info to get some help.
Sounds like MySQL needs to be tuned. I would start with the InnoDB Buffer Pool Size. This answer has info on innodb_buffer_pool_size: Do Drupal websites have bad performance or are heavy?

If the innodb buffer pool is a reasonable size and If MySQL is in ball park of doing 3k+ queries per second and your running MySQL version 5.1 or lower you might be affected by the query cache bug. At work I ended up disabling the MySQL query cache after reading this bug and verifying that the version of MySQL we are running (5.1) does not have this fix http://bugs.mysql.com/bug.php?id=43758 (available in 5.5). The bug is that a lot of queries get stuck in the “freeing items” state http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html#id770305 (The thread has executed a command. Some freeing of items done during this state involves the query cache. This state is usually followed by cleaning up). I issued this command SET GLOBAL query_cache_size = 0; and saw an instant improvement in query times.