Recently a colleague of mine ask me to provide a simple example on how ProxySQL can perform sharding.
ProxySQL is a very powerful platform that allow us to manipulate and manage our connections and query in a simple but effective way.
In this article I will show you how.

{autotoc enabled=yes}

Before starting is better to clarify some basic concepts.

ProxySQL organize its internal set of servers in Host Groups (HG), each HG can be associate to users and to Query Rules (QR).

Each QR can be final (apply = 1) or can let ProxySQL continue to parse other QR.

A QR can be a rewrite action, or can be a simple match, it can have a specific target HG, or be generic, finally QR are defined using regex.

You can see QR as a sequence of filters and transformation that you can arrange as you like.

These simple basic rules give us enormous flexibility, and allow us to create very simple actions, like a simple query re-write or very complex chains that could see dozens of QR concatenated.

Documentation can be found here

The information related to HG or QR is easily accessible using the the ProxySQL administrator interface, in the tables mysql_servers, mysql_query_rules and stats.stats_mysql_query_rules; the last one allow us to evaluate if and how the rule(s) is used.

About sharding, what ProxySQL can do for us to achieve what we need in a (relatively easy) way?

Some people/company include sharding logic in the application, and use multiple connection to reach the different targets, or have some logic to split the load across several schemas/tables.

ProxySQL allow us to simplify the way connectivity and query distribution is suppose to work reading data in the query or accepting HINTS.

No matter which kind of requirements the sharding exercise can be summarize in few different categories.

•  By split the data inside the same container (like having a shard by State where each State is a schema)
•  By physical data location (this can have multiple mysql servers in the same room, as well as having them geographically distributed)
•  Combination of the two, where I do split by state using a server dedicated and again split by schema/table by whatever (say by gender)

In the following examples I will show how to use ProxySQL to cover the three different scenario defined above and a bit more.

The example below will report text from the Admin ProxySQL interface and from MySQL console.I will mark each one as follow:

• Mc for MySQL console
• Pa for ProxySQL Admin

Please note that mysql console MUST use the -c flag to pass the comments in the query. This because the default behaviour, in mysql console, is to remove the comments.

I am going to illustrate procedures that you can replicate on your laptop.

This because I want you to test directly the ProxySQL functionalities.

For the example describe below I have used PrxySQL v1.2.2 that is going to become the master in few days.

You can download it from :

1. git clone https://github.com/sysown/proxysql.git
2. git checkout v1.2.2
3. Then to compile :
4. cd make
5. make install

If you need full instructions on how to install and configure ProxySQL than read here and here

Finally you need to have the WORLD test db loaded, world test DB can be found here

First example/exercise is :

Shard inside the same MySQL Server using 3 different schemas split by continent.

Obviously you can have any number of shards and number of relative schemas.

What is relevant here is to demonstrate how traffic can be redirect to different targets (schemas) maintaining the same structure (tables).

This discriminating the target on the base of some relevant information in the Data or pass by the application.

Ok let us roll the ball.

Having :

[Mc]
+---------------+-------------+
| Continent     | count(Code) |
+---------------+-------------+
| Asia          |          51 | <--
| Europe        |          46 | <--
| North America |          37 | 
| Africa        |          58 | <-- 
| Oceania       |          28 |
| Antarctica    |           5 |
| South America |          14 |
+---------------+-------------+

For this exercise you can use single host or multiple servers in replica.

Summarizing you will need:

• 3 hosts: 192.168.1.[5-6-7] (only one needed now but the others are for future use)
• 3 schemas: Continent X + world schema
• 1 user : user_shardRW
• 3 hostgroups: 10, 20, 30 (for future use)

We will create the following Schemas Asia, Africa, Europe first.

[Mc]
CREATE schema [Asia|Europe|Africa];
CREATE TABLE Asia.City AS SELECT a.* FROM  world.City a JOIN Country ON a.CountryCode = Country.code WHERE Continent='Asia' ;
CREATE TABLE Europe.City AS SELECT a.* FROM  world.City a JOIN Country ON a.CountryCode = Country.code WHERE Continent='Europe' ;
CREATE TABLE Africa.City AS SELECT a.* FROM  world.City a JOIN Country ON a.CountryCode = Country.code WHERE Continent='Africa' ;
 
CREATE TABLE Asia.Country AS SELECT * FROM  world.Country WHERE Continent='Asia' ;
CREATE TABLE Europe.Country AS SELECT * FROM  world.Country WHERE Continent='Europe' ;
CREATE TABLE Africa.Country AS SELECT * FROM  world.Country  WHERE Continent='Africa' ;

Create the user:

[Mc]
GRANT ALL ON *.* TO user_shardRW@'%' IDENTIFIED BY 'test';

Now let us start to configure the ProxySQL:

[Pa]
INSERT INTO mysql_users (username,password,active,default_hostgroup,default_schema) VALUES ('user_shardRW','test',1,10,'test_shard1');
LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS TO DISK;
 
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.5',10,3306,100);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.6',20,3306,100);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.1.7',30,3306,100);
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

With this we have defined the User, the servers and the Host groups.

Let us start to define the logic with the query rules:

[Pa]

DELETE FROM mysql_query_rules WHERE rule_id > 30;
INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (31,1,'user_shardRW',"^SELECT\s*(.*)\s*from\s*world.(\S*)\s(.*).*Continent='(\S*)'\s*(\s*.*)$","SELECT \1 from \4.\2 WHERE 1=1 \5",1);
LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;

I am now going to query the master (or a single node) but I am expecting ProxySQL to redirect the query to the right shard catching the value of the Continent.

[Mc]
 SELECT name,population FROM world.City  WHERE Continent='Europe' AND CountryCode='ITA' ORDER BY population DESC LIMIT 1;
+------+------------+
| name | population |
+------+------------+
| Roma |    2643581 |
+------+------------+

Well you can say ... "hey you are querying the schema world, of course you get back the correct data".

But this is not what had really happened, ProxySQL did not query the schema world but the schema Europe.

Let see the details:

[Pa]
SELECT * FROM stats_mysql_query_digest;
Original    :SELECT name,population FROM world.City  WHERE Continent='Europe' AND CountryCode='ITA' ORDER BY population DESC LIMIT 1;
Transformed :SELECT name,population FROM Europe.City WHERE ?=? AND CountryCode=? ORDER BY population DESC LIMIT ?

Let me explain what happened.
Rule 31 in ProxySQL will take all the FIELDS we will pass in the query, it will catch the CONTINENT in the where clause, it will take any condition after the WHERE and it will reorganize the query all using the RegEx.

Does this works for any table in the sharded schemas
Of course  it does.
A query like:

SELECT name,population FROM world.Country WHERE Continent='Asia' ;

