Shulou(Shulou.com)06/01 Report--

Max_connections

Variable Scope: Global

Dynamic Variable: Yes

Default: 151 (mysql5.5+)

Meaning: the maximum number of simultaneous client connections allowed

The default value used to be 151 after 100m MySQL 5.5 +, but the default value is not enough for most applications. By observing Max_used_connections

The state variable changes over time. I can tell you if the server connection has a peak at some point in time. If this value reaches max_connections, the client has been rejected at least once.

Suggested value: 500 + (set to 500 or more if you think there are 300 or more connections under normal circumstances)

Thread_cache_size

Variable Scope: Global

Dynamic Variable: Yes

Default: 0 (mysql5.6.7-)

Meaning: how many threads should be cached for reuse

The default value is mysql5.6.8+ (autosized), which is obtained according to the following formula: 8 + (max_connections / 100), and its upper limit is 100. By setting this variable, you can calculate a valid value by observing the activity of the server for a period of time.

Observe the Threads_connected state variable and find its maximum and minimum values in general. For example, if the Threads_connected state changes from 150 to 175, you can set the thread cache to 75. But you don't have to set it very big, because you keep a lot of it.

Idle threads waiting for a connection are of no real use. You can also observe the change of Threads_created state over time. If this value is large or growing, this is another clue that you may need to increase the thread_cache_size variable.

Threads_cached to see how many threads are already in the cache.

Suggested value: 50-100

Table_open_cache

Variable Scope: Global

Dynamic Variable: Yes

Default: 400 (mysql5.6.7-)

Meaning: number of tables opened by all threads

From the official documentation, we can see that the default value of 2000 from the beginning of MySQL5.6.8+ can simply determine that the original default value is not enough. You can check this variable by observing the value of Opened_ tables and their changes over time. If you see a large value of Opened_tables

And you don't execute FLUSH TABLES very often (execute its command to force all tables to close and open again), so maybe you should increase the value of this variable.

Suggested value: 4096 (there is another saying: this value is set from 10 times max_connections)

Open_files_limit

Variable Scope: Global

Dynamic Variable: No

Default: 0 (mysql5.6.7-)

Meaning: the number of files that the operating system allows the mysqld service to open.

Its default value is mysql5.6.8+ (autosized), which is obtained according to the following formula:

1) 10 + max_connections + (table_open_cache * 2)

2) max_connections * 5

3) open_files_limit value specified at startup, 5000 if none

Be aware that two file handles are required for each MyISAM table to open; the connection to each client is also a file handle. The value of a valid open_files_limit is based on the value specified at system startup that is associated with max_connections,table_open_cache.

Recommended value: 65535 (its value is the most secure in most operating systems)

Table_definition_cache

Variable Scope: Global

Dynamic Variable: Yes

Default: 400 (mysql5.6.7-)

Meaning: number of cached table definitions (files ending in .frm)

Its default value is mysql5.6.8+ (autosized), and its upper limit is 2000 according to the following formula: 400 + (table_open_cache / 2). You can often set the table_definition_cache high enough to cache all table definitions.

Unless there are tens of thousands of tables, this may be the easiest way.

Suggested value: based on the number of tables in the real database (for example, the database instance has 1000 tables, which can be set to 1000 +)

Back_log

Variable Scope: Global

Dynamic Variable: No

Default: 50 (mysql5.6.5-)

Meaning: how many request links can be in the stack waiting to be processed in a very short time.

The default value is mysql5.6.6+ (autosized), and the upper limit of 50 + (max_connections / 5) is 900 according to the following formula.

If there are a large number of connections per second, you can increase its value. Its value is associated with the TCP/IP link of OS and the value of the kernel parameter net.ipv4.tcp_max_syn_backlog, and the value of back_log cannot be greater than its value.

Suggested value: 2048

Max_allowed_packet

Variable Scope: Global

Dynamic Variable: Yes

Default: 1MB (mysql5.6.5-)

Meaning: this setting prevents the server from sending packets that are too large, and also controls how large packets are accepted.

Its default value is 4MB in mysql5.6.6+, which may be too small, but it may be dangerous to set it too large. If the setting is too small, sometimes there will be problems with replication, usually showing that the standby database cannot receive the replication data sent by the master database. Using mysql and mysqldump

