ha and clustering solution proxysql as an intelligent
play

HA and clustering solution: ProxySQL as an intelligent router for - PowerPoint PPT Presentation

Aug 31, 2022 •30 likes •530 views

HA and clustering solution: ProxySQL as an intelligent router for Galera and Group Replication Rene Cannao Rene Cannao ProxySQL ProxySQL Introduction A bit about ProxySQL LLC We provide services to help build, support We provide services

  1. HA and clustering solution: ProxySQL as an intelligent router for Galera and Group Replication Rene Cannao Rene Cannao ProxySQL ProxySQL

  2. Introduction

  3. A bit about ProxySQL LLC We provide services to help build, support We provide services to help build, support as well as improve the performance & as well as improve the performance & reliability of your Cloud-Based or On- reliability of your Cloud-Based or On- Premise MySQL infrastructure: Premise MySQL infrastructure: • ProxySQL Development • ProxySQL Development • Remote Consulting • Remote Consulting • ProxySQL Support Services • ProxySQL Support Services • ProxySQL Training • ProxySQL Training 3

  4. A bit about me… Rene Cannao Rene Cannao • Founder of ProxySQL • Founder of ProxySQL • MySQL DBA • MySQL DBA 4

  5. Other sessions ProxySQL Hand-on ProxySQL Hand-on Monday 5 th November , 2018 @9:00AM Monday 5 th November , 2018 @9:00AM ProxySQL Adaptive query routing based on GTID tracking ProxySQL Adaptive query routing based on GTID tracking Wednesday 7 th November, 2018 @4:30PM Wednesday 7 th November, 2018 @4:30PM 5

  6. What is ProxySQL?

  7. What is ProxySQL The MySQL data stargate The MySQL data stargate 7

  8. ProxySQL Architecture Overview Data gateway Clients connect to ProxySQL Requests are evaluated Actions are performed 8

  9. Some of the most interesting features: ● on-the-fly rewrite of queries ● caching reads outside the database server ● connection pooling and multiplexing ● complex query routing and read/write split ● load balancing ● real time statistics ● monitoring ● data masking ● multiple instances on same ports 9

  10. Some of the most interesting features: ● high availability and scalability ● seamless failover ● firewall ● query throttling ● query timeout ● query mirroring ● runtime reconfiguration ● scheduler ● support for Galera/PXC and Group Replication 10

  11. Some of the most interesting features: ● support for millions of users ● support for tens of thousands of database servers ● native ProxySQL Clustering solution ● support for ClickHouse as a backend ● support for Aurora ● SSL support for frontend ● SSLv1.2 ● native Support for Galera ● causal reads using GTID 11

  12. 12

  13. What is Galera Cluster and Oracle Group Replication?

  14. What is Galera Cluster? • Multi-master / Active-Active Clustered MySQL Solution • Multi-master / Active-Active Clustered MySQL Solution • Synchronous Replication (certification based) • Synchronous Replication (certification based) • Multi-threaded Replication • Multi-threaded Replication • InnoDB Compliant • InnoDB Compliant • Suitable for LAN, WAN and Cloud Solutions • Suitable for LAN, WAN and Cloud Solutions • IST (incremental) & SST (full) for state transfer • IST (incremental) & SST (full) for state transfer • Auto reconnect mechanism for rejected nodes • Auto reconnect mechanism for rejected nodes

  15. What is Oracle Group Replication? • Multi-master / Active-Active Clustered MySQL Solution • Multi-master / Active-Active Clustered MySQL Solution • • Single master by default (group_replication_single_primary_mode) Single master by default (group_replication_single_primary_mode) • Synchronous Replication (certification based) • Synchronous Replication (certification based) • Multi-threaded Replication • Multi-threaded Replication • InnoDB Compliant • InnoDB Compliant • Suitable for LAN and low latency networks • Suitable for LAN and low latency networks • State transfer is based on GTID matching • State transfer is based on GTID matching

  16. Why ProxySQL? • Real time monitoring of backends • Real time monitoring of backends • Transparent redirect of traffic • Transparent redirect of traffic • Automatic reconfiguration • Automatic reconfiguration

  17. ProxySQL for Galera

  18. Galera Support in ProxySQL • Historically in ProxySQL v1.x support for Galera is based on “external” • Historically in ProxySQL v1.x support for Galera is based on “external” scripts scripts • An “external” script is configured in the ProxySQL scheduler: • An “external” script is configured in the ProxySQL scheduler: • • proxysql_galera_checker.sh is provided as a “ready to use script” proxysql_galera_checker.sh is provided as a “ready to use script” • ProxySQL 2.x provides native support for Galera • ProxySQL 2.x provides native support for Galera

  19. A quick overview of pre-2.x support Typically the script is added to the “scheduler” table and loaded to runtime Typically the script is added to the “scheduler” table and loaded to runtime Admin> select * from scheduler\G Admin> select * from scheduler\G *************************** 1. row *************************** *************************** 1. row *************************** id: 1 id: 1 interval_ms: 10000 interval_ms: 10000 filename: /var/lib/proxysql/proxysql_galera_checker.sh filename: /var/lib/proxysql/proxysql_galera_checker.sh arg1: 127.0.0.1 arg1: 127.0.0.1 arg2: 6032 arg2: 6032 arg3: 0 arg3: 0 arg4: /var/lib/proxysql/proxysql_galera_checker.log arg4: /var/lib/proxysql/proxysql_galera_checker.log arg5: NULL arg5: NULL

  20. A quick overview of pre-2.x support Sample log file during an outage: Sample log file during an outage: ### /var/lib/proxysql/proxysql_galera_checker.log ### /var/lib/proxysql/proxysql_galera_checker.log Sat Jul 16 18:42:14 UTC 2016 Check server 10.10.10.11:3306 , status ONLINE , wsrep_local_state 4 Sat Jul 16 18:42:14 UTC 2016 Check server 10.10.10.11:3306 , status ONLINE , wsrep_local_state 4 ERROR 2003 (HY000): Can't connect to MySQL server on '10.10.10.12' (111) ERROR 2003 (HY000): Can't connect to MySQL server on '10.10.10.12' (111) Sat Jul 16 18:42:14 UTC 2016 Check server 10.10.10.12:3306 , status ONLINE , wsrep_local_state Sat Jul 16 18:42:14 UTC 2016 Check server 10.10.10.12:3306 , status ONLINE , wsrep_local_state Sat Jul 16 18:42:14 UTC 2016 Changing server 10.10.10.12:3306 to status OFFLINE_SOFT Sat Jul 16 18:42:14 UTC 2016 Changing server 10.10.10.12:3306 to status OFFLINE_SOFT Sat Jul 16 18:42:14 UTC 2016 Check server 10.10.10.13:3306 , status ONLINE , wsrep_local_state 4 Sat Jul 16 18:42:14 UTC 2016 Check server 10.10.10.13:3306 , status ONLINE , wsrep_local_state 4 Sat Jul 16 18:42:25 UTC 2016 Check server 10.10.10.11:3306 , status ONLINE , Sat Jul 16 18:42:25 UTC 2016 Check server 10.10.10.11:3306 , status ONLINE , wsrep_local_state 4 wsrep_local_state 4 ERROR 2003 (HY000): Can't connect to MySQL server on '10.10.10.12' (111) ERROR 2003 (HY000): Can't connect to MySQL server on '10.10.10.12' (111) Sat Jul 16 18:42:25 UTC 2016 Check server 10.10.10.12:3306 , status OFFLINE_SOFT , Sat Jul 16 18:42:25 UTC 2016 Check server 10.10.10.12:3306 , status OFFLINE_SOFT , wsrep_local_state wsrep_local_state Sat Jul 16 18:42:25 UTC 2016 Check server 10.10.10.13:3306 , status ONLINE , wsrep_local_state 4 Sat Jul 16 18:42:25 UTC 2016 Check server 10.10.10.13:3306 , status ONLINE , wsrep_local_state 4

  21. A quick overview of pre-2.x support Changes are reflected in the “mysql_servers” and “runtime_mysql_servers” tables: Changes are reflected in the “mysql_servers” and “runtime_mysql_servers” tables: Admin> SELECT hostgroup_id,hostname,port,status FROM mysql_servers; Admin> SELECT hostgroup_id,hostname,port,status FROM mysql_servers; +--------------+-------------+------+--------------+ +--------------+-------------+------+--------------+ | hostgroup_id | hostname | port | status | | hostgroup_id | hostname | port | status | +--------------+-------------+------+--------------+ +--------------+-------------+------+--------------+ | 0 | 10.10.10.11 | 3306 | ONLINE | | 0 | 10.10.10.11 | 3306 | ONLINE | | 0 | 10.10.10.12 | 3306 | OFFLINE_SOFT | | 0 | 10.10.10.12 | 3306 | OFFLINE_SOFT | | 0 | 10.10.10.13 | 3306 | ONLINE | | 0 | 10.10.10.13 | 3306 | ONLINE | +--------------+-------------+------+--------------+ +--------------+-------------+------+--------------+

  22. A quick overview of pre-2.x support Changes will be reflected in the “mysql_servers” table: Changes will be reflected in the “mysql_servers” table: Admin> SELECT hostgroup_id,hostname,port,status FROM mysql_servers; Admin> SELECT hostgroup_id,hostname,port,status FROM mysql_servers; +--------------+-------------+------+--------------+ +--------------+-------------+------+--------------+ | hostgroup_id | hostname | port | status | | hostgroup_id | hostname | port | status | +--------------+-------------+------+--------------+ +--------------+-------------+------+--------------+ | 0 | 10.10.10.11 | 3306 | ONLINE | | 0 | 10.10.10.11 | 3306 | ONLINE | | 0 | 10.10.10.12 | 3306 | OFFLINE_SOFT | | 0 | 10.10.10.12 | 3306 | OFFLINE_SOFT | | 0 | 10.10.10.13 | 3306 | ONLINE | | 0 | 10.10.10.13 | 3306 | ONLINE | +--------------+-------------+------+--------------+ +--------------+-------------+------+--------------+

