• 通过performance schema收集慢查询


    MySQL5.6起performance schema自动开启,里面涉及记录 statement event的表

    mysql> show tables like '%statement%';
    +----------------------------------------------------+
    | Tables_in_performance_schema (%statement%)         |
    +----------------------------------------------------+
    | events_statements_current                          |
    | events_statements_history                          |
    | events_statements_history_long                     |
    | events_statements_summary_by_account_by_event_name |
    | events_statements_summary_by_digest                |
    | events_statements_summary_by_host_by_event_name    |
    | events_statements_summary_by_program               |
    | events_statements_summary_by_thread_by_event_name  |
    | events_statements_summary_by_user_by_event_name    |
    | events_statements_summary_global_by_event_name     |
    | prepared_statements_instances                      |
    +----------------------------------------------------+
    11 rows in set (0.00 sec)

    statement event 的设置 

    详细参考官方文档:https://dev.mysql.com/doc/refman/5.6/en/performance-schema-statement-tables.ht

    server端发生的所有的statement都会记录,当前未结束的会话发生的statement记录在表events_statements_current,已结束的会话线程发生的statement记录在events_statements_history,此表有大小限制,默认是100,达到限制会删除老的statement

    设置记录哪些类型的statement,默认设置为所有的类型

    mysql> SELECT * FROM setup_instruments WHERE NAME LIKE 'statement/%';
    +---------------------------------------------+---------+-------+
    | NAME                                        | ENABLED | TIMED |
    +---------------------------------------------+---------+-------+
    | statement/sql/select                        | YES     | YES   |
    | statement/sql/create_table                  | YES     | YES   |
    | statement/sql/create_index                  | YES     | YES   |
    ...
    | statement/sp/stmt                           | YES     | YES   |
    | statement/sp/set                            | YES     | YES   |
    | statement/sp/set_trigger_field              | YES     | YES   |
    | statement/scheduler/event                   | YES     | YES   |
    | statement/com/Sleep                         | YES     | YES   |
    | statement/com/Quit                          | YES     | YES   |
    | statement/com/Init DB                       | YES     | YES   |
    ...
    | statement/abstract/Query                    | YES     | YES   |
    | statement/abstract/new_packet               | YES     | YES   |
    | statement/abstract/relay_log                | YES     | YES   |
    +---------------------------------------------+---------+-------+

    关于对statement event相关表的设置,MySQL5.6 默认events_statements_history关闭,MySQL5.7开启

    mysql> SELECT * FROM setup_consumers WHERE NAME LIKE '%statements%';
    +--------------------------------+---------+
    | NAME                           | ENABLED |
    +--------------------------------+---------+
    | events_statements_current      | YES     |
    | events_statements_history      | NO      |
    | events_statements_history_long | NO      |
    | statements_digest              | YES     |
    +--------------------------------+---------+

    如果通过pmm来收集慢查询,其查询的是表events_statements_history_long,MySQL5.6需要启用此表

    启用events_statements_history

    UPDATE setup_consumers SET ENABLED = 'NO'
    WHERE NAME LIKE 'events_statements_history';
     
  • 相关阅读:
    TCP粘包的拆包处理
    字节序列化
    同步,异步,阻塞和非阻塞
    用Doxygen生成文档
    Visual Studio新建的源文件的默认编码
    Boost编程之获取可执行文件的当前路径
    特征点寻找的基础数据结构和函数
    寻找Harris、Shi-Tomasi和亚像素角点
    特征点的基本概念和如何找到它们
    工业相机与普通相机的差别
  • 原文地址:https://www.cnblogs.com/Bccd/p/8331405.html
Copyright © 2020-2023  润新知