ProxySQL Features

33706 ワード
mysql proxysql テキストリンク

ProxySQL Features


ProxySQLは、MySQL用のオープンソース・コードの高性能、高可用性データベース・プロトコル・センシング・エージェントです.
ProxySQLの主な機能を見てみましょう.
詳細については、正式な書類またはgithubを参照してください.

Application Layer Proxy

  • Connection Multiplexing
  • Query Caching
  • Limiting the number of connections to a backend
  • Prioritizing traffic by changing the weight of a backend
  • Disabling a backend server (Gracefully/Immediately/Re-enabling an offline)
  • Query Logging
  • ProxySQL Statistics

    • Advanced Query Rules

  • Query Rules in MySQL
  • Read/Write Split (using different ports, regex, regex and digest)
  • Query Rewrite

    • Data Sharding & Transformation

  • Sharding in ProxySQL (User/Schema/Data based sharding)

    • Failover Detection

  • Backend’s health check
  • Monitor Module
  • Automatically shunning slaves with replication lag

    • Zero-downtime Changes


    Database Firewall


    ProxySQLのインストールについては、前の投稿を参照してください.

    Getting Started


    Configuring ProxySQL


    ProxySQLバインド設定は、プロファイルまたはデータベース(=sqlite)に格納されます.
    Configuring ProxySQL through the admin interface
    $ mysql -u radmin -pradmin -h 127.0.0.1 -P6032 --prompt='Admin> ' 
Admin>
    Configuring ProxySQL through the config file
    mysql_servers =
(
{
address="127.0.0.1"
port=3306
hostgroup=0
max_connections=200
}
)

    Exporting configuration into file

    # /etc/proxysql.cnf 

SELECT CONFIG INTO OUTFILE /tmp/f1
mysql -padmin -uadmin -h127.0.0.1 -P6032 -e 'select config file'

    Application Layer Proxy


    Connection Multiplexing


    Connection Multiplexingは、起点に大量の要求があるコネクタの場合、これらのコネクタの性質が似ている場合は、効率的に使用できるようにコネクタと見なす機能です.アプリケーションとDBMSの間で中間倉庫として機能し、接続多重化を提供します.ソース:hakurei.tistory
    一緒に見ればいい参考動画:NHN、200万銅板ゲームのMySQLサディン

    Query Caching


    Query Cacheは、mysql query rulesの各rule idのcache ttlを設定することによって設定できます.
    Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'msandbox','^SELECT c FROM sbtest1 WHERE id=\?$',2,1);
 
Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'msandbox','DISTINCT c FROM sbtest1',2,1);
    Admin> UPDATE mysql_query_rules set cache_ttl=5000 WHERE active=1 AND destination_hostgroup=2;
    Define traffic that needs to be cached
    Admin> INSERT INTO mysql_query_rules (rule_id,active,digest,cache_ttl,apply)
VALUES (5,1,'0xE8930CB2CC9E68D7',2000,1);
 
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;

    Limiting the number of connections to a backend


    mysql serversの各hostgroupidのmax connectionsを設定することで制御できます.
    Admin> SELECT hostgroup_id,hostname,max_connections FROM mysql_servers;
+--------------+------------+-----------------+
| hostgroup_id | hostname   | max_connections |
+--------------+------------+-----------------+
| 0            | 172.16.0.1 | 1000            |
| 1            | 172.16.0.2 | 1000            |
| 1            | 172.16.0.3 | 1000            |
+--------------+------------+-----------------+
3 rows in set (0.00 sec)
 
Admin> UPDATE mysql_servers SET max_connections=10 WHERE hostname='172.16.0.2';
 
