MySQL installation parameters in Linux

Skip-locking
Key_buffer = 384 M
Max_allowed_packet = 3 M
Read_buffer_size = 20 M
Myisam_sort_buffer_size = 128 M
Query_cache_size = 64 M
Thread_concurrency = 8
Set-variable = back_log = 1024
Set-variable = interactive_timeout = 7200
Set-variable = wait_timeout = 7200
Set-variable = key_buffer_size = 16773120
Set-variable = max_connections = 2048
Set-variable = record_buffer = 16773120
Set-variable = sort_buffer = 16773120
Set-variable = table_cache = 512
Set-variable = thread_cache_size = 80
Set-variable = max_connect_errorrs = 2048
Set-variable = log_slow_queries = ON
Set-variable = delay_key_write = ON
Set-variable = join_buffer_size = 16773120
Skip-locking

Avoid external locks of MySQL and reduce the chance of errors to enhance stability.

Key_buffer_size:

The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used for index blocks. You can increase the size of indexes that can be better processed (for all reads and multi-Rewrite) so that you can afford that much. If you make it too large, the system will begin to change pages and it will really slow down. The default value is 8388600 (8 M). My MySQL host has 2 GB of memory, so I changed it to 402649088 (400 MB ).

Max_allowed_packet

Read_buffer_size = 6 M

The buffer size that can be used by the read query operation. The allocated memory corresponding to this parameter is exclusive to each connection! If there are 100 connections, the total size of the actually allocated sort buffer is 100 × 6 = 600 MB. Therefore, we recommend that you set the size of a server with around 4 GB to 6-8 Mb.

Myisam_sort_buffer_size

Query_cache_size

Specify the size of the MySQL Query Buffer. You can run the following command on the MySQL console:

#> Show variables like '% query_cache % ';

#> Show status like 'qcache % ';

# If the Qcache_lowmem_prunes value is very large, it indicates that there is often insufficient buffer;

If the Qcache_hits value is very large, it indicates that the query buffer is used very frequently. If the value is small, it will affect the efficiency, you can consider not to use the Query Buffer; Qcache_free_blocks, if the value is very large, it indicates that there are many fragments in the buffer.

Thread_concurrency

In this example, the server has two physical CPUs, and each physical CPU supports H.T hyper-threading, therefore, the actual value is 4x2 = 8.

Back_log:

The number of connections that MySQL can have. When the main MySQL thread receives a lot of connection requests in a very short period of time, this works, and then the main thread takes some time (although very short) to check the connection and start a new thread. The back_log value indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops answering new requests. Only if you want 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 listener queue for the incoming TCP/IP connection. Your operating system has its own limit on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid. When you observe the process list of your host and find a large number of 264084 | unauthenticated user | xxx. xxx. xxx. xxx | NULL | Connect | NULL | login | when a NULL process is to be connected, increase the value of back_log. The default value is 50. I will change it to 500.

Interactive_timeout

The number of seconds that 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. I will change it to 7200.

Wait_timeout

The number of seconds that the server waits for action on a connection before closing it. The default value is 28800. I will change it to 7200.

Key_buffer_size

The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used for index blocks. You can increase the size of indexes that can be better processed (for all reads and multi-Rewrite) so that you can afford that much. If you make it too large, the system will begin to change pages and it will really slow down. The default value is 8388600 (8 M). My MySQL host has 2 GB of memory, so I changed it to 402649088 (400 MB ).

Max_connections:

Number of customers allowed simultaneously. Increase the number of file descriptors required by mysqld. This number should be added. Otherwise, you will often see the Too connector connections error. The default value is 100. I will change it to 1024.

Record_buffer:

Each thread that performs an ordered scan allocates a buffer of this size to each table it scans. If you perform many sequential scans, you may want to increase the value. The default value is 131072 (128 K). I changed it to 16773120 (16 M)

Sort_buffer:

Each thread that needs to be sorted allocates a buffer of this size. Add this value to accelerate the order by or group by operation. The default value is 2097144 (2 M). I changed it to 16777208 (16 M ).

Table_cache:

Number of tables opened for all threads. Increase this value to increase the number of file descriptors required by mysqld. MySQL requires two file descriptors for each unique opened table. The default value is 64. I changed it to 512.

Thread_cache_size:

The number of threads that can be reused. If yes, the new thread is obtained from the cache. If there is space when the connection is disconnected, the customer's thread is placed in the cache. If there are many new threads, this variable value can be used to improve performance. By comparing variables in Connections and Threads_created states, you can see the role of this variable. I set it to 80.

Max_connect_errors

The number of connection interruption requests allowed. When the number of connection requests exceeds the value specified by max_connect_errors, mysqld considers an error (for example, someone is attempting to insert) and shields further connection requests from the host, until the mysqladmin flush-hosts command is executed or the flush hosts statement is issued.

Log_slow_queries

Enabling the slow query log (slow query log) is very useful for querying problematic traces. It records all long_query_time queries. If needed, you can also record records that do not use indexes.

Join_buffer_size

The buffer size that can be used by the Joint query operation. The same as sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!

Show status like 'qcache % ';

Qcache queries in cache 12737 indicates the number of items currently cached

Qcache inserts 20649006

Qcache hits 79060095 seems that the repeat query rate is quite high

Qcache lowmem prunes 617913 has so many times that the cache is too low

Qcache not cached 189896

Qcache free memory 18573912

Qcache free blocks 5328 seems to be a little big.

Qcache total blocks 30953