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

MariaDB 10.1 101

MariaDB 10.1 101

Presentation for SELF 2015

Daniel Bartholomew

June 12, 2015
Tweet

More Decks by Daniel Bartholomew

Other Decks in Technology

Transcript

  1. MariaDB 10.1
    Daniel Bartholomew, MariaDB Release Manager
    MariaDB – mariadb.org
    [email protected] | daniel-bartholomew.com
    101

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

    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
    – Available as 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
    – 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. 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

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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/

    View Slide

  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/

    View Slide

  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

    View Slide

  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/

    View Slide

  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/

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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;

    View Slide

  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/

    View Slide

  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/

    View Slide

  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/

    View Slide

  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

    View Slide

  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

    View Slide

  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)

    View Slide

  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/

    View Slide

  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/

    View Slide

  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

    View Slide

  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)

    View Slide

  31. Offline Resources (Books)

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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide