High Availability HA solutions Misses from current vanilla solutions Where ProxySQL fits It is NOT about Performance It is NOT an in depth description of HA or ProxySQL feature It is NOT an implementation guide
is related to the real need of your business. • We are pathologically online/connected, and often we expect to have over dimensioned HA or DR. • Business needs often do not require all the components to be ALWAYS available. How We Need to think about HA and DR
needs • Technical challenges • Supportable solutions • Knowhow Don’t: • Choose based on the “shiny object” • Pick something you know nothing about • Choose by yourself and push it up or down • Use shortcuts, to accelerate deploying time. The first step to have a robust solution is to design the right solution for your business.
your architecture http://www.tusacentral.com/joomla/index.php/mysql-blogs/204-how-not-to-do-mysql-high-availability-geographic-node-distribution-with-galera-based-replication-misuse http://www.tusacentral.com/joomla/index.php/mysql-blogs/205-mysql-high-availability-on-premises-a-geographically-distributed-scenario
• Datacentric approach (single state of the data, distributed commit) • Data is consistent in time cross nodes • Replication requires high performant link • Geographic distribution is forbidden • DR is not supported Loosely coupled database clusters • Single node approach (local commit) • Data state differs by node • Single node state does not affect the cluster • Replication link doesn’t need to be high performance • Geographic distribution is allow • DR is supported
Write scale (for real not a fake) • Read scale • Data is in sync all the time Bad things: • Too be very efficient should stay in memory as much as possible • Complex to manage • Require deep understanding also from application point of view Understand solutions: NDB
• Read scale • Data is in almost in sync all the time Bad things: • Doesn’t Scale writes • More nodes more internal overhead • Network is a very impacting factor • 1 Master only (PLEASE!!!!!) Understand solutions: Galera
thing: • Highly available • Read scale • Data is in almost in sync all the time • More network tolerant Bad things: • Doesn’t Scale writes • 1 Master only (PLEASE!!!!!) When something ends to be a new things?
thing: • Each node is independent • Read scale • Low network impact Bad things: • Stale reads • Low HA • Consistent only in the master • Each node has its own data state
are bound to 1 node • Failing read channel will cause total failure 2. No recognition of possible stale reads • In case of replication delay, application can get wrong data 3. No single entry point • Architecture must implement a double mechanism to check nodes status (topology manager) and entry point like VIP • Application must use a connector that allow multiple entry point in case of issue. (like Java connector) What they have in common?
Group) • Writes and reads are automatically redirect to working node(s) 2. Identify stale node with the binary log reader • In case of replication delay, delayed node will not receive the read requests 3. Embedded node failure detection, automatic redirection • Proxy provide native status recognition for Galera and Group replication • Only when using basic replication, you need a topology manager • Application connect to ProxySQL no need to add VIP What ProxySQL can do to be better
ProxySQL for Galera • writer_hostgroup: the hostgroup ID that refers to the WRITER • backup_writer_hostgroup: the hostgoup ID referring to the Hostgorup that will contain the candidate servers • reader_hostgroup: The reader Hostgroup ID, containing the list of servers that need to be taken in consideration • offline_hostgroup: The Hostgroup ID that will eventually contain the writer that will be put OFFLINE • active: True[1]/False[0] if this configuration needs to be used or not • max_writers: This will contain the MAX number of writers you want to have at the same time. In a sane setup this should be always 1, but if you want to have multiple writers, you can define it up to the number of nodes. • writer_is_also_reader: If true [1] the Writer will NOT be removed from the reader HG • max_transactions_behind: The number of wsrep_local_recv_queue after which the node will be set OFFLINE. This must be carefully set, observing the node behaviour. • comment: I suggest to put some meaningful notes to identify what is what.
Transaction_behind • writer_hostgroup: the hostgroup ID that refers to the WRITER • backup_writer_hostgroup: the hostgoup ID referring to the Hostgorup that will contain the candidate servers • reader_hostgroup: The reader Hostgroup ID, containing the list of servers that need to be taken in consideration • offline_hostgroup: The Hostgroup ID that will eventually contain the writer that will be put OFFLINE • active: True[1]/False[0] if this configuration needs to be used or not • max_writers: This will contain the MAX number of writers you want to have at the same time. In a sane setup this should be always 1, but if you want to have multiple writers, you can define it up to the number of nodes. • writer_is_also_reader: If true [1] the Writer will NOT be removed from the reader HG • max_transactions_behind: determines the maximum number of transactions behind the writers that ProxySQL should allow before shunning the node to prevent stale reads (this is determined by querying the transactions_behind field of the sys.gr_member_routing_candidate_status table in MySQL). • comment: I suggest to put some meaningful notes to identify what is what.
ping read_only Latency Stale READS with GTID delete from mysql_servers where hostgroup_id in (500,501); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections,comment) VALUES ('10.0.0.50',500,3306,10000,2000,'DC1 writer'); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections,comment) VALUES ('10.0.0.50',501,3306,10000,2000,'DC1 Reader'); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections,comment) VALUES ('10.0.0.51',501,3306,10000,2000,'DC1 Reader'); INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections,comment) VALUES ('10.0.0.52',501,3306,10000,2000,'DC1 Reader'); INSERT INTO mysql_replication_hostgroups VALUES (500,501,'read_only', 'Simple replica'); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; Easy to monitor select b.weight, c.* from stats_mysql_connection_pool c left JOIN runtime_mysql_servers b ON c.hostgroup=b.hostgroup_id and c.srv_host=b.hostname and c.srv_port = b.port where hostgroup in (500,501) order by hostgroup,srv_host desc; select * from mysql_server_read_only_log order by time_start_us desc limit 3; +------------+------+------------------+-----------------+-----------+-------+ | hostname | port | time_start_us | success_time_us | read_only | error | +------------+------+------------------+-----------------+-----------+-------+ | 10.0.0.52 | 3306 | 1569604127710729 | 895 | 1 | NULL | | 10.0.0.51 | 3306 | 1569604127697866 | 542 | 1 | NULL | | 10.0.0.50 | 3306 | 1569604127685005 | 795 | 0 | NULL | +------------+------+------------------+-----------------+-----------+-------+
distribute the load • Reduce the service downtime shifting to available node(s) • In case of writer down reads are served • With Binlog reader allow u to identify node with stale data also in basic replication • More stuff that we are not covering here… • Multiplexing; sharding; masking; firewalling etc.. In short your HA solution will be more resilient and flexible, which means that your business will be safer. Conclusions
proxysql_sysown
cyberagentdevelopers
kakehashi
fkino
hide212131
yoshimi0227
oracle4engineer
zakky
ny7760
hacomono
eileencodes
maltzj
bkeepers
deanohume
maggiecrowley
jcasabona
quramy
phodgson
aki_iinuma
moore
kneath
erikaheidi
