MariaDB 10.1 101

Presentation for SELF 2015

Transcript

1. MariaDB 10.1 Daniel Bartholomew, MariaDB Release Manager MariaDB – mariadb.org dbart@mariadb.org | daniel-bartholomew.com 101
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 an independent board • Ensures MariaDB is compatible with MySQL, maintains mariadb.org, keeps 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 – Available as 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 – 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. 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…)
9. Where to get MariaDB • Probably already in your distribution's package repository (esp. if you're using a recent release) – 5.5 in some, 10.0 in others • https://downloads.mariadb.org/ – Official location for latest MariaDB releases – Repositories (apt & yum), generic binary releases, Windows packages, repository configuration tool
10. MariaDB 10.1 • Main 10.1 information page: – https://mariadb.com/kb/en/what-is-mariadb-101/ • 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 – 2 Mar 2015 – MariaDB 10.1.3 beta – 13 Apr 2015 – MariaDB 10.1.4 beta – 4 Jun 2015 – MariaDB 10.1.5 beta
11. 10.1 – WebScaleSQL • Many individual patches – https://mariadb.atlassian.net/browse/MDEV-6039 • Several of them also backported to 10.0 – …when they were fixing bugs & not changing behavior
12. 10.1 – Galera Cluster • 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 • wsrep_membership & wsrep_status Information Schema tables • https://mariadb.com/kb/en/mariadb-galera-cluster/
13. 10.1 – Encryption • Table & Tablespace 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; w/ Aria for temp tables • New file_key_management plugin • https://mariadb.com/kb/en/table-encryption/
14. 10.1 – Optimistic parallel replication • 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 • https://mariadb.com/kb/en/parallel-replication/#optimis tic-mode-of-in-order-parallel-replication
15. 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 – https://mariadb.com/kb/en/thread-pool-in-mariadb/
16. 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 – https://mariadb.com/kb/en/thread-pool-in-mariadb/
17. 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 InnoDB/XtraDB) – https://mariadb.com/kb/en/innodb-xtradb-page-compr ession/ • 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
18. 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
19. 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 (saves wear on flash) – https://mariadb.atlassian.net/browse/MDEV-6932 • LRU (least recently used) flushing regardless of server activity – 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
20. 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;
21. 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/
22. 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/
23. 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/
24. 10.1 – GIS • Full compliance for the OGC standards around GIS – https://mariadb.com/kb/en/what-is-mariadb-101/#gis • MariaDB GIS is now OpenGIS compliant, and passes all the OpenGIS conformance tests – https://mariadb.atlassian.net/browse/MDEV-7509
25. 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 for current user) – https://mariadb.atlassian.net/browse/MDEV-4397 • Kerberos authentication plugin – https://mariadb.atlassian.net/browse/MDEV-4691
26. 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 – https://mariadb.com/kb/en/information-schema-system_ variables-table/ • 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)
27. 10.1 – Other bits cont… • 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 • Overview of new status & system variables in 10.1: – https://mariadb.com/kb/en/system-variables-added-in-mariadb-101/ – https://mariadb.com/kb/en/status-variables-added-in-mariadb-101/
28. 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/
29. 10.1 – How to get it • https://downloads.mariadb.org • Not listed in the repository configurator – https://downloads.mariadb.org/mariadb/repositories/ – People were installing it in production when it was still alpha! – Now partially hidden to help save people from themselves – Choose MariaDB 10.0, then in the instructions, replace 10.0 with 10.1 – Will be added to the configuration tool when it reaches gamma (release candidate) stage
30. 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)
31. Offline Resources (Books) • https://mariadb.com/kb/en/books/
32. Online Resources • Knowledge Base: – https://mariadb.com/kb • JIRA (bug/dev tracker): – http://mariadb.org/jira • Code: – All dev trees are now on GitHub – https://github.com/MariaDB/server
33. 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: – facebook.com/MariaDB.dbms – Twitter: @mariadb – Google+: +MariaDB – #maria on irc.freenode.net
34. Thanks! dbart@mariadb.org @daniel_bart & +DanielBartholomew speakerdeck.com/dbart launchpad.net/~dbart & github.com/dbart daniel-bartholomew.com