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.

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

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

MySQL mode optimization:

40. Keep your database organized.
41. Old data archiving – delete redundant rows for return or search queries.
42. Index your data.
43. Don’t overuse indexes, comparisons and queries.
44. Compress text and BLOBdata types – To save space and reduce the number of disk reads.
45. UTF 8 and UTF16 are both lower than latin1 execution efficiency.
46. Use triggers sparingly.47. Redundant data is kept to a minimum – no unnecessary duplication of data.
48. Use linked tables instead of extended rows.
49. Pay attention to data types and use the smallest possible in your real data A.
50. If other data is frequently used in queries, but BLOB/TEXT data is not, separate BLOB/TEXT data from other data.
51. Check and optimize tables frequently.
52. Frequently rewrite InnoDB table optimization.
53. Sometimes, dropping the index when adding a column, and then adding the index back will be faster.
54. Use different storage engines for different needs .
55. Use archive storage engine log tables or audit tables - this is more efficient for writing.
56. Session data is stored in cache (memcache) rather than in MySQL - cache allows automatic autofilling of values , 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 as fixed length CHAR , while VARCHAR length is not fixed (UTF8 is not affected by this).58. Make schema changes gradually – a small change can have a huge impact.
59. Test all schemas in a development environment, reflect Production Changes.
60. Don't randomly change values ​​in your
configuration files, it can have disastrous effects.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 at their best - 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. When load increases on your server, use SHOW PROCESS
LIST
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 table units – 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.
The Last 101: Performing MySQL Monitoring: Monitis Unveils The World's First Free On-demand MySQL Monitoring.

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!