ProxySQL 2.0 native support for Percona XtraDB Cluster (PXC) 5.7 by Vinicius Grippa

This presentation was given at ProxySQL Tech Talks with Percona by Vinicius Grippa, Percona's Sr. Support Engineer on June 4, 2020. The talk abstract is as follows:

"In this session, I will cover the new functionalities introduced by ProxySQL 2.X and how simple became when we compare to ProxySQL 1.X versions when it was necessary to use an external script with the scheduler. A demonstration will show how to configure ProxySQL for PXC. Subsequently, new functionalities will be covered to explore the potential of the new version using SSL in the front end to communicate with the application and how to avoid causal reads. And to close the session, how to actively monitor your ProxySQL instance most important parameters such as connections(backend and frontend), cache efficiency, network and others using Percona Monitoring and Management 2 (PMM 2)".

Transcript

1. © 2019 Percona 1 Vinicius M. Grippa ProxySQL 2.0 native support for Percona XtraDB Cluster (PXC) 5.7 Ensuring transparent high availability and scalability for your application Senior Support Engineer for MySQL / MongoDB
2. © 2019 Percona 2 About Percona ▪ Founded in 2006 ▪ 190+ staff worldwide ▪ Percona blog has 100k+ readers per month ▪ Percona tools have been used over a billion times in the past 3 years ▪ Percona conferences attract thousands of attendees from some of the world’s largest companies (#PerconaLive) ▪ Percona software is installed on millions of servers
3. © 2019 Percona 3 About me ▪ Support Engineer at Percona since 2017. ▪ Working with MySQL for over six years. ▪ Working with databases for over nine years. ▪ Speaker at Percona Live conferences and meetups about MySQL/MongoDB. ▪ Social media • https://www.linkedin.com/in/vinicius-grippa/ • vinicius.grippa@percona.com / vgrippa@gmail.com
4. © 2019 Percona 4 Percona XtraDB Cluster
5. © 2019 Percona 5 Traditional MySQL replication - Replication delay - Switchover interval - Single point of failure - Data integrity problem
6. © 2019 Percona 6 PXC/Galera - All nodes can act as a master - Data integrity - Synchronous replication - Read scalability
7. © 2019 Percona 7 Automatic Node Provisioning - Bootstrapping cluster - SST (Snapshot State Transfer) (rsync, xtrabackup, mysqldump) - IST(incremental state transfer) Auto-catchup cluster state
8. © 2019 Percona 8 Workload Conflict - Brute force abort - Forceful abort of conflicting transaction - Certification failure
9. © 2019 Percona 9 Flow Control - Trx are queued. Queue full can cause flow-control - Dynamic Control of the workload
10. © 2019 Percona 10 Cluster-safe-mode - Workload that is not safe to the cluster - Pxc_strict_mode (Enforcing, Master, Permissive, Disabled)
11. © 2019 Percona 11 Load Balancer - PXC can operate with multiple load balancers (HA Proxy, ProxySQL, etc…) - PXC suggests ProxySQL - Integrated and close development - Feature rich load balancer
12. © 2019 Percona 12 ProxySQL 2.0
13. © 2019 Percona 13 ProxySQL Features •Query Caching •Query Routing •Firewall •Advanced configuration with 0 downtime •ProxySQL cluster •Open source :)
14. © 2019 Percona 14 ProxySQL topology
15. © 2019 Percona 15 ProxySQL topology
16. © 2019 Percona 16 ProxySQL topology
17. © 2019 Percona 17 ProxySQL 2.0 •ProxySQL v2.0 has native support for Galera Clustering (In previous versions of ProxySQL an external scheduler was required to track the status of Galera nodes) •Introduces mysql_galera_hostgroups and mysql_server_galera_log tables
18. © 2019 Percona 18 ProxySQL Configuration
19. © 2019 Percona 19 ProxySQL Example IPs 172.16.2.181 - node3 172.16.1.54 - node2 172.16.3.136 - node1 Hostgroups Writer HG-> 100 Reader HG-> 101 BackupW HG-> 102 offHG HG-> 9101
20. © 2019 Percona 20 ProxySQL Example Servers first: INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('172.16.2.181',101,3306,1000); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('172.16.1.54',101,3306,100); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('172.16.3.136',101,3306,100); save mysql servers to disk; load mysql servers to runtime;
21. © 2019 Percona 21 ProxySQL Example Creating user (it is necessary to create the user on PXC as well): insert into mysql_users (username, password,default_hostgroup) values ('app_test','app_test', 100); load mysql users to runtime; save mysql users from runtime; save mysql users to disk;
22. © 2019 Percona 22 ProxySQL Example Creating Rules: insert into mysql_query_rules (rule_id,proxy_port,schemaname,username,destination_hostgroup,active, retries,match_digest,apply) values(1040,6033,'*','app_test',100,1,3,'^SELECT.*FOR UPDATE',1); insert into mysql_query_rules (rule_id,proxy_port,schemaname,username,destination_hostgroup,active, retries,match_digest,apply) values(1041,6033,'*','app_test',101,1,3,'^SELECT.*@@',1); save mysql query rules to disk; load mysql query rules to run;
23. © 2019 Percona 23 ProxySQL Example Then the galera settings: insert into mysql_galera_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,ma x_transactions_behind) values (100,102,101,9101,0,1,1,16); # max_transactions_behind - determines the maximum number of writesets behind the cluster that ProxySQL should allow before shunning the node to prevent stale reads (this is determined by querying the wsrep_local_recv_queue Galera variable).
24. © 2019 Percona 24 ProxySQL Example Setting version: update global_variables set variable_value='5.7.25' where variable_name='mysql-server_version'; LOAD MYSQL VARIABLES TO RUNTIME;SAVE MYSQL VARIABLES TO DISK;
25. © 2019 Percona 25 ProxySQL Example Create monitor user on MySQL and adjust on ProxySQL: UPDATE global_variables SET variable_value='admin'WHERE variable_name='mysql-monitor_username'; UPDATE global_variables SET variable_value='admin'WHERE variable_name='mysql-monitor_password';
26. © 2019 Percona 26 ProxySQL Example PXC: admin@127.0.0.1 ((none)) > select * from runtime_mysql_galera_hostgroups \G *************************** 1. row *************************** writer_hostgroup: 100 backup_writer_hostgroup: 102 reader_hostgroup: 101 offline_hostgroup: 9101 active: 0 ← https://github.com/sysown/proxysql/issues/1902 max_writers: 1 writer_is_also_reader: 1 max_transactions_behind: 16 comment: NULL 1 row in set (0.01 sec)
27. © 2019 Percona 27 ProxySQL Features
28. © 2019 Percona 28 Run yum install epel-release yum install boost-system ./proxysql_binlog_reader -h 127.0.0.1 -u root -psekret -P 3306 -l 3307 -L binlogreader.log ProxySQL Features
29. © 2019 Percona 29 PMM + ProxySQL + PXC DEMO https://github.com/vgrippa/pmm_lab
30. © 2019 Percona 30 Questions?
31. © 2019 Percona 31 • Write for our community blog percona.com/community-blog • Join in with our community forums percona.com/forums • Contribute to our open source projects Join in: Percona Community
32. DATABASE PERFORMANCE MATTERS Database Performance Matters Database Performance Matters Database Performance Matters Database Performance Matters Champions of Unbiased Open Source Database Solutions