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 PERFORMANCE_SCHEMA tables
• 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

• martin.hansson@oracle.com
• 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
• video

Fedora by the Numbers – Remy DeCausemaker

• Over 200,000 downloads a month for Fedora (at least this is where they could installer downloads). Upgrades don’t need an installer download though!
• https://github.com/fedora-infra/fedora-stats-tools

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

Congratulations to the entire team at Red Hat, for the release of Red Hat Enterprise Linux 7 (RHEL7). The release notes have something important, under Web Servers & Services:

MariaDB 5.5

MariaDB is the default implementation of MySQL in Red Hat Enterprise Linux 7. MariaDB is a community-developed fork of the MySQL database project, and provides a replacement for MySQL. MariaDB preserves API and ABI compatibility with MySQL and adds several new features; for example, a non-blocking client API library, the Aria and XtraDB storage engines with enhanced performance, better server status variables, and enhanced replication.

Detailed information about MariaDB can be found at https://mariadb.com/kb/en/what-is-mariadb-55/.

This is a huge improvement over MySQL 5.1.73 currently shipping in RHEL6. I’m really looking forward to welcome more MariaDB users. Remember if you are looking for information, find it at the Knowledge Base. If you’ve found a bug, report it at Jira (upstream) or Bugzilla (Red Hat). If you want to chat with friendly developers and users, hop on over to #maria on irc.freenode.net. And don’t forget we have some populated mailing lists: maria-discuss and maria-developers.

I currently maintain a host of machines in the RPM world from CentOS 5, CentOS 6, and now Fedora 20 (in preparation for CentOS 7). Being part of an upstream project that ships on many platforms, it is in my interest to make sure that all things “just work” on the myriad of platforms out there that we support.

In CentOS 6.5, changing a hostname is a process of editing /etc/sysconfig/network, making sure the /etc/hosts file is sensible and fixing it with the hostname command. Rackspace has a good support article on this.

This is the world before systemd. Now in Fedora 20, this muscle memory goes away in favour of using hostnamectl (docs from the excellent System Administrator’s Guide). Now all I have to do is: sudo hostnamectl set-hostname <newhostname>.

I see a whole host of feature requests with regards to MariaDB & systemd as well as a couple for MySQL. I’ll be spending some time on this in the near future.

(and in other news, shutdown doesn’t understand the -f switch – invalid option code – this was to skip fsck on reboot – still valid in CentOS 5, accepted input in CentOS 6, but unlikely for CentOS 7.)

Today I downloaded Fedora 19 alpha to give it a spin. Some quick notes.

You can get MySQL by asking for the package community-mysql-server. This is 5.5.31. If you ask for stock “mysql” (i.e. yum install mysql-server), you automatically get MariaDB 5.5.30 (mariadb-server).

Fedora 19 runs systemd, so there is no longer /etc/init.d/mysql to start/stop/restart. So just do systemctl enable mysqld.service. Then perform: systemctl start mysqld.service. Replace start with: stop/status too. You can disable it too if you want.

MariaDB 10.0.2 compiles cleanly on Fedora 19 with gcc-4.8. Just perform: yum install bzr gzip tar gcc gcc-c++ make libtool bison ncurses-devel zlib-devel automake autoconf cmake. Get the source code (I just downloaded it). Do BUILD/compile-pentium64-max. Wait. Run make test. Enjoy. Refer to build environment setup, generic build instructions.

