MySQL Configuration optimization

Max_connections the number of simultaneous sessions allowed by MySQL Error:too many connections
Max_connect_errors Maximum error (connection Class) allowed number, which will cause flush hosts to restart service
key_buffer_size keyword buffer size, cache MyISAM index block, determine index processing speed, read index processing speed
Max_allowed_packet sets the maximum package, limits the server end block size, avoids lengthy SQL execution, Error: packets are too large to close the connection. Check this option if the client encounters a missing error with MySQL connection.
Thread_cache_size Server thread Caching
Thread_concurrency defaults to CPU cores X2, setting the wrong words affects MySQL using multi-core processor performance
Sort_buffer_size The amount of memory allocated when each connection needs to use buffer, not the larger the better. Example: 1000 connections, one 1MB, consumes 1GB of memory, 200WX1MB=20GB
Join_buffer_size Join table uses a cache size that is generally default and does not reset
Query_cache_size query cache size, and then return to the cache when queried, the cache period table must not be changed, otherwise the cache fails, multiple write operation if the setting is large will affect the write efficiency
Read_buffer_size the buffer size of the full table scan under the MyISAM engine. If you cannot add an index, you should also scan the whole table, increasing this value for optimization
When read_rnd_buffer_size reads rows from sorted data, the size of the row data read from the buffer increases the order by performance Note: MySQL will request this buffer for each client, too large to affect memory overhead
Myisam_sort_buffer_size MyISAM table changes, reorder the required caches
Innodb_buffer_pool_size InnoDB saves the index with the cache, saves the cache size of the original data, and effectively reduces the disk IO required to read the data
Innodb_log_file_size data log file size, large values can improve performance, but increases the time to recover the failed database (data log files need to be read when recovering the failed database, which is too long for the event)
Innodb_log_buffer_size The log file cache, increasing the file can improve performance, but increases the risk of losing data after a sudden outage (the log file is in the cache and has not yet been saved in the hard drive)
When Innodb_flush_log_at_trx_commit executes the transaction, it inserts the transaction log into the log cache of the InnoDB storage engine, writes the log (pre-write log mode) to 0, writes it in real time, and when set to 1 o'clock, the cache is written to disk in real time; Cache writes files in real time, files are written to disk in real time
Innodb_lock_wait_timeout is rolled back (when a transaction is revoked), a INNODB transaction should wait for a lock to be approved for how long, and this value is useful when InnoDB cannot detect the deadlock.
Summary: Buffur_size big, can improve performance, but occupy the corresponding memory.

MySQL Configuration optimization