从查询使用率和查询类型频率测量MySQL“load”

Question

我有一个有很多表的mysql数据库。这个数据库为一个网站提供动力，这个网站已经获得了越来越多的流量。

我设计的数据库和查询有意避免了会造成性能瓶颈的连接，这样在特定表负载过大的情况下，我可以根据需要将表拆分到不同的服务器上(稍后，我可以根据需要拆分单个表)。

我的问题是:给定我拥有的表的数量，有没有一种简单的方法来检测哪个表和查询接收了最多的“负载”：我特别想知道具有高读写使用率的表。

我想要一些方法来判断(除了查看我的代码和日志之外)，以确定哪些表应该移动到其他服务器，以分发请求和管理资源。我使用的术语'load‘通常我知道，可能是错误的(？)。

Answer 1

这正是Percona Toolkit的目的(以及许多其他功能)。具体地说，pt-query-digest (Link)-您可以将其用于大量实用程序，从缓慢的查询到检测SQL注入。

在这种情况下，pt-query-digest可以与设置long_query_time的一般策略一起使用，通过设置long_query_time = 0将所有查询记录到slow_query_log文件中。现在，所有查询都被记录到慢查询文件中(请确保将时间重置为上一个值)。

mysql> SELECT @@GLOBAL.slow_query_log_file;
+------------------------------------------+
| @@GLOBAL.slow_query_log_file             |
+------------------------------------------+
| /var/lib/ubuntu/mysql/slowquery.log            |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL slow_query_log_file='/tmp/sniffed_queries.log';
mysql> SET GLOBAL long_query_time = 0; 
mysql> FLUSH LOGS; #Clear the logs

现在，您有了在服务器上运行的所有查询的方便日志，而不会影响常规日志或其他表，以便使用pt-query-digest进行分析：

pt-query-digest /tmp/sniffed_queries.log 

将产生一个非常有用的输出，这对于初学者来说是非常有用的：

3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

# Profile
# Rank Query ID           Response time Calls R/Call Apdx V/M   Item
# ==== ================== ============= ===== ====== ==== ===== ==========
#    1 0x92F3B1B361FB0E5B  4.0522 50.0%   312 0.0130 1.00  0.00 SELECT wp_options
#    2 0xE71D28F50D128F0F  0.8312 10.3%  6412 0.0001 1.00  0.00 SELECT poller_output poller_item
#    3 0x211901BF2E1C351E  0.6811  8.4%  6416 0.0001 1.00  0.00 SELECT poller_time
#    4 0xA766EE8F7AB39063  0.2805  3.5%   149 0.0019 1.00  0.00 SELECT wp_terms wp_term_taxonomy wp_term_relationships
#    5 0xA3EEB63EFBA42E9B  0.1999  2.5%    51 0.0039 1.00  0.00 SELECT UNION wp_pp_daily_summary wp_pp_hourly_summary wp_pp_hits wp_posts
#    6 0x94350EA2AB8AAC34  0.1956  2.4%    89 0.0022 1.00  0.01 UPDATE wp_options
#    7 0x7AEDF19FDD3A33F1  0.1381  1.7%   909 0.0002 1.00  0.00 SELECT wp_options
#    8 0x4C16888631FD8EDB  0.1160  1.4%     5 0.0232 1.00  0.00 SELECT film
#    9 0xCFC0642B5BBD9AC7  0.0987  1.2%    50 0.0020 1.00  0.01 SELECT UNION wp_pp_daily_summary wp_pp_hourly_summary wp_pp_hits
#   10 0x88BA308B9C0EB583  0.0905  1.1%     4 0.0226 1.00  0.01 SELECT poller_item
#   11 0xD0A520C9DB2D6AC7  0.0850  1.0%   125 0.0007 1.00  0.00 SELECT wp_links wp_term_relationships wp_term_taxonomy
#   12 0x30DA85C940E0D491  0.0835  1.0%   542 0.0002 1.00  0.00 SELECT wp_posts
#   13 0x8A52FE35D340A347  0.0767  0.9%     4 0.0192 1.00  0.00 TRUNCATE TABLE poller_time
#   14 0x3E84BF7C0C2A3005  0.0624  0.8%   272 0.0002 1.00  0.00 SELECT wp_postmeta
#   15 0xA01053DA94ED829E  0.0567  0.7%   213 0.0003 1.00  0.00 SELECT data_template_rrd data_input_fields
#   16 0xBE797E1DD5E4222F  0.0524  0.6%    79 0.0007 1.00  0.00 SELECT wp_posts
#   17 0xF8EC4434E0061E89  0.0475  0.6%    62 0.0008 1.00  0.00 SELECT wp_terms wp_term_taxonomy
#   18 0xCDFFAD848B0C1D52  0.0465  0.6%     9 0.0052 1.00  0.01 SELECT wp_posts wp_term_relationships
#   19 0x5DE709416871BF99  0.0454  0.6%   260 0.0002 1.00  0.00 DELETE poller_output
#   20 0x428A588445FE580B  0.0449  0.6%   260 0.0002 1.00  0.00 INSERT poller_output
# MISC 0xMISC              0.8137 10.0%  3853 0.0002   NS   0.0 
<147 ITEMS>

从这个例子中，您可以看到SELECT...FROM wp_options调用的R/调用导致了最大的负载。还有很多很棒的其他信息。如果你打算坚持使用mysql，我强烈建议你尽早使用percona-toolkit --我拖延了很长时间，仍然会因为他们本可以避免的头痛而责备自己。

Percona在这方面有一篇很棒的文章，它是为他们的Percona Server MySQL构建而写的，这很棒，但仍然应该适用：Identifying the load with the help of pt-query-digest and Percona Server

Answer 2

您可以在表上设置触发器，并让触发器将要操作的表名插入到单独的跟踪表中。

我可以看到两种方法来设置这样的触发器。

1. 每次都将触发器插入到跟踪表中。您的跟踪表将存储要操作的表的名称。然后，您可以通过按表名对跟踪表进行分组来统计更新次数。这里的问题是每次更新都需要单独的行。

1. 让跟踪表存储表名和计数。然后让触发器根据需要更新计数：

例如：

SELECT * FROM trackingTable WHERE table_name = 'sometablename'；

如果@@ROWCOUNT == 0 -- sometablename不在trackingTable中

开始

INSERT Insert INTO sometablename值(‘sometablename，1)；-- Insert Insert table，count=1

结束

否则

开始

将@count声明为INT；

设置@count = (SELECT count trackingTable WHERE table_name = 'sometablename')；

SET @count = @count+1；--计数加1

UPDATE trackingTable SET theCount=@count WHERE table_name='sometablename'；

结束