Original: www.monitis.com/blog/101-ti…
MySQL is a powerful open source database. With more and more applications on MySQL, MySQL has gradually encountered a bottleneck. Here are 101 tips for optimizing MySQL. Some techniques are appropriate for a particular installation environment, but the idea is the same. I’ve broken them down into several categories to help you understand.
Mysql monitor
MySQL server hardware and OS tuning:
1. There is enough physical memory to load entire InnoDB files into memory – InnoDB is much faster if the accessed files are in memory instead of on disk.
1, Do everything possible to avoid Swap – the Swap (Considerations) is reading data from disk, so it will be very slow.
3. Use battery-backed RAM.
4. Use an advanced disk array – preferably RAID10 or higher.
5. Avoid RAID5 — checksum is expensive to ensure integrity.
6. Separate your operating system from your data, not just logically, but physically as well – the overhead of reading and writing to the operating system affects database performance.
7. Separate temporary files and replication logs from data files – Background write operations affect database reads and writes from disk files.
8. More disk space equals more speed.
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 a battery-backed Cache RAID controller.
13. Avoid floppy disk arrays.
14. Consider using solid state IO cards (not disks) as data partitions — these cards can support 2 GBps writes for almost all levels of data.
15. On Linux, set swappiness to 0 – there is no reason to cache files on the database server, which is more common on Web servers or desktop applications.
Mount the file system with noatime and nodirTime whenever possible – there is no need to update the file modification time for each access.
17. Use XFS file system – a faster, smaller file system than ext3, with more journaling options, and MySQL has double buffer issues on ext3.
18. Optimize your XFS file system log and buffer parameters for maximum performance benchmarks.
16, In Linux systems, using the NOOP or DEADLINE IO scheduler – CFQ and ANTICIPATORY schedulers have been shown to be slower than NOOP and DEADLINE.
20. Use 64-bit operating systems – more memory available for addressing and MySQL use.
21. Remove unused packages and daemons from the server – reducing resource usage.
22, configure both MySQL host and MySQL host in a host file — no DNS lookup.
Never force a MySQL process to kill – you will corrupt the database and run a backup.
24. Make your server MySQL only – spoolers and other services take up CPU time on your database.
Mysql configuration
25, use innodb_flush_method=O_DIRECT to avoid double-buffer writing.
26. Avoid using O_DIRECT and EXT3 filesystems — this serializes everything written.
27. Allocate enough Innodb_buffer_POOL_size to load the entire InnoDB file into memory – reducing reads from disk.
28. Don’t make innodb_log_file_size too large, it will be faster and have more disk space – refreshing frequently helps reduce recovery time in the event of a failure.
29. Do not use the innodb_thread_concurrency and thread_concurrency variables at the same time — the two values are not compatible.
30, Specify a small value for max_connections – too many connections will use up your RAM and cause the entire MySQL server to be locked.
31. Keep thread_cache at a relatively high value, around 16 — to prevent slowing down when opening a connection.
32, use skip-name-resolve — to 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 changing data can have a negative impact on performance.
34. Increase temp_table_size – prevent disk writes.
35, increase max_heap_table_size — prevent disk write.
36. Do not set sort_buffer_SIZE too high – the connection may run out of memory too quickly.
37. Monitor key_read_requests and key_reads to determine the value of key_buffer — the read requirement for key should be higher than the value for key_reads, otherwise using key_buffer is inefficient.
38. Set innodb_flush_log_at_trx_COMMIT = 0 to improve performance, but keep the default value (1) to ensure data integrity and no lag in replication.
There is a test environment, easy to test your configuration, can often restart, will not affect the production environment.
Mysql Schema optimization
Keep your database clean.
Archive old data – remove excess rows retrieved or returned from queries
Index the data.
Don’t overuse indexes and evaluate your queries.
Compress text and BLOb data types – To save space, reduce data read from disk.
UTF 8 and UTF16 are slower than latin1.
Use triggers sparingly.
Keep data to a minimum of redundancy – don’t copy unnecessary data.
48. Use linked tables, not extended rows.
Pay attention to your data type and use the smallest possible.
If other data needs to be queried frequently and blob/text does not, separate the blob/text data domain from the rest of the data.
Check and optimize the table frequently.
52, Often do rewrite InnoDB table optimization.
53. Sometimes, when adding a column, it is faster to drop the index first and add the index later.
Choose different storage engines for different needs.
55, Log tables or audit tables use the ARCHIVE storage engine – write more efficiently.
56. Store session data in memcache, not MySQL — Memcache can be set to expire automatically, preventing MySQL from costly reads and writes to temporary data.
57. If the string length is variable, use VARCHAR instead of CHAR — saving space, since CHAR is fixed length and VARCHAR is not (utF8 is not affected by this).
Make gradual schema changes – a small change can have a big impact.
59. Test all schema changes in the development environment, not in the production environment.
60, Do not arbitrarily change your configuration file, it can have a very big impact.
Sometimes, a small amount of configuration is better.
62, Query the use of generic MySQL configuration files.
Mysql query optimization
63. Use slow query logs to find slow queries.
64, Use EXPLAIN to decide if the query function is appropriate.
65, test your queries frequently to see if performance tuning is needed – performance may change over time.
Avoid using count(*) on the entire table; it 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 if appropriate.
Add index to WHERE, GROUP BY, and ORDER BY columns.
Keep indexes simple and do not add multiple indexes to the same column.
71, Sometimes MySQL selects the wrong INDEX. In this case, USE INDEX.
72, use SQL_MODE=STRICT to check the problem.
73, When the index field is less than 5, use LIMIT instead of OR.
74, Use INSERT ON DUPLICATE KEY or INSERT IGNORE instead of UPDATE to avoid SELECT before UPDATE.
75, Use index fields and ORDER BY instead of MAX.
76, Avoid using ORDER BY RAND().
77, LIMIT M,N in specific scenarios will reduce query efficiency, use it sparingly.
78, Use UNION instead of subquery in WHERE clause.
79. For UPDATE, use SHARE MODE to prevent exclusive locking.
80, When restarting MySQL, remember to preheat the database to ensure that data is loaded into memory to improve query efficiency.
81, Use DROP TABLE and then CREATE TABLE instead of DELETE FROM to DROP all data in the TABLE.
Minimize the data you need to query, only get the data you need, generally don’t use *.
83, Consider persistent connections, rather than multiple connections, has reduced the consumption of resources.
84, Benchmark queries, including server load, sometimes a simple query can affect other queries.
85, When the load on the server increases, use SHOW PROCESSLIST to see slow/problematic queries.
Test all suspicious queries in a development environment with a copy of production data.
Mysql backup procedure
87. Back up data on the secondary replication server.
88. Stop data replication during backup to prevent inconsistency between data dependencies and foreign key constraints.
89, Stop MySQL completely, and then backup from the data file.
If MySQL dump is used for backup, please back up binary logs as well – to ensure that the replication process is not interrupted.
91. Don’t trust backups of LVM snapshots — inconsistent data can be created and cause problems in the future.
92. Make a backup for each table, which makes it easier to restore a single table if the data is independent from other tables.
93. Specify -opt when mysqldump is used.
Detect and optimize tables before backup.
95, Temporarily disable foreign key constraints to speed up imports.
Temporarily disable uniqueness checking to improve import speed.
After each backup, calculate the size of database/table data and index and monitor its growth.
98. Use scheduled task (CRON) scripts to monitor errors and delays in replication from the library.
99. Back up your data regularly.
100. Test your backup data periodically.
Unveils The World’s First Free On-Demand MySQL Monitoring Monitis Unveils The World’s First Free On-Demand MySQL Monitoring.
Recommended reading
Recommended: Share a set of advanced Java pen questions (real shot in HD)
The latest Java interview questions and answers are compiled
Long press attention, more exciting!