In this blog post, I’ll be looking at a new tool Proxytop for managing MySQL topologies using ProxySQL. Proxytop is a self-contained, real-time monitoring tool for ProxySQL. As some of you already know ProxySQL is a popular open source, high performance and protocol-aware proxy server for MySQL and its forks (Percona and MariaDB).
Installation of Proxytop is pretty straightforward:
## You may first need to install system Python and MySQL dev packages ## e.g. "sudo apt install python-dev libmysqlclient-dev" pip install MySQL-python npyscreen wget -P /usr/bin https://raw.githubusercontent.com/sysown/proxytop/master/proxytop
At this stage, we have everything we need to demonstrate Proxytop. The lab we have setup provides a bunch of bash scripts to demonstrate load for reruns. I’m using following script under the bin directory:
root@localhost docker-mysql-proxysql]# ./bin/docker-benchmark.bash [Fri Feb 16 10:19:58 BRST 2018] Dropping 'sysbench' schema if present and preparing test dataset:mysql: [Warning] Using a password on the command line interface can be insecure. [Fri Feb 16 10:19:58 BRST 2018] Running Sysbench Benchmarksi against ProxySQL:sysbench 1.0.12 (using bundled LuaJIT 2.1.0-beta2)
This script is totally customizable to benchmark as parameters can be tuned within the script:
PREP_THREADS=8 RUN_THREADS=16 NUM_TABLES=10 SIZE_TABLES=10000 REPORT_INTERVAL=5 TIME=120
Now let’s take a look at the Proxytop utility. It has menu driven style similarly to Innotop. Once you are in the tool, use [tab] to toggle between screens. Various shortcuts are also available to do things like changing sort order (‘s’), filter on specific criteria (‘l’) or changing the refresh interval for the view you are on (‘+’ / ‘-’).
Current, y it supports viewing the following aspects of a ProxySQL instance.
- ConnPool – “ProxySQL Connection Pool” statistics
- QueryRules – “ProxySQL Query Rules” statistics and definitions
- GloStat – “ProxySQL Global Status” statistics
- ProcList – “ProxySQL Processlist” for all incoming DML / DQL
- ComCount – “ProxySQL Command Counter” statistics
We’ll go each of these screens in detail.
This screen basically shows the Connection Pool, specifically:
- MySQL hostname and port
- Assigned ProxySQL hostgroup
- Connection statistics: Used / Free / OK / Error
- MySQL Server state in ProxySQL i.e. ONLINE / OFFLINE / etc.
- MySQL Server latency
Query Rules Screen:
This screen shows query rules and their use by count, and can be sorted either by rule_id or hits (ascending or descending) by cycling through the ordering list by pressing “s”.
It also allows you to view the actual definition of each rule by selecting and entering a rule. In the popup window, you will find a list of the relevant and defined columns for the query rule. For example:
If you have a lot of query rules defined, you can filter on a specific rule by pressing the letter “l”:
Global Statistics Screen: This screen shows Global Statistics from ProxySQL divided into four sections.
- Connection Information
- Prepared Statement Information
- Command Information
- Query Cache information
Proclist Screen: In this screen, we’re able to see running active queries with a minimum of a five-second refresh interval. In this way you can monitor long running queries in flight for troubleshooting:
ComCount Screen: This screen shows all command types executed with the total time and counts for each type, and also provides drill down to view the number of queries executed within specific ranges. This way type of workload can be easily identified both during testing and production:
You can drill down on each Com by using arrows and hitting enter key:
We all know the power of command line utilities such as proxysql-admin. The proxysql-admin utility is designed to be part of the configuration and ad-hoc monitoring of ProxySQL that is explained here in this blog post. Proxytop is designed to be menu driven to repeat commands in intervals. You can easily monitor and administer ProxySQL from the command line, but sometimes running recursive commands and monitoring over a period of time is annoying. This tool helps with that situation.