MySQL

My MySQL tips valid-rss-rogers

 

This article is the spin-off of the article How ProxySQL deal with schema (and schemaname)

1 How ProxySQL differes from MySQL in managing explicit default schema declaration? does it respect the -D or USE

MySQL and ProxySQL will behave the same when passing the default schema, setting it as default.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

      MySQL
        mysql -uuallworld -ptest -h192.168.1.107 -P 3306 -D test
        select database();
        +------------+
        | database() |
        +------------+
        | test       |
        +------------+
 
        SHOW TABLES;
        +-----------------+
        | Tables_in_test  |
        +-----------------+
        | rewrite_tbtest  |
        | rewrite_tbtest1 |
        +-----------------+
 
      ProxySQL
        mysql -uuallworld -ptest -h127.0.0.1 -P 6033 -D test
        select database();
        +------------+
        | database() |
        +------------+
        | test       |
        +------------+
 
        SHOW TABLES;
        +-----------------+
        | Tables_in_test  |
        +-----------------+
        | rewrite_tbtest  |
        | rewrite_tbtest1 |
        +-----------------+
 

 

Passing a different schema from the default one in ProxySQL will override the ProxySQL default:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

        MySQL
         ./mysql-3306 -uuallworld -ptest -D mysql
        (uallworld@localhost) [mysql]>select database();
        +------------+
        | database() |
        +------------+
        | mysql      |
        +------------+
        ProxySQL
        [root@rms2 server57S]# ./mysql-3306 -uuallworld -ptest -h192.168.1.50 -P6033 -D mysql
 
        (This email address is being protected from spambots. You need JavaScript enabled to view it.) [mysql]>select database();
        +------------+
        | database() |
        +------------+
        | mysql      |
        +------------+
 

 

Connecting without DEFAULT Schema

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

      MySQL 
        mysql -uuallworld -ptest -h192.168.1.107 -P 3306
        mysql> select database();
          +------------+
          | database() |
          +------------+
          | NULL       |
          +------------+
 
      ProxySQL 
        mysql -uuallworld -ptest -h127.0.0.1 -P 6033
        mysql> select database();
        +------------+
        | database() |
        +------------+
        | test       |
        +------------+
 

 

The last is different between MySQL and ProxySQL.      

By default ProxySQL will connect the user to his default schema.     

Not only, if the default schema is not define:

1
2
3
4
5
6
7
8
9
10
11
12
13

      +-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
      | username  | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
      +-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
      | uallworld | test     | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
 
      ProxySQL will point the connection to information_schema.
          mysql> select database();
          +--------------------+
          | database()         |
          +--------------------+
          | information_schema |
          +--------------------+
 

 

2 How proxy respect/follow security agains schema

1
2
3
4
5
6
7
8
9
10
11

      MySQL
      mysql -uuworld -ptest -h192.168.1.107 -P 3306 -D test
        mysql: [Warning] Using a password on the command line interface can be insecure.
        ERROR 1044 (42000): Access denied for user 'uworld'@'%' to database 'test'
 
      ProxySQL
      mysql -uuworld -ptest -h127.0.0.1 -P 6033 -D test
        mysql: [Warning] Using a password on the command line interface can be insecure.
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 483902
        Server version: 5.5.30 (ProxySQL)

 

And no warnings.     

Why?

Because while you directly connect to MySQL, when you connect to ProxySQL you do not actually touch the final server.      

You only open a connection to the ProxySQL, issue a query will open a connection and if the user do not have the right grants an error will be returned.

Let us see.

 just issuing the command:

 

1
2
3

        mysql> select database();
        ERROR 1044 (42000): Access denied for user 'uworld'@'%' to database 'test'
 

 

 

 

As soon as you try to query the MySQL server you got an error.     

 

 

3 How schemaname filter acts in the query rules?

