MySQL performance_schema无法正常工作？ [英] MySQL performance_schema not working properly?

问题描述

我有MySQL 5.6.19社区服务器在我的Ubuntu 14.04上运行。最近我一直试图监视MySQL服务器使用performance_schema。以前，我通过使用cronjob每隔5秒运行SHOW FULL PROCESSLIST查询来执行此操作。

我配置了 performance_schema.setup_consumers 表，如下所示。

  mysql>使用performance_schema; 
 mysql> select * from setup_consumers; 
 + -------------------------------- + --------- + 
 | NAME | ENABLED | 
 + -------------------------------- + --------- + 
 | events_stages_current | NO | 
 | events_stages_history | NO | 
 | events_stages_history_long | NO | 
 | events_statements_current | YES | 
 | events_statements_history | NO | 
 | events_statements_history_long | NO | 
 | events_waits_current | NO | 
 | events_waits_history | NO | 
 | events_waits_history_long | NO | 
 | global_instrumentation | YES | 
 | thread_instrumentation | YES | 
 | statements_digest | YES | 
 + -------------------------------- + --------- + 
  

默认情况下，未启用 events_statements_history 。我已启用它如下。

  mysql> update setup_consumers set enabled =YESwhere name =events_statements_history; 
查询OK，1行受影响（0.01秒）
行匹配：1更改：1警告：0 
 
 mysql> select * from setup_consumers; 
 + -------------------------------- + --------- + 
 | NAME | ENABLED | 
 + -------------------------------- + --------- + 
 | events_stages_current | NO | 
 | events_stages_history | NO | 
 | events_stages_history_long | NO | 
 | events_statements_current | YES | 
 | events_statements_history | YES | 
 | events_statements_history_long | NO | 
 | events_waits_current | NO | 
 | events_waits_history | NO | 
 | events_waits_history_long | NO | 
 | global_instrumentation | YES | 
 | thread_instrumentation | YES | 
 | statements_digest | YES | 
 + -------------------------------- + --------- + 
  

现在已启用， events_statements_history 表现在可以捕获查询，不是。以下是结果。

  mysql> select events（sql_text）from events_statements_history; 
 + ----------------- + 
 | count（sql_text）| 
 + ----------------- + 
 | 0 | 
 + ----------------- + 
  

我不明白为什么它不记录任何查询？

解决方案

当前正在执行的仪器已启用

>

启用记录数据的消费者

主题

默认情况下，每个线程（1）都被插入。

要检查线程是否插入：

$ b b

 选择THREAD_ID，NAME，PROCESSLIST_ID，PROCESSLIST_USER，PROCESSLIST_HOST，INSTRUMENTED from threads; 
 

要更改默认情况下检测的线程，请使用表setup_actors。

Instruments

默认情况下，语句（aka，查询）的工具已启用（2）。

要查看哪些查询未插入：

  select * from setup_instruments其中名称如statement /％和enabled ='NO'; 
  

要测试所有查询：

  update setup_instruments set enabled =YES其中名称像statement /％; 
  

消费者

要查看哪些消费者已检测，或更改此选项，请在表setup_consumers上使用select / update。

这个部分似乎很好，所以检查部分（1）和（2）。

I have MySQL 5.6.19 community server running on my Ubuntu 14.04. Lately I have been trying to monitor MySQL server using performance_schema. Previously I used to do this by running SHOW FULL PROCESSLIST query every 5 second using a cronjob.

I configured performance_schema.setup_consumers table as follows.

mysql> use performance_schema;
mysql> select * from setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | NO      |
| events_stages_history          | NO      |
| events_stages_history_long     | NO      |
| events_statements_current      | YES     |
| events_statements_history      | NO      |
| events_statements_history_long | NO      |
| events_waits_current           | NO      |
| events_waits_history           | NO      |
| events_waits_history_long      | NO      |
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| statements_digest              | YES     |
+--------------------------------+---------+

By default the events_statements_history is not enabled. I enabled it as follows.

mysql> update setup_consumers set enabled="YES" where name="events_statements_history";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | NO      |
| events_stages_history          | NO      |
| events_stages_history_long     | NO      |
| events_statements_current      | YES     |
| events_statements_history      | YES     |
| events_statements_history_long | NO      |
| events_waits_current           | NO      |
| events_waits_history           | NO      |
| events_waits_history_long      | NO      |
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| statements_digest              | YES     |
+--------------------------------+---------+

Now enabled, the events_statements_history table should now be able to capture queries but somehow it is not. Following is the result.

mysql> select count(sql_text) from events_statements_history;
+-----------------+
| count(sql_text) |
+-----------------+
|               0 |
+-----------------+

I do not understand as to why it is not logging in any queries?

解决方案

The performance schema records data when three conditions are met:

1. The running thread is instrumented
2. The instrument currently executed is enabled
3. The consumers for the data to record are enabled

Threads

By default, every thread (1) is instrumented.

To check if threads are instrumented:

select THREAD_ID, NAME, PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, INSTRUMENTED from threads;

To change which threads are instrumented by default, use table setup_actors.

Instruments

By default, instruments for statements (aka, queries), are enabled (2).

To see which queries are not instrumented:

select * from setup_instruments where name like "statement/%" and enabled='NO';

To instrument all queries:

update setup_instruments set enabled="YES" where name like "statement/%";

Consumers

By default, only a few consumers are instrumented (3).

To see which consumers are instrumented, or to change this, use select / update on table setup_consumers.

This part seems fine per the question, so check part (1) and (2).

这篇关于MySQL performance_schema无法正常工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！