The MySQL Server Ecosystem in 2016

1 .The MySQL Server Ecosystem in 2016 Colin Charles, Chief Evangelist, Percona Inc. colin.charles@percona.com / byte@bytebot.net http://bytebot.net/blog/ | @bytebot on Twitter Percona Webminar 3 November 2016 

2 .whoami • Chief Evangelist (in the CTO office), Percona Inc • Focusing on the MySQL ecosystem (MySQL, Percona Server, MariaDB Server), as well as the MongoDB ecosystem (Percona Server for MongoDB) + 100% open source tools from Percona like Percona Monitoring & Management, Percona xtrabackup, Percona Toolkit, etc. • Founding team of MariaDB Server (2009-2016), previously at Monty Program Ab, merged with SkySQL Ab, now MariaDB Corporation • Formerly MySQL AB (exit: Sun Microsystems) • Past lives include Fedora Project (FESCO), OpenOffice.org • MySQL Community Contributor of the Year Award winner 2014 

3 .A Mature Ecosystem • MySQL: 21 years old — May 1995 • Percona Server: 8 years old — November 2008 • MariaDB Server: 6 years old — February 2010 • Drizzle: 2008 - 2012 (R.I.P.) • WebScaleSQL: March 2014 

4 .History • 1979: UNIREG • 1986: UNIREG on UNIX • mSQL - freely available but not opensource • 1995: MySQL 1.0 + MySQL AB founded • 1996: 3.19 • 2000: GPL MySQL Server 

5 .History 2 • Shortly thereafter, the dual-license arrives — libmysql is GPL and not LGPL like before • May 2000: 3.23.15 - (statement based) replication arrives • 2001: 3.23 GA • 2001: InnoDB storage engine (InnoBase Oy) • 2001: first round VC $$$, Mårten Mickos as CEO 

6 .History 3 • 2002: MySQL sued Progress NuSphere over the Gemini storage engine • 2003: VC Series B • March 2003: 4.0 GA + 4.1 & 5.0 as alpha • 2003: SAP partnership (hello MaxDB) • 2003: Acquires Alzato (NDBCLUSTER) 

7 .History 4 • October 2004: 4.1 GA (with NDBCLUSTER) • 2005: 5.0 tree is the focus, but it has issues • October 2005: Oracle acquires Innobase Oy (“InnoDB Friday”) • October 2005: 5.0 becomes GA* • Late 2005: Maria project starts (make a crash- safe+transactional MyISAM) 

8 .History 5 • February 2006: VC Series C • 2006: Netfrastructure acquired (Falcon engine) • Pluggable storage architecture - only database that support(ed) it • A “fork” of MySQL Cluster in 2006 • 2006: PBXT (log-based design + performance) … 2011 

9 .What makes engines different? • Storage: how the data is • Transactions: protects the caches indexes only stored on disk integrity of your data (relying on OS disk cache • Or in NDB (memory (Atomic-Consistent- for data) +disk), CassandraSE Isolated-Durable - ACID) • Full-text search capability: (access a Cassandra • Locking level: MyISAM MyISAM has this, InnoDB Cluster), SphinxSE (table locks), InnoDB (row 5.6 got this (access the Sphinx locks), old BDB (page • GIS: MyISAM & Aria work daemon) locks) (R-tree indexes exist), • Indexes: improves search • Data types: Data types InnoDB 5.7 has this too operations may be converted, • Backups • Memory usage: improves MEMORY doesn’t support • Foreign Keys data access for speed TEXT, etc. • Caching: InnoDB caches data & indexes, MyISAM 

10 .History 6 • November 2007: MySQL 6.0 Alpha, 5.1 current tree being worked on but not ready • 2007: quiet period for IPO • January 2008: Sun Microsystems acquires MySQL AB for USD$1 BILLION • June 2008: Drizzle — fork of MySQL 6.0 — modular, fast, microkernel architecture, UTF8, etc. • November 2008: 5.1 GA 

11 .History 7 • Late 2008: OurDelta binaries • November 2008: Percona Server (patchset ~July) • http://www.bytebot.net/blog/archives/2009/10/14/ flickrs-upgraded-shard 

12 .History 8 • February 2009: Monty leaves Sun • March 2009: Final 6.0 Alpha (and shortly dead thereafter) • April 2009: Oracle acquires Sun Microsystems (January 2010 sale completes, after a long battle with the EU) • shortly thereafter Monty Program Ab is where MariaDB starts being worked on • October 2009: MariaDB 5.1 Beta release 

