Test and solution for MySQL's large memory usage and high CPU usage

After the changes:

innodb_buffer_pool_size=576M ->256M InnoDB engine buffer accounts for the majority of the memory, so the first thing to do is to cut 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 dropped below 200.

In addition, there are several files in 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 them when setting up.
2G memory MYSQL database server my.ini optimization (my.ini)
2G memory, for fewer stations, high-quality settings, test features:
table_cache=1024 The larger the physical memory, the larger the setting. The default is 2402, and it is best to adjust it to 512-1024
innodb_additional_mem_pool_size=8M Default is 2M
innodb_flush_log_at_trx_commit=0 Wait until the innodb_log_buffer_size queue is full before storing it uniformly. The default value is 1
innodb_log_buffer_size=4M Default is 1M
innodb_thread_concurrency=8 Set it to the number of CPUs on your server. The default is 8
key_buffer_size=256M The default is 218, and it is best to adjust it to 128
tmp_table_size=64M The default is 16M, adjust to 64-256 for maximum
read_buffer_size=4M default is 64K
read_rnd_buffer_size=16M default is 256K
sort_buffer_size=32M default is 256K
max_connections=1024 default is 1210

Test 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 Set it to the number of CPUs on your server. 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 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

generally:
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. If there is no special situation, it is best to use the default.
2G memory, for multiple stations, stress-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 it to 512-1024
innodb_additional_mem_pool_size=4M Default is 2M
innodb_flush_log_at_trx_commit=1
(Setting it to 0 means waiting until the innodb_log_buffer_size queue is full before storing it uniformly. The default value is 1)
innodb_log_buffer_size=2M Default is 1M
innodb_thread_concurrency=8 Set it to the number of CPUs on your server. The default value is usually 8.
key_buffer_size=256M The default is 218, and it is best to adjust it to 128
tmp_table_size=64M The default is 16M, adjust to 64-256 for maximum
read_buffer_size=4M default is 64K
read_rnd_buffer_size=16M default is 256K
sort_buffer_size=32M default is 256K
max_connections=1024 default is 1210
thread_cache_size=120 default is 60
query_cache_size=64M

Ten parameters to optimize MySQL database performance
(1) max_connections:
The number of simultaneous clients allowed. Increasing this value increases the number of file descriptors that mysqld requires. This number should be increased, otherwise, you will often see too many connections errors. The default value is 100, I changed it to 1024.
(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 might want to increase this value. The default value is 131072 (128k), 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, increase it for better handling of indexes (for all reads and multiple writes), as much as you can afford. If you make it too big, the system will start paging and really slow down. The default value is 8388600 (8M), my MySQL host has 2GB of memory, so I changed it to 402649088 (400MB).
4) back_log:
The number of connections that mysql is allowed to have. This works when the main mysql thread gets a lot of connection requests in a short period of time, then the main thread takes some time (albeit short) to check the connection and start a new thread.
The back_log value indicates how many requests can be stacked 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 sets 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 back_log higher than your operating system's limit will have no effect.
When you observe your host process list and find a large number of 264084 | unauthenticated user | xxx.xxx.xxx.xxx | null | connect | null | login | null waiting to connect processes, you should increase the value of back_log. The default value is 50, I changed it to 500.
(5) interactive_timeout:
The number of seconds the server waits for action on an interactive connection before closing it. An interactive client is defined as one that uses the client_interactive option to mysql_real_connect(). The default value is 28800, I changed it to 7200.
(6) sort_buffer:
Each thread that needs to do a sort is allocated a buffer of this size. Increasing this value speeds up order by or group by operations. The default value is 2097144 (2m), I changed it to 16777208 (16m).
(7) table_cache:
The number of open tables for all threads. Increasing this value can increase the number of file descriptors that mysqld requires. mysql requires 2 file descriptors for each uniquely opened table. The default value is 64, I changed it to 512.
(8) thread_cache_size:
The number of threads stored in that can be reused. If so, the new thread is taken from the cache, and when disconnecting, if there is space, the client's thread is placed in the cache. If there are many new threads, this variable value can be increased to improve performance. By comparing the connections and threads_created state variables, you can see the role of this variable. I set it to 80.
(9) mysql search function Use mysql to search in a case-insensitive manner and to search in Chinese. Just specify --default-character-set=gb2312 when starting mysqld.
(10) wait_timeout:
The number of seconds the server waits for action on a connection before closing it. The default value is 28800, I changed it to 7200.

Note: Parameters can be adjusted by modifying the /etc/my.cnf file and restarting mysql. This is a relatively cautious job, and the above results are just my opinions. You can make further modifications based on the hardware conditions of your own host (especially the memory size).