Admin> SELECT hostgroup_id,hostname,max_connections FROM mysql_servers;
+--------------+------------+-----------------+
| hostgroup_id | hostname   | max_connections |
+--------------+------------+-----------------+
| 0            | 172.16.0.1 | 1000            |
| 1            | 172.16.0.2 | 10              |
| 1            | 172.16.0.3 | 1000            |
+--------------+------------+-----------------+
3 rows in set (0.00 sec)

    Prioritizing traffic by changing the weight of a backend


    重みはmysql serversのhostgroup id固有の重みを設定することで制御できます.
    Admin> SELECT hostgroup_id,hostname,weight FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname   | weight |
+--------------+------------+--------+
| 0            | 172.16.0.1 | 1      |
| 1            | 172.16.0.2 | 1      |
| 1            | 172.16.0.3 | 1      |
+--------------+------------+--------+
3 rows in set (0.00 sec)
 
Admin> UPDATE mysql_servers SET weight=1000 WHERE hostname NOT IN ('172.16.0.2', '172.16.0.1') AND hostgroup_id=1;
 
Admin> SELECT hostgroup_id,hostname,weight FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname   | weight |
+--------------+------------+--------+
| 0            | 172.16.0.1 | 1      |
| 1            | 172.16.0.2 | 1      |
| 1            | 172.16.0.3 | 1000   |
+--------------+------------+--------+
3 rows in set (0.00 sec)

    Disabling a backend server


    mysql_servers > status (ONLINE | OFFLINE_SOFT | OFFLINE_HARD)

    Gracefully disabling a backend server

    Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname   | status |
+--------------+------------+--------+
| 0            | 172.16.0.1 | ONLINE |
| 1            | 172.16.0.2 | ONLINE |
| 1            | 172.16.0.3 | ONLINE |
| 1            | 172.16.0.1 | ONLINE |
+--------------+------------+--------+
4 rows in set (0.00 sec)
 
Admin> UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostname='172.16.0.2';
 
Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------------+
| hostgroup_id | hostname   | status       |
+--------------+------------+--------------+
| 0            | 172.16.0.1 | ONLINE       |
| 1            | 172.16.0.2 | OFFLINE_SOFT |
| 1            | 172.16.0.3 | ONLINE       |
| 1            | 172.16.0.1 | ONLINE       |
+--------------+------------+--------------+
4 rows in set (0.00 sec)

    Immediately disabling a backend server

    Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------------+
| hostgroup_id | hostname   | status       |
+--------------+------------+--------------+
| 0            | 172.16.0.1 | ONLINE       |
| 1            | 172.16.0.2 | OFFLINE_SOFT |
| 1            | 172.16.0.3 | ONLINE       |
| 1            | 172.16.0.1 | ONLINE       |
+--------------+------------+--------------+
4 rows in set (0.00 sec)
 
Admin> UPDATE mysql_servers SET status='OFFLINE_HARD' WHERE hostname='172.16.0.1' AND hostgroup_id=1;
 
Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------------+
| hostgroup_id | hostname   | status       |
+--------------+------------+--------------+
| 0            | 172.16.0.1 | ONLINE       |
| 1            | 172.16.0.2 | OFFLINE_SOFT |
| 1            | 172.16.0.3 | ONLINE       |
| 1            | 172.16.0.1 | OFFLINE_HARD |
+--------------+------------+--------------+
4 rows in set (0.00 sec)

    Re-enabling an offline / disabled backend server

    Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------------+
| hostgroup_id | hostname   | status       |
+--------------+------------+--------------+
| 0            | 172.16.0.1 | ONLINE       |
| 1            | 172.16.0.2 | OFFLINE_SOFT |
| 1            | 172.16.0.3 | ONLINE       |
| 1            | 172.16.0.1 | OFFLINE_HARD |
+--------------+------------+--------------+
4 rows in set (0.00 sec)
 
