{"id":1221,"date":"2020-05-02T07:30:50","date_gmt":"2020-05-02T00:30:50","guid":{"rendered":"https:\/\/www.whplus.com\/blog\/?p=1221"},"modified":"2021-06-23T10:10:57","modified_gmt":"2021-06-23T03:10:57","slug":"mysql-tuning-and-optimization","status":"publish","type":"post","link":"https:\/\/www.whplus.com\/blog\/2020\/05\/02\/mysql-tuning-and-optimization.html","title":{"rendered":"MySQL Tuning and Optimization"},"content":{"rendered":"

MySQL<\/a> is a powerful open-source database. With more and more database driven applications, people have been pushing MySQL to its limits. Here are some tips for tuning and optimizing your MySQL install. Some tips are specific to the environment they are installed on, but the concepts are universal.<\/p>\n

MySQL Server Hardware and OS Tuning:<\/strong>
\n1. Have enough physical memory to load your entire InnoDB file into memory \u2013 InnoDB is much faster when the file can be accessed in memory rather than from disk.
\n2. Avoid Swap at all costs \u2013 swapping is reading from disk, its slow.
\n3. Use Battery-Backed RAM.
\n4. Use an advanced RAID \u2013 preferably RAID10 or higher.
\n5. Avoid RAID5 \u2013 the checksum needed to ensure integrity is costly.
\n6. Separate your OS and data partitions, not just logically, but physically \u2013 costly OS writes and reads will impact your database performance.
\n7. Put your mysql temp space and replication logs on a separate partition than your data \u2013 background writes will impact your database when it goes to write\/read from disk.
\n8. More disks equals more speed.
\n9. Faster disks are better.
\n10. Use SAS over SATA.
\n11. Smaller disks are faster than larger disks, especially in RAID configs.
\n12. Use Battery-Backed Cache RAID controllers.
\n13. Avoid software raids.
\n14. Consider using Solid State IO Cards (not disk drives) for your data partition \u2013 these cards can sustain over 2GB\/s writes for almost any amount of data.
\n15. On Linux set your swappiness value to 0 \u2013 no reason to cache files on a database server, this is more of a web server or desktop advantage.
\n16. Mount filesystem with noatime and nodirtime if available \u2013 no reason to update database file modification times for access.
\n17. Use XFS filesystem \u2013 a faster, smaller filesystem than ext3 and has more options for journaling, also ext3 has been shown to have double buffering issues with MySQL.
\n18. Tune your XFS filesystem log and buffer variables \u2013 for maximum performance benchmark.
\n19. On Linux systems, use NOOP or DEADLINE IO scheduler \u2013 the CFQ and ANTICIPATORY scheduler have been shown to be slow vs NOOP and DEADLINE scheduler.
\n20. Use a 64-bit OS \u2013 more memory addressable and usable to MySQL.
\n21. Remove unused packages and daemons from servers \u2013 less resource stealing.
\n22. Put your host that use MySQL and your MySQL host in a hosts file \u2013 no dns lookups.
\n23. Never force kill a MySQL process \u2013 you will corrupt your database and be running for the backups.
\n24. Dedicate your server to MySQL \u2013 background processes and other services can steal from the db cpu time.<\/p>\n

MySQL Configuration:<\/strong>
\n25. Use innodb_flush_method=O_DIRECT to avoid a double buffer when writing.
\n26. Avoid O_DIRECT and EXT3 filesystem \u2013 you will serialize all your writes.
\n27. Allocate enough innodb_buffer_pool_size to load your entire InnoDB file into memory \u2013 less reads from disk.
\n28. Do not make innodb_log_file_size too big, with faster and more disks \u2013 flushing more often is good and lowers the recovery time during crashes.
\n29. Do not mix innodb_thread_concurrency and thread_concurrency variables \u2013 these two values are not compatible.
\n30. Allocate a minimal amount for max_connections \u2013 too many connections can use up your RAM and lock up your MySQL server.
\n31. Keep thread_cache at a relatively high number, about 16 \u2013 to prevent slowness when opening connections.
\n32. Use skip-name-resolve \u2013 to remove dns lookups.
\n33. Use query cache if your queries are repetitive and your data does not change often \u2013 however using query cache on data that changes often will give you a performance hit.
\n34. Increase temp_table_size \u2013 to prevent disk writes.
\n35. Increase max_heap_table_size \u2013 to prevent disk writes.
\n36. Do not set your sort_buffer_size too high \u2013 this is per connection and can use up memory fast.
\n37. Monitor key_read_requests and key_reads to determine your key_buffer size \u2013 the key read requests should be higher than your key_reads, otherwise you are not efficiently using your key_buffer.
\n38. Set innodb_flush_log_at_trx_commit = 0 will improve performance, but leaving it to default (1), you will ensure data integrity, you will also ensure replication is not lagging
\n39. Have a test environment where you can test your configs and restart often, without affecting production.<\/p>\n","protected":false},"excerpt":{"rendered":"

MySQL is a powerful open-source database. With more and more database driven applications, people have been pushing MySQL to its limits. Here are some tips for tuning and optimizing your MySQL install. Some tips are specific to the environment they are installed on, but the concepts are universal. MySQL Server Hardware and OS Tuning: 1.\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-1221","post","type-post","status-publish","format-standard","hentry","category-tutorial"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.whplus.com\/blog\/wp-json\/wp\/v2\/posts\/1221","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.whplus.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.whplus.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.whplus.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.whplus.com\/blog\/wp-json\/wp\/v2\/comments?post=1221"}],"version-history":[{"count":4,"href":"https:\/\/www.whplus.com\/blog\/wp-json\/wp\/v2\/posts\/1221\/revisions"}],"predecessor-version":[{"id":1319,"href":"https:\/\/www.whplus.com\/blog\/wp-json\/wp\/v2\/posts\/1221\/revisions\/1319"}],"wp:attachment":[{"href":"https:\/\/www.whplus.com\/blog\/wp-json\/wp\/v2\/media?parent=1221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.whplus.com\/blog\/wp-json\/wp\/v2\/categories?post=1221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.whplus.com\/blog\/wp-json\/wp\/v2\/tags?post=1221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}