The Low Hanging Fruits of MySQL
summary
Tune InnoDB buffer pool to 75–80% memory, split it with buffer_pool_instances to avoid mutex contention, and use O_DIRECT with large innodb_log_file_size (even up to buffer pool size) plus increased innodb_log_buffer_size for big transactions. Drop query cache, set log_output=FILE, raise table_open_cache, reduce swappiness to 1 and use noatime/noop. Protect MySQL from OOM with oom_score_adj -800. Prefer InnoDB with primary keys, short unsigned integers, properly ordered composite and covering indexes. Use pt-online-schema-change and pt-query-digest, run PMM monitoring, test backups and restores, and enable RDS/Aurora parameter groups, multi-AZ, encryption and backtrack.
AI-written summary · about
chapters (20)
- 0:00Introduction
- 1:25Overview
- 2:05Common Knowledge
- 4:05Agenda
- 5:12MySQL Configuration
- 7:12RBflush
- 9:58IODB
- 12:17Table Cache
- 15:47Out of Memory
- 18:00How to Avoid Out of Memory
- 20:05Database Design
- 23:56Secondary Keys
- 27:36Querying
- 30:38Replication
- 33:56Backup
- 35:50Business Objectives
- 36:57Backups
- 38:32Percona Tools
- 41:47PMM
- 45:33Conclusions
related talks
25mMySQL Performance for DevOps
53mMySQL DevOps
33m7 or so deadly MySQL performance sins
36mMySQL & InnoDB Fundamentals and Configuration
53mIntroduction to MySQL Performance Tuning - workshop 1hr
49m18 Ways to Fix MySQL Bottlenecks
34mMySQL and InnoDB Performance for the Rest of Us
37mMySQL Database Scalability