Shulou(Shulou.com)05/31 Report--

This article introduces you how to optimize virtual memory in mySQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.

After the change is as follows:

Innodb_buffer_pool_size=576M-> 256m InnoDB engine buffer occupies a large amount, so the first thing to do is to operate on 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 to less than 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 beginners can also refer to them when setting them.

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

2G memory, aiming at the setting of few stations and high quality, the test is special:

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

Innodb_additional_mem_pool_size=8M defaults to 2m

Innodb_flush_log_at_trx_commit=0 waits until the innodb_log_buffer_size queue is full before storing it uniformly. Default is 1.

Innodb_log_buffer_size=4M defaults to 1m

Innodb_thread_concurrency=8 your server CPU is set to as many as possible. The default is 8.

Key_buffer_size=256M is best adjusted from 218 to 128 by default.

Tmp_table_size=64M defaults to 16m to 64-256the most hung up.

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 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 possible. 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

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

It has been tested. There are no special circumstances, it is best to use the default.

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

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

Innodb_additional_mem_pool_size=4M defaults to 2m

Innodb_flush_log_at_trx_commit=1

(set to 0, that is, wait until the innodb_log_buffer_size queue is full before storing it uniformly. Default is 1)

Innodb_log_buffer_size=2M defaults to 1m

Innodb_thread_concurrency=8 your server CPU can be set to several. It is recommended that the default is 8.

Key_buffer_size=256M is best adjusted from 218 to 128 by default.

Tmp_table_size=64M defaults to 16m to 64-256the most hung up.

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

Ten 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, and I'll change it to 1024.

(2) record_buffer:

Each thread that performs a sequential scan allocates a buffer of this size to 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), which I changed to 16773120 (16m)

(3) key_buffer_size:

The index block is buffered and shared by all threads. Key_buffer_size is the buffer size for index blocks, increasing it to get better-handled indexes (for all reads and multiple overrides) as much as you can afford. 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 period of time, and then the main thread takes some (albeit 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 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 limits on the queue size. Trying to set the limit that back_log is higher than your operating system will not be valid.

When you look at your list of host processes and find a large number of 264084 | unauthenticated user | xxx.xxx.xxx.xxx | connect | null | login | null processes to be connected, you need to increase the value of back_log. The default value is 50, and I'll change it to 500.

(5) interactive_timeout:

The number of seconds the server waits for action on an interactive connection before closing it. An interactive customer is defined as a customer who uses the client_interactive option for mysql_real_connect (). The default value is 28800, and I'll change it to 7200.

(6) sort_buffer:

Each thread that needs to sort allocates a buffer of that size. Increase this value to speed up order by or group by operations. The default value is 2097144 (2m), which I changed to 16777208 (16m).

(7) table_cache:

The number of open tables 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. The default value is 64, and I'll change 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 the customer's line is placed in the cache if there is space when the connection is disconnected. If there are many new threads, the value of this variable can be used 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) search function of mysql

Search with mysql in order to be able to search in Chinese regardless of case

Just specify-- default-character-set=gb2312 when you start mysqld

(10) wait_timeout:

The number of seconds the server waits for action on a connection before shutting it down. The default value is 28800, and I'll change it to 7200.

Note: the parameters can be adjusted by modifying the / etc/my.cnf (hereinafter referred to as my.ini) file and restarting. This is a more cautious work, and the above results are just some of my views, which you can further modify according to the hardware of your own host (especially the memory size).

On how to optimize virtual memory in mySQL to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.