Shulou(Shulou.com)06/03 Report--

Original: https://www.percona.com/blog/2017/05/31/proxysql-assisted-pxc-maintenance-mode/

Author: Ramesh Sivaraman

In this blog post, we will show how the PXC maintenance mode uses ProxySQL to take cluster nodes offline without affecting the workload

Percona XtraDB Cluster maintenance mode

Because PXC provides a highly available solution, it must take into account the offline maintenance of cluster nodes (by isolating from the cluster or shutting down completely)

PXC does this by introducing maintenance patterns. The maintenance mode of PXC reduces the number of times that a sudden failure affects the workload, if the node is offline using ProxySQL (as a load balancer)

The central idea is to delay the actions of the core nodes and allow ProxySQL to transfer workloads

How ProxySQL manages PXC maintenance mode

In PXC maintenance mode, when a user triggers a shutdown signal (or sets a specific node to maintenance mode), ProxySQL marks the node as OFFLINE

When the user triggers the shutdown,PXC node to set pxc_maint_mode to SHUTDOWN (default DISABLED) and then sleep x seconds (determined by the parameter pxc_maint_transition_period-default 10s). ProxySQL automatically detects this change and marks the node as OFFLINE. After applying this change, ProxySQL refuses to establish a connection for any DML transaction, but existing queries can continue to execute until pxc_maint_transition_period seconds later. Once the dormancy period is complete, PXC sends a real shutdown signal-giving ProxySQL enough time to transfer the workload

If you need to set the node to maintenance mode, you can simply set the parameter pxc_maint_mode to MAINTENANCE. Then, the pxc_maint_mode is updated and the client connection begins to hibernate for x seconds (determined by pxc_maint_transition_period) before giving control to the user. ProxySQL automatically detects this change and marks the node as OFFLINE. In this case, ProxySQL avoids establishing connections for any DML transactions, but existing connections can continue to be used.

ProxySQL automatically detects changes in maintenance state and automatically reroutes traffic, thus reducing sudden workload failures

Technical details:

The ProxySQL Galera checker script (proxysql_galera_checker) continuously detects the status of each node by using the ProxySQL scheduler to check the status of the pxc_maint_mode parameters (in addition to the existing wsrep_local_state).

The scheduler is an implementation like scheduled tasks (cron-like) integrated in ProxySQL, supporting millisecond granularity.

If the script (proxysql_galera_checker) detects pxc_maint_mode = SHUTDOWN | MAINTENANCE, then proxySQL marks the node as OFFLINE_SOFT. This avoids establishing new connections (or workloads) on this node

Proxysql_galera_checker log example:

Thu Dec 8 11:21:11 GMT 2016 Enabling config Thu Dec 8 11:21:17 GMT 2016 Check server 10:127.0.0.1:25000, status ONLINE, wsrep_local_state 4 Thu Dec 8 11:21:17 GMT 2016 Check server 10:127.0.0.1:25100, status ONLINE, wsrep_local_state 4 Thu Dec 8 11:21:17 GMT 2016 Check server 10:127.0.0.1:25200, status ONLINE Wsrep_local_state 4 Thu Dec 8 11:21:17 GMT 2016 Changing server 10:127.0.0.1:25200 to status OFFLINE_SOFT due to SHUTDOWN Thu Dec 8 11:21:17 GMT 2016 Number of writers online: 2: hostgroup: 10 Thu Dec 8 11:21:17 GMT 2016 Enabling config Thu Dec 8 11:21:22 GMT 2016 Check server 10:127.0.0.1:25000, status ONLINE, wsrep_local_state 4 Thu Dec 8 11:21:22 GMT 2016 Check server 10:127.0.0.1:25100 Status ONLINE, wsrep_local_state 4 Thu Dec 8 11:21:22 GMT 2016 Check server 10:127.0.0.1:25200, status OFFLINE_SOFT, wsrep_local_state 4

The translation is not good. Criticism is welcome.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.