0

Our Drupal site has recently started to get much more traffic and often pegs the CPU and memory when we have 50-100 active connections. We have 8cpus and 16GB of RAM. I notice that when the cpu is pegged mysql is by far the largest consumer so I decided to look at the mysql-slow.log and found that some queries take over 1 second and I am wondering if this is reasonable. Here is one example:

# Time: 150330 12:01:34
# User@Host: root[root] @ localhost []
# Query_time: 1.131631  Lock_time: 0.000301 Rows_sent: 4  Rows_examined: 151102
SET timestamp=1427738494;
SELECT 
  node.nid AS nid, 
  node.title AS node_title, 
  users_node.name AS users_node_name, 
  users_node.uid AS users_node_uid, 
  votingapi_cache_node_points_projects_sum.value AS votingapi_cache_node_points_projects_sum_value, 
  node.created AS node_created, 
  node.changed AS node_changed, 
  node_revision.timestamp AS node_revision_timestamp, 
  'node' AS field_data_field_screenshot_node_entity_type
FROM node AS node
INNER JOIN users AS users_node 
  ON node.uid = users_node.uid
LEFT JOIN votingapi_cache AS votingapi_cache_node_points_projects_sum 
  ON node.nid = votingapi_cache_node_points_projects_sum.entity_id 
  AND votingapi_cache_node_points_projects_sum.entity_type = 'node' 
  AND votingapi_cache_node_points_projects_sum.value_type = 'points' 
  AND votingapi_cache_node_points_projects_sum.tag = 'projects' 
  AND votingapi_cache_node_points_projects_sum.function = 'sum'
LEFT JOIN votingapi_vote AS votingapi_vote_node_points_vote 
  ON node.nid = votingapi_vote_node_points_vote.entity_id 
  AND votingapi_vote_node_points_vote.entity_type = 'node' 
  AND votingapi_vote_node_points_vote.value_type = 'points' 
  AND votingapi_vote_node_points_vote.tag = 'vote'
LEFT JOIN node_revision AS node_revision 
  ON node.vid = node_revision.vid
WHERE node.status = '1'
  AND node.type IN ('arcade_project_container')
  AND EXISTS (
    SELECT na.nid AS nid
    FROM node_access AS na
    WHERE na.gid = '0' 
    AND na.realm = 'all'
    AND na.grant_view >= '1' 
    AND node.nid = na.nid
  )
ORDER BY 
  node_created DESC, 
  node_changed DESC, 
  node_revision_timestamp DESC
LIMIT 4 OFFSET 5800;

This query comes from the front page where I have a view which shows the most recent submissions. Should this query really take 1.13 seconds?

EDIT: Here is a screenshot of the results of running EXPLAIN on this query on phpmyadmin:

enter image description here

Here is the EXPLAIN after switch nid and uid as suggested by mikeytown2:

enter image description here

4
  • 1
    What does EXPLAIN say? Add EXPLAIN before the SELECT to get the output. – mikeytown2 Mar 30 '15 at 19:06
  • @mikeytown2 I wonder, how could I do that? This query is generated by Drupal core and views. Or am i missing something? – Mike2012 Mar 30 '15 at 19:26
  • 1
    @Mike2012 you can copy/paste the query in to something like phpmyadmin and add the "explain" – user5482 Mar 30 '15 at 19:50
  • @user5482 Ok, great thanks for the tip! I uploaded a screenshot of the result of EXPLAIN from phpmyadmin. – Mike2012 Mar 30 '15 at 21:38
1

Add a new index of type index on the node table with these 7 columns; I would label it "status_type_uid_nid_vid_created_changed"

status, type, uid, nid, vid, created, changed

Once you have done that run explain again; odds are the index will need to be re-ordered or another table will need to have an index added to it.

Try #2. Flip nid & uid.

status_type_nid_uid_vid_created_changed

status, type, nid, uid, vid, created, changed
6
  • Thanks for taking the time to look at this. I have added the index you suggested and posted the new result from explain. It doesn't seem that different. – Mike2012 Mar 30 '15 at 22:58
  • @Mike2012 Try flipping nid & uid in the order – mikeytown2 Mar 30 '15 at 23:12
  • ok, I flipped nid and uid like you suggested and uploaded the new EXPLAIN. – Mike2012 Mar 30 '15 at 23:20
  • What version of MySQL are you using? – mikeytown2 Mar 31 '15 at 0:07
  • I am using version: 14.14 Distrib 5.5.41, for debian-linux-gnu – Mike2012 Mar 31 '15 at 2:08

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.