Shulou(Shulou.com)06/01 Report--

The following brings you 100 ways to tune / optimize MySQL. If you are interested, let's take a look at this article. I believe it will be of some help to you after reading the 100 methods of MySQL tuning / optimization.

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

MySQL monitors MySQL CVM hardware and OS (operating system) tuning: 1. There is enough physical memory to load the entire InnoDB file into memory-if the accessed file is in memory instead of on disk, InnoDB will be much faster.

2. Try your best to avoid Swap operations-swapping reads data from disk, so it will be slow.

3. Use battery-powered RAM (Battery-Backed RAM).

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

5. Avoid using RAID5-and checking needs to ensure integrity, which is expensive.

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

7. Separate temporary files and replication logs from data files-background writes affect the read and write operations of the database from disk files.

8. More disk space equals higher speed.

9. The faster the disk, the better.

10. SAS is better than SATA.

11. Small disks are faster than large disks, especially in RAID.

12. Use battery-powered cache RAID (Battery-Backed Cache RAID) controller.

Avoid using floppy disk arrays.

14. Consider using solid-state IO cards (not disks) as data partitions-for almost all orders of magnitude, this card can support 2 GBps write operations.

15. On Linux systems, set the value of swappiness to 0-there is no reason to cache files on the database cloud server, which is more commonly used in Web cloud servers or desktop applications.

16. Use noatime and nodirtime to mount the file system whenever possible-there is no need to update the modification time of the file for each visit.

17. Use the XFS file system-a faster and 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-for maximum performance benchmarks.

19. In Linux systems, using NOOP or DEADLINE IO schedulers-CFQ and ANTICIPATORY schedulers have been proven to be slower than NOOP and DEADLINE.

Use a 64-bit operating system-there is more memory available for addressing and MySQL use.

21. Delete unused packages and daemons from the CVM-reduce resource consumption.

Configure host that uses MySQL and host of MySQL itself in a host file-so there is no DNS lookup.

Never forcibly kill a MySQL process-you will damage the database and run a backup.

24. Let your CVM only serve MySQL-background processors and other services will take up CPU time of the database.

MySQL configuration: 25. Use innodb_flush_method=O_DIRECT to avoid double buffers when writing.

Avoid using O_DIRECT and EXT3 file systems-this serializes everything written.

Allocate enough innodb_buffer_pool_size to load the entire InnoDB file into memory-reduce reading from disk.

Don't make the innodb_log_file_size too large, which can be faster and have more disk space-frequent refreshes can help reduce recovery time in the event of a failure.

Do not use both innodb_thread_concurrency and thread_concurrency variables-these two values are not compatible.

30. Specify a small value for max_connections-too many connections will deplete your RAM, causing the entire MySQL CVM to be locked.

31. Keep thread_cache at a relatively high value, about 16-to prevent the speed from slowing down when opening the connection.

32. Use skip-name-resolve-remove DNS lookup.

33. If your query repetition rate is high and your data does not change often, use query caching-however, using query caching on frequently changed data can have a negative impact on performance.

34. Add temp_table_size-prevent disk writing.

35. Add max_heap_table_size-prevent disk writing.

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

Monitor key_read_requests and key_reads to determine the value of key_buffer-the read requirement of key should be higher than the value of key_reads, otherwise it would be inefficient to use key_buffer.

38. Setting innodb_flush_log_at_trx_commit = 0 can improve performance, but keeping the default value (1) can ensure the integrity of the data and ensure that replication will not lag.

There is a test environment that makes it easy to test your configuration and can be restarted frequently without affecting the production environment.

MySQL Schema optimization: 40. Ensure the cleanliness of your database.

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

42. Add an index to the data.

43. Don't overuse the index. Evaluate your query.

44. Compress text and blob data types-to save space and reduce reading data from disk.

45, UTF 8 and UTF16 are slower than latin1.

46. Moderate use of triggers.

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

48. Use linked tables instead of expanding rows.

49. Pay attention to your data type and use the smallest possible.

50. If other data needs to be queried frequently, but blob/text does not, separate the other data from the blob/text data domain.

51. Check and optimize the table frequently.

52. Often do the optimization of rewriting InnoDB table.

Sometimes, when adding columns, it is faster to delete the index first, and then add the index later.

54. Choose different storage engines for different needs.

Log tables or audit tables use the ARCHIVE storage engine-write more efficiently.

56. Store session data in memcache instead of MySQL-memcache can set automatic expiration to prevent MySQL from costly read and write operations on temporary data.

57. If the length of the string is variable, use VARCHAR instead of CHAR-save space, because CHAR is a fixed length and VARCHAR is not (utf8 is not affected by this).

58. Make gradual changes to schema-a small change will have a huge impact.

59. Test all schema changes in the development environment, not in a mirror image of the production environment.

60. don't change your configuration file at will, which can have a very big impact.

61. Sometimes, a small amount of configuration is better.

62. Question the use of general MySQL configuration files.

Query optimization: 63. Use slow query logs to find out slow queries.

64. Use EXPLAIN to determine whether the query function is appropriate.

65. Test your queries frequently to see if you need to optimize performance-performance may change over time.

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

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

68. If appropriate, use GROUP BY instead of DISTINCT.

69. Index the columns of WHERE, GROUP BY, and ORDER BY.

70. make sure the index is simple and don't add multiple indexes to the same column.

Sometimes, MySQL chooses the wrong index, in which case USE INDEX is used.

72. Use SQL_MODE=STRICT to check for problems.

73. When there are fewer than 5 index fields, UNION functions as LIMIT, not OR.

74. Use INSERT ON DUPLICATE KEY or INSERT IGNORE instead of UPDATE. You need to SELECT before avoiding UPDATE.

Use index fields and ORDER BY instead of MAX.

Avoid using ORDER BY RAND ().

77. LIMIT MMagne will reduce the query efficiency and use it moderately in specific scenarios.

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

For UPDATE, use SHARE MODE to prevent exclusive locks.

80. When restarting MySQL, remember to preheat the database to ensure that the data is loaded into memory to improve query efficiency.

Use DROP TABLE, and then CREATE TABLE instead of DELETE FROM to delete all the data in the table.

82. Minimize the data you want to query and get only the data you need. Generally speaking, do not use *.

83. Considering persistent connections rather than establishing connections multiple times has reduced the consumption of resources.

84. Benchmark queries, including the load of the cloud server, sometimes a simple query will affect other queries.

85. When the load on the CVM increases, use SHOW PROCESSLIST to view slow / problematic queries.

86. Test all suspicious queries in a development environment that has a copy of the production environment data.

MySQL backup process: 87. Backup on the secondary replication CVM.

88. Stop data replication during backup to prevent inconsistencies between data dependencies and foreign key constraints.

89. After completely stopping MySQL, back up from the data file.

90. If you use MySQL dump for backup, back up the binary logs at the same time-make sure that the replication process is not interrupted.

91. Do not trust backups of LVM snapshots-inconsistent data may be created and problems may arise in the future.

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

93. Specify the-opt parameter when using mysqldump.

94. Check and optimize the table before backup.

95. Temporarily disable foreign key constraints to improve the speed of import.

96. Temporarily disable uniqueness checking to improve the speed of import.

97. after each backup, calculate the size of database / table data and indexes and monitor their growth.

98. Use scheduled task (cron) scripts to monitor errors and delays copied from the library.

99. Back up data regularly.

Test the backed-up data regularly.

Look at the details of the above 100 methods for tuning / optimization in MySQL and see if there is anything to gain. If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.