Optimization Techniques in MySQL
summary
MySQL 8 removes the query cache and relies on performance_schema plus Percona PMM for query analytics and top‑time digests, with pt‑query‑digest as an alternative. Key tuning specifics: set innodb_buffer_pool_size to roughly 70–80% of RAM on a dedicated DB, use multiple buffer_pool_instances when pool >1GB, size innodb_log_file_size to hold about an hour of redo (tradeoff: longer recovery), raise max_connections above the 151 default only after memory budgeting, increase tmp_table_size to avoid on‑disk temp tables, and apply OS tweaks (jemalloc, swappiness=1, disable THP). Test one change at a time.
AI-written summary · about
chapters (25)
- 0:00Start
- 1:00Agenda
- 2:41Mysql Architecture
- 3:28How Mysql Optimizes and Executes the Queries
- 4:48Application Layer
- 5:17Query Cache
- 8:53Storage Engine
- 9:31Measuring the Performance
- 10:04How Does Measuring Help Us
- 10:50Best Tools Available To Monitor Is Mysql
- 11:22Monitoring Metrics
- 13:16Event Statement Summary by Digest
- 16:05Query Analyzer
- 19:41How Tables Are Joined
- 21:10Clustered Index
- 24:00Query Optimization
- 24:20Best Practices
- 30:50Buffer Pool
- 35:25Buffer Pool Miss Ratio
- 39:44Innodb Log File Size
- 44:14Max Connection
- 46:43Temp Table Size
- 48:13Open Files Limit
- 50:52Os Optimization Tips
- 54:24Conclusion
related talks
53mIntroduction to MySQL Performance Tuning - workshop 1hr
53mMySQL DevOps
49m18 Ways to Fix MySQL Bottlenecks
25mMySQL Performance for DevOps
33m7 or so deadly MySQL performance sins
41mMySQL Query Optimization Best Practices and Indexing
28mBecoming a DBA for 5 minutes a day, the most important things to know for sysadmins and developers
54mPerformance Analysis and Optimization of MySQL