MariaDB 10.0 & 10.1 - The Good Stuff

Talk given at Percona University, Raleigh.

Transcript

1. MariaDB 10.0 & 10.1 The good stuff Daniel Bartholomew MariaDB Release Manager MariaDB – mariadb.org dbart@mariadb.org daniel-bartholomew.com
2. Before we begin… • I dislike “wall of text” slide presentations, but that's what this is, sorry about that • I am not a “big time” DBA or a developer, so… – Likely unable to answer in-depth questions • Covering a lot of ground, so… – Lots of links – http://speakerdeck.com/dbart
3. What is MariaDB? • MySQL was named after Monty's oldest daughter, My, MariaDB named after his youngest. • Community developed branch/fork/evolution of MySQL • Additional features, storage engines, plugins • Goal is to be compatible & feature complete with MySQL • 5 years old! – Project started in early 2009 – First stable (GA) release: 1 Feb 2010
4. Backed by MariaDB Foundation • Driver of the MariaDB project • Foundation not controlled by a single entity or person; has a board • Ensure MariaDB is compatible with MySQL, maintain mariadb.org, keep community voice • Major sponsors: MariaDB Corp, Parallels, Booking.com, Automattic, OpenQuery, Percona, Webyog, others…
5. Aims of MariaDB • Compatible, drop-in replacement of MySQL – Data on disk & on the wire is the same – Same file names, sockets, port, etc… • Stable (bug-free) releases with no regressions • GPLv2, just like Linux kernel • No closed source “enterprise only” features
6. MariaDB Popularity • Wikipedia (English, German), Wikidata run MariaDB – http://blog.wikimedia.org/2013/04/22/wikipedia-adopts-mariadb/ • Google, Tumblr, SpamExperts, Nimbuzz, Web of Trust, FictionPress, & many others, use MariaDB • Fedora, OpenSUSE, Suse, Red Hat shipping MariaDB as a default – Slackware, Chakra, Arch, Mageia, others also – Alternative in Debian & Ubuntu • Many success stories: – https://mariadb.com/kb/en/mariadb/case-studies/
7. Well supported • Everyone supports MariaDB from a consulting & support standpoint (except Oracle) – MariaDB Corp, Percona, etc… • Jelastic offers MariaDB as PaaS – http://docs.jelastic.com/connection-to-mariadb • MariaDB in Ubuntu Juju Charm Store – https://jujucharms.com/mariadb/ • Many different Docker containers, Amazon AMIs, Chef cookbooks, Puppet modules, & etc… • All stable (GA) releases supported for 5 years (security & bug fixes, and etc…)
8. Where to get it • Might already be in your distribution's package repository (esp. if you're using a recent release) – 10.0 series under development/unstable branches in some distributions • https://downloads.mariadb.org/ – Official location for latest MariaDB releases – Repositories (apt & yum), generic binary releases, Windows packages, repository configuration tool
9. History – MariaDB 5.1-5.3 • Built on MySQL 5.1 • Fewer warnings & bugs, improved test suite • Features include: extra storage engines (Aria, XtraDB, FederatedX, etc…), table elimination, virtual columns, extended user statistics, segmented MyISAM keycache, pool of threads, optimizer features, microsecond precision, faster HANDLER interface, dynamic columns, virtual columns, HandlerSocket, group commit in the binary log, checksum for binary log events, progress reporting for ALTER TABLE/LOAD DATA INFILE, GIS precise operations, kill by query ID (not just thread ID) or by USER, & lots more… – https://mariadb.com/kb/en/what-is-mariadb-51/ – https://mariadb.com/kb/en/what-is-mariadb-52/ – https://mariadb.com/kb/en/what-is-mariadb-53/
10. History – MariaDB 5.5 • Built on MariaDB 5.3 + MySQL 5.5 • Non-blocking client library • LIMIT ROWS EXAMINED • Extended keys for XtraDB/InnoDB • New SphinxSE • New & improved threadpool • Lots more… – https://mariadb.com/kb/en/what-is-mariadb-55/
11. MariaDB 10.0 in a nutshell • Current stable series • Built on MariaDB 5.5 • Includes crossported & reimplemented features from MySQL 5.6 • Many new features – https://mariadb.com/kb/en/what-is-mariadb-100/
12. Why MariaDB 10.0? • The 5.5 merge took about a year (!) • 5.6 merge promised to be even harder – Had to deal with pointless refactoring, shoddy code, features already in MariaDB, etc… • The diff between MariaDB 5.5. & MySQL 5.5 is ~61MB – over 1.5 million lines of extra code • MariaDB is not a patch set against MySQL • MariaDB does not depend on MySQL for future development, so why tie ourselves to their release schedule?
13. 10.0 – Crossported features • InnoDB (from MYSQL) • PERFORMANCE_SCHEMA – https://mariadb.com/kb/en/performance-schema/ • Online ALTER TABLE – https://mariadb.com/kb/en/alter-table/ • Upstream collation tables – https://mariadb.atlassian.net/browse/MDEV-4928 • Privileges on temporary tables • GET DIAGNOSTICS – https://mariadb.com/kb/en/get-diagnostics/ • Optimizer – ORDER BY…LIMIT optimization (shows only a few rows of a result set)
14. 10.0 – Re-implemented features • Error messages (w/system error string + meanings) • CURRENT_TIMESTAMP / NOW() as DEFAULT for DATETIME columns • EXPLAIN for INSERT/UPDATE/DELETE • Temporal literals – TIME'12:05:00'
15. 10.0 – Replication • Global Transaction ID – Have complex replication topologies; simple failover & slave promotion, doesn't require restarts – New slave provisioning: SET GLOBAL GTID_SLAVE_POS = BINLOG_GTID_POS(“masterbin.00045”,600); CHANGE MASTER TO master_host=“192.168.2.4”, master_use_gtid=slave_pos; START SLAVE; – Turning on GTID for slaves: STOP SLAVE; CHANGE MASTER TO master_use_gtid=current_pos; START SLAVE; – Change masters: STOP SLAVE; CHANGE MASTER TO master_host=“10.2.3.5”; START SLAVE; – https://mariadb.com/kb/en/global-transaction-id/ • Crash-safe slaves – GTID position stored in InnoDB table
16. 10.0 – Multi-source replication • Work from Taobao/Lixun Peng – Known to be in-use for production purposes at Tumblr, FunPlus Game, Taobao, Flipkart • Many users partition data across many masters… now you can replicate many masters to a single slave • Great for analytical queries, complete backups, etc… • https://mariadb.com/kb/en/multi-source-replication/
17. 10.0 – Explain and Mem Usage • SHOW EXPLAIN for <thread_id> gets the query plan for a running statement – https://mariadb.com/kb/en/show-explain/ • Per-thread memory usage (Taobao) – INFORMATION_SCHEMA.PROCESSLIST has MEMORY_USAGE & EXAMINED_ROWS now – SHOW STATUS has memory usage too
18. 10.0 – CassandraSE • MariaDB as a “data platform” • Integration with NoSQL/Big Data DB, Apache Cassandra cluster, seen as a storage engine to MariaDB • Combine (JOIN) data between Cassandra & MariaDB & Oracle (via CONNECT) • Write to Cassandra from SQL (e.g. SELECT, INSERT, UPDATE, DELETE) • https://mariadb.com/kb/en/cassandra/
19. 10.0 – Engine ind. statistics • InnoDB has persistent statistics in MySQL 5.6; MariaDB has a storage-engine-independent version • These statistics aren't limited by the SE API, and are used by query optimizer to choose the best execution plan for each statement • Statistics collected for non-indexed columns too (unlike InnoDB's) • https://mariadb.com/kb/en/engine-independent-table-statistics/
20. 10.0 – Dynamic columns • Allows you to create virtual columns with dynamic content for each row in a table • Basically a blob with handling functions (GET, CREATE, ADD, DELETE, EXISTS, LIST, JSON) • Store different attributes for each item (like a web store). Hard to do relationally • In MariaDB 10: name support (instead of referring to columns by numbers, name it), convert all dynamic column content to JSON array, interface with Cassandra • https://mariadb.com/kb/en/dynamic-columns/ • INSERT INTO tbl SET dyncol_blob=COLUMN_CREATE("column_name","value");
21. 10.0 – Group commit in the binary log • sync_binlog=1,innodb_flush_log_at_trx_commit=1 • https://facebook.com/note.php?note_id=10150261692455933 • http://mariadb.com/kb/en/group-commit-for-the-binary-log
22. 10.0 – PCRE • Powerful REGEXP/RLIKE operator • New operators: – REGEXP_REPLACE(sub,pattern,replace) – REGEXP_INSTR(sub,pattern) – REGEXP_SUBSTR(sub,pattern) • Works with multi-byte character sets that MariaDB supports, including East-Asian sets • https://mariadb.com/kb/en/pcre/
23. 10.0 – PAM Authentication • Authentication using /etc/shadow • Authentication using LDAP, SSH pass phrases, password expiration, username mapping, logging every login attempt, etc… • INSTALL PLUGIN pam SONAME 'auth_pam.so'; • CREATE USER foo@host IDENTIFIED via pam • REMEMBER to configure PAM (/etc/pam.d or /etc/pam.conf) • https://mariadb.com/kb/en/pam-authentication-plugin/
24. 10.0 – SphinxSE • CREATE TABLE t1 (…) ENGINE=SPHINX CONNECTION="sphinx://localhost:9312/test"; • Engine connects to Sphinx searchd • Let indexing, searching, sorting, filtering be performed by Sphinx – Instead of WHERE, ORDER BY, LIMIT – Sphinx is optimized/fast for these tasks • Most of the Sphinx API is exposed to engine • JOIN search table with other MySQL tables • https://mariadb.com/kb/en/sphinx-storage-engine/
25. 10.0 – GIS support • MySQL has OpenGIS SFS (Simple feature access, SQL access method) – Now, SQL with full geometry types – ST_ prefix • MyISAM, Aria, for SPATIAL & non-spatial indexes • Use Osmosis, you can load all OpenStreetMap data into MariaDB • https://mariadb.com/kb/en/gis-features-in-533/ • https://mariadb.com/kb/en/openstreetmap-dataset/
26. MariaDB 10.1 • Releases – 30 Jun 2014 – MariaDB 10.1.0 alpha – 17 Oct 2014 – MariaDB 10.1.1 alpha – 7 Dec 2014 – MariaDB 10.1.2 alpha – “real soon now” – MariaDB 10.1.3 • https://mariadb.com/kb/en/what-is-mariadb-101/
27. 10.1 – Galera Integration • Full integration of Galera Cluster 4 into MariaDB 10.1 – not a separate download – No lost transactions – Optimisations for WAN replication – Non-blocking DDL – No limits on transaction size • https://mariadb.com/kb/en/mariadb-galera-cluster/
28. 10.1 – Encryption • Tablespace and table level encryption with support for rolling keys using the AES algorithm – Table encryption – PAGE_ENCRYPTION=1 – Tablespace encryption – encrypts everything including log files (but not the binlog) • Encryption overhead of ~10% • XtraDB/InnoDB only; Aria for temporary tables • New file_key_management_plugin • https://mariadb.com/kb/en/table-and-tablespace-encryption • In tree now, will debut when 10.1.3 is released
29. 10.1 – Optimistic parallel repl. • Before, only transactions committed in parallel on the master could be run in parallel on slaves • Now, when replicating multiple transactions, they will be considered to be run in parallel, giving a performance boost in master-to-slave replication • https://mariadb.atlassian.net/browse/MDEV-6676
30. 10.1 – Threadpool • Backport/evolution of threadpool improvements in Percona server – thread_pool_high_prio_mode – thread_pool_high_prio_tickets – https://mariadb.atlassian.net/browse/MDEV-5533
31. 10.1 – InnoDB • Multi-threaded flush (also in 5.7, but different implementation) – https://mariadb.com/kb/en/fusion-io-multi-threaded-flush/ • Page compression (for FusionIO) – https://blog.mariadb.org/significant-performance-boo st-with-new-mariadb-page-compression-on-fusionio/ • 64KB pages in InnoDB (old limit = 16KB) – https://mariadb.atlassian.net/browse/MDEV-6075
32. 10.1 – InnoDB cont… • Defragmentation (from Facebook, ported by DaumKakao) – https://mariadb.com/kb/en/defragmenting-innodb- tablespaces/ • Forced primary key – If option is true, creating a table without a primary key or unique key where all keyparts are NOT NULL is not allowed, instead an error message is printed – https://mariadb.atlassian.net/browse/MDEV-5335 – https://mariadb.com/kb/en/xtradbinnodb-server-sy stem-variables/#innodb_force_primary_key
33. 10.1 – InnoDB WebScaleSQL • Buffer pool list scan optimization – https://mariadb.atlassian.net/browse/MDEV-6936 • Facebook prefix index queries optimization – https://mariadb.atlassian.net/browse/MDEV-6929 • Lazy flushing – https://mariadb.atlassian.net/browse/MDEV-6932 • LRU flushing – https://mariadb.atlassian.net/browse/MDEV-6931 • Clustering key is covering key (TokuDB) – https://tokutek.atlassian.net/browse/DB-746 • lock_wait_timeout_thread wakeup in lock_wait_suspend_thread() – https://mariadb.atlassian.net/browse/MDEV-6933
34. 10.1 – Per query variables • Port from Percona Server – https://mariadb.atlassian.net/browse/MDEV-5231 • History: – http://www.bytebot.net/blog/archives/2014/05/04/p er-query-variable-settings-in-mysqlpercona-server webscalesql • Example: – SET STATEMENT max_statement_time=1000 FOR SELECT name FROM name ORDER BY name;
35. 10.1 – Query timeouts • Statement timeouts patch from Twitter; re- written by Monty – https://mariadb.atlassian.net/browse/MDEV-4427 • MAX_STATEMENT_TIME to abort long-running queries – https://mariadb.com/kb/en/aborting-statements/
36. 10.1 – Optimizer • UNION ALL without temporary tables (5.7) – https://mariadb.com/kb/en/union/ • Improve ORDER BY in optimizer – https://mariadb.com/kb/en/improvements-to-order-by/ • EXPLAIN JSON (like 5.6) – https://mariadb.com/kb/en/explain-format-json/ – https://mariadb.com/kb/en/explain-format-json-differences/ • EXPLAIN ANALYZE with FORMAT=JSON – Includes data from the query execution itself – MariaDB only – https://mariadb.com/kb/en/analyze-format-json/
37. 10.1 – WebScaleSQL • Many individual patches – https://mariadb.atlassian.net/browse/MDEV-6039 • Many of them also backported to 10.0 – When they were fixing bugs & not changing behavior
38. 10.1 – Passwords • Password validation plugin API – https://mariadb.com/kb/en/password-validation/ • simple_password_check plugin – Can enforce a minimum password length and guarantee that a password contains at least a specified number of upper and lowercase letters, digits, and punctuation characters – https://mariadb.com/kb/en/simple_password_check/ • cracklib_password_check plugin – Allows passwords that are strong enough to pass the CrackLib test (same test that pam_cracklib.so does) – https://mariadb.com/kb/en/cracklib_password_check/
39. 10.1 – Other bits • Slaves can execute triggers w/ row-based replication – https://mariadb.atlassian.net/browse/MDEV-5095 • CONNECT has full JSON/BSON support – https://mariadb.com/kb/en/connect/ • CREATE or REPLACE for most database objects minus indexes – https://mariadb.atlassian.net/browse/MDEV-5359 • SET DEFAULT ROLE (there is a default role now for current user) – https://mariadb.atlassian.net/browse/MDEV-4397
40. 10.1 – Other bits cont… • FRM files are now not created for temporary tables – https://mariadb.atlassian.net/browse/MDEV-4260 • INFORMATION_SCHEMA.SYSTEM_VARIABLES – https://mariadb.atlassian.net/browse/MDEV-6138 • Compiled with security hardening options, fortify source – https://mariadb.atlassian.net/browse/MDEV-5730 • @@sql_log_slow can now be controlled on a session basis (not just globally)
41. 10.1 – GIS • Full compliance for the OGC standards around GIS • MariaDB GIS is now OpenGIS compliant, and passes all the OpenGIS conformance tests – https://mariadb.atlassian.net/browse/MDEV-7509
42. 10.1 – Other • Kerberos authentication plugin – https://mariadb.atlassian.net/browse/MDEV-4691 • Audit plugin to track password changes – https://mariadb.com/kb/en/mariadb-audit-plugin/ • Ipv6/Ipv4 datatype (pending review) – https://mariadb.atlassian.net/browse/MDEV-274 • Additional character sets (GB18030) for Chinese government mandate (pending review) – https://mariadb.atlassian.net/browse/MDEV-7495
43. 10.1 – Compatibility • Temporary tables are stored in Aria but now there is a --default-tmp-storage-engine option – https://mariadb.atlassian.net/browse/MDEV-6107 • engine_condition_pushdown flag removed (it's always on for engines that support it) – https://mariadb.atlassian.net/browse/MDEV-6513 • --mysql56-temporal-format option to use the MySQL 5.6 low level formats to store TIME, DATETIME, and TIMESTAMP types – https://mariadb.com/kb/en/server-system-variables/#mysql56_te mporal_format • PERFORMANCE_SCHEMA disabled by default (like in 10.0) – https://mariadb.com/kb/en/performance-schema/
44. Feedback plugin • Disabled by default, consider enabling it to show use! – https://mariadb.com/kb/en/feedback-plugin/ – Collected data: http://mariadb.org/feedback_plugin • Data from over 150 countries! • But only ~6000 servers actively uploading data, most of them Windows (so not many production servers) • Can also be used for internal statistics gathering (with feedback-url option)
45. Offline Resources (Books) • https://mariadb.com/kb/en/books/
46. Online Resources • Knowledge Base: https://mariadb.com/kb • JIRA (bug/dev tracker): http://mariadb.org/jira • Code: – 10.0 on Launchpad: • https://code.launchpad.net/maria/10.0 – 10.1 on GitHub: • https://github.com/MariaDB/server/tree/10.1 – Plan to move all code to GitHub
47. Online Resources cont… • Low traffic announce list: http://lists.askmonty.org/cgi-bin/mailman/listinfo/announce • Discussion list: https://launchpad.net/~maria-discuss • Developer list: https://launchpad.net/~maria-developers • Social: – fb.com/MariaDB.dbms – Twitter: @mariadb – G+: +MariaDB – #maria on irc.freenode.net – Knowledge Base: https://mariadb.com/kb/en/
48. Thanks! dbart@mariadb.org @daniel_bart & +DanielBartholomew speakerdeck.com/dbart launchpad.net/~dbart & github.com/dbart daniel-bartholomew.com