Performance of MySQL Semi-Synchronous Replication Over High Latency Connections

I have seen a few posts on DBA.SE (where I answer a lot of questions) recommending the use of semi-synchronous replication in MySQL 5.5 over a WAN as a way to improve the reliability of replication. My gut reaction was that this is a very bad idea with even a tiny write load, but I wanted to test it out to confirm. Please note that I do not mean to disparage the author of those posts, a user whom I have great respect for.

What is semi-synchronous replication?

The short version is that one slave has to acknowledge receipt of the binary log event before the query returns. The slave doesn’t have to execute it before returning control so it’s still an asynchronous commit. The net effect is that the slave should only miss a maximum of 1 event if the master has a catastrophic failure. It does not improve the reliability of replication itself or prevent data drift.

What about performance, though? Semi-synchronous replication causes the client to block until a slave has acknowledged that it has received the event. On a LAN with sub-millisecond latencies, this should not present much of a problem. But what if there is 85ms of latency between the master and the slave, as is the case between Virginia and California? My hypothesis is that, with 85ms of latency, it is impossible to get better than 11 write queries (INSERT/UPDATE/DELETE) per second – 1000ms / 85ms = 11.7.

Let’s test that out.

I spun up identical m1.small instances in EC2′s us-east-1 and us-west-1 regions using the latest Ubuntu 12.04 LTS AMI from Alestic and installed the latest Percona Server 5.5 from their apt repository.

gpg --keyserver  hkp://keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
gpg -a --export CD2EFD2A | apt-key add -
echo deb http://repo.percona.com/apt precise main > /etc/apt/sources.list.d/percona.list
apt-get update
apt-get install percona-server-server-5.5 libmysqlclient-dev

Although mostly irrelevant to the benchmarks, my.cnf is configured thusly (basically identical to support-files/my-huge.cnf shipped with the distribution):

[mysqld]
port          = 3306
socket          = /var/run/mysql/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8

server-id     = 1
log-bin=mysql-bin
binlog_format=mixed

innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50

Then, I wrote a very simple Ruby script to perform 10k inserts into a table, using the sequel gem, which I love.

apt-get install rubygems
gem install sequel mysql2 --no-rdoc --no-ri
#!/usr/bin/env ruby
# insertperf.rb

require 'logger'
require 'rubygems'
require 'sequel'

logger = Logger.new(STDOUT)
localdb = "inserttest"

db = Sequel.connect( :database => localdb,
                     :adapter  => 'mysql2',
                     :user     => 'root',
                     :logger   => logger )

db["DROP DATABASE IF EXISTS #{localdb}"].all
db["CREATE DATABASE #{localdb}"].all
db["CREATE TABLE IF NOT EXISTS #{localdb}.foo (
  id int unsigned AUTO_INCREMENT PRIMARY KEY,
  text VARCHAR(8)
) ENGINE=InnoDB"].all

n = 10000
t1 = Time.new
n.times do
  value = (0...8).map{65.+(rand(25)).chr}.join
  db["INSERT INTO #{localdb}.foo (text) VALUES (?)", value].insert
end
t2 = Time.new
elapsed = t2-t1
logger.info "Elapsed: #{elapsed} seconds. #{n/elapsed} qps"

With MySQL configured, let’s knock out a few INSERTs into the us-east-1 database, which has no slaves:

# w/ no slaves
...
INFO -- : (0.000179s) INSERT INTO test.foo (text) VALUES ('FKGDLOWD')
...
INFO -- : Elapsed: 9.37364 seconds. 1066.82142689499 qps

My control is roughly 1000 inserts/sec with each query taking less than .2ms.

Then, I set up a traditional, asynchronous MySQL slave on the server in us-west-1 and ran the test again:

# w/ traditional replication
...
INFO -- : (0.000237s) INSERT INTO test.foo (text) VALUES ('CVGAMLXA')
...
INFO -- : Elapsed: 10.601943 seconds. 943.223331798709 qps

Somewhat inexplicably, the performance was slightly worse with the slave attached, but not by much. ~950 inserts/sec

Next is semi-synchronous replication. First, I tested the latency between us-east-1 and us-west-1.