In MySQL we can easily change the default schema with USE , this action is fully supported by ProxySQL.    
But it may have some side effects when using "schemaname" as filter in the query_rules.  
If you define a rule that include the default schemaname and the default schema is changed with USE, the rule will not apply, and unpredictable results may happen.  
To prevent that ProxySQL has another option in mysql_user "schema_locked" which will prevent the schema to be changed by USE.
This feature is present but not fully implemented, and after a brief conversation with Rene (https://github.com/sysown/proxysql/issues/1133).   
For now my advice is to DO NOT use schemaname as filter in the query_rules.  

Below the full test case:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

    mysql> select active,hits,destination_hostgroup, 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 where destination_hostgroup < 30  ORDER BY mysql_query_rules.rule_id;
      +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+
      | active | hits | destination_hostgroup | rule_id | match_digest        | match_pattern | replace_pattern | cache_ttl | apply | flagIN | flagOUT |
      +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+
      | 1      | 0    | 10                    | 10      | ^SELECT.*FOR UPDATE | NULL          | NULL            | NULL      | 1     | 0      | NULL    |
      | 1      | 0    | 11                    | 11      | ^SELECT             | NULL          | NULL            | NULL      | 1     | 0      | NULL    |
      +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+
      2 rows in set (0.00 sec)
 
 
      (This email address is being protected from spambots. You need JavaScript enabled to view it.) [world]>Select * from world.City order by Population DESC limit 5 ;
      +------+-----------------+-------------+--------------+------------+
      | ID   | Name            | CountryCode | District     | Population |
      +------+-----------------+-------------+--------------+------------+
      | 1024 | Mumbai (Bombay) | IND         | Maharashtra  |   10500000 |
      | 2331 | Seoul           | KOR         | Seoul        |    9981619 |
      |  206 | São Paulo       | BRA         | São Paulo    |    9968485 |
      | 1890 | Shanghai        | CHN         | Shanghai     |    9696300 |
      |  939 | Jakarta         | IDN         | Jakarta Raya |    9604900 |
      +------+-----------------+-------------+--------------+------------+
      5 rows in set (0.01 sec)
 
 
 
      mysql> select active,hits,destination_hostgroup, 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 where destination_hostgroup < 30  ORDER BY mysql_query_rules.rule_id;
      +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+
      | active | hits | destination_hostgroup | rule_id | match_digest        | match_pattern | replace_pattern | cache_ttl | apply | flagIN | flagOUT |
      +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+
      | 1      | 0    | 10                    | 10      | ^SELECT.*FOR UPDATE | NULL          | NULL            | NULL      | 1     | 0      | NULL    |
      | 1      | 1    | 11                    | 11      | ^SELECT             | NULL          | NULL            | NULL      | 1     | 0      | NULL    | <---
      +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+
      2 rows in set (0.00 sec)
 
      mysql> select * from stats_mysql_query_digest;
      +-----------+------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+
      | hostgroup | schemaname | username | digest             | digest_text                                               | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
      +-----------+------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+
      | 10        | world      | uworld   | 0x594F2C744B698066 | select USER()                                             | 1          | 1502789864 | 1502789864 | 0        | 0        | 0        |
      | 10        | world      | umyworld | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1          | 1502789849 | 1502789849 | 10264    | 10264    | 10264    |
      | 10        | world      | uworld   | 0x02033E45904D3DF0 | show databases                                            | 1          | 1502789863 | 1502789863 | 1750     | 1750     | 1750     |
      | 10        | world      | umyworld | 0x594F2C744B698066 | select USER()                                             | 1          | 1502789745 | 1502789745 | 0        | 0        | 0        |
      | 10        | world      | uworld   | 0x226CD90D52A2BA0B | select @@version_comment limit ?                          | 1          | 1502789863 | 1502789863 | 0        | 0        | 0        |
      | 10        | world      | uworld   | 0x99531AEFF718C501 | show tables                                               | 1          | 1502789863 | 1502789863 | 470      | 470      | 470      |
      | 10        | world      | umyworld | 0x226CD90D52A2BA0B | select @@version_comment limit ?                          | 1          | 1502789745 | 1502789745 | 0        | 0        | 0        |
      | 10        | world      | umyworld | 0x99531AEFF718C501 | show tables                                               | 1          | 1502789745 | 1502789745 | 7427     | 7427     | 7427     |
      | 11        | world      | uworld   | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1          | 1502789866 | 1502789866 | 3349     | 3349     | 3349     | <---
      | 10        | world      | umyworld | 0x02033E45904D3DF0 | show databases                                            | 1          | 1502789745 | 1502789745 | 615      | 615      | 615      |
      +-----------+------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+
      10 rows in set (0.00 sec)
 
      (This email address is being protected from spambots. You need JavaScript enabled to view it.) [world]>USE information_schema;
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
 
      Database changed
      (This email address is being protected from spambots. You need JavaScript enabled to view it.) [information_schema]>Select * from world.City order by Population DESC limit 5 ;
      +------+-----------------+-------------+--------------+------------+
      | ID   | Name            | CountryCode | District     | Population |
      +------+-----------------+-------------+--------------+------------+
      | 1024 | Mumbai (Bombay) | IND         | Maharashtra  |   10500000 |
      | 2331 | Seoul           | KOR         | Seoul        |    9981619 |
      |  206 | São Paulo       | BRA         | São Paulo    |    9968485 |
      | 1890 | Shanghai        | CHN         | Shanghai     |    9696300 |
      |  939 | Jakarta         | IDN         | Jakarta Raya |    9604900 |
      +------+-----------------+-------------+--------------+------------+
      5 rows in set (0.01 sec)
 
 
 
 
      mysql> select * from stats_mysql_query_digest;
      +-----------+--------------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+
      | hostgroup | schemaname         | username | digest             | digest_text                                               | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
      +-----------+--------------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+
      | 10        | information_schema | uworld   | 0x99531AEFF718C501 | show tables                                               | 1          | 1502789929 | 1502789929 | 540      | 540      | 540      |
      | 10        | information_schema | uworld   | 0x02033E45904D3DF0 | show databases                                            | 1          | 1502789929 | 1502789929 | 897      | 897      | 897      |
      | 11        | world              | uworld   | 0x620B328FE9D6D71A | SELECT DATABASE()                                         | 1          | 1502789929 | 1502789929 | 537      | 537      | 537      |
      | 10        | world              | uworld   | 0x594F2C744B698066 | select USER()                                             | 1          | 1502789864 | 1502789864 | 0        | 0        | 0        |
      | 10        | world              | umyworld | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1          | 1502789849 | 1502789849 | 10264    | 10264    | 10264    |
      | 10        | world              | uworld   | 0x02033E45904D3DF0 | show databases                                            | 1          | 1502789863 | 1502789863 | 1750     | 1750     | 1750     |
      | 10        | world              | umyworld | 0x594F2C744B698066 | select USER()                                             | 1          | 1502789745 | 1502789745 | 0        | 0        | 0        |
      | 10        | world              | uworld   | 0x226CD90D52A2BA0B | select @@version_comment limit ?                          | 1          | 1502789863 | 1502789863 | 0        | 0        | 0        |
      | 10        | world              | uworld   | 0x99531AEFF718C501 | show tables                                               | 1          | 1502789863 | 1502789863 | 470      | 470      | 470      |
      | 10        | world              | umyworld | 0x226CD90D52A2BA0B | select @@version_comment limit ?                          | 1          | 1502789745 | 1502789745 | 0        | 0        | 0        |
      | 10        | world              | umyworld | 0x99531AEFF718C501 | show tables                                               | 1          | 1502789745 | 1502789745 | 7427     | 7427     | 7427     |
      | 10        | information_schema | uworld   | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1          | 1502789933 | 1502789933 | 3707     | 3707     | 3707     | <---
      | 11        | world              | uworld   | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1          | 1502789866 | 1502789866 | 3349     | 3349     | 3349     | <---
      | 10        | world              | umyworld | 0x02033E45904D3DF0 | show databases                                            | 1          | 1502789745 | 1502789745 | 615      | 615      | 615      |
      +-----------+--------------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+
      14 rows in set (0.01 sec)
 
      mysql> select active,hits,destination_hostgroup, 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 where destination_hostgroup < 30  ORDER BY mysql_query_rules.rule_id;
      +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+
      | active | hits | destination_hostgroup | rule_id | match_digest        | match_pattern | replace_pattern | cache_ttl | apply | flagIN | flagOUT |
      +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+
      | 1      | 0    | 10                    | 10      | ^SELECT.*FOR UPDATE | NULL          | NULL            | NULL      | 1     | 0      | NULL    |
      | 1      | 2    | 11                    | 11      | ^SELECT             | NULL          | NULL            | NULL      | 1     | 0      | NULL    |
      +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+
      2 rows in set (0.00 sec)
 

 

4 how can I transparently redirect using schema name?

This is not a Schema feature, more one of the things that in ProxySQL are quite easy to set, while close to be impossible if done in plain MySQL.
When connecting directly with MySQL there is no option for you to "play" with GRANTS and schema such that you will transparently allow a user to do an action on a schema/server pair and another on a different schema/server pair.
When using ProxySQL to filter by schemaname is quite trivial.

For instance assuming we have 3 users one is admin of the platform which include 2 separate schemas (or more), each user can access one schema for write (but that can be table as well), and a final slave with reporting information, where all the users that needs to read from other schema except their own can read cross schemas.
While all the select not cross schema mus still got to the Master.
This is not so uncommon, actually with few variant is exactly what one of the customer I spoke last week needs to do.

Let see what we need and how to do it:

  1. user(s) uworld & umyworld need to go to their default schema on Master for Writes.        
  2. user(s) uworld & umyworld should go to their default schema on  master for direct reads
  3. user(s) uworld & umyworld should go to the slave for reads when the other schema is used

To do this we will need the following rules:

 

1
2
3
4
5
6
7
8
9
10

        insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) values(10,'uworld',10,1,3,'^SELECT.*FOR UPDATE',1);
        insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply,FlagOUT,FlagIN) values(11,'uworld',10,1,3,'^SELECT ',0,50,0); 
        insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply,FlagOUT,FlagIN) values(12,'uworld',11,1,3,'\smyworld.',1,50,50);
 
 
        insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) values(13,'umyworld',10,1,3,'^SELECT.*FOR UPDATE',1);
        insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply,FlagOUT,FlagIN) values(14,'umyworld',10,1,3,'^SELECT ',0,50,0); 
        insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply,FlagOUT,FlagIN) values(15,'umyworld',11,1,3,'\sworld.',1,50,50);
 
        LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;

 

 

 

To check the behaviour:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

        select database();
        update world.City set Population=10500000 where ID=1024;
        update world.Country set LifeExpectancy=62.5 where Code='IND';
        update myworld.CityM set Population=10500001 where ID=1024;
        update myworld.CountryM set LifeExpectancy=0 where Code='IND';
 
        select database();
        Select * from world.City order by Population DESC limit 5 ;
        Select * from myworld.CityM order by Population DESC limit 5 ;
 
        Select City.*, Country.name, Country.LifeExpectancy from world.City as City join world.Country as Country on City.CountryCode=Country.code order by Population DESC limit 5; 
        Select City.*, Country.name, Country.LifeExpectancy from myworld.CityM as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit 5; 
 
        Select City.*, Country.name, Country.LifeExpectancy from world.City as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit 5; 
 

 

