ProxySQL Tutorial High Performance & High Availability Proxy - PowerPoint PPT Presentation

ProxySQL Cluster - Config mysql> SHOW VARIABLES LIKE 'admin-cluster%'; mysql> SET admin-cluster_username='radmin'; +---------------------------------------------------+--------+ mysql> SET admin-cluster_password='radmin'; | Variable_name | Value | mysql> LOAD ADMIN VARIABLES TO RUNTIME; +---------------------------------------------------+--------+ | admin-cluster_username | radmin | mysql> SAVE ADMIN VARIABLES TO DISK; | admin-cluster_password | radmin | | admin-cluster_check_interval_ms | 1000 | mysql> INSERT INTO proxysql_servers (hostname) VALUES | admin-cluster_check_status_frequency | 10 | | admin-cluster_mysql_query_rules_diffs_before_sync | 3 | ('proxysql1'),('proxysql2'),('proxysql3'); | admin-cluster_mysql_servers_diffs_before_sync | 3 | mysql> LOAD PROXYSQL SERVERS TO RUNTIME; | admin-cluster_mysql_users_diffs_before_sync | 3 | mysql> SAVE PROXYSQL SERVERS TO DISK; | admin-cluster_proxysql_servers_diffs_before_sync | 3 | | admin-cluster_mysql_query_rules_save_to_disk | true | | admin-cluster_mysql_servers_save_to_disk | true | | admin-cluster_mysql_users_save_to_disk | true | | admin-cluster_proxysql_servers_save_to_disk | true | +---------------------------------------------------+--------+ 12 rows in set (0.00 sec) 52

ProxySQL Cluster - Monitoring Stats_proxysql_servers_checksums Stats_proxysql_servers_metrics 53

ProxySQL Cluster DEMO

MySQL Query rules

Query rewriting overview Sql-aware Regex-based flexible 56

Query rewriting examples read/write splitting Data masking Sharding 57

Query rewriting flow 58

Match Related Tables mysql_query_rules mysql_users • active • username • fast_forward(bypass) • schemaname • flagIN/OUT • client_addr • proxy_addr • proxy_port • digest • match_digest • match_pattern • negate_match_pattern

Action related fields flagOUT active replace_pattern destination_hostgroup cache_ttl timeout retries delay mirror_flagOUT mirror_hostgroup OK_msg error_msg l og multiplex apply

3 layers for ProxySQL tables Disk Memory Runtime Runtime Memory Config Disk

Precautions Confirm the query hits with stats_mysql_query_rules double check rules and rewrite results hold off on writing rules to disk until you're sure they are working as expected have a rollback plan • load from disk, • then load from memory to runtime test select query rules and rewrites on a slaves first test mutable rules on a mirror or other throw away db.

Example - Simple R/W mysql> SELECT username, default_hostgroup FROM mysql_users\G *************************** 1. row *************************** username: root default_hostgroup: 0 1 row in set (0.00 sec) mysql> SELECT match_digest, destination_hostgroup FROM mysql_query_rules\G *************************** 1. row *************************** match_digest: ^SELECT.*FOR UPDATE destination_hostgroup: 0 *************************** 2. row *************************** match_digest: ^SELECT destination_hostgroup: 1 63 2 rows in set (0.00 sec)

Query Rewriting with ProxySQL DEMO Click to add text

Mirroring with ProxySQL Click to add text

What is mirroring? 66

What is mirroring? 67

Why mirror queries? Validate performance on a different server using different hostgroups. Validate performance of query rewrite or schema change Pre-fetch slave replication (Replication Booster) 68

How to mirror mysql>SHOW CREATE TABLE mysql_query_rules\G *********************** 1. row ************************ table: mysql_query_rules Create Table: CREATE TABLE mysql_query_rules ( rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0, username VARCHAR, schemaname VARCHAR, flagIN INT NOT NULL DEFAULT 0, *snip* mirror_flagOUT INT UNSIGNED, mirror_hostgroup INT UNSIGNED, error_msg VARCHAR, 69 * snip *)

Mirroring flow 70

Mirroring - example #1 mysql> SELECT username, destination_hostgroup, mirror_hostgroup, mirror_flagOUT FROM mysql_query_rules WHERE username='plam_mirror'\G ****************** 1. row ********************** username: pl_mirror destination_hostgroup: 3 mirror_hostgroup: 4 71 mirror_flagOUT: NULL

Mirroring - example #2 72

Mirroring Demo Click to add text

Mirroring caveats Can be dropped: mysql-mirror_max_concurrency, mysql-mirror_max_queue_length Can be executed out of order No error handling of failed queries 74

Failover with ProxySQL

Failover highlights improve failover time as perceived by the application prevent errors sent to the application perform transparent database failovers: gracefully redirecting traffic without the application knowing existing applications do not have to be rewritten to autoreconnect since connections are not lost from failovers 76

Failover scenario - without ProxySQL 77

Failover scenario - without ProxySQL 78

Failover scenario - with ProxySQL 79

Failover scenario - with ProxySQL 80

Failover scenario - with ProxySQL 81

Failover scenario - with ProxySQL 82

Failover without ProxySQL (10 minute outage) 83 https://www.nylas.com/blog/growing-up-with-mysql/

Failover with ProxySQL (10 second outage) 84 https://www.nylas.com/blog/growing-up-with-mysql/

Failover - MySQL Servers mysql> SELECT hostgroup_id, hostname, status FROM mysql_servers WHERE hostname IN ('mysql1, 'mysql2’)\G *************************** 1. row *************************** hostgroup_id: 0 hostname: mysql1 status: ONLINE *************************** 2. row *************************** hostgroup_id: 1 hostname: mysql2 status: ONLINE 2 rows in set (0.00 sec) 85

Failover - Replication Hostgroups mysql> SELECT * FROM mysql_replication_hostgroups\G *************************** 1. row *************************** writer_hostgroup: 0 reader_hostgroup: 1 comment: 1 row in set (0.00 sec) 86

ProxySQL Failover ProTIP ProxySQL does not handle promotion or re-slaving External process needed, such as MHA or Orchestrator 87

Failover Demo Click to add text

New Features Click to add text

ClickHouse Server New in 1.4.3 – experimental (not fully completed, subject to change) Support for ClickHouse: https://clickhouse.yandex/ Fast, really fast data analytics using MySQL client Users management Supports only one backend Supports only DDL and SELECT (no INSERT)

ClickHouse Server

ClickHouse Server

SQLite3 Server New in 1.4.3 – experimental (not fully completed, subject to change) Support for SQLite3 : https://www.sqlite.org/ Fully transactional, in-memory Same users as MySQL backends A lot of features in the roadmap

You-Name-It Server What other service should become part of ProxySQL ?

You-Name-It Server What other service should become part of ProxySQL ?

Causal read with GTID Feature introduced in 2.0 Ensures that reads sent to slave returns what the client has written on master Requires GTID and MySQL 5.7+

ProxSQL Read / Write Split 97

ProxySQL Binlog Reader 98

ProxySQL Binlog Reader 99

10 0