320 likes | 429 Vues
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
E N D
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 • Started on SQL Server • 90% MySQL
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
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 • 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
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
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/
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
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
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)
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
MySQL Proxy • Standard • With Proxy
MySQL Proxy • Ours
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
MySQL Proxy • SSH tunneling
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
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
Ben Black – ben.black@garmin.com • Mark Filipi – mark.filipi@garmin.com