Once I run the test (queries above):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

        mysql> select active,hits,destination_hostgroup, 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 where destination_hostgroup < 30  ORDER BY mysql_query_rules.rule_id;
        +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+
        | active | hits | destination_hostgroup | rule_id | match_digest        | match_pattern | replace_pattern | cache_ttl | apply | flagIN | flagOUT |
        +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+
        | 1      | 0    | 10                    | 10      | ^SELECT.*FOR UPDATE | NULL          | NULL            | NULL      | 1     | 0      | NULL    |
        | 1      | 5    | 10                    | 11      | ^SELECT             | NULL          | NULL            | NULL      | 0     | 0      | 50      | <-- 5 selects in total OK
        | 1      | 3    | 11                    | 12      | myworld.            | NULL          | NULL            | NULL      | 1     | 50     | 50      | <-- 3 contains term myworld OK
        | 1      | 0    | 10                    | 13      | ^SELECT.*FOR UPDATE | NULL          | NULL            | NULL      | 1     | 0      | NULL    |
        | 1      | 0    | 10                    | 14      | ^SELECT             | NULL          | NULL            | NULL      | 0     | 0      | 50      |
        | 1      | 0    | 11                    | 15      | world.              | NULL          | NULL            | NULL      | 1     | 50     | 50      |
        +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+
        6 rows in set (0.00 sec)
 
 
 
        mysql> select * from stats_mysql_query_digest;                                                                                                                                         
        +-----------+------------+----------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
        | hostgroup | schemaname | username | digest             | digest_text                                                                                                                                                                       | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
        +-----------+------------+----------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
        | 11        | world      | uworld   | 0x921512ADAF79D0FF | Select City.*, Country.name, Country.LifeExpectancy from world.City as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit ?    | 1          | 1502718358 | 1502718358 | 6531     | 6531     | 6531     |
        | 11        | world      | uworld   | 0xE9D26001526F2618 | Select City.*, Country.name, Country.LifeExpectancy from myworld.CityM as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit ? | 1          | 1502718358 | 1502718358 | 6573     | 6573     | 6573     |
        | 10        | world      | uworld   | 0xE846287B5A6B3945 | Select City.*, Country.name, Country.LifeExpectancy from world.City as City join world.Country as Country on City.CountryCode=Country.code order by Population DESC limit ?       | 1          | 1502718358 | 1502718358 | 3181     | 3181     | 3181     |
        | 11        | world      | uworld   | 0x55FFF888F4642D3A | Select * from myworld.CityM order by Population DESC limit ?                                                                                                                      | 1          | 1502718358 | 1502718358 | 7753     | 7753     | 7753     |
        | 10        | world      | uworld   | 0x26DB674419D1E979 | update myworld.CountryM set LifeExpectancy=? where Code=?                                                                                                                         | 1          | 1502718358 | 1502718358 | 257      | 257      | 257      |
        | 10        | world      | uworld   | 0x056615DE2CFD8C8E | update myworld.CityM set Population=? where ID=?                                                                                                                                  | 1          | 1502718358 | 1502718358 | 235      | 235      | 235      |
        | 10        | world      | uworld   | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ?                                                                                                                         | 1          | 1502718358 | 1502718358 | 3262     | 3262     | 3262     |
        | 10        | world      | uworld   | 0x7A15CC342D54452D | update world.Country set LifeExpectancy=?.? where Code=?                                                                                                                          | 1          | 1502718358 | 1502718358 | 319      | 319      | 319      |
        | 10        | world      | uworld   | 0x500E6F01B02078B6 | update world.City set Population=? where ID=?                                                                                                                                     | 1          | 1502718358 | 1502718358 | 970      | 970      | 970      |
        +-----------+------------+----------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
 

 

As we can see with this simple set of rules my uworld user was able to perform exactly as expected and able to access the schema from the designated HG.       

All the selects with schema "myworld" were redirect to HG 11.

Results details:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

        (This email address is being protected from spambots. You need JavaScript enabled to view it.) [(none)]>update world.City set Population=10500000 where ID=1024;
        Query OK, 1 row affected (0.01 sec)
        Rows matched: 1  Changed: 0  Warnings: 0
 
        (This email address is being protected from spambots. You need JavaScript enabled to view it.) [(none)]>update world.Country set LifeExpectancy=62.5 where Code='IND';
        Query OK, 1 row affected (0.00 sec)
        Rows matched: 1  Changed: 0  Warnings: 0
 
        (This email address is being protected from spambots. You need JavaScript enabled to view it.) [(none)]>update myworld.CityM set Population=10500001 where ID=1024;      <--- I am NOT managing this but I could if needed
        ERROR 1142 (42000): UPDATE command denied to user 'uworld'@'192.168.1.50' for table 'CityM'
        (This email address is being protected from spambots. You need JavaScript enabled to view it.) [(none)]>update myworld.CountryM set LifeExpectancy=0 where Code='IND';   <--- I am NOT managing this but I could if needed
        ERROR 1142 (42000): UPDATE command denied to user 'uworld'@'192.168.1.50' for table 'CountryM'
        (This email address is being protected from spambots. You need JavaScript enabled to view it.) [(none)]>
 
        (This email address is being protected from spambots. You need JavaScript enabled to view it.) [(none)]>Select * from world.City order by Population DESC limit 5 ;
        +------+-----------------+-------------+--------------+------------+
        | ID   | Name            | CountryCode | District     | Population |
        +------+-----------------+-------------+--------------+------------+
        | 1024 | Mumbai (Bombay) | IND         | Maharashtra  |   10500000 |
        | 2331 | Seoul           | KOR         | Seoul        |    9981619 |
        |  206 | São Paulo       | BRA         | São Paulo    |    9968485 |
        | 1890 | Shanghai        | CHN         | Shanghai     |    9696300 |
        |  939 | Jakarta         | IDN         | Jakarta Raya |    9604900 |
        +------+-----------------+-------------+--------------+------------+
        5 rows in set (0.00 sec)
 
        (This email address is being protected from spambots. You need JavaScript enabled to view it.) [(none)]>Select * from myworld.CityM order by Population DESC limit 5 ;
        +------+-----------------+-------------+--------------+------------+
        | ID   | Name            | CountryCode | District     | Population |
        +------+-----------------+-------------+--------------+------------+
        | 1024 | Mumbai (Bombay) | IND         | Maharashtra  |   10500001 |
        | 2331 | Seoul           | KOR         | Seoul        |    9981619 |
        |  206 | São Paulo       | BRA         | São Paulo    |    9968485 |
        | 1890 | Shanghai        | CHN         | Shanghai     |    9696300 |
        |  939 | Jakarta         | IDN         | Jakarta Raya |    9604900 |
        +------+-----------------+-------------+--------------+------------+
        5 rows in set (0.01 sec)
 
        (This email address is being protected from spambots. You need JavaScript enabled to view it.) [(none)]>
        (This email address is being protected from spambots. You need JavaScript enabled to view it.) [(none)]>Select City.*, Country.name, Country.LifeExpectancy from world.City as City join world.Country as Country on City.CountryCode=Country.code order by Population DESC limit 5; 
        +------+-----------------+-------------+--------------+------------+-------------+----------------+
        | ID   | Name            | CountryCode | District     | Population | name        | LifeExpectancy |
        +------+-----------------+-------------+--------------+------------+-------------+----------------+
        | 1024 | Mumbai (Bombay) | IND         | Maharashtra  |   10500000 | India       |           62.5 |
        | 2331 | Seoul           | KOR         | Seoul        |    9981619 | South Korea |           74.4 |
        |  206 | São Paulo       | BRA         | São Paulo    |    9968485 | Brazil      |           62.9 |
        | 1890 | Shanghai        | CHN         | Shanghai     |    9696300 | China       |           71.4 |
        |  939 | Jakarta         | IDN         | Jakarta Raya |    9604900 | Indonesia   |           68.0 |
        +------+-----------------+-------------+--------------+------------+-------------+----------------+
        5 rows in set (0.01 sec)
 
        (This email address is being protected from spambots. You need JavaScript enabled to view it.) [(none)]>Select City.*, Country.name, Country.LifeExpectancy from myworld.CityM as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit 5; 
        +------+-----------------+-------------+--------------+------------+-------------+----------------+
        | ID   | Name            | CountryCode | District     | Population | name        | LifeExpectancy |
        +------+-----------------+-------------+--------------+------------+-------------+----------------+
        | 1024 | Mumbai (Bombay) | IND         | Maharashtra  |   10500001 | India       |            0.0 |
        | 2331 | Seoul           | KOR         | Seoul        |    9981619 | South Korea |           74.4 |
        |  206 | São Paulo       | BRA         | São Paulo    |    9968485 | Brazil      |           62.9 |
        | 1890 | Shanghai        | CHN         | Shanghai     |    9696300 | China       |           71.4 |
        |  939 | Jakarta         | IDN         | Jakarta Raya |    9604900 | Indonesia   |           68.0 |
        +------+-----------------+-------------+--------------+------------+-------------+----------------+
        5 rows in set (0.00 sec)
 
        (This email address is being protected from spambots. You need JavaScript enabled to view it.) [(none)]>
        (This email address is being protected from spambots. You need JavaScript enabled to view it.) [(none)]>Select City.*, Country.name, Country.LifeExpectancy from world.City as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit 5; 
        +------+-----------------+-------------+--------------+------------+-------------+----------------+
        | ID   | Name            | CountryCode | District     | Population | name        | LifeExpectancy |
        +------+-----------------+-------------+--------------+------------+-------------+----------------+
        | 1024 | Mumbai (Bombay) | IND         | Maharashtra  |   10500000 | India       |            0.0 |
        | 2331 | Seoul           | KOR         | Seoul        |    9981619 | South Korea |           74.4 |
        |  206 | São Paulo       | BRA         | São Paulo    |    9968485 | Brazil      |           62.9 |
        | 1890 | Shanghai        | CHN         | Shanghai     |    9696300 | China       |           71.4 |
        |  939 | Jakarta         | IDN         | Jakarta Raya |    9604900 | Indonesia   |           68.0 |
        +------+-----------------+-------------+--------------+------------+-------------+----------------+
        5 rows in set (0.00 sec)
 

 