Client programs can specify the size of their values.

Suggested value: 16MB

Max_connect_errors

Variable Scope: Global

Dynamic Variable: Yes

Default: 100 (mysql5.6.6+)

Meaning: maximum number of connection errors

If sometimes the network has a brief fuss, or there is an error in the application configuration, or if there is another problem, such as permissions, constantly trying to link in a short period of time, the client may be blacklisted and will not be able to connect until the host cache is refreshed again (FLUSH HOSTS).

The default setting for this option is too small and can easily cause problems. You may want to increase this value, in fact, if you know that the server is fully resistant to brute force attacks, you can set this value very large to effectively disable the host blacklist. This option is

The so-called can prevent violent cracking.

Suggested value: 1000000 (its value is the recommended value given by Percona, but its host should be determined to have the ability to resist brute force attacks)

Skip_name_resolve

Variable Scope: Global

Dynamic Variable: No

Default: OFF

Meaning: DNS lookup

This option disables another network-related and authentication trap: DNS lookup. DNS is a weak link in the process of MySQL connection. When connecting to the server, it attempts to determine the hostname of the host that is connected and used as part of the authentication credentials.

(that is, your credentials are username, hostname, and password, not just username and password.) but to verify the source of the host, the server needs to perform forward and reverse lookups of DNS. It would be tragic if there was something wrong with DNS, which is inevitable at some point in time.

To avoid this, we strongly recommend that you set this option and turn off DNS lookup during validation, which is fast and secure.

Suggested value: ON

Log_bin

Variable Scope: Global

Dynamic Variable: No

Meaning: whether to enable binlog

Turn on this option to support replication and point-in-time recovery.

Suggested value: set its value to mysql-bin to avoid its default generated file name (that is, independent of the host name)

Sync_binlog

Variable Scope: Global

Dynamic Variable: Yes

Default: 0

Meaning: controls how MySQL flushes binary logs to disk

The default value is 0, which means that the MySQL is not refreshed, and the operating system decides when to refresh the cache to the persistent device. If this value is greater than 0, it specifies the number of binary log writes between flushing to disk

If autocommit is set, each separate statement is written once, otherwise a transaction is written at a time. If sync_binlog is not set to 1, the binary log may not synchronize transaction data after a crash. This can easily lead to replication interruptions.

And makes it impossible to recover in time. In any case, you can set this value to 1 for security. This will require MySQL to refresh the binary log and transaction log files to different locations. This may require disk seek, which is a relatively slow operation.

Suggested value: 1

Expire_log_days

Variable Scope: Global

Dynamic Variable: Yes

Default: 0

Meaning: the server cleans up the old binary log after the specified number of days

If binary logging is enabled, this option should be turned on to allow the server to clean up old binary logs after a specified number of days. If not enabled, the server will eventually run out of space, causing the server to get stuck or crash.

Suggested value: 7: 14

Tmp_table_size and max_heap_table_size

These two settings control how much memory can be used by temporary tables that use the Memory engine. If the size of the implicit memory temporary table exceeds the values set by these two settings, it will be converted to an MyISAM table, so its size can continue to grow. (an implicit temporary table is a form that is not created by yourself

Instead, the server creates a table that holds the intermediate results of executing the query in the row.) you should simply set the two variables to the same value. But beware that this variable is too large. Temporary tables are best kept in memory, but if they are propped up, let them actually use them.

The disk is better, otherwise it may overflow the server memory. Assuming that query statements do not create large temporary tables (which can usually be avoided by reasonable indexing and query design), make these variables larger to avoid converting memory temporary tables into disk temporary tables. This process can

I saw it in SHOW PROCESSLIST. The use of temporary tables can be monitored by the state variables Created_tmp_tables and Created_tmp_disk_tables.

Suggested value: set two variables to the same value (this size depends on your own SQL query level and SQL statement optimization)

Query_cache_size

Variable Scope: Global

Dynamic Variable: Yes

Default: 0 (mysql5.6.7-)

Meaning: memory size for caching query results

The total memory space used by the query cache, in bytes. This value must be an integral multiple of 1024, otherwise the actual data allocated by MySQL will be slightly different from what you specify.

Suggested values:

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.