A TencentDB for MySQL instance encounters such issues as slow response, failure to get connections, and timeout. The cause is a high CPU utilization of more than 80%.
If MySQL's CPU utilization remains high for a prolonged time, the overall performance of the database will be severely compromised, and under extreme circumstances, instances may be hung.
When the HA system detects such an issue, it will trigger a source-replica switch to maintain the high availability of your business. During the switch, instances are usually unavailable for less than 60 seconds. If the switch occurs during peak hours, business stability and continuity will be seriously affected.
To protect your business from being affected by CPU resource shortage, we recommend that you optimize the application or upgrade the CPU resources for the instance with a high CPU utilization. A source-replica switch is accompanied by a disconnection lasting for just seconds; therefore, for persistent connections, your application should have a reconnection mechanism.
MySQL's CPU resources are mainly used by system threads and user threads. Therefore, if CVMs are for exclusive use by your TencentDB for MySQL instances, you can solve most of the issues just by focusing on the two types of threads.
In most cases, busy user threads are caused by slow queries, heavy computation, and high QPS (queries per second).
In a production environment, system thread issues are less frequent. In general, the CPU utilizations of multiple system threads are rarely too high or close to 100% at the same time as long as the CVM has at least four available CPU cores. However, there are a few bugs that may affect the CPU utilization, as shown in the figure below:
As most CPU issues are caused by busy user threads, the following sections focus on the solutions to high CPU utilization caused by user threads.
Use DBbrain to identify and optimize the SQL statements which cause a high CPU utilization:
In MySQL, slow query time (
long_query_time) is set to 10 seconds by default. After a performance issue occurs, if no slow query is found, it is recommended to adjust the parameter value to 1 second and then observe whether there are slow queries in a business cycle, and if yes, optimize the slow queries accordingly. After the parameter is adjusted, if still no slow queries are found but the CPU utilization remains high, you are recommended to upgrade the CPU configuration so as to improve the overall performance of the database.
When MySQL handles huge amounts of data, its CPU utilization can be high, even if the indexes and query execution plans work well. Moreover, such an issue can still occur at a low concurrency due to MySQL’s one-thread-per-connection feature
Generally, there are two common solutions: