LAMP---Mysql parameter description

socket =/tmp/mysql.sock # Specifies a socket file for local communication between the MySQL client and server (the default is/var/lib/mysql/mysql.sock file under Linux)
port = 3306 # Specifies the port on which MSSQL listens

Key_buffer = 384M # Key_buffer is the buffer size used for the index block, increasing the index (for all read and multiple writes) that it can get better processing. The index is shared by all threads, and the size of the key_buffer depends on the size of the memory.

1) Key_buffer_size first can be based on the system's memory size set it, probably a reference value: 1G or less memory settings 128m;2g/256m; 4g/384m;8g/1024m;16g/2048m. This value can be checked by checking the status values key_read_requests and key_reads to see if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above status values can be obtained using the show status like ' key_read% '). Note: This parameter value setting is too large to be the overall efficiency of the server down!

Table_cache = 512 # Open the number of tables for all threads. Increasing this value can increase the number of file descriptors required by the mysqld. The overhead of frequently opening data tables can be avoided

2) Table_open_cache open a table, will temporarily put the data inside the table in this part of memory, generally set to 1024 is enough, its size we can measure by this way: if you find Open_tables equals Table_cache, And Opened_tables is growing, you need to increase the value of Table_cache (the above status values can be obtained using show status like ' Open%tables '). Note that you cannot blindly set the Table_cache to a very large value. If set too high, it may cause insufficient file descriptors, resulting in performance instability or connection failures.

sort_buffer_size = 2M # Each thread that needs to be sorted allocates a buffer of that size. Increase this value to accelerate the order by or group by operation.
Note: This parameter corresponds to the allocated memory that is per connection exclusive! If there are 100 connections, the actual allocated total sort buffer size is 100X6=600MB

3) Sort_buffer_size the size of the buffer that can be used when the query is sorted, the allocated memory for that parameter is per-connection exclusive! If there are 100 connections, the actual allocated total sort buffer size is 100x4 = 400MB. Therefore, the recommended setting for a server that has around 4GB is 4-8m.

read_buffer_size = 2M # The buffer size that can be used by the read query operation. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.

4) read_buffer_size The buffer size that can be used by the read query operation. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!

query_cache_size = 32M # Specifies the size of the MySQL query result buffer

read_rnd_buffer_size = 8M # parameters are randomly read after they are sorted using the row pointer.

myisam_sort_buffer_size =64m # MyISAM table changes when the required buffers are reordered

6) Myisam_sort_buffer_size This buffer is primarily used to fix the memory used by the sort index during the table process or the memory size used to sort the index when indexing, generally 4G memory to 64M.

thread_concurrency = 8 # Maximum number of concurrent threads, value is the number of server logical CPUs x2, if the CPU supports H.T Hyper-threading, then X2

9) Thread_concurrency This value is set to twice times the number of CPU cores

Thread_cache = 8 # #缓存可重用的线程数

8) thread_cache_size indicates that the number of threads stored in the cache can be re-used, referring to the following values:1g-> 8 2g-> 3g-> >3G-> 64

skip-locking # avoid MySQL external lock, reduce the chance of error increase stability.

Long_query_time = 1 # #慢查询日志的超时时间

log_slow_queries =/path/to/slow_queries # # Slow query log path, must be used together with the above parameters

This article is from the "you and I Walk" blog, please be sure to keep this source http://ondali.blog.51cto.com/6650368/1613498

LAMP---Mysql parameter description