ProxySQL 监控和统计
很多有价值的统计数据在stats和monitor库中.
admin@127.0.0.1 [(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)
admin@127.0.0.1 [(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)
- 查看后端mysql server的连接情况
admin@127.0.0.1 [(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)
- 查看后端mysql server的PING情况
admin@127.0.0.1 [(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)
- 查看执行的命令统计情况
admin@127.0.0.1 [(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)
- 查看执行过的SQL语句统计情况
admin@127.0.0.1 [(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 * from t2 | 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)