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 !!
- Go generous while buying physical memory, InnoDB works great when files are accessed from memory rather than from disk.
- Lean MySQL Schema and SQL works great. Trust me, The fat is not Optimal everywhere !!
- Carefully choose your Datatype, The smaller ones are better.
- You need Indexes to search records faster but Index overuse / abuse will have negative impact to performance, Remove unused indexes.
- Occasionally MySQL go wrong in choosing Optimal Indexes, Here help MySQL using hints like “USE INDEX”.
- Think seriously about indexing columns in WHERE, GROUP BY, and ORDER BY queries.
- Less Rows Examined is great !!!
- Reduce disk reads, Compress TEXT, BLOB
- Make incremental Schema changes sometimes small changes cause severe Performance improvements / degradation.
- Lookout for duplicate records they seriously cause Performance Bottlenecks.
- Archive the old records, They make Sort / Search especially very expensive.
- 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 🙂
- The fat multi-core processors hyper-threading enabled works great for high performance MySQL operations
- Watch out for excessive pagination queries.
- Go conservative about MySQL Configs (sometimes less is more).
- 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 !!
- High max_connections excessively consumes the RAM.
- Use query cache only if you have repetitive queries, Using query cache on data that changes often will give you a performance hit.
- Remember to warm your MySQL Database in every restart, This get schema, SQL back to memory.
- Fetch only the data you need in SELECT queries
- Consider persistent connection against multiple connections
- Sort_buffer_size eat memory very fast because it is actually consumed per connection so please be very cautious while increasing it.
- Use large redo logs
- Measure cost of query with Response Time, Use MySQL slow query log to diagnose query performance.
- I use SHOW PROCESSLIST while load increases, This works good for me during diagnostics / forensics of slow queries.
- Use Performance Schema wisely, This is an great MySQL performance orchestration platform.
- Avoid using Triggers as much as possible.
- Use ARCHIVE Storage engine for audit purposes.
- 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.
- Split reads across several instances / disk, This helps distributing load efficiently
