100 recommendations for MySQL tuning/optimization

100 recommendations for MySQL tuning/optimization

MySQL is a powerful open-source database. With more and more applications on MySQL, MySQL is experiencing a bottleneck. Here are 101 recommendations for optimizing MySQL. Some techniques are suitable for a particular installation environment, but the ideas are interlinked. I have divided them into several categories to help you understand.

MySQL monitoring MySQL server hardware and OS (operating system) tuning:

1. There is enough physical memory to load the entire InnoDB file into memory-the INNODB will be much faster if the files are accessed in memory rather than on disk.

2, to avoid swap operation-Exchange (swapping) is to read data from the disk, so it will be very slow.

3. Use battery-powered RAM (battery-backed RAM).

4. Use an advanced disk array-preferably RAID10 or higher.

5, avoid the use of raid5-and calibration needs to ensure integrity, high overhead.

6, separate your operating system and data, not only logically separate, physically also separate-the operating system read and write overhead will affect the performance of the database.

7, the temporary files and replication logs and data files separate-background write operations affect the database from the disk file read and write operations.

8. More disk space equals higher speed.

9. The faster the disk, the better.

10, SAS is superior to SATA.

11, small disks faster than large disks, especially in RAID.

12. Use a battery-powered cache Raid (battery-backed cache RAID) controller.

13. Avoid the use of floppy disk arrays.

14. Consider using a solid-state IO card (not a disk) as the data partition-almost all magnitude data, which can support 2 GBps of write operations.

15. On a Linux system, set the value of swappiness to 0-there is no reason to cache files on the database server in a way that is more used in Web servers or desktop applications.

16. Use Noatime and Nodirtime to mount the file system whenever possible-there is no need to update the file's modification time for each access.

17. Use XFS file System-a faster, smaller file system than ext3, with more logging options, and MySQL has a double-buffer problem on ext3.

18. Optimize your XFS file system log and buffer parameters –-in order to obtain the maximum performance benchmark.

19. In Linux systems, using the NOOP or DEADLINE IO Scheduler-cfq and Anticipatory Scheduler has been proven to be slower than NOOP and DEADLINE.

20, using 64-bit operating system-more memory can be used for addressing and MySQL use.

21. Remove unused packages and background programs from the server-reduce resource usage.

22, will use the MySQL host and MySQL's own host is configured in a host file-so there is no DNS lookup.

23. Never force a MySQL process to be killed-you will corrupt the database and run a backup.

24, let your server only serve the mysql-spooler and other services will consume the database CPU time.

MySQL Configuration:

25, use Innodb_flush_method=o_direct to avoid writing when the double buffer.

26. Avoid using O_direct and EXT3 file systems-This will serialize everything written.

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

28, do not let innodb_log_file_size too large, so that it can be faster, there is more disk space-often refreshing to reduce the recovery time when the failure.

29. Do not use innodb_thread_concurrency and thread_concurrency variables at the same time-these two values are not compatible.

30. Specify a small value for max_connections-too many connections will drain your RAM and cause the entire MySQL server to be locked.

31. Keep The thread_cache in a relatively high value, approximately 16-prevents the speed drop when the connection is opened.

32. Use skip-name-resolve-to remove DNS lookups.

33, if your query repetition rate is high, and your data does not change frequently, use query caching-however, using query caching on frequently changing data can negatively affect performance.

34, increase temp_table_size-prevent disk write.

35, increase max_heap_table_size-prevent disk write.

36. Do not set the value of sort_buffer_size too high-it may cause the connection to run out of all memory quickly.

37, monitoring key_read_requests and key_reads, in order to determine the value of Key_buffer-key read demand should be higher than key_reads, otherwise use key_buffer will not be efficient.

38, Set Innodb_flush_log_at_trx_commit = 0 can improve performance, but to maintain the default value (1), can guarantee the integrity of the data, but also to ensure that replication does not lag.

39, there is a test environment, easy to test your configuration, can be restarted frequently, does not affect the production environment.

MySQL Schema Optimization:

40. Ensure the cleanliness of your database.

41. Archive old data-delete extra rows retrieved or returned from the query

42. Add an index to the data.

43, do not overuse the index, evaluate your query.

44. Compress text and BLOB data types-to save space, reduce read data from disk.

45, UTF 8 and UTF16 are slower than latin1.

46, the use of a controlled trigger.

47. Keep the minimum amount of data redundant-do not copy unnecessary data.

48. Use a linked table instead of an extension row.

49, pay attention to your data type, as far as possible to use the smallest.

50. If other data needs to be queried frequently and blob/text is not required, separate the Blob/text data from other data fields.

51, regular inspection and optimization of the table.

52, often do rewrite InnoDB table optimization.

53. Sometimes, when the column is added, the index is dropped first, and then the index is added faster.

54. Choose a different storage engine for different needs.

55. Log tables or Audit tables use the archive storage engine-write more efficiently.

56, the session data stored in the memcache, instead of MySQL-memcache can set automatic expiration, prevent MySQL to the temporary data high-cost read and write operations.

57. If the length of the string is variable, use varchar instead of char-to save space, because char is a fixed length and varchar is not (UTF8 not affected by this).

58, gradually change the schema-a small change will have a huge impact.

59. Test all schema changes in the development environment instead of mirroring the production environment.

60, do not arbitrarily change your profile, this can have a very big impact.

61, sometimes, a small amount of configuration will be better.

62, questioned the use of the common MySQL configuration file.

Query Optimization:

63, use slow query log, find the execution slow query.

64, use EXPLAIN to determine whether the query function is appropriate.

65. Test your query frequently to see if performance optimization is required-performance may change 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 will be able to use the query cache.

68, if appropriate, use GROUP by instead of DISTINCT.

69. Add an index to the where, GROUP by, and ORDER by columns.

70, ensure that the index is simple, do not add multiple indexes on the same column.

71. Sometimes MySQL chooses the wrong index, which is the case with use index.

72, use Sql_mode=strict to check the problem.

73. When the index field is less than 5, the UNION operation uses LIMIT instead of OR.

74. Use Insert on DUPLICATE KEY or insert IGNORE instead of update to avoid needing SELECT before update.

75. Use index fields and order by instead of MAX.

76. Avoid using ORDER by RAND ().

77, LIMIT M,n in a specific scenario will reduce the efficiency of the query, the use of moderation.

78. Use UNION instead of a subquery in the WHERE clause.

79. For UPDATE, use SHARE MODE to prevent exclusive locks.

80, restart MySQL, remember to warm up the database, to ensure that the data loaded into memory, improve query efficiency.

81. Use drop table, and then CREATE table instead of delete from to delete all data in the table.

82, minimize the data you want to query, only get the data you need, usually do not use *.

83, consider the persistent connection, rather than establishing the connection multiple times, has reduced the consumption of resources.

84, the benchmark query, including the server load, sometimes a simple query can affect other queries.

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

86. Test all suspicious queries in a development environment that contains copies of production environment data.

MySQL backup process:

87. Backup on a level two replication server.

88, the backup process to stop the replication of data to prevent data dependencies and foreign key constraints inconsistent.

89, after completely stop MySQL, and then back from the data file.

90. If you are backing up with MySQL dump, back up the binary logs at the same time-ensure that the replication process is not interrupted.

91. Do not trust backup of LVM snapshots-you may create inconsistent data that will cause problems in the future.

92. Make a backup of each table, which makes it easier to recover a single table-if the data is independent from the other tables.

93. When using Mysqldump, specify the-opt parameter.

94, pre-backup detection and optimization of the table.

95, temporarily disable foreign key constraints, to improve the speed of imports.

96, temporarily disable the uniqueness check, to improve the speed of the import.

97. After each backup, calculate the size of the database/table data and index to monitor its growth.

98. Use a timed task (cron) script to monitor errors and delays from library replication.

99, backup data regularly.

100. Regularly test the backed up data

100 recommendations for MySQL tuning/optimization