Deploying MySQL in Production

MySQL是著名的,因为您可以在不到5分钟的时间内安装并开始开发。但通常情况下,您希望在生产环境和规模上运行MySQL。这需要一些计划和知识。那么,为什么不学习有关安装、配置、部署和备份的最佳实践呢?
这场网络研讨会是一场即兴的对话,会让你在短时间内从零变为英雄。它包括讨论安装、配置、备份、监控等方面的最佳实践。

展开查看详情

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