Sharing of 101 debugging and optimization skills in MySQL

With more and more database driven applications, people have been pushing MySQL to its limits. Here are 101 tips for tuning and optimizing your MySQL installation. Some tips are specific to a specific installation environment, but the ideas are general. I have divided them into several categories to help you master more MySQL tuning and optimization skillsMySQL is a powerful open source database. With more and more database-driven applications, people have been pushing MySQL to its limits. Here are 101 tips for tuning and optimizing your MySQL installation. Some tips are specific to a specific installation environment, but the ideas are general. I have divided them into several categories to help you master more MySQL tuning and optimization techniques.

1. Have enough physical memory to load the entire InnoDB file into memory - when accessing the file in memory The speed is much faster than when accessed on the hard disk.

3. Use battery-powered RAM (Note: RAM is random access memory).
4. Use advanced RAID (Note: Redundant
Array
s of Inexpensive Disks, i.e. disk array) - preferably RAID10 or higher. 5. Avoid RAID5 (Note: A storage solution that balances storage performance, data security
, and storage costs) - Verification to ensure database integrity comes at a cost. 6. Separate the operating system and data partitions, not just logically, but also physically - the read and write operations of the operating system will affect the performance of the database. 7. Place MySQL temporary space and replication logs and data in different partitions - when the database background reads and writes from the disk, it will affect the performance of the database.
8. More disk space equals faster speeds.
9. Better and faster disks.
10. Use SAS (Note: Serial Attached SCSI, i.e. Serial Attached SCSI) instead of SATA (Note: SATA, i.e. Serial Hard Drive).
11. Smaller hard drives are faster than larger hard drives, especially in RAID configurations.
12. Use battery-backed high-speed
caching
RAIDcontroller. 13. Avoid using software disk arrays. 14. Consider using solid-state IO cards (not disk drives) for data partitions - these cards are capable of supporting 2GB/s write speeds for almost any amount of data.
15. Set the swappiness value to 0 in Linux - there is no reason to cache files in the database server, which is an advantage for a server or desktop.
16. If possible, use noatime and nodirtime to mount the file system - there is no reason to update the modification time of accessed database files.
17. Use the XFS file system – a file system that is faster and smaller than ext3 and has many logging options, and ext3 has been shown to have double buffering issues with MySQL.
18. Tuning XFS file system log and buffer variables – for highest performance standards.
19. In Linux systems, use the NOOP or DEADLINE IO scheduled scheduler – Compared with the NOOP and DEADLINE scheduled schedulers, the CFQ and ANTICIPATORY scheduled schedulers are very slow.
20. Use a 64-bit operating system - For MySQL, there will be greater memory support and usage.
21. Remove unused installation packages and daemons on the server – less resource usage.
22. Put the host using MySQL and your MySQL host into a hosts file - no DNS lookup.
23. Never force-kill a MySQL process - you will damage the database and the program running the backup.
24. Contribute the server to MySQL - background processes and other services can reduce the CPU time occupied by the database.

25. When writing, use innodb_flush_method=O_DIRECT to avoid double buffering.
26. Avoid using O_DIRECT and EXT3 file systems - you will serialize everything you write.
27. Allocate enough innodb_buffer_pool_size to load the entire InnoDB file into memory – less reading from disk.
28. Don't set the innodb_log_file_size parameter too large, so you can be faster and have more disk space - dropping more logs is usually good and can reduce the time to restore the database after a database crash.
29. Do not mix innodb_thread_concurrency and thread_concurrency parameters – these 2 values ​​are incompatible.
30. Assign a very small number to the max_connections parameter - too many connections can use up RAM and lock up the MySQL service.
31. Keep thread_cache at a relatively high number, around 16 – to prevent slowness when opening connections.
32. Use skip-name-resolve parameter – remove DNS lookup.
33. If your queries are repeated and the data does not change often, you can use query caching. But if your data changes frequently, using the query cache will set you back.
34. Increase the temp_table_size value to prevent writing to the disk
35. Increase the max_heap_table_size value to prevent writing to the disk
36. Do not set the sort_buffer_size value too high, otherwise your memory will It will be exhausted quickly
37. Determine the size of key_buffer based on key_read_requests and key_reads values. Generally speaking, key_read_requests should be higher than key_reads value, otherwise you cannot use key_buffer efficiently
38. Setting innodb_flush_log_at_trx_commit to 0 will Improves performance, but if you want to keep the default value (1), then you need to ensure data integrity, and you also need to ensure that replication does not lag.
39. You need to have a test environment to test your configuration and restart it frequently without affecting normal production.

The above is the detailed content of Sharing of 101 debugging and optimization skills in MySQL. For more information, please follow other related articles on the PHP Chinese website!