This blog post proposes modifications to the MySQL semi-sync replication process in order to improve the overall consistency and resilience of the system.

This is based on my previous blog post on Flexible Paxos and the related paper Flexible Paxos: Quorum intersection revisited by Howard, Malkhi and Spiegelman.


Background Durability requirements have changed over the last few years. Traditional systems considered their data Durable if it was written to disk. However, this is not acceptable any more. In today’s world, data is considered durable only if it has been replicated to more than one machine.

MySQL is one of the few databases that has made an effort to satisfy this form of durability. It supported …

In this post, I’ll point you to MyRocks Docker images with binaries, allowing you to install and play with the software.

During the @Scale conference, Facebook announced that MyRocks is mature enough that it has been installed on 5% of Facebook’s MySQL slaves. This has saved 50% of the space on these slaves, which allows them to decrease the number of servers by half. Check out the announcement here:  https://code.facebook.com/posts/190251048047090/myrocks-a-space-and-write-optimized-mysql-database/

Those are pretty impressive numbers, so I decided to take a serious look at MyRocks. The biggest showstopper is usually binary availability, since Facebook only provides the source …

Introduction

This is the final installment of a five part blog series to explore InnoDB internals by looking at the related tunable system variables. In this section we’re going to cover variables that relate to enforcing data consistency, and how index statistics are handled and stored.

Just like previous sections, I would like to emphasize something that was written in part one of this blog post series.

“I should note that while tuning recommendations are provided, this objective of this blog post series was NOT meant to be a tuning primer, but instead to explore the mechanics that each variable interacts with. As such I would like to advise against reading this guide and trying to fine tune all of the available InnoDB variables. System variable tuning is an exercise in diminishing returns, the most benefit you’ll get out of tuning your MySQL server will occur within the first 15 minutes of configuration. In …

This post is about partitioning a MySQL table by year by using range partition type. This post does not deal with the theories on partition, if you would like to understand the complete partitioning methods and types visit the official MySQL documentation. In this post we are directly focus on the implementation steps, assuming that we have knew the basics of MySQL partitioning types and its usages.

Step 1: Create a test database:CREATE DATABASE partitiontest;USE partitiontest;

Step 2: Create a test table with partition definition:CREATE TABLE parttable (rollno INT, name VARCHAR(50), birthdate …

Oracle OpenWorld is right around the corner and now is the time to plan your agenda! My Schedule enables you to effectively plan your conference and reserve you seat in sessions. You’ll also get notified of potential updates. Last year we had to turn down folks for a number of MySQL sessions so it does make sense to book your seat in advance...

Go ahead and start by adding the General Session "MySQL’s State of the Dolphin & Customer Experiences" to your agenda now! Rich Mason, General Manager of the MySQL Global Business Unit, and Tomas Ulin, Vice President of MySQL Engineering, will review Oracle’s latest …

pt-table-checksum perfectly solves problem of checking if master and its slaves are in-sync. It answers the question “Are the slaves consistent with the master?”. However if the answer is “No” pt-table-checksum doesn’t actually tell what exactly is different. [crayon-59f9aff300dfa915077909/] pt-table-sync may give a partial answer. It can print SQL statements to sync the replication cluster. Reading the SQL […]

The post Show differences found by pt-table-checksum appeared first on TwinDB.

Since PHP 7.0 has been released there's more attention on scalar types. Keeping types for data from within your application is relatively simple. But when talking to external systems, like a database things aren't always as one eventually might initially expect.

For MySQL the type we see -- in the first approximation -- is defined by the network protocol. The MySQL network protocol by default converts all data into strings. So if we fetch an integer from the database and use PHP 7's typing feature we get an error:

<?php
declare(strict_types=1);

function getInteger() : int {
  $mysqli = new mysqli(...);
  return $mysqli->query("SELECT 1")->fetch_row()[0];
}

var_dump(getInteger());
?>

Fatal error: Uncaught TypeError: Return value of getInteger() must be of the type integer, string returned in t.php:6

Of course the solution is easy: Either we cast ourselves or we disable the strict mode and PHP will …

In this blog, I will provide answers to the Q & A for the InnoDB Troubleshooting webinar.

First, I want to thank everybody for attending the August 11 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: What’s a good speed for buffer pool speed/size for maximum query performance?

A: I am sorry, I don’t quite understand the question. InnoDB buffer pool is an in-memory buffer. In an ideal case, your whole active dataset (rows that are accessed by application regularly) should be in the buffer pool. There is a good …

MHA (Master High Availability Manager and tools for MySQL) is one of the most important pieces of our managed services. When properly set up, it can check replication health, move writer and reader virtual IPs, perform failovers, and have its output constantly monitored by Nagios. Is it easy to deploy and follows the KISS (Keep It Simple, Stupid) philosophy that I love so much.

This blog post is a quick start guide to try it out and play with it in your own testing environment. I assume that you already know how to install software, deal with SSH keys and setup replication in MySQL. The post just covers MHA configuration.

Testing environment

Taken from /etc/hosts

192.168.1.116      mysql-server1
192.168.1.117   mysql-server2
192.168.1.118   mysql-server3
192.168.1.119   mha-manager

mysql-server1: Our master MySQL …

Introduction

This is part four of a five part blog series to explore InnoDB internals by looking at the related tunable system variables. In this section we’re going to explore the mechanics that impact CPU resourcing and how InnoDB handles concurrent threads. You’ll notice that a lot of the variables covered in section relate to features that are now disabled but were a lot more prevalent in previous versions of MySQL, particularly those that were released at a time where system context switching had a greater cost than it does today. These variables are still worth discussing as you may run into older systems that utilize these mechanics, and there are even modern systems I’ve worked with that have implemented these features to tune performance to its highest potential.

Just like previous sections, I would like to emphasize something that was written in part one of this blog post series.

“I should note that …