Recommend

ProxySQL hand-on Ren Canna Ren Canna ProxySQL ProxySQL Frankfurt, 5 th Nov 2018

ProxySQL hand-on Ren Canna Ren Canna ProxySQL ProxySQL Frankfurt, 5 th Nov 2018

ProxySQL hand-on Ren Canna Ren Canna ProxySQL ProxySQL Frankfurt, 5 th Nov 2018 Frankfurt, 5 th Nov 2018 A bit about ProxySQL LLC We provide services to help build, support We provide services to help build, support as well as

1.5k views • 121 slides
Finding Clusters Types of Clustering Approaches: Linkage Based, e.g. Hierarchical Clustering

Finding Clusters Types of Clustering Approaches: Linkage Based, e.g. Hierarchical Clustering

Finding Clusters Types of Clustering Approaches: Linkage Based, e.g. Hierarchical Clustering Clustering by Partitioning, e.g. k-Means Density Based Clustering, e.g. DBScan Grid Based Clustering Compendium slides for Guide to Intelligent

1.32k views • 82 slides
CiMS Intelligent Communications, Information & Management Solution Are you Ready for

CiMS Intelligent Communications, Information & Management Solution Are you Ready for

CiMS Intelligent Communications, Information & Management Solution Are you Ready for the most innovative, all-in-one solution iOBI has to offer? INTELLIGENT RISK MANAGEMENT SOLUTIONS The future of communication is now! Any