Admin> UPDATE mysql_servers SET status='ONLINE' WHERE status NOT IN ('ONLINE');
 
Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname   | status |
+--------------+------------+--------+
| 0            | 172.16.0.1 | ONLINE |
| 1            | 172.16.0.2 | ONLINE |
| 1            | 172.16.0.3 | ONLINE |
| 1            | 172.16.0.1 | ONLINE |
+--------------+------------+--------+
4 rows in set (0.00 sec)

    Query Logging


    To enable logging in JSON format it is required to set mysql-eventslog_format=2.
    SET mysql-eventslog_format=2;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

    ProxySQL Statistics

  • stats.stats_mysql_connection_pool
  • stats_mysql_commands_counters
  • stats_mysql_query_digest 
    • Admin> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+----------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+----------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| 10        | mysql-01 | 3306     | ONLINE | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 379        |
| 20        | mysql-01 | 3306     | ONLINE | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 379        |
| 20        | mysql-02 | 3306     | ONLINE | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 715        |
| 20        | mysql-03 | 3306     | ONLINE | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 375        |
+-----------+----------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
4 rows in set (0.01 sec)
    Admin> SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
+---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| Command | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |
+---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| BEGIN   | 1921940       | 7249      | 4214      | 2106      | 570     | 340     | 14       | 5        | 0         | 0         | 0      | 0      | 0       | 0        |
| COMMIT  | 5986400       | 7249      | 119       | 3301      | 1912    | 1864    | 44       | 8        | 1         | 0         | 0      | 0      | 0       | 0        |
| DELETE  | 2428829       | 7249      | 325       | 5856      | 585     | 475     | 5        | 3        | 0         | 0         | 0      | 0      | 0       | 0        |
| INSERT  | 2260129       | 7249      | 356       | 5948      | 529     | 408     | 6        | 2        | 0         | 0         | 0      | 0      | 0       | 0        |
| SELECT  | 40461204      | 101490    | 12667     | 69530     | 11919   | 6943    | 268      | 149      | 13        | 1         | 0      | 0      | 0       | 0        |
| UPDATE  | 6635032       | 14498     | 333       | 11149     | 1597    | 1361    | 42       | 16       | 0         | 0         | 0      | 0      | 0       | 0        |
+---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
6 rows in set (0.00 sec)
    Admin> SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+-----------+--------------------+----------+----------------+--------------------+--------------------------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| hostgroup | schemaname         | username | client_address | digest             | digest_text                                            | count_star | first_seen | last_seen  | sum_time | min_time | max_time | sum_rows_affected | sum_rows_sent |
+-----------+--------------------+----------+----------------+--------------------+--------------------------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| 10        | information_schema | appuser  |                | 0xE1880433C2B1F950 | insert into testdb.insert_test select @@hostname,now() | 5          | 1615910122 | 1615910126 | 7449     | 941      | 3483     | 0                 | 0             |
| 10        | information_schema | appuser  |                | 0x226CD90D52A2BA0B | select @@version_comment limit ?                       | 5          | 1615910122 | 1615910126 | 0        | 0        | 0        | 0                 | 0             |
+-----------+--------------------+----------+----------------+--------------------+--------------------------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
2 rows in set (0.02 sec)
    Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+--------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                            |
+----+----------+------------+--------------------------------------------------------+
| 10 | 7449     | 5          | insert into testdb.insert_test select @@hostname,now() |
| 10 | 0        | 5          | select @@version_comment limit ?                       |
+----+----------+------------+--------------------------------------------------------+
2 rows in set (0.01 sec)

    Advanced Query Rules


    Query Rules in MySQL

    Admin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username='msandbox' ORDER BY rule_id;
+-------------------------------------+-----------------------+
| match_digest                        | destination_hostgroup |
+-------------------------------------+-----------------------+
| ^SELECT c FROM sbtest1 WHERE id=\?$ | 2                     |
| DISTINCT c FROM sbtest1             | 2                     |
+-------------------------------------+-----------------------+
2 rows in set (0.00 sec)
    Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                                          |
+----+----------+------------+----------------------------------------------------------------------+
| 2  | 14520738 | 50041      | SELECT c FROM sbtest1 WHERE id=?                                     |
| 2  | 3203582  | 5001       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1  | 3142041  | 5001       | COMMIT                                                               |
| 1  | 2270931  | 5001       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c          |
| 1  | 2021320  | 5003       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?                     |
| 1  | 1768748  | 5001       | UPDATE sbtest1 SET k=k+? WHERE id=?                                  |
| 1  | 1697175  | 5003       | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?                |
| 1  | 1346791  | 5001       | UPDATE sbtest1 SET c=? WHERE id=?                                    |
| 1  | 1263259  | 5001       | DELETE FROM sbtest1 WHERE id=?                                       |
| 1  | 1191760  | 5001       | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)              |
| 1  | 875343   | 5005       | BEGIN                                                                |
+----+----------+------------+----------------------------------------------------------------------+
11 rows in set (0.00 sec)

    Read/Write Split


    Read/write split using different ports

    INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)
VALUES (1,1,6401,10,1), (2,1,6402,20,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

    Basic read/write split using regex


    DO NOT USE THE ABOVE EXAMPLE IN PRODUCTION
    UPDATE mysql_users SET default_hostgroup=10; # by default, all goes to HG10
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK; # if you want this change to be permanent
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(1,1,'^SELECT.*FOR UPDATE$',10,1),
(2,1,'^SELECT',20,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

    Read/write split using regex and digest

    Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 15000 ORDER BY sum_time DESC LIMIT 5;
+--------------------+--------------------------+------------+---------------+----------+--------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      | avg_time | pct    |
+--------------------+--------------------------+------------+---------------+----------+--------+
| 0x38BE36BDFFDBE638 | SELECT instance.name as  | 59360371   | 1096562204931 | 18472    | 13.006 |
| 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390     | 185951894994  | 1270249  | 2.205  |
| 0x1DEFCE9DEF3BDF87 | SELECT DISTINCT i.extid  | 592281     | 40215136635   | 67898    | 0.477  |
| 0xDA8C56B5644C0822 | SELECT COUNT(*) as total | 44130      | 24842335265   | 562935   | 0.295  |
| 0x9EED412C6E63E477 | SELECT a.id as accountid | 961768     | 24116011513   | 25074    | 0.286  |
+--------------------+--------------------------+------------+---------------+----------+--------+
5 rows in set (0.00 sec)
    using digest
    INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)
VALUES
(1,1,'0x38BE36BDFFDBE638',20,1);
    using regex
    INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(1,1,'^SELECT COUNT\(\*\)',20,1);

    Query Rewrite

    Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                                          |
+----+----------+------------+----------------------------------------------------------------------+
| 1  | 8150528  | 5307       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c          |
| 1  | 7341765  | 5304       | COMMIT                                                               |
| 2  | 5717866  | 7860       | SELECT c FROM sbtest1 WHERE id=?                                     |
| 1  | 4807609  | 5307       | UPDATE sbtest1 SET k=k+? WHERE id=?                                  |
| 1  | 4164131  | 5308       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?                     |
| 1  | 3731299  | 5307       | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?                |
| 1  | 3156638  | 5305       | DELETE FROM sbtest1 WHERE id=?                                       |
| 1  | 3074430  | 5306       | UPDATE sbtest1 SET c=? WHERE id=?                                    |
| 2  | 2857863  | 1705       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1  | 2732332  | 5304       | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)              |
| 1  | 2165367  | 5310       | BEGIN                                                                |
| -1 | 0        | 3602       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| -1 | 0        | 45235      | SELECT c FROM sbtest1 WHERE id=?                                     |
+----+----------+------------+----------------------------------------------------------------------+
13 rows in set (0.00 sec)
    Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| hits  | rule_id | match_digest                        | match_pattern          | replace_pattern | cache_ttl | apply |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 48560 | 10      | ^SELECT c FROM sbtest1 WHERE id=\?  | NULL                   | NULL            | 5000      | 1     |
| 4856  | 20      | DISTINCT c FROM sbtest1             | NULL                   | NULL            | 5000      | 0     |
| 4856  | 30      | NULL                                | DISTINCT(.*)ORDER BY c | DISTINCT\1      | NULL      | 1     |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.01 sec)

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
    Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+-------------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                                 |