13 .History 9 • February 2010: MariaDB 5.1 GA release • November 2010: MariaDB 5.2 GA release • December 2010: MySQL 5.5 GA • April 2011: Percona Server 5.5 stable • February 2012: MariaDB 5.3 GA (GIS, replication improvements, optimiser) 

14 .History 10 • April 2012: MariaDB 5.5 GA • November 2012: Announcement of MariaDB Foundation • February 2013: MySQL 5.6 GA • April 2013: SkySQL Ab acquires Monty Program Ab • October 2013: Percona Server 5.6 GA 

15 .History 11 • March 2014: MariaDB 10 GA • March 2014: WebScaleSQL — but there’s no GA/ shipping release • October 2015: MariaDB 10.1 GA, MySQL 5.7 GA • February 2016: MySQL 5.7.11 gets InnoDB at-rest data encryption post-GA; Percona Server 5.7 GA • April 2016: MySQL 5.7.12 gets X Protocol, MySQL Shell, X DevAPI post-GA 

16 .Open source community • MariaDB: takes external contributors/committers • MySQL: 5.7 takes Generated Columns (virtual columns in MariaDB 5.2) from Andrey Zhakov • contributions welcome, commits not • see: MySQL Community Contributor Award Program • WebScaleSQL: Alibaba, Facebook, Google, LinkedIn, and Twitter (w/ Percona + MariaDB also agreeing to work on it) • Percona: bug reports/feature requests welcome, commits not (yet) 

17 .Google Summer of Code • 2013: 3 contributors (all shipping code, one committer) • 2014: 4 contributors (all shipping code, a MariaDB Foundation member hired a new developer) • 2015: accepted with 8 contributors, finally 3 passed • 2016: accepted with 10 students (ended with 5 passing) 

18 .Is MySQL dying? • “The reports of my death have been greatly exaggerated” — Mark Twain • MySQL ecosystem development is at its most vibrant now than it has ever been • Oracle has been a great steward of pushing MySQL development forward 

19 .MySQL adoption • MySQL Server (5.5) still leads the way in Debian/Ubuntu (the only one you can track via popcon) • Followed by MariaDB Server and then Percona Server • Today you can also see stats on the Docker hub, Juju Charms, etc. 

20 .OpenStack user survey 

21 .

22 .MySQL 5.7 • Multi-source • Change master • Online buffer pool replication without stopping resize • Dynamic replication SQL thread • Username size filters • Online GTID increase • Lossless semisync implementation • LOCK/UNLOCK • SHOW EXPLAIN for • GTID no longer accounts connection_id requires log-slave- • JSON + MySQL Shell • GIS functionality updates to be + X DevAPI enabled • Encryption at rest • Statement timeouts • Virtual columns (generated columns) 

23 .MariaDB 10.1 • START TRANSACTION • InnoDB defragmentation WITH CONSISTENT • ANALYZE <statement> SNAPSHOT • Threadpool • Integrated Galera Cluster • cracklib_password_check • Table/tablespace • SQL error logging plugin encryption • Extended REGEXP (PCRE) • Optimistic parallel replication • Roles 

24 .Percona Server (5.6)/5.7 • Per table/index/user/client/thread • Utility user - system access to performance counters (userstats) admin tasks, limited access to • Lock-free SHOW SLAVE STATUS user schema NOLOCK • Slow query log enhancements • Percona Toolkit UDFs • Improved MEMORY storage • Expanded fast index creation - engine expand_fast_index_creation • Log all client commands to syslog • XtraDB changed page tracking • PROXY protocol support • Kill idle transactions • Backup locks • Column compression • TokuBackup 

25 .InnoDB Parallel Doublewrite Buffer 

26 .WebScaleSQL • Clients can specify millisecond read/write/ connect timeouts • Super read-only to prevent writes by SUPER users • Prefix index query optimisation • Idle system flush rate 

27 .Facebook • Dynamic columns + indexes? DocStore • RocksDB, including myrocks_hotbackup • Asynchronous mysql client support • max_running_queries / max_waiting_queries • Relay log writes don’t block SHOW SLAVE STATUS 

28 .Twitter • Last updated mid-June 2015 :( • They made Apache Cotton for Mesos/ MySQL 

29 .AliSQL • Further optimised threadpool • For Single’s Day, they have a “hot SKU” fix for inventory deductions of a single SKU • SQL firewall • Temporary table space limitations • Log SHUTDOWN information • Persistent InnoDB AUTO_INCREMENT (MDEV-6076) • Column level compression