ProxySQL, the journey from a MySQL proxy to being the de-facto multi-functional tool that scales MySQL

This presentation was given by René Cannaò, ProxySQL author & CEO, at ProxySQL Tech Talks with Percona on June 4, 2020. The talk abstract is as follows:

"ProxySQL has had a stable, generally available release since December 2015, and since that time has moved from Version 1.x to Version 2.x, now with a more fast track release cadence. The goal of this hour long webinar is to go through some of our design principles and how we have managed to progress from database proxy to multi-function tool that so many benefit from.

ProxySQL 2.0 has so many new features, from setting up causal reads for read consistency using GTIDs, down to support for AWS Aurora, LDAP authentication, and SSL for client connections.

However beyond just a high level introduction to these features, some of which will be covered in-depth during the next part of this webinar, we will focus a little on the native support for Galera Cluster and Percona XtraDB Cluster (PXC) which made its debut since ProxySQL 2.0.0. Since then it has been enhanced in 2.0.8, 2.0.10, and 2.0.11 and it also supports extensions like pxc_maint_mode.

We will also cover some of our future plans, and answer such questions such as, is it possible to run ProxySQL in Kubernetes, backed by a Percona XtraDB Cluster (PXC) within Kubernetes? Why, yes, it is! Extensions to monitoring that could be very well consumed by Percona Monitoring & Management (PMM) will also be covered."

Transcript

