ProxySQL is a high-performance load balancer that helps DBAs a lot and gives more control over MySQL Database. At Mydbops we use ProxySQL extensively to support our customers. We have seen a variety of use cases with ProxySQL and blogged a few
We have written a series of blogs on ProxySQL (ProxySQL Series)
Monitoring Database is the priority task of DBA, a database can be monitored via multiple third-party tools, but ProxySQL is a one-handed tool that can be good for load balancing and more DBA friendly. Let us explore if ProxySQL can be used to monitor your MySQL infra.
In this blog post, we will look at the useful queries with ProxySQL which can be used to monitor the MySQL database.
As a DBA we monitor below major things to ensure the database is up and running
Priority 1 (Service) :
Priority 2 (Performance) :
MySQL Availability Check
ProxySQL monitor uses ping to all the backend MySQL Servers and logs of success/failure status are stored in a table mysql_server_ping_log. A simple SQL based on error conditions can define its availability.
ProxySQL> select hostname,from_unixtime(time_start_us/1000000) as last_check ,ping_error as error from monitor.mysql_server_ping_log group by hostname order by time_start_us desc;
+---------------+---------------------+--------------------------------------------------------+
| hostname | last_check | error |
+---------------+---------------------+--------------------------------------------------------+
| 192.168.33.12 | 2020-01-10 11:54:49 | NULL |
| 192.168.33.14 | 2020-01-10 11:54:49 | Can't connect to MySQL server on '192.168.33.14' (115) |
| 192.168.33.11 | 2020-01-10 11:54:49 | NULL |
+---------------+---------------------+--------------------------------------------------------+
3 rows in set (0.00 sec)
Long-running (time-consuming) Queries
We can get top time-consuming queries by querying stats_mysql_query_digest table, this provides enough information about the SQL’s executed and their execution times (max/min).
Top queries based on count:
ProxySQL> select hostgroup,schemaname,count_star,(min_time/1000000) as `min_time in secs`,(max_time/1000000) as `max_time in secs`,digest_text from stats_mysql_query_digest order by count_star desc limit 10;
+-----------+------------+------------+------------------+------------------+-----------------------------------+
| hostgroup | schemaname | count_star | min_time in secs | max_time in secs | digest_text |
+-----------+------------+------------+------------------+------------------+-----------------------------------+
| 101 | sbtest | 195856 | 0 | 0 | BEGIN |
| 102 | sbtest | 131740 | 0 | 0 | SELECT c FROM sbtest6 WHERE id=? |
| 101 | sbtest | 131270 | 0 | 0 | SELECT c FROM sbtest10 WHERE id=? |
| 101 | sbtest | 131110 | 0 | 123 | SELECT c FROM sbtest5 WHERE id=? |
| 102 | sbtest | 130690 | 0 | 0 | SELECT c FROM sbtest15 WHERE id=? |
| 101 | sbtest | 130460 | 0 | 0 | SELECT c FROM sbtest9 WHERE id=? |
| 102 | sbtest | 130330 | 0 | 0 | SELECT c FROM sbtest13 WHERE id=? |
+-----------+------------+------------+------------------+------------------+-----------------------------------+
Top queries based on max executing time:
ProxySQL> select hostgroup,schemaname,count_star,(sum_time/1000000) as `sum_time in secs`,(max_time/1000000) as `max_time in secs`,digest_text from stats_mysql_query_digest order by max_time desc limit 10;
+-----------+------------+------------+------------------+------------------+--------------------------------------------------------------------+
| hostgroup | schemaname | count_star | sum_time in secs | max_time in secs | digest_text |
+-----------+------------+------------+------------------+------------------+--------------------------------------------------------------------+
| 101 | sbtest | 12264 | 187 | 123 | SELECT DISTINCT c FROM sbtest5 WHERE id BETWEEN ? AND ? ORDER BY c |
| 102 | sbtest | 125599 | 478 | 123 | SELECT c FROM sbtest5 WHERE id=? |
| 101 | sbtest | 12506 | 520 | 123 | SELECT c FROM sbtest5 WHERE id BETWEEN ? AND ? |
| 101 | sbtest | 10788 | 159 | 123 | UPDATE sbtest5 SET c=? WHERE id=? |
| 101 | sbtest | 10923 | 157 | 123 | DELETE FROM sbtest5 WHERE id=? |
| 102 | sbtest | 123900 | 227 | 0 | SELECT c FROM sbtest14 WHERE id=? |
| 101 | sbtest | 10976 | 323 | 0 | DELETE FROM sbtest14 WHERE id=? |
| 101 | sbtest | 118087 | 906 | 0 | COMMIT |
+-----------+------------+------------+------------------+------------------+--------------------------------------------------------------------+
This ProxySQL table (stats_mysql_query_digest) can reduce the overhead of analyzing slow_logs and sorting of slow queries using pt-query-digest.
Monitor MySQL Replication lag
ProxySQL monitors replication lag (second_behind_master) of all backends servers as a part of effective load balancing which were configured with max_replication_lag greater than 0(can be defined in mysql_servers table), and checks are logged in a table mysql_server_replication_lag_log.
If Seconds_Behind_Master > max_replication_lag the server is shunned until Seconds_Behind_Master < max_replication_lag
Slave lag is monitored every 10 secs by default, we can define desired value in the variable mysql-monitor_replication_lag_interval.
To monitor the backend servers a monitoring user needs to be created in Master DB and updated in ProxySQL.
SET mysql-monitor_username='monitor';
SET mysql-monitor_password='M0nito3!567';
ProxySQL> select hostname as `host` ,repl_lag as `repl_lag in secs`,error from monitor.mysql_server_replication_lag_log group by host order by repl_lag desc;
+---------------+------------------+-------+
| host | repl_lag in secs | error |
+---------------+------------------+-------+
| 192.168.33.12 | 4592 | NULL |
+---------------+------------------+-------+
1 row in set (0.00 sec)
Monitoring InnoDB cluster(GR)
For group_replication cluster we can monitor the transactions_behind from monitor.mysql_server_group_replication_log table
mysql> SELECT hostname,from_unixtime(time_start_us/1000000) as last_check,transactions_behind,error FROM monitor.mysql_server_group_replication_log group by hostname ORDER BY time_start_us DESC;
+---------------+---------------------+---------------------+-------+
| hostname | last_check | transactions_behind | error |
+---------------+---------------------+---------------------+-------+
| 192.168.33.12 | 2020-01-11 04:06:01 | 0 | NULL |
| 192.168.33.11 | 2020-01-11 04:06:01 | 15 | NULL |
| 192.168.33.13 | 2020-01-11 04:06:01 | 0 | NULL |
+---------------+---------------------+---------------------+-------+
3 rows in set (0.00 sec)
Monitoring Galera Cluster (PXC/MariaDB Cluster)
For the Galera cluster, we can monitor the transactions_behind from monitor.mysql_server_galera_log table,
mysql> select hostname,from_unixtime(time_start_us/1000000) as last_check,read_only,wsrep_local_recv_queue,wsrep_local_state,wsrep_desync,error from monitor.mysql_server_galera_log order by time_start_us desc;
+---------------+---------------------+-----------+------------------------+-------------------+--------------+--------+
| hostname | last_check | read_only | wsrep_local_recv_queue | wsrep_local_state | wsrep_desync | error |
+---------------+---------------------+-----------+------------------------+-------------------+-----------------------+
| 192.168.33.12 | 2020-01-11 06:02:01 | NO | 0 | 4 | NO |NULL |
| 192.168.33.11 | 2020-01-11 06:02:02 | NO | 0 | 4 | NO |NULL |
| 192.168.33.13 | 2020-01-11 06:02:01 | NO | 0 | 4 | NO |NULL |
+---------------+---------------------+-----------+------------------------+-------------------+--------------+--------+
3 rows in set (0.03 sec)
Monitor MySQL Errors
Table stats_mysql_errors will log MySQL encountered errors, we can be easily viewed the most recent errors that occurred.
Querying this table will reduce the overhead of viewing the physical application error log for the database.
ProxySQL> select hostgroup,hostname,schemaname,errno,count_star,from_unixtime(first_seen) as `first_seen`,from_unixtime(last_seen) as `last_seen`,last_error from stats_mysql_errors order by last_seen desc\G
*************************** 1. row ***************************
hostgroup: 101
hostname: 192.168.33.11
schemaname: sbtest
errno: 1062
count_star: 13
first_seen: 2020-01-10 06:11:18
last_seen: 2020-01-10 06:11:18
last_error: Duplicate entry '4987' for key 'PRIMARY'
*************************** 2. row ***************************
hostgroup: 101
hostname: 192.168.33.11
schemaname: sbtest
errno: 1213
count_star: 35
first_seen: 2020-01-05 05:01:11
last_seen: 2020-01-08 08:41:56
last_error: Deadlock found when trying to get lock; try restarting transaction
*************************** 3. row ***************************
hostgroup: 101
hostname: 192.168.33.11
schemaname: sbtest
errno: 1292
count_star: 18215
first_seen: 2020-01-10 06:11:18
last_seen: 2020-01-10 06:11:18
last_error: Incorrect datetime value: '39530005999-04984558244-45167224599-23327995156-31295607297' for column 'pad' at row 1
*************************** 4. row ***************************
hostgroup: 101
hostname: 192.168.33.11
schemaname: sbtest
errno: 1451
count_star: 77856
first_seen: 2020-01-10 06:11:18
last_seen: 2020-01-10 06:11:18
last_error: Cannot delete or update a parent row: a foreign key constraint fails (`sbtest`.`sbtest3`, CONSTRAINT `sbtest3_ibfk_2` FOREIGN KEY (`id`) REFERENCES `sbtest5` (`k`))
4 rows in set (0.01 sec)
Monitor read_only state of backend MySQL Servers
ProxySQL monitor read_only value for all backend servers, If read_only=1 the host is copied/moved to the reader_hostgroup, while if read_only=0 the host is copied/moved to the writer_hostgroup.
The logs will be stored in mysql_server_read_only_log table.
ProxySQL> SELECT hostname,from_unixtime(time_start_us/1000000) as last_check,read_only FROM monitor.mysql_server_read_only_log group by hostname ORDER BY time_start_us DESC;
+---------------+---------------------+-----------+
| hostname | last_check | read_only |
+---------------+---------------------+-----------+
| 192.168.33.11 | 2020-01-10 12:04:09 | 0 |
| 192.168.33.12 | 2020-01-10 12:04:09 | 1 |
+---------------+---------------------+-----------+
2 rows in set (0.00 sec)
Query for InnoDB cluster(GR)
mysql> SELECT hostname,from_unixtime(time_start_us/1000000) as last_check,read_only,error FROM monitor.mysql_server_group_replication_log group by hostname ORDER BY time_start_us DESC;
+---------------+---------------------+-----------+-------+
| hostname | last_check | read_only | error |
+---------------+---------------------+-----------+-------+
| 192.168.33.13 | 2020-01-11 03:55:01 | YES | NULL |
| 192.168.33.12 | 2020-01-11 03:55:01 | YES | NULL |
| 192.168.33.11 | 2020-01-11 03:55:01 | NO | NULL |
+---------------+---------------------+-----------+-------+
3 rows in set (0.00 sec)
Monitor SQL counters executed
Table stats_mysql_commands_counters keep records of all type of queries executed, and collects statistics based on their execution time, grouping them into buckets
ProxySQL> select (Total_Time_us/1000000) as `Total_time in secs`,Total_cnt,Command from stats.stats_mysql_commands_counters where Command IN ('SELECT','INSERT','UPDATE','DELETE') ORDER BY Total_cnt DESC;
+--------------------+-----------+---------+
| Total_time in secs | Total_cnt | Command |
+--------------------+-----------+---------+
| 40176 | 14765002 | SELECT |
| 11285 | 1969519 | UPDATE |
| 3450 | 914878 | DELETE |
| 2620 | 793321 | INSERT |
+--------------------+-----------+---------+
4 rows in set (0.01 sec)
Total_Time_us – the total time spent executing commands of that type, in microseconds
Note: From the above table we can get the count of all the SQL commands which are executed in MySQL.
Monitoring active Sessions
It can be monitored from stats_mysql_processlist table, this table simulates the same results of the “SHOW PROCESSLIST” statement.
ProxySQL> select ThreadID as `thread`,user,db,hostgroup,srv_host as `sent_host`,command,(time_ms/1000) as `time`,info from stats_mysql_processlist order by time_ms desc limit 5;
+--------+----------+--------+-----------+---------------+---------+------+--------------------------------------------------------------------------+
| thread | user | db | hostgroup | sent_host | command | time | info |
+--------+----------+--------+-----------+---------------+---------+------+--------------------------------------------------------------------------+
| 0 | proxysql | sbtest | 101 | 192.168.33.11 | Query | 0 | COMMIT |
| 2 | proxysql | sbtest | 101 | 192.168.33.11 | Query | 0 | BEGIN |
| 3 | proxysql | sbtest | 102 | 192.168.33.12 | Query | 0 | SELECT DISTINCT c FROM sbtest5 WHERE id BETWEEN 4988 AND 5087 ORDER BY c |
| 1 | proxysql | sbtest | 101 | 192.168.33.11 | Query | 0 | COMMIT |
| 0 | proxysql | sbtest | 101 | 192.168.33.11 | Query | 0 | DELETE FROM sbtest14 WHERE id=5021 |
+--------+----------+--------+-----------+---------------+---------+------+--------------------------------------------------------------------------+
5 rows in set (0.01 sec)
Monitoring History of MySQL Connections
mysql_connections table keeps a track of backend connections based on the timestamp. The ideal connections can be problematic in case of application not using persistent connections.
ProxySQL> select from_unixtime(timestamp) as timestamp,Client_Connections_aborted,Client_Connections_connected,Client_Connections_created,Server_Connections_aborted,Server_Connections_connected,Server_Connections_created,Questions,Slow_queries from stats_history.mysql_connections order by timestamp desc limit 1 \G
*************************** 1. row ***************************
timestamp: 2020-01-10 14:00:30
Client_Connections_aborted: 0
Client_Connections_connected: 12
Client_Connections_created: 36
Server_Connections_aborted: 8
Server_Connections_connected: 12
Server_Connections_created: 24
Questions: 24466490
Slow_queries: 24
1 row in set (0.01 sec)
Overall this blog will cover the most useful queries that can be used for monitoring our backend MySQL database. They can be integrated with the plugins of Nagios, Icinga, datadog, and others. Of course, this blog is more of experimenting ProxySQL as a monitoring solution for MySQL.
Ready to unlock the full potential of your MySQL databases? Explore Mydbops' open-source database management solutions and services! We offer expert guidance, tools, and resources to streamline your database operations. Visit our website or contact us today!
{{cta}}
.png)
.png)
.svg)
