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 !!<\/p>\n

\n
1. Go generous while buying physical memory, InnoDB works great\u00a0when files are accessed from memory rather than from disk.<\/li>\n
2. Lean MySQL Schema and SQL works great. Trust me, The fat is not Optimal everywhere !!<\/li>\n
3. Carefully choose your Datatype, The smaller ones are better.<\/li>\n
4. You need Indexes to search records faster but Index overuse \/ abuse will have negative impact to performance, Remove unused indexes.<\/li>\n
5. Occasionally MySQL go wrong in choosing Optimal Indexes, Here help MySQL using hints like “USE INDEX”.<\/li>\n
6. Think seriously about indexing columns in WHERE, GROUP BY, and ORDER BY queries.<\/li>\n
7. Less Rows Examined is great !!!<\/li>\n
8. Reduce disk reads, Compress TEXT, BLOB<\/li>\n
9. Make incremental Schema changes sometimes small changes cause severe Performance improvements \/ degradation.<\/li>\n
10. Lookout for duplicate records they seriously cause Performance Bottlenecks.<\/li>\n
11. Archive the old records, They make Sort \/ Search especially very expensive.<\/li>\n
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 \ud83d\ude42<\/li>\n
13. The fat multi-core processors hyper-threading enabled works great for high performance MySQL operations<\/li>\n
14. Watch out for excessive pagination queries.<\/li>\n
15. Go conservative about MySQL Configs (sometimes less is more).<\/li>\n
16. Be cautious with MySQL configuration parameters\u00a0innodb_thread_concurrency and\u00a0thread_concurrency variables, Don’t use them if you don’t clearly understand how they work !!<\/li>\n
17. High\u00a0max_connections excessively consumes the RAM.<\/li>\n
18. Use query cache only if you have repetitive queries, Using query cache on data that changes often will give you a performance hit.<\/li>\n
19. Remember to warm your MySQL Database in every restart, This get schema, SQL back to memory.<\/li>\n
20. Fetch only the data you need in SELECT queries<\/li>\n
21. Consider persistent connection against multiple connections<\/li>\n
22. Sort_buffer_size \u00a0eat memory very fast because it is actually consumed per connection so please be very cautious while increasing it.<\/li>\n
23. Use large redo logs<\/li>\n
24. Measure cost of query with Response Time, Use MySQL slow query log to diagnose query performance.<\/li>\n
25. I use SHOW PROCESSLIST while load increases, This works good for me during diagnostics \/ forensics of slow queries.<\/li>\n
26. Use Performance Schema wisely, This is an great MySQL performance orchestration platform.<\/li>\n
27. Avoid using Triggers as much as possible.<\/li>\n
28. Use ARCHIVE Storage engine for audit purposes.<\/li>\n
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.<\/li>\n
30. Split reads across several instances \/ disk, This helps distributing load efficiently<\/li>\n<\/ol>\n<\/div>\n<\/div>\n
    <\/div>\n","protected":false},"excerpt":{"rendered":"

    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 […]<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,3,15],"tags":[5,6,16],"yoast_head":"\n