Miscellaneous, Software

Enhanced replication monitoring in ProxySQL 1.4.4

ProxySQL and pt-heartbeat

The Percona toolkit provides a very useful script for more accurate replication lag monitoring called pt-heartbeat. The pt-heartbeat script provides replication lag metrics based on actual data that has been replicated and is more accurate than the Seconds_Behind_Master metrics gathered from the output of SHOW SLAVE STATUS. Seconds_Behind_Master relies purely on system time (i.e. current time – time the event executed on the master server). By default ProxySQL relies on the output of the SHOW SLAVE STATUS command in order to identify replicas that should be excluded from the backend connection pool when max_replication_lag has been configured for a server in the mysql_servers table.

Starting from ProxySQL version 1.4.4 a new feature has been introduced in order to leverage the data provided by pt-heartbeat and in turn much more precise replication lag monitoring, all that is required is to configure mysql-monitor_replication_lag_use_percona_heartbeat with the database and table name which pt-heartbeat is writing to.

This tutorial aims to provide a simple how-to article in order to setup pt-heartbeat and use the data provided for ProxySQL’s replication lag monitoring. In order to proceed you’ll need to make sure the Percona toolkit is installed on your system. Instructions can be found here. After Percona toolkit has been installed (either on a monitoring server or your master server) you can proceed with the following steps:

  • Create a percona database (on the master) to store the heartbeat data:
    $ mysql
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 7653
    Server version: 5.7.19-17-log Percona Server (GPL), Release '17', Revision 'e19a6b7b73f'
    Copyright (c) 2009-2017 Percona LLC and/or its affiliates
    Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    mysql> CREATE DATABASE IF NOT EXISTS percona;
    Query OK, 1 row affected, 1 warning (0.54 sec)
    mysql> exit;
    Bye
  • Now we’re ready to kick off the pt-heartbeat process:
    $ pt-heartbeat -D percona --update -h localhost --create-table --daemonize
  • Lets go ahead and verify pt-heartbeat is running properly on the slave:
    $ mysql
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 7653
    Server version: 5.7.19-17-log Percona Server (GPL), Release '17', Revision 'e19a6b7b73f'
    Copyright (c) 2009-2017 Percona LLC and/or its affiliates
    Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    mysql> select * from percona.heartbeat;
    +----------------------------+-----------+------------------+----------+-----------------------+---------------------+
    | ts                         | server_id | file             | position | relay_master_log_file | exec_master_log_pos |
    +----------------------------+-----------+------------------+----------+-----------------------+---------------------+
    | 2018-01-18T17:13:42.000790 | 192168111 | mysql-bin.000020 | 51025284 | mysql-bin.000012      |            40774236 |
    +----------------------------+-----------+------------------+----------+-----------------------+---------------------+
    1 row in set (0.00 sec)
    mysql> select * from percona.heartbeat;
    +----------------------------+-----------+------------------+----------+-----------------------+---------------------+
    | ts                         | server_id | file             | position | relay_master_log_file | exec_master_log_pos |
    +----------------------------+-----------+------------------+----------+-----------------------+---------------------+
    | 2018-01-18T17:13:44.001590 | 192168111 | mysql-bin.000020 | 51026250 | mysql-bin.000012      |            51026250 |
    +----------------------------+-----------+------------------+----------+-----------------------+---------------------+
    1 row in set (0.00 sec)

    We can see from the above output that the pt-heartbeat data is being written on the master and is replicating to the slave as well so now we’re ready to configure the ProxySQL host.

  • Connect to the ProxySQL Admin interface:
    $ mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'Admin> '
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 4
    Server version: 5.5.30 (ProxySQL Admin Module)
    Copyright (c) 2009-2017 Percona LLC and/or its affiliates
    Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    Admin>
  • The next step is to enable the replication lag check on all or sum of the servers as desired:
    Admin> SELECT * FROM mysql_servers;
    +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
    +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | 10           | ubuntu-vm-nv1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 20           | ubuntu-vm-nv2 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    2 rows in set (0.03 sec)
    Admin> update mysql_servers set max_replication_lag = 30;
    Query OK, 2 rows affected (0.00 sec)
    Admin> save mysql servers to disk; load mysql servers to runtime;
    Query OK, 0 rows affected (0.40 sec)
    Query OK, 0 rows affected (0.00 sec)

    Here the threshold has been set to 30(s) on all servers, feel free to adjust to whatever granularity your checks require. Remember that the frequency of the check is controlled by the mysql-monitor_replication_lag_interval variable. Make sure that max_replication_lag is not smaller than mysql-monitor_replication_lag_interval.

  • Finally configure ProxySQL to use pt-heartbeat data:
    Admin> select @@mysql-monitor_replication_lag_use_percona_heartbeat;
    +-------------------------------------------------------+
    | @@mysql-monitor_replication_lag_use_percona_heartbeat |
    +-------------------------------------------------------+
    |                                                       |
    +-------------------------------------------------------+
    1 row in set (0.00 sec)
    Admin> set mysql-monitor_replication_lag_use_percona_heartbeat = 'percona.heartbeat';
    Query OK, 1 row affected (0.00 sec)
    Admin> save mysql variables to disk; load mysql variables to runtime;
    Query OK, 93 rows affected (0.30 sec)
    Query OK, 0 rows affected (0.00 sec)

ProxySQL is now monitoring replication lag using Percona’s pt-heartbeat!

Source link

Leave a Reply

Your email address will not be published. Required fields are marked *