Wednesday, July 19, 2017

InnoDB Basics - Compaction: when and when not

This is old news for MySQL/MariaDB expert but people that are starting using InnoDB do not always know that disk space is not automatically released when deleting data from a table.  To explain and demonstrate that, I will take two real-world examples: table1 and table2.

Wednesday, July 5, 2017

Fun with InnoDB Persistent Statistics

Something interesting happened to me in the last days, and it is worth sharing.  I was upgrading MariaDB (MySQL also impacted) to a new major version and mysql_upgrade showed something like this:
[...]
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1062 (23000) at line 586: Duplicate entry 'schema-table_name#P#partition_name_truncated' for key 'PRIMARY'
ERROR 1062 (23000) at line 590: Duplicate entry 'schema-table_name#P#partition_name_truncated' for key 'PRIMARY'
ERROR 1062 (23000) at line 593: Duplicate entry 'schema-table_name#P#partition_name_truncated' for key 'PRIMARY'
FATAL ERROR: Upgrade failed

Monday, May 22, 2017

Better Replication when running both InnoDB and MyRocks (or other Storage-Engines)

Kristian Nielsen is working on a new feature for MariaDB 10.3 and he published very interesting results.  This feature is MDEV-12179: Per-engine mysql.gtid_slave_pos tables.  He writes about replicating twice as fast in the worst case when using two storage engines (InnoDB and MariaRocks in his tests, but could also be InnoDB and TokuDB or TokuDB and MyRocks).  I will let you read all the details on his blog about Improving replication with multiple storage engines.

Why am I posting this here ?  Mostly because I want to share with you that:
  • I am also involved in this project,
  • I am working closely with Kristian on this feature,
  • and that Booking.com is financing Kristian's time on this development.
If you are also interested in this, feel free to comment in the JIRA MDEV, to leave a comment below, or on Kristian's post.

Tuesday, April 18, 2017

Sunday, April 16, 2017

Booking.com talks at Percona Live Santa Clara 2017

In a week, me and some Booking.com colleagues will be in Santa Clara for Percona Live.

Booking.com is sponsoring the conference and we will be present at the Monday Evening Reception.  You do not need a tutorial pass to attend the dinner (even if it is on the tutorial day): any valid pass will do.  If you do not have your ticket yet, it is time to register (you can use the discount code “SeeMeSpeak” for a 10% discount on the registration fees).

Tuesday, April 11, 2017

Many thanks Oracle for implementing RESET MASTER TO

MySQL 8.0.1 is out and it includes an implementation of my feature request (Bug #77438).  This extension to RESET MASTER allows to simplify master promotion with Binlog Servers.  Let's see how it works:
# mysql -N <<< "SHOW MASTER STATUS"
binlog.027892   3006935
# mysql -N <<< "RESET MASTER TO 12345; DO sleep(rand()*10); SHOW MASTER STATUS"
binlog.012345   92773
# mysql -N <<< "RESET MASTER TO 12345678; DO sleep(rand()*10); SHOW MASTER STATUS"
binlog.12345678 24795
# mysql -N <<< "RESET MASTER TO 1234567890; DO sleep(rand()*10); SHOW MASTER STATUS"
binlog.1234567890       13987
# mysql -N <<< "RESET MASTER TO 12345678901; DO sleep(rand()*10); SHOW MASTER STATUS"
ERROR 3567 (HY000) at line 1: The requested value '12345678901' for the next
binary log index is out of range. Please use a value between '1' and '2147483647'.
# mysql -N <<< "RESET MASTER TO $RANDOM; DO sleep(rand()*10); SHOW MASTER STATUS"
binlog.013529   89880
# mysql -N <<< "RESET MASTER TO $RANDOM; DO sleep(rand()*10); SHOW MASTER STATUS"
binlog.000831   22961
# mysql -N <<< "RESET MASTER TO $RANDOM; DO sleep(rand()*10); SHOW MASTER STATUS"
binlog.023089   107764
# mysql -N <<< "RESET MASTER TO $RANDOM; DO sleep(rand()*10); SHOW MASTER STATUS"
binlog.003433   67903
Many thanks Oracle for implementing my feature request, and a special mention to Daniël van Eeden for providing a patch in the bug report.