+----+----------+------------+-------------------------------------------------------------+
| 1  | 7240757  | 4856       | COMMIT                                                      |
| 1  | 6127168  | 4856       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 2  | 4264263  | 7359       | SELECT c FROM sbtest1 WHERE id=?                            |
| 1  | 4081063  | 4856       | UPDATE sbtest1 SET k=k+? WHERE id=?                         |
| 1  | 3497644  | 4856       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?            |
| 1  | 3270527  | 4856       | DELETE FROM sbtest1 WHERE id=?                              |
| 1  | 3193123  | 4856       | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?       |
| 1  | 3124698  | 4856       | UPDATE sbtest1 SET c=? WHERE id=?                           |
| 1  | 2866474  | 4856       | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)     |
| 1  | 2538840  | 4856       | BEGIN                                                       |
| 2  | 1889996  | 1633       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?   |
| -1 | 0        | 3223       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?   |
| -1 | 0        | 41201      | SELECT c FROM sbtest1 WHERE id=?                            |
+----+----------+------------+-------------------------------------------------------------+
13 rows in set (0.00 sec)

    Data Sharding & Transformation


    Sharding in ProxySQL


    User based sharding

    INSERT INTO mysql_users
(username, password, active, default_hostgroup, comment)
VALUES
('accounts', 'shard0_pass', 1, 0, 'Routed to the accounts shard'),
('transactions', 'shard1_pass', 1, 1, 'Routed to the transactions shard'),
('logging', 'shard2_pass', 1, 2, 'Routed to the logging shard');
 
LOAD MYSQL USERS RULES TO RUNTIME;
SAVE MYSQL USERS RULES TO DISK;

    Schema based sharding

    INSERT INTO mysql_query_rules (rule_id, active, schemaname,
destination_hostgroup, apply)
VALUES
(1, 1, 'shard_0', 0, 1),
(2, 1, 'shard_1', 1, 1),
(3, 1, 'shard_2', 2, 1);
 
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

    Data based sharding


    Data based sharding is the most complex type of sharding.

    Failover Detection


    Backend’s health check


    mysql serversは、RUNTIMEにロードする前に、テーブルの内容に基づいて接続およびマッピングモニタリングを行います.この方法は意図的である.これにより、ノードを本番に追加する前に基本ステータスチェックを実行できます.
    admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+----------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us    | connect_success_time_us | connect_error |
+----------+------+------------------+-------------------------+---------------+
| mysql-02 | 3306 | 1615909440358636 | 2963                    | NULL          |
| mysql-01 | 3306 | 1615909439688804 | 3129                    | NULL          |
| mysql-03 | 3306 | 1615909439018691 | 2516                    | NULL          |
| mysql-02 | 3306 | 1615909380144622 | 3530                    | NULL          |
| mysql-03 | 3306 | 1615909379616115 | 2158                    | NULL          |
| mysql-01 | 3306 | 1615909379087750 | 2622                    | NULL          |
+----------+------+------------------+-------------------------+---------------+
6 rows in set (0.01 sec)
    Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
+----------+------+------------------+----------------------+------------+
| hostname | port | time_start_us    | ping_success_time_us | ping_error |
+----------+------+------------------+----------------------+------------+
| mysql-02 | 3306 | 1615909499134205 | 320                  | NULL       |
| mysql-03 | 3306 | 1615909499048568 | 400                  | NULL       |
| mysql-01 | 3306 | 1615909498961603 | 217                  | NULL       |
| mysql-02 | 3306 | 1615909489209406 | 873                  | NULL       |
| mysql-01 | 3306 | 1615909489103038 | 947                  | NULL       |
| mysql-03 | 3306 | 1615909488996409 | 484                  | NULL       |
| mysql-01 | 3306 | 1615909479218781 | 354                  | NULL       |
| mysql-02 | 3306 | 1615909479107152 | 318                  | NULL       |
| mysql-03 | 3306 | 1615909478995644 | 238                  | NULL       |
| mysql-03 | 3306 | 1615909469260573 | 574                  | NULL       |
+----------+------+------------------+----------------------+------------+
10 rows in set (0.00 sec)
    LOAD MYSQL SERVERS TO RUNTIME;

