Detailed example of how to solve the problem that MySQL takes up too much memory

This article brings you relevant knowledge about mysql. It mainly sorts out the related problems of excessive memory usage and uses two systems, Windows and CentOS7 respectively, to solve this problem. Let’s take a look at it together, I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

Preface

For some petty bourgeoisie players, the number of servers and Memory is often very limited. For example, my personal server configuration is 2 cores 4G5M.

4G memory is really not big for Java players. If you open a few middlewares, your own microservices are really crowded, and then you have to deal with the big enemy of MySQL. MySQL on my local machine only occupies a few MB of memory (although I don’t use it very much, MySQL on this machine is indeed open):

And the server requires It occupies 400M, so it doesn’t have much throughput. It’s just for fun. The memory usage is really too much. . .

Solution

I learned that the memory usage of MySQL can be reduced by modifying the configuration, so I tried it and recorded it. Since I have two servers at the same time, one is installed with Windows Service 2016, and the other is installed with CentOS7. The situation on both servers is similar, so I will make two records at the same time for your reference.

Find the configuration file

Windows Service 2016

The default location of the configuration file is C:\ProgramData\ MySQL\MySQL Server 8.0\my.ini, if there are any modifications, find them based on your actual situation.

CentOS7

The default path of the configuration file is /etc/my.cnf, if modified , you can find it according to your actual situation.

Modify the configuration file

You can use Ctrl F to search on Windows and ‘/key’ on Linux. If you don’t know how, you can refer to Baidu.

There are three things we need to find and modify:

1. Find table_definition_cache, we modified it to 400

   official Interpreted as: The number of table definitions (from .frm files) that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up table opening. Unlike the ordinary table cache, the table definition cache takes up less space and does not use file descriptors. The minimum and default values ​​are both 400.

2. Find table_open_cache, we changed it to 256

   Every time MySQL opens a table, it will read some data into the table_open_cache cache. When MySQL is in When the corresponding information cannot be found in this cache, it will be read from the disk.

   The official explanation is: the number of open tables for all threads. Increasing this value increases the number of file descriptors required by mysqld. Therefore, you must ensure that the number of files allowed to be opened is set to at least 4096 in the variable "open files limit" in the [mysqld safe] section.

3. Find performance_schema and change it to off

   If you cannot find this, add performance_schema = off directly in the appropriate place That’s it.

   Used to monitor the resource consumption, resource waiting, etc. of the MySQL server during a lower-level operation. After closing, it can save costs and will not change the behavior of the server.

Save and exit after modification.

Restart MySQL

Windows Service 2016

Start the console as an administrator, enter net stop mysql, and then Enter net start mysql.

After restarting, it still occupies 62M.

CentOS7

Enter service mysqld restart on the console to restart.

If that doesn’t work, you can try the /etc/init.d/mysqld restart command.

After restarting, it occupies 92M, which is more than the Windows one.

Postscript

This method can indeed reduce the memory usage of mysql, but I just reduce the performance in exchange for memory. If the throughput is If the requirements are relatively high, it certainly cannot be modified directly like this. It must be adjusted according to actual requirements.

Recommended learning: mysql video tutorial

The above is the detailed content of Detailed example of how to solve the problem that MySQL takes up too much memory. For more information, please follow other related articles on the PHP Chinese website!