Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MariaDB 10.0 & 10.1 - The Good Stuff

MariaDB 10.0 & 10.1 - The Good Stuff

Talk given at Percona University, Raleigh.

Daniel Bartholomew

February 12, 2015
Tweet

More Decks by Daniel Bartholomew

Other Decks in Technology

Transcript

  1. MariaDB 10.0 & 10.1
    The good stuff
    Daniel Bartholomew
    MariaDB Release Manager
    MariaDB – mariadb.org
    [email protected]
    daniel-bartholomew.com

    View Slide

  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

    View Slide

  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

    View Slide

  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…

    View Slide

  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

    View Slide

  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/

    View Slide

  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…)

    View Slide

  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

    View Slide

  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/

    View Slide

  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/

    View Slide

  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/

    View Slide

  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?

    View Slide

  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)

    View Slide

  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'

    View Slide

  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

    View Slide

  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/

    View Slide

  17. 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

    View Slide

  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/

    View Slide

  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/

    View Slide

  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");

    View Slide

  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

    View Slide

  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/

    View Slide

  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 [email protected] IDENTIFIED via pam

    REMEMBER to configure PAM (/etc/pam.d or
    /etc/pam.conf)

    https://mariadb.com/kb/en/pam-authentication-plugin/

    View Slide

  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/

    View Slide

  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/

    View Slide

  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/

    View Slide

  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/

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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;

    View Slide

  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/

    View Slide

  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/

    View Slide

  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

    View Slide

  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/

    View Slide

  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

    View Slide

  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)

    View Slide

  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

    View Slide

  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

    View Slide

  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/

    View Slide

  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)

    View Slide

  45. Offline Resources (Books)

    https://mariadb.com/kb/en/books/

    View Slide

  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

    View Slide

  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/

    View Slide

  48. Thanks!
    [email protected]
    @daniel_bart & +DanielBartholomew
    speakerdeck.com/dbart
    launchpad.net/~dbart & github.com/dbart
    daniel-bartholomew.com

    View Slide