• notice
  • Congratulations on the launch of the Sought Tech site

Mysql occupied memory larger and CPU too high test and solution

After the change is as follows:

innodb_buffer_pool_size=576M -> 256M InnoDB engine buffer takes up the bulk, the first thing to do is to use 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 installation directory of mysql: my-huge.ini, my-large.ini , My-medium.ini...These are the recommended configurations based on the memory size, and novices can also refer to them when setting them up.
2G memory MYSQL database server my.ini optimization (my.ini)
2G memory, aiming at fewer stations, high-quality settings, special test:
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 default is 2M
innodb_flush_log_at_trx_commit=0 Wait until the innodb_log_buffer_size queue is full before storing together, the default is 1
innodb_log_buffer_size=4M default is 1M
innodb_thread_concurrency=8 If your server has a few CPUs, set it to a few, the default is 8
key_buffer_size=256M The default is 218, adjust to 128 best
tmp_table_size=64M The default is 16M, adjusted to 64-256 the most hanging
read_buffer_size=4M defaults to 64K
read_rnd_buffer_size=16M default is 256K
sort_buffer_size=32M defaults to 256K
max_connections=1024 The default is 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 If your server has a few CPUs, set it 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

General:
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 is no special case, it is best to use the default.
2G memory, for multiple stations and compression-resistant settings, the 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 default is 2M
innodb_flush_log_at_trx_commit=1
(Set to 0 is to wait until the innodb_log_buffer_size queue is full before storing in a unified way, the default is 1)
innodb_log_buffer_size=2M default is 1M
innodb_thread_concurrency=8 If your server has a few CPUs, set it to a few, it is recommended to use the default generally 8
key_buffer_size=256M The default is 218, adjust to 128 best
tmp_table_size=64M The default is 16M, adjusted to 64-256 the most hanging
read_buffer_size=4M defaults to 64K
read_rnd_buffer_size=16M default is 256K
sort_buffer_size=32M defaults to 256K
max_connections=1024 defaults to 1210
thread_cache_size=120 The default is 60
query_cache_size=64M

Ten parameters for optimizing the performance of mysql database
(1), max_connections:
The number of simultaneous clients 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, I changed it to 1024.
(2), record_buffer:
Each thread that performs 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.The default value is 131072 (128k), I changed it to 16773120 (16m)
(3), key_buffer_size:
The index block is buffered and shared by all threads.key_buffer_size is the size of the buffer used for index blocks, increase it to get better handling of the index (for all reads and multiple rewrites), until you can afford that much.If you make it too large, the system will start to page 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:
Requires the number of connections that mysql can have.This works when the main mysql thread gets a lot of connection requests in a short period of time, and then the main thread spends some time (albeit short) 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 period of 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 limit on the size of this queue.Attempting to set back_log higher than the limit of your operating system will be invalid.
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 processes to be connected, you must 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 a client who 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 be sorted allocates 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 required by mysqld.MySQL requires 2 file descriptors for each unique open 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 obtained from the cache, and if there is space when the connection is disconnected, the client's thread is placed in the cache.If there are many new threads, you can use this variable 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
Use mysql to search, the purpose is to be able to search in Chinese regardless of case
Just specify when starting mysqld --default-character-set=gb2312
(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: The parameter adjustment can be achieved by modifying the/etc/my.cnf file and restarting mysql.This is a more prudent work.The above results are just some of my views.You can modify it further according to the hardware situation of your own host (especially the memory size).

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

atorvastatin 40mg tablet & lt;a href="https://lipiws.top/"& gt;lipitor 10mg brand& lt;/a& gt; atorvastatin 40mg ca

Yhnbbd

2024-03-07

Leave a Reply

+