Share 101 MySQL debugging and optimization tips

MySQL is a powerful open source database. With the growing number of database-driven applications, people have been pushing MySQL to its limits. Here are 101 tips for tuning and optimizing your MySQL installation. Some of the tips are specific to your installation, 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 OS Tuning:

1. Have enough physical memory to load the entire InnoDB file into memory - accessing files in memory is much faster than accessing them from disk.
2. Avoid using Swap partitions at all costs - Swap is read from the hard disk, which is slow.
3. Use battery-powered RAM (Note: RAM stands for random access memory).
4. Use advanced RAID (Note: Redundant Arrays of Inexpensive Disks) – preferably RAID10 or higher.
5. Avoid RAID5 (Note: a storage solution that takes into account storage performance, data security, and storage costs) – Ensuring database integrity verification comes at a price.
6. Separate the operating system and data partitions, not just logically but also physically – the operating system's read and write operations will affect the database's performance.
7. Put MySQL temporary space and replication logs on different partitions from data – This will affect database performance when the database backend reads and writes to disk.
8. More disk space equals more speed.
9. Better and faster disks.
10. Use SAS (Note: Serial Attached SCSI) instead of SATA (Note: SATA, i.e. serial port hard disk).
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 swappiness to 0 in Linux – There is no reason to cache files in a database server, this is an advantage for a server or desktop.
16. Mount file systems with noatime and nodirtime if possible – there is no reason to update modification times when accessing database files.
17. Use the XFS filesystem – a faster and smaller filesystem than ext3 with many journaling options, and ext3 has been shown to have double-buffering issues with MySQL.
18. Tune XFS filesystem journaling and buffering variables – for highest performance standards.
19. In Linux, use NOOP or DEADLINE IO scheduler - Compared with NOOP and DEADLINE schedulers, the CFQ and ANTICIPATORY schedulers are very slow.
20. Use a 64-bit operating system – For MySQL, there will be greater memory support and usage.
21. Remove unused packages and daemons on the server – less resource usage.
22. Put the host using MySQL and your MySQL host in one hosts file – no DNS lookup.
23. Never force kill a MySQL process - you will corrupt the database and the backup process you are running.
24. Dedicate servers to MySQL – Background processes and other services can reduce the time the database uses the CPU.

MySQL Configuration:

25. When writing, use innodb_flush_method=O_DIRECT to avoid double buffering.
26. Avoid using O_DIRECT and the EXT3 filesystem – you will serialize all writes.
27. Allocate enough innodb_buffer_pool_size to load the entire InnoDB file into memory – read less from disk.
28. Don't set the innodb_log_file_size parameter too large, this will allow for faster execution and more disk space - dropping more logs is usually a good thing, as it reduces the time it takes to recover the database after a 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 server.
31. Keep thread_cache at a relatively high number, around 16 - to prevent slowness when opening connections.
32. Use the skip-name-resolve parameter – to remove the DNS lookup.
33. If your queries are repetitive and the data does not change often, you can use query caching. But if your data changes frequently, using the query cache will frustrate you.
34. Increase the temp_table_size value to prevent writing to disk
35. Increase max_heap_table_size to prevent writing to disk
36. Don’t set the sort_buffer_size value too high, otherwise you will quickly run out of memory.
37. Determine the size of the key_buffer based on the key_read_requests and key_reads values. Generally, key_read_requests should be higher than the key_reads value, otherwise you cannot use the key_buffer efficiently.
38. Setting innodb_flush_log_at_trx_commit to 0 will improve performance, but if you keep the default value (1), you must ensure data integrity and you must ensure that replication does not fall behind.
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. Archiving of old data – removing redundant rows returned 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 extending rows.
49. Pay attention to the data type, and use the smallest one possible in your real data.
50. If other data is often used in queries and 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 it is faster to drop the index when adding the column and then add it back.
54. Use different storage engines for different needs.
55. Use the archive storage engine for log tables or audit tables - these are more efficient to write to.
56. Session data is stored in cache (memcache) instead of MySQL – Caching allows for automatic auto-population of values ​​and prevents you from creating spatiotemporal 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 fixed length, while VARCHAR is not fixed length (UTF8 is not affected by this).
58. Make incremental pattern changes – A small change can have a huge impact.
59. Test all modes in development environment and reflect production changes.
60. Don’t change random values ​​in your configuration files, it can have disastrous effects.
61. Sometimes, less is more in MySQL configs.
62. When in doubt, use a generic MySQL configuration file.

Query optimization:

63. Use slow query log to find slow queries.
64. Use the execution plan to determine whether the query is running properly.
65. Always test your queries to see if they are running optimally – performance can vary over time.
66. Avoid using count(*) on the entire table, it may lock the entire table.
67. Make queries consistent so that 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 uses the wrong index. In 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 in UNION.
74. To avoid SELECT before updating, use INSERT ON DUPLICATE KEY or INSERT IGNORE instead of UPDATE.
75. Don’t use MAX, use indexed fields and an ORDER BY clause.
76. Avoid using ORDER BY RAND().
77. LIMIT M,N can actually slow down queries in some cases; use it sparingly.
78. Use UNION instead of subqueries in the WHERE clause.
79. For UPDATES, use SHARE MODE to prevent exclusive locks.
80. Before restarting MySQL, remember to warm up 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. Minimize the data When querying the data you need, using * consumes a lot of time.
83. Consider persistent connections instead of multiple connections to reduce overhead.
84. Benchmark queries, including using the load on the server. Sometimes a 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 mirrored data generated in the development environment.

MySQL backup process:

87. Back up from the secondary replication server.
88. Stop replication during backup to avoid inconsistencies in data dependencies and foreign key constraints.
89. Stop MySQL completely and back up the database files.
90. If you use MySQL dump for backup, back up the binary log files as well – to ensure that replication is not interrupted.
91. Don't trust LVM snapshots – these are likely to create data inconsistencies that will cause you trouble in the future.
92. Export data by table for easier single table recovery – if the data is isolated from other tables.
93. Please use –opt when using mysqldump.
94. Check and optimize tables before backing them up.
95. For faster imports, temporarily disable foreign key constraints during import.
96. For faster imports, temporarily disable uniqueness checking during import.
97. Calculate the size of database, tables and indexes after each backup to better monitor the growth of data size.
98. Monitor replication instances for errors and latency via automated scheduling scripts.
99. Perform backups regularly.
100. Test your backups regularly.
The Final 101: Implementing MySQL Monitoring: Monitis Unveils The World's First Free On-demand MySQL Monitoring.