Will be transformed into: 

SELECT name,population FROM Asia.Country WHERE ?=?

[Mc]
+----------------------+------------+
| name                 | population |
+----------------------+------------+
| Afghanistan          |   22720000 |
| United Arab Emirates |    2441000 |
| Armenia              |    3520000 |
<snip ...>
| Vietnam              |   79832000 |
| Yemen                |   18112000 |
+----------------------+------------+

Another possible a approach to instruct ProxySQL to shard is:

Pass a hint inside a comment.

Let see how.

First let me disable the rule I have just insert, this is not really needed but so you can see how :)

[Pa]
mysql> UPDATE mysql_query_rules SET active=0 WHERE rule_id=31;
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.00 sec)

Done.

Now what I want to have is that *ANY* query that contains comment /* continent=X */ should go to the continent X schema, same server.

To do so, I will instruct ProxySQL to replace any reference to the world schema inside the the query I am going to submit.

[Pa]
DELETE FROM mysql_query_rules WHERE rule_id IN (31,33,34,35,36);
INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply,FlagOUT,FlagIN) VALUES (31,1,'user_shardRW',"\S*\s*\/\*\s*continent=.*Asia\s*\*.*",NULL,0,23,0);
INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply,FlagIN,FlagOUT) VALUES (32,1,'user_shardRW','world.','Asia.',0,23,23);
INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply,FlagOUT,FlagIN) VALUES (33,1,'user_shardRW',"\S*\s*\/\*\s*continent=.*Europe\s*\*.*",NULL,0,25,0);
INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply,FlagIN,FlagOUT) VALUES (34,1,'user_shardRW','world.','Europe.',0,25,25);
INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply,FlagOUT,FlagIN) VALUES (35,1,'user_shardRW',"\S*\s*\/\*\s*continent=.*Africa\s*\*.*",NULL,0,24,0);
INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply,FlagIN,FlagOUT) VALUES (36,1,'user_shardRW','world.','Africa.',0,24,24);
LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;

How this works?

I have defined mainly to concatenated rules.

The first capture the incoming query that contains the desired value (like continent = Asia).

If the match is there ProxySQL will exit that action, but while doing so it will read the Apply field and if Apply is 0 it will read the FlagOUT value. At this point it will go to the first rule (in sequence) that has the value of FlagIN equal to the FlagOUT.

The second rule will get the request and will replace the value of world with the one I have define.

In short it will replace whatever is in the match_pattern with the value that is in the replace_pattern.
Now what happens here is that ProxySQL implement the Re2 google library for RegEx.

Re2 is very fast but has some limitations, like it does NOT support (at the time of the writing) the flag option g.

In other words if I have a select with many tables and as such several "world." Re2 will replace ONLY the first instance.

As such a query like:

SELECT /* continent=Europe */ * FROM world.Country JOIN world.City ON world.City.CountryCode=world.Country.Code WHERE Country.code='ITA' ;

Will be transformed into :

SELECT /* continent=Europe */ * FROM Europe.Country JOIN world.City ON world.City.CountryCode=world.Country.Code WHERE Country.code='ITA' ;

And fail.

The other day with Rene' we were discussing how to solve this given the lack of implementation in Re2. Finally we had opted for recursive actions.

What this means?

It means that ProxySQL from v1.2.2 now has a new functionality that allow recursive calls to a Query Rule, the maximum number of iterations that ProxySQL can run, is managed by the option (global variable) mysql-query_processor_iterations. 

Mysql-query_processor_iterations define how many operation, a query process can execute as whole (from start to end).

This new implementation allow us to reference a Query Rule to itself in order to be executed multiple times.

If you go back you will noticed that QR 34 has FlagIN and FlagOUT pointing to the same value of 25 and Apply =0.

This will bring ProxySQL to recursively call rule 34 until it change ALL the value of the word world.

The result is the following:

[Mc]
SELECT /* continent=Europe */ Code, City.Name, City.population  FROM world.Country JOIN world.City ON world.City.CountryCode=world.Country.Code WHERE City.population > 10000 GROUP BY Name ORDER BY City.Population DESC LIMIT 5;
+------+---------------+------------+
| Code | Name          | population |
+------+---------------+------------+
| RUS  | Moscow        |    8389200 |
| GBR  | London        |    7285000 |
| RUS  | St Petersburg |    4694000 |
| DEU  | Berlin        |    3386667 |
| ESP  | Madrid        |    2879052 |
+------+---------------+------------+

You can see ProxySQL internal information using the following queries:

[Pa]
 SELECT active,hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+--------+------+---------+---------------------+----------------------------------------+-----------------+-----------+-------+--------+---------+
| active | hits | rule_id | match_digest        | match_pattern                          | replace_pattern | cache_ttl | apply | flagIN | flagOUT |
+--------+------+---------+---------------------+----------------------------------------+-----------------+-----------+-------+--------+---------+
| 1      | 1    | 33      | NULL                | \S*\s*\/\*\s*continent=.*Europe\s*\*.* | NULL            | NULL      | 0     | 0      | 25      | <--
| 1      | 4    | 34      | NULL                | world.                                 | Europe.         | NULL      | 0     | 25     | 25      | <--
| 1      | 0    | 35      | NULL                | \S*\s*\/\*\s*continent=.*Africa\s*\*.* | NULL            | NULL      | 0     | 0      | 24      |
| 1      | 0    | 36      | NULL                | world.                                 | Africa.         | NULL      | 0     | 24     | 24      |
+--------+------+---------+---------------------+----------------------------------------+-----------------+-----------+-------+--------+---------+

And

[Pa]
SELECT * FROM stats_mysql_query_digest;
<snip AND taking only digest_text>
SELECT Code, City.Name, City.population FROM Europe.Country JOIN Europe.City ON Europe.City.CountryCode=Europe.Country.Code WHERE City.population > ? GROUP BY Name ORDER BY City.Population DESC LIMIT ?

As you can see ProxySQL has nicely replace the word world. 

And executed only on the desired schema.

How I can shard redirecting the queries to an Host?

(Instead of a schema)This is even easier :)

The main point is that whatever match the rule, should go to a defined HG.No rewrite imply which means less work. 

So how this is done?As said before I have 3 NODES 192.168.1.[5-6-7]For this example I will use world db (no continent schema), distributed in each node, and I wil retrieve the node bind IP to be sure I am going on the right place.

What I will do is to instruct ProxySQL to send my query by using a HINT to a specific host.

I choose the hint "shard_host_HG" and I am going to inject it in the query as comment.

As such the Query Rules will be:

