Running Multiple MySQL Instances On a Single Server

1. Running Multiple MySQL Instances On a Single Server • Ben Black – ben.black@garmin.com • Mark Filipi – mark.filipi@garmin.com

2. About us • Ben (Kansas State University, 2001) • SysAdmin / Oracle DBA • Started using MySQL in 2006 • Mark (University of Kansas, 2008) • Hired to work on Oracle • Started on SQL Server • 90% MySQL

3. About us • 24/7 databases for websites, connected services, and manufacturing for Garmin. • Examples of data we process… • ~5.5 million data points from connected units per day • ~100,000 web orders per day • Massive amounts of binary traffic data every 3 minutes • Largest MySQL instance: 2.5 TB

4. In the beginning… • 1 instance per server • Some masters had slaves • Some servers were old… really old. • Only some DBs were documented, protected, backed up, etc.

5. Reasons for consolidation • Underutilized hardware • Long lead times to provision new hardware • Licensing / hw / OS costs • Smaller datacenter footprint • Reduced overhead

6. Reasons for consolidation • Standardize hardware and operating system • Increased redundancy • Separate DB from Application • load balanced active-passive pool for planned maintenance and failover • Enterprise Monitor/query analyzer

7. Reasons for consolidation • F5 db traffic routing • Garmin’s MySQL environment no longer takes MONTHS to master • Standard repeatable build • Automated configurations with Puppet • Able to build a new db instance in 15 minutes

8. The old ways

9. What we did • Added multiple alias network interfaces per server – one per instance plus extras for expansion • Added multiple MySQL unix accounts per server • One init script per instance • Placed shared binaries in /opt/mysql/ belonging to mysql unix group

10. What we did [filipi@olaxpd-myz03 mysql]$ ls -lh total 8.0K drwxr-xr-x 3 mysql mysql 4.0K Jun 8 2009 5.0.72sp1 drwxr-xr-x 3 root root 4.0K Jul 31 2009 5.1.31sp1 lrwxrwxrwx 1 mysql mysql 21 Jun 8 2009 mysql -> /opt/mysql/5.0.72sp1/ lrwxrwxrwx 1 mysql mysql 26 Nov 25 2008 mysql5.0 -> 5.0.72sp1/mysql-5.0.72sp1/ lrwxrwxrwx 1 root root 26 Jul 30 2009 mysql5.1 -> 5.1.31sp1/mysql-5.1.31sp1/

11. What we did • LVM on SAN LUNs mounted at /sqldata and /sqllogs • Directory for each instance under /sqldata and /sqllogs • One my.cnf for each instance under /sqldata/instance_name/ • Route traffic through F5 VIP

12. What we did [filipi@olaxpd-myz03 sqldata]$ ls -lh total 56K drwxr-xr-x 4 mysql05 mysql05 4.0K Oct 8 15:24 eepl_s drwxr-xr-x 4 mysql03 mysql03 4.0K Jun 9 2009 extensis_m drwxr-xr-x 4 mysql07 mysql07 4.0K Sep 29 2009 forums_ger_s drwxr-xr-x 4 mysql01 mysql01 4.0K Jul 6 2009 forums_m drwxr-xr-x 4 mysql04 mysql04 4.0K Aug 31 2009 gcs_ps drwxr-xr-x 4 mysql06 mysql06 4.0K Nov 3 08:45 gif_ps drwxr-xr-x 4 mysql00 mysql00 4.0K Jun 9 2009 jahia_m drwxr-xr-x 4 mysql02 mysql02 4.0K Jul 1 2009 jahiauk_m drwxr-xr-x 4 mysql03 mysql03 4.0K Nov 16 13:58 phpmini_m

13. What we did

14. What we didn’t do • Why not mysqld_multi? • Keep instances portable and self-contained • MySQL processes are independent of each other • Server virtualization (VMWare)

15. my.cnf [mysqld_safe] ledir = /opt/mysql/mysql5.0/bin [mysqld] user = mysql01 bind-address = 192.168.15.171 port = 3307 socket = /sqldata/mg_m/db/mysql.sock basedir = /opt/mysql/mysql5.0 datadir = /sqldata/mg_m/db/ innodb_data_home_dir = /sqldata/mg_m/db/ innodb_log_group_home_dir= /sqllogs/mg_m/ innodb_buffer_pool_size = 8G

16. MySQL Proxy • Standard • With Proxy

17. MySQL Proxy • Ours

18. mysql-monitor-agent.ini [mysql-proxy] keepalive = true plugins=proxy,agent agent-mgmt-hostname = http://agent:xxxx@localhost:18080/heartbeat mysqld-instance-dir= etc/instances agent-item-files = share/mysql-proxy/items/quan.lua,share/mysql-proxy/items/items-mysql-monitor.xml,share/mysql-proxy/items/agent-allocation-stats.lua proxy-address = 192.168.15.171:3306 proxy-backend-addresses = 192.168.15.171:3307 proxy-lua-script = share/mysql-proxy/quan.lua max-open-files=15000 agent-uuid = 5479b948-b97d-40c9-a84f-7f26a1dd5d78 log-file = mysql-monitor-agent.log pid-file=/sqldata/mg_t/opt/mysql/enterprise/agent/mysql-monitor-agent.pid

19. MySQL Proxy • SSH tunneling

20. Advantages • Run multiple versions of MySQL, and rapidly switch between them for testing • Able to upgrade a single instance without affecting the other instances on the same server • All production instances are paired with a slave for backups and redundancy • 43 Prod instances on 14 hosts • 65 non-prod on 28 hosts

21. Problems/Issues • Proxy port issues – 3306/3307/4040 • Proxy performance under load • Slave filling disk • Server locking up after running out of RAM • LVM Snapshots of separate LUNs

22. “Fixes” • Only route through proxy if you have a good reason • Set ulimit in start script • Limit RAM if necessary • Be very careful when setting up load balancing and failover • QUAN using new JDBC connector, not proxy!

23. Challenges • Each instance can adversely affect others especially disk I/O • Binary logs can chew up lots of disk

24. Maintenance Planning • Embodiment of all advantages of our consolidation • With a capable slave and sufficient planning, downtime is minimized

25. Maintenance Planning

26. Maintenance Planning

27. Maintenance Planning

28. Maintenance Planning

29. Maintenance Planning

30. Maintenance Planning

31. Maintenance Planning

32. Ben Black – ben.black@garmin.com • Mark Filipi – mark.filipi@garmin.com