Tab housekeeping but I also realise that people seem to have missed announcements, developments, etc. that have happened in the last couple of months (and boy have they been exciting). I think we definitely need something like the now-defunct MySQL Newsletter (and no, DB Weekly or NoSQL Weekly just don’t seem to cut it for me!).
During @scale (August 31), Yoshinori Matsunobu mentioned that MyRocks has been deployed in one region for 5% of its production workload at Facebook.
By October 4 at the Percona Live Amsterdam 2016 event, Percona CEO Peter Zaitsev said that MyRocks is coming to Percona Server (blog). On October 6, it was also announced that MyRocks is coming to MariaDB Server 10.2 (note I created MDEV-9658 back in February 2016, and that’s a great place to follow Sergei Petrunia’s progress!).
Rick Pizzi talks about MyRocks: migrating a large MySQL dataset from InnoDB to RocksDB to reduce footprint. His blog also has other thoughts on MyRocks and InnoDB.
Of course, checkout the new site for all things MyRocks! It has a getting started guide amongst other things.
Proxies: MariaDB MaxScale, ProxySQL
With MariaDB MaxScale 2.0 being relicensed under the Business Source License (from GPLv2), almost immediately there was a GPLScale fork; however I think the more interesting/sustainable fork comes in the form of AirBnB MaxScale (GPLv2 licensed). You can read more about it at their introductory post, Unlocking Horizontal Scalability in Our Web Serving Tier.
ProxySQL has a new webpage, a pretty active mailing list, and its the GPLv2 solution by DBAs for DBAs.
Vitess 2.0 has been out for a bit, and a good guide is the talk at Percona Live Amsterdam 2016, Launching Vitess: How to run YouTube’s MySQL sharding engine. It is still insanely easy to get going (if you have a credit card), at their vitess.io site.
The MariaDB Server original goals were to be a drop-in replacement. In fact this is how its described (“It is an enhanced, drop-in replacement for MySQL”). We all know that its becoming increasingly hard for that line to be used these days.
Anyhow in March 2016, Debian’s release team has made the decision that going forward, MariaDB Server is what people using Debian Stretch get, when they ask for MySQL (i.e. MariaDB Server is the default provider of an application that requires the use of port 3306, and provides a MySQL-like protocol).
All this has brought some interesting bug reports and discussions, so here’s a collection of links that interest me (with decisions that will affect Debian users going forward).
- Don’t include in stretch – bug#837615 – this is about how MariaDB Server 10.0 (note the version – this matters) should be included, but MySQL 5.6 shouldn’t be.
- MariaDB 10.1? – note that Otto Kekäläinen, CEO of the MariaDB Foundation, says the plan is to skip MariaDB Server 10.1 and go straight to MariaDB Server 10.2. As of this writing, MariaDB Server 10.2 is in its first beta released 27 Sep 2016, so are we expecting a few more betas before the release candidate? History shows there were four betas for 10.1 and one release candidate, while there were three betas and two release candidates of 10.0. There is no response here as to what is gained from skipping MariaDB Server 10.1, but one can guess that this has to do with support cycles.
- default-mysql-client forces removal of mysql-server* and mysql-client* – bug#842011 – bug reporter is a bit hostile towards the package team, but the gist is that “mariadb is NOT a drop-in replacement for mysql.” Users are bound to realise this once Debian Stretch gets more mainstream use.
[debian-mysql] Bug#840855: Bug#840855: mysql-server: MySQL 5.7? – questioning what happens to MySQL 5.7, and this is really a call to action – if you disagree, email the security and release teams now not after Stretch is released! Quoting Clint Byrum, “The release and security teams have decided that MySQL will live only in unstable for stretch due to the perceived complications with tracking security patches in MySQL.”
[debian-mysql] About packages that depend on mysql-* / mariadb / virtual-mysql-* – in where we find the API-incompatible
libmysqlclient, naming conventions, and more.
At Percona Live Amsterdam recently, the conference expanded beyond just its focus areas of MySQL & its ecosystem and MongoDB to also include PostgreSQL and other open source databases (just look at the recent poll). The event was a sold out success.
This will continue for Percona Live Santa Clara 2017, happening April 24-27 2017 – and the call for papers is open till November 13 2016, so what are you waiting for? Submit already!
I am on the conference committee and am looking forward to making the best program possible. Looking forward to your submissions!
I have never been to Sofia, Bulgaria till this past February 2016, and boy did I enjoy myself. I visited the Bulgaria Web Summit and spoke there amongst many others. A few notes:
- Almost 800 people (so more than last year); hence the event was sold out
- Missed the RocksDB talk due to the massive Q&A session that went on afterwards.
- Very interesting messaging
- LinvoDB (embeddable MongoDB alternative) — LinvoDB / www.strem.io
- Use case: < 1 million objects (indexes are in memory using a binary search tree; so don’t use it for more). HTML5/Electron/NW.js. Best used with AngularJS/React and maybe Meteor. Can also use NativeScript or React Native. You can use it with node.js but its not recommended for server use cases.
- Works with SQLite or LevelDB (why not RocksDB yet?). Can also use with IndexedDB/LocalStorage
- Implemented almost entirely the MongoDB query language. Gives you automatic indexes.
- FTS in memory (linvodb-fts) – uses trie/metaphone modules for node.js. Can also do p2p replication, persistent indexes, compound indexes
I enjoyed speaking about MariaDB Server as always, and its clear that many people had a lot of questions about it. Slides. Video. It was tweeted that I had to answer questions for about as long as my talk, afterwards, and it was true :)
I got to meet Robert Nyman at the social event (small world, since he works at the office where Jonas of ex-MySQL fame does). Also met someone very interested in contributing to InfiniDB. It was nice having a beer with my current colleague Salle too. And speaking to the track moderator, Alexander Todorov was also a highlight – since we had many common topics, and he does an amazing amount of work around automation and QA. His blog is worth a read.
While being on the committee for the FOSDEM MySQL & friends devroom, I didn’t speak at that devroom (instead I spoke at the distributions devroom). But when I had time to pop in, I did take some notes on sessions that were interesting to me, so here are the notes. I really did enjoy Yoshinori Matsunobu’s session (out of the devroom) on RocksDB and MyRocks and I highly recommend you to watch the video as the notes can’t be very complete without the great explanation available in the slide deck. Anyway there are videos from the MySQL and friends devroom.
MySQL & Friends Devroom
MySQL Group Replication or how good theory gets into better practice – Tiago Jorge
- Multi-master update everywhere with built-in automatic distributed recovery, conflict detection and group membership
- Group replication added 3
- If a server leaves the group, the others will be automatically informed (either via a crash or if you execute
STOP GROUP REPLICATION)
- Cloud friendly, and it is self-healing. Integrated with server core via a well-defined API. GTIDs, row-based replication,
PERFORMANCE_SCHEMA. Works with MySQL Router as well.
- Multi-master update everywhere. Conflicts will be detected and dealt with, via the first committer wins rule. Any 2 transactions on different servers can write to the same tuple.
- labs.mysql.com / mysqlhighavailability.com
- Q: When a node leaves a group, will it still accept writes? A: If you leave voluntarily, it can still accept writes as a regular MySQL server (this needs to be checked)
- Online DDL is not supported
- Checkout the video
ANALYZE for statements – Sergei Petrunia
- a lot like EXPLAIN ANALYZE (in PostgreSQL) or PLAN_STATISTICS (in Oracle)
- Looks like explain output with execution statistics
- slides and video
Preparse Query Rewrite Plugins – Sveta Smirnova / Martin Hansson
- Query rewwriting with a proxy might be too complex, so they thought of doing it inside the server. There is a pre-parse (string-to-string) and a post-parse (parse tree) API. Pre-parse: low overhead, but no structure. Post-parse: retains structure, but requires re-parsing (no destructive editing), need to traverse parse tree and will only work on select statements
- Query rewrite API builds on top of teh Audit API, and then you’ve got the pre-parse/post-parse APIs on the top that call out to the plugins
Fedora by the Numbers – Remy DeCausemaker
MyRocks: RocksDB Storage Engine for MySQL (LSM Databases at Facebook) – Yoshinori Matsunobu
- SSD/Flash is getting affordable but MLC Flash is still expensive. HDD has large capacity but limited IOPS (reducing rw IOPS is very important and reducing write is harder). SSD/Flash has great read iops but limited space and write endurance (reducing space here is higher priority)
- Punch hole compression in 5.7, it is aligned to the sector size of your device. Flash device is basically 4KB. Not 512 bytes. So you’re basically wasting a lot of space and the compression is inefficient
- LSM tends to have a read penalty compared to B-Tree, like InnoDB. So a good way to reduce the read penalty is to use a Bloom Filter (check key may exist or not without reading data, and skipping read i/o if it definitely does not exist)
- Another penalty is for delete. It puts them into tombstones. So there is the workaround called SingleDelete.
- LSMs are ideal for write heavy applications
- Similar features as InnoDB, transactions: atomicity, MVCC/non-locking consistent read, read committed repeatable read (PostgreSQL-style), Crash safe slave and master. It also has online backup (logical backup by mysqldump and binary backup by myrocks_hotbackup).
- Much smaller space and write amplification compared to InnoDB
- Reverse order index (Reverse Column Family). SingleDelete. Prefix bloom filter. Mem-comparable keys when using case sensitive collations. Optimizer statistics for diving into pages.
- RocksDB is great for scanning forward but ORDER BY DESC queries are slow, hence they use reverse column families to make descending scan a lot faster
- watch the video
I have a few speaking engagements coming up in April 2016, and I hope to see you at some of these events. I’m always available to talk shop (opensource, MariaDB Server, MySQL, etc.) so looking forward to saying hi.
- A short talk at the MariaDB Berlin Meetup on April 12 2016 – this should be fun if you’re in Berlin as you’ll see many people from the MariaDB Server and MariaDB MaxScale world talk about what they’re doing for the next releases.
- rootconf.in – April 14-15 2016, tutorial day on 16 – I’ve not been to India since about 2011, so I’m looking forward to this trip to Bangalore (and my first time to a HasGeek event). Getting the email from the conference chair was very nice, and I believe I’m giving a keynote and a tutorial.
- Percona Live Data Performance Conference 2016 – April 18-21 2016 – this is obviously the event for the MySQL ecosystem, and I’m happy to state that I’m giving a tutorial and a talk at this event.
- Open Source Data Centre Conference – April 26-28 2016 – Its been a few years since I’ve been here, but I’m looking forward to presenting to the audience again.
There’s some prep work for some internal presentations and tutorials that I’ll be running in Berlin at the company meeting as well.