{"id":1030,"date":"2025-09-13T00:52:59","date_gmt":"2025-09-13T00:52:59","guid":{"rendered":"https:\/\/sreschool.com\/blog\/?p=1030"},"modified":"2025-09-13T00:53:00","modified_gmt":"2025-09-13T00:53:00","slug":"mysql-cpu-consumtions-monitoring","status":"publish","type":"post","link":"https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/","title":{"rendered":"MySql CPU Consumtions Monitoring"},"content":{"rendered":"\n

This is the key question \ud83d\udc4d. With MariaDB\/MySQL the trick is: \u201chigh CPU\u201d doesn\u2019t always show up as long queries in PROCESSLIST<\/strong>. Most of the burn comes from thousands of short queries, temp tables, and bad indexing<\/strong>.<\/p>\n\n\n\n

Here\u2019s a structured way to catch exactly<\/em> what is chewing CPU:<\/p>\n\n\n\n

\n\n\n\n

\ud83d\udd0e Step 1 \u2014 Enable the slow query log (with no-index logging)<\/h2>\n\n\n\n

In the MySQL shell:<\/p>\n\n\n\n

SET GLOBAL slow_query_log = 'ON';\nSET GLOBAL slow_query_log_file = '\/var\/log\/mysql-slow.log';\nSET GLOBAL long_query_time = 0.5;                 -- log >0.5s\nSET GLOBAL log_queries_not_using_indexes = 'ON';  -- catch CPU-burners with no indexes\n<\/code><\/pre>\n\n\n\n
\n
\u26a0\ufe0f On WHM\/cPanel the log path may differ (\/var\/log\/mysql\/<\/code> or \/usr\/local\/cpanel\/logs\/mysql\/<\/code>), so adjust the file path.<\/p>\n<\/blockquote>\n\n\n\n
This will record real workload<\/strong>, including those 10ms queries that repeat millions of times.<\/p>\n\n\n\n
\n\n\n\n
\ud83d\udd0e Step 2 \u2014 Let it run 10\u201330 minutes<\/h2>\n\n\n\n
Don\u2019t guess. Let your live traffic fill the slow log. The file will grow quickly if you have lots of bad queries.<\/p>\n\n\n\n
\n\n\n\n
\ud83d\udd0e Step 3 \u2014 Digest the log<\/h2>\n\n\n\n
On the server:<\/p>\n\n\n\n
yum install percona-toolkit   # (or apt install percona-toolkit)\npt-query-digest \/var\/log\/mysql-slow.log | less\n<\/code><\/pre>\n\n\n\n
This groups queries by fingerprint and shows:<\/p>\n\n\n\n
    \n
  • Total time spent<\/li>\n\n\n\n
  • Avg\/max execution time<\/li>\n\n\n\n
  • % of overall load<\/li>\n\n\n\n
  • Example query text<\/li>\n<\/ul>\n\n\n\n
    \ud83d\udc49 The top 5 fingerprints<\/strong> are your true CPU killers.<\/p>\n\n\n\n
    \n\n\n\n
    \ud83d\udd0e Step 4 \u2014 Cross-check with counters<\/h2>\n\n\n\n
    Run these in MySQL to confirm where overhead comes from:<\/p>\n\n\n\n
    SHOW GLOBAL STATUS LIKE 'Created_tmp%';     -- tmp tables (CPU\/disk)\nSHOW GLOBAL STATUS LIKE 'Handler_read%';    -- table\/index reads\nSHOW GLOBAL STATUS LIKE 'Innodb_rows_%';    -- InnoDB row ops\nSHOW GLOBAL STATUS LIKE 'Threads_running';  -- concurrency at peaks\n<\/code><\/pre>\n\n\n\n
    If Created_tmp_disk_tables<\/code> or Created_tmp_tables<\/code> are climbing fast \u2192 queries missing indexes.<\/p>\n\n\n\n
    \n\n\n\n
    \ud83d\udd0e Step 5 \u2014 Use EXPLAIN<\/code><\/h2>\n\n\n\n
    Take the worst queries from step 3 and run:<\/p>\n\n\n\n
    EXPLAIN SELECT ...;\n<\/code><\/pre>\n\n\n\n
    Check the key<\/code> column:<\/p>\n\n\n\n
      \n
    • If NULL<\/code> \u2192 full scan = CPU hog.<\/li>\n\n\n\n
    • If Using temporary<\/code> or Using filesort<\/code> in Extra<\/code> \u2192 also CPU hog.<\/li>\n<\/ul>\n\n\n\n
      \n\n\n\n
      \ud83d\udd0e Step 6 \u2014 Fix<\/h2>\n\n\n\n
        \n
      1. Add indexes<\/strong> matching the WHERE<\/code>, JOIN<\/code>, ORDER BY<\/code> of those queries.<\/li>\n\n\n\n
      2. Cache results<\/strong> in WordPress (Redis object cache, page cache).<\/li>\n\n\n\n
      3. Disable\/reconfigure plugins<\/strong> generating high-cost queries (e.g. visit-tracker).<\/li>\n\n\n\n
      4. Right-size PHP-FPM pools<\/strong> so one site doesn\u2019t stampede.<\/li>\n<\/ol>\n\n\n\n
        \n\n\n\n
        \ud83d\udd0e Step 7 \u2014 Monitor improvement<\/h2>\n\n\n\n
          \n
        • CPU usage for mariadbd<\/code> in top<\/code>\/pidstat<\/code> should fall.<\/li>\n\n\n\n
        • SHOW GLOBAL STATUS LIKE 'Created_tmp%';<\/code> should grow much slower.<\/li>\n\n\n\n
        • Slow log should shrink to just occasional misses.<\/li>\n<\/ul>\n\n\n\n
          \n\n\n\n
          \u2705 TL;DR<\/h3>\n\n\n\n
          To know<\/em> what is causing CPU burn:<\/p>\n\n\n\n
            \n
          1. Enable slow query log + log_queries_not_using_indexes.<\/strong><\/li>\n\n\n\n
          2. Run pt-query-digest<\/strong> to find the top queries by total CPU time.<\/li>\n\n\n\n
          3. EXPLAIN<\/strong> those queries and add the right indexes or cache them.<\/li>\n\n\n\n
          4. Watch tmp table counters<\/strong> \u2014 if they calm down, CPU will too.<\/li>\n<\/ol>\n\n\n\n
            \n\n\n\n
            <\/p>\n\n\n\n
            <\/p>\n","protected":false},"excerpt":{"rendered":"
            This is the key question \ud83d\udc4d. With MariaDB\/MySQL the trick is: \u201chigh CPU\u201d doesn\u2019t always show up as long queries […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1030","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nMySql CPU Consumtions Monitoring - SRE School<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySql CPU Consumtions Monitoring - SRE School\" \/>\n<meta property=\"og:description\" content=\"This is the key question \ud83d\udc4d. With MariaDB\/MySQL the trick is: \u201chigh CPU\u201d doesn\u2019t always show up as long queries […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/\" \/>\n<meta property=\"og:site_name\" content=\"SRE School\" \/>\n<meta property=\"article:published_time\" content=\"2025-09-13T00:52:59+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-09-13T00:53:00+00:00\" \/>\n<meta name=\"author\" content=\"Rajesh Kumar\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Rajesh Kumar\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/\",\"url\":\"https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/\",\"name\":\"MySql CPU Consumtions Monitoring - SRE School\",\"isPartOf\":{\"@id\":\"https:\/\/sreschool.com\/blog\/#website\"},\"datePublished\":\"2025-09-13T00:52:59+00:00\",\"dateModified\":\"2025-09-13T00:53:00+00:00\",\"author\":{\"@id\":\"https:\/\/sreschool.com\/blog\/#\/schema\/person\/0ffe446f77bb2589992dbe3a7f417201\"},\"breadcrumb\":{\"@id\":\"https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/#breadcrumb\"},\"inLanguage\":\"en\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/sreschool.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySql CPU Consumtions Monitoring\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/sreschool.com\/blog\/#website\",\"url\":\"https:\/\/sreschool.com\/blog\/\",\"name\":\"SRESchool\",\"description\":\"Master SRE. Build Resilient Systems. Lead the Future of Reliability\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/sreschool.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/sreschool.com\/blog\/#\/schema\/person\/0ffe446f77bb2589992dbe3a7f417201\",\"name\":\"Rajesh Kumar\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en\",\"@id\":\"https:\/\/sreschool.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f901a4f2929fa034a291a8363d589791d5a3c1f6a051c22e744acb8bfc8e022a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f901a4f2929fa034a291a8363d589791d5a3c1f6a051c22e744acb8bfc8e022a?s=96&d=mm&r=g\",\"caption\":\"Rajesh Kumar\"},\"sameAs\":[\"http:\/\/sreschool.com\/blog\"],\"url\":\"https:\/\/sreschool.com\/blog\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySql CPU Consumtions Monitoring - SRE School","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/","og_locale":"en_US","og_type":"article","og_title":"MySql CPU Consumtions Monitoring - SRE School","og_description":"This is the key question \ud83d\udc4d. With MariaDB\/MySQL the trick is: \u201chigh CPU\u201d doesn\u2019t always show up as long queries […]","og_url":"https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/","og_site_name":"SRE School","article_published_time":"2025-09-13T00:52:59+00:00","article_modified_time":"2025-09-13T00:53:00+00:00","author":"Rajesh Kumar","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Rajesh Kumar","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/","url":"https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/","name":"MySql CPU Consumtions Monitoring - SRE School","isPartOf":{"@id":"https:\/\/sreschool.com\/blog\/#website"},"datePublished":"2025-09-13T00:52:59+00:00","dateModified":"2025-09-13T00:53:00+00:00","author":{"@id":"https:\/\/sreschool.com\/blog\/#\/schema\/person\/0ffe446f77bb2589992dbe3a7f417201"},"breadcrumb":{"@id":"https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/#breadcrumb"},"inLanguage":"en","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sreschool.com\/blog\/mysql-cpu-consumtions-monitoring\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sreschool.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MySql CPU Consumtions Monitoring"}]},{"@type":"WebSite","@id":"https:\/\/sreschool.com\/blog\/#website","url":"https:\/\/sreschool.com\/blog\/","name":"SRESchool","description":"Master SRE. Build Resilient Systems. Lead the Future of Reliability","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sreschool.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en"},{"@type":"Person","@id":"https:\/\/sreschool.com\/blog\/#\/schema\/person\/0ffe446f77bb2589992dbe3a7f417201","name":"Rajesh Kumar","image":{"@type":"ImageObject","inLanguage":"en","@id":"https:\/\/sreschool.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/f901a4f2929fa034a291a8363d589791d5a3c1f6a051c22e744acb8bfc8e022a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f901a4f2929fa034a291a8363d589791d5a3c1f6a051c22e744acb8bfc8e022a?s=96&d=mm&r=g","caption":"Rajesh Kumar"},"sameAs":["http:\/\/sreschool.com\/blog"],"url":"https:\/\/sreschool.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/posts\/1030","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/comments?post=1030"}],"version-history":[{"count":1,"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/posts\/1030\/revisions"}],"predecessor-version":[{"id":1031,"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/posts\/1030\/revisions\/1031"}],"wp:attachment":[{"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/media?parent=1030"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/categories?post=1030"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sreschool.com\/blog\/wp-json\/wp\/v2\/tags?post=1030"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}