If running in a VM, set aside 15GB to ensure you always have sufficient space (I personally use 20GB as I like to test various upgrade scenarios too.

John Lilly, CEO of Mozilla, shares some insights and thoughts on Mozilla, and its a most interesting presentation to go through. The insights are (drizzled with some of my comments):

1. Superior Products Matter – Without excellent experience and utility, the rest is meaningless. This is true, even with MySQL – our aims and values have always been performance, reliability and ease of use.
2. Push (most) decision-making to the edges – I understand that as make sure your community has a significant voice (kind of like Wikipedia’s anyone edits policy, but there’s patrolling). He also suggests that on a regular basis, you need to have surprising innovation – things that blow people’s minds. In Mozilla’s case, there are a set of core values that everyone agrees too; decision making is with the module owners (very much like how the Linux kernel, tends to run), after all, groups have different ways of working. Mozilla has decision makers, that are even outside the “official” organisation – i.e. community has a voice. And communication, is key.
3. Communication will happen in every possible way (so make sure it’s reusable) – this means via Wikis, blogs, the bug tracker, IRC, forums/newsgroups, mailing lists, audio, video, Skype chat, real-life get-togethers, and probably more. Writing notes, and sharing them, might be useful – I’ve found that the Mozilla Weekly Progress Reports on Planet Mozilla (and especially from Zak Greant) to be really useful. I’m thinking of something similar, in the MySQL (and other Sun open source communities) scope. A lot of decisions tend to be taken up on IRC, and people go on hacking on stuff, without writing documentation (worklogs/blueprints), or consulting with the mailing lists – I guess we all have communication improvements in us.
4. Make it easy for your community to do the important things – Here the highlights are SpreadFirefox, Mozilla QA, localisation and more. A focus “to help others do more” should be the mantra of every community! I see it as very easy to translate Drizzle now, that its on Launchpad, but its not the same with MySQL. Translation, documentation, non-code related tasks tend to increase community contributions – though, what do you do when you already have an excellent manual?
5. Surprise is overrated – John suggests that surprise is the opposite of engagement, which is true – no one likes surprises, and everyone wants to feel they’re important and had a role to play when something has happened. The “inner circle” needs more participation. I remember back in the days of Red Hat Linux to Fedora… there was something called the “Fedora Merge” group, and this allowed externals to provide significant decisions towards the direction of the Fedora Project. This was eventually eclipsed by fedora-maintainers, and the various boards like FESCO, and so on. As a participant in the Merge group, I felt like I had a voice, and was part of the “cabal” (there is no cabal), or the inner circle, so to speak – decisions I made, mattered. The inner circle grew, so that everyone (a maintainer, i.e. a person who “deserved” a voice) could feel included. Similar things happened for documentation, marketing, and so on, with various members and boards.
6. Communities are not markets: members are citizens – John stats that citizens are more than consumers, bystanders and stake-holders – we are all citizens in the community (whether you’re a paid staff member, or an external). The best citizens even challenge the status quo, propose improvements and make the conversation richer – I think we have this, via Planet MySQL. The question though is, are we as Sun, listening to the citizens?
7. The key is the art of figuring out whether & how to apply each of these ideas – John suggests experimenting, trying new things, and then measuring the reaction.

Of course, back to point #6, engaged citizens are noisy is highly true. But the old adage of people complaining because they care, is probably a good thing to remember. Expect noise, demands, threats, contradictions, and more. You can’t please everyone in a healthy community, but they will help you make decisions.

A most interesting presentation, and there’s a lot to learn from Mozilla, for other communities to apply.

So, you want to compile Workbench for Linux, on Fedora 9. You need to install the following packages:

autoconf automake libtool libzip-devel libxml2-devel libsigc++20-devel libglade2-devel gtkmm24-devel mesa-libGLU-devel mysql-libs mysql mysql-devel uuid-devel lua-devel glitz-devel glitz-glx-devel pixman-devel pcre-devel libgnome-devel gtk+-devel pango-devel cairo

I feel I’m being too liberal with dependencies, but I’m not about to strip it, I just want to get it working first :)

You need to have ctemplate and ctemplate-devel installed from updates-testing-newkey (relevant koji build log).

By default, configure.in in Workbench looks for “google-ctemplate”, as opposed to just “ctemplate” as Fedora calls it. You can fix this (easy), or “cheat” – in /usr/local/include you can do sudo ln -s /usr/include/ctemplate google. Take your pick.

Now for the fun. Fedora includes gcc-4.3. Ubuntu ships gcc-4.2. Let’s just say gcc-4.3 got stricter (its C++ header dependency streamlining related). So strict, they have a useful porting to gcc-4.3 guide. Just FYI, I believe OpenSUSE is also shipping a more modern GCC (I expect the next Ubuntu release in the coming month to do the same).

So, start getting used to doing the following in files that lead the compiler to fail:

• #include <cstring>
• #include <algorithm>
• #include <memory>
• #include <climits>
• #include <cstdlib>

This will help get rid of error messages like: error: 'memset' was not declared in this scope. This stuff is already fixed in trunk (or the patch is being applied).

You will need to pick up cairo from source. Why? Because --enable-glitz isn’t on by default in the RPM spec file (similar with Ubuntu). The glitz backend is apparently still a release preview and doesn’t fully work yet. (I have spoken to Alfredo, and he says he’s just removed the requirement on glitz in trunk).

Now, once you’ve built cairo with glitz, before running ./autogen.sh, do: export PKGCONFIG_PATH=/usr/local/lib/pkgconfig. Then go ahead and make it.

There are still plenty of warnings that spew by. I have this vague idea that once Workbench for Linux sits on Launchpad, we’ll run a janitorial tree that fixes warnings in the code. A great way to garner community contributions!

Anyway, a lot of this is either fixed or patches will be applied to trunk. I expect a mysql-workbench-5.1.2-alpha out sooner than you think to eclipse 5.1.1-alpha. Again, superb work Alfredo (and MikeZ and the rest of the GUI team).