[Pa]
DELETE FROM mysql_query_rules WHERE rule_id IN (40,41,42, 10,11,12);
INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,destination_hostgroup,apply) VALUES (10,1,'user_shardRW',"\/\*\s*shard_host_HG=.*Europe\s*\*.",10,0);
INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,destination_hostgroup,apply) VALUES (11,1,'user_shardRW',"\/\*\s*shard_host_HG=.*Asia\s*\*.",20,0);
INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,destination_hostgroup,apply) VALUES (12,1,'user_shardRW',"\/\*\s*shard_host_HG=.*Africa\s*\*.",30,0);
LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;

While the queries I am going to test are:

[Mc]
SELECT /* shard_host_HG=Europe */ City.Name, City.Population FROM world.Country JOIN world.City ON world.City.CountryCode=world.Country.Code WHERE Country.code='ITA' LIMIT 5; SELECT * /* shard_host_HG=Europe */ FROM information_schema.GLOBAL_VARIABLES WHERE variable_name LIKE 'bind%';
SELECT /* shard_host_HG=Asia */ City.Name, City.Population FROM world.Country JOIN world.City ON world.City.CountryCode=world.Country.Code WHERE Country.code='IND' LIMIT 5; SELECT * /* shard_host_HG=Asia */ FROM information_schema.GLOBAL_VARIABLES WHERE variable_name LIKE 'bind%';
SELECT /* shard_host_HG=Africa */ City.Name, City.Population FROM world.Country JOIN world.City ON world.City.CountryCode=world.Country.Code WHERE Country.code='ETH' LIMIT 5; SELECT * /* shard_host_HG=Africa */ FROM information_schema.GLOBAL_VARIABLES WHERE variable_name LIKE 'bind%';

Running the query for Africa, I will get:

[Mc]
SELECT /* shard_host_HG=Africa */ City.Name, City.Population FROM world.Country JOIN world.City ON world.City.CountryCode=world.Country.Code WHERE Country.code='ETH' LIMIT 5; SELECT * /* shard_host_HG=Africa */ FROM information_schema.GLOBAL_VARIABLES WHERE variable_name LIKE 'bind%';
+-------------+------------+
| Name        | Population |
+-------------+------------+
| Addis Abeba |    2495000 |
| Dire Dawa   |     164851 |
| Nazret      |     127842 |
| Gonder      |     112249 |
| Dese        |      97314 |
+-------------+------------+
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| BIND_ADDRESS  | 192.168.1.7    |
+---------------+----------------+

That will give me :

[Pa]
SELECT active,hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+--------+------+---------+---------------------+----------------------------------------+-----------------+-----------+-------+--------+---------+
| active | hits | rule_id | match_digest        | match_pattern                          | replace_pattern | cache_ttl | apply | flagIN | flagOUT |
+--------+------+---------+---------------------+----------------------------------------+-----------------+-----------+-------+--------+---------+
| 1      | 0    | 40      | NULL                | \/\*\s*shard_host_HG=.*Europe\s*\*.    | NULL            | NULL      | 0     | 0      | 0       |
| 1      | 0    | 41      | NULL                | \/\*\s*shard_host_HG=.*Asia\s*\*.      | NULL            | NULL      | 0     | 0      | 0       |
| 1      | 2    | 42      | NULL                | \/\*\s*shard_host_HG=.*Africa\s*\*.    | NULL            | NULL      | 0     | 0      | 0       | <-- Note the HITS (2 as the run queries)
+--------+------+---------+---------------------+----------------------------------------+-----------------+-----------+-------+--------+---------+

In this example we have NO replace_pattern this is only a matching and redirecting Rule, where the destination HG is defined in the value of destination_hostgroup attribute while inserting.

In the case for Africa is HG 30.

The server in HG 30 is:

[Pa]
SELECT hostgroup_id,hostname,port,STATUS FROM mysql_servers ;
+--------------+-------------+------+--------+
| hostgroup_id | hostname    | port | STATUS |
+--------------+-------------+------+--------+
| 10           | 192.168.1.5 | 3306 | ONLINE |
| 20           | 192.168.1.6 | 3306 | ONLINE |
| 30           | 192.168.1.7 | 3306 | ONLINE | <---
+--------------+-------------+------+--------+

Which match perfectly with our returned value.

You can try by your own the other two continents.

Using destination_hostgroup

Another way to assign to which final host a query should go is to use the the destination_hostgroup, set the Schema_name attribute and use the use schema syntax in the query.

like:

[Pa]
INSERT INTO mysql_query_rules (active,schemaname,destination_hostgroup,apply) VALUES
(1, 'shard00', 1, 1), (1, 'shard01', 1, 1), (1, 'shard03', 1, 1),
(1, 'shard04', 2, 1), (1, 'shard06', 2, 1), (1, 'shard06', 2, 1),
(1, 'shard07', 3, 1), (1, 'shard08', 3, 1), (1, 'shard09', 3, 1);

And then in the query do something like :

USE shard02; SELECT * FROM tablex;

I mention this method because is one of the most common at the moment in large companies using SHARDING.

But it is not safe, because it relays on the fact the query will be execute in the desired HG.

While the risk of error is high.

Just think if a query doing join against a specified SHARD:

USE shard01; SELECT * FROM tablex JOIN shard03 ON tablex.id = shard03.tabley.id;

This will probably generate an error because shard03 is probably NOT present on the host containing shar01.

As such this approach can be used ONLY when you are 100% sure about what you are doing and when you are sure NO query will have explicit schema declaration.

Shard By Host and by Schema

Finally is obviously possible to combine the two approaches sharding by host and have only a subset of schemas

To do so let us use all the 3 nodes and have the schema distribute as follow:

• Europe on Server 192.168.1.5 -> HG 10
• Asia on Server 192.168.1.6 -> HG 20
• Africa on Server 192.168.1.7 -> HG 30

I have already set the query rules both using HINT so what I have to do is to use them BOTH to combine the operations:

[Mc]
SELECT /* shard_host_HG=Asia */ /* continent=Asia */  City.Name, City.Population FROM world.Country JOIN world.City ON world.City.CountryCode=world.Country.Code WHERE Country.code='IND' LIMIT 5; SELECT * /* shard_host_HG=Asia */ FROM information_schema.GLOBAL_VARIABLES WHERE variable_name LIKE 'bind%';
+--------------------+------------+
| Name               | Population |
+--------------------+------------+
| Mumbai (Bombay)    |   10500000 |
| Delhi              |    7206704 |
| Calcutta [Kolkata] |    4399819 |
| Chennai (Madras)   |    3841396 |
| Hyderabad          |    2964638 |
+--------------------+------------+
5 rows IN SET (0.00 sec)
 
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| BIND_ADDRESS  | 192.168.1.6    |
+---------------+----------------+
1 row IN SET (0.01 sec)

