Downloaded 43 times
![Progress reporting For ALTER TABLE or LOAD DATA INFILE MariaDB [mail]> alter table mail engine = maria; Stage: 1 of 2 'copy to tmp table' 17.55% of stage done MariaDB [mail]> select id, user, db, command, state, -> time_ms, progress from information_schema.processlist; +---------+-------------------+-----------+----------+ | command | state | time_ms | progress | +---------+-------------------+-----------+----------+ | Query | copy to tmp table | 23407.131 | 17.551 | +---------+-------------------+-----------+----------+ 1 row in set (0.47 sec)](https://image.slidesharecdn.com/08-colincharlesmariadbthenewmysql-121023105419-phpapp02/75/Maria-db-the-new-mysql-Colin-Charles-21-2048.jpg)
Uploaded byOntico
Maria db the new mysql (Colin Charles)
MariaDB is a community-developed, drop-in replacement for MySQL that aims to be fully compatible without compromising on features or stability. Over the past 32 months, MariaDB has released four major versions with new features like improved replication, optimization enhancements, and storage engines. It is led by many of the original developers of MySQL and has a large community of contributors working to advance it as a better open source database.
Maria db the new mysql (Colin Charles)
- 1. MariaDB: The New MySQL? Colin Charles, Monty Program Ab colin@montyprogram.com | colin@mariadb.org http://montyprogram.com/ | http://mariadb.org/ http://bytebot.net/blog/ | @bytebot on Twitter HighLoad++, Moscow, Russia 23 October 2012
- 2. whoami MariaDB guy atMonty Program Ab Formerly MySQL AB/Sun Microsystems Past lives included Fedora Project (FESCO), OpenOffice.org
- 3. Aims 32 months, fourmajor releases (5.1,5.2,5.3,5.5) & more (Galera Cluster, 10.0) Discuss the broader MySQL ecosystem The future of MariaDB
- 4. First up... Thank youOracle & Percona and the larger MySQL community for continuing to do work that is great
- 5. MariaDB is... (currently) Community developed Feature enhanced a better MySQL Fully compatible with MySQL not a fork feature complete
- 6. Ownership MySQL (database) ownedby MySQL AB (company) -> Sun -> Oracle Monty Program is a (major) sponsor of MariaDB maria-captains: 50% MP, 50% community including Sphinxsearch, Twitter, SkySQL, Taobao, Facebook, Percona, Codership, & more
- 7. Aims of MariaDB 100%compatible, drop-in replacement to MySQL i.e. no changes in connectors, app doesn’t care its MariaDB unless using new features, easy upgrade (uninstall mysql, install mariadb, magic!) Stable (bug-free) releases with no regressions GPLv2
- 8. Compatibility with MySQL NDB cluster included, but not enabled by default Client libraries, client server protocol, SQL dialect, master-slave replication all same Data files supported as long as same versions XtraDB enabled by default now. InnoDB included too & may change to default MariaDB Galera Cluster is a separate download http://kb.askmonty.org/v/mariadb-versus-mysql- compatibility
- 9. XtraDB ENGINE=InnoDB uses XtraDBby default Less checkpointing (smoother), less flushing to disk, stable performance
- 10. MariaDB 5.5 GA April2012 https://kb.askmonty.org/en/what-is-mariadb-55/ Current release: MariaDB 5.5.27 By far the most popular release of MariaDB downloaded, in use, today
- 11. MariaDB 5.5: an opensourcethreadpool Modified from 5.1 (libevent based), great for CPU bound loads and short running queries Windows (threadpool), Linux (epoll), Solaris (event ports), FreeBSD/OSX (kevents) No minimization of concurrent transactions with dynamic pool size
- 12. MariaDB 5.5: Betterfor DBAs non-blocking client library start operation, do work in thread, operation processed, result travels back use cases: multiple queries against single server (utilize more CPUs); queries against multiple servers (SHOW STATUS on many machines) fast node.js driver available: mariasql SELECT now has LIMIT ROWS EXAMINED to consume less resources SELECT * from t1, t2 LIMIT 10 ROWS EXAMINED 1000;
- 13. That’s it? From December2010 to April 2012, 3 features for a 16 month delay?!?
- 14. MariaDB 5.3 Biggest changein the optimizer since it was written - more complete than MySQL 5.6RC!!! Replication improvements Released February 2012 Based on the MySQL 5.1 codebase
- 15. Better replication Original statementslogged with row based replication (RBR) events Checksum for binlog events RBR works for tables with no primary key Consistent snapshot between storage engines
- 16. Group commit inthe binary log sync_binlog=1, innodb_flush_log_at_trx_commit=1 https://www.facebook.com/note.php?note_id=10150261692455933 http://kb.askmonty.org/en/group-commit-for-the-binary-log
- 17. MariaDB 5.3: GIS support! MySQL has OpenGIS SFS (Simple feature access, SQL access method) Now, SQL with full geometry types ST_ prefix http://kb.askmonty.org/en/gis-features-in-533
- 18. Optimizer enhancements Joinadditions block nested loop joins for outer-joins, block hash joins, Batched Key Access (BKA) Optimization for derived tables & views mergeable derived tables processed like VIEWs + optimizer creates indexes over materialized derived tables Disk access optimization Index Condition Pushdown (ICP), Multi-Range Read (MRR)
- 19. Subquery optimizations Semi-joinoptimization, materialization for non- correlated IN queries, subquery cache Goodbye rewriting as JOINs or separate queries DBT-3, 60M rows, 29GB XtraDB
- 20. Welcome MySQL 5.6 InstantEXPLAIN https://kb.askmonty.org/en/optimizer-feature-comparison-matrix/
- 21. Progress reporting For ALTER TABLE or LOAD DATA INFILE MariaDB [mail]> alter table mail engine = maria; Stage: 1 of 2 'copy to tmp table' 17.55% of stage done MariaDB [mail]> select id, user, db, command, state, -> time_ms, progress from information_schema.processlist; +---------+-------------------+-----------+----------+ | command | state | time_ms | progress | +---------+-------------------+-----------+----------+ | Query | copy to tmp table | 23407.131 | 17.551 | +---------+-------------------+-----------+----------+ 1 row in set (0.47 sec)
- 22. NoSQL access methods Comes with HandlerSocket direct access to XtraDB/InnoDB for CRUD operations Dynamic columns create columns with dynamic content basically a blob with handling functions
- 23. MariaDB 5.2 Released November2011 Pluggable authentication w/PAM plugin User statistics: CLIENT_STATISTICS, USER_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS (userstats=1) Virtual columns - PERSISTENT or VIRTUAL
- 24. Still using MyISAM? Mitigatesthread contention for key cache lock, with notable performance improvements Key caches divided into different segments, allowing for better key cache concurrency 1-64 segments
- 25. 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
- 26. MariaDB 5.1 Arrived February2010 Storage engines: XtraDB, FederatedX, PBXT*, OQGRAPH*, Aria, etc. microslow patch, processlist w/microsecond precision table elimination
- 27. What is Aria? Stilljust a crash-safe MyISAM used for temporary tables internally group commit added in 5.2 to speed up multi- user inserts
- 28. How do wesupport all this? Support Latest Version Released MySQL till release 5.1.42 Feb 2010 Feb 2015 5.1.62 5.1.63 5.2.3 Nov 2010 Nov 2015 5.2.12 5.1.63 5.3.5 Feb 2012 Feb 2017 5.3.8 5.1.65 5.5.23 Apr 2012 Apr 2017 5.5.28 5.5.28
- 29. Oh, and we’refast Oracle loves MySQL performance on Microsoft Windows. So do we OLTP read-only OLTP write-only
- 30. How open isMariaDB? Mailing lists: Launchpad maria-developers@lists.launchpad.net maria-discuss@lists.launchpad.net Code hosting: Launchpad http://launchpad.net/maria/ Bugs database/feature worklog: Jira http://mariadb.atlassian.net/ #maria on freenode
- 31.
- 32. Deployments! happy users: pap.fr,wabtec, Paybox Services, OLX, Jelastic, Web of Trust, SaltOS, ERP5, etc. “We made the switch on Saturday -- and we’re seeing benefits “MariaDB had these same bugs that we ran into already -- our daily optimization with MySQL. However the big difference was that time is down from 24 minutes to when we reported these bugs, they were quickly just 4 minutes” -- Ali Watters, resolved within 48 hours!” -- Dreas van Donselaar, CEO, travelblog.org Chief Technology Officer, SpamExperts B.V. after migrating over 300 servers from MySQL 5.0 to MariaDB 5.1. “Migrating from MySQL 5.1 to MariaDB 5.2 was as simple as removing MySQL RPMs and installing the MariaDB packages, then running mysql_upgrade.” - Panayot Belchev, proprietor, Host Bulgaria on providing MariaDB to over 7,000 of their web hosting customers.
- 33. Getting MariaDB http://mariadb.org/ hasrepositories (APT,YUM) & regular downloads OpenSUSE build service Gentoo, FreeBSD, Homebrew, Mageia, many distributions http://kb.askmonty.org/v/distributions-which-include-m
- 34. We have abook
- 35. Other branches MariaD Percon MySQL Percona MySQL MariaDB B a 5.5.20 7.7M 61M 5.5.20 222299 1587843 5.5.22 16M 60M 5.5.22 438567 1540932
- 36. What are wemissing from Percona Server? percona_innodb_buffer_pool_shm - requires big shmax not default on many systems percona_log_slow_query_log- log_slow_verbosity - InnoDB filtering information not fully in MariaDB Disabled test suite is resource
- 37. Faster even? read only? InnoDB/XtraDB is fine InnoDB has higher throughput, but stalls & checkpoints w/high write load
- 38. Track record We foundthe latest MySQL security bug and MariaDB was first to be patched (sql/password.c & memcmp()) We don’t like regressions http://www.skysql.com/blogs/hartmut/nasty-innodb-regression-mysql-5525 http://www.skysql.com/blogs/kolbe/heads-no-more-query-cache-partitioned-tabl We care about backward compatibility & introduce features carefully XtraDB innodb_adaptive_checkpoint=none|reflex|estimate| keep_average (no more reflex...)
- 39. We care aboutquality Automated test suite run upon every push Better QA & code coverage MySQL test cases: 1,765 Percona Server test cases: 1,837 MariaDB test cases: 2,180
- 40. User stats plugin Disabled by default, consider enabling it to show use! http://mariadb.org/feedback_plugin/ data from over 85 countries!
- 41. We love thecommunity Tokutek’s TokuDB ships with MariaDB too groonga/spider may be merged next Multiple software packages talk about us (as an alternative to MySQL): Drupal, MediaWiki, Plone, phpMyAdmin, WordPress, etc.
- 42. Future MySQL 5.6 justRC, we have most features in 5.5 MariaDB 10.0 new InnoDB inside MariaDB (done) Global transaction ID support Multi-source replication (done) Enhanced semisync replication Persistent InnoDB statistics (done) Cassandra Storage Engine (done), HBase Storage Engine virtual machine for Cassandra made by community!
- 43. MariaDB Galera Cluster http://kb.askmonty.org/en/galera/ Scale your read & write Now in ALPHA! workloads, automatically, without the need for NDB Cluster!
- 44. Support mariadb.org/service-providers/ SkySQL, Percona, PalominoDB,etc. Monty Program does developer support & NRE http://montyprogram.com/developer-support/
- 45. We’re incredibly social facebook:fb.com/MariaDB.dbms twitter: @mariadb google plus: gplus.to/mariadb we also have a LinkedIn group
- 46. Q&A colin@montyprogram.com | colin@mariadb.org http://montyprogram.com/ | http://mariadb.org/ twitter: @bytebot | url: http://bytebot.net/blog/