This article to introduce the solution about MySQL occupy virtual memory is very high, there is a need to learn friends can refer to this article.

mysql wildly eat memory, just start the memory only accounted for 700M, half a day after 1.5G, running 2 days after the accounted for 3G memory, including FP, before restarting MYSQL down, so that I set up a scheduled task every morning to automatically restart mysql, but now restarting mysql is useless, even together with the FP occupies 4.5G memory, while my server Only 2G of physical memory, causing the site to run super slow.

Server configuration is win2003+PHP5+MYSQL5+ZEND3.3 ~~~~~my.ini

Solutions

Solve mySQL occupies a huge memory problem

In order to install the mysql environment test, after installing, I found that after the start of the mysql occupies a large amount of virtual memory I'm not sure how much memory I'm using, but it's a lot. I searched the internet and got some guidance on my.ini, but I didn't see any more details.... I had to open my.ini line by line to chew, although the English poor point, but how many M or see clear ^-^

Changed as follows:

Code as follows

innodb_buffer_pool_size=576M ->256M InnoDB engine buffer accounted for the bulk of the first is to take it to the knife < /p>

query query=576M ->256M InnoDB engine buffer accounts for the bulk of the first is to take it to the knife< /p>

query_cache_size=100M ->16M query cache

tmp_table_size=102M ->64M temporary table size

key_buffer_size=256m ->32M

After restarting mysql service

After restarting the mysql service, the virtual memory drops below 200.

Additionally, there are several files in the mysql installation directory: my-huge.ini, my-large.ini, my-medium.ini.... These are based on the memory size for the recommended configuration, newcomers in the setup can also refer to.

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

2G memory, for the station less, high-quality type of setup, test special:

code as follows

table_cache=1024 The larger the physical memory, the greater the setting. The default is 2402, adjusted to 512-1024 best

innodb_additional_mem_pool_size=8M The default is 2M

innodb_flush_log_at_trx_commit=0 Wait until the innodb_log_buffer_size queue is full and then unified storage. queue is full and then unify the storage, default is 1

innodb_log_buffer_size=4M default is 1M

innodb_thread_concurrency=8 How many CPUs you have on your server is the setting, default is 8

key_buffer_size=256M default is 218 256M default 218 adjusted to 128 best

tmp_table_size=64M default 16M adjusted to 64-256 best

read_buffer_size=4M default 64K

read_rnd_buffer_size=16M default ......

Testing

2G RAM, for more stations, anti-pressure type of settings, the best:

Code as follows

table_cache=1024 The larger the physical memory, the larger the setting. Default is 2402, adjust to 512-1024 best

innodb_additional_mem_pool_size=4M Default is 2M

innodb_flush_log_at_trx_commit=1

(set to 0 is to wait till innodb_ log_buffer_size queue is full and then unified storage, the default is 1)

innodb_log_buffer_size=2M Default is 1M

innodb_thread_concurrency=8 How many CPUs do you have on the server, it's recommended to use the default which is usually 8

key_buffer_size=256M Default=218 Tune to 128 Best

tmp_table_size=64M Default=16M Tune to 64-256 Best

read_buffer_size=4M Default=64K

read_rnd_buffer_size=16M Default=64K

read_rnd_buffer_size=16M Default=64K

read_rnd_buffer_size=16M Default=16K buffer_size=16M default 256K

sort_buffer_size=32M default 256K

max_connections=1024 default 1210

thread_cache_size=120 default 60

query_cache_size=64M

Ten parameters to optimize mysql database performance:

(1), max_connections

Number of simultaneous clients allowed. Increasing this value increases the number of file descriptors requested by mysqld. This number should be increased, otherwise you will often see too many connections errors.

(2), record_buffer

Each thread doing a sequential scan allocates a buffer of this size for each table it scans. If you do a lot of sequential scans, you may want to increase this value.

(3), key_buffer_size

Index blocks are buffered and ****enjoyed by all threads. key_buffer_size is the size of the buffer used for the index block, increase it to get better handling of the index (for all reads and multiple rewrites) to the point where you can afford to have that much. If you make it too big, the system will start to page and really slow down.

(4), back_log

Requires the number of connections mysql can have. This works when the main mysql thread gets a very large number of connection requests in a very short period of time, and then the main thread spends some time (albeit briefly) checking for connections and starting a new thread.

The back_log value indicates how many requests can be stored on the stack in a short period of time before mysql temporarily stops answering new requests. You need to increase it only if you expect a lot of connections in a short period of time, in other words, this value is the size of the queue that listens for incoming tcp/ip connections. Your operating system has its own limits on this queue size. Trying to set back_log higher than your operating system's limit will be ineffective.

When you look at your host process list and see a large number of 264084 | unauthenticated user | xxx.xxx.xxx.xxx | null | connect | null | login | null processes waiting to be connected, it's time to increase the back_log value.

(5), interactive_timeout

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

(6), sort_buffer

A buffer of this size is allocated for each thread that needs to perform a sort. Increasing this value speeds up order by or group by operations.

(7), table_cache

The number of tables to open for all threads. Increasing this value increases the number of file descriptors required by mysqld. mysql requires 2 file descriptors for each unique open table.

(8), thread_cache_size

The number of threads that can be multiplexed and saved in. If there are, new threads are fetched from the cache, and when disconnecting if there is space, the client's threads are placed in the cache. If there are many new threads, this variable can be used to improve performance. You can see what this variable does by comparing the connections and threads_created state variables.

(9), mysql search function

Search with mysql, the purpose is to be able to case-insensitive, but also to search in Chinese

Just start mysqld specify ?default-character-set=gb2312

(10), wait_timeout

(10), wait_timeout timeout

The number of seconds the server waits for action on a connection before closing it.

Note: The above results are also just some personal opinions, you can further modify them according to your own host's hardware (especially memory size).