Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about what are the 15 suggestions on MySQL database configuration optimization, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Overview

Today, I will mainly share 15 suggestions on the configuration of mysql database for reference only.

Recommendations for Mysql configuration optimization

1. Use innodb_flush_method=O_DIRECT to avoid double buffers when writing.

2. Avoid using O_DIRECT and EXT3 file systems-this serializes everything written.

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

4. Don't make the innodb_log_file_size too large, so it can be faster and have more disk space-frequent refreshes can help reduce recovery time in the event of a failure.

5. Do not use both innodb_thread_concurrency and thread_concurrency variables-these two values are not compatible.

6. Specify a small value for max_connections-too many connections will deplete your RAM, causing the entire MySQL server to be locked.

7. Keep thread_cache at a relatively high value, about 16-to prevent the speed from slowing down when opening the connection.

8. Use skip-name-resolve-remove DNS lookup.

9. If your query repetition rate is high and your data does not change often, use query caching-however, using query caching on frequently changed data can have a negative impact on performance.

10. Add temp_table_size-prevent disk writing.

11. Add max_heap_table_size-prevent disk writing.

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

Monitor key_read_requests and key_reads to determine the value of key_buffer-the read requirement of key should be higher than the value of key_reads, otherwise it would be inefficient to use key_buffer.

14. Setting innodb_flush_log_at_trx_commit = 0 can improve performance, but keeping the default value (1) can ensure the integrity of the data and ensure that replication will not lag.

There is a test environment that makes it easy to test your configuration and can be restarted frequently without affecting the production environment.

After reading the above, do you have any further understanding of the 15 recommendations for MySQL database configuration optimization? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.