MySQL parameters and explanations

key_buffer_size: #*** MyISAM unique options. Even if you do not use the MyISAM table, you should still set it to 8-64mbecause it is also used for internal temporary disk tables. This parameter is used to set the index block (indexblocks) cache size, which is shared by all threads, strictly speaking it determines the speed of database indexing processing, especially the speed of index reads.

Max_allowed_packet: #** Common configuration Options , The maximum communication packet size that can be handled is set on either the client or the server side.

Table_open_cache #** Common configuration Options , parameter sets the number of table caches. Each connection comes in with at least one table cache open. Therefore, the size of the Table_cache should be related to the Max_connections setting. For example, for A connection that runs in parallel, you should have the table cache at least ten x n , where N is the maximum number of tables in a join of a query that the application can execute. In addition, some additional file descriptors need to be reserved for temporary tables and files. mysql open_tables indicates the number of open tables, if the number of MySQL opened_tables is too large, the description in the configuration Table_cache ( This value is called Table_open_cache after 5.1.3 ) may be too small

sort_buffer_size#** General Configuration

, is a Connection level parameters, in each Connection The first time you need to use this Buffer , a one-time allocation of the set of memory

Net_buffer_length , which is used to establish connection buffering and result buffering at connection time. The minimum value is 1Kand the maximum value is 1M. ( You can speed up Import and export of mysqldump )

read_buffer_size #*** MyISAM Unique Options , used to perform MyISAM the buffer size of the table full table scan. , If the full table scan is needed, it will be assigned to each thread.

read_rnd_buffer_size#*** MyISAM Unique Options , when a row is read in an ordered sort, the buffer can be used to read the rows to avoid finding the disk. If you set this value to a very high value, you can significantly increase the performance of the ORDER by. Assigned to each thread when needed.

myisam_sort_buffer_size#*** MyISAM Unique Options , when MySQL need to pass REPAIR , OPTIMIZE , ALTER Table statement to rebuild the index, and LOAD DATA INFILE to an empty table, the buffer is allocated. It is assigned to each thread, so be careful to compare large settings

log-bin#** The Common configuration option enables binary logging. This is necessary for a replication configuration to act as MASTER . If you need to be able to recover from your last backup point in time, you also need a binary log.

binlog_format#** Common configuration Options , recommended binary log format

binlog_cache_size#** Common configuration Options , ability to log binary in a transaction SQL the cache size at which the statement remains. If you frequently use large, multi-statement transactions, you can increase the value to get better performance. All statements from the transaction are buffered into the binary log cache and are written to the binary log immediately after the COMMIT. If the transaction is larger than this value, the temporary files on the disk will be substituted for use. This buffer is assigned to each connection at the first UPDATE statement in the transaction

max_connections #** Common configuration Options , MySQL The maximum number of concurrent sessions allowed. One of these connections will be reserved for users with SUPER privileges, and even if they have reached the connection limit, they can still allow the manager to log in.

max_connect_errors#** Common configuration Options , the maximum number of errors allowed per host. If this limit is reached, the host will block the connection to the MySQL server until the "FLUSH HOSTS" is run or the server is restarted. Invalid passwords and other errors during the connection phase will cause this value to be increased. See the "aborted_connects" state variable for the global counter.

log-bin-trust-function-creators#** Common configuration Options , valid when binary logs are open , This parameter determines whether the stored function creator can create a storage function that causes unsafe time to be logged to the binary log. When 0 , This is also the default value , in addition to having super Permissions In addition Create routine or Alter routine Permissions . It also enforces that the function must be Deterministic or reads SQL data or No SQL the character set is consistent . When set to 1 when MySQL not to limit these , This variable also acts on the trigger

Slow_query_log #** Common configuration Options , record slow queries. A slow query is a query that consumes more time thanthetotal time defined in "Long_query_time", or if log_short_format is not enabled, does not use an indexed query. If you frequently add new queries to the system, opening this is a good note.

slow_query_log_file #** Common configuration Options , file path and filename for specifying the slow query log

long_query_time #** Common configuration Options , all queries that consume more time than the total time will be considered slow. Do not use the "1" value here, as this will cause even very fast queries to be logged from time-to-time (MySQL 's current metric is only accurate to seconds).

log-queries-not-using-indexes#** Common configuration Options , used to log all queries that do not use indexes to the slow query logs

transaction_isolation#** Common configuration Options , sets the default transaction isolation level. The available levels are:read-uncommitted,read-committed,repeatable-read, SERIALIZABLE.

