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')) )))

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.