Test and solution of MySQL's large memory occupation and CPU's high memory occupation

The changes are as follows:

innodb_buffer_pool_size=576M - > 256M InnoDB engine buffer takes up a big part, and the first thing is to take it
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.

In addition, there are several files under the mysql installation directory: my-huge. ini, my-large. ini, my-medium. ini... These are recommended configurations based on memory size, and novices can also refer to 1 when setting.
MYSQL database server my. ini optimization with 2G memory (my. ini)
2G memory, aiming at the setting of few stations and high quality, the test is special:
table_cache=1024 The larger the physical memory, the larger the setting. The default is 2402, and it is best to adjust to 512-1024
innodb_additional_mem_pool_size=8M defaults to 2M
innodb_flush_log_at_trx_commit=0 Wait until innodb_log_buffer_size queue is full before saving in 1, default is 1
innodb_log_buffer_size=4M defaults to 1M
innodb_thread_concurrency=8 Your server CPU is set to as many as you have, and the default is 8
key_buffer_size=256M defaults to 218 to 128 best
tmp_table_size=64M defaults to 16M to 64-256
read_buffer_size=4M defaults to 64K
read_rnd_buffer_size=16M Defaults to 256K
sort_buffer_size=32M Default to 256K
max_connections=1024 Default to 1210

Experiment 1:
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 as many as you have, and 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

Experiment 2:
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

1 like:
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

After testing. There are no special circumstances, it is better to use the default.
2G memory, for multi-station, pressure-resistant settings, best:
table_cache=1024 The larger the physical memory, the larger the setting. The default is 2402, and it is best to adjust to 512-1024
innodb_additional_mem_pool_size=4M defaults to 2M
innodb_flush_log_at_trx_commit=1
(Setting it to 0 means waiting until the innodb_log_buffer_size queue is full before saving it in 1, and the default is 1)
innodb_log_buffer_size=2M defaults to 1M
innodb_thread_concurrency=8 Your server CPU is set to as many as you have. It is recommended to use the default 1 as 8
key_buffer_size=256M defaults to 218 to 128 best
tmp_table_size=64M defaults to 16M to 64-256
read_buffer_size=4M defaults to 64K
read_rnd_buffer_size=16M Default to 256K
sort_buffer_size=32M Default to 256K
max_connections=1024 Default to 1210
thread_cache_size=120 Default to 60
query_cache_size=64M

10 Parameters for Optimizing the Performance of mysql Database
(1), max_connections:
The number of simultaneous customers allowed. Increasing this value increases the number of file descriptors required by mysqld. This number should be increased, otherwise you will often see too many connections errors. The default value is 100, so I changed it to 1024.
(2), record_buffer:
Each thread that performs 1 sequential scan allocates 1 buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value. The default value is 131072 (128k), and I changed it to 16773120 (16m)
(3), key_buffer_size:
Index blocks are buffered and shared by all threads. key_buffer_size is the buffer size used for index blocks, increasing it to get better handling of indexes (for all reads and multiple rewrites) to the point where you can afford that much. If you make it too big, the system will start to change pages and really slow down. The default value is 8388600 (8m), and my mysql host has 2gb memory, so I changed it to 402649088 (400mb).
4), back_log:
The number of connections required for mysql. This works when the main mysql thread gets a lot of connection requests in a very short time, and then the main thread takes some time (albeit a short time) to check the connection and start a new thread.
The back_log value indicates how many requests can be stored on the stack in a short time before mysql temporarily stops answering new requests. Only if you expect 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 listening queue for incoming tcp/ip connections. Your operating system has its own limits on this queue size. Attempts to set back_log above your operating system limit will be invalid.
When you look at your host process list and find a large number of processes to be connected 264084 unauthenticated user xxx. xxx. xxx. xxx null connect null login null, you should increase the value of back_log. The default value is 50, so I changed it to 500.
(5), interactive_timeout:
The number of seconds the server waits for action on 1 interactive connection before shutting it down. One interacting customer is defined as one that uses the client_interactive option for mysql_real_connect (). The default value is 28800, so I changed it to 7200.
(6), sort_buffer:
Each thread that needs to sort is allocated 1 buffer of this size. Increasing this value speeds up order by or group by operations. The default value is 2097144 (2m), and I changed it to 16777208 (16m).
(7), table_cache:
The number of tables opened for all threads. Increasing this value increases the number of file descriptors required by mysqld. mysql requires 2 file descriptors for each 1-only open table. The default value is 64, so I changed it to 512.
(8), thread_cache_size:
The number of threads saved in that can be reused. If so, the new thread is fetched from the cache, and when disconnected, the customer's thread is placed in the cache if there is room. If there are many new threads, this variable value can be used to improve performance. You can see the effect of this variable by comparing the variables for the connections and threads_created states. I set it to 80.
(9) Search function of mysql
Using mysql to search, the purpose is to search in Chinese regardless of case
Simply specify--default-character-set=gb2312 when starting mysqld
(10), wait_timeout:
The number of seconds the server waits for action on 1 connection before shutting it down. The default value is 28800, so I changed it to 7200.

Note: The parameters can be adjusted by modifying the/etc/my. cnf file and restarting mysql. This is a more prudent job, and the above results are only my views. You can modify it one step according to the hardware situation of your own host (especially the memory size).