The MySQL Server Ecosystem in 2016

加入Percona的首席布道者ColinCharles,他展示了“2016年的MySQL生态系统”。
MySQL是一个独特的成人(现在21岁)在很多方面。它支持插件。它支持存储引擎。它也属于Oracle,因此产生了流行的OpenSource数据库的两个分支:Percona服务器和Mariadb服务器。它也曾经生下一个叉子:毛毛雨。最近,一个由网络规模用户组成的联盟(想想十大网站中的一大块)产生了WebscaleSQL。
你是一个忙碌的DBA,必须保持这两者的混合。或者你是首席信息官,打算选择一个分支机构。你怎么挑选?支持多个数据库?了解更多信息。此外,我们还深入探讨了mysql/percona server/mariadb/webscalesql之间存在哪些特性差异,以及分发版如何以不同的方式打包各种数据库。在一个小时内,你会被告知过去,现在,并希望有足够的知识,知道在未来选择什么。
注意,webscalesql周围的各种树也会被覆盖,比如facebook树、阿里巴巴树以及twitter树。

展开查看详情

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