1 / 32

Running Multiple MySQL Instances On a Single Server

Running Multiple MySQL Instances On a Single Server. Ben Black – ben.black@garmin.com Mark Filipi – mark.filipi@garmin.com. 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

fallon
Download Presentation

Running Multiple MySQL Instances On a Single Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related