101 MySQL tuning and tuning tips

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 adjusting and optimizing MySQL installation. Some of the tricks are specific to the installation environment, but these ideas are generic. I've divided them into several categories to help you learn more about MySQL's tuning and optimization skills.

MySQL server hardware and operating system tuning:

1. Have enough physical memory to load the entire InnoDB file into memory-faster access to files in memory than when accessed on a hard disk.

2. Avoid the use of swap partitions at all costs-the swap is read from the hard drive and it is slow.

3. Use battery-powered RAM (Note: RAM is random memory).

4. Use advanced RAID (note: Redundant Arrays of Inexpensive disks, disk array) – preferably RAID10 or higher.

5. Avoid RAID5 (note: A storage solution that balances storage performance, data security, and storage costs) – Ensuring that database integrity is validated at a cost.

6. Separate the operating system from the data partition, not only logically, but also physically – the operating system's read and write operations can affect the performance of the database.

7. Put the MySQL temporary space and the replication log and data into different partitions-when the database is read and written from the disk in the background can affect the performance of the database.

8. More disk space equals faster speed.

17. Using XFS file Systems – a faster, smaller file system than ext3, with a number of logging options, and ext3 has been proven to have a double buffering problem with MySQL.

18. Adjust XFS file system logs and buffer variables – for maximum performance standards.

19. In Linux systems, the CFQ and anticipatory timing scheduler is very slow compared to the NOOP and deadline timing Scheduler, using the NOOP or deadline IO timing scheduler.

20. Using 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 consumption.

22. Put the host with MySQL and your MySQL host in a Hosts file – no DNS lookup.

23. Do not force the killing of a MySQL process – you will damage the database and the program that is running the backup.

24. The contribution of the server to the mysql– background process and other services can shorten the CPU time of the database.

MySQL configuration:

25. When writing, use Innodb_flush_method=o_direct to avoid double buffering.

26. Avoid the use of O_direct and EXT3 file Systems – you will serialize all that you want to write.

27. Allocate enough innodb_buffer_pool_size to load the entire InnoDB file into memory-read less from the disk.

28. Do not set the Innodb_log_file_size parameter too large, so that you can have more disk space at the same time-lost a lot of log is usually good, after the database crash can reduce the time to restore the database.

29. Do not mix innodb_thread_concurrency and Thread_concurrency parameters – these 2 values are incompatible.

30. Assign a minimum number to the Max_connections parameter – too many connections will run out of RAM and lock down the MySQL service.

31. Keep The thread_cache in a relatively high number, about 16 – to prevent slow opening of the connection.

32. Use Skip-name-resolve parameter – Remove DNS lookup.

33. If your query is duplicated and the data does not change frequently, you can use the query cache. But if your data changes frequently, using a query cache can be frustrating for you.

34. Increase the temp_table_size value to prevent writing to disk

35. Increase the max_heap_table_size value to prevent writing to disk

36. Do not set the Sort_buffer_size value too high, otherwise your memory will soon run out

37. According to Key_read_requests and Key_reads value to determine the size of the Key_buffer, in general, key_read_requests should be higher than the key_reads value, otherwise you can not efficiently use Key_buffer

38. Setting the Innodb_flush_log_at_trx_commit to 0 will improve performance, but if you want to keep the default (1), then you need to ensure the integrity of the data, and you also want to ensure that replication does not lag.

39. You need to have a test environment to test your configuration, and you can often reboot without impacting normal production.

MySQL mode optimization:

40. Keep your database organized.

41. Old Data Archive-delete extra rows to return or search for queries.

42. Add your data to the index.

43. Do not overuse indexes, compare and query.

44. Compress text and BLOB data types-to save space and reduce disk reads.

Both UTF 8 and UTF16 are less efficient than latin1 execution.

46. Use triggers in a controlled manner.

47. Redundant data is kept to a minimum-no unnecessary duplication of data is required.

48. Use linked tables instead of extended rows.

49. Note 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. Inspection and regular optimization of the table.

52. Frequently rewrite InnoDB table optimization.

53. Sometimes the index is deleted when the column is added, and then the index is added back, which is faster.

54. Use different storage engines for different needs.

55. Use the archive Storage Engine log table or audit form-which is more effectively written.

56. Session data is stored in the cache (Memcache) rather than in MySQL-caching allows automatic filling of values and prevents you from creating spatiotemporal data that is difficult to read and write to MySQL.

57. The use of varchar instead of char when storing variable-length strings is space-saving because of fixed-length char, while varchar length is not fixed (UTF8 is not affected).

58. Gradual changes in patterns-a small change can have a huge impact.

59. Test all patterns in the development environment to reflect production changes.

60. Do not arbitrarily change the values in your profile, which can have disastrous effects.

61. Sometimes, the configs in MySQL is much less.

62. Use a generic MySQL profile when in doubt.

Query optimization:

63. Use slow query log to find slow query.

64. Use the execution plan to determine whether the query is working properly.

65. Always go to test your query to see if they are running in the best state – performance always changes over time.

66. Avoid using COUNT (*) on the entire table, which may lock the entire table.

67. Keep the query consistent so that subsequent similar queries can use the query cache.

68. Use GROUP by instead of distinct in appropriate cases.

69. Use indexed columns in where, GROUP by and ORDER BY clauses.

70. Keep the index simple and do not include the same column in multiple indexes.

71. Sometimes MySQL uses the wrong index, which is used with use index for this situation.

72. Check the problem of using sql_mode=strict.

73. For indexed fields that have a record number of less than 5, the use of limit in union is not with or.

74. In order to avoid the select before updating, use Insert on DUPLICATE key or insert IGNORE, do not use update to implement.

75. Do not use MAX, use indexed fields and ORDER BY clauses.

76. Avoid using ORDER by RAND ().

77. LIMIT m,n can actually slow down the query in some cases and use it sparingly.

78. Use union in place of a subquery in the WHERE clause.

79. For updates (updates), use SHARE mode (shared mode) to prevent exclusive locks.

80. In the restart of MySQL, remember to warm your database to ensure that your data is in memory and the query is fast.

81. Deletes all data from the table using the Drop table,create table delete from.

82. Minimize the data in the query you need the data, use * consumes a lot of time.

83. Consider persistent connections, rather than multiple connections, to reduce overhead.

84. Benchmark queries, including using the load on the server, sometimes a simple query can affect other queries.

85. When the load increases on your server, use show processlist to view slow and problematic queries.

86. All suspicious queries that are tested in mirrored data generated in the development environment.

MySQL Backup process:

87. Backup from level two replication server.

88. Stop replication during backup to avoid inconsistencies in data dependencies and foreign key constraints.

89. Completely stop MySQL, backup from the database file.

90. If you are using MySQL dump for backup, also back up the binary log files – make sure replication is not interrupted.

91. Do not trust LVM snapshots – this is likely to result in inconsistent data that will cause you trouble in the future.

92. To make it easier to perform a single table recovery, export data in a table-if the data is isolated from other tables.

93. Use –opt when using mysqldump.

94. Check and optimize tables prior to backup.

95. Temporarily disable the FOREIGN KEY constraint when importing for faster import.

96. For faster import, disable uniqueness detection temporarily during import.

97. Calculate the size of the database, tables, and indexes after each backup so that you can monitor the growth of the data size more.

98. Monitor replication instances for errors and delays through automated scheduling scripts.

99. Perform regular backups.

100. Test your backups regularly.

Finally 101: Perform MySQL monitoring: Monitis unveils the world's ' s ' on-demand mysql monitoring.