696 views • 21 slides
ProxySQL Tutorial High Performance & High Availability Proxy for MySQL With a GPL license!

ProxySQL Tutorial High Performance & High Availability Proxy for MySQL With a GPL license!

ProxySQL Tutorial High Performance & High Availability Proxy for MySQL With a GPL license! Santa Clara, California | April 23th 25th, 2018 Who we are Ren Canna ProxySQL Founder Derek Downey Director of OSDB, Pythian 2 Agenda

1.45k views • 103 slides
Stability of Clustering Methods Sasha Rakhlin Ph.D. candidate, MIT 1 A procedure is stable if P

Stability of Clustering Methods Sasha Rakhlin Ph.D. candidate, MIT 1 A procedure is stable if P

Stability of Clustering Methods Sasha Rakhlin Ph.D. candidate, MIT 1 A procedure is stable if P ( solution perturbed solution > ) 0 2 This talk: A tool for theoretical analysis of stability of clustering algorithms. Idea:

346 views • 24 slides
On Information-Maximization On Information-Maximization Clustering: Tuning Parameter Clustering:

On Information-Maximization On Information-Maximization Clustering: Tuning Parameter Clustering:

ICML2011 Jun. 28-Jul. 2, 2011 On Information-Maximization On Information-Maximization Clustering: Tuning Parameter Clustering: Tuning Parameter Selection and Analytic Solution Selection and Analytic Solution Masashi Sugiyama, Makoto Yamada,