1. ProxySQL the journey from a MySQL proxy to being the de-facto multi-functional tool that scales MySQL by René Cannaò, 4th June 2020
2. Introduction
3. Who am I? René Cannaò Founder of ProxySQL LLC Author of ProxySQL MySQL DBA
4. ProxySQL LLC • ProxySQL Development • ProxySQL Support Services • ProxySQL, MySQL, DevOps & Outsourcing • ProxySQL Training • Consulting Services
5. Upcoming training Upcoming training! Register at: https://proxysql.com/services/training/
6. Software stack and database systems
7. Key attributes • Performance • Efficiency • Usability • Visibility • Availability • Manageability
8. Performance Vendors aim to always improve performance in their products New storage engines New algorithms New performance related features
9. Efficiency Reduce hardware cost Better use of resources (CPU/memory/disk/network) Web scale
10. Usability Adding new features that make it more usable Example: cloud friendly
11. Visibility Systems to provide: • Monitoring • Trending • Alerting Many providers and vendors (ex: PMM)
12. Availability Cluster solutions: • MySQL async (or semi-sync) replication • Galera • MySQL Cluster (NDB) • Percona PXC • Group Replication • AWS Aurora Some of them provide HA as built-in features, while others require an external manager.
13. Manageability Manage HA: • MHA • Orchestrator • Replication Manager How to take advantage of a highly available database system?
14. Layered architecture
15. Layered architecture APPLICATIONS DATABASES
16. Layered architecture APPLICATIONS Highly available DATABASES
17. Layered architecture APPLICATIONS Highly available DATABASES + MANAGER(S)
18. Layered architecture APPLICATIONS + Service Discovery Highly available DATABASES + MANAGER(S)
19. Layered architecture Boundaries are sometimes fuzzy Each layer can have multiple layers or heterogeneous structures The two layers communicate using API (protocol)
20. Layered architecture APPLICATIONS + Service Discovery Highly available DATABASES + MANAGER(S) L4 LOAD BALANCER
21. Layered architecture: Challenges before ProxySQL Each layer treats the other layer as a black box: • application layer doesn’t know what is happening in database layer • Database layer tries to serve as many requests as possible, without knowing any logic No way to manage traffic between the two layers (routing, retrying, flow control, QoS, firewall, etc)
22. ProxySQL development
23. ProxySQL - main motivations Empower the DBAs (made by DBA for DBAs) • Development driven by challenges seen from DBA perspective Improves manageability Create a proxy layer to shield the database Understand and improve performance High performance and High Availability
24. Initial features Load balancing (only feature available in L4 proxies) On-the-fly rewrite of queries Caching reads outside the database Connection pooling and multiplexing Read/write split
25. ProxySQL - pre-requirements Understand the MySQL wire protocol Implement a reverse proxy
26. Layered architecture APPLICATIONS + Service Discovery Highly available DATABASES + MANAGER(S) L4 LOAD BALANCER
27. Layered architecture APPLICATIONS + Service Discovery Highly available DATABASES + MANAGER(S) PROXYSQL = L7 REVERSE PROXY
28. Runtime reconfiguration ProxySQL itself must be highly available If it manages backends HA, it must provide uptime Restarting ProxySQL must be avoided at all costs: • Loading a new configuration must not cause a restart
29. Admin interface
30. Admin interface ProxySQL provides an unique admin interface: • Can be accessed using a normal MySQL client • Configuration stored in tables and databases • Configuration can be queried and updated • Input validation • Atomic commit • Rollback • Multi-layer configuration
31. Admin interface $ mysql -h 127.0.0.1 -P 6032 -uadmin -padmin --prompt='Admin> ' -e "SHOW TABLES" -t +----------------------------------------------------+ | tables | +----------------------------------------------------+ | global_variables | | mysql_aws_aurora_hostgroups | | mysql_collations | | mysql_firewall_whitelist_rules | | mysql_firewall_whitelist_sqli_fingerprints | | mysql_firewall_whitelist_users | | mysql_galera_hostgroups | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | restapi_routes | | runtime_checksums_values | ...
32. Admin interface - example Admin> SHOW CREATE TABLE mysql_servers\G *************************** 1. row *************************** table: mysql_servers Create Table: CREATE TABLE mysql_servers ( hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0, hostname VARCHAR NOT NULL, port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306, gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0, status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE', weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1, compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0, max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000, max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0, use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0, max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0, comment VARCHAR NOT NULL DEFAULT '', PRIMARY KEY (hostgroup_id, hostname, port) ) 1 row in set (0.01 sec)
33. Admin interface - example Admin> INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (10, ‘172.16.1.121’,3306); Admin> SELECT * FROM mysql_servers; Admin> SELECT * FROM runtime_mysql_servers; Admin> SELECT * FROM disk.mysql_servers; Admin> LOAD MYSQL SERVERS TO RUNTIME; Admin> SAVE MYSQL SERVERS TO DISK;
34. Multi layer configuration Memory Configuration file /etc/proxysql.cnf is used to initialize the internal MEMORY structures, DISK database and RUNTIME. Used only on initial startup Disk Runtime
35. New features driven by manageability Seamless planned and unplanned failover Limiting number of connections Query throttling Query timeout Query mirroring (initial) Query firewall
36. ProxySQL Failover Goals The main goals for ProxySQL during a database failover are to: • Reduce service unavailability time during failover • Prevent errors sent to the application • Perform transparent planned database failovers: gracefully redirecting traffic without the application knowing • Avoid having to rewrite existing applications to reconnect after a failover by maintaining the connections
37. Failover scenario - without ProxySQL
38. Failover scenario - without ProxySQL
39. Failover scenario - with ProxySQL
40. Failover scenario - with ProxySQL
41. Failover scenario - with ProxySQL
42. Failover scenario - with ProxySQL
43. Notes about failover with ProxySQL • ◦ •
44. New features driven by cluster solutions Scheduler Support for various replication topology: • Galera • Group Replication • Percona PXC • MySQL NDB • AWS Aurora
45. MySQL at Scale
46. New features driven by scalability Millions of MySQL users Hundreds of MySQL servers Hundreds of thousands of connections Route to millions of schemas Multiple clusters using different technologies
47. ProxySQL - DBaaS layer
48. Database as a Service Layer APPLICATIONS + Service Discovery Highly available DATABASES + MANAGER(S) DBaaS layer
49. Configuration Management ProxySQL Cluster
50. ProxySQL Cluster Topologies A ProxySQL Cluster can be configured to pull configuration data from a single specific node • Nodes pulling configuration can start with almost zero configuration, just the IP of the controller specified and a short static configuration
51. ProxySQL Cluster Topologies A ProxySQL Cluster can also be configured to pull configuration data from a set of nodes • The same principle applies as in the previous slide regarding static configuration
52. Kubernetes Service + Controller • Optionally an additional controller pod can be added • ProxySQL instances serving traffic are never configured, they just pull config from the Controller via ProxySQL Cluster • Controller is configured dynamically via SQL, updates are pushed to the pod – config is re-used and persisted in a volume
53. ProxySQL Cascading in Kubernetes © ProxySQL 2013-2020. All rights reserved. • ProxySQL is deployed in each application pod and in a ProxySQL service layer • Applications connect to the local ProxySQL server • Provides load balancing as well as HA
54. Features driven by ProxySQL availability Multiple ProxySQL instances listening on the same ports Online version upgrade PAUSE/RESUME/START/STOP
55. Features driven by community needs Data masking Support for Clickhouse Causal reads using GTID Strong encryption Advanced firewall LDAP authentication REST API
56. Visibility
57. Layered architecture - visibility APPLICATIONS + Service Discovery Highly available DATABASES + MANAGER(S) PROXYSQL = L7 REVERSE PROXY
58. ProxySQL and visibility Analyze all the traffic Check the status of the backends Collect metrics Export statistics Export internal statuses and metrics
59. ProxySQL and visibility Admin> SHOW TABLES FROM stats; +--------------------------------------+ | tables | +--------------------------------------+ | global_variables | | stats_memory_metrics | | stats_mysql_commands_counters | | stats_mysql_connection_pool | | stats_mysql_connection_pool_reset | | stats_mysql_errors | | stats_mysql_errors_reset | | stats_mysql_free_connections | | stats_mysql_global | | stats_mysql_gtid_executed | | stats_mysql_prepared_statements_info | | stats_mysql_processlist | | stats_mysql_query_digest | | stats_mysql_query_digest_reset | | stats_mysql_query_rules | | stats_mysql_users | ... +--------------------------------------+ 19 rows in set (0.00 sec)
60. ProxySQL and visibility Admin> SHOW TABLES FROM monitor; +--------------------------------------+ | tables | +--------------------------------------+ | mysql_server_aws_aurora_check_status | | mysql_server_aws_aurora_failovers | | mysql_server_aws_aurora_log | | mysql_server_connect_log | | mysql_server_galera_log | | mysql_server_group_replication_log | | mysql_server_ping_log | | mysql_server_read_only_log | | mysql_server_replication_lag_log | +--------------------------------------+ 9 rows in set (0.00 sec)
61. ProxySQL and visibility Admin> SHOW TABLES FROM stats_history; +-------------------------------------+ | tables | +-------------------------------------+ | history_mysql_query_digest | | history_mysql_status_variables | | history_mysql_status_variables_v2 | | history_stats_mysql_connection_pool | | myhgm_connections | | myhgm_connections_day | | myhgm_connections_hour | | mysql_connections | ... | system_memory_hour | +-------------------------------------+ 20 rows in set (0.01 sec)
62. Web UI HTTP server Export metrics New version currently under development Allows to easily configure ProxySQL
63. Prometheus Exporter All internal metrics exported using the built-in web server No need for external exporter
64. Thank you!