What should I do if MySQL occupies a very high virtual memory?

This article introduces the solution for MySQL to occupy a very high virtual memory. If you need to learn, refer to this article.

This article introduces the solution for MySQL to occupy a very high virtual memory. If you need to learn, refer to this article.

Mysql consumes 700 mb of memory. After half a day, MYSQL consumes 1.5 GB of memory. After two days of running, mysql consumes 3 GB of memory, including FP. MYSQL has been restarted before, in this way, I set up a scheduled task to automatically restart mysql every morning, but it is useless to restart mysql now. In fact, together with FP, mysql occupies GB of memory, while my server only has 2 GB of physical memory, resulting in extremely slow website operation. What should I do ????

The server configuration is win2003 + PHP5 + MYSQL5 + ZEND3.3 ~~~~~ My. ini

Solution

Solve the high memory usage problem of mySQL
In order to install the mysql environment for testing, it was found that mysql occupied a large amount of virtual memory after the installation, which could reach more than 8 hundred megabytes. I searched for it online and got some tips from my. ini. I have no more details .. I have to open my. ini line by line. Although the English language is a little poor, I still can see how many M ^-^

The changes are as follows:

The Code is as follows:
Innodb_buffer_pool_size = 576 M-> 256 M InnoDB Engine Buffer occupies a large proportion, the first is to use it to open the knife Query_cache_size = 100 M-> 16 M query Cache Tmp_table_size = 102 M-> 64 M temporary table size Key_buffer_size = 256 m-> 32 M

After the mysql service is restarted, the virtual memory is reduced to less than 200.

In addition, mysql installation directory has a few files: my-huge.ini, my-large.ini, my-medium.ini... these are based on the memory size for the recommended configuration, when the novice in the setting can also refer to it.

2 GB memory MYSQL database server my. ini optimization (my. ini)

2 GB memory, for low-site, high-quality settings, test features:

The Code is as follows:

Table_cache = 1024 the larger the physical memory is, the larger the setting will be. The default value is 2402. The optimal value is adjusted to 512-1024. Innodb_additional_mem_pool_size = 8 M; default value: 2 M Innodb_flush_log_at_trx_commit = 0 wait until innodb_log_buffer_size is full and then store it in a unified manner. The default value is 1. Innodb_log_buffer_size = 4 M; default value: 1 M Innodb_thread_concurrency = 8 The number of CPUs on your server is set to 8 by default. Key_buffer_size = 256 M. The default value is 218 to 128. Tmp_table_size = 64 M. The default value is 16 M and the value is adjusted to 64-256. Read_buffer_size = 4 M. The default value is 64 K. Read_rnd_buffer_size = 16 M by default ......

Test

2 GB memory, which is suitable for multiple sites and compression-type settings. The best solution is:

The Code is as follows:

Table_cache = 1024 the larger the physical memory is, the larger the setting will be. The default value is 2402. The optimal value is adjusted to 512-1024. Innodb_additional_mem_pool_size = 4 M; default value: 2 M Innodb_flush_log_at_trx_commit = 1 (If it is set to 0, innodb_log_buffer_size is stored after the queue is full. The default value is 1) Innodb_log_buffer_size = 2 M; default value: 1 M Innodb_thread_concurrency = 8 if you have a few CPUs on your server, set it to a few. We recommend that you use the default value of 8 Key_buffer_size = 256 M. The default value is 218 to 128. Tmp_table_size = 64 M. The default value is 16 M and the value is adjusted to 64-256. Read_buffer_size = 4 M. The default value is 64 K. Read_rnd_buffer_size = 16 M. The default value is 256 K. Sort_buffer_size = 32 M. The default value is 256 K. Max_connections = 1024 the default value is 1210. Thread_cache_size = 120 The default value is 60. Query_cache_size = 64 M

Ten parameters for optimizing mysql database performance:
(1), max_connections
Number of customers allowed simultaneously. Increase the number of file descriptors required by mysqld. This number should be added. Otherwise, you will often see the too connector connections error.

(2), record_buffer
Each thread that performs an ordered scan allocates a buffer of this size to each table it scans. If you perform many sequential scans, you may want to increase the value.

(3), key_buffer_size
The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used for index blocks. You can increase the size of indexes that can be better processed (for all reads and multi-Rewrite) so that you can afford that much. If you make it too large, the system will begin to change pages and it will really slow down.

(4) back_log
The number of connections that mysql can have. When the main mysql thread receives a lot of connection requests in a very short period of time, this works, and then the main thread takes some time (although very short) to check the connection and start a new thread.
The back_log value indicates how many requests can be stored in the stack within a short time before mysql temporarily stops answering new requests. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection. Your operating system has its own limit on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid.
When you observe the process list of your host and find a large number of 264084 | unauthenticated user | xxx. xxx. xxx. xxx | null | connect | null | login | when a null process is to be connected, increase the value of back_log.

(5) interactive_timeout
The number of seconds that the server waits for action on an interactive connection before closing it. An interactive customer is defined as a customer who uses the client_interactive option for mysql_real_connect.

(6) sort_buffer
Each thread that needs to be sorted allocates a buffer of this size. Add this value to accelerate the order by or group by operation.

(7), table_cache
Number of tables opened for all threads. Increase this value to increase the number of file descriptors required by mysqld. Mysql requires two file descriptors for each unique opened table.

(8), thread_cache_size
The number of threads that can be reused. If yes, the new thread is obtained from the cache. If there is space when the connection is disconnected, the customer's thread is placed in the cache. If there are many new threads, this variable value can be used to improve performance. By comparing variables in connections and threads_created states, you can see the role of this variable.

(9) mysql search function
Mysql is used for search. It is case-insensitive and can be searched in Chinese.
You only need to specify-default-character-set = gb2312 when starting mysqld

(10), wait_timeout
The number of seconds that the server waits for action on a connection before closing it.
Note: The above results are just some personal opinions. You can further modify them based on the hardware situation of your host (especially the memory size.