MySQL environment variable configuration

Modify the MySQL running parameters, modify the etcmy. cnf file, and adjust the mysql running parameters to take effect after MySQL is restarted. After MySQL 4, some internal variables can be used when MySQL is running.

Modify the MySQL running parameters, modify the/etc/my. cnf file, and modify the mysql running parameters to restart MySQL. After MySQL 4, some internal variables can be used when MySQL is running.

Modify MySQL running parameters and/etc/my. the cnf file adjusts the mysql running parameters and takes effect after MySQL is restarted. After MySQL 4, some internal variables can be set during MySQL running, but the restart of mysql becomes invalid.
Mysqld Program-directory and file
Basedir = path # Use the given directory as the root directory (installation directory ).
Datadir = path # Read database files from a given directory.
Pid-file = filename # specify a file for the mysqld program to store the process ID (only applicable to UNIX/Linux systems );

[Mysqld]
Socket =/tmp/mysql. sock # specifies a socket file for local communication between the MySQL client program and the server (/var/lib/mysql. sock file by default in Linux)
Port = 3306 # specify the port on which MsSQL listens
Key_buffer = 384 M # key_buffer is the buffer size used for index blocks. You can add indexes that can be better processed (for all reads and multiple writes ).
The index block is buffered and shared by all threads. The size of key_buffer depends on the memory size.
Table_cache = 512 # number of tables opened for all threads. Increase this value to increase the number of file descriptors required by mysqld. Avoid overhead caused by frequent data table opening.
Sort_buffer_size = 2 M # 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.
Note: 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.
Read_buffer_size = 2 M # buffer size available for read query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to each connection.
Query_cache_size = 32 M # specify the buffer size of MySQL query results
Read_rnd_buffer_size = 8 M # this parameter is used for random read after the row pointer is used for sorting.
Myisam_sort_buffer_size = 64 M # buffer required for sorting the MyISAM table again when it changes
Thread_concurrency = 8 # maximum number of concurrent threads. The value is the number of logical CPUs on the server × 2. If the CPU supports H.T hyper-threading, then × 2
Thread_cache = 8 # Number of reusable cache threads
Skip-locking # Avoid MySQL external locks, reduce the chance of errors, and enhance stability.
[Mysqldump]
Max_allowed_packet = 16 M # maximum possible information packages that can be sent between the server and the client

[Myisamchk]
Key_buffer = 256 M
Sort_buffer = 256 M
Read_buffer = 2 M
Write_buffer = 2 M

Other optional parameters:
Back_log = 384
Specify the number of possible MySQL connections.
When the MySQL main thread receives many connection requests in a short time, this parameter takes effect. The main thread takes a short time to check the connection and start a new thread.
The value of the back_log parameter indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops responding to a new request.
If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listener queue for the incoming TCP/IP connection.
Trying to set back_log to be higher than your operating system limit will be invalid. The default value is 50. We recommend that you set the value to an integer smaller than 512 in Linux.
Max_connections = n
The maximum number of database connections that the MySQL server processes simultaneously (100 by default ). When the limit is exceeded, the Too worker connections error is reported.
Key_buffer_size = n
Used to store the RMA value of the index block (the default value is 8 Mb) and add an index that can be better processed (for all reads and writes)
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)
Wait_timeout:
The number of seconds that the server waits for action on a connection before closing it.
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. You can change it to 3600.
Skip-name-resolve
Prohibit MySQL from performing DNS resolution on external connections. This option can eliminate the time for MySQL to perform DNS resolution.
However, if this option is enabled, IP addresses are required for all remote host connection authorizations. Otherwise, MySQL cannot process connection requests normally!
Log-slow-queries = slow. log
Record slow queries, and then optimize slow queries one by one
Skip-innodb
Skip-bdb
Disable unnecessary table types. Do not add this type if necessary.


#> 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, so you can consider not to use the Query Buffer;
If the value of Qcache_free_blocks is very large, it indicates that there are many fragments in the buffer.


######################################## ##
###### Max_allowed_packet ######
######################################## ##

The communication information package is a single SQL statement sent to the MySQL server or a single row sent to the client.
The maximum possible information package that can be sent between the MySQL 5.1 server and the client is 1 GB.
When the MySQL client or mysqld server receives an information packet greater than the value of max_allowed_packet, the "information packet is too large" error is sent and the connection is closed. For some clients, if the communication information package is too large, the "lost connection to MySQL Server" error can be returned during query.
Both the client and server have their own max_allowed_packet variable. Therefore, if you want to process a large information package, you must add the variable on the client and server.
If you are using a mysql client, the default value of the max_allowed_packet variable is 16 MB. To set a large value, you can start mysql in the following ways:
Mysql> mysql -- max_allowed_packet = 32 M
It sets the size of the information package to 32 MB.
The default max_allowed_packet value of the server is 1 MB. If the server needs to process a large query, you can add this value (for example, if you want to process a large BLOB column ). For example, to set this parameter to 16 MB, start the server in the following ways:
Mysql> mysqld -- max_allowed_packet = 16 M

