January 24, 2018

The Commandments for Optimal MySQL Performance

What we have to keep in mind for an Optimal MySQL Performance ? There are many, I am just writing down what I seriously consider for an Optimal and Scalable MySQL Performance, As you usual the comments are most welcome, Thank you !!

  1. Go generous while buying physical memory, InnoDB works great when files are accessed from memory rather than from disk.
  2. Lean MySQL Schema and SQL works great. Trust me, The fat is not Optimal everywhere !!
  3. Carefully choose your Datatype, The smaller ones are better.
  4. You need Indexes to search records faster but Index overuse / abuse will have negative impact to performance, Remove unused indexes.
  5. Occasionally MySQL go wrong in choosing Optimal Indexes, Here help MySQL using hints like “USE INDEX”.
  6. Think seriously about indexing columns in WHERE, GROUP BY, and ORDER BY queries.
  7. Less Rows Examined is great !!!
  8. Reduce disk reads, Compress TEXT, BLOB
  9. Make incremental Schema changes sometimes small changes cause severe Performance improvements / degradation.
  10. Lookout for duplicate records they seriously cause Performance Bottlenecks.
  11. Archive the old records, They make Sort / Search especially very expensive.
  12. Profile your MySQL regularly to define the trending chart of Performance, Nothing goes wrong overnight, Performance degradation happens in several cases over a period of time so think proactive, That’s cool 🙂
  13. The fat multi-core processors hyper-threading enabled works great for high performance MySQL operations
  14. Watch out for excessive pagination queries.
  15. Go conservative about MySQL Configs (sometimes less is more).
  16. Be cautious with MySQL configuration parameters innodb_thread_concurrency and thread_concurrency variables, Don’t use them if you don’t clearly understand how they work !!
  17. High max_connections excessively consumes the RAM.
  18. Use query cache only if you have repetitive queries, Using query cache on data that changes often will give you a performance hit.
  19. Remember to warm your MySQL Database in every restart, This get schema, SQL back to memory.
  20. Fetch only the data you need in SELECT queries
  21. Consider persistent connection against multiple connections
  22. Sort_buffer_size  eat memory very fast because it is actually consumed per connection so please be very cautious while increasing it.
  23. Use large redo logs
  24. Measure cost of query with Response Time, Use MySQL slow query log to diagnose query performance.
  25. I use SHOW PROCESSLIST while load increases, This works good for me during diagnostics / forensics of slow queries.
  26. Use Performance Schema wisely, This is an great MySQL performance orchestration platform.
  27. Avoid using Triggers as much as possible.
  28. Use ARCHIVE Storage engine for audit purposes.
  29. You need Backups but do that on a secondary Replication Instance, This actually is a quick win to boost the Performance of your MySQL Primary Master Instance.
  30. Split reads across several instances / disk, This helps distributing load efficiently
About Shiv Iyer 12 Articles
Independent and vendor neutral MySQL, MariaDB and Percona Server consultant with core expertise in performance, scalability and high availability. Shiv is a frequent speaker in Open Source Software conferences worldwide. Shiv currently is the founder and principal of MinervaSQL, An boutique private-label MySQL, MariaDB and Percona Server consulting and remote DBA services company with several customers worldwide. Shiv in the past worked for companies like MySQL AB, SUN, Oracle, AOL, Nike, eBay, PayPal, PalominoDB (acquired by Pythian) and Percona in Sr. MySQL consulting and professional services roles. To engage MinervaSQL for MySQL consulting and professional services, please send email to shiv@minervasql.com