773 views • 28 slides
Methods for Intelligent Systems Lecture Notes on Clustering (II) 2009-2010 Davide Eynard

Methods for Intelligent Systems Lecture Notes on Clustering (II) 2009-2010 Davide Eynard

Methods for Intelligent Systems Lecture Notes on Clustering (II) 2009-2010 Davide Eynard eynard@elet.polimi.it Department of Electronics and Information Politecnico di Milano p. 1/19 Course Schedule [ Tentative ] Date Topic 11/03/2010

744 views • 25 slides
Orchestrating Proxysql with OCP Avi Apelbaum, Wix aviap@wix.com linkedin/aviapelbaum

Orchestrating Proxysql with OCP Avi Apelbaum, Wix aviap@wix.com linkedin/aviapelbaum

Orchestrating Proxysql with OCP Avi Apelbaum, Wix aviap@wix.com linkedin/aviapelbaum github.com/wix Hi. I am Eyal DBA Team Leader @WIx.com Wix in a Minute Our main production DBs Version 5.6.x & 5.7.x 3.2.12 3.0.9 DB instances

597 views • 37 slides
Lecture 23: Spectral clustering Hierarchical clustering What is a good clustering?

Lecture 23: Spectral clustering Hierarchical clustering What is a good clustering?

Lecture 23: Spectral clustering Hierarchical clustering What is a good clustering? Aykut Erdem May 2016 Hacettepe University Last time K-Means An iterative clustering algorithm - Initialize: Pick K random points as cluster

932 views • 64 slides
Clustering Hierarchical clustering, k-mean clustering Genome 559: Introduction to Statistical and

Clustering Hierarchical clustering, k-mean clustering Genome 559: Introduction to Statistical and

Clustering Hierarchical clustering, k-mean clustering Genome 559: Introduction to Statistical and Computational Genomics Elhanan Borenstein A quick review The clustering problem: partition genes into distinct sets with high homogeneity

772 views • 40 slides
Graph Clustering Graph Clustering What is clustering? What is clustering? Finding patterns

Graph Clustering Graph Clustering What is clustering? What is clustering? Finding patterns

Graph Clustering Graph Clustering What is clustering? What is clustering? Finding patterns in data, or grouping similar groups of data-points together into clusters . Clustering algorithms for numeric data: Lloyds K-means, EM

688 views • 19 slides
Clustering Hierarchical clustering and k-mean clustering Genome 373 Genomic Informatics

Clustering Hierarchical clustering and k-mean clustering Genome 373 Genomic Informatics

Clustering Hierarchical clustering and k-mean clustering Genome 373 Genomic Informatics Elhanan Borenstein A quick review The clustering problem: partition genes into distinct sets with high homogeneity and high separation Many

768 views • 43 slides
Local clustering with graph diffusions and spectral solution paths Joint with Kyle Kloster

Local clustering with graph diffusions and spectral solution paths Joint with Kyle Kloster

Local clustering with graph diffusions and spectral solution paths Joint with Kyle Kloster David F David F. . Gleich Gleich, (Purdue), supported by Purdue University NSF CAREER 1149756-CCF Local Clustering Given seed(s) S in G

639 views • 44 slides
FAULT TOLERANCE WITH PROXYSQL, MRM AND CONSUL ABOUT ME NARCS PILLAO Systems Engineer

FAULT TOLERANCE WITH PROXYSQL, MRM AND CONSUL ABOUT ME NARCS PILLAO Systems Engineer

FAULT TOLERANCE WITH PROXYSQL, MRM AND CONSUL ABOUT ME NARCS PILLAO Systems Engineer letgo.com | edreams.com | palominodb | emagister.com BEHIND THE SCENES 75M Downloads | 200M listings | 3B chats in 2,5 years ! AGENDA

543 views • 18 slides
Clustering DSE 210 Clustering in R d Two common uses of clustering: Vector quantization Find

