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

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 tuning and optimizing your MySQL installation. Some techniques are specific to a specific installation environment, but these ideas are common. I have divided them into several categories to help you master more MySQL tuning and optimization skills.

MySQL server hardware and operating system tuning:

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

two。 Avoid swapping partitions with Swap at all costs-swapping is read from the hard disk, which is slow.

3. Use battery powered RAM (Note: RAM is random access memory).

4. Use advanced RAID (note: Redundant Arrays of Inexpensive Disks, that is, disk array)-preferably RAID10 or higher.

5. Avoid RAID5 (a storage solution that combines storage performance, data security, and storage costs)-there is a price to pay for verifying database integrity.

6. Separate the operating system from the data partition, not only logically, but also physically-the read and write operations of the operating system can affect the performance of the database.

7. Put the MySQL temporary space and replication logs in different partitions from the data-when the database background reads and writes from disk, it affects the performance of the database.

8. More disk space equals faster speed.

9. A better and faster disk.

10. Use SAS (Serial Attached SCSI, serial connection SCSI) instead of SATA (SATA).

11. Smaller hard drives are faster than larger hard drives, especially in RAID configurations.

twelve。 Use a cache RAID controller supported by the battery.

13. Avoid using software disk arrays.

14. Consider using solid-state IO cards (not disk drives) for data partitions-these cards can support 2GB/s write speeds for almost any amount of data.

15. Set the value of swappiness to 0 in Linux-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 modification time for accessing the database file.

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

18. Adjust the XFS file system log and buffer variables-for the highest performance standards.

19. In Linux systems, use the NOOP or DEADLINE IO timing scheduler-this CFQ and DEADLINE timing scheduler is very slow compared to the NOOP and ANTICIPATORY timing schedulers.

20. Use a 64-bit operating system-for MySQL, there will be greater memory support and usage.

21. Remove unused installation packages and daemons from the server-less resource footprint.

twenty-two。 Put the host using MySQL and your MySQL host into a hosts file-no DNS lookup.

23. Do not forcibly kill a MySQL process-you will damage the database and the program that is running the backup.

24. Contributing the server to MySQL-background processes and other services can reduce the amount of time that databases occupy CPU.

MySQL configuration:

25. Use innodb_flush_method=O_DIRECT to avoid double buffering when writing.

twenty-six。 Avoid using O_DIRECT and EXT3 file systems-you will serialize everything you want to write.

twenty-seven。 Allocate enough innodb_buffer_pool_size to load the entire InnoDB file into memory-read less from disk.

twenty-eight。 Do not set the innodb_log_file_size parameter too large so that you can have more disk space at the same time faster-it is usually good to lose more logs and reduce the time it takes to recover the database after a database crash.

twenty-nine。 Do not mix the innodb_thread_concurrency and thread_concurrency parameters-these two values are incompatible.

thirty。 Assign a very small number to the max_connections parameter-too many connections will exhaust RAM and lock the MySQL service.

thirty-one。 Keep thread_cache at a relatively high number, about 16-to prevent slow opening of connections.

thirty-two。 Use the skip-name-resolve parameter-remove the DNS lookup.

thirty-three。 If your queries are repetitive and the data does not change often, you can use query caching. But if your data changes frequently, you will be disappointed to use query caching.

thirty-four。 Increase the temp_table_ size value to prevent writing to disk

thirty-five。 Increase the max_heap_table_ size value to prevent writing to disk

thirty-six。 Don't set the sort_buffer_ size too high, or you will run out of memory quickly

thirty-seven。 According to the value of key_read_requests and key_ reads to determine the size of key_buffer, in general, key_read_requests should be higher than the value of key_ read, otherwise you can not use key_buffer efficiently

thirty-eight。 Setting innodb_flush_log_at_trx_commit to 0 will improve performance, but if you want to keep the default value (1), you need to ensure the integrity of the data and make sure that replication does not lag.

thirty-nine。 You need to have a test environment to test your configuration and restart frequently without affecting normal production.

MySQL mode optimization:

forty。 Keep your database organized.

forty-one。 Old data archiving-Delete redundant rows to return or search queries.

forty-two。 Index your data.

forty-three。 Do not overuse the index, compare and query.

forty-four。 Compress text and BLOB data types-to save space and reduce disk reads.

forty-five。 Both UTF 8 and UTF16 are less efficient than latin1.

forty-six。 Use triggers in a moderate manner.

forty-seven。 Redundant data is kept to a minimum-unnecessary data is not repeated.

forty-eight。 Use linked tables instead of expanding rows.

forty-nine。 Pay attention to the data type and use the smallest one in your real data as much as possible.

