Users Authentication Credentials stored in the proxy Credentials stored in the proxy User login always possible (even without backends) User login always possible (even without backends) Max connections Max connections Login credentials are encrypted Login credentials are encrypted Extremely scalable, up to millions of users: Extremely scalable, up to millions of users: https://www.percona.com/live/e17/sessions/scaling-million- https://www.percona.com/live/e17/sessions/scaling-million- databases-000webhost-lt databases-000webhost-lt 3 6
Hostgroups Manager Management of servers Management of servers T rack servers status T rack servers status Manages the connections pool Manages the connections pool 3 7
Connections Pool Reduced the overhead of creating new connections, and are Reduced the overhead of creating new connections, and are recycled when not in use recycled when not in use One to many connections One to many connections Multiplexing & maximum connections Multiplexing & maximum connections Auto-reconnect and automatic re-execution of queries Auto-reconnect and automatic re-execution of queries Failover management Failover management 3 8
Connections Pool 3 9
Connections Pool 4 0
Connection Pool Replies to ping. Replies to ping. From hundreds of millions of ping per day, to 0: From hundreds of millions of ping per day, to 0: https://www.percona.com/blog/2017/09/01/life360-used-proxysql-lower-database-load/ https://www.percona.com/blog/2017/09/01/life360-used-proxysql-lower-database-load/ 4 1
Purge Thread Unsafe connections are reset instead of being dropped Unsafe connections are reset instead of being dropped Drastically reduce the need to establish new connections Drastically reduce the need to establish new connections In ProxySQL 2.0 : each worker thread performs most of the In ProxySQL 2.0 : each worker thread performs most of the reset reset 4 2
Auto-reconnect and re-execution Automatic detection of failures Automatic detection of failures Graceful handling Graceful handling Auto-reconnect when possible Auto-reconnect when possible Pause until a backend becomes available Pause until a backend becomes available Re-execution of queries Re-execution of queries 4 3
Multiplexing Reduce the number of connections against mysqld Reduce the number of connections against mysqld (confjgurable) (confjgurable) Many clients connections (tens of thousands) can use few Many clients connections (tens of thousands) can use few backend connections (few hundreds) backend connections (few hundreds) T racks connection status (transactions, user variables, T racks connection status (transactions, user variables, temporary tables, etc) temporary tables, etc) Order by waiting time Order by waiting time 4 4
No ProxySQL: ProxySQL: 4 5
No ProxySQL: ProxySQL: 4 6
No ProxySQL: ProxySQL: 4 7
4 8
Multiplexing https://www.percona.com/blog/2017/09/01/life360-used-proxysql-lower-database-load/ https://www.percona.com/blog/2017/09/01/life360-used-proxysql-lower-database-load/ 4 9
MySQL Threads and Connection Pool MyHGM is a shared resource so it can cause contention when accessed by MySQL Threads MyHGM MySQL_Thread MySQL_Thread 5 0
Thread Connection Cache Connections Connections MyHGM Cache Cache MySQL_Thread MySQL_Thread 5 1
ProxySQL Web UI
Multiplexing (see it in web UI) 5 3
Multiplexing (see it in web) 5 4
More metrics on web UI 5 5
More metrics on web UI 5 6
More metrics on web UI 5 7
Monitoring Module It monitors backends and collects metrics It monitors backends and collects metrics Monitors replication lag and shun hosts Monitors replication lag and shun hosts Monitors read_only (and super_read_only and innodb_read_only in Monitors read_only (and super_read_only and innodb_read_only in 2.0) variables (replication hostgroups) 2.0) variables (replication hostgroups) Ping and terminates unresponsive nodes Ping and terminates unresponsive nodes 5 8
Monitoring Module Built in support for asynchronous replication: Built in support for asynchronous replication: second behind master and pt-heartbeat second behind master and pt-heartbeat Support for any type of clustering solution via external scripts Support for any type of clustering solution via external scripts (Galera/PXC , and any heterogeneous replication setup) (Galera/PXC , and any heterogeneous replication setup) In ProxySQL 1.4, support for Group Replication In ProxySQL 1.4, support for Group Replication In ProxySQL 2.0, support for Galera In ProxySQL 2.0, support for Galera 5 9
Admin Module
Admin Interface Allows runtime confjguration Allows runtime confjguration Exports internal statuses Exports internal statuses It uses MySQL protocol It uses MySQL protocol Confjguration possible from any client/tool using MySQL API Confjguration possible from any client/tool using MySQL API Covered during the tutorial Covered during the tutorial 6 1
Admin Interface Disk Disk Memory Memory Runtime Runtime Runtime Memory Config Disk 6 2
Try it! Source code on GitHub: Source code on GitHub: https://github.com/sysown/proxysql/ https://github.com/sysown/proxysql/ Forum: Forum: https://groups.google.com/forum/#!forum/proxysql https://groups.google.com/forum/#!forum/proxysql T utorials on: T utorials on: http://www.proxysql.com http://www.proxysql.com 6 3
Demo Environment
Demo Code on GitHub: Code on GitHub: https://github.com/renecannao/ProxySQL-Tutorial-PLSC2018 https://github.com/renecannao/ProxySQL-Tutorial-PLSC2018 Some hosts provided: Some hosts provided: Username: plsc Username: plsc Passwords: proxysql Passwords: proxysql 6 5
ProxySQL Cluster
ProxySQL Cluster Introduced in 1.4.2 Introduced in 1.4.2 Support for MySQL Query Rules, MySQL Users, MySQL Servers and Support for MySQL Query Rules, MySQL Users, MySQL Servers and ProxySQL Servers (no Global Variables, no Scheduler) ProxySQL Servers (no Global Variables, no Scheduler) A group of instances constantly check each other for changes in configuration A group of instances constantly check each other for changes in configuration A configuration change applied in a proxy is pulled by the other proxies A configuration change applied in a proxy is pulled by the other proxies Allow to keep multiple ProxySQL instances with the same configuration(s) Allow to keep multiple ProxySQL instances with the same configuration(s) 6 7
ProxySQL Cluster Core Nodes ● Satellite Nodes ● Largest setup we know: 1) 3 core nodes 2) 900 satellite nodes 6 8
ProxySQL Cluster Conflict resolution is timestamp based Conflict resolution is timestamp based • • No order No order • • Last win Last win No quorum (in the roadmap) No quorum (in the roadmap) Highly scalable: Core nodes vs satellite nodes Highly scalable: Core nodes vs satellite nodes 6 9
ProxySQL Cluster - Confjg mysql> SHOW VARIABLES LIKE 'admin-cluster%'; mysql> SHOW VARIABLES LIKE 'admin-cluster%'; mysql> SET admin-cluster_username='radmin'; mysql> SET admin-cluster_username='radmin'; +---------------------------------------------------+--------+ +---------------------------------------------------+--------+ mysql> SET admin-cluster_password='radmin'; mysql> SET admin-cluster_password='radmin'; | Variable_name | Value | | Variable_name | Value | mysql> LOAD ADMIN VARIABLES TO RUNTIME; mysql> LOAD ADMIN VARIABLES TO RUNTIME; +---------------------------------------------------+--------+ +---------------------------------------------------+--------+ | admin-cluster_username | radmin | | admin-cluster_username | radmin | mysql> SAVE ADMIN VARIABLES TO DISK; mysql> SAVE ADMIN VARIABLES TO DISK; | admin-cluster_password | radmin | | admin-cluster_password | radmin | | admin-cluster_check_interval_ms | 1000 | | admin-cluster_check_interval_ms | 1000 | mysql> INSERT INTO proxysql_servers mysql> INSERT INTO proxysql_servers | admin-cluster_check_status_frequency | 10 | | admin-cluster_check_status_frequency | 10 | | admin-cluster_mysql_query_rules_difgs_before_sync | 3 | | admin-cluster_mysql_query_rules_difgs_before_sync | 3 | (hostname) VALUES ('proxysql1'),('proxysql2'), (hostname) VALUES ('proxysql1'),('proxysql2'), | admin-cluster_mysql_servers_difgs_before_sync | 3 | | admin-cluster_mysql_servers_difgs_before_sync | 3 | ('proxysql3'); ('proxysql3'); | admin-cluster_mysql_users_difgs_before_sync | 3 | | admin-cluster_mysql_users_difgs_before_sync | 3 | mysql> LOAD PROXYSQL SERVERS TO RUNTIME; mysql> LOAD PROXYSQL SERVERS TO RUNTIME; | admin-cluster_proxysql_servers_difgs_before_sync | 3 | | admin-cluster_proxysql_servers_difgs_before_sync | 3 | | admin-cluster_mysql_query_rules_save_to_disk | true | | admin-cluster_mysql_query_rules_save_to_disk | true | mysql> SAVE PROXYSQL SERVERS TO DISK; mysql> SAVE PROXYSQL SERVERS TO DISK; | admin-cluster_mysql_servers_save_to_disk | true | | admin-cluster_mysql_servers_save_to_disk | true | | admin-cluster_mysql_users_save_to_disk | true | | admin-cluster_mysql_users_save_to_disk | true | | admin-cluster_proxysql_servers_save_to_disk | true | | admin-cluster_proxysql_servers_save_to_disk | true | +---------------------------------------------------+--------+ +---------------------------------------------------+--------+ 12 rows in set (0.00 sec) 12 rows in set (0.00 sec) 7 0
ProxySQL Cluster - Monitoring stats_proxysql_servers_checksums stats_proxysql_servers_checksums stats_proxysql_servers_metrics stats_proxysql_servers_metrics 7 1
ProxySQL Cluster DEMO
MySQL Query rules
Query rewriting overview SQL-aware SQL-aware Regex-based Regex-based fmexible fmexible 7 4
Query rewriting examples read/write splitting read/write splitting Data masking Data masking Sharding Sharding 7 5
Query rewriting fmow 7 6
Match Related T ables mysql_query_rules mysql_users mysql_query_rules mysql_users • • active active • • username username • fast_forward(bypass) • fast_forward(bypass) • • schemaname schemaname • • flagIN/OUT flagIN/OUT • • client_addr client_addr • proxy_addr • proxy_addr • proxy_port • proxy_port • digest • digest • • match_digest match_digest • • match_pattern match_pattern • • negate_match_pattern negate_match_pattern 7 7
Action related fjelds flagOUT active flagOUT active replace_pattern replace_pattern destination_hostgroup destination_hostgroup cache_ttl cache_ttl timeout timeout New in 2.0 : New in 2.0 : retries retries gtid_from_hostgroup gtid_from_hostgroup delay delay cache_empty_result cache_empty_result mirror_flagOUT mirror_flagOUT cache_timeout cache_timeout mirror_hostgroup mirror_hostgroup OK_msg OK_msg error_msg error_msg l og l og multiplex multiplex apply apply 7 8
3 layers for ProxySQL tables Disk Disk Memory Memory Runtime Runtime Runtime Memory Config Disk 7 9
Precautions Confirm the query hits with stats_mysql_query_rules Confirm the query hits with stats_mysql_query_rules double check rules and rewrite results double check rules and rewrite results hold off on writing rules to disk until you're sure they are working as expected hold off on writing rules to disk until you're sure they are working as expected have a rollback plan have a rollback plan • load from disk, • load from disk, • then load from memory to runtime • then load from memory to runtime test select query rules and rewrites on a slaves first test select query rules and rewrites on a slaves first test mutable rules on a mirror or other throw away db. test mutable rules on a mirror or other throw away db. 8 0
Example - Simple R/W mysql> SELECT username, default_hostgroup FROM mysql_users\G mysql> SELECT username, default_hostgroup FROM mysql_users\G *************************** 1. row *************************** *************************** 1. row *************************** username: root username: root default_hostgroup: 0 default_hostgroup: 0 1 row in set (0.00 sec) 1 row in set (0.00 sec) mysql> SELECT match_digest, destination_hostgroup FROM mysql_query_rules\G mysql> SELECT match_digest, destination_hostgroup FROM mysql_query_rules\G This example is a *************************** 1. row *************************** *************************** 1. row *************************** simple one, NOT match_digest: ^SELECT.*FOR UPDATE match_digest: ^SELECT.*FOR UPDATE destination_hostgroup: 0 destination_hostgroup: 0 for production use *************************** 2. row *************************** *************************** 2. row *************************** match_digest: ^SELECT match_digest: ^SELECT destination_hostgroup: 1 destination_hostgroup: 1 2 rows in set (0.00 sec) 2 rows in set (0.00 sec) For production configuration guide, see: https://proxysql.com/blog/configure-read-write-split 8 1
Query Rewriting with ProxySQL DEMO Click to add text Click to add text
ProxySQL Upgrade with zero downtime
Multiple instances running on the same port Requires Linux Kernel 3.9 Requires Linux Kernel 3.9 Use different datadir (optionally different config file) Use different datadir (optionally different config file) Close the listener for a process Close the listener for a process Drain connections from one node Drain connections from one node Wait all the connections to pass over Wait all the connections to pass over 8 4
Mirroring with ProxySQL Click to add text Click to add text
What is mirroring? 8 6
What is mirroring? 8 7
Why mirror queries? Validate performance on a difgerent server using difgerent hostgroups. Validate performance on a difgerent server using difgerent hostgroups. Validate performance of query rewrite or schema change Validate performance of query rewrite or schema change Pre-fetch slave replication (Replication Booster) Pre-fetch slave replication (Replication Booster) 8 8
How to mirror mysql>SHOW CREATE TABLE mysql_query_rules\G mysql>SHOW CREATE TABLE mysql_query_rules\G *********************** 1. row ************************ *********************** 1. row ************************ table: mysql_query_rules table: mysql_query_rules Create T able: CREATE TABLE mysql_query_rules ( Create T able: CREATE TABLE mysql_query_rules ( rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0, active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0, username VARCHAR, username VARCHAR, schemaname VARCHAR, schemaname VARCHAR, fmagIN INT NOT NULL DEFAULT 0, fmagIN INT NOT NULL DEFAULT 0, *snip* *snip* mirror_fmagOUT INT UNSIGNED, mirror_fmagOUT INT UNSIGNED, mirror_hostgroup INT UNSIGNED, mirror_hostgroup INT UNSIGNED, error_msg VARCHAR, error_msg VARCHAR, * snip *) * snip *) 8 9
Mirroring fmow 9 0
Mirroring - example #1 mysql> SELECT username, destination_hostgroup, mysql> SELECT username, destination_hostgroup, mirror_hostgroup, mirror_fmagOUT FROM mysql_query_rules mirror_hostgroup, mirror_fmagOUT FROM mysql_query_rules WHERE username='plam_mirror'\G WHERE username='plam_mirror'\G ****************** 1. row ********************** ****************** 1. row ********************** username: pl_mirror username: pl_mirror destination_hostgroup: 3 destination_hostgroup: 3 mirror_hostgroup: 4 mirror_hostgroup: 4 9 mirror_fmagOUT: NULL mirror_fmagOUT: NULL 1
Mirroring - example #2 9 2
Mirroring Demo Click to add text Click to add text
Mirroring caveats Can be dropped: Can be dropped: mysql-mirror_max_concurrency, mysql-mirror_max_concurrency, mysql-mirror_max_queue_length mysql-mirror_max_queue_length Can be executed out of order Can be executed out of order No error handling of failed queries No error handling of failed queries 9 4
Failover with ProxySQL
Failover highlights improve failover time as perceived by the application improve failover time as perceived by the application prevent errors sent to the application prevent errors sent to the application perform transparent database failovers: gracefully redirecting traffic without the perform transparent database failovers: gracefully redirecting traffic without the application knowing application knowing existing applications do not have to be rewritten to autoreconnect since existing applications do not have to be rewritten to autoreconnect since connections are not lost from failovers connections are not lost from failovers 9 6
Failover scenario - without ProxySQL 9 7
Failover scenario - without ProxySQL 9 8
Failover scenario - with ProxySQL 9 9
Failover scenario - with ProxySQL 1 0 0
Recommend
More recommend
