What is CPU Usage in MySQL?

CPU usage in MySQL refers to the proportion of a computer's processor resources that the MySQL server utilizes to execute operations, including processing queries, managing database transactions, and maintaining the database system. In essence, it measures how much of the CPU's computational capacity is dedicated to running MySQL tasks at any given time.

When MySQL CPU usage is very high this can indicate that your database is actively processing a large number of requests or complex queries. On the other hand, consistently low CPU usage might suggest you are underutilizing resources. This can mean there are inefficiencies or bottlenecks elsewhere in the system.

How to Check MySQL CPU Usage

Using the top Command

The top command is a generic tool available in Linux and other Unix-like operating systems (like CentOS). It provides a dynamic real-time view of your system's current state, including the CPU usage of all processes.

1. Open your terminal.
2. Execute the top command.

Look for the mysql process in the list. The %CPU column will show the current CPU usage of your instance of MySQL.

Using the SHOW STATUS Command

MySQL's SHOW STATUS command is a powerful tool for fetching a wide array of server status information. Although it doesn't directly show CPU usage, it allows you to infer CPU load through various counters and status indicators.
Access your MySQL server through the CLI or a MySQL client.
Run the SHOW STATUS: like 'Threads_%' command.

From the list of server statuses and variables, threads_running and threads_connected are the two most important indicators for gauging CPU usage:

• Threads_running – Shows the number of threads that are not sleeping. High numbers here can indicate a high CPU load.
• Threads_connected – This shows the total number of open connections to the MySQL server. When this number is significantly higher than threads_running, it might suggest that many connections are idle or waiting for available CPU resources to process their requests.

Using the SHOW PROCESSLIST Command

The SHOW PROCESSLIST command provides a real-time snapshot of all active threads within your MySQL server.

1. Access your MySQL server via the CLI or through a MySQL client.
2. Execute the SHOW PROCESSLIST; command.

The output includes several columns of data for each thread. Pay close attention to the following:

• Command Column – Indicates the type of operation being formed. Long-running queries, especially CPU-intensive queries, can be identified here.
• Time Column – Shows how long (in seconds) the command has been running. Threads with a high value in the Time column may be contributing to CPU load.

Using Performance Schema

For a more in-depth analysis, MySQL's Performance Schema can be used. It provides detailed information on server events and can be queried to analyze CPU usage per thread or per query.

To enable Performance Schema (if not already enabled):

1. Locate your MySQL configuration file, named my.cnf or my.ini.
2. Add or modify the following entry performance_schema=ON.
3. Restart your MySQL server to apply this change.

Execute a query that joins the performance_schema.threads table with the performance_schema.events_statements_summary_by_thread_by_event_name table. This query will provide detailed insights into the CPU usage associated with different threads and queries executed by the server.

Use External Monitoring Tools

A range of external monitoring solutions, including Percona Monitoring and Management (PMM) and Releem, offer in-depth analysis of MySQL's performance metrics, such as CPU utilization.

These tools provide a comprehensive overview of CPU and associated metrics through an intuitive interface, making it far easier to spot trends and issues.

Diagnosing High CPU Usage in MySQL

High CPU usage within MySQL databases impacts the performance and dependability of your dependent applications. To effectively diagnose this issue, it is key to thoroughly examine MySQL's operational mechanics, query execution processes, and the broader system environment.

1. Inefficient Queries

One of the most common causes of high CPU usage is inefficient or complex SQL queries. Queries that lack proper indexing or involve full table scans can cause the database engine to consume excessive CPU cycles. This inefficiency arises because the database has to read more data than necessary, process each row individually, and perform complex calculations without the aid of indexes.

• Suboptimal Index Usage – Without indexes, MySQL has to perform full table scans, which are highly CPU-intensive. Indexes should be strategically created based on query patterns and frequently accessed columns.
• Complex Joins – Queries involving multiple joins, especially on large tables or those lacking appropriate indexes, can lead to high CPU utilization. Each join operation can exponentially increase the amount of data processed.
• Aggregation Functions – SUM(), COUNT(), AVG()) over large datasets, without proper indexing forces MySQL to scan and process large volumes of data.

You can configure the Slow Query Log (enable and then set your preferred long query threshold) or use Releem Query Analytics to identify which specific queries are taking too long to finish executing. These are the queries that need your attention.

2. High Concurrency

MySQL is designed to handle multiple connections and transactions simultaneously. However, as the level of concurrency increases, so does the complexity of managing these simultaneous connections. A separate thread handles each active connection. Managing a large number of threads requires more CPU resources, not just for the execution of queries but also for the overhead of context switching between threads.

3. Lock Contention

Lock contention occurs when multiple transactions attempt to access the same data at the same time, leading to a scenario where they must wait for each other to release locks before proceeding. When transactions compete for locks on rows or tables, the database engine spends additional CPU cycles managing these locks.

You can determine if lock contention is occurring by looking at the information_schema.innodb_lock_waits table. Use the following query to identify transactions that are being locked:

