How to troubleshoot the memory and related problems of MySQL

MySQL memory and related troubleshooting is what, for this problem, this article details the corresponding analysis and solution, hoping to help more want to solve this problem of small partners to find a simpler and easier way.

We all know that the database is an IO-intensive application. In order to improve its performance, IO operations that use a large amount of memory instead of files (swap partitions) are the basic principles to ensure the stability and efficiency of the database. So how does the database use memory, how do we view the database memory footprint, and how can we improve its performance by setting the database memory configuration? Take Mysql database (InnoDB engine) as an example to learn about Linux database and memory-related topics. Reads from memory are very fast, and to improve performance we want to put as much of the data set as possible in memory to be efficient. However, Swap partition as a life-saving straw, we must also set up mysql to prevent the sudden lack of memory, mysql services directly killed by OOM. At the same time, mysql swap partition occupancy is also a means for us to measure whether a data is healthy or not. If a database frequently uses swap, it means that we need manual intervention to optimize the database.

memory footprint

Under Linux, we can understand MySQL memory usage by using some shell commands.

First use ps command to check memory usage of mysqld process:

ps -eo size,pid,user,command --sort -size|grep mysqld |awk '{hr=$1/1024;printf(".2f MB",hr)} {for (x=4;x