Notice: MySQL is a registered trademark of Sun Microsystems, Inc. MariaDB 10.1 New features in MariaDB 10.1 Webconf 2015 Riga Michael “Monty” Widenius Entrepreneur, MariaDB hacker [email protected] http://mariadb.org/ 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Time to Celebrate ● 20 Years of MySQL and PHP ● MySQL was internally released 1995 and publicly 1996 ● Some MySQL code is 34 years old ● MariaDB is 6 years 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. The origin of Maria (DB) Introducing Maria 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Why MariaDB was created “Save the People, Save the Product” ● To keep the MySQL talent together ● To ensure that a free version of MySQL always exists ● To get one community developed and maintained branch ● Work with other MySQL forks/branches to share knowhow and code After Oracle announced it wanting to buy Sun & MySQL this got to be even more important. 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. MariaDB Foundation Overview The Foundation is the owner of the main MariaDB server project Custodian of the code, Guardian of the community Foundation can never to be controlled by a single entity or person Note that the Foundation is not about the MariaDB trademark or to decide upon the MariaDB roadmap! The Foundation was created to ensure that anyone can become a contributor to the MariaDB project on equal terms! 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. MariaDB Foundation Goals That MariaDB be actively developed in the community and to: ● Increase adoption of MariaDB ● Ensure sustainable high-quality efforts to build, test and distribute MariaDB ● Ensure that community patches are reviewed and adopted ● Guarantee a community voice and define development rules ● Keep MariaDB compatible with MySQL ● Maintain mariadb.org. The MariaDB foundation now employes: ● Management team: Michael Widenius as CTO ● Otto Kekäläinen as CEO ● 4 full time MariaDB developers (including the CTO) ● 1 documentation writer 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. MariaDB Foundation members Many of the MariaDB 10.0 features has been developed together with the MariaDB foundation! The foundation are very grateful to it's 2013-2015 members, who have made MariaDB 10.0 possible: ● Booking.com (2 years) ● SkySQL (now MariaDB Corporation) (3 years!) ● Parallels/Odin (3 years!) ● Automattic ● Zenimax ● Visma Additional sponsors can be found on mariadb.org 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. MariaDB Foundation More members and sponsors are welcome! If you care about the future of the MySQL ecosystem, please contact us and and ask how you can get involved! Michael Widenius, CTO, [email protected] 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Where to get MariaDB ● In Distributions: ● Red Hat Enterprise Linux, Fedora, Debian, Ubuntu, Mageia, Suse Enterprise, openSuse, Gentoo, Slackware, Arch, ALTLinux, TurboLinux, Chacra Project, Kdu, Unbreakable Linux …. ● FreeBSD, OpenBSD ● Mac OS X with MacPorts or Homebrew ● From MariaDB.org ● Sources, binaries in tar.gz or .zip (Windows) ● Windows MSI installer ● MariaDB apt and yum repositories 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. MariaDB server is a branch of MySQL ● User level (data, API, replication, configuration files..) compatible with MySQL ● Drop in replacement ● Easy upgrades (both from MySQL and earlier MariaDB versions); We don't remove features! ● More plugins, more storage engines, more features, faster, better code quality. ● GPL-only server license. ● LGPL C, ODBC and Java connectors. ● More open development ● Source in public repository on launchpad ● Active external contributors ● All development plans public on mariadb.com 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. MariaDB server releases ● MariaDB 5.1 was released in February 2010 ● MariaDB 5.2 was released in November 2010 ● MariaDB 5.3 was released in April 2012 ● MariaDB 5.5 was released in April 2013 ● MariaDB 10.0 was released in March 2014 ● MariaDB 10.1 was released in October 2015 ● Galera (multi-master, mesh network) is now included in MariaDB 10.1 ● Separate binaries available for 5.5 and 10.0 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. The MariaDB releases ● MariaDB 5.1 (based on MySQL 5.1) ● Better build & test system, code cleanups, community patches, new storage engines, table elimination. ● MariaDB 5.2 (based on MariaDB 5.1) ● Community features that did not go into 5.1: ● Virtual columns ● Extended User Statistics ● Segmented MyISAM key cache (faster multi user!) ● MariaDB 5.3 (based on MariaDB 5.2) ● Optimizer features (faster subquerier, joins etc) ● Microsecond, dynamic columns, faster HANDLER etc. ● Better replication (group commit, more options) ● MariaDB 5.5 (based on MariaDB 5.3 and MySQL 5.5) 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. The MariaDB releases ● MariaDB 10.0 ● Most of MySQL 5.6 features ● Multi-source and Parallel replication ● Working GTID (Global transaction ID) ● SQL standard ROLES ● MariaDB 10.1 ● Galera integrated ● Even better parallel replication ● Strong encryption ● Speed improvements in a lot of areas 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Feedback plugin ● All recent MariaDB versions has the feedback plugin ● Enable by adding “plugin-load=feedback.so” and "enable- feedback” to the [mysqld] section in my.cnf. ● Feedback plugin will automatically send a report (basicly SHOW STATUS) at startup and once a week ● This information is used to decide what features should be developed/expanded upon ● For more information see https://mariadb.com/kb/en/feedback-plugin/ ● For statistics see http://mariadb.org/feedback_plugin/ 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Feedback plugin Some statistics Major versions of MariaDB in use with feedback enabled. 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Parallel slave benchmark in 10.0 10x speedup with 12 threads From: http://kristiannielsen.livejournal.com/18435.html Crash safe: --log-slave-updates --sync-binlog=1 --innodb-flush-log-at-trx- commit=1 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Parallel slave, no wait Slave performance relative to master 0.0% 100.0% 200.0% 300.0% 400.0% 500.0% 600.0% 700.0% 800.0% 900.0% 1000.0% slave performance relative to master slave @ 1 slave @ 2 slave @ 4 slave @ 8 slave @ 16 slave @ 32 slave @ 64 clients Benchmark: sysbench OLTP rw, 64 tables, data fits in memory Master config: trx_commit=1, sync_binlog=1, SBR Slave config: trx_commit=1 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Optimizer enhancements ● ORDER BY … LIMIT optimizer ● UNION ALL optimization (no temporary tables created) ● Analyze ● Like EXPLAIN but exact information ● ANALYZE and EXPLAIN supports FORMAT=JSON Engine condition pushdown always on ● Required by many storage engines ● Affected rows in the slow log ● Allowing storage engine to shortcut group by queries (Used by scaledb and sequence) 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Performance and Scalability ● Removing locks — using lock-free data structures ● In meta-data locking ● In Table definition cache ● In XA (XID hash) ● Numerous scalability improvements ● 60% speedup in sysbench on Power8 ● No .frm files for temporary tables ● Speeds up creation of temporary memory tables ● Removing calls to current_thd() and malloc() ● Disable performance schema by default (gives 1% → 20% speedup) 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Sysbench OLTP RO on Power8 http://svoj-db.blogspot.ru/2014/12/mariadb-on- power8-2014-wrap-up.html 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Sysbench read only n Read only benchmark run on commodity hardware 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Replication ● Galera (multi-master) is now part of 10.1 ● WSREP_MEMBERSHIP and WSREP_STATUS I_S. ● Optimistic parallel replication ● Makes slave in some cases faster than the master ● Row based replication (RBR) triggers on slave ● Enhanced semisync replication ● Wait for at least one slave to acknowledge transaction before committing. ● Binary log dump thread enhancements (Google) ● Makes multiple slave setup's faster ● domain_id based replication filters ● RESET MASTER TO # 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. GIS Better OpenGIS standard compliance: ● INFORMATION_SCHEMA (IS) tables ● GEOMETRY_COLUMN SPATIAL_REF_SYS ● Support for Spatial Reference systems ● REF_SYSTEM_ID per GEOMETRY column ● Missing OGC standard spatial functions added: ● ST_Boundary ● ST_ConvexHull ● ST_IsRing ● ST_PointOnSurface ● ST_Relate 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Consistent support of IF [NOT] EXIST and OR REPLACE In 10.0 only TABLES was supported. In 10.1 we also support: ● CREATE DATABASE (MDEV-7280) ● CREATE FUNCTION UDF (MDEV-7283) ● CREATE ROLE (MDEV-7288) ● CREATE SERVER (MDEV-7285) ● CREATE USER (MDEV-7288) ● CREATE VIEW (MDEV-7283) ● DROP ROLE (MDEV-7288) ● DROP USER (MDEV-7288) ● CREATE EVENT and DROP EVENT (MDEV-7281) ● CREATE INDEX and DROP INDEX (MDEV-7284) ● CREATE TRIGGER and DROP TRIGGER (MDEV-7286) 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Information schema ● INFORMATION_SCHEMA.SYSTEM_VARIABLES ● Shows help, limits and where a variable was set ● IS plugins can support SHOW and FLUSH statements ● SHOW QUERY_RESPONSE_TIME ● FLUSH QUERY_RESPONSE_TIME ● SHOW LOCALES ● New columns in ● INFORMATION_SCHEMA.APPLICABLE_ROLES ● INFORMATION_SCHEMA.VIEWS 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Miscellaneous ● SET STATEMENT ● Set variables for duration of query ● Compound statements ● IF (…) THEN CREATE TABLE … ; END IF ● Sequence engine enabled by default ● Microseconds in GET_LOCK() ● Assisted discovery in OQGraph ● SQL standards-compliant behavior when dealing with Primary Keys with Nullable Columns ● CREATE TABLE (a int primary key) and CREATE TABLE (a int, primary key(a)) are now identical. 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Admin/Maintainability ● SET DEFAULT ROLE ● Statement timeouts ● SET STATEMENT max_statement_time= 100 SELECT …. ● PERFORMANCE_SCHEMA without .frm files ● Enabling slow log per session ● enforce_storage_engine and default_tmp_storage_engine variables ● Better help for SET/ENUM command-line options ● --getopt-prefix-matching ● For testing configuration files or MySQL 5.7 compatiblity ● 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Security ● Encryption ● Encryption of InnoDB/XtraDB tables, table spaces and logs, Aria temporary tables and the binary log files ● Scrubbing (Get back lost space) ● Password validation ● Security-enhanced binaries 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Compilation and Packaging ● Link with dynamic system libjemalloc.so ● GUI-friendly cmake options to enable/disable plugins ● Special compilation options for security-enhanced binaries ● Support for pkg-config, a mariadb.pc file added 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. XtraDB/InnoDB ● Encryption ● Tablespaces and binary logs ● Per-table or all tables ● Background key rotation ● Key identifiers specified per table ● Scrubbing (Ensure that data is really deleted) ● Tablespaces and logs ● Defragmentation trough OPTIMIZE TABLE ● --innodb-defragment=1 ● Support for 4K and 64K page size ● Multi-threaded flush 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. XtraDB/InnoDB ● FusionIO related ● CREATE TABLE ... ATOMIC_WRITES=... ● Real time compression (about 2x space saving): ● CREATE TABLE ... PAGE_COMPRESSION=... ● – zlib, lz4, lzo, lzma, snappy ● TRIM ● Used to compress tables 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. XtraDB/InnoDB ● I_S.INNODB_SEMAPHORE_WAITS, I_S.INNODB_MUTEXES ● Monitor progress of online ALTER TABLE ● Enforce that tables has a PRIMARY KEY ● Prefix index queries optimization ● Lazy flushing 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Plugin API ● Two new plugin types ● Password verification ● simple_password_check ● cracklib_password_check ● Encryption ● file_key_management (For table encryption) ● example_key_management ● password_key_management 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Services for Plugins ● Five new services ● md5 ● sha1 ● encryption and key management ● thd_specifics ● wsrep (write set replication) 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Connect storage engine MariaDB 10.0 includes the Connect storage engine by Olivier Bertrand. With the connect storage engine you can read, write and update files in a lot of different storage formats: ● Various fixed and dynamic text formats ● .DBF (dBASE format) ● .CSV ● .INI ● .XML ● ODBC ; Table extracted from an application accessible with ODBC; You can for example connect MariaDB to an Oracle database with this. ● JSON + BSON 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Free MariaDB/MySQL/SQL99 documentation The knowledgebase allows you to: ● Find answers to your problems ● Ask questions and get answers from others ● Add your own documentation or help with translations 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. There are a lot of others involved ● Most features in MariaDB 5.2 and 10.1 were contributed by the community! ● Many of the advanced features in MariaDB 5.3 where sponsored features ● In the mariadb.com/kb knowledge base (free MariaDB and MySQL documentation) we have now 3000 (mostly English) articles Statistics from the August 2015: ● Added/Changed Articles: 129 ● On Freenode #maria, 660 people wrote 8779 lines ● Launchpad & Github Activity ● 12 active branches / Git: 149 forks, 569 stars, 117 watching ● 395 commits / Git total: 172611 commits, 71 branches ● More than 2.5 M downloads of MariaDB. Probably >> 4M users ● Google has employed people to work on MariaDB. 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. MariaDB popularity is increasing ● In December 2012 ● Wikipedia announced they are moving to MariaDB. ● In January-March 2013 ● DB at Mozilla blogged they have moved to MariaDB ● Fedora voted 7-0 to make MariaDB the default MySQL database ● OpenSuse 12.3 included MariaDB as default. ● Slackware, Chakra Linux and Arch Linux has MariaDB as default. ● In April 2013 ● Google is basing their new SQL offerings on MariaDB ● FusionIO is showing benchmarks with MariaDB. ● June 2013 ● RedHat announced it will include MariaDB in RedHat Enterprise. ● December 2013 ● MariaDB was added to Debian and later included in Ubuntu ● 2014 ● RedHat Enterprise Linux 7, Suse Enterprise and Oracle unbreakable Linux has MariaDB as default 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Reasons to switch to MariaDB today ● MariaDB has 20 man years of more development than MySQL (and the gap will continue growing). ● MariaDB is maintained by the people that originally created MySQL and has the best knowledge of the MySQL code. ● MariaDB is binary compatible (data and API) with MySQL, so its trivial to replace MySQL with MariaDB (minutes). ● Reasons to switch to MariaDB ● Faster queries thanks to XtraDB and TokuDB (both from Percona), a much better optimizer, better replication and better code. ● Open source development: Anyone can be part of the development at all stages. Developer meetings are public! ● More features, including critical ones like true parallel replication, better statistics, dynamic columns and Spider & Cassandra storage engines. ● Less risk as MariaDB will not remove features like MySQL is doing (thread pool, storage engines, safemalloc (developer feature), older OS) 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. MariaDB 10.1 was made possible thanks to ● MariaDB corporation ● Replication, optimizer, security, speed enhancements, bug fixing, etc ● MariaDB foundation ● Power8 optimization ● Documentation ● Query timeouts ● Character set enhancements and speedups ● Upgraded regexp library (PCRE) ● Reviews for replication, encryption, compression, Galera, Connect storage engine, Moronga storage engine, Spider, OR REPLACE etc. 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. MariaDB 10.1 was made possible thanks to ● Google ● Encryption, scrubbing, enhanced semisync, dump thread enhancements, thd_specifics plugin service ● Eperi GmbH ● Table level encryption, plugin for secure encryption ● DaumKakao Inc ● Defragmentation, online alter progress monitoring ● Codership ● Galera (wsrep patches) ● Antony Curtis ● Compound statements ● Sriram Patil ● CREATE OR REPLACE/IF NOT EXISTS 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. MariaDB 10.1 was made possible thanks to ● Daniel Black ● New status variables for replication & other patches ● Stewart Smith ● Optimizations for Power8 ● Daniël van Eeden ● RESET MASTER TO # In cooperation with: ● IBM ● Power8 performance and scalability optimizations ● FusionIO ● Atomic writes, page compression, trim, multi- threaded flush for XtraDB/InnoDB 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. MariaDB 10.1 was made possible thanks to Also used code snippets by: ● Facebook / Webscale ● Defragmentation, prefix index queries optimization, lazy flushing, buffer pool list scan optimization, configurable long semaphore wait timeout ● Percona ● SET STATEMENT, enforce_storage_engine ● Oracle ● UNION ALL optimization, default_tmp_storage_engine 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. How you can help ● Help develop features in MariaDB 10.x ● There is a lot of easy thing 'any' C/C++ developer could do ● Help with writing, answering questions or translating documentation in the knowledge base at https://mariadb.com/kb/en/ ● Be active on the MariaDB email lists (maria- [email protected]) ● Spread the word about MariaDB ● Talk about MariaDB in your blogs or at conferences. ● Convince your company to support, become a member or hire a developer for the MariaDB foundation. ● Become a support partner to MariaDB Corporation. You can find information of how to be part of the MariaDB development team at: https://mariadb.com/kb/en/community/ 

Notice: MySQL is a registered trademark of Sun Microsystems, Inc. Questions ? For questions later, use the public MariaDB email list at [email protected] or #maria on Freenode. For questions regarding the MariaDB foundation: [email protected]