SELECT 
  t.trx_id, 
  t.trx_state, 
  t.trx_started, 
  COUNT(distinct w.requesting_trx_id) AS blocked_trxs
FROM 
  information_schema.innodb_lock_waits w 
INNER JOIN information_schema.innodb_trx t
   ON t.trx_id = w.blocking_trx_id 
GROUP BY t.trx_id,t.trx_state, t.trx_started
ORDER BY t.trx_id;

Long-running transactions can also provide insights into the level of contention within your database. Use the SHOW ENGINE INNODB STATUS command to view a list of all open transactions from newest to oldest. Inspect the oldest transactions to get an idea of how long they are running, which tables and rows are involved, and what statements are involved.

4. Poorly Configured MySQL Server

MySQL's performance is highly dependent on its configuration. Parameters that are not optimized for your workload or hardware can lead to inefficient CPU usage. Some key parameters include:

• Inappropriate Buffer Pool Size – The InnoDB buffer pool reduces disk I/O by caching data and indexes in memory. An incorrectly sized buffer pool can cause frequent disk reads. Because the system handles disk I/O operations, MySQL CPU usage is increased.
• Thread Cache Size – If the thread cache is too small, MySQL may spend extra CPU cycles creating and destroying threads for each new connection rather than reusing existing threads.

5. Schema Design Issues

The physical design of the database, including table structures and data types, can also affect CPU efficiency. If you use larger-than-necessary data types (like BIGINT over INT), MySQL processes more data than needed, consuming more CPU resources.

Normalization is a database design technique used to organize tables in a way that reduces data redundancy and improves data integrity. The process involves dividing a database into two or more tables and defining relationships between the tables. While over-normalization can lead to complex joins, under-normalization can result in redundant data processing and larger scans, both of which increase CPU usage.

11 Tips for Mitigating High MySQL CPU Usage

1. MySQL's resource control features can help prevent any single user from consuming too much CPU time, ensuring a balanced load across applications. Percona published the article on that.
2. Analyze query patterns and create indexes on columns that are frequently used in WHERE clauses, JOIN conditions, and as part of ORDER BY and GROUP BY statements. Be cautious of over-indexing, as it can lead to increased overhead during write operations.
3. This command shows you how the database goes about executing a specific query, including which indexes are used. The execution plan can reveal if a query results in a full table scan or if a query is using an index inefficiently or not at all. These results would be target for query optimization.
4. Reduce the complexity of joins, especially those involving multiple tables or large datasets. All joined tables should have appropriate indexes on the join columns. Consider denormalizing your schema if certain joins are heavily impacting CPU usage.
5. Break down complex queries into simpler ones to avoid full table scans and reduce temporary table usage, especially those created on disk. Use subqueries and derived tables carefully.
6. Adjust the InnoDB buffer pool size to fit as much of the working dataset into memory as possible, reducing disk I/O and CPU usage for disk reads. The buffer pool size should ideally be set to about 70-80% of available system memory.
7. The default value for the thread cache size might be too low for systems under heavy load or with frequent connection turnover. Configure the thread cache size to reduce the overhead of creating and destroying threads for connections. A starting point can be to set the thread cache size to the number of concurrent connections that your system typically handles at peak times.
8. Implement connection pooling in the application layer to reduce the overhead of frequent connections and disconnections to the database. Connection pooling allows a set of connections to be reused among multiple clients, diminishing the CPU load associated with connection management.
9. MySQL should run on a server with dedicated resources. Sharing resources with other CPU-intensive applications can lead to contention and high CPU usage.
10. Use hardware that matches your workload requirements. In CPU-bound scenarios, choosing processors with higher core counts and faster clock speeds can improve performance. Consider SSDs over HDDs for storage to reduce CPU load associated with disk I/O operations.
11. MySQL's Performance Schema and the slow query log can be used to monitor database operations and identify high CPU usage patterns. These tools can help pinpoint inefficient queries and suboptimal configurations contributing to high CPU usage.

Releem’s Capabilities for MySQL CPU Optimization

Releem offers a range of capabilities to help you monitor and manage your CPU usage:

• Intuitive Interface: The user-friendly interface presents CPU usage trends over time in a visually accessible format. It simplifies the optimization process, allowing you to make informed decisions with ease. No need to manually interpret CPU usage from other variables or use the CLI.
• Monitoring: Releem monitors your server, capturing important parameters and key metrics such as CPU usage, InnoDB buffer pool size, thread cache size, and more. This real-time data collection provides invaluable insights into your server's performance dynamics. Automatic Performance Analysis By crunching the numbers gathered during monitoring, pinpoints areas where CPU efficiency can be improved.
• Configuration Recommendations: Releem then suggests easy-to-apply configuration recommendations to optimize your MySQL CPU usage. These recommendations are customized to your server's specific characteristics and workload patterns.
• Query Analytics: Digs into the slow query log to identify and analyze queries that might be hogging CPU resources. Armed with this knowledge, you can fine-tune queries, apply proper indexing, and take other corrective actions.

If you'd like to check out these capabilities, you can sign up for free to get started!