Over the past few months I’ve written a couple of posts about dangerous debt of InnoDB Transactional History and about the fact MVCC can be the cause of severe MySQL performance issues. In this post I will cover a related topic – InnoDB Transaction Isolation Modes, their relationship with MVCC (multi-version concurrency control) and how they impact MySQL performance.

The MySQL Manual provides a decent description of transaction isolation modes supported by MySQL – I will not repeat it here but rather focus on performance implications.

SERIALIZABLE – This is the strongest …

The devroom’s schedule is now published on Fosdem’s website.

As you can see, it will be a heavy day full of good content. You can also notice that there won’t be any lunch-time break.

We advise you to then bring your lunch (like a sandwich) with you in the room and eat it between talks or prepare in advance when you will take your break during a talk you don’t want to attend (is it even possible ?).

This blog explains how relay log recovery happens in a scenario where an applier thread (SQL_Thread) is starting for the first time and its starting position is not available for relay log recovery operations. If you are using GTIDs with MASTER_AUTO_POSITION then the following is more or less irrelevant since we then employ a more resilient repositioning scheme. The potential issue described here will also not occur if you have employed crash-safe replication settings, including --sync_master_info=1. With those disclaimers out of the way, let’s proceed.

Background

A crash-safe slave in MySQL …

Our latest eBook, the Ultimate Guide to Building Database Driven Apps with Go, consolidates years of expert advice into one, easy-to-read document. It is the fastest way to learn how to connect to a database from Go programs and use database/sql properly while avoiding many mistakes and ensuring high performance and correct code.

The below table of contents shows the scope of the ebook.

Download the full edition here. Ready, set, Go!

Silly pun, we know. We couldn’t help ourselves.

A while ago I blogged about a potential workaround for MySQL error 1070 that could be used to add a unique constraint with more than 16 columns.

As a reminder here's the error you get when you try to create a unique constraint with more than 16 columns in MySQL:

ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed

The solution I proposed should work, but when I started to implement it I made a couple of changes that merit this follow-up post. One change is fairly generic, and the other is specific to my use case.

The generic change was to only concatenate some of the columns, rather than all of them. Specifically I only need to concatenate N-15 columns. So if I want to have a unique constraint on 20 columns, I can include 15 of those actual columns and then …

MySQL 5.6.22 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there is 1 “Security Note”, 2 “Functionality Changed”, and 5 “Compilation Notes”, all benign, but let me address them:

1. Security Note: The linked OpenSSL library for the MySQL Commercial Server has been updated from version 1.0.1h to version 1.0.1j. Issues fixed in the new version are described at http://www.openssl.org/news/vulnerabilities.html.
2. Functionality Changed: Replication: The variable binlogging_impossible_mode has been renamed binlog_error_action. binlogging_impossible_mode is now deprecated. (Bug #19507567)
3. Functionality Changed: …

MariaDB 5.5.41 was recently released (it is the latest MariaDB 5.5), and is available for download here:

https://downloads.mariadb.org/mariadb/5.5.41/

This is a maintenance release, and so there were not too many changes, *but* please take notice as there are 2 very important bug fixes:

• Bug Fixed: A fix to a serious bug in InnoDB and XtraDB that sometimes could cause a hard lock up of the server (Bug #MDEV-7026)
• Bug Fixed: A fix to unnecessary waits in InnoDB and XtraDB (Bug #MDEV-7100)
• Includes all bugfixes and updates from MySQL 5.5.41 ( …

2014 was a productive year for the MariaDB Foundation.

Here is a list of some of the things MariaDB Foundation employees have
accomplished during 2014:

The 3 full-time MariaDB Foundation developers have worked hard to make MariaDB better:

• Some 260 commits
• Some 25 reviews of code from the MariaDB community.
• Fixed some 170 bugs and new features. For a full list, please check Jira.
• Reported some 160 bugs.

Some of the main new features Foundation developers have worked on in 2014 are:

• Porting and improving MariaDB on IBM Power8.
• Porting Galera to MariaDB 10.1 as a standard feature.
• Query timeouts (MDEV-4427)
• Some coding and reviews of Parallel replication …

I showed in an earlier post how to drop a whole database in a very safe way (no replication lag at all) and that technique is usable to drop a single table too, but cleaning up a table can take hours if not days to finish, so this is not the most comfortable way to do that. We also don’t want to have even a small spike of replication lag, so we need to find an another solution.

How to remove database in a safe way
When you have to drop a large database, you’ll encounter some problems, mainly replication…
Read more
What happens when you issue a DROP TABLE command? The table has to be removed from the table dictionary – which is a fast, atomic operation – and has to be removed from file system too. If you use older version than 5.5.10 you have to calculate with a huge amount of time if your buffer pool is big, because the server will scan through the pages there, checking if anything is in memory from that …

Last month I wrote a blog about the closing of MongoDB ticket SERVER-1240, which brings Collection Level Locking (CLL) to the MMAPV1 storage engine in MongoDB 2.8. In MongoDB 2.6 there is a writer lock at the database level, so each database only allows one writer at a time. In concurrent write workloads, this means that all writers essentially form a single line and do their writes one at a time. In MongoDB 2.8 this lock has been moved to the collection level. Better yet is document level locking, but even though this feature was shown at MongoDB World 2014 it's not going to ship. But it did make for one amazing demo by …