MySQL Configuration optimization

MySQL configuration:

1, use Innodb_flush_method=o_direct to avoid writing when the double buffer.

2. Avoid using O_direct and EXT3 file systems-This will serialize everything written.

3. Allocate enough innodb_buffer_pool_size to load the entire InnoDB file into memory-reducing read from disk.

4, do not let innodb_log_file_size too large, so that it can be faster, there is more disk space-often refreshing to reduce the recovery time when the failure.

5. Do not use innodb_thread_concurrency and thread_concurrency variables at the same time-these two values are not compatible.

6. Specify a small value for max_connections-too many connections will drain your RAM and cause the entire MySQL server to be locked.

7. Keep the thread_cache in a relatively high value, approximately 16-prevents the speed drop when the connection is opened.

8. Use skip-name-resolve-to remove DNS lookups.

9, if your query repetition rate is high, and your data does not change frequently, use query caching-however, using query caching on frequently changing data can negatively affect performance.

10, increase temp_table_size-prevent disk write.

11, increase max_heap_table_size-prevent disk write.

12. Do not set the value of sort_buffer_size too high-it may cause the connection to run out of all memory quickly.

13, monitoring key_read_requests and key_reads, in order to determine the value of Key_buffer-key read demand should be higher than key_reads, otherwise use key_buffer will not be efficient.

14, Set Innodb_flush_log_at_trx_commit = 0 can improve performance, but to maintain the default value (1), can guarantee the integrity of the data, but also to ensure that replication does not lag.

15, there is a test environment, easy to test your configuration, can be restarted frequently, does not affect the production environment.

MySQL Configuration optimization