fifty。 If other data is often used for queries, but BLOB / TEXT data is not, BLOB / TEXT data is separated from other data.

fifty-one。 Check and regularly optimize tables.

fifty-two。 InnoDB table optimizations are often rewritten.

fifty-three。 Sometimes, drop the index when adding a column, and then add the index back, which is faster.

fifty-four。 Different storage engines are used for different needs.

fifty-five。 Use the archive storage engine log table or audit table-this is more effectively written.

fifty-six。 Session data is stored in memcache instead of MySQL-caching allows automatic filling of values and prevents you from creating spatio-temporal data that is difficult to read and write to MySQL.

fifty-seven。 Use VARCHAR instead of CHAR when storing variable-length strings-saves space because of fixed-length CHAR, while VARCHAR length is not fixed (UTF8 is not affected by this).

fifty-eight。 A gradual change in patterns-a small change can have a huge impact.

fifty-nine。 Test all patterns in the development environment to reflect production changes.

sixty。 Don't change the values in your configuration file at will, it can have a disastrous effect.

sixty-one。 Sometimes, less configs is more in MySQL.

sixty-two。 Use a general MySQL configuration file when in doubt.

Query optimization:

sixty-three。 Use slow query logs to find slow queries.

sixty-four。 Use the execution plan to determine whether the query is running properly.

sixty-five。 Always test your queries to see if they are running at their best-performance always changes over time.

sixty-six。 Avoid using count (*) on the entire table, which may lock the entire table.

sixty-seven。 Keep queries consistent so that subsequent similar queries can use query caching.

sixty-eight。 Use GROUP BY instead of DISTINCT where appropriate.

sixty-nine。 Use indexed columns in the WHERE, GROUP BY, and ORDER BY clauses.

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

seventy-one。 Sometimes MySQL uses the wrong index, and USE INDEX is used in this case.

seventy-two。 Check for problems with using SQL_MODE=STRICT.

seventy-three。 For index fields with less than 5 records, LIMIT is not used in OR when using UNION.

seventy-four。 To avoid SELECT before updating, use INSERT ON DUPLICATE KEY or INSERT IGNORE, do not use UPDATE to implement.

seventy-five。 Do not use MAX, use index fields and ORDER BY clauses.

seventy-six。 Avoid using ORDER BY RAND ().

77.LIMIT MForce N can actually slow down queries, in some cases, in a controlled way.

seventy-eight。 Use UNION instead of subqueries in the WHERE clause.

seventy-nine。 For UPDATES (update), use SHARE MODE (shared mode) to prevent exclusive locks.

eighty。 After restarting MySQL, remember to warm your database to ensure that your data is fast in memory and query.

eighty-one。 Use DROP TABLE,CREATE TABLE DELETE FROM to delete all data from the table.

eighty-two。 Minimizing data in querying the data you need takes a lot of time to use.

eighty-three。 Consider persistent connections rather than multiple connections to reduce overhead.

eighty-four。 Benchmark queries, including using the load on the server, sometimes a simple query can affect other queries.

eighty-five。 When the load increases on your server, use SHOW PROCESSLIST to view slow and problematic queries.

eighty-six。 All suspicious queries tested in the mirrored data generated in the development environment.

MySQL backup process:

eighty-seven。 Make a backup from a secondary replication server.

eighty-eight。 Stop replication during backup to avoid inconsistencies on data dependencies and foreign key constraints.

eighty-nine。 Stop MySQL completely and back up from the database file.

ninety。 If you use MySQL dump for backup, back up the binary log files at the same time-make sure the replication is not interrupted.

ninety-one。 Don't trust LVM snapshots-this is likely to result in data inconsistencies that will cause you trouble in the future.

ninety-two。 To make it easier to recover from a single table, export data on a table-by-table basis-if the data is isolated from other tables.

ninety-three。 Use-opt when using mysqldump.

ninety-four。 Check and optimize tables before backing up.

ninety-five。 In order to import faster, foreign key constraints are temporarily disabled during import.

ninety-six。 In order to import more quickly, uniqueness detection is temporarily disabled during import.

ninety-seven。 Calculate the size of databases, tables, and indexes after each backup to better monitor data size growth.

ninety-eight。 Monitor errors and delays for replication instances through automatic scheduling scripts.

ninety-nine。 Perform backups regularly.

one hundred。 Test your backup regularly.

Finally, 101: execute MySQL monitoring: Monitis Unveils The World's First Free On-demand MySQL Monitoring.

Original English text: 101 Tips to MySQL Tuning and Optimization (July 12, 2011)

Http://www.oschina.net/translate... Ng-and-optimization

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.