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

Editor to share with you how to adjust the MySQL server hardware and operating system, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

MySQL is a powerful open source database. With more and more database-driven applications, people have been pushing MySQL to its limits. Here are tips for tuning and optimizing MySQL installation. Some techniques are specific to a specific installation environment, but these ideas are common.

MySQL server hardware and operating system tuning:

1. Have enough physical memory to load the entire InnoDB file into memory-accessing the file in memory is much faster than accessing it on the hard drive.

two。 Avoid swapping partitions with Swap at all costs-swapping is read from the hard disk and is slow.

3. Use battery powered RAM (Note: RAM is random access memory).

4. Use advanced RAID-preferably RAID10 or higher.

5. Avoid RAID5 (a storage solution that takes into account storage performance, data security, and storage cost)-there is a price to pay for checking database integrity.

6. Separate the operating system from the data partition, not only logically, but also physically-the read and write operation of the operating system will affect the performance of the database.

7. Put the MySQL temporary space and replication logs in different partitions from the data-when the database background reads and writes from disk, it will affect the performance of the database.

8. More disk space equals faster speed.

9. A better and faster disk.

10. Use SAS (SerialAttachedSCSI) instead of SATA (SATA).

11. Smaller hard drives are faster than larger hard drives, especially in RAID configurations.

twelve。 Use a battery-supported cache RAID controller.

13. Avoid using software disk arrays.

14. Consider using solid-state IO cards (not disk drives) for data partitions-these cards can support 2GB/s write speeds for almost any amount of data.

15. Set swappiness to 0 in Linux-there is no reason to cache files in the database server, which is the advantage of a server or desktop.

16. Mount the file system using noatime and nodirtime if possible-there is no reason to update the modification time for accessing database files.

17. Using the XFS file system-a faster and smaller file system than ext3, and there are many logging options, and ext3 has been proven to have a double buffering problem with MySQL.

18. Adjust the XFS file system log and buffer variables-for the highest performance standards.

19. In Linux systems, using NOOP or DEADLINEIO timing schedulers-this CFQ and DEADLINE timing scheduler is very slow compared to NOOP and ANTICIPATORY timing schedulers.

20. Use 64-bit operating system-for MySQL, there will be more memory support and use.

21. Remove unused installation packages and daemons from the server-less resource footprint.

twenty-two。 Put host using MySQL and your MySQLhost in a hosts file-no DNS search.

23. Do not forcibly kill a MySQL process-you will damage the database and running backup programs.

24. Contributing the server to the MySQL- background process and other services can shorten the time that the database takes up CPU.

MySQL configuration:

25. Use innodb_flush_method=O_DIRECT to avoid double buffering when writing.

twenty-six。 Avoid using O_DIRECT and EXT3 file systems-you will serialize everything you want to write.

twenty-seven。 Allocate enough innodb_buffer_pool_size to load the entire InnoDB file into memory-read less from disk.

twenty-eight。 Don't set the innodb_log_file_size parameter too large, so you can have more disk space faster and have more disk space-it's usually good to lose more logs and reduce the time it takes to recover the database after a database crash.

twenty-nine。 Do not mix innodb_thread_concurrency and thread_concurrency parameters-these two values are incompatible.

thirty。 Assign a very small number to the max_connections parameter-too many connections will exhaust RAM and lock the MySQL service.

thirty-one。 Keep thread_cache at a relatively high number, about 16-prevent slow opening of connections.

thirty-two。 Use the skip-name-resolve parameter-remove the DNS lookup.

thirty-three。 If your queries are repetitive and the data does not change often, you can use query caching. But if your data changes frequently, using query caching will disappoint you.

thirty-four。 Increase the temp_table_ size value to prevent writing to disk

thirty-five。 Increase the max_heap_table_ size value to prevent writing to disk

thirty-six。 Don't set the sort_buffer_ size too high, or you will run out of memory quickly

thirty-seven。 According to the value of key_read_requests and key_ reads to determine the size of key_buffer, in general, key_read_requests should be higher than the value of key_ read, otherwise you can not use key_buffer efficiently

thirty-eight。 Setting innodb_flush_log_at_trx_commit to 0 will improve performance, but if you want to keep the default value (1), you need to ensure the integrity of the data and make sure that replication does not lag.

thirty-nine。 You need to have a test environment to test your configuration and restart frequently without affecting normal production.

The above is all the contents of the article "how to adjust the hardware and operating system of MySQL server". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.