MySQL memory and virtual memory optimization settings

In order to install the MySQL environment test, installed after the discovery started MySQL occupied a large amount of virtual memory, up to 800 trillion. Online search for a bit, get expert pointing my.ini. I don't see any details anymore. Had to open My.ini, although the English poor point, but how many m is still visible ^-^

The following changes are made:

innodb_buffer_pool_size=576m->256m InnoDB engine buffer takes the big head, the first is to take it to the axe
query_cache_size=100m->16m Query Cache
tmp_table_size=102m->64m temporary table size
key_buffer_size=256m->32m

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

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 of the proposed configuration, the novice in the setting can also refer to.
MySQL database server My.ini optimized for 2G memory (My.ini)

2G memory, low-profile, high-quality settings, test Special:

table_cache=1024 the larger the physical memory, the greater the setting. Default is 2402, set to 512-1024 best
innodb_additional_mem_pool_size=8m defaults to 2M
Innodb_flush_log_at_trx_commit=0 wait until the innodb_log_buffer_size queue is full, then the unified storage, the default is 1
innodb_log_buffer_size=4m defaults to 1M
Innodb_thread_concurrency=8 Your server CPU is set to a few, the default is 8
key_buffer_size=256m default is 218 to 128 best
tmp_table_size=64m default is 16M to 64-256 most hanging
read_buffer_size=4m defaults to 64K
read_rnd_buffer_size=16m defaults to 256K
sort_buffer_size=32m defaults to 256K
max_connections=1024 defaults to 1210

Test One:

table_cache=512 or 1024
Innodb_additional_mem_pool_size=2m
Innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=1m
Innodb_thread_concurrency=8 Your server CPU is set to a few, the default is 8
key_buffer_size=128m
tmp_table_size=128m
read_buffer_size=64k or 128K
read_rnd_buffer_size=256k
sort_buffer_size=512k
max_connections=1024

Test Two:

table_cache=512 or 1024
Innodb_additional_mem_pool_size=8m
Innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=4m
Innodb_thread_concurrency=8
key_buffer_size=128m
tmp_table_size=128m
read_buffer_size=4m
read_rnd_buffer_size=16m
sort_buffer_size=32m
max_connections=1024

So so:

table_cache=512
Innodb_additional_mem_pool_size=8m
Innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=4m
Innodb_thread_concurrency=8
key_buffer_size=128m
tmp_table_size=128m
read_buffer_size=4m
read_rnd_buffer_size=16m
sort_buffer_size=32m
max_connections=1024

Tested. There is no special case, it is best to use the default.

2G memory, multi-station, pressure-resistant settings, best:

table_cache=1024 the larger the physical memory, the greater the setting. Default is 2402, set to 512-1024 best
innodb_additional_mem_pool_size=4m defaults to 2M
Innodb_flush_log_at_trx_commit=1
(set to 0 to wait until the innodb_log_buffer_size queue is full before the unified storage, the default is 1)
innodb_log_buffer_size=2m defaults to 1M
Innodb_thread_concurrency=8 Your server CPU is set to several, the recommended default is generally 8
key_buffer_size=256m default is 218 to 128 best
tmp_table_size=64m default is 16M to 64-256 most hanging
read_buffer_size=4m defaults to 64K
read_rnd_buffer_size=16m defaults to 256K
sort_buffer_size=32m defaults to 256K
max_connections=1024 defaults to 1210
thread_cache_size=120 defaults to 60
query_cache_size=64m

10 parameters for optimizing MySQL database performance

(1), Max_connections:
The number of simultaneous customers allowed. Increase this value to increase the number of file descriptors required by the mysqld. This number should be increased, otherwise you will often see too many connections error. The default value is 100, I change it to 1024.

(2), Record_buffer:
Each thread that makes a sequential scan allocates a buffer of that size for each table it scans. If you do a lot of sequential scans, you may want to increase the value. The default value is 131072 (128k), I change it to 16773120 (16m)

(3), Key_buffer_size:
The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used for the index block, increasing the index (for all reads and multiple writes) that can be better processed, to the extent that you can afford it. If you make it too big, the system will start to change pages and really become slow. The default value is 8388600 (8m), my MySQL host has 2GB memory, so I changed it to 402649088 (400MB).

4), Back_log:
The number of connections required for MySQL to be available. When the primary MySQL thread gets very many connection requests in a very short time, this works, and then the main thread takes some time (albeit very short) to check the connection and start a new thread.
The Back_log value indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops answering a new request. Only if you expect to have a lot of connections in a short period of time, you need to increase it, in other words, the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limits on this queue size. Attempting to set a limit of back_log above your operating system will be invalid.
When you look at your host process list, you find a lot of 264084 | Unauthenticated user | xxx.xxx.xxx.xxx | null | Connect | null | Login | Null to connect the process, it is necessary to increase the value of Back_log. The default value is 50, I change it to 500.

(5), Interactive_timeout:
The number of seconds the server waits for an action on an interactive connection before shutting it down. An interactive customer is defined as a customer who uses the client_interactive option for Mysql_real_connect (). The default value is 28800, I change it to 7200.

(6), Sort_buffer:
Each thread that needs to be sorted allocates a buffer of that size. Increase this value to accelerate the order by or group by operation. The default value is 2097144 (2m), and I change it to 16777208 (16m).

(7), Table_cache:
The number of tables opened for all threads. Increasing this value can increase the number of file descriptors required by the mysqld. MySQL requires 2 file descriptors for each unique open table. The default value is 64, I change it to 512.

(8), Thread_cache_size:
The number of threads that can be reused for saving in. If there is, a new thread is obtained from the cache, and if there is space when disconnected, the customer's line is placed in the cache. If there are many new threads, the value of this variable can be increased in order to improve performance. By comparing the variables of the connections and threads_created states, you can see the effect of this variable. I set it to 80.

(9) MySQL search function
Search with MySQL in order to be able to search in Chinese with no case
Specify--default-character-set=gb2312 only when starting mysqld

(10), Wait_timeout:
The number of seconds the server waits for an action on a connection before shutting it down. The default value is 28800, I change it to 7200.

Note: The parameters can be adjusted by modifying the/ETC/MY.CNF (Windows My.ini) file and restarting the MySQL implementation. This is a relatively cautious work, the above results are just some of my views, you can be based on the hardware of your own host (especially memory size) to further modify.