The most comprehensive MySQL debugging and optimization skills

MySQL 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.

MySQL server hardware and operating system adjustments:

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.
2. Avoid using Swap at all costs – swapping is done from the hard drive and is very slow.
3. Use battery-powered RAM (Note: RAM is random access memory).
4. Use advanced RAID (Note: Redundant Arrays 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) – there is a price to pay for ensuring database integrity verification.
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, Serial Attached SCSI) instead of SATA (Note: SATA, Serial Hard Drive).
11. Smaller hard drives are faster than larger hard drives, especially in RAID configurations.
12. Use a battery-backed cache RAID controller.
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.

MySQL configuration:

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 will 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.

MySQL mode optimization:

40. Keep your database organized.
41. Old data archiving – remove redundant rows for return or search queries.
42. Index your data.
43. Don’t overuse indexes, comparisons and queries.
44. Compress text and BLOB data types – to save space and reduce disk reads.
45. Both UTF 8 and UTF16 are less efficient than latin1.
46. Use triggers sparingly.
47. Keep redundant data to a minimum – do not repeat unnecessary data.
48. Use linked tables instead of extended rows.
49. Pay attention to the data type. In your real data, use the smallest one possible.
50. If other data is often used in queries, and If the BLOB/TEXT data is not, separate the BLOB/TEXT data from other data.
51. Check and optimize the table frequently.
52. Frequently rewrite the InnoDB table optimization.
53. Sometimes, when adding Delete the index when the column is added, and then add the index back, which will be faster.
54. Use different storage engines for different needs.
55. Use archive storage engine log tables or audit tables - this is Write more efficiently.
56. Session data is stored in cache (memcache) rather than in MySQL – the cache allows automatic autofilling and prevents you from creating spatio-temporal data that is difficult to read and write to MySQL.
57. Use VARCHAR instead of CHAR when storing variable length strings - saves space because CHAR is of fixed length, while VARCHAR is not of fixed length (UTF8 is not affected by this).
58. Progressive pattern changes – A small change can have a huge impact.
59. Test all modes in a development environment, reflecting production changes.
60. Don’t change values ​​in your configuration files randomly, it can have disastrous consequences Impact.
61. Sometimes, less is more in MySQL configs.
62. When in doubt, use a common MySQL configuration file.

Query optimization:

63. Use slow query logs to discover slow queries.
64. Use the execution plan to determine whether the query is running normally.
65. Always test your queries to see if they are running optimally – performance will always change over time.
66. Avoid using count(*) on the entire table, it may lock the entire table.
67. Make queries consistent so subsequent similar queries can use the query cache.
68. Use GROUP BY instead of DISTINCT when appropriate.
69. Use indexed columns in WHERE, GROUP BY and ORDER BY clauses.
70. Keep indexes simple and do not include the same column in multiple indexes.
71. Sometimes MySQL will use the wrong index, for this case use USE INDEX.
72. Check for problems using SQL_MODE=STRICT.
73. For index fields with less than 5 records, use LIMIT instead of OR when UNION.
74. In order to avoid SELECT before updating, use INSERT ON DUPLICATE KEY or INSERT IGNORE. Do not use UPDATE. accomplish.
75. Do not use MAX, use index fields and ORDER BY clause.
76. Avoid using ORDER BY RAND().
77. LIMIT M, N can actually slow down queries in some cases, use sparingly.
78. Use UNION in the WHERE clause instead of a subquery.
79. For UPDATES, use SHARE MODE to prevent exclusive locks.
80. Before restarting MySQL, remember to warm your database to ensure your data is in memory and queries are fast.
81. Use DROP TABLE, CREATE TABLE DELETE FROM to delete all data from the table.
82. When querying the data you need with minimized data, using * consumes a lot of time.
83. Consider persistent connections instead of multiple connections to reduce overhead.
84. Benchmark queries, including using load on the server. Sometimes one simple query can affect other queries.
85. As load increases on your server, use SHOW PROCESSLIST to view slow and problematic queries.
86. Test all suspicious queries on the image data generated in the development environment.

MySQL backup process:

87. Backup from the secondary replication server.
88. Stop replication during backups to avoid inconsistencies in data dependencies and foreign key constraints.
89. Stop MySQL completely and make a backup from the database file.
90. If you use MySQL dump for backup, please also back up the binary log files - ensure that replication is not interrupted.
91. Don’t trust LVM snapshots – this is likely to create data inconsistencies that will cause you trouble in the future.
92. To make single-table recovery easier, export data in units of tables – if the data is isolated from other tables.
93. Please use –opt when using mysqldump.
94. Check and optimize tables before backing up.
95. For faster import, temporarily disable foreign key constraints during import.
96. For faster import, uniqueness detection is temporarily disabled during import.
97. Calculate database, table and index sizes after each backup to better monitor data size growth.
98. Monitor replication instances for errors and latency via automated scheduling scripts.
99. Perform regular backups.
100. Test your backups regularly.
Last 101: Execute MySQL Monitoring: Monitis Unveils The World's First Free On-demand MySQL Monitoring.

Related recommendations:

Must-see js breakpoint debugging experience sharing

Sharing of 5 debugging skills necessary for JavaScript debugging

What are the js debugging methods

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