Proxysql Monitoring and statistics

Proxysql Monitoring and statistics

A lot of valuable statistics are in the stats and monitor libraries.

[Email protected] [(None)]>show TABLES from stats;+--------------------------------------+| Tables |+--------------------------------------+| Global_variables | | Stats_memory_metrics | | Stats_mysql_commands_counters | | Stats_mysql_connection_pool | | Stats_mysql_connection_pool_reset | | Stats_mysql_global | | Stats_mysql_prepared_statements_info | | Stats_mysql_processlist | | Stats_mysql_query_digest | | Stats_mysql_query_digest_reset | | Stats_mysql_query_rules | | Stats_mysql_users | | Stats_proxysql_servers_checksums | | Stats_proxysql_servers_metrics | | Stats_proxysql_servers_status |+--------------------------------------+15 rows in Set (0.00 sec) [Email protect ed] [(None)]>show tables from monitor;+------------------------------------+| Tables |+------------------------------------+| Mysql_server_connect_log | | Mysql_server_group_replication_log | | Mysql_server_ping_log | | Mysql_server_read_only_log | | Mysql_server_replication_lag_log |+------------------------------------+5 rows in Set (0.00 sec)

• to view the connectivity of the backend MySQL server

[Email protected] [(None)]>select * from Monitor.mysql_server_connect_log ORDER by Time_start_us DESC LIMIT 10;+----------+------+---- --------------+-------------------------+---------------+| hostname | Port | Time_start_us | Connect_success_time_us | Connect_error |+----------+------+------------------+-------------------------+---------------+| db212 | 3507 | 1535188682629466 | 1516 | NULL | | db210 | 3507 | 1535188682031063 | 1192 | NULL | | db211 | 3507 | 1535188681432956 | 2308 | NULL | | db211 | 3507 | 1535188622982831 | 1132 | NULL | | db212 | 3507 | 1535188622207688 | 1439 | NULL | | db210 | 3507 | 1535188621432849 | 1078 | NULL | | db210 | 3507 | 1535188562867433 | 642 | NULL | | db212 | 3507 | 1535188562150665 | 1353 | NULL | | db211 | 3507 | 1535188561432802 | 1362 | NULL | | db211 | 3507 | 1535188503010756 | 1071 | NULL |+----------+------+------------------+-------------------------+---------------+10 rows in Set (0.00 sec)

• to view the ping of the back-end MySQL server

[Email protected] [(None)]>select * from Monitor.mysql_server_ping_log ORDER by Time_start_us DESC LIMIT 10;+----------+------+------- -----------+----------------------+------------+| hostname | Port | Time_start_us | Ping_success_time_us | Ping_error |+----------+------+------------------+----------------------+------------+| db211 | 3507 | 1535188751756034 | 231 | NULL | | db212 | 3507 | 1535188751635806 | 179 | NULL | | db210 | 3507 | 1535188751517985 | 209 | NULL | | db211 | 3507 | 1535188741749812 | 191 | NULL | | db212 | 3507 | 1535188741632588 | 156 | NULL | | db210 | 3507 | 1535188741515156 | 71 | NULL | | db211 | 3507 | 1535188731716039 | 191 | NULL | | db210 | 3507 | 1535188731615626 | 81 | NULL | | db212 | 3507 | 1535188731515197 | 265 |    Null   || db211 | 3507 | 1535188721661829 | 203 | NULL |+----------+------+------------------+----------------------+------------+10 rows in Set (0.00 sec)

• to view the statistics of the commands executed

  [email protected] [(none)]>select Command,total_time_us,total_cnt,cnt_100us,cnt_1ms,cnt_infs From Stats_mysql_commands_counters WHERE total_cnt;+---------+---------------+-----------+-----------+---------+- ---------+| Command | Total_time_us | total_cnt | Cnt_100us | cnt_1ms | Cnt_infs |+---------+---------------+-----------+-----------+---------+----------+| BEGIN | 22310507194 | 1585457 | 51 | 129945 | 2113 | | COMMIT | 1060260349 | 826562 | 4233 | 7349 | 0 | | DELETE | 22825509825 | 1057796 | 8455 | 227783 | 52 | | INSERT | 179673106152 | 20885757 | 8469 | 129291 | 0 | | SELECT | 8888256088 | 22149881 | 56184 | 3228537 | 1 | | UPDATE | 134309337044 | 3174979 | 16886 | 547909 | 320 | | SHOW | 6485 | 5 | 0 | 1 | 0 |+---------+---------------+-----------+-----------+---------+----------+7 rows in Set (0.00 sec)  

• to view the statistics of SQL statements executed