You can also use the option file to set max_allowed_packet. To set this variable of the server to 16 MB, add the following content to the option file:
[Mysqld]
Max_allowed_packet = 16 M
It is safe to increase the value of this variable because additional memory is allocated only when necessary. For example, if you issue a long query or mysqld must return a large result, mysqld will allocate more memory. The small default value of this variable is a preventive measure to capture error packets between the client and the server, and ensure that memory overflow is not caused by accidental use of large information packets.
If you use a large BLOB value and do not grant mysqld the permission to access enough memory for query processing, you may also encounter a strange problem related to the big information package. If this is suspected, add ulimit-d 256000 in the mysqld_safe script and restart mysqld.

######################################## ##
##### How to enable and disable a database table in MySQL #####
######################################## ##

Table_cache, max_connections, and max_tmp_tables affect the maximum number of files that the server keeps open. If you add one or two of these values, you can encounter a strong limit on the number of file descriptors opened by each process in your operating system. However, you can add this restriction to many systems. Ask your OS document to find out how to do this, because the methods for changing the limits vary greatly from system to system.
Table_cache is related to max_connections. For example, for 200 open connections, you should set a table buffer to at least 200 * n. Here n is the maximum number of tables in a join.

Show variables like '% slow %'

Max_used_connections/max_connections = 0.85 --- Max_connections

Key_cache_miss_rate = key_reads/key_read_requests 0.1% -- Key_buffer_size is valid for the myisam table

Key_blocks_used/(key_blocks_used + key_blocks_used) -- Key_buffer_size is valid for the myisam table

Created_tmp_disk_tables/Created_tmp_tables <= 0.25 -- corresponding variable: tmp_table_size, max_heap_table_size

Open_tables is large in quantity. You can adjust the table_cache parameter.

Thread_created is too large. Please configure thread_cache_size =

Query cache)

Qcache_free_blocks: Number of adjacent memory blocks in the cache. A large number of fragments may exist. Flush query cache sorts the fragments in the CACHE to obtain an idle block.

Qcache_free_memory: idle memory in the cache.

Qcache_hits: increases when a query hits the cache.

Qcache_inserts: It increases every time a query is inserted. By dividing the number of hits by the number of inserts, This is the ratio of no hits.

Qcache_lowmem_prunes: the cache has insufficient memory and must be cleaned up to provide more space for queries. It would be better to look at this number for a long time; if this number continues to grow, it may indicate that the fragmentation is very serious, or the memory is very small. (The free_blocks and free_memory above can tell you what the situation is)

Qcache_not_cached: the number of queries that are not suitable for caching. It is generally because these queries are not SELECT statements or use functions such as now.

Qcache_queries_in_cache: number of queries (and responses) cached currently.

Qcache_total_blocks: Number of cached blocks.

Query cache parameter configuration:

Query_cache_limit | 2097152 | queries exceeding this size are not cached.

| Query_cache_min_res_unit | 4096 | the minimum size of the cache block is 4 kb, which is too large and may cause fragments and waste.

| Query_cache_size | 203423744 | query cache size

| Query_cache_type | ON | the cache type, which determines the query to be cached. In this example, select SQL _no_cache query is not cached.

| Query_cache_wlock_invalidate | OFF | if another client is performing a write operation on the MyISAM table, if the query is in the query cache, whether to return the cache result or wait until the write operation is complete and then read the table to obtain the result.

Query cache fragmentation rate = Qcache_free_blocks/Qcache_total_blocks * 100%
---- If the query cache fragmentation rate exceeds 20%, you can use the flush query cache to sort out the CACHE fragmentation, or try to reduce query_cache_min_res_unit, if your QUERY involves a small amount of data.

Query cache utilization = (query_cache_size-Qcache_free_memory)/query_cache_size * 100%
--- If the query cache utilization is below 25%, the query_cache_size setting is too large and can be appropriately reduced. If the query cache utilization is above 80% and Qcache_lowmem_prunes> 50, the query_cache_size may be small, or too many fragments.

Query cache hit rate = (Qcache_hits-Qcache_inserts)/Qcache_hits * 100%

Sorting usage:

Sort_merge_passes | 29 |

| Sort_range | 37432840 |

| Sort_rows | 9178691532 |

| Sort_scan | 1860569 |

Adjusted parameter: Sort_buffer_size

Open_files: open_files_limit
Suitable settings: Open_files/open_files_limit * 100% <= 75%

Table lock status:
Table_locks_immediate indicates the number of table locks to be released immediately
Table_locks_waited indicates the number of table locks to wait
Table_locks_immediate/Table_locks_waited> 5000, preferably using the InnoDB Engine

Thread_cache_sized:
1G-> 8
2G-> 16
3G-> 32
> 3G-> 64