# ping -c 1 184.72.189.235
PING 184.72.189.235 (184.72.189.235) 56(84) bytes of data.
64 bytes from 184.72.189.235: icmp_req=1 ttl=52 time=85.5 ms

Latency between us-east-1 and us-west-1 is 85ms, so I still predict 11 inserts/sec at most, which means my script will take 15 minutes instead of 10 seconds:

I set up semi-synchronous replication like this:

master> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
master> SET GLOBAL rpl_semi_sync_master_enabled = 1;
slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
slave> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
slave> STOP SLAVE; START SLAVE;

I started the script and, as predicted, each insert was taking approximately 85ms. There was a screaming 2 month old in the next room so in the interest of brevity, I reduced the count from 10k to 1k. That should take about 90s:

# w/ semi-sync replication
...
INFO -- : (0.086301s) INSERT INTO test.foo (text) VALUES ('JKIJTUDO')
...
INFO -- : Elapsed: 86.889529 seconds. 11.5088666207409 qps

Just as I suspected – 11 inserts/sec.

In conclusion, the speed of light is a bitch, so don’t enable semi-synchronous replication over wide area or high latency networks.

INSERT, Don’t DELETE

I’ve been working on a data archival project over the last couple weeks and thought it would be interesting to discuss something a bit counter-intuitive. Absolutes are never true, but when getting rid of data, it’s usually more efficient to insert the data being kept into a new table rather than deleting the old data from the existing table.

Here is our example table from the IMDB database.

mysql> show create table title\G
*************************** 1. row ***************************
       Table: title
Create Table: CREATE TABLE `title` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `imdb_index` varchar(12) DEFAULT NULL,
  `kind_id` int(11) NOT NULL,
  `production_year` int(11) DEFAULT NULL,
  `imdb_id` int(11) DEFAULT NULL,
  `phonetic_code` varchar(5) DEFAULT NULL,
  `episode_of_id` int(11) DEFAULT NULL,
  `season_nr` int(11) DEFAULT NULL,
  `episode_nr` int(11) DEFAULT NULL,
  `series_years` varchar(49) DEFAULT NULL,
  `md5sum` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_title` (`title`(10)),
  KEY `idx_pcode` (`phonetic_code`),
  KEY `idx_epof` (`episode_of_id`),
  KEY `idx_md5` (`md5sum`),
  KEY `title_kind_id_exists` (`kind_id`),
  CONSTRAINT `title_episode_of_id_exists` FOREIGN KEY (`episode_of_id`) REFERENCES `title` (`id`),
  CONSTRAINT `title_kind_id_exists` FOREIGN KEY (`kind_id`) REFERENCES `kind_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2172322 DEFAULT CHARSET=latin1

It has about 2.2 million rows

mysql> SELECT COUNT(*) FROM title;
+----------+
| count(*) |
+----------+
|  2172320 |
+----------+
1 row in set (1.35 sec)

To make this a fair test, let’s say that we want to get rid of half the records. In this contrived example, ‘g’ is the median starting letter. So, we’re going to get rid of everything that matches /^[0-9a-gA-G]/. The title column is indexed, so building the result set should be reasonably efficient.

mysql> SELECT COUNT(*) FROM title WHERE title < = 'g';
+----------+
| count(*) |
+----------+
|  1085426 |
+----------+
1 row in set (2.48 sec)

mysql> SELECT COUNT(*) FROM title WHERE title > 'g';
+----------+
| count(*) |
+----------+
|  1086894 |
+----------+
1 row in set (2.60 sec)

We’ll do it my way first and insert the records to keep into a new table:

mysql> CREATE TABLE new_title LIKE title;
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO new_title SELECT * FROM title WHERE title > 'g';
Query OK, 1086894 rows affected (1 min 5.18 sec)
Records: 1086894  Duplicates: 0  Warnings: 0

mysql> RENAME TABLE original_title TO old_title, new_title TO title
Query OK, 0 rows affected (0.08 sec)

mysql> DROP TABLE old_title;
Query OK, 0 rows affected (0.26 sec)