query_cache_size #** Common configuration Options , Specify MySQL the size of the query buffer. You can observe by executing the following commands in the MySQL console: # > SHOW VARIABLES like '%query_cache% ';
# > SHOW STATUS like ' cache% ';

if Cache_lowmem_prunes is very large, it indicates that there is often insufficient buffering;
if cache_hits is very large, it indicates that query buffering is used very frequently, and if the value is smaller it will affect efficiency, then you can consider not querying buffer; Cache_free_blocks , if the value is very large, it indicates that there is a lot of fragmentation in the buffer.

query_cache_limit#** Common configuration Options , caching Only the result set is less than that limit. This can protect the query cache of a very large result set from overwriting all other query results.

query_cache_type#** Common configuration Options , Specifies whether to use query buffering 1 Use, 0 Do not use

init_connect#** Common configuration Options , The string that the server executes for each connected client. A string consists of one or more SQL statements. To specify multiple statements, use semicolons to open them.

bind-address #** Common configuration Options , binding MySQL Server's IP address. This option is important if the computer on which the MySQL server resides has multiple IP addresses.

ft_min_word_len#** Common configuration Options , minimum Word length work for full-text indexing. The default setting for this option is 4, meaning that when you create a full-text index, you do not consider those words that are constructed from 3 or fewer characters.

thread_concurrency #** Common configuration Options , This function enables the application to provide the expected number of threads to a thread system running at the same time. This value is only meaningful on systems that support the thread_concurrency () function call (for example , Sun Solaris). You should try to thread_concurrency The number of CPUs 2/4/6/ ... times.

join_buffer_size #** Common configuration Options , the buffer is used to optimize Full JOIN (Non-indexed JOIN ). In any case, the JOIN is very bad for performance in most cases, but setting the variable to a large value will reduce the impact on performance. See the "select_full_join" state variable for a certain number of full joins . If full JOIN is found, it is allocated to each thread

thread_cache_size#** Common configuration Options , we keep the number of reusable threads in a cache. When a client disconnects, Theclient's thread is placed in the cache if the thread before it does not exceed thread_cache_size. The following rules are set according to physical memory:1G ---> 8 2G---> 3G---> >3g--->

Thread_stack #** Common configuration Options , the size of the thread stack used. The amount of memory is always retained at the time of connection. MySQL itself usually needs no more than 64K of memory, however if you are using your own stack UDF function or your system requires more stacks for some operations, you may need to set this value to a higher value.

tmp_table_size#** Common configuration Options , Tmp_table_size the maximum capacity of the internal (in-memory) temporal table. If a table grows more than this value, it is automatically converted to a disk-based table. The limit is for a single table, but there can be many such tables.

Basedir #** Common configuration Options , This parameter specifies the installation MySQL installation path, fill in the full path can solve the problem caused by the relative path.

datadir#** Common configuration Options , This parameter specifies the MySQL What path the database file is placed under. The database file is the MySQL data file that we often say .

wait_timeout#** Common configuration Options , number of seconds that the server waits for activity before closing a non-interactive connection.

skip-name-resolve #** Common configuration Options , MySQL after receiving the connection request, the client is given a IP , for a better match Mysql.user Records (some of which are hostname defined). If the mysql server has a DNS server set up and the client IP is in DNS There is no corresponding hostname, then the process is slow, causing the connection to wait. After adding skip-name-resolve , I skipped a process.

log_long_format#** Common configuration Options , opening this entry will record that queries that do not use the index are also attached to the slow log as a slow query

relay-log#** Common configuration Options , from the server I/O The thread reads the primary server's binary log over to the local file from the server, and then SQL Threads will read Relay-log the contents of the log and applied to the slave server

relay-log-index#** Common configuration Options , as a slave the time to store Binlog the index file

skip-slave-start#** Common configuration Options , tells from the server when the server starts without booting from the server thread. Use the start SLAVE statement to start the thread at a later time.

Innodb_data_home_dir #**innodb configuration Options , if you want to InnoDB table space files are stored elsewhere, setting this option. The default is the MySQL Data directory.

Innodb_data_file_path #**innodb configuration Options , InnoDB stores data into one or more data files, forming a tablespace. If you have a single physical device for your data, then a single auto-extension file is sufficient. In other cases, a single file Per device is a very good choice. You can also configure InnoDB to use the original disk partition - Please refer to the manual for more information about this

Innodb_log_group_home_dir#**innodb configuration Options , InnoDB the location of the log file. The default is MySQL 's Data directory. You may want to specify it to a dedicated hard disk or a RAID1 volume label to improve performance.

