2

I have a problem with extreme slow database queries that make my site loading for more than a minute. I am not able to locate the source of that problem. I'm using devel to control the queries and I found out that mostly the functions saving or updating the cache or session tables are the cause. Here is one example of devel's output:

19622.34 1 _drupal_session_write P A UPDATE users SET access=:db_update_placeholder_0 WHERE (uid = :db_condition_placeholder_0)
10442.86 2 DrupalDatabaseCache::clear P A DELETE FROM cache_bootstrap WHERE (cid = :db_condition_placeholder_0) 

I do not have an idea what I could do to find the cause of that problem.

  • Did you consider using caching modules like Memcache, Boost, APC, and Varnish that will help you alot in solving this slow queries which is a known Drupal 7 issue. See these links: (drupal.org/node/97347) (groups.drupal.org/node/21897) (drupalwatchdog.com/1/1/performance-scalability-drupal-7) – ninjascorner Jun 2 '12 at 5:53
  • Is the problem intermittent or on every page load? – Citricguy Jun 3 '12 at 0:56
  • @Citricguy: The strange thing is, that this problem occurs almost every time but not every time the page is loaded. @ ninjascorner: Thank you for this advice. But I think that this problem must have an other cause. I have two server with the same Drupal installation and almost the same database but this problem occurs only with one of them. I am wandering whether this could have something to do with an encoding issue. I had some strange errors trying to create some nodes that contain UTF8 encoded text before... – user5950 Jun 4 '12 at 8:32
  • Do you have cron configured and running? I had a very similar performance issue that was fixed by configuring cron. Tasks were piling up and causing painfully slow page loads on a very regular basis. – Citricguy Jun 6 '12 at 4:41
  • Tank you guys for your help. Finally I found out the the vServer was screwed up. I had to contact the hoster and know every thing works fine again. Thank to you I learned much about performance! – user5950 Jun 7 '12 at 11:03
1

When a server's I/O performance (especially its disk write performance) reaches its limit, it'll probably show first with cache queries because they're frequent and can contain some quite big chunks of serialized data.

I recommend you take a look at your disk I/O while you're waiting for a page to render, by using iotop for example.

The best way to prevent those I/O bottlenecks is to keep page requests from even reaching Drupal by putting a Varnish cache in front of the webserver. If they need to be answered by Drupal, though, at least take the cache load from the database by using Memcache.

1

Look at this answer I recently gave: Do Drupal websites have bad performance or are heavy? In short you might need to tune MySQL a little bit. What does SHOW VARIABLES LIKE 'innodb_buffer_pool_size' say?

  • MY site is vey sloe, and SHOW VARIABLES LIKE 'innodb_buffer_pool_size' says 1. What does that mean? What can I do? – Dev Sep 21 '16 at 13:13
  • @Drupaler that's not a valid number for that variable dev.mysql.com/doc/refman/5.6/en/… – mikeytown2 Sep 21 '16 at 15:29

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.