Clustering DSE 210 Clustering in R d Two common uses of clustering: Vector quantization Find

Clustering DSE 210 Clustering in R d Two common uses of clustering: Vector quantization Find a finite set of representatives that provides good coverage of a complex, possibly infinite, high-dimensional space. Finding meaningful

396 views • 36 slides
Clustering: Hierarchical Clustering and K- Means Clustering Machine

Clustering: Hierarchical Clustering and K- Means Clustering Machine

Clustering: Hierarchical Clustering and K- Means Clustering Machine Learning 10-601B Seyoung Kim Many of these slides are derived from William Cohen,

660 views • 46 slides
Clustering and Dimensionality Reduction Preview Clustering K -means clustering

Clustering and Dimensionality Reduction Preview Clustering K -means clustering

Clustering and Dimensionality Reduction Preview Clustering K -means clustering Mixture models Hierarchical clustering Dimensionality reduction Principal component analysis Multidimensional scaling Isomap

1.61k views • 17 slides
ProxySQL Tutorial High Performance & High Availability Proxy for MySQL With a GPL license!

ProxySQL Tutorial High Performance & High Availability Proxy for MySQL With a GPL license!

ProxySQL Tutorial High Performance & High Availability Proxy for MySQL With a GPL license! Santa Clara, California | April 24th 27th, 2017 Who we are David Turner DBA, Uber Derek Downey OSDB Practice Advocate, Pythian Ren Canna

1.31k views • 76 slides
Clustering A Categorization of Major Clustering Methods Partitioning Methods

Clustering A Categorization of Major Clustering Methods Partitioning Methods

Clustering What is Clustering? Types of Data in Cluster Analysis Clustering A Categorization of Major Clustering Methods Partitioning Methods Hierarchical Methods 1 2 What is Clustering? What is Clustering? Typical

647 views • 18 slides
Subspace Clustering Ensemble Clustering Subspace Clustering, Ensemble Clustering, Alternative

Subspace Clustering Ensemble Clustering Subspace Clustering, Ensemble Clustering, Alternative

LUDWIG- MAXIMILIANS- INSTITUTE DATABASE UNIVERSITT FOR SYSTEMS MNCHEN MNCHEN INFORMATICS INFORMATICS GROUP GROUP Subspace Clustering Ensemble Clustering Subspace Clustering, Ensemble Clustering, Alternative Clustering, Multiview

513 views • 9 slides
Fortify your MySQL data security in AWS using ProxySQL and Firewalling Marco Tusa Percona About

Fortify your MySQL data security in AWS using ProxySQL and Firewalling Marco Tusa Percona About

Fortify your MySQL data security in AWS using ProxySQL and Firewalling Marco Tusa Percona About Me Open source enthusiast Principal Consultant Working in DB world over 25 years Open source developer and community contributor

979 views • 48 slides
Consistent Reads Using ProxySQL and GTID Santa Clara, California | April 23th 25th, 2018

Consistent Reads Using ProxySQL and GTID Santa Clara, California | April 23th 25th, 2018

Consistent Reads Using ProxySQL and GTID Santa Clara, California | April 23th 25th, 2018 Disclaimer I am not Ren Canna @lefred MySQL Community Manager / Oracle the one who provided a hint for this not the one

1.27k views • 50 slides
Introduction to Machine Learning, Clustering and EM Barnab s P czos Contents Clustering

Introduction to Machine Learning, Clustering and EM Barnab s P czos Contents Clustering

Introduction to Machine Learning, Clustering and EM Barnab s P czos Contents Clustering K-means Mixture of Gaussians Expectation Maximization Variational Methods 2 Clustering 3 K- means clustering What is clustering?

1.32k views • 66 slides
PAC-Bayesian Analysis of Co-clustering, Graph Clustering and Pairwise Clustering Yevgeny Seldin

PAC-Bayesian Analysis of Co-clustering, Graph Clustering and Pairwise Clustering Yevgeny Seldin

PAC-Bayesian Analysis of Co-clustering, Graph Clustering and Pairwise Clustering Yevgeny Seldin Motivation Example Clustering cannot be analyzed without specifying what it will be used for! be used for! Example Cluster then pack

293 views • 28 slides

More recommend