Contact Us
How To Setup ProxySQL(MySql Proxy) Server ? Part -2
In the previous article – How To Setup ProxySQL Server? PART-1, we learned about setting up ProxySQL and this article is more on the same
So moving forward with what we had from PART-1
Our Scenario:
Host OS: Ubuntu 18.04
RAM: 2GB memory
Proxy server: 172.31.2.24
Master IP: 172.31.2.196
Slave IP:-172.31.2.162
Let’s Start
1. Configure Monitoring
ProxySQL constantly monitors the servers it has configured. To do so, it is important to configure some interval and timeout variables ( in milliseconds ). we can do it by a simple SQL command. the value of 2000 for variable_value=2000 is for the interval.
Admin> UPDATE global_variables SET variable_value=2000 WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.00 sec)
Admin> UPDATE global_variables SET variable_value = 1000 where variable_name = 'mysql-monitor_connect_timeout';
Query OK, 1 row affected (0.00 sec)
Admin> UPDATE global_variables SET variable_value = 500 where variable_name = 'mysql-monitor_ping_timeout';
Query OK, 1 row affected (0.00 sec)
Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 116 rows affected (0.02 sec)
With the below configuration, servers will only be shunned in case the replication delay exceeds 60 seconds ( 1 min) behind the master
Admin> UPDATE mysql_servers SET max_replication_lag=60;
2. Check Backend Status
when our servers are under ProxySQL we can monitor its status with the below command and shows that ProxySQL can successfully connect to all backends.
mysql> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 3; +--------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +--------------+------+------------------+----------------------+------------+ | 172.31.2.162 | 3306 | 1563269179814946 | 549 | NULL | | 172.31.2.196 | 3306 | 1563269179787950 | 494 | NULL | | 172.31.2.162 | 3306 | 1563269177825857 | 541 | NULL | +--------------+------+------------------+----------------------+------------+
3. Check Query Distribution
Here we will use “sysbench” user to check query distributions on the cluster, the output of the command shows the number of queries executed per host of ProxySQL
mysql> select hostgroup,srv_host,status,Queries,Bytes_data_sent,Latency_us from stats_mysql_connection_pool where hostgroup in (0,1); +-----------+--------------+--------+---------+-----------------+------------+ | hostgroup | srv_host | status | Queries | Bytes_data_sent | Latency_us | +-----------+--------------+--------+---------+-----------------+------------+ | 0 | 172.31.2.196 | ONLINE | 0 | 0 | 512 | | 1 | 172.31.2.162 | ONLINE | 1 | 18 | 500 | | 1 | 172.31.2.196 | ONLINE | 0 | 0 | 512 | +-----------+--------------+--------+---------+-----------------+------------+
4. Troubleshoot General ProxySQL issues
Let’s check for host status with the command and if the third column shows the status is SHUNNED then ProxySQL won’t send any queries until it comes back to ONLINE.
mysql> select hostgroup,srv_host,status,Queries,Bytes_data_sent,Latency_us from stats_mysql_connection_pool where hostgroup in (0,1); +-----------+--------------+---------+---------+-----------------+------------+ | hostgroup | srv_host | status | Queries | Bytes_data_sent | Latency_us | +-----------+--------------+---------+---------+-----------------+------------+ | 0 | 172.31.2.196 | ONLINE | 0 | 0 | 512 | | 1 | 172.31.2.162 | SHUNNED | 1 | 18 | 500 | | 1 | 172.31.2.196 | ONLINE | 0 | 0 | 512 | +-----------+--------------+---------+---------+-----------------+------------+
so to change its status Gracefully for maintenance set the status to OFFLINE_SOFT and if you think you need to make offline immediately set its status to OFFLINE_HARD(Immediately)
we can do it with a simple command
mysql> UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostname='172.31.2.162';
5. Configure Query Rules
we need to create query rules to route our traffic on the basis we want.
1. For SELECT
To send all SELECT queries on the slave ( based on Regex ), we will set a regex to check for queries starting with select.
Admin> INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^SELECT.*', 1, 0); Query OK, 1 row affected (0.00 sec)
Let’s check the status of the regex
mysql> SELECT rule_id, match_digest,destination_hostgroup hg_id, apply FROM mysql_query_rules WHERE active=1; +---------+---------------------+-------+-------+ | rule_id | match_digest | hg_id | apply | +---------+---------------------+-------+-------+ | 1 | ^SELECT.* | 1 | 0 | +---------+---------------------+-------+-------+
2. For Update
Again we will use the regex to route or traffic to the master MySQL host
mysql> INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^SELECT.*FOR UPDATE', 0, 1); Query OK, 1 row affected (0.00 sec)
Let’s check the status of the regex
mysql> SELECT rule_id, match_digest,destination_hostgroup hg_id, apply FROM mysql_query_rules WHERE active=1; +---------+---------------------+-------+-------+ | rule_id | match_digest | hg_id | apply | +---------+---------------------+-------+-------+ | 1 | ^SELECT.* | 1 | 0 | | 2 | ^SELECT.*FOR UPDATE | 0 | 1 | +---------+---------------------+-------+-------+ 2 rows in set (0.00 sec)
we see that we have 2 rows for select and update with host group id 1 for select and host group id 0 for select and after every update in setting run the below command.
2. For Updatemysql> LOAD MYSQL QUERY RULES TO RUNTIME; Query OK, 0 rows affected (0.00 sec) mysql> SAVE MYSQL QUERY RULES TO DISK; Query OK, 0 rows affected (0.08 sec)
6. Check Configuration:
In our case, the user “sysbench” has a default_hostgroup=0, therefore any query not matching the above rules [e.g. ALL WRITES ] will be sent to host group 0 [Master]. Below stats, tables are used to validate if your query rules getting used by incoming traffic. we can check our configuration like below
mysql> SELECT rule_id, hits, destination_hostgroup hg FROM mysql_query_rules NATURAL JOIN stats_mysql_query_rules; +---------+------+----+ | rule_id | hits | hg | +---------+------+----+ | 1 | 0 | 1 | | 2 | 0 | 0 | +---------+------+----+
also, we can validate our DB connection for the same user on the proxy server itself.
root@ip-172-31-2-24:/home/kapendra.singh# mysql -u sysbench -psysbench -h 127.0.0.1 -P6033 -e "SELECT @@server_id" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 2 | +-------------+
Conclusion: we tried to cover the most useful scenarios and queries that can be used for monitoring our backend MySQL database. However, we can always push this data to several monitoring services like Nagios, Icinga, datadog, and others with custom-made plugins available. Don’t get scared to get your hands dirty in experimenting with ProxySQL as a monitoring solution for MySQL.
