101 Essential MySQL Tuning Tips for Maximum Performance

MySQL Server Hardware and OS Tuning

1. Ensure enough physical memory to load the entire InnoDB file into RAM.

2. Avoid using swap at all costs.

3. Use battery‑backed memory.

4. Deploy advanced RAID, preferably RAID10 or higher.

5. Avoid RAID5 due to integrity‑check overhead.

6. Separate OS and data partitions both logically and physically.

7. Place MySQL temporary space and binary logs on a different partition from data.

8. More disk space generally yields faster performance.

9. Prefer faster disks; SAS over SATA.

10. Smaller disks can be faster than larger ones, especially in RAID configurations.

11. Use battery‑supported high‑speed cache RAID controllers.

12. Avoid software RAID.

13. Consider SSD I/O cards for data partitions, offering up to 2 GB/s write speeds.

14. Set Linux swappiness to 0.

15. Mount filesystems with noatime and nodiratime when possible.

16. Use XFS instead of ext3 for better performance and logging options.

17. Tune XFS journal and buffer settings for optimal speed.

18. Prefer the NOOP or DEADLINE I/O scheduler over CFQ or ANTICIPATORY.

19. Run a 64‑bit OS to benefit from larger memory addressing.

20. Remove unused packages and daemons from the server.

21. Add MySQL hosts to the /etc/hosts file to bypass DNS lookups.

22. Never force‑kill a MySQL process.

23. Offload background tasks to reduce CPU usage by MySQL.

MySQL Configuration

24. Set innodb_flush_method=O_DIRECT to avoid double buffering.

25. Avoid using O_DIRECT with EXT3 filesystems.

26. Allocate sufficient innodb_buffer_pool_size to hold the entire InnoDB dataset in memory.

27. Keep innodb_log_file_size moderate to balance speed and disk usage.

28. Do not mix innodb_thread_concurrency with thread_concurrency.

29. Limit max_connections to a reasonable number.

30. Keep thread_cache_size relatively high (e.g., around 16).

31. Use skip-name-resolve to eliminate DNS lookups.

32. Enable query cache only for read‑heavy, rarely changing data.

33. Increase tmp_table_size and max_heap_table_size to avoid disk‑based temporary tables.

34. Do not set sort_buffer_size excessively high.

35. Size key_buffer_size based on key_read_requests vs. key_reads.

36. Setting innodb_flush_log_at_trx_commit=0 improves performance but may affect durability.

37. Maintain a test environment for configuration changes.

MySQL Schema Optimization

38. Keep the database well‑organized.

39. Archive old data and delete unnecessary rows.

40. Add appropriate indexes.

41. Avoid over‑indexing; balance index use with query patterns.

42. Compress TEXT and BLOB columns to save space.

43. Prefer UTF‑8/UTF‑16 over latin1 for better performance.

44. Use triggers sparingly.

45. Minimize redundant data.

46. Use link tables instead of expanding rows.

47. Choose the smallest suitable data type for each column.

48. Separate frequently queried BLOB/TEXT data from other columns.

49. Regularly check and optimize tables.

50. Periodically rewrite InnoDB tables for better performance.

51. When adding columns, consider dropping and recreating indexes.

52. Select storage engines based on workload requirements.

53. Use the ARCHIVE engine for log or audit tables.

54. Cache session data in external caches (e.g., memcached) instead of MySQL.

55. Prefer VARCHAR over CHAR for variable‑length strings.

56. Apply schema changes incrementally.

57. Test schema changes in a development environment before production.

58. Avoid arbitrary configuration changes that can cause disasters.

59. Sometimes less configuration is more.

60. Use a generic, well‑tested MySQL configuration file when unsure.

Query Optimization

61. Enable the slow query log to identify problematic queries.

62. Use EXPLAIN to analyze query execution plans.

63. Regularly test queries to ensure they remain optimal.

64. Avoid COUNT(*) on large tables when possible.

65. Keep queries consistent to benefit from the query cache.

66. Prefer GROUP BY over DISTINCT when appropriate.

67. Use indexed columns in WHERE, GROUP BY, and ORDER BY clauses.

68. Keep indexes simple; avoid duplicate columns across indexes.

69. Use USE INDEX when MySQL chooses a suboptimal index.

70. Verify SQL_MODE=STRICT settings.

71. For small indexed fields, prefer LIMIT over OR in UNIONs.

72. Use INSERT ... ON DUPLICATE KEY or INSERT IGNORE instead of a preceding SELECT‑then‑UPDATE.

73. Prefer indexed columns with ORDER BY over MAX().

74. Avoid ORDER BY RAND().

75. Use LIMIT M,N judiciously to reduce result set size.

76. Replace subqueries with UNION when beneficial.

77. Use SELECT ... LOCK IN SHARE MODE for updates to prevent exclusive locks.

78. Warm up the database after a restart to improve cache performance.

79. Use DROP TABLE, CREATE TABLE, or TRUNCATE to quickly clear tables.

80. Select only needed columns instead of *.

81. Consider persistent connections to reduce overhead.

82. Benchmark queries under realistic load to detect performance impacts.

83. Monitor slow queries with SHOW PROCESSLIST during high load.

84. Test suspicious queries with production‑like data in a staging environment.

MySQL Backup Process

85. Perform backups from a secondary replica.

86. Stop replication during backup to avoid inconsistencies.

87. Shut down MySQL and copy raw database files for a cold backup.

88. When using mysqldump, also back up binary logs to preserve replication.

89. Do not rely on LVM snapshots alone; they may produce inconsistent data.

90. Export data table‑by‑table for easier single‑table restores.

91. Use mysqldump --opt for faster dumps.

92. Optimize tables before backing up.

93. Temporarily disable foreign‑key checks during import.

94. Temporarily disable unique‑key checks during import for speed.

95. After each backup, record database, table, and index sizes for growth monitoring.

96. Automate monitoring of replication errors and lag with scheduled scripts.

97. Perform regular backups.

98. Regularly test backup restores.

99. Continuously monitor MySQL performance metrics.