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
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
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…
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
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/
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…)
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
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/
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/
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?
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)
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/
10.0 – Explain and Mem Usage ● SHOW EXPLAIN for 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
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/
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/
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");
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
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/
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/
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/
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/
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
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
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
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;
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/
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
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/
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
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)
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
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/
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)
dbart
devops_vtj
sakiengineer
microcms
yuyakakizaki08
keithyokoma
goyoki
yu4u
startree
kadogen_0527
takashi1029
lefred
spinscale
jcasabona
addyosmani
paigeccino
searls
lynnandtonic
maltzj
keavy
sachag
tmm1
caitiem20
davidbonilla
michaelherold