Admin> SELECT * FROM mysql_servers;
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | mysql-01 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | mysql-01 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | mysql-02 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | mysql-03 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

    Monitor Module


    The Monitor Module is responsible for a series of checks against the backends.
    It currently supports 4 types of checks:
  • connect : it connects to all the backends, and success/failure is logged in table mysql_server_connect_log;
  • ping : it pings to all the backends, and success/failure is logged in table mysql_server_ping_log . In the case of mysql-monitor_ping_max_failures missing a heartbeat, it sends a signal to MySQL_Hostgroups_Manager to kill all connections;
  • replication lag : it checks Seconds_Behind_Master to all backends configured with max_replication_lag greater than 0, and the check is logged in table mysql_server_replication_lag_log. If Seconds_Behind_Master > max_replication_lag the server is shunned until Seconds_Behind_Master < max_replication_lag ;
  • read only : it checks read_only for all hosts in the hostgroups in table mysql_replication_hostgroups, and the check is logged in table mysql_server_read_only_log . If read_only=1 the host is copied/moved to the reader_hostgroup, while if read_only=0 the host is copied/moved to the writer_hostgroup .

    • Automatically shunning slaves with replication lag

    Admin> SELECT hostgroup_id,hostname,max_replication_lag FROM mysql_servers;
+--------------+------------+---------------------+
| hostgroup_id | hostname   | max_replication_lag |
+--------------+------------+---------------------+
| 0            | 172.16.0.1 | 0                   |
| 1            | 172.16.0.2 | 0                   |
| 1            | 172.16.0.3 | 0                   |
+--------------+------------+---------------------+
3 rows in set (0.00 sec)
 
Admin> UPDATE mysql_servers SET max_replication_lag=30 WHERE hostname='172.16.0.3';
 
Admin> SELECT hostgroup_id,hostname,max_replication_lag FROM mysql_servers;
+--------------+------------+---------------------+
| hostgroup_id | hostname   | max_replication_lag |
+--------------+------------+---------------------+
| 0            | 172.16.0.1 | 0                   |
| 1            | 172.16.0.2 | 0                   |
| 1            | 172.16.0.3 | 30                  |
+--------------+------------+---------------------+
3 rows in set (0.00 sec)
    
## Removing a backend server
    Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
    +--------------+------------+--------+
    | hostgroup_id | hostname | status |
    +--------------+------------+--------+
    | 0 | 172.16.0.1 | ONLINE |
    | 1 | 172.16.0.2 | ONLINE |
    | 1 | 172.16.0.3 | ONLINE |
    | 1 | 172.16.0.1 | ONLINE |
    +--------------+------------+--------+
    4 rows in set (0.00 sec)
    Admin> DELETE FROM mysql_servers WHERE hostgroup_id=1 AND hostname IN ('172.16.0.1','172.16.0.2');
    Query OK, 2 rows affected (0.00 sec)
    Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
    +--------------+------------+--------+
    | hostgroup_id | hostname | status |
    +--------------+------------+--------+
    | 0 | 172.16.0.1 | ONLINE |
    | 1 | 172.16.0.3 | ONLINE |
    +--------------+------------+--------+
    2 rows in set (0.00 sec)
    

> 자세한 정보는 [공식문서](https://proxysql.com/documentation/) 또는 [github](https://github.com/sysown/proxysql)을 참고 부탁드립니다.

> 이미지 출처 : https://www.percona.com/blog/2018/06/11/proxysql-experimental-feature-native-clustering/
    linux hook demo
    [Spring]JPAとAWS RDSを統合