Innodb_additional_mem_pool_size#**innodb configuration Options , This parameter specifies InnoDB the size of the memory pool used to store data dictionaries and other internal structures. The default value is 1M. Usually not too big, as long as enough on the line, should be related to the complexity of the table structure. If not enough,MySQL writes a warning message to the error log. According to the MySQL Manual, the recommended value for 2G memory Machines is 20M.

innodb_buffer_pool_size #**innodb configuration Options , InnoDB , not like MyISAM , use a buffer pool to cache the index and row data. The more you set the value, the less disk I/O you need to access the data in the table . On a dedicated database server, you can set this parameter to the 80% of the machine's physical memory size . Do not set it too large because the competition for physical memory may cause paging in the operating system. Note that on a system of three digits, you may be limited to 2-3.5g on the user level memory of each processor , so do not set it too high.

innodb_file_io_threads #**innodb configuration Options , InnoDB , asynchronous IO The operation is used by the IO The number of threads. This value is hardcoded to 4on Unix systems, but on Windows , disk I/O may benefit from a larger number.

innodb_thread_concurrency #**innodb configuration Options , InnoDB the number of threads allowed inside the kernel. The optimal value height depends on the application, hardware, and scheduling properties of the operating system. A value that is too high can cause a thread to bump.

Innodb_flush_log_at_trx_commit #**innodb configuration Options , If set to 1 , InnoDB upon each submission (providing the full ACID behavior) When the transaction log is flushed to disk. If you want to compromise safely and you are running a small transaction, you can reduce the disk I/Ofor logs by 0 or 2 . A value of 0 indicates that the log is only written to the log file, and the log file is flushed to disk approximately once per second. A value of 2 indicates that the log is written to the log file each time it is committed, but the log file is flushed to disk only about once per second.

innodb_log_buffer_size #**innodb configuration Options , InnoDB buffer size used to buffer the log data. Once it's full,InnoDB will flush it to disk. Because no matter how it is refreshed every second, it is not necessary to make it large (even a long transaction).

Innodb_log_file_size#**innodb configuration Options , the size of each log file in a log group. You can set the federated size of the log file to the 25%-100% of your buffer pool size to avoid the unnecessary buffer pool dynamic flush overrides on the log file. However, note that a larger log file size will increase the amount of time that is required for recovery processing.

Innodb_log_files_in_group#**innodb configuration Options , The total number of files in the log group. The usual value of 2-3 is sufficient.

Innodb_max_dirty_pages_pct#**innodb configuration Options , InnoDB The maximum percentage of dirty pages allowed in the buffer pool. If it arrives,InnoDB will start to actively clean them up to avoid consuming all the clean pages. This is a soft limit and is not guaranteed to remain.

innodb_lock_wait_timeout #**innodb configuration Options , a InnoDB The length of time that a transaction should wait for the lock to be authorized before rolling back. the InnoDB automatically detects the transaction deadlock in its own locking table and rolls back the transaction. If you use the LOCK TABLES command in the same transaction , or any other storage engine that is more transaction-safe than InnoDB, you will later appear InnoDB cannot prompt for deadlocks. If like this, timeouts are useful for solving problems.

log-slave-updates #** Common configuration Options , Configure the update operation from the library whether to write binary files, if this from the library, but also to do other from the library's main library, then you need to hit this parameter, so from the library from the library to be able to log synchronization this parameter and- Logs-bin used together.

slave-skip-errors#** Common configuration Options , during the copy process, due to various reasons Binlog in the SQL error, by default, the copy is stopped from the library for the user to intervene. You can set slave-skip-errors to define the error number, which can be skipped if the error number encountered during the copy process is a defined error number. If the from library is used for backup, setting this parameter will present inconsistent data, do not use. If you are sharing the query pressure of the main library, consider it.

Sync_binlog #** Common configuration Options , This parameter is for MySQL system is critical, it not only affects Binlog the MySQL performance loss, but also affects Mysql the integrity of the data in the. For "sync_binlog" parameter description "sync_binlog=0 When things are committed,MySQL is simply to Binlog_cache data written in the Binlog file. But do not perform Disk synchronization instructions such as Fsync notify the file system to flush the cache to disk and let Filesystem decide when to synchronize Sync_binlog=n. after n commits,MySQL executes a fsync Disk synchronization directives that notify the file system to flush the cache of the binlog file to disk.

auto_increment_increment=2 #** Common configuration Options , Self-growth ID, must be the same , two units for 2, three is for 3, if the master-slave can not be set

auto_increment_offset=1#** General configuration options, since the starting position of the growth here is 1, the second set must be 2

MySQL parameters and explanations