A few days ago I wrote about MySQL performance implications of InnoDB isolation modes and I touched briefly upon the bizarre performance regression I found with InnoDB handling a large amount of versions for a single row. Today I wanted to look a bit deeper into the problem, which I also filed as a bug.

First I validated in which conditions the problem happens. It seems to happen only in REPEATABLE-READ isolation mode and only in case there is some hot rows which get many row versions during a benchmark run. For example the problem does NOT happen if I run sysbench with “uniform” distribution.

In terms of concurrent selects it also seems to require some very special conditions – you need to have the connection to let some …

On a recent consulting engagement, the PSCE team were charged with what can be considered a fairly common task of synchronising tables between master and slave in MySQL Replication. On this occasion the  schema contained both foreign key constraints and triggers, this post describes how we avoided the potential problems related to such an operation.

The process to synchronise tables in MySQL is to first identify the differences between tables and then execute queries which bring those tables into a consistent state. The first part of the process can be handled by the pt-table-checksum tool, which steps through the table analysing sets of rows (chunks) and recording a checksum value. Then taking advantage of replication, the same process occurs on each of the slaves and the checksums can then be compared. Once the entire table has been processed, a second tool pt-table-sync can be used …

MariaDB 10.1.2 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.1.2/

This is the third alpha release of MariaDB 10.1, so there are still a lot of new changes, functionalities added, defaults changed, and many bugs fixed (I counted 117, which is *way* down from the 637 fixed in 10.1.1). Since it’s alpha, I’ll only cover the major changes and additions, and omit covering general bug fixes (feel free to browse them all here).

To me, these are the highlights of the new features:

• –mysql56-temporal-format option to use the MySQL-5.6 low level formats to store …

Fri, 2015-01-16 15:32guillaumelefranc

This new blog series will be about how to easily automate common MariaDB administration tasks using Ansible. We will showcase how to automatically install and configure software such as MariaDB server, MariaDB Galera and MaxScale with ease in reproducible environments.

What’s Ansible?

Ansible is an IT automation tool. It can configure systems, deploy software, and orchestrate more advanced IT tasks such as continuous deployments or zero downtime rolling updates.

Ansible’s goals are foremost those of simplicity and maximum ease of use. It also has a strong focus on security and reliability, featuring a minimum of moving parts, usage of OpenSSH for transport (with an accelerated socket mode and pull modes as alternatives), and a language that is designed around auditability by humans – even those not familiar with the program (source: Ansible …

In the MySQL Labs version of MySQL version 5.7, there is a new HTTP plugin. The HTTP plugin documentation from the labs site provides this information (from MySQL Labs):

The HTTP Plugin for MySQL adds HTTP(S) interfaces to MySQL. Clients can use the HTTP respectively HTTPS (SSL) protocol to query data stored in MySQL. The query language is SQL but other, simpler interfaces exist. All data is serialized as JSON. This version of MySQL Server HTTP Plugin is a Labs release, which means it’s at an early development stage. It contains several known bugs and limitation, and is meant primarily to give you a rough idea how this plugin will look some day. Likewise, the user API is anything but finalized. Be aware it will change in many respects.

In …

In the MySQL Team, we are currently evaluating potential changes to the default server configuration for MySQL 5.7. For more context, please see my earlier post here.

One of the specific changes we would like to make is with the following three related replication and InnoDB settings:

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 …

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