Why Is My MySQL Site So Slow? Diagnose and Fix High CPU Usage

While investigating a website that loaded very slowly, the author logged into the server and used top to see that MySQL was consuming over 100% CPU, confirming it as the bottleneck.

Running SHOW PROCESSLIST revealed many LOCK operations and several "Copying to tmp table" entries, indicating that complex SQL statements were creating excessive temporary tables and blocking other work.

The remediation plan focused on two areas: adjusting MySQL configuration parameters and optimizing the problematic SQL queries.

Configuration changes began with inspecting temporary table usage via SHOW GLOBAL STATUS LIKE 'created_tmp%'; which showed a high created_tmp_disk_tables count, prompting an increase of tmp_table_size and related settings. Thread statistics ( SHOW GLOBAL STATUS LIKE 'Thread%';) showed a large threads_created value, so thread_cache_size was raised. Table cache metrics ( SHOW GLOBAL STATUS LIKE 'open%tables%';) indicated an oversized opened_tables count, leading to a higher table_cache value. Other parameters such as join_buffer_size and sort_buffer_size were also increased from their defaults.

These adjustments were applied in my.cnf, for example:

table_cache = 64<br/>sort_buffer_size = 8M<br/>join_buffer_size = 4M<br/>thread_cache_size = 300<br/>thread_concurrency = 8<br/>tmp_table_size = 246M

For SQL optimization, the author filtered the SHOW PROCESSLIST output to locate the most complex statements, then used EXPLAIN and PROFILE to analyze them, added appropriate indexes, and split large queries into smaller ones.

After completing these steps, MySQL returned to normal operation, and the site’s performance improved, with further SQL and caching refinements planned for the future.