1

I have a website with 4 views (and sometimes 5 views) on the homepage. The problem is that when I try to open the website, it takes minutes to load and the cpu goes crazy because of mysql processes.

Screenshot of cpu usage enter image description here

Output of SHOW PROCESS LIST enter image description here

I work on a dedicated server with 8GB of RAM, 1900Mhz Intel Hexa-Core Xeon and 4 × 300GB SAS (RAID 10)

I have been using drupal for years, and never encountered such an issue. Any ideas how to fix this?

Update Views query:

SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.language AS node_language, node.created AS node_created, users_node.picture AS users_node_picture, users_node.uid AS users_node_uid, users_node.name AS users_node_name, users_node.mail AS users_node_mail, taxonomy_term_data_node.tid AS taxonomy_term_data_node_tid, taxonomy_term_data_taxonomy_term_hierarchy.tid AS taxonomy_term_data_taxonomy_term_hierarchy_tid, 'node' AS field_data_field_news_photo_node_entity_type, 'node' AS field_data_field_op_section_term_node_entity_type, 'node' AS field_data_body_node_entity_type, 'user' AS field_data_field_user_realname_user_entity_type, 'user' AS field_data_field_user_organization_user_entity_type, 'user' AS field_data_field_profile_picture_user_entity_type FROM {node} node LEFT JOIN {users} users_node ON node.uid = users_node.uid LEFT JOIN (SELECT td.*, tn.nid AS nid FROM {taxonomy_term_data} td LEFT JOIN {taxonomy_vocabulary} tv ON td.vid = tv.vid LEFT JOIN {taxonomy_index} tn ON tn.tid = td.tid WHERE (tv.machine_name IN ('sections')) ) taxonomy_term_data_node ON node.nid = taxonomy_term_data_node.nid LEFT JOIN {taxonomy_term_hierarchy} taxonomy_term_data_node__taxonomy_term_hierarchy ON taxonomy_term_data_node.tid = taxonomy_term_data_node__taxonomy_term_hierarchy.tid LEFT JOIN {taxonomy_term_data} taxonomy_term_data_taxonomy_term_hierarchy ON taxonomy_term_data_node__taxonomy_term_hierarchy.parent = taxonomy_term_data_taxonomy_term_hierarchy.tid LEFT JOIN {field_data_field_news_photo} field_data_field_news_photo ON node.nid = field_data_field_news_photo.entity_id AND (field_data_field_news_photo.entity_type = 'node' AND field_data_field_news_photo.deleted = '0') WHERE (( (node.status = '1') AND (node.type IN ('news')) AND (field_data_field_news_photo.field_news_photo_fid IS NOT NULL ) AND (node.language IN ('***CURRENT_LANGUAGE***')) )) ORDER BY node_created DESC LIMIT 12 OFFSET 0

EXPLAIN output. enter image description here

  • can you post the views performance data as well? it's shown when you preview a view. also devel can print the query log so you can pin point which query is the worst. – AyeshK Mar 1 '15 at 12:30
  • I get the same results even if the homepage has one only view. I updated the question with the query from views preview. Also, i have about 9000 nodes. – Muhammad Reda Mar 1 '15 at 12:37
  • Add explain to the front of the query and post that output – mikeytown2 Mar 1 '15 at 16:33
  • @mikeytown2; added a screenshot. – Muhammad Reda Mar 1 '15 at 16:40
  • 1
    if you manually run the sql's in phpMyAdmin or similar, what is the execution time? – Geoff Mar 1 '15 at 21:15
0

The process list is saying that Mysql is writing to tmp tables. The issue is the way you configured mysql. Check the my.cnf configuration, and make sure that the temporary tables are large enough.

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.