0

I have been having a few problems with one of my clients site. Its a big site, theres a few thousand nodes, 10k users, and it holds large images galleries with 100+ images that are 500kb each.

At its slowest, the average page load is 45 seconds!! And the worst i when you are logged in.

After enabling devel query logs, this one stands out as the worst...

3923.69 ms 1 node_tag_new P A INSERT INTO history (uid, nid, timestamp) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2)

Thats the first page load, the second is much much worse....

65594.921 ms node_tag_new P A UPDATE history SET timestamp=:db_update_placeholder_0 WHERE ( (uid = :db_condition_placeholder_0) AND (nid = :db_condition_placeholder_1) )

What is the history table? Any idea why it is taking so long to run the query?

Improve this question
4
  • Have you optimized your database instance for you the catalog and traffic you are getting? Most stock configs, are too generic for a production server, and rarely optimal for anything. That is your starting point; w/o that this question is borderline Too Broad because there are so may possibilities as to what can be wrong.
    – mpdonadio ♦
    Commented May 11, 2016 at 16:38
  • My answer is based on the assumption this has happened suddenly. Or has it slowly became like this as the site grew bigger?
    – Neograph734
    Commented May 11, 2016 at 16:40
  • Just restarted apache and mysql, and its doing the same thing. The site in question is www.ultrabooth.co.uk
    – Collins
    Commented May 11, 2016 at 16:46
  • Copied to a dev server, deleted all users, deleted loads of nodes so there are around 500 left, and the site is still very slow. I also disabled the theme, and started to diable modules. nothing helping yet. Also spoke to the host, and they dont report any problems from their end
    – Collins
    Commented May 12, 2016 at 22:26

1 Answer 1

Reset to default
2

What is the history table?

This is a table for keeping track of what user has read what node. (To show a new/updated message next to it.)

Any idea why its taking so long to run the query?

This could be anything, and might be at the server level (not related to Drupal).

First, check if SQL is slow outside Drupal (eg. PHPmyadmin). Run the same query, see what happens.

If SQL is still slow and you have ssh access to the server, try to run some disk speed checks using sudo hdparm -Tt /dev/sda and dd if=/dev/zero of=/tmp/output bs=8k count=10k; rm -f /tmp/output (assuming Linux). I had a slow site recently that was caused by a malfunctioning hard drive.

Improve this answer
5
  • Beat me to the answer. But I would like to add. With several thousand nodes (say 2000) and 10000 users that up to 20 Million entries in that table. That's kind of big. Consider partitioning the table to increase performance. Not entirely sure how to do that with Drupal but I'm confident it's supported.
    – danielson317
    Commented May 11, 2016 at 16:40
  • Also if you don't care about the data in the table, User last read this, new node for this user, and other such features, You can write a cron to only keep the last 10 thousand entries Similar to what the system log does. Warning! you will be deleting real and valuable data. So be very sure you don't need it.
    – danielson317
    Commented May 11, 2016 at 16:44
  • I have 8 drupal sites on this server, all with databases on the same server, and the other 7 sites are insanely quick. Its just this one that is slow, and yeah it happened within a few days, with nothng I can think of to spur it
    – Collins
    Commented May 11, 2016 at 16:45
  • Is one of your modules throwing lots of errors? There excess database queries can cause slowdowns.
    – Neograph734
    Commented May 11, 2016 at 16:55
  • No errors to be found in watchdog, and no recent changes to modules. Im going to download everything and set up in WAMP and see how the speeds are there
    – Collins
    Commented May 11, 2016 at 17:01

Your Answer

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

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