Running Multiple My SQL Instances On a Single

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

About us § Ben (Kansas State University, 2001) § Sys. Admin / Oracle DBA § Started using My. SQL in 2006 § § Mark (University of Kansas, 2008) Hired to work on Oracle Started on SQL Server 90% My. SQL

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 My. SQL instance: 2. 5 TB

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.

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

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

Reasons for consolidation § F 5 db traffic routing § Garmin’s My. SQL environment no longer takes MONTHS to master § Standard repeatable build § Automated configurations with Puppet § Able to build a new db instance in 15 minutes

The old ways

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

What we did [filipi@olaxpd-myz 03 mysql]$ ls -lh total 8. 0 K drwxr-xr-x 3 mysql 4. 0 K Jun drwxr-xr-x 3 root 8 2009 5. 0. 72 sp 1 4. 0 K Jul 31 2009 5. 1. 31 sp 1 lrwxrwxrwx 1 mysql 21 Jun lrwxrwxrwx 1 mysql 26 Nov 25 2008 mysql 5. 0 -> 5. 0. 72 sp 1/mysql-5. 0. 72 sp 1/ lrwxrwxrwx 1 root 26 Jul 30 2009 mysql 5. 1 -> 5. 1. 31 sp 1/mysql-5. 1. 31 sp 1/ root 8 2009 mysql -> /opt/mysql/5. 0. 72 sp 1/

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 F 5 VIP

What we did [filipi@olaxpd-myz 03 sqldata]$ ls -lh total 56 K drwxr-xr-x 4 mysql 05 4. 0 K Oct 8 15: 24 eepl_s drwxr-xr-x 4 mysql 03 4. 0 K Jun 9 2009 extensis_m drwxr-xr-x 4 mysql 07 4. 0 K Sep 29 2009 forums_ger_s drwxr-xr-x 4 mysql 01 4. 0 K Jul 2009 forums_m 6 drwxr-xr-x 4 mysql 04 4. 0 K Aug 31 2009 gcs_ps drwxr-xr-x 4 mysql 06 4. 0 K Nov 3 08: 45 gif_ps drwxr-xr-x 4 mysql 00 4. 0 K Jun 9 2009 jahia_m drwxr-xr-x 4 mysql 02 4. 0 K Jul 1 2009 jahiauk_m drwxr-xr-x 4 mysql 03 4. 0 K Nov 16 13: 58 phpmini_m

What we did

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

my. cnf [mysqld_safe] ledir = /opt/mysql 5. 0/bin [mysqld] user = mysql 01 bind-address = 192. 168. 15. 171 port = 3307 socket = /sqldata/mg_m/db/mysql. sock basedir = /opt/mysql 5. 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 = 8 G

My. SQL Proxy § Standard § With Proxy

My. SQL Proxy § Ours

mysql-monitor-agent. ini [mysql-proxy] keepalive = true plugins=proxy, 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-mysqlmonitor. 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 = 5479 b 948 -b 97 d-40 c 9 -a 84 f-7 f 26 a 1 dd 5 d 78 log-file = mysql-monitor-agent. log pid-file=/sqldata/mg_t/opt/mysql/enterprise/agent/mysql-monitor-agent. pid

My. SQL Proxy § SSH tunneling

Advantages § Run multiple versions of My. SQL, 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

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

“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!

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

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

Maintenance Planning

Maintenance Planning

Maintenance Planning

Maintenance Planning

Maintenance Planning

Maintenance Planning

Maintenance Planning

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