MySql CPU Consumtions Monitoring

This is the key question 👍. With MariaDB/MySQL the trick is: “high CPU” doesn’t always show up as long queries in PROCESSLIST. Most of the burn comes from thousands of short queries, temp tables, and bad indexing.

Here’s a structured way to catch exactly what is chewing CPU:

---

🔎 Step 1 — Enable the slow query log (with no-index logging)

In the MySQL shell:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql-slow.log';
SET GLOBAL long_query_time = 0.5;                 -- log >0.5s
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- catch CPU-burners with no indexes

⚠️ On WHM/cPanel the log path may differ (/var/log/mysql/ or /usr/local/cpanel/logs/mysql/), so adjust the file path.

This will record real workload, including those 10ms queries that repeat millions of times.

---

🔎 Step 2 — Let it run 10–30 minutes

Don’t guess. Let your live traffic fill the slow log. The file will grow quickly if you have lots of bad queries.

---

🔎 Step 3 — Digest the log

On the server:

yum install percona-toolkit   # (or apt install percona-toolkit)
pt-query-digest /var/log/mysql-slow.log | less

This groups queries by fingerprint and shows:

• Total time spent
• Avg/max execution time
• % of overall load
• Example query text

👉 The top 5 fingerprints are your true CPU killers.

---

🔎 Step 4 — Cross-check with counters

Run these in MySQL to confirm where overhead comes from:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';     -- tmp tables (CPU/disk)
SHOW GLOBAL STATUS LIKE 'Handler_read%';    -- table/index reads
SHOW GLOBAL STATUS LIKE 'Innodb_rows_%';    -- InnoDB row ops
SHOW GLOBAL STATUS LIKE 'Threads_running';  -- concurrency at peaks

If Created_tmp_disk_tables or Created_tmp_tables are climbing fast → queries missing indexes.

---

🔎 Step 5 — Use EXPLAIN

Take the worst queries from step 3 and run:

EXPLAIN SELECT ...;

Check the key column:

• If NULL → full scan = CPU hog.
• If Using temporary or Using filesort in Extra → also CPU hog.

---

🔎 Step 6 — Fix

1. Add indexes matching the WHERE, JOIN, ORDER BY of those queries.
2. Cache results in WordPress (Redis object cache, page cache).
3. Disable/reconfigure plugins generating high-cost queries (e.g. visit-tracker).
4. Right-size PHP-FPM pools so one site doesn’t stampede.

---

🔎 Step 7 — Monitor improvement

• CPU usage for mariadbd in top/pidstat should fall.
• SHOW GLOBAL STATUS LIKE 'Created_tmp%'; should grow much slower.
• Slow log should shrink to just occasional misses.

---

✅ TL;DR

To know what is causing CPU burn:

1. Enable slow query log + log_queries_not_using_indexes.
2. Run pt-query-digest to find the top queries by total CPU time.
3. EXPLAIN those queries and add the right indexes or cache them.
4. Watch tmp table counters — if they calm down, CPU will too.

---