[Email protected] [(None)]>select hostgroup,schemaname,digest,digest_text,count_star,sum_time from Stats_mysql_query_digest ORDER by Sum_time desc;+-----------+--------------------+--------------------+---------------------------------------- ----------------------------+------------+-------------+| HostGroup | SchemaName | Digest | Digest_text | Count_star | Sum_time |+-----------+--------------------+--------------------+---------------------------------------------- ----------------------+------------+-------------+| 10 | Sysbench_testdata | 0xd284ea74c062daa9 |            INSERT into Sbtest2 (ID, k, c, pad) VALUES (?,?,?,?) | 10442025 | 89906803469 | | 10 | Sysbench_testdata | 0xe52a0a0210634dac |            INSERT into Sbtest1 (ID, k, c, pad) VALUES (?,?,?,?) | 10443732 | 89766302683 | | 10 | Sysbench_testdata | 0X16ADA60275E5EFAA | UPDATE sbtest2 SET k=k+? WHERE id=? | 794387 | 46930347033 | | 10 | Sysbench_testdata | 0xc198e52bccb481c7 | UPDATE sbtest1 SET k=k+?                                WHERE id=? | 793183 | 46790103859 | | 10 | Sysbench_testdata | 0xfad1519e4760cbde | BEGIN | 1587889 | 46631879754 | | 10 | Sysbench_testdata | 0xfb239bc95a23ca36 | UPDATE sbtest1 SET c=?                                  WHERE id=? | 794260 | 20383945734 | | 10 | Sysbench_testdata | 0xc2e5fce49337737d | UPDATE sbtest2 SET c=?                                  WHERE id=? | 793149 | 20204940418 | | 10 | Sysbench_testdata | 0x713a0db06fce81e2 |                                     DELETE from Sbtest2 WHERE id=? | 528677 | 11583020333 | | 10 | Sysbench_testdata | 0xe365beb555319b9e |                                     DELETE from Sbtest1 WHERE id=? | 529119 | 11242489492 | | 11 |  Sysbench_testdata| 0x9af59b998a3688ed |                                   SELECT c from Sbtest2 WHERE id=? | 7907798 | 2336353929 | | 11 | Sysbench_testdata | 0xbf001a0c13781c1d |                                   SELECT c from Sbtest1 WHERE id=? | 7891798 | 2334872578 | | 10 | Sysbench_testdata | 0X695FBF255DBEB0DD | COMMIT | 826562 | 1060260349 | | 11 | Sysbench_testdata | 0x4ac6cc3e8e66e2a5 | SELECT DISTINCT C from sbtest2 WHERE ID between? and? ORDER by C | 794166 | 952405491 | | 11 | Sysbench_testdata | 0xc19480748ae79b4b | SELECT DISTINCT C from sbtest1 WHERE ID between? and? ORDER by C | 793404 | 948846057 | | 11 | Sysbench_testdata | 0xac80a5ea0101522e | SELECT C from Sbtest1 WHERE ID between? and? ORDER by C | 795040 | 478417319 | | 11 | Sysbench_testdata | 0x2bd5ca9a9c3b517d | SELECT C from Sbtest2 WHERE ID between? and? ORDER by C | 792530 | 475211391 | | 11 | Sysbench_testdata | 0x381aad21f4326865 | SELECT C from Sbtest2 WHERE ID between?                     and? | 794962 | 361663394 | | 11 | Sysbench_testdata | 0x290b92fd743826da | SELECT C from Sbtest1 WHERE ID between?                     and? | 792608 | 361139683 | | 11 | Sysbench_testdata | 0xdbf868b2aa296bc5 | SELECT SUM (k) from Sbtest1 WHERE ID between?                and? | 793851 | 315185628 | | 11 | Sysbench_testdata | 0x106724e15cc958ca | SELECT SUM (k) from Sbtest2 WHERE ID between?                and? | 793719 | 314158192 | | 11 | Information_schema | 0x26cc5cb63fe90834 | SELECT * FROM Wenyz.t2 | 2 | 10001358 | | 10 | Information_schema | 0x02033e45904d3df0 | Show Databases | 5 | 6485 | | 11 | Information_schema | 0x1ca8f88b407b7239 | SELECT * FROMT2 | 1 | 1068 | | 10 | Information_schema | 0x226cd90d52a2ba0b |                                   SELECT @ @version_comment limit? | 1 | 0 | | 10 | Information_schema | 0x594f2c744b698066 | Select USER () | 1 | 0 |+-----------+--------------------+--------------------+------------------------------------------------------ --------------+------------+-------------+25 rows in Set (0.00 sec)

Proxysql Monitoring and statistics