0

A running mysql service uses CPU in a crazy way. (See attached screenshot from htop)

I followed the instructions here with no luck.

My server specs:

  • CPU: 1900Mhz . Intel Hexa-Core Xeon E5-2420
  • Number of CPUs: 2. Number of cores: 6.
  • RAM: 8GB
  • Hard disks: 4 × 300GB SAS
  • RAID 10
  • OS: Cent OS 6.5

Anything I can do?

screenshot from htop

SHOW PROCESSLIST; output enter image description here

DB query:

SELECT COUNT(*) AS expression FROM (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, 1 AS expression 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')) AND (td.language IN ('ar', 'und')) ) 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 ('ar')) )))

Community
  • 1
Muhammad Reda
  • 101
  • 2
  • 1
    Connect to mysql and do SHOW PROCESSLIST; That will give you a clue of what is going on. – Rick James Feb 25 '15 at 16:12
  • I know the name of the database that causes this cpu over usage. – Muhammad Reda Feb 25 '15 at 16:15
  • The database name is not enough -- find the entire statement that is causing the problem; show us. Also show us SHOW CREATE TABLE for each table involved in the query. Perhaps with those, you can get this question off "hold". – Rick James Feb 25 '15 at 16:49
  • I added a screenshot for SHOW PROCESSLIST output. – Muhammad Reda Feb 26 '15 at 12:35
  • It seems some of your queries are not optimized / not using indexes. Apart from the advice to optimize your queries, I don't think we can help much. – ypercubeᵀᴹ Feb 26 '15 at 12:44
  • In the PROCESSLIST, I see at least 2 different SELECTs that have been running for 17 seconds. Find them, show us the complete SELECT (look in the code, or SHOW FULL PROCESSLIST, then copy the query). Then add SHOW CREATE TABLE for the relevant tables. 17 seconds is a sloooow query; we may be able to speed it up by adding an index or rewriting the SELECT. (Hence, we need to see both.) – Rick James Feb 26 '15 at 16:48
  • I updated the answer with the query. Also, what do you mean by SHOW CREATE TABLE ? – Muhammad Reda Feb 26 '15 at 16:52

0 Answers0