"Reprint: MySQL optimizer" 101 MySQL optimization 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 techniques are specific to the installation environment, but these ideas are generic. I've divided them into several categories to help you master more of the tuning and optimization techniques of MySQL.

MySQL server hardware and operating system tuning:

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

2. Avoid using swap swap partitions at all costs – the exchange 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, i.e. disk array) – preferably RAID10 or higher.

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

6. Separating the operating system and data partitions, 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 staging space and the replication log and data into different partitions – the database performance is affected when the database is read and written from the disk in the background.

8. More disk space equals faster speed.

9. Better and faster disk.

10. Use SAS (Note: Serial attached SCSI, serial attached SCSI) instead of SATA (note: SATA, serial drive).

11. The smaller hard drive is faster than the larger hard drive, especially in the case of RAID configuration.

12. Use the battery-backed cache RAID controller.

13. Avoid using a software disk array.

14. Consider using solid-state IO cards (not disk drives) for data partitioning – These cards can support 2gb/s write speeds for almost any amount of data.

15. The value of setting swappiness in Linux is 0– there is no reason to cache files in the database server, which is the advantage of a server or desktop.

16. Mount the file system using Noatime and Nodirtime if possible – there is no reason to update the modified time of accessing the database files.

17. Using the XFS file system – a faster, smaller file system than ext3, with many logging options, and ext3 has been proven to have double buffering problems with MySQL.

18. Adjust XFS file system logs and buffering variables – for the highest performance standards.

19. In Linux systems, this 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 more memory support and use.

21. Remove unused installation packages and daemons on the server – less resource usage.

22. Place the host with MySQL and your MySQL host in a Hosts file – no DNS lookups.

23. Do not force a MySQL process to be killed – you will corrupt the database and the program that is running the backup.

24. Contributing servers to mysql– background processes and other services can shorten the time the database consumes CPU.

MySQL configuration:

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

26. Avoid using the O_direct and EXT3 file Systems – you will serialize all the files you want to write.

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

28. Do not set the Innodb_log_file_size parameter too large, so that you can have more disk space faster – it is usually good to lose more logs, which can reduce the time to recover the database after a database crash.

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

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

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

32. Use the Skip-name-resolve parameter – remove DNS lookups.

33. If your queries are duplicates and the data does not change frequently, you can use the query cache. But if your data changes frequently, then using the query cache will disappoint you.

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 be exhausted soon

37. According to the key_read_requests and Key_reads values to determine the size of the Key_buffer, generally key_read_requests should be higher than the key_reads value, otherwise you can not effectively use Key_buffer

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

39. You need to have a test environment to test your configuration, and can often be restarted without affecting 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, comparisons and queries.

44. Compress text and BLOB data types – to save space and reduce disk read times.

Both UTF 8 and UTF16 are below latin1 execution efficiency.

46. Use the trigger in moderation.

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

48. Use the linked table instead of extending the row.

49. Note the data type, and use the smallest one possible in your real data.

50. If other data is often used for querying, and blob/text data is not, separate blob/text data from other data.

51. Check and constantly optimize the table.

52. Often rewrite InnoDB table optimization.

53. Sometimes, when you add a column, you delete the index, and then you add the index back, which is faster.

54. Use different storage engines for different needs.

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

56. Session data is stored in the cache (memcache) instead of MySQL – the cache allows auto-filling of values automatically and prevents you from creating spatiotemporal data that is difficult to read and write to MySQL.

57. When storing variable-length strings, use varchar instead of char– to save space because fixed-length char, while varchar length is not fixed (UTF8 is not affected by this).

58. Progressive pattern Changes – a small change that can have a huge impact.

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

60. Do not arbitrarily change the value in your profile, it can have a catastrophic impact.

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

62. Use a common MySQL configuration file when in doubt.

Query optimization:

63. Use the slow query log to find slow queries.

64. Use the execution plan to determine whether the query is functioning correctly.

65. Always test your queries to see if they are running optimally – over time performance will always change.

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. In where, the GROUP by and ORDER BY clauses use indexed columns.

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

71. Sometimes MySQL uses the wrong index, and use index for this case.

72. Check for problems with Sql_mode=strict.

73. For indexed fields where the number of records is less than 5, the use of limit at Union is not the OR.

74. To avoid the pre-update Select, use Insert on DUPLICATE key or insert IGNORE, do not use update to implement.

75. Do not use the index field and the ORDER BY clause with MAX.

76. Avoid using ORDER by RAND ().

A. LIMIT m,n can actually slow down the query in some cases, using it sparingly.

78. Use union instead of subquery in the WHERE clause.

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

80. In the restart MySQL, remember to warm your database to make sure your data is fast in memory and query.

81. Delete 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 * consume a lot of time.

83. Consider persistent connections, not multiple connections, to reduce overhead.

84. Benchmark queries, including the use of 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 the mirrored data generated in the development environment.

MySQL Backup process:

87. Back up from a level two replication server.

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

89. Completely stop MySQL and back up from the database file.

90. If you are backing up using MySQL dump, back up the binary log files simultaneously – Make sure that replication is not interrupted.

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

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

93. Use –opt when using mysqldump.

94. Check and optimize the tables before backing up.

95. For faster import, the foreign KEY constraint is temporarily disabled on import.

96. For faster import, uniqueness detection is temporarily disabled on import.

97. Calculate the size of the database, table, and index after each backup so that it is more sufficient to monitor the growth of the data size.

98. Monitor replication instance errors and delays with the automatic dispatch script.

99. Perform regular backups.

100. Test your backups on a regular basis.

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