MySQL server process CPU usage 100% solution

There are many friends who use the mysql database server, and we often encounter mysql process CPU usage of 100%. Below I will summarize some solutions.

There are many friends who use the mysql database server, and we often encounter mysql process CPU usage of 100%. Below I will summarize some solutions.

Solution 1, Modify the my. cnf File

Find

Query_cache_size = 0

This is probably the cause of this problem. No query cache is enabled.

(Usually 64 MB cache for 1 GB)

Memory of my server is 4 GB,

Adjusted

The Code is as follows:
Query_cache_size = 256 M

If your system needs a large temporary cache, we can find it again.

The Code is as follows:
Tmp_table_size

Modify My. ini and assign the tmp_table_size value to 200 MB.

System cpu usage 100% troubleshooting

Cause Analysis

The deployed application reports to the master every 30 seconds. Each report creates a connection to mysql. After processing, the mysql connection is closed,
However, after the mysql connection is closed, the tcp resources are not released and remain in the TIME_WAIT status within 2-3 minutes.
During High IO operations, a large number of mysql connections will be created and closed after mysq requests are created. Therefore, there will be more tcp connections than TIME_WAIT.
Eventually lead to new mysql connections, no tcp resources available, and finally mysql connect fail

Possible solutions:

1. Change the application connection mode to a mysql persistent connection for each thread. (Change short connection to persistent connection)
2. Change the disk used by mysql so that it is not deployed on the same physical machine as the application, or use an independent disk.
3. Change item: innodb_flush_log_at_trx_commit = 2