Deploying MySQL in Production

1 . Deploying MySQL in Production Daniel Kowalewski — Senior Technical Operations Engineer, Percona daniel.kowalewski@percona.com 1 © 2017 Percona 

2 .Deploying MySQL in Production • Installation • Configuration (OS and MySQL) • Backups • Monitoring • Before you go live... 2 © 2017 Percona 

3 .Installation 3 © 2017 Percona 

4 .MySQL Installation • This is not your job! • Don't reinvent the wheel • Use a package manager • Oracle, Percona, MariaDB all have repos • Stay current 4 © 2017 Percona 

5 .Config management • Ansible, Puppet, Chef, SaltStack, etc. • Use what the rest of your company uses if possible • Infrastructure as code (revision control!) • Consistency 5 © 2017 Percona 

6 .Use config management for: • Package installation • Volume and directory creation • Permissions • Firewalls • Initial MySQL accounts • delete default accounts 6 © 2017 Percona 

7 .OS Configuration 7 © 2017 Percona 

8 .Storage • Can you afford SSDs? • RAID10 👍, RAID5/6👎, RAID0👎👎 (*) • LVM for flexibility • Filesystem – XFS or ext4 • XFS: inode64,nobarrier,noatime,logbufs=8 • ext4: noatime,nodiratime,barrier=0 • IO Scheduler – noop or deadline 8 © 2017 Percona 

9 .CPU • Set CPU governor to "performance" 9 © 2017 Percona 

10 .Life Goal: Don't run out of memory • Set vm.swappiness = 1 • Don't disable swap, it's your reserve parachute • Use jemalloc • Disable Transparent Huge Pages 10 © 2017 Percona 

11 .MySQL Configuration 11 © 2017 Percona 

12 .MySQL Configuration • Don't use the default files • Percona Config Wizard isn't a bad start 12 © 2017 Percona 

13 .Basic settings • innodb_buffer_pool_size (still defaults to 128 MiB!) • max_connections (still defaults to 151!) • innodb_log_file_size • query_cache_size and query_cache_type • innodb_thread_concurrency • innodb_buffer_pool_instances 13 © 2017 Percona 

14 .Also consider • innodb_flush_log_at_transaction_commit • sync_binlog 14 © 2017 Percona 

15 .Set now, or regret later • character_set (utf8 or utf8mb4) • innodb_file_per_table • log-bin • expire_logs_days • innodb_numa_interleave 15 © 2017 Percona 

16 .Backups 16 © 2017 Percona 

17 .Things that are not backups • RAID • SAN • mirrored SAN • MySQL replicas • even delayed replicas • Untested backups • Backups stored on the DB host 17 © 2017 Percona 

18 .Type Size Time to Back Time to Partial Tools up Restore Restores Physical larger faster faster hard xtrabackup, MySQL Enterprise Backup Logical smaller slower slower easy mysqldump, mydumper, mysqlpump 18 © 2017 Percona 

19 .Incremental/differential backups • Store changes since the last backup • Only possible with physical backups • More complicated restores • Don't go too long without a full backup! 19 © 2017 Percona 

20 .Binary log backups • Essential for point-in-time recovery • Stream with mysqlbinlog --read-from-remote-server • Play back by piping to mysql • You did turn on log_bin, right? 20 © 2017 Percona 

21 .Test your backups • Automate restores • Watch for errors (especially with logical backups) • Replicate from production • pt-table-checksum • Compare schemas 21 © 2017 Percona 

22 .Monitoring 22 © 2017 Percona 

23 .Alerting • Nagios, Icinga, Sensu, VividCortex, New Relic, etc • Use what you already have • Alert on: • Things that matter to the application • Things that are hard to recover from • Don't over-alert 23 © 2017 Percona 

24 .Metrics that affect the application: • Service status (connection check) • as realistic as possible • Update / select from table • Average execution time • threads_running • Maybe replication delay 24 © 2017 Percona 

25 .Disasters waiting to happen: • Disk space • History list size • Auto-increment keys • threads_connected (percentage of max_connections) • Checksum differences • Swap usage 25 © 2017 Percona 

26 .Maybe not so important • Long transactions (check history list instead) • threads_connected • CPU usage • Deadlocks 26 © 2017 Percona 

27 .Trending • Don't over-alert, but trend everything you can! • PMM, VividCortex, Sensu, etc. 27 © 2017 Percona 

28 .Query analyzer • Removes guesswork • "This query is responsible for OOM" • "This is our worst query" • "3% of queries would benefit from this index" • PMM, VividCortex, NewRelic 28 © 2017 Percona 

29 .Before you go live... LEEEEEEROOOOOY JENKINS! 29 © 2017 Percona