Obviosuly doing it on the other user/schema work exacty the same.

 

Back to main article

 

Recently Fred published a post (http://lefred.be/content/mysql-group-replication-is-sweet-but-can-be-sour-if-you-misunderstand-it) in which he was stating, I had publish my blog (http://www.tusacentral.net/joomla/index.php/mysql-blogs/191-group-replication-sweet-a-sour.html) which contains few “misunderstanding”.

 

All the people that knows me, also know I have no problem to admit if I do a mistake, at the end doing mistake is a way of learning and be afraid of the ones who don’t do them.

But at the same time, I need to have proof of it. As I provide proof of what I wrote with numbers and tests (all available on github).

Let us put down the basis of the discussion with facts, not personal speculation or assertions.

Facts:

1) From MySQL official documentations (https://dev.mysql.com/doc/refman/5.7/en/group-replication-use-cases.html) 

Group Replication enables you to create fault-tolerant systems with redundancy by replicating the system state throughout a set of servers. Consequently, even if some of the servers fail, as long it is not all or a majority, the system is still available, and all it could have degraded performance or scalability, it is still available. Server failures are isolated and independent. They are tracked by a group membership service which relies on a distributed failure detector that is able to signal when any servers leave the group, either voluntarily or due to an unexpected halt. There is a distributed recovery procedure to ensure that when servers join the group they are brought up to date automatically. There is no need for server fail-over, and the multi-master update everywhere nature ensures that not even updates are blocked in the event of a single server failure. Therefore MySQL Group Replication guarantees that the database service is continuously available.

 

2) Still from MySQL (https://dev.mysql.com/worklog/task/?id=9838) Group Replication introduced flow control to avoid excessive buffering and to maintain group members reasonably close to one another. For several reasons, it's more efficient to keep buffering low but, as mentioned before, it is not a requirement that members are kept in sync for replication to work: once a slave becomes delayed it will just increase the number of pending transactions in the relay log.

The flow control mechanism enters the scene to bound the back-log members can accumulate, in terms of transactions to certify and transactions to apply. Once those bounds are exceeded, and while they remain exceeded, the flow-control mechanism limits the throughput of writer members to adjust to the capacity of the slowest members of the group. By imposing conservative bounds it tries to make sure all members are able to keep-up and can return safely to within the bounds defined by the user.

 

 

So given the above, I, as “standard” user, read that as “when using MySQL and GR, and setting (eventually) the Flow Control correctly, I will achieve to have a data platform that is continuously available given the use of GR”.

Cool, right? So what I was doing in my tests? Well two main things, one is to see if I can use GR for scaling reads as I was doing (and hundreds of customers as well) with PXC, the other is to see if in case of crash of my Master, I can safely fail-over another slave.

This doesn’t mean I am comparing the way the two product works. I cannot care less at this stage, as I am sure most of the customer will not care. What they care is what they can do SAFELY, and with what. All the mambo-jambo about the technical details (how much sync or async I can be) is not their concern.

So the point was and is… Given I am used to product X can I move to product Y and if so how and what I should be aware of?

Once more I was trying to answer to the question “If GR and InnoDB cluster has to work as alternative to other (virtually) synchronous replication mechanism, what change or shift our customers must consider if they want move from one to the other.”

The outcome of the tests is pointing to answer that, period.

 

Let us clarify two things more:

I am perfectly aware (Fred talking to you) that GR use a different mechanism for FC, and that the numbers set in the group_replication_flow_control_certifier_threshold/ group_replication_flow_control_applier_threshold are then use to calculate the Quota. Still they are user threshold, and Specifies the number of waiting transactions in the applier/certifier queue that trigger flow control which are connected to the final statements reported above: By imposing conservative bounds it tries to make sure all members are able to keep-up and can return safely to within the bounds defined by the user.
Bound that as for the manual can go from 0 to 2147483648. 
As such setting it to 25 (I did try also 1000 or more with even worse results) is perfectly legit and I have some issues in considering it a mistake.

 

I was measuring the lag with the only tools MySQL/Oracle had give us, in the article I said I had used:
“select  @last_rec:=SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( Received_transaction_set,':',-2),':',1),'-',-1) last_received FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier'; select (@last_rec - @last_exec) as real_lag"

Which use the only information available at the moment regarding the incoming and the current executed transactions.
Fred says that is not valid because the certification and This means that a transaction listed on last_received_transaction_set is certified and queued to apply on this member. On the other members it may already be certified/applied or soon be.
I agree that it may not be perfect .. and I should have said: is for sure the last apply or soon to be given the certification on the master a node can know about.
But here we are not talking of 1 or 10 entries, but in most cases lag of hundreds or thousands entries. So also if this is not perfect and I can miss in a way or another a couple or entries because still processing the certification, I still think it is a valid way to evaluate the lag given the dimensions we are talking about.

BTW if this is not … well please help us and provide the right way to calculate the REAL lag between each node in a GR cluster and the writing master, with the most accurate precision.

 

About the comment of the node dimension, well thanks and yes you are right here I forgot to put the details.

The four nodes are VMS on separated hosts, so the gr1/2 where hosted on Host1 while gr3/4 hosted on the another host. Nothing else running on the hosts while test was running. Applications and monitor where running on a 3 host. The exact dimension is not really relevant given all of them were the same.

I want to say that I can setup the perfect environment for GR or PXC or NDB or whatever you are testing and showing how cool that is.

But I think we should consider that real life is far to be like that, and that we must see/test is how a product is behaving in adverse conditions, or if not adverse challenging.

 

Given all the above, in my opinion the tests clearly shown that also if the Flow Control mechanism in GR is the coolest thing ever conceptualize, at the end it does not what it is suppose to, no matter what.

Was I setting the thresholds wrong? Well not sure about that, given the results I got.

If I have 4 nodes and 2 of them (so … no majority) are lagging behind of hundreds or even thousands of entries, while my threshold is in the order of hundreds or less, this means that the cool mechanism is not doing his job, period.