[Pa]
mysql> SELECT digest_text FROM stats_mysql_query_digest;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| digest_text                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT * FROM information_schema.GLOBAL_VARIABLES WHERE variable_name LIKE ?                                                               |
| SELECT City.Name, City.Population FROM Asia.Country JOIN Asia.City ON Asia.City.CountryCode=Asia.Country.Code WHERE Country.code=? LIMIT ? |
+--------------------------------------------------------------------------------------------------------------------------------------------+
2 rows IN SET (0.00 sec)
 
mysql> SELECT active,hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+--------+------+---------+---------------------+----------------------------------------+-----------------+-----------+-------+--------+---------+
| active | hits | rule_id | match_digest        | match_pattern                          | replace_pattern | cache_ttl | apply | flagIN | flagOUT |
+--------+------+---------+---------------------+----------------------------------------+-----------------+-----------+-------+--------+---------+
| 1      | 0    | 10      | NULL                | \/\*\s*shard_host_HG=.*Europe\s*\*.    | NULL            | NULL      | 0     | 0      | NULL    |
| 1      | 2    | 11      | NULL                | \/\*\s*shard_host_HG=.*Asia\s*\*.      | NULL            | NULL      | 0     | 0      | NULL    |
| 1      | 0    | 12      | NULL                | \/\*\s*shard_host_HG=.*Africa\s*\*.    | NULL            | NULL      | 0     | 0      | NULL    |
| 1      | 0    | 13      | NULL                | NULL                                   | NULL            | NULL      | 0     | 0      | 0       |
| 1      | 1    | 31      | NULL                | \S*\s*\/\*\s*continent=.*Asia\s*\*.*   | NULL            | NULL      | 0     | 0      | 23      |
| 1      | 4    | 32      | NULL                | world.                                 | Asia.           | NULL      | 0     | 23     | 23      |
| 1      | 0    | 33      | NULL                | \S*\s*\/\*\s*continent=.*Europe\s*\*.* | NULL            | NULL      | 0     | 0      | 25      |
| 1      | 0    | 34      | NULL                | world.                                 | Europe.         | NULL      | 0     | 25     | 25      |
| 1      | 0    | 35      | NULL                | \S*\s*\/\*\s*continent=.*Africa\s*\*.* | NULL            | NULL      | 0     | 0      | 24      |
| 1      | 0    | 36      | NULL                | world.                                 | Africa.         | NULL      | 0     | 24     | 24      |
+--------+------+---------+---------------------+----------------------------------------+-----------------+-----------+-------+--------+---------+

As you can see rule 11 has two HITS, which means my queries will go to the associated HG.

But given Apply for rule 11 is =0, ProxySQL will first continue to process the QueryRules.

As such it will also transform the queries as for rules 31 and 32, each one having the expected number of hits (1 the first and the 4 because the loop, the second).

How to shard base on a MOD

to shard base on a MOD value Like handle table sharding based on the mod of the user id?

such that `insert into 'foo' ('user_id', 'name') values (180, 'bar')` becomes `insert into 'foo_X ('user_id', 'name') values (180, 'bar')` based on 180 % 25, where 25 is the number of sharded tables.

Based on 180 % 25, where 25 is the number of sharded tables.

Solution is to create X number of query rules to manage the MOD value:

INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(00|25|50|75),","INSERT INTO foo_00 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(01|26|51|76),","INSERT INTO foo_01 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(02|27|52|77),","INSERT INTO foo_02 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(03|28|53|78),","INSERT INTO foo_03 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(04|29|54|79),","INSERT INTO foo_04 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(05|30|55|80),","INSERT INTO foo_05 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(06|31|56|81),","INSERT INTO foo_06 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(07|32|57|82),","INSERT INTO foo_07 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(08|33|58|83),","INSERT INTO foo_08 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(09|34|59|84),","INSERT INTO foo_09 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(10|35|60|85),","INSERT INTO foo_10 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(11|36|61|86),","INSERT INTO foo_11 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(12|37|62|87),","INSERT INTO foo_12 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(13|38|63|88),","INSERT INTO foo_13 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(14|39|64|89),","INSERT INTO foo_14 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(15|40|65|90),","INSERT INTO foo_15 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(16|41|66|91),","INSERT INTO foo_16 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(17|42|67|92),","INSERT INTO foo_17 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(18|43|68|93),","INSERT INTO foo_18 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(19|44|69|94),","INSERT INTO foo_19 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(20|45|70|95),","INSERT INTO foo_20 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(21|46|71|96),","INSERT INTO foo_21 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(22|47|72|97),","INSERT INTO foo_22 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(23|48|73|98),","INSERT INTO foo_23 (user_id,name) VALUES(\1\2,",1);
INSERT INTO mysql_query_rules (active, match_pattern, replace_pattern, apply) VALUES (1, "INSERT INTO foo \(user_id,name\) VALUES \((\d+)(24|49|74|99),","INSERT INTO foo_24 (user_id,name) VALUES(\1\2,",1);
LOAD MYSQL QUERY RULES TO RUNTIME;

As you can see below this solution allow the transparent assignment using MOD 25

1
2
3
4
5
6
7

mysql> CREATE TABLE foo_00 (user_id INT, name VARCHAR(30));
mysql> CREATE TABLE foo_01 (user_id INT, name VARCHAR(30));
mysql> CREATE TABLE foo_02 (user_id INT, name VARCHAR(30));
mysql> CREATE TABLE foo_03 (user_id INT, name VARCHAR(30));
mysql> CREATE TABLE foo_04 (user_id INT, name VARCHAR(30));
mysql> INSERT INTO foo (user_id,name) VALUES (1000,"nameA");
mysql> INSERT INTO foo (user_id,name) VALUES (7456076,"nameB");

SELECT * FROM foo_00;
+---------+-------+
| user_id | name  |
+---------+-------+
|    1000 | nameA |
+---------+-------+
mysql> SELECT * FROM foo_01;
+---------+-------+
| user_id | name  |
+---------+-------+
| 7456076 | nameB |
+---------+-------+
 

Credits

It is obvious that I need to acknowledge and kudo the work Rene' Cannao is doing to make ProxySQL a solid, fast and flexible product.

I have also to mention that I was and am working with him very often, more often than he likes, asking him fix and discussing with him optimization.

Requests that he try to satisfied with surprising speed and efficiency.

Reference

https://github.com/sysown/proxysql/tree/v1.2.2/doc
https://github.com/google/re2/wiki/Syntax
http://www.proxysql.com/2015/09/proxysql-tutorial-setup-in-mysql.html
https://github.com/sysown/proxysql/blob/v1.2.2/doc/configuration_howto.md
https://github.com/sysown/proxysql/blob/v1.2.2/INSTALL.md
https://dev.mysql.com/doc/index-other.html

As most of us know, we will have the chance to attend to the MySQL conference in April (from 18 to 21).

For the ones like me that had being there from long, this is a moment of reunion with colleagues and friend. It is also a moment of confrontation and sharing.

In the years this conference had be the moment for the ones surfing the MySQL sea in which things can be put on the table and discuss. Very few matter if it was call MySQL conference or, as it is now Percona Live. What matter is the spirit with which the people participate, and the desire to share.

One of the important aspects was and is, to be able to learn from others experience, innovation and experimentations.

The past year had be a very difficult for me, thankfully only work wise, but I had also be able to be in some interesting exercises, that had allow me to come with a list of proposal that I consider quite interesting, some more some less as usual, depending from the angle you perform your work.

Anyhow given this year Percona had invite the community to express an opinion on the submissions, I decide to share mine and explain a bit each of them.

Here we go:

The first element is a tutorial on Performance schema. I know that a lot of people are talking about it, presenting in various way the usage of it, and some of the presentations are really really good. So why I should spend time to prepare a tutorial, and why anyone should attend?
My answer is simple, because I am approaching it from a different angle. Most of the presentations look to it as something isolated, auto reference to MySQL space. I am looking to it as part of a larger design and vision, connecting Performance schema to the USE methodology (see other proposal about it).

What I want to achieve during the tutorial is not only to provide instruction on how to access PS or what is there, but how to contextualize the information in the context of the Server(s) behavior.

Here the tutorial title and link for you to vote :

Learn how to use Performance Schema in MySQL 5.7 the basics and not only.

https://www.percona.com/live/data-performance-conference-2016/sessions/learn-how-use-performance-schema-mysql-57-basics-and-not-only#community-voting

The next one is a presentation that is linked to an article I wrote about the cloud, Galera, Aurora and other solutions.

If you had missed it I suggest you to read it (http://www.tusacentral.net/joomla/index.php/mysql-blogs/175-aws-aurora-benchmarking-blast-or-splash.html).

I had a lot of feedback about that article, from colleagues and from Amazon as well. Given the topic, and given the active evolution that a new product like Aurora is subject to, numbers and conclusion may change in short time.

As such I had plan to perform additional testing during the year (2016), collecting data and present results in articles and presentations.

As mentioned I had a very productive conversation with Amazon about Aurora, and given we all love to have new productive platform, able to perform at the best, I will be more than happy to run those tests with them to help them identify bottlenecks and possible solutions. As usual I will maintain my independence, transparence and impartiality, such that everyone can validate my numbers, and let us see what it will be.

The presentation title and link for you to vote:

Comparing synchronous replication solutions in the cloud

https://www.percona.com/live/data-performance-conference-2016/sessions/comparing-synchronous-replication-solutions-cloud#community-voting

I cannot say or count at how many presentations I had attended, talking about Performance, and how to analyze, check or improve it. Most focus on this or that aspect of the specific storage engine, or the new feature deliver in the X MySQL release.
But so far I had NOT attend to a presentation that would help me in define a methodology, an organic approach that I can use and reuse for such analysis.

As such I decide to do two things, first was to write an extensive document to be used by my teams in my ex-company (Pythian), something like an endowment for them to follow and relay on to do what is needed to perform a good performance review.

Second to start to talk about it and present the approach. What is important to understand, is that I am not inventing anything new, but using what had be already well defined and apply to the MySQL world.

In short I will present the USE methodology (utilization, saturation, and errors), should be used early in a performance investigation, to identify systemic bottlenecks.
USE can be summarized this way, for every resource, check utilization, saturation, and errors.

My presentation will explain the USE methodology, and how it can help any administrator during the analysis of performance issues. It will also extend the approach for the MySQL specifics, taking advantage of Performance Schema instruments.

Presentation title and link for you to vote below:

The USE Method and how to boost the way you perform performance tuning on your (MySQL) environment

https://www.percona.com/live/data-performance-conference-2016/sessions/use-method-and-how-boost-way-you-perform-performance-tuning-your-mysql-environment#community-voting

When MySQL 5.6 comes out, I had cover with articles and presentations how to use at the best the new features related to table space managements. Covering how use the features that allow an administrator to play with them, and what kind of issues he may encounter.

You can review them here:

http://www.tusacentral.net/joomla/index.php/mysql-blogs/index.php/mysql-blogs/136-portable-tablespace-in-innodb-i-test-it

http://www.tusacentral.net/joomla/index.php/mysql-blogs/137-portable-tablespace-in-innodb-i-test-it-part2.html

http://www.tusacentral.net/joomla/index.php/mysql-blogs/138-portable-table-space-part-iii-or-we-can-do-it-partition.html

Old presentation here:

http://www.slideshare.net/marcotusa/discard-inport-exchange-table-tablespace

My next presentation is an update to it; I will also release articles about this topic during the next year, with more instructions and details for DBA to follow, also covering General table space and compression.

Presentation title, description and link for you to vote below:

MySQL 5.7 Tablespace management and optimization

https://www.percona.com/live/data-performance-conference-2016/sessions/mysql-57-tablespace-management-and-optimization#community-voting

Finally I had submitted a proposal for a topic that I personally love, which is related to Java development.

Despite most nasty, and often erroneous, comments, Java is not only a very powerful programming language, but also it is used so often and in so many ways that you can easily state that every day you use several applications develop using it.

What I had often found, and what I had fight against, is the very misuse of several abstraction layers, that developers often use, without understanding what they are doing.

Unfortunately this is a cultural issue that had be push and reinforce in years mostly by bad developers, who do not get how important is keep in mind a very basic concept: “Scaling scenario and huge data, are not present in your laptop”.

The basic meaning is, that whenever you develop code, you need to think to the big numbers, not if that functionality works now, but if it will work on a deployment of 200 application servers, and how it will impact the data layer while scaling.

I will produce a series of articles about this in the future, but the first step is to explain how use and how to use correctly one of the most powerful tool we have at the moment, the MySQL Java connector.

Too often I had see application relay on crazy solutions, or even crazier customize code; ignoring what is already available, able to provide quite efficient out of the box  solution.

This presentation, that you need to see as a first step, has the scope to start a journey, in which we will free good developers, able to think and plan application for the future, from the dumb approach often used by dull abstraction tools.

The title and link for you to vote:

Empower your application with sophisticate High Availability features using MySQL Connector/j

https://www.percona.com/live/data-performance-conference-2016/sessions/empower-your-application-sophisticate-high-availability-features-using-mysql-connectorj#community-voting

Conclusion

I had wait a bit before asking you to vote, this because I want to explain why I had submitted what I had submit.

I was also collecting information, comments and feedback about few topics; to be sure I could provide the time to my submission.

What I would like now, is have YOU spend few minutes and think if any of the topic I had describe above may be of your interest.

If so please follow the link and vote for the presentation, if not do not worry I am not tracing your IP and I am NOT going to send you some Italian Friends to convince you to vote for me.

Thanks in advance anyhow!!!

Stay tuned!

Some time ago, I published the article “AWS Aurora Benchmarking - Blast or Splash?”. In which I was analyzing the behavior of different solutions using synchronous replication in AWS environment.

After I published it, I received a lot of comments and feedback, from the community and from Amazon engineers.

Given that I had decide to perform another round of tests, keeping into account the comments received and the suggestions.

I had presented some of the results during the Percona conference in Santa Clara last April 2016. The following is the transposition into an article of that presentation with more details.

{autotoc enabled=yes}

Why new test?

Very good question, with an easy answer.

Aurora is a product that is still under development and code refining, six months of development could present major changes in performance. Not only, the initial tests where focus on entry level solutions, meaning I was analyzing that kind of user, that are currently starting their business and looking for a flexible solution allow them to save money and scale.

This time I had put the focus on enterprise solution, analyzing what an already well establish company would eventually get when in the need to find for a decent scalable solution.

As such two different scenarios.

Why so many (different) tests?

I had used many different benchmarking tool, and I am still planning to run others. Why so? Why don’t simply relay in one of them?

Again simple answer, I had use different tools because in some case they provide me different way of access and use data. Not only, I do not trust benchmarking tools, not even the one I had developed, as such I want to tests same thing using different tools and compare results, ONLY if I see a common pattern, then I consider the test valid. Personally I tend to discard any test is not consistent or analysis performed using a single benchmarking tool. In my opinion be lazy is not an option when doing this kind of exercises.

Tests run

I had run three main kind of tests:

• Performance and load stress
• High Availability failover
• Response time (latency) from application point of view

Performance and load stress

These tests were the most extensive and demanding.

I was analyzing the capacity to serve load in different conditions, from light load up to full utilization and some degree of saturation or resources.

First set of tests was to evaluate simple load on a single table causing the table to become a hotspot and showing how the platform would manage the increasing contention.

Second set of tests was to perform similar load but distributing it cross multiple table and batching the operations. Parallelization, contention, scalability and distributed hotspots where in the picture.

The two above were focus on write operation only, and were done using different tools comparing the results given they were complementary.

Third set of tests, using my own stresstool, was focus on R/W oriented usage. Performed tests execute against multiple tables, performing CRUD actions, using simple and batch insert, reads by PK, index, by range, IN and exact match conditions.

Fourth set of tests was performed using TPC-C like load (OLTP).

Fifth set of tests was using Sysbench in OLTP mode with 250 tables.

Scope of the last three set of tests was to identify how the platforms would had managed the load, considering the following:

• Read and write contention on the same tables
• High level of parallelism (from the application)
• Possible hot-spots (TPCC district)
• Increasing utilization (memory, threads, IO)
• Saturation (connections)

About the tests

It was difficult to compare apple with apple here. And I think that is the main point to keep in mind.

Aurora is not a standard RDS solution, as we were used to have.
Aurora looks like MySQL, smell like MySQL, but is not vanilla MySQL.

To achieve what they have to achieve the engineers there had to change many parts, and the more you dig the more you realize there are significant differences.

Because that I had to focus more on identify what each solution can do, comparing solutions against expectations, instead comparing the numbers for the numbers.

I was more interested to see, what happen if I have a burst of connections and my application will go from 4K to 40K connections. Will it crash? Will it slow down?

How long I should wait if a node fails?

What should I prevent to have in my schema design, in order to do not have bottlenecks.

In this context, those in my opinion, are relevant questions, more than discover that solution A can have 3000 rows written/sec and the other can have 3100.

Or that I may (may) have some additional page rotation, file -> memory-> flush because the amount of memory differs.

Those are valuable information too, for sure, but less than have a decent understanding of which platform will help my business to grow and remain stable.

What is the right tool for the Job? This is the question I was addressing.

The machines

Small Boxes (first round of tests)

EIP = 1
VPC = 1
ELB=1
Subnets = 4 (1 public, 3 private)
HAProxy = 6 
MHA Monitor (micro ec2) = 1
NAT Instance (EC2) =1 (hosting EIP)
DB Instances (EC2) = 3 (m4.xlarge) 16GB
Application Instances (EC2) = 6 (4)
EBS SSD 3000 PIOS
Aurora RDS node = 3 (db.r3.xlarge) 30GB
 

Large Boxes (latest tests)

EIP = 1
VPC = 1
ELB=1
Subnets = 4 (1 public, 3 private)
HAProxy = 4
MHA Monitor (micro ec2) = 1
NAT Instance (EC2) =1 (hosting EIP)
DB Instances (EC2) = 3 (c3.8xlarge) 60GB
Application Instances (EC2) = 4
EBS SSD 5000 PIOS
Aurora RDS node = 3 (db.r3.8xlarge) 244GB
 

Note

It was pointed to me that I had deliberately choose to use an Ec2 solutions for PXC with less memory than the one available in Aurora.

This is true, and we must keep in to consideration.
Reason for this is the fact that the only Ec2 solution matching the memory of a db_r3.8xlarge is the d2.8xlarge.

I did try it but the level of scalability I got from the CPU point of view was less efficient than the one available with c3.8xlarge.

Given that I had decide to prefer CPU resource to memory here, especially because I was going to test, concurrency and parallelism in conjunction to load increase.

From the result I got I feel confident that I choose right, but I am open to comment.

The layout

This is how the setup looks like

Where you read Java, those are the application nodes running the different test applications.

Two words about Aurora first

Aurora has few key concepts that must have clear in mind. Especially how it manages the writes cross replica, and how connections are implemented.

The IO activity

To replicate the information across the different storage, Aurora replicate FRM files and data coming from IB_LOGS only. This is a quite significant advantage to other form of replication, given the limited number of bytes that are replicated over the network also if they are replicated for 6 times.

image from Amazon Aurora Deep dive

Another significant advantage is that Aurora does not use double write buffer which is obviously another blast (see recent optimization in Percona Server https://www.percona.com/blog/2016/05/09/percona-server-5-7-parallel-doublewrite/ ) .

Simplifying, writes in Aurora are organized filling its commit queue and pushing the changes as group commit to the storage.

image from Amazon Aurora Deep dive

Now in some presentations you may have seen that all steps are asynchronous, but is important to underline that a commit is acknowledge by Aurora when at least 2 AZ had received and wrote the incoming data related to that commit. Writes here means received in the storage node incoming queue, and with a quorum of 4 over 6 nodes.

This means that no matter what, data has to travel on the network reach the final destination and ack signal come back, before Aurora returns the ack to the commit operation. Network is in the same region but still it could represent an incognita about performance. No wonder if we may have some latency at this stage.

As you can see what I am reporting is also confirmed in the image below and in the observations, point is that from that slide is not clear the impact of the step 1 – 2.

image from Amazon Aurora Deep dive

Thread pooling

Oh yes, Aurora use thread pooling, a lot. That will become very clear later, and more the work is based on parallelism, more efficient thread pooling seems to be.

In most cases we are used to see CPUs on database servers not fully utilized, unless some heavy ordering operation or bad query. That behavior is also (not only) a direct consequence of the connection-to-thread model, that imply period of latency and stand by. In Aurora the incoming connections are not following the same model, instead the pool redistributes the load of the incoming connection to a pool of threads, optimizing the latency period, resulting in a higher CPU utilization. Which is what you want from your resource, to be utilized and not sit there waiting for something else to do its job.

image from Amazon Aurora Deep dive

The results

Without additional waste of electronic ink, let see what comes out by this round of tests (not the final one by the way). To simplify the reading, I will report also the graphs from the first set of tests, but will focus on the latest, Small Boxes = SB, Large Boxes LB.

First test: IIBench

As declared previously my scope was to verify how the two platforms would have reacted to simple load focus on insert on a basic single table, bufferpool was saturated before the running.

SB

LB

As we can see in presence of a hot spot the Solution using PXC outperform the Aurora, in both cases. What is notable though is that while PXC remain approximately around the same time/performance, Aurora is significantly reducing the time taken. This shows that Aurora was actually taking advantage of the more powerful platform while PXC was not able to.

Analyzing in more details what was happening, we can notice that Aurora is actually performing atomically better. It was able to manage more writes/second as well as rows and page managed. But it was inconsistent, Aurora was having performance hiccups at regular intervals. As such the final result was that it takes more time to process the whole workload.

I was not able to dig a lot given some metrics are not fully available in Aurora, as such I had to fully rely on Aurora engineers who mention me the hot-spot contention as possible issue.

Aurora Handler calls

PXC Handlers calls

The execution in PXC is showing less calls but constant performance, while Aurora has hiccups.

Aurora Page Activity Write

PXC Page Activity Write  

The trend shown by the handler stay consistent in the page management and rows insert, as logically expected.

Second test Application ingest

As mention this test see many threads from different application servers inserting by batch of 50 statement against multiple tables.

The results coming from this test are quite in favor to Aurora, as we can see starting from the time taken to complete the same workload:

LB

SB

While with small ones the situation was the inverse.

But here starts the interesting part.

Aurora is able to manage significantly higher number rows as the picture below shows

The results are also quite constant and not significantly decreasing like the inserts with PXC.

But the number of Handler commits are significantly less.

Once more they stay the same on the load increase, without impacting performance.

Reviewing all Handlers call we have a first surprise

PXC Handlers calls

Aurora Handlers calls

The gap/drop existing in the two graphs are the different tests (with increasing number of threads)

We have two things to notice here, the first one is that PXC has a decrease in performance while processing the load, while Aurora has not. The second (you need to zoom the image) the number of commit is floating in PXC while it stays fix in Aurora.

Even bigger surprise comes up when reviewing the connections graphs.

As expected PXC is having all my connections open there and the number of threads running is quite close to what is the number of the threads connected.

And both of them follow the increasing number of connected threads.

But this is not the case in Aurora.

Also if my applications are actually trying to open ~800 threads, the Aurora node see only a part of them, and the number of running is fix to 32 threads.

Thing to consider here are the following, first my applications does not connect directly to Aurora instance, but to a connector (MariaDB). Second Aurora, in this case, cap the number of running threads to the number of CPU available on the instance (here 32).

Given that I may expect to have worse performance, but I do not.

The fact that Aurora use one thread for multiple connections seems working quite efficiently here.

See also the number of Rows inserted is consistent with the handler calls and better performing than PXC.

Aurora Rows inserted

PXC Rows inserted

Again we have the same trend, only this time we have Aurora able to perform definitely better than PXC.

Third test: OLTP application

When run on the small boxes this test saw PXC performing tons’ time better then Aurora,

The time taken by Aurora was ~3 times the one taken by PXC

With large box I had the inverse, Aurora is outperforming PXC by many times, being from two up to almost 7 times faster then PXC.

Analyzing the number of commands executed with increasing workload, we can see how PXC is able to perform better than Aurora with a workload of 128 threads, but is starting to have worse performance as the load increase.

On the other hand, Aurora is able to manage the load and in read/write without significant performance loss, that include being able to increase the number of commits/sec.

Reviewing the Handler calls, we see gain that the Handler commit calls are significantly less in Aurora as already noticed in the ingest tests.

The other thing to notice, is that the number of calls for PXC is significantly higher and not scaling, while Aurora has a nice scaling trend.

Forth Test: TPCC-mysql

Tpcc test is main to test OLTP traffic, with the note that some tables like district my become a hotspot. The tests I run were executed against 400 warehouse and using 128 Threads as maximum for the small box and 2048 threads for the Large.

During this test I hit one of the Aurora limitation and I had escalated that to the Aurora engineers, who are aware of the problem.

Small boxes

In the case of small boxes, there is nothing to say, PXC is able to manage the load more efficiently, also if his trend is not optimal having significant fluctuation. Aurora is just not able to keep the it up.

Large boxes

Different and a bit more complex scenario in the case of the use of large boxes.

I would like to say that Aurora is performing better:

And as you can see this is true for 2 tests over 3, and up to when it got stuck by internal limitation, Aurora was also performing better on the 3td. But then its performance just collapse.

Performing more in depth investigation I noticed that under the hood, Aurora was not performing as well as it looks like.

That comes out quite clear performing comparison between few graphs covering Comm_ execution, Open Files, Handlers and Innodb row lock time.

In all of them is quite evident how PXC is able to keep serving the workload with consistent behavior, while Aurora fails from the second test on (512 threads), and not only on the 3td with 2048 threads.

Aurora

PXC

It is clear how Aurora was better serving during the test with 256 threads going over the 450K com select serve (in 10 sec interval), comparing with PXC that was not able to go over 350K.

But in the tests after while PXC was able to keep going, also if with decreasing performance, Aurora was starting to struggle, with very inconsistent behavior.

This was also confirmed by the open files graph

Aurora

PXC

The graphs show the instance of files open during the running, not the one already open.
It reflect the Open_file metric “The number of files that are open. This count includes regular files opened by the server. It does not include other types of files such as sockets or pipes. Also, the count does not include files that storage engines open using their own internal functions rather than asking the server level to do so”. I was quite surprise by the number of files open by Aurora.

Handlers as well were reflecting the same behavior

Aurora

PXC

Perfectly in line with the Com trend.

So what was instead reversely increasing?

Aurora

PXC 

As you can see from the above, the exactly same workload, had generate an increasing lock row time, from quite low in the test with 256 threads, up to crazy high in the one with 2048 threads.

As mention we know that TPCC has a couple of tables that works as hotspots, and we had already saw with IIbench how Aurora is not working efficiently in that cases.

As additional information during the tests, I was getting a lot of 188 errors, this is an Aurora internal error. When I report it, I was told, they know about it, and they are planning to work on it.

I hope they will do soon, because if this issue is solved it is very likely that Aurora will not only be able to manage the tested workload, but go over it by far.

I am saying this because also with the identified issues Aurora was able to keep going and manage a more then decent response time during the test2 with 512 threads.

Fifth test: Sysbench

I add the sysbench tests to test the scalability, and to see the what happen when the system reaches the saturation point.

This test brought up some limitation existing in the Aurora solution, more related to the connector than the Aurora engine itself.

Aurora has a limit of 16k connection, as said I was looking to see what happens if I got to saturation point or close to it. It doesn’t matter if this is a crazy high number or not.

What happened is that I was able to have Aurora managing traffic up to 4K but the more I was going close to the limit, the more I was having issue in connectivity, more than anything else.

At the end I had to run the test with 8k 12k and 20k threads pointing directly to the Aurora instance, bypassing the connector that was not able to serve the traffic.

After that I was able to hit up to ~15500 Threads but with a lot of inconsistent performance. Given that I am defining the limit of meaningful test to the previous level of 12K threads.

PXC was able to scale up to 16K no problem.

What also is notable here is that Aurora was able to mange the workload more efficiently in terms of transaction handling as transactions executed and latency.

The number of transaction executed by Aurora were ~three times the one executed by PXC.

Also in term of latency Aurora was showing less latency then PXC.

Internally Aurora and PXC operations were once more different in terms of how the workload was handle. The most diverging result was the handlers calls.

Commit calls in Aurora were a fraction of the calls in PXC, while the number of rollback was higher.

The read calls had an even more diverging behavior, with PXC performing high number of read_keys, while Aurora was having a very limited number of them. Read_rnd are very high in PXC but totally absent in Aurora (note that in Aurora, read_rnd are reported but seems not really increasing).  On the other hand, Aurora report a high number of read_rnd_next while PXC has none.

HA availability

Fail-over time

Both solutions

In this test the fail-over time had seen the solution using Galera and HAProxy to be more efficient. That was happening with limited or mid level load, one assumption is that given Aurora has in any case to verify the status of the data transmitted and its consistency across the 6 data store node, the process is not so fast as it could be.

Or, another assumption, it could be that the cluster connector is not as efficient as it should in redirecting the traffic from one node to another. It would be a very interesting exercise to replace it with some other custom solution.

Note that I was performing the tests following the Amazon indication to use the following to simulate a real crash:

ALTER SYSTEM CRASH [INSTANCE|NODE]

As such, I was not doing anything strange or out of the ordinary.

It is worth mentioning that of the 8 seconds taken by MySQL/Galera to perform the failover, 6 were due to the HAProxy settings which had 3000 ms interval and 2 loops in the settings before executing failover.

Execution latency

The scope of this tests, was to identify the latency existing between the moment that application send the request, and the moment MySQL/Aurora take the request in “charge”.

The expectation is that the more the database will get busy, the longer latency will exist.

For this test I had report both results, the one coming from old test with small box and the new one with large box.

Small Boxes  

Large boxes

It is clear from the graphs that the two tests report a different scenario.

In the first Galera was able to manage the load more efficiently and serve requests with lower latency.

For the new tests, I had utilized higher number of threads than the ones for the small box, nevertheless in the second CPUs utilization and the number of running threads drive me to think Aurora was finally able to to utilize the resource more efficiently and the latency, just drop.

To mention, that latency was jumping up again when the number of connection was going above the 12K, but that was expected given previous tests results.

Conclusions

High Availability

The two platforms were shown to be able to manage the failover operation in a limited time frame (below 1 minute).

Nevertheless, MySQL/Galera was shown to be more efficient and consistent.

This result is a direct consequence of the synchronous replication, that by design brings MySQL/Galera in to not allow an active node to fell behind.

In my opinion the replication method used in Aurora, is efficient, and given data is shared across the read replicas, fail-over should happen faster.

I had suffered a lot during the tests because the connector, and I have the feeling that having another solution in place may bring some surprise, and actually I would really like to test that as well.

Performance

In this run of tests Aurora was able to invert the results I had in the first test with the small boxes. In almost all cases I had Aurora performing as well or better then PXC. There are still cases where Aurora is penalized and those are the ones where hotspots are present, and contention in Aurora is killing the performance, and raise errors (188). But I hope we will see a significant evolution soon.

General comments on Aurora

The product is evolving quickly, and benchmark results may become obsolete in very short time, this is why is important to have repeatable and comparable tests.

From my point of view, in this set of tests Aurora had clearly show where it fits better.  

Critical applications that require High Available platform, and a lot of CPU power.

There is no reason to use Aurora in small-mid boxes, the platform is not going to be as efficient as a standard solution like PXC.

But if cost is not an issue, and the application really require a lot of parallelism, Aurora on db.r3.8xlarge is a good solution.

I still see space for improvements, like for cluster connectors, or the time taken to restart a cluster after a full stop, or contention reduction.

But I am also confident that the work lead by the developer team will fix most of my concern (and more) soon.

Final note, it would be nice to have the code open source, to have the community to contribute, also if I understand the business reasons for not to.

On Monday 21 September Percona Live will start in Amsterdam.

The program is full of interesting topics and I am sure a lot of great discussions will follow.

I whish all my best to all my colleagues, friends and customers that will attend it. Have fun guys and drink a couple of beer for me as well.

That is it, I had decided to do not submit speech(es) and to do not come this year, not only to Percona Live but to most or all the conferences.

I want to stay focus on my customers for now, and be present as much as I can for my teammates.

We have so much going on that an effort in that direction must be done, and the few time left ... well I have to read a lot of intersting stuff not Tech related.

So have fun, learn, teach, listen and talk ... but on top of all share and keep the spirit high, these are hard times and events like Percona Live are important.

I will miss it ... but as said sometime we have to choose our priorities.

Great MySQL (still talking about MySQL right??) to everybody