MySQL optimized 100 of the 100 suggestions for MySQL Optimization
MySQL is a powerful open-source database. With more and more database-driven applications, people have been pushing MySQL to its limit. Here are 100 tips for adjusting and optimizing MySQL installation. Some tips are applicable to specific installation environments, but these ideas are common. I have already divided them into several categories to help you master more MySQL adjustment and optimization skills.
MySQL server hardware and operating system adjustment:
1. Have enough physical memory to load the entire InnoDB file into the memory. The speed of accessing files in the memory is much faster than that on the hard disk.
2. Avoid using Swap to Swap a partition at any cost-the Swap is read from the hard disk, and its speed is very slow.
3. Battery-powered RAM (Note: RAM is random memory ).
4. Use advanced RAID (Note: Redundant Arrays of Inexpensive Disks, that is, the disk array)-preferably RAID 10 or higher.
5. Avoid RAID5 (Note: A storage solution that combines storage performance, data security, and storage costs)-ensuring database integrity verification is costly.
6. Separate the operating system from the Data Partition, not only logically, but also physically-the read/write operations of the operating system will affect the database performance.
7. Place the MySQL temporary space and copy logs and data in different partitions-when the database background reads and writes from the disk, the database performance will be affected.
8. More disk space equals faster speed.
9. better and faster disks.
10. Use SAS (Note: Serial Attached SCSI, that is, Serial connection SCSI) instead of SATA (Note: SATA, that is, Serial drive ).
11. Smaller hard disks are faster than larger ones, especially when RAID is configured.
12. Use the battery-Supported High-speed cache RAID Controller.
13. Avoid using a software disk array.
14. Consider using a solid-state iocard (not a disk drive) for data partitioning-these cards can support 2 Gb/s write speed for almost any amount of data.
15. Set the value of swappiness to 0 in Linux-there is no reason to cache files on the database server, which is an advantage of a server or desktop.
16. If possible, use noatime and nodirtime to mount the file system-there is no reason to update the modification time for accessing database files.
17. Use XFS file system-a faster and smaller file system than ext3, with many log options, and ext3 has been proven to have dual-buffering problems with MySQL.
18. Adjust the XFS File System Log and buffer variables-to the highest performance standard.
19. In Linux, The NOOP or deadline io scheduled scheduling program is very slow compared with the NOOP and DEADLINE scheduled scheduling program.
20. Use a 64-bit operating system-for MySQL, more memory is supported and used.
21. delete unused installation packages and daemon on the server-less resource usage.
22. Put the host using MySQL and your MySQL host in a hosts file-No DNS lookup.
23. Do not force a MySQL process to be killed-you will damage the database and the program that is running the backup.
24. Contributing servers to MySQL-background processes and other services can shorten the CPU usage time of databases.
MySQL Configuration:
25. When writing data, innodb_flush_method = O_DIRECT is used to avoid double buffering.
26. Avoid using O_DIRECT and EXT3 file systems-you will serialize all the files to be written.
27. allocate enough innodb_buffer_pool_size to load the entire InnoDB file to the memory-less read 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. It is usually good to lose more logs. After the database crashes, it can reduce the database recovery time.
29. Do not mix the innodb_thread_concurrency and thread_concurrency parameters-these two values are incompatible.
30. assign a very small number to the max_connections parameter-too many connections will use up RAM and lock the MySQL service.
31. Keep thread_cache in a relatively high number, around 16-to prevent slow connection.
32. Use the skip-name-resolve parameter-Remove DNS lookup.
33. If your queries are repeated and the data does not change frequently, you can use the query cache. However, if your data changes frequently, using the query cache will disappoint you. \
34. Increase the value of temp_table_size to prevent data from being written to the disk.
35. Increase the max_heap_table_size value to prevent data from being written to the disk.
36. Do not set sort_buffer_size too high. Otherwise, your memory will soon be exhausted.
37. The key_read_requests and key_reads values are used to determine the key_buffer size. Generally, the key_read_requests value should be higher than the key_reads value. Otherwise, you cannot use key_buffer efficiently.
38. Setting innodb_flush_log_at_trx_commit to 0 will improve performance. However, if you want to retain the default value (1), you must ensure data integrity and ensure that the replication will not lag.
39. You need to have a test environment to test your configuration and often restart it without affecting normal production.
MySQL mode optimization:
40. Keep your database organized.
41. Archive old data-delete redundant rows and return or search for queries.
42. Add an index to your data.
43. Do not over-use indexes, comparison and query.
44. compress text and BLOB data types to save space and reduce disk reads.
45. UTF 8 and UTF16 are both less efficient than latin1.
46. Use the trigger in a controlled manner.
47. Keep redundant data to a minimum-do not repeat unnecessary data.
48. Use a chain table instead of expanding rows.
49. Pay attention to the data type. Use the smallest data type in your real data.
50. If other data is often used for query, but BLOB/TEXT data is not, the BLOB/TEXT data is separated from other data.
51. Check and frequently optimize the table.
52. Rewrite InnoDB table optimization frequently.
53. Sometimes, the index is deleted when a column is added, and then the index is added back, which will be faster.
54. Use different storage engines to meet different requirements.
55. Use the archive storage engine log table or audit table-this is more effective to write.
56. session data is stored in the cache rather than in MySQL. The cache allows automatic value filling and prevents you from creating time-space data that is hard to read and write to MySQL.
57. Use VARCHAR instead of CHAR-to save space when storing variable-length strings, because the fixed-length CHAR and VARCHAR length are not fixed (UTF8 is not affected ).
58. Gradual changes to models-a small change can have a huge impact.
59. test all modes in the development environment to reflect production changes.
60. Do not arbitrarily change the value in your configuration file. It can have disastrous effects.
61. Sometimes, less configs in MySQL is more.
62. If you have any questions, use a common MySQL configuration file.
Query Optimization:
63. Use slow query logs to find slow queries.
64. Use the execution plan to determine whether the query is normal.
65. Always test your query to see if they are running in the best state-the performance will change over time.
66. Avoid using count (*) on the entire table. It may lock the entire table.
67. Make the query consistent so that similar queries can use the query cache.
68. Use group by instead of DISTINCT as appropriate.
69. Use indexed columns in the 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 an incorrect INDEX. In this case, use index is used.
72. Check whether SQL _MODE = STRICT is used.
73. For index fields with the number of records less than 5, use LIMIT during UNION instead of OR.
74. To avoid SELECT Before UPDATE, use insert on duplicate key or insert ignore instead of UPDATE.
75. Do not use MAX. Use the index field and order by clause.
76. Avoid using order by rand ().
77. Limit m, N can actually slow down the use of queries in some cases.
78. Use UNION in the WHERE clause to replace subqueries.
79. For UPDATES (update), use share mode to prevent exclusive locks.
80. When you restart MySQL, remember to warm up your database to ensure that your data is saved and queried quickly.
81. Use drop table, create table delete from to DELETE all data FROM the TABLE.
82. The minimal data is used to query the data you need, which consumes a lot of time.
83. Consider persistent connections instead of multiple connections to reduce overhead.
84. Benchmark query, including the use of server load, 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 tested in the image data generated in the development environment.
MySQL backup process:
87. Back up data from the secondary replication server.
88. Stop copying during backup to avoid inconsistency between data dependency and foreign key constraints.
89. Stop MySQL and back up the database files.
90. If you use MySQL dump for backup, back up the binary log file at the same time to ensure that the replication is not interrupted.
91. Do not trust LVM snapshots-this may cause data inconsistency and will cause you trouble in the future.
92. Export data in units of tables for easier single-Table recovery-if the data is isolated from other tables.
93. Use-opt when using mysqldump.
94. Check and optimize the table before backup.
95. For faster import, the foreign key constraint is temporarily disabled during import.
96. For faster import, the uniqueness check is temporarily disabled during import.
97. Calculate the size of the database, table, and index after each backup to better monitor the growth of data size.
98. Use an automatic scheduling script to monitor errors and latencies of copying instances.
99. Perform regular backup.
100. Regularly test your backup.
Articles you may be interested in:Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
