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.

Improve this question
5
  • 1
    Are you running cron normally?
    – David Thomas
    Jan 14, 2013 at 8:56
  • Yes, my cron runs normal.
    – Mr.B
    Jan 14, 2013 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, 2013 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, 2013 at 9:58
  • 2
    you want to use memcache or redis to store your cached queries in server memory.
    – uwe
    Jan 15, 2013 at 15:28

2 Answers 2

Reset to default
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.

Improve this answer
2
  • 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, 2013 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, 2013 at 17:36
0

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

Improve this answer
2
  • 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, 2013 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, 2013 at 12:54

Your Answer

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

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