3

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.

  • 1
    Are you running cron normally? – David Thomas Jan 14 '13 at 8:56
  • Yes, my cron runs normal. – Mr.B Jan 14 '13 at 9:24
  • Normally slow cache queries are on write, not on read, such as INSERT, instead of SELECT. Perhaps your web host can shed some light on the issue. – David Thomas Jan 14 '13 at 9:50
  • Already asked them, they told me that there are no problems with the server and that I have to investigate why there are a lot of slow queries... :s – Mr.B Jan 14 '13 at 9:58
  • 2
    you want to use memcache or redis to store your cached queries in server memory. – uwe Jan 15 '13 at 15:28
1

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.

  • It's shared hosting so I can't tune MYSQL. The problem above went away after deleting MySQL DB's that I don't use anymore and enabling the boost module on Drupal on one site. – Mr.B Jan 16 '13 at 8:48
  • Read the comment on the linked answer, it's the same issue that you have. "I would see if you can increase that value in MySQL. If you can not up this value, change all tables except for semaphore, node_access, sessions, queue, batch, and history to MyISAM." In your case I would look at changing the cache tables to MyISAM. It should help with the read performance if you can't tune MySQL. – mikeytown2 Jan 16 '13 at 17:36
0

The problem above went away after deleting MySQL DB's that I don't use anymore.

  • I have only one DB on the server but I am facing the same problem.. did you do anything to solve it? – Tareq Assaf Jun 17 '13 at 9:29
  • I also tried to repair my DB, but I don't think did had anything to do with it. – Mr.B Jul 17 '13 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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