MySQL Top CPU Usage by Database

When top shows MySQL hogging a server, mysql top cpu usage inside the MySQL shell tells you which database, user, or query is actually responsible. SHOW FULL PROCESSLIST is the one-second snapshot; performance_schema gives you cumulative stats over time. Together they let you trace a CPU spike back to the exact site, user, or query pattern to fix.

Contents

TL;DR
Snapshot: SHOW FULL PROCESSLIST
Historical view: performance_schema
Matching threads to sites
Secondary diagnostic: SHOW OPEN TABLES
From diagnosis to fix
Frequently asked questions
Related guides
References

Last verified: 2026-04-23 on MySQL 8.0 and MariaDB 10.11. Originally published 2022-06-08, rewritten and updated 2026-04-23.

TL;DR

-- Connect as root via SSH
mysql -u root -p

-- Snapshot of what's running right now
SHOW FULL PROCESSLIST;

-- Top 10 query patterns by cumulative time (MySQL 8 / MariaDB 10.5+)
SELECT
    SUBSTRING(digest_text, 1, 100) AS query,
    count_star,
    ROUND(sum_timer_wait / 1e12, 2) AS total_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

Snapshot: `SHOW FULL PROCESSLIST`

SHOW FULL PROCESSLIST;

Returns a row per thread. The columns that matter:

User — MySQL user running the query. Match this against your apps.
db — database the query is touching. Match this against your app configs.
Command — Query means actively working; Sleep means idle connection.
Time — seconds the thread has been in its current state. Long times = long queries = CPU hogs.
State — Sending data, Copying to tmp table, Sorting result are usually CPU-bound.
Info — the actual SQL (truncated unless you used FULL).

Run it three or four times in quick succession. Anything that shows up every time is the real problem; a one-off Time: 12 that disappears on the next check is just a slow query passing through.

mysql top cpu usage — PROCESSLIST snapshot plus performance_schema historical digest

Historical view: performance_schema

SELECT
    SUBSTRING(digest_text, 1, 100) AS query,
    schema_name                     AS db,
    count_star                      AS exec_count,
    ROUND(sum_timer_wait / 1e12, 2) AS total_seconds,
    ROUND(avg_timer_wait / 1e9,  2) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 20;

This is the top 20 query digests (similar queries grouped) by cumulative time since the server started. Unlike PROCESSLIST, it sees patterns — a query that runs 10,000 times at 50ms each isn’t visible in a snapshot but dominates the cumulative list. The schema_name tells you which database.

performance_schema is enabled by default on MySQL 5.6.6+ and MariaDB 10.0.5+. If the query errors with “Unknown table,” check SHOW VARIABLES LIKE 'performance_schema'; — if OFF, enable it in my.cnf and restart (see restart MariaDB).

Matching threads to sites

If your server hosts multiple sites, each should have its own MySQL user (see creating users and granting privileges). Then:

# In the site's config:
#   WordPress: wp-config.php → DB_USER, DB_NAME
#   Laravel:   .env          → DB_USERNAME, DB_DATABASE

Cross-reference the PROCESSLIST User + db columns against each site’s config. The CPU-burning threads belong to whichever site owns that user/db pair. If every site shares root, you can’t distinguish them — which is a good reason to fix that first.

Secondary diagnostic: `SHOW OPEN TABLES`

SHOW OPEN TABLES WHERE In_use > 0;

Lists tables currently locked or being read. Not a CPU-usage tool, but useful alongside PROCESSLIST: if a site’s table keeps showing up as in-use, its queries are likely the hot ones. Treat as supporting evidence, not primary diagnostic.

From diagnosis to fix

-- Once you've identified the slow query, explain it
EXPLAIN SELECT ... FROM ... WHERE ...;

-- Look for:
--   type: ALL            -> full table scan, needs an index
--   rows: 1000000        -> scanning too much data
--   Using temporary      -> creates a temp table (slow)
--   Using filesort       -> does an extra sort pass

Add an index on the column in the WHERE or JOIN, re-run EXPLAIN until type shows something other than ALL. For WordPress, the most common fix is adding a composite index on (post_id, meta_key) in wp_postmeta. For Laravel, checking that your Eloquent relationships aren’t triggering N+1 queries (use with('relation')).

Frequently asked questions

What’s the fastest mysql top cpu usage check?

SHOW FULL PROCESSLIST; inside a root MySQL session. It lists every currently-running thread with its user, database, state, time, and query. The threads with high Time values and states like Sending data or Copying to tmp table are the ones burning CPU. Run it a few times in quick succession — anything that shows up repeatedly is the real hog.

Can I see historical CPU use, not just the current second?

Yes — enable the performance schema’s statements summary: SELECT digest_text, count_star, sum_timer_wait FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10;. Shows the top 10 query shapes (not individual queries — similar queries are grouped by digest) by total time since server start. Better than PROCESSLIST for diagnosing patterns.

How do I identify the website causing MySQL CPU spikes?

Match the User and db columns from SHOW PROCESSLIST against your WordPress/Laravel config files — wp-config.php‘s DB_USER + DB_NAME, or .env‘s DB_USERNAME + DB_DATABASE. Each site typically has its own user/db pair, so PROCESSLIST lines tagged with wp_myblog on database wp_myblog point straight to that site.

Is SHOW OPEN TABLES useful here?

Only indirectly — it shows what’s in the table cache, not active CPU. It can hint at which tables are hot, but PROCESSLIST and performance_schema are the right tools for ‘who’s burning the CPU right now.’ Treat OPEN TABLES as supporting evidence, not a primary diagnostic.

What do I do once I’ve found the slow query?

EXPLAIN SELECT ... the query, look for type: ALL (full table scan) or big rows estimates. Usually it needs an index. On WordPress, common culprits are wp_options lookups that skip autoload = 'yes' filtering, or wp_postmeta joins without the meta_key index. Fix the index, re-run EXPLAIN until the query uses one. If the query itself is bad architecture (SELECT * across millions of rows), refactor.