How Do I View the Cause of MySQL Memory Usage?

Check out proc meminfo

Tips: "Large memory page" is also known as traditional large page, large page memory, etc. helps Linux to manage virtual memory, the standard memory page is 4kb, here the use of "large memory page" can define a maximum page size of 1GB, during the system boot can use "large memory page" to reserve a part of the memory for the application, this part of the memory is occupied and will never be swapped out of the memory, it will remain there until the configuration is changed. (For details, please see the link below for the official explanation).

So who is allocated such a large page memory?

Lookup: shell> proc sys vm hugetlb shm group

shell> id 27

uid=27(mysql) gid=27(mysql) groups=27(mysql)

The hugetlb shm group file is filled in with the user group ID of the specified large page memory, and here you can see that it is the mysql group id, so since it is given to mysql, why is free equal to total, and mysql still only has more than 20 g of actual used memory?

It turns out that there is a parameter in MySQL that specifically enables large memory pages, and large memory pages in MySQL are called large pages.

Review the MySQL configuration file.

Found that there is indeed a large-page configuration in the configuration file, but for the sake of a disabled state.

After confirming with the business, I did enable MySQL's large page a long time ago, but it was disabled later. After checking this, there is basically a conclusion.

Conclusion. This set of environment previously opened 20000 large memory pages, each page size is 2MB, occupying 40G memory space, for mysql to use, and mysql opened large page, but later when it is not used, only the large page parameter on the mysql side is closed, but it does not actually change the configuration of the host on the large memory page, so in fact, there are still 20000 large memory pages on the host, and they are not in use, this part is idle for a long time, and other programs can't be used.

So mysql is using about 20g memory, and the memory of the entire host is saturated, and then under some conditions, OOM is triggered, causing mysqld to be killed, but there is a mysqld safe daemon on the host, so it is pulled up again, and I saw the occasional unconnection phenomenon at the beginning of the article.

Workaround for high server memory usage:

1. First, sort the process through the task manager to find the program processes that occupy a large amount of memory. Generally, the processes that occupy more memory include w3wp, sqlserver, etc.;

2. The site process w3wp can be connected to the *** memory occupation by the iisapp command in the slag line next to the cmd command. You can reduce the memory usage by setting the ** time, the maximum memory usage value or the pooled process pool, but if you want to ensure the access quality of **, it is recommended to upgrade to a higher model to solve the problem;

3. The database SQL Server can also set the maximum memory occupation through the enterprise manager of the database, but if the first program must be opened to occupy a large memory, the page will be errored, unable to open and other problems will occur after setting;

4. MySQL itself will occupy a large amount of virtual memory, if you don't use MySQL database, you can stop it.