The total time is about 1min 5secs. There are a caveats:

  1. You must be able to stop all writes to the table. This can be done by operating on a slave with the slave threads stopped.
  2. The DROP TABLE statement can take a long time on ext filesystems, though it is nearly instant on xfs.
  3. DROP TABLE can cause stalls on a very active server due to InnoDB buffer pool purging and filesystem activity. Percona Server has a global variable that helps with this, innodb_lazy_drop_table and MySQL 5.6 will implement a similar solution. Ovais from Percona compared the performance of these options in their blog.

Now let’s look at the performance of deleting the unneeded records:

mysql> DELETE FROM title WHERE title < = 'g';
Query OK, 1085426 rows affected (1 min 26.27 sec)

1min, 26secs is a little slower, but not too bad. The thing is, you're probably deleting these records to save some disk space. The table on disk is still the same size.

anders:imdb root# ls -lh title.* 
-rw-rw----  1 _mysql  wheel   8.8K Jun 26 18:09 title.frm
-rw-rw----  1 _mysql  wheel   572M Jun 26 18:23 title.ibd

To reclaim the space, we need to run OPTIMIZE TABLE

mysql> OPTIMIZE TABLE title;
+-------------+----------+----------+-------------------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                          |
+-------------+----------+----------+-------------------------------------------------------------------+
| title       | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| title       | optimize | status   | OK                                                                |
+-------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 min 23.72 sec)

anders:test root# ls -lh title.*
-rw-rw----  1 _mysql  wheel   8.8K Jun 26 18:19 title.frm
-rw-rw----  1 _mysql  wheel   304M Jun 26 18:21 title.ibd

The space is reclaimed, but it took a total of 2min 50s to get to the same point as in the INSERT example which was nearly 3x faster. This is only relevant with innodb_file_per_table set or when using MyISAM. Without innodb_file_per_table, there is no way to reclaim the space without rebuilding the entire database from a mysqldump.

I picked 1/2 of the table to make this a fair example. As the number of records being removed gets smaller, deleting becomes a more efficient way to get rid of the rows. DELETE can also be done online, where the INSERT technique requires some down time.

NOT IN with NULLs in the Subquery

A coworker came to me with a perplexing issue. He wanted to know why these two queries were not returning the same results:

mysql> SELECT COUNT(*) 
    -> FROM parent
    -> WHERE id NOT IN (SELECT parent_id FROM child);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (7.84 sec)
mysql> SELECT COUNT(*)
    -> FROM parent p
    -> WHERE NOT EXISTS(SELECT 1 
    ->                  FROM child c
    ->                  WHERE p.id = c.parent_id);
+----------+
| count(*) |
+----------+
|     5575 |
+----------+
1 row in set (2.95 sec)

At first (and second, and third) glance these two queries look identical. It obviously is an exclusion join and because the MySQL optimizer is what it is, I decided to rewrite it as a LEFT JOIN to see what results came back:

mysql> SELECT COUNT(*) FROM parent p
    -> LEFT JOIN child c ON p.id = c.parent_id
    -> WHERE c.id IS NULL;
+----------+
| COUNT(*) |
+----------+
|     5575 |
+----------+
1 row in set (2.90 sec)

The LEFT JOIN returned the same results as the NOT EXISTS version. Why did the NOT IN query return 0 results? Since it was a beautiful day and I am completely unable to solve problems while sitting at the computer, I went for a walk.

After I returned and changed my thunderstorm-soaked clothes, I had a thought of something…

mysql> SELECT COUNT(*)FROM child WHERE parent_id IS NULL;
+----------+
| count(*) |
+----------+
|     3686 |
+----------+
1 row in set (0.20 sec)

There are NULLs in the pseudo-foreign key (this database does not have explicit foreign key constraints). What if the NULLs are excluded from the dependent subquery in the NOT IN clause?

mysql> SELECT COUNT(*)
    -> FROM parent
    -> WHERE id NOT IN (SELECT parent_id 
    ->                  FROM child 
    ->                  WHERE parent_id IS NOT NULL);
+----------+
| count(*) |
+----------+
|     5575 |
+----------+
1 row in set (7.67 sec)

Sure enough, the NULLs were the issue. But, why? Let’s think about a simpler case with constant values instead of subqueries. These two queries (using the sakila database) are logically equivalent:

mysql> SELECT COUNT(*) FROM actor WHERE actor_id NOT IN (1,2,3,4);
+----------+
| COUNT(*) |
+----------+
|      199 |
+----------+
1 row in set (0.13 sec)

mysql> SELECT COUNT(*) FROM actor 
    -> WHERE actor_id <> 1 
    ->   AND actor_id <> 2
    ->   AND actor_id <> 3
    ->   AND actor_id <> 4;
+----------+
| COUNT(*) |
+----------+
|      199 |
+----------+
1 row in set (0.06 sec)

What if there is a NULL in there?

mysql> SELECT COUNT(*) FROM actor
    -> WHERE actor_id <> 1
    ->   AND actor_id <> 2
    ->   AND actor_id <> 3
    ->   AND actor_id <> 4
    ->   AND actor_id <> NULL ;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

The reason why this returns 0 results is that column <> NULL (or column = NULL, for that matter) always evaluates to NULL. NULL doesn’t equal anything. It is just NULL. TRUE AND TRUE AND TRUE AND NULL always evaluates to NULL. An illustration:

mysql> SELECT TRUE AND FALSE;
+----------------+
| TRUE AND FALSE |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT TRUE AND TRUE;
+---------------+
| TRUE AND TRUE |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT TRUE AND NULL;
+---------------+
| TRUE AND NULL |
+---------------+
|          NULL |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT 1=NULL;
+--------+
| 1=NULL |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

Here’s the final proof of the NULL problem:

mysql> SELECT COUNT(*) FROM actor WHERE actor_id NOT IN (1,2,3,4,NULL);
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

The moral of the story is, if you use NOT IN with a dependent subquery, make sure you exclude NULLs. This problem would also go away if there had been explicit foreign key constraints, which would enforce referential integrity. Additionally, as you can see from the performance differences above, NOT IN with a subquery is horribly optimized in MySQL pre-5.6, so just don’t do this at all on MySQL and use one of the other two derivations instead.

List MySQL Indexes With INFORMATION_SCHEMA

Have you ever wanted to get a list of indexes and their columns for all tables in a MySQL database without having to iterate over SHOW INDEXES FROM ‘[table]‘? Here are a couple ways…

The following query using the INFORMATION_SCHEMA STATISTICS table will work prior to MySQL GA 5.6 and Percona Server 5.5.

SELECT table_name AS `Table`,
       index_name AS `Index`,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE table_schema = 'sakila'
GROUP BY 1,2;

This query uses the INNODB_SYS_TABLES, INNODB_SYS_INDEXES, and INNODB_SYS_FIELDS tables from INFORMATION_SCHEMA and is only available in MySQL 5.6 or Percona Server 5.5. However, it is much much faster than querying the STATISTICS table. It also only shows InnoDB tables.

SELECT t.name AS `Table`,
       i.name AS `Index`,
       GROUP_CONCAT(f.name ORDER BY f.pos) AS `Columns`
FROM information_schema.innodb_sys_tables t 
JOIN information_schema.innodb_sys_indexes i USING (table_id) 
JOIN information_schema.innodb_sys_fields f USING (index_id)
WHERE t.schema = 'sakila'
GROUP BY 1,2;

Assuming that all your tables are InnoDB, both queries will produce identical results. If you have some MyISAM tables in there, only the first query will provide complete results.

+---------------+-----------------------------+--------------------------------------+
| Table         | Index                       | Columns                              |
+---------------+-----------------------------+--------------------------------------+
| actor         | idx_actor_last_name         | last_name                            |
| actor         | PRIMARY                     | actor_id                             |
| address       | idx_fk_city_id              | city_id                              |
| address       | PRIMARY                     | address_id                           |
| category      | PRIMARY                     | category_id                          |
| city          | idx_fk_country_id           | country_id                           |
...
| rental        | rental_date                 | rental_date,inventory_id,customer_id |
| staff         | idx_fk_address_id           | address_id                           |
| staff         | idx_fk_store_id             | store_id                             |
| staff         | PRIMARY                     | staff_id                             |
| store         | idx_fk_address_id           | address_id                           |
| store         | idx_unique_manager          | manager_staff_id                     |
| store         | PRIMARY                     | store_id                             |
+---------------+-----------------------------+--------------------------------------+
42 rows in set (0.04 sec)