One think is write down whatever about this or that but another is doing tests and provide numbers and evidences, not just words.

As said in a comment (not published in the Fred blog) I am more than happy to perform more tests and do them in conjunctions with anyone else.

Then if I am wrong I am wrong… but until I have different evidence, just saying a car can fly because it has very nice and colorful wings, doesn’t make it fly.

chitty-1

 


A story around replication lag and Flow-Control.

Overview

In the last few months we had 2 main actors in the the MySQL ecosystem, ProxySQL and Group-Replication (with the evolution to InnoDB Cluster). 

While I had extensively covered the first, my last serious work on GR, goes back to some lab version in the past years. veg sweet and sour soup
Given the decision Oracle made to declare it GA, and the Percona decision to provide some level of support to GR, I decide it was time for me to take a look at it again.
A lot of reviews were already done covering different topics. I saw articles about GR and performance, GR and basic functionalities (or lack of it like automatic node provisioning), GR and ProxySQL and so on.

But one question was coming up over and over in my mind. If GR and InnoDB cluster has to work as alternative to other (virtually) synchronous replication mechanism, what change or shift our customers must consider if they want move from one to the other.
In solutions using Galera, like Percona Xtradb Cluster (PXC), there is a main concept to which all of us must refer to. The cluster is data-centric, which at the end brings us to what matters, which is the data and its state, that must be exactly the same on each node at a given time (commit/apply). To guarantee this PXC and others use a set of data validation and FlowControl that at the end will make possible to the cluster dataset to be consistent on each node, respecting the main principle (be data-centric).
Immediate application of this principle is that an application can query ANY node in the PXC and be sure to get the same data, or to write and know that the data will be visible on all node (virtually) at the same time.
Last but not list, if a node is not consistent with the others, it will be excluded and must be rebuild, or inconsistency fix, before joining back.

Not a minor thing if you think carefully, and a very useful thing to have because allow you to transparently split write/read operations, or failover from one node to another with lees troubles, and more.

When I thought to GR (or InnoDB Cluster), I put myself in the customer shoes, and I asked to the other myself: “Aside all the other things we know (see above) what is the real impact of moving form PXC to GR/Innodb-Cluster for my application? Because when you mention me that GR is still using (basically) replication with binlogs and relaylog, also if there is a Flow-Control mechanism an alarm bell started to ring in my mind.”

My Answer was: “Let us do a POC, and see what is really going on in that terms.”

{autotoc enabled=yes}

 

The POC

Given that I setup a simple set of servers using GR, with a very basic application performing writes on a single Writer node, and (eventually) reads on the other nodes.Schema definition can be found here, mainly I had use the 4 tables from my windmills test suite, nothing special, or weird of specifically design for GR. Actually I had use this test a lot for PXC in the past, so was a perfect fit.

Test definition

The application will do very simple work, and I had thought to test 4 main cases:

  • One thread performing one insert at each transaction.
  • One thread performing 50 batched inserts at each transaction.
  • 8 threads performing one insert to each transaction.
  • 8 threads performing 50 batched inserts at each transaction.

As you can see nothing crazy, a quite simple operation.
Then I decide to test it using the following 4 conditions on the servers:

  • Single slave worker FC as default
  • Single slave worker FC set to 25
  • 8 slave workers FC as default
  • 8 slave workers FC set to 25

Again nothing weird or strange from my point of view.I had used 4 nodes:

  • Gr1 Writer
  • Gr2 Reader
  • Gr3 Reader minimal latency (~10ms)
  • Gr4 Reader minimal latency (~10ms)

Finally, I had to be sure I measure the lag in a way that allow me to reference it in a consistent way on all nodes.
I think we can safely say that the incoming GTID (last_ Received_transaction_set from replication_connection_status) is for sure the last apply on the master a slave node can know about.
This because network delay can prevent the last one to really be "received".
The other point of reference is the GTID_EXECUTED which refers to the latest GTID processed on the Node itself.

The closest Query that can track the distance will be:

1

select @last_exec:=SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( @@global.GTID_EXECUTED,':',-2),':',1),'-',-1) last_executed;
select  @last_rec:=SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( Received_transaction_set,':',-2),':',1),'-',-1) last_received
FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier'; select (@last_rec - @last_exec) as real_lag

 

Or in case of a single worker

1

select @last_exec:=SUBSTRING_INDEX(SUBSTRING_INDEX( @@global.GTID_EXECUTED,':',-1),'-',-1) last_executed;
select  @last_rec:=SUBSTRING_INDEX(SUBSTRING_INDEX(Received_transaction_set,':',-1),'-',-1) last_received 
FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier'; select (@last_rec - @last_exec) as real_lag;

 

The result will be something like this:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

+---------------+
| last_executed |
+---------------+
| 23607         |
+---------------+
 
+---------------+
| last_received |
 
+---------------+
| 23607         |
+---------------+
 
+----------+
| real_lag |
 
+----------+
|        0 |
+----------+

 

The whole set of tests can be found here, with all the commands you may need to run the application (you can find it here), and replicate the tests.
I will focus on the results, or this blogpost will be far too long, but I invite you to see the details.


The results

Efficiency on Writer by execution time & Rows/secHere using the raw data from the tests (excel spreadsheet available here) I was interested to identify if and how the writer is affected by the use of GR and FC.

writer_efficency_bytime


Reviewing the Graph we can see that the Writer has a linear increase of the execution time (when using default FC), with the increase of the load, nothing really concerning and all in all expected also if the load is light, we will see after that the volume of rows at the end justify the execution time.

Different scenario if we use FC, the execution time increase significantly in both cases (single  worker/multiple workers). In the worth case (8 threads, 50 inserts batch) it becomes 4 time higher the same load without FC.
What happen to the inserted rows? In the application I trace the rows inserted/sec, as such is easy to see what is going on there as well.

writer_efficency_by_rows

We can see that the Writer with FC activated is able to insert less than 1/3 of the rows it can process without FC.We can definitely say, that FC has a significant impact on the Writer performance.
To clarify let see this graph:

Writer_difference_data_time_fc_nofc


Without FC the Writer is able to process a high volume of rows in a limited number of time (results from test 8 workers; 8 threads; 50 insert batch).
While with FC the situation changes drastically, the Writer will take a long time processing a significant smaller amount of rows/sec. In short performance will drop significantly.


But, hey I can be ok with that if this means to have a consistent data-set cross all the nodes.
At the end also PXC and similar, pay a significant price in performance to match the data-centric Principle.

Ok let see what happen on the other nodes.

Entries Lag

Well, the scenario is not so good.

Writer_slave_real_lag_all_tests

 

When NOT using FC, the nodes lag behind the writer significantly. Remember that by default the FC in GR is set to 25000 entries, I mean 25K of entries!!!

So what happens here is that as as soon as I put some salt (see load) on the Writer the slave nodes will start to lag.
When using the default single worker, that will be significant, while when using multiple workers, we will see that the lag will mainly happen only on the node(s) with a minimal (10ms network latency).
Sad thing is that is not really going down respect to the single thread worker, indicating the simple minimal latency of 10ms was enough to affect the replication.

Well time to activate the FC and have no lag.

Writer_slave_real_lag_all_tests_wFC

Unfortunately, this is not going to be the case. As we can see the lag in case of single worker remain high also in Gr2 (154 entries).
While when using multiple workers, the Gr3/4 nodes are able to perform much better, and lag is significantly less, but still high ~1k entries.

It is important to remember that at this time the Writer is processing 1/3 or less of the rows it is normally able to do. It is important to note, that I had set 25 to the entry limit in the FC, and never the less the Gr3 (and Gr4) nodes are lagging more than 1K entries behind.

To clarify, let check the two graphs below:

Writer_slave_distance_without_FC

 

Using the Writer (Master) as baseline in entry #N, without FC, the nodes (slaves) using GR, will start to lag behind the writer, in a significant way also with light load. The distance in this POC was from very minimal with 58 entries, up to 3849 entries in the case of higher load.

Writer_slave_distance_with_FC


Using FC the Writer (Master) will diverge less, as expected, but also if it will have a significant drop in performance (1/3 or less), the Nodes will lag anyhow, worse case up to 1363 entries.Need to underline that we have not further way (I am aware of) to tune the lag and prevent it to happen.
Which means an application cannot transparently split Writes/Reads and expect consistency. The gap will be too high


A graph that tell us a story

I was using PMM to keep an eye on the nodes while doing the tests. And one of the graph was really telling me that GR has still some “limits” if we want to consider it as the replication mechanism for a cluster.

test8


This graph shows the MySQL Queries executed on all the 4 nodes, in the 8-50 threads-batch with FC test.As you can see the Gr1 (the writer) is the first one to takeoff, followed by Gr2, the nodes Gr3 and Gr4 will require a bit more, given the binlog transmission (and 10ms delay), once the data is there, they match (inconsistently) the Gr2 node, this is an effect of the FC asking the master to slow down. But as seen previously, the nodes will never be able to match the Writer. At the end, when the load test is over, the nodes will continue to process the queue for additional ~130 sec. Considering that the whole load takes 420 sec on the Writer, this means that 1/3 of the total time on the Writer is spent AFTER on the slave to sync.

test4


The above graph shows the same test without FC, is interesting to see how the Writer was going above the 300 Queries/sec while G2 stay around 200 and Gr3/4 far below. The writer was able to process the whole load in ~120 seconds instead 420, while Gr3/4 continue to process the load for additional ~360 seconds.This means that without FC set the Nodes will lag around 360 seconds behind the Master, while with FC set to 25, they will lag 130 seconds.


A significant gap.

businessman_across_gap_cliff

 

Conclusions

Going back to the origin, and the reason why I was looking to this POC.
I, as customer of myself, think that my application(s) will not be a good fit for GR, given I have set PXC to scale out the reads, and be able to efficiently move my writer to another when in need to.
GR while based on a very interesting concept it is still based on asynchronous replication (as my colleague Kenny said). I am sure it could make sense in many other cases, but it cannot be compare to solutions that are based on virtually synchronous replication; and it still requires a lot of refinement.

 

On the other hand, all the applications that can afford to have a significant gap between writer and readers, will probably be fine with that.But I raise another question … was not standard replication already covering that?Reviewing the Oracle documentations (https://dev.mysql.com/doc/refman/5.7/en/group-replication-background.html) I can see why GR as part of the InnoDB cluster, can help me in improving HA, when comparing it to standard replication.But I also think it is important to understand that GR (and derived solutions like InnoDB cluster) are not comparable or a replacement of data-centric solutions as PXC. At least up to now.

 

Good MySQL to everyone.

 

References

https://dev.mysql.com/doc/refman/5.7/en/group-replication.html

https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-userguide.html

http://lefred.be/content/mysql-group-replication-understanding-flow-control/

https://dev.mysql.com/worklog/task/?id=9838

  1. Overview
  2. Setup
  3. ProxySQL setup
  4. Tests results
    1. Test1
    2. Test 2.
    3. Test 4
    4. Test 7 Crud
  5. Conclusions
  6. Acknowledgments

 

Overview

I love ProxySQL, I think it is a great component for expanding architecture flexibility and HA, but not all what shine is gold.

Let me make clear that I only want to set the expectations right, and avoid to sell carbon for gold. Carbon has it's own use, gold has another. 

 

First of all let me clarify what is mirroring for me (and hope most of you).

Then we need to cover the basic of how ProxySQL manage the (I cannot say mirroring) traffic dispatch.

ProxySQL receive a connection from the application, and through it we can have a simple SELECT or a more complex transaction. ProxySQL will get each query, pass it to the QueryProcessor, process the query, identify if the query is mirrored, duplicate the whole mysql session ProxySQL internal object and associate it to a mirror queue, which refer to a mirror threads pool.


If the pool is free (has an available active slot in the concurrent active threads set) then the query is processed right away, if not it will stay in the queue. If the queue is full, the query is lost.

Whatever is returned from the query goes to /dev/null, as such no result set is passed back to client.

The whole process is not free for a server, actually if you will check the CPU utilization you will see that the “mirroring” in ProxySQL will actually double the CPU utilization. Meaning that also the traffic on server A will be impacted because resource contention.

Summarizing ProxySQL will:

  • Send the query for execution in different order
  • Completely ignore any transaction isolation
  • Have different number of query executed on B respect to A
  • Add significant load on the server resources

Comparing this with the point and expectations I mention in the reasoning at the end of this article, it is quite clear to me that at the moment we cannot consider ProxySQL as a valid mechanism to duplicate consistent load from server A to server B.

 

Personally, I don’t think that ProxySQL development Team (Rene’ :D), should waste his time on fixing this part, there are so many other things to cover and improve on ProxySQL.

After having work extensively with ProxySQL and have done deep QA on mirroring, I think that either we keep it as basic blind traffic dispatcher or a full re-conceptualization is required.

Setup

But once we have clarified that, we can still see ProxySQL “traffic dispatch” (cannot say mirroring really) as a very interesting feature, that may result useful in many ways, especially because it is so easy to setup.

The following is the result of tests I had performed, which should help in setting correct expectations.

Tests were simple, load data in a PXC cluster and use ProxySQL to replicate the load on a MySQL master-slave environment.

Machines for MySQL/PXC where VM with CentOS 7, 4 CPU 3 GB RAM, attached storage.

Machine for ProxySQL VM CentOS 7, 8 CPU 8GB RAM.

Why I choose to give ProxySQL such higher  volume of resources?

I knew in advance I may need to play a bit with a couple of settings requiring more memory and CPU cycles and I want to be sure I don’t get any problem from ProxySQL in relation to CPU and Memory.

The application that I was using to add load is a Java application I develop to perform my tests. App is https://github.com/Tusamarco/blogs/blob/master/stresstool_base_app.tar.gz, the whole set I had used to do the test is here

I had used 4 different tables,

1
2
3
4
5
6
7
8

+------------------+
| Tables_in_mirror |
+------------------+
| mirtabAUTOINC    |
| mirtabMID        |
| mirtabMIDPart    |
| mirtabMIDUUID    |

 

For full table definition see here

 

ProxySQL setup

Ok so let start.

First setup ProxySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

DELETE FROM mysql_servers WHERE hostgroup_id IN (500,501,700,701);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.5',500,3306,60000,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.5',501,3306,100,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.21',501,3306,20000,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.231',501,3306,20000,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.7',700,3306,1,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.7',701,3306,1,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.25',701,3306,1,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.43',701,3306,1,400);
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

DELETE FROM mysql_users WHERE username='load_RW';
INSERT INTO mysql_users (username,password,active,default_hostgroup,default_schema,transaction_persistent) VALUES ('load_RW','test',1,500,'test',1);
LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS TO DISK;

DELETE FROM mysql_query_rules WHERE rule_id=202;
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,mirror_hostgroup,active,retries,apply) VALUES(202,'load_RW',500,700,1,3,1);
LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK

 

Tests results

Now the action. 

Test1

The first test is mainly a simple functional test during which I insert records using 1 single thread in PXC and in MySQL.

No surprise here I have 3000 loops and at the end I have 3000 records on both platforms.

To have a baseline we can see that ProxySQL CPU utilization is quite low

cpu_proxy

 

AT the same time the number of "questions" against PXC and MySQL very similar:
PXC

question_galera

MySQL

question_mysql

 

The other two metrics we want to keep an eye on are Mirror_concurrency and Mirror_queue_length this two refer respectively to mysql-mirror_max_concurrency and mysql-mirror_max_queue_length.

The two new variables and metrics were introduced in ProxySQL 1.4.0 with the intent to control and manage the load ProxySQL generate internally related to the mirroring feature.

 

queue_proxy

In this case as you can see we have a max of 3 concurrent connections and 0 queue entry, all good.

Now that we have a baseline, and that we know at functional level "it works" let see what happen increasing the load.

 

Test 2.

Scope of the test was to identify how ProxySQL will behave with standard configuration and increasing load.

It comes up that as soon as ProxySQL has a little bit more load, it will start to loose some query along the way.

Executing 3000 loop for 40 threads, insert only will result in 120,000 rows inserted in all the 4 tables in PXC but the table in the Secondary (mirrored) platform will only have a variable number between 101,359 and 104,072. Showing consistent lost of data.

Reviewing the insight and comparing the connections running in PXC and the secondary we can see that, as expected, the PXC number of connections is scaling serving the number of incoming requests, while the connections on the Secondary are limited by the default value of mysql-mirror_max_concurrency=16.

test2

 

Is also interesting to notice that to process the queue of transaction existing in ProxySQL the connection on the Secondary persist longer than the connection in PXC.

queue_proxy

 

As we can see above the queue as an evident bell curve reaching the 6K entries which is quite below the mysql-mirror_max_queue_length limit (32K). Yet the queries were drop by ProxySQL, which indicate the queue is not really enough to accommodate the pending work.

 

cpu_proxy

CPU wise ProxySQL as expected take a bit more cycles, but nothing crazy and the overhead for the simple mirroring queue processing can be see when the main load stops around 12:47.

 

Another interesting graph to keep an eye on is the one describing the executed command inside PXC and the Secondary:

PXC

commands_galera

Secondary

commands_mysql

 

As you can see the traffic on the Secondary was significantly less 669 average than PXC 1.17K. Then it spikes when the main load on the PXC node terminates.

In short it is quite clear that ProxySQL is not able here to scale following the traffic existing in PXC and actually loosing significant amount of data on the Secondary.

Doubling the load in the Test3 show the same behavior, having ProxySQL reach his limit for the traffic duplication.

 

But can this be optimized?

Of course yes, this is what the mysql-mirror_max_concurrency is for, lets see what is going to happen if I increase the value from 16 to 100 just to make it crazy high.

 

Test 4

2 app node writing.

I am jumping the description of test 3 because is mainly the same of Test 2 with more load.

 

The first thing that is coming to the attention is that both PXC and secondary report same number of rows in the tables (240,000). That is a first good win.

 

Second the number of running connections:

test4

Lines now are much closer and the queue just drop to few entries.

 

Commands executed in PXC:

command_galera

And commands executed in the Secondary:

command_mysql

Average execution report the same value, and very similar trend.

 

Finally, what was the CPU cost and effect?

PXC:

cpu_galera

 

Secondary:

cpu_mysql

As expected some difference in the CPU usage distribution exists, but the trend is consistent between the two nodes and with the operations.

 

The ProxySQL CPU utilization is definitely higher than before:

cpu_proxy

But absolutely manageable, and still reflecting the initial distribution.

 

Finally what about CRUD now?
So far I had only tested the insert operation, but what happen if we run a full CRUD set of tests?

 

Test 7 Crud

First of all, let us review the executed commands, in PXC

commands_galera

And Secondary

commands_mysql

While in appearance we have very similar workload, selects aside the behavior will significantly diverge.
This because in the Secondary the different operations are not encapsulated by the transaction and executed as they are received.  We can see significant difference in update and delete operations between the two.

 

Also the threads in execution will show a different picture between the two platform.

PXC

threads_galera

Secondary

threads_mysql

It appears quite clear that PXC is constantly having more running threads and more connections.

Never the less both platform process similar total number of questions.

PXC

questions_galera

Secondary

questions_mysql

Both having an average around 1.17K/second questions.

This is also another indication of how much the behavior is impacted when we have concurrent operation but no respect of the isolation or execution order.

Different behavior that is also clear reviewing the CPU utilization.

PXC

cpu_galera

Secondary

cpu_mysql

 

Conclusions

To close this long article, I want to go back to the start.

We cannot consider the mirror function in ProxySQL as a real mirroring, but more a traffic redirection.

 

Use ProxySQL with this approach, is still partially effective in testing the load and the effect it may have on a secondary platform.
As we know data consistency is not guarantee in this scenario, and Selects, Updates and Delete are affected by this given the different data-set and result-set they will manage.

Given that, the server behaviors will change between original and mirror, if not in the quantity in the quality.

I am convinced that when we need a tool able to test our production load on a different or new platform, we will do better to look to something else, like Query Playback recently reviewed and significantly patch by DropBox (https://github.com/Percona-Lab/query-playback).

At the end ProxySQL is already a cool tool and if it doesn't cover this, well I can live with that, I am interested to have it working as it should and does in many other functionalities.

 

Acknowledgments

As usual to Rene' who had work on fixing and introducing new functionalities associated to the mirror, like the queue and concurrency control.

To the Percona team who develop PMM, all the graphs here (except 3) comes from PMM, and some of them are my customization.

If you met one of the (few) MySQL consultants around the globe and asked him/her to review your queries and/or schemas, I am sure that he/she would tell you something regarding the importance of good primary key(s) design. Especially in the case of InnoDB, I’m sure they started to explain to you about index merges and page splits. These two notions are closely related to performance, and you should take this relationship into consideration when designing any index (not just PKs).

That may sound like mumbo jumbo to you, and you may be right. This is not easy stuff, especially when talking about internals. This is not something you deal with on a regular basis, and often you don’t want to deal with it at all.

But sometimes it’s a necessity. If so, this article is for you.

In this article, I want to shed some light in explaining some of the most unclear, behind the scenes operations in InnoDB: page index creation, page merging and page splitting.

In Innodb all data is an index. You’ve probably heard that as well right? But what exactly does that mean?

 

File-Table Components

Let's say you have MySQL installed, the latest 5.7 version (Percona Server for MySQL, right?  ), and you have a table named wmills in the schema windmills. In the data directory (normally /var/lib/mysql/) you will see that it contains:

 

data/  
windmills/
     wmills.ibd
     wmills.frm

 

This is because the parameter innodb_file_per_table is set to 1 since MySQL 5.6. With that setting, each table in your schema is represented by one file (or many files if the table is partitioned).

What is important here is that the physical container is a file named wmills.ibd. This file is broken up into and contains N number of segments. Each segment is associated with an index.

While a file’s dimensions do not shrink with row-deletions, a segment itself can grow or shrink in relation to a sub-element named extent. An extent can only exist inside a segment and has a fixed dimension of 1MB (in the case of default page size). A page is a sub-element of an extent and has a default size of 16KB.

Given that, an extent can contain a maximum of 64 pages. A page can contain two to N number of rows. The number of rows a page can contain is related to the size of the row, as defined by your table schema. There is a rule within InnoDB that says, at minimum, two rows must fit into a page. Therefore, we have a row-size limit of 8000 bytes.

If you think this sounds like Matryoshka dolls, you are right! An image might help:

segment_extent

InnoDB uses B-trees to organize your data inside pages across extents, within segments.

Roots, Branches, and Leaves

Each page (leaf) contains 2-N rows(s) organized by the primary key. The tree has special pages to manage the different branch(es). These are known as internal nodes (INodes).

Bplustree

This image is just an example, and is not indicative of the real-world output below.

Let’s see the details:

 

ROOT NODE #3: 4 records, 68 bytes
 NODE POINTER RECORD ≥ (id=2) → #197
 INTERNAL NODE #197: 464 records, 7888 bytes
 NODE POINTER RECORD ≥ (id=2) → #5
 LEAF NODE #5: 57 records, 7524 bytes
 RECORD: (id=2) → (uuid="884e471c-0e82-11e7-8bf6-08002734ed50", millid=139, kwatts_s=1956, date="2017-05-01", lo

 

 

Below is the table structure:

 

CREATE TABLE `wmills` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `uuid` char(36) COLLATE utf8_bin NOT NULL,
  `millid` smallint(6) NOT NULL,
  `kwatts_s` int(11) NOT NULL,
  `date` date NOT NULL,
  `location` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `active` tinyint(2) NOT NULL DEFAULT '1',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `strrecordtype` char(3) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_millid` (`millid`)
) ENGINE=InnoDB;

 

All styles of B-trees have a point of entry known as the root node. We’ve identified that here as page #3. The root page contains information such as index ID, number of INodes, etc. INode pages contain information about the pages themselves, their value ranges, etc. Finally, we have the leaf nodes, which is where we can find our data. In this example, we can see that leaf node #5 has 57 records for a total of 7524 bytes. Below that line is a record, and you can see the row data.

The concept here is that while you organize your data in tables and rows, InnoDB organizes it in branches, pages, and records. It is very important to keep in mind that InnoDB does not work on a single row basis. InnoDB always operates on pages. Once a page is loaded, it will then scan the page for the requested row/record.

Is that clear up to now? Good. Let’s continue.

Page Internals

A page can be empty or fully filled (100%). The row-records will be organized by PK. For example, if your table is using an AUTO_INCREMENT, you will have the sequence ID = 1, 2, 3, 4, etc.

Locality_1

A page also has another important attribute: MERGE_THRESHOLD. The default value of this parameter is 50% of the page, and it plays a very important role in InnoDB merge activity:

Locality_2

While you insert data, the page is filled up sequentially if the incoming record can be accommodated inside the page.

When a page is full, the next record will be inserted into the NEXT page:

Locality_4

Given the nature of B-trees, the structure is browsable not only top-down following the branches, but also horizontally across the leaf nodes. This is because each leaf node page has a pointer to the page that contains the NEXT record value in the sequence.

For example, Page #5 has a reference to the next page, Page #6. Page #6 has references backward to the previous page (Page #5) and a forward to the next page (Page #7).

This mechanism of a linked list allows for fast, in-order scans (i.e., Range Scans). As mentioned before, this is what happens when you are inserting and have a PK based on AUTO_INCREMENT. But what happens if I start to delete values?

 

Page Merging

When you delete a record, the record is not physically deleted. Instead, it flags the record as deleted and the space it used becomes reclaimable.

Locality_3

When a page has received enough deletes to match the MERGE_THRESHOLD (50% of the page size by default), InnoDB starts to look to the closest pages (NEXT and PREVIOUS) to see if there is any chance to optimize the space utilization by merging the two pages.

Locality_4

In this example, Page #6 is utilizing less than half of its space. Page #5 received many deletes and is also now less than 50% used. From InnoDB’s perspective, they are mergeable:

Locality_5

The merge operation results in Page #5 containing its previous data plus the data from Page #6. Page #6 becomes an empty page, usable for new data.

Locality_6

The same process also happens when we update a record and the size of the new record brings the page below the threshold.

The rule is: Merges happen on delete and update operations involving close linked pages.

If a merge operation is successful, the index_page_merge_successful metric in INFORMATION_SCHEMA.INNODB_METRICS is incremented.

 

Page Splits

As mentioned above, a page can be filled up to 100%. When this happens, the next page takes new records.

But what if we have the following situation?

Locality_7

Page #10 doesn’t have enough space to accommodate the new (or updated) record. Following the next page logic, the record should go on Page #11. However:

Locality_9

Page #11 is also full, and data cannot be inserted out of order. So what can be done?

Remember the linked list we spoke about? At this moment Page #10 has Prev=9 and Next=11.

What InnoDB will do is (simplifying):

  1. Create a new page
  2. Identify where the original page (Page #10) can be split (at the record level)
  3. Move records
  4. Redefine the page relationships

Locality_8

A new Page #12 is created:

Locality_10

Page #11 stays as it is. The thing that changes is the relationship between the pages:

  • Page #10 will have Prev=9 and Next=12
  • Page #12 Prev=10 and Next=11
  • Page #11 Prev=12 and Next=13

The path of the B-tree still sees consistency since it is following a logical organization. However, physically the page is located out of order, and in most cases in a different extent.

As a rule we can say: Page splits happens on Insert or Update, and cause page dislocation (in many cases on different extents).

InnoDB tracks the number of page splits in INFORMATION_SCHEMA.INNODB_METRICS. Look for index_page_splits and index_page_reorg_attempts/successful metrics.

Once the split page is created, the only way to move back is to have the created page drop below the merge threshold. When that happens, InnoDB moves the data from the split page with a merge operation.

The other way is to reorganize the data by OPTIMIZE the table. This can be a very heavy and long process, but often is the only way to recover from a situation where too many pages are located in sparse extents.

Another aspect to keep in mind is that during merge and split operations, InnoDB acquires an x-latch to the index tree. On a busy system, this can easily become a source of concern. This can cause index latch contention. If no merges and splits (aka writes) touch only a single page, this is called an “optimistic” update in InnoDB, and the latch is only taken in S. Merges and splits are called “pessimistic” updates, and take the latch in X.

 

My Primary Key

A good Primary Key (PK) is not only important for retrieving data, but also correctly distributing the data inside the extents while writing (which is also relevant in the case of split and merge operations).

In the first case, I have a simple auto-increment. In the second my PK is based on an ID (1-200 range) and an auto-increment value. In my third, I have the same ID (1-200 range) but associate with a UUID.

When inserting, InnoDB must add pages. This is read as a SPLIT operation:

Page Merging and Page Splitting

The behavior is quite different depending on the kind of Primary Key I use.

The first two cases will have more “compact” data distribution. This means they will also have better space utilization, while the semi-random nature of the UUID will cause a significant “sparse” page distribution (causing a higher number of pages and related split operations).

In the case of merges, the number of attempts to merge is even more different by PK type.

merges_1

On Insert-Update-Delete operations, auto-increment has less page merge attempts and 9.45% less of a success ratio than the other two types. The PK with UUID (on the side other of the spectrum) has a higher number of merge attempts, but at the same time also a significantly higher success ratio at 22.34%, given that the “sparse” distribution left many pages partially empty.

The PK values with similar numbers also come from a secondary index.

Conclusion

MySQL/InnoDB constantly performs these operations, and you have very limited visibility of them. But they can bite you, and bite hard, especially if using a spindle storage VS SSD (which have different issues, by the way).

The sad story is there is also very little we can do to optimize this on the server side using parameters or some other magic. But the good news is there is A LOT that can be done at design time.

Use a proper Primary Key and design a secondary index, keeping in mind that you shouldn’t abuse of them. Plan proper maintenance windows on the tables that you know will have very high levels of inserts/deletes/updates.

This is an important point to keep in mind. In InnoDB you cannot have fragmented records, but you can have a nightmare at the page-extent level. Ignoring table maintenance will cause more work at the IO level, memory and InnoDB buffer pool.

You must rebuild some tables at regular intervals. Use whatever tricks it requires, including partitioning and external tools (pt-osc). Do not let a table to become gigantic and fully fragmented.

Wasting disk space? Need to load three pages instead one to retrieve the record set you need? Each search causes significantly more reads?
That’s your fault; there is no excuse for being sloppy!

Happy MySQL to everyone!

 

Acknowledgments

Laurynas Biveinis: who had the time and patience to explain some internals to me.

Jeremy Cole: for his project InnoDB_ruby (that I use constantly).

More Articles …

  1. Setup ProxySQL as High Available (and not a SPOF)
  2. ProxySQL – Percona Cluster (Galera) integration
  3. ProxySQL and MHA integration
  4. How to stop an offending query with ProxySQL
Page 14 of 25

Path

  2. Home
  3. Technical Tips
  4. MySQL

Latest conferences

We have 13286 guests and one member online

login

Remember Me