MySQL Performance Schema

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL Performance Schema

关尚 2018-01-31 11:19:58 浏览3227
展开阅读全文

目前我们在5.7打开了Performance Schema,使用默认的设置。现在打算新增以下这一项,这样可以监控内存使用情况。 

        performance-schema-instrument='memory/%=COUNTED' 

看了手册,似乎只有一种方法:在my.cnf添加上述项,重启MySQL生效。 

请问 

(1)有没有办法不需要重启MySQL就可以生效? 

(2)根据了解,添加该采集项对MySQL性能应该没有明显的影响。能否帮忙确认? 

(3)除此之外,有没有其他推荐的performance schema采集项?性能影响如何?

 

开启performance_schema:

[mysqld]

performance_schema=ON

 

查看是否支持performance_schema

mysql> select * from information_schema.engines where engine ='performance_schema';

+--------------------+---------+--------------------+--------------+------+------------+

| ENGINE             | SUPPORT | COMMENT            | TRANSACTIONS | XA   | SAVEPOINTS |

+--------------------+---------+--------------------+--------------+------+------------+

| PERFORMANCE_SCHEMA | YES     | Performance Schema | NO           | NO   | NO         |

+--------------------+---------+--------------------+--------------+------+------------+

1 row in set (0.00 sec)

 

是否开启performance_schema

mysql> show variables like 'performance_schema';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| performance_schema | ON    |

+--------------------+-------+

1 row in set (0.00 sec)

 

performance_schema下有些什么:

performance_schema下有哪些视图表,可以通过如下语句进行查看:

mysql> select table_name from information_schema.tables where table_schema='performance_schema' and engine='pperformance_schema';

 

use performance_schema;

mysql> show create table users\G;

*************************** 1. row ***************************

       Table: users

Create Table: CREATE TABLE `users` (

  `USER` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

  `CURRENT_CONNECTIONS` bigint(20) NOT NULL,

  `TOTAL_CONNECTIONS` bigint(20) NOT NULL

) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

performance_schema的视图表都是performance_schema的引擎,这种引擎数据保存在内存里。

 

主要有:按照存储事件信息类型纬度可分为(stage、statement、transaction、wait每一个事件类型又可以进行一步分类为user、host、thread、global等),按照对象纬度可分为(file、instance、table、lock等),以及一些无法很好归类的事件和配置表setup_xx等。

 

performance_schema如何配置:

         instruments表示用于监视某个资源使用消耗的仪器,consumersinstruments采集到的数据进行展示,存储的地方,两者采用生产者/消费者模型,instruments为生产者,consumers为消费者,两者都各自有一个setup_xx配置表。

       数据库刚刚初始化启动时,并非所有instrumentsconsumers都启用了,所以默认不会收集所有的事件,可能你需要检测的事件并没有打开,需要进行设置,可以使用如下语句打开对应的instrumentsconsumers(下面以waits类型为例进行演示说明)

 

use performance_schema;

采集项:

mysql> update setup_instruments set ENABLED = 'YES', TIMED='YES' where name like 'wait%';

Query OK, 269 rows affected (0.00 sec)

Rows matched: 323  Changed: 269  Warnings: 0

存储的地方:

mysql> update setup_consumers set ENABLED = 'YES' where name like '%wait%';

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0

 

当配置好之后,MySQL 在各种工作过程中,与wait相关的事件就会被instruments收集,并保存在performance_schema下wait相关的表中,例如:

mysql> select * from events_waits_current limit 1\G;

*************************** 1. row ***************************

            THREAD_ID: 10                         线程ID

             EVENT_ID: 9960                       事件ID

         END_EVENT_ID: 9960

           EVENT_NAME: wait/synch/mutex/innodb/buf_dblwr_mutex

EVENT_NAME:事件的采集项。wait开头表明为等待事件,synch表示同步等待事件,mutex:一个互斥的同步等待事件,innodb 表示是innodb存储引擎

               SOURCE: buf0dblwr.cc:954    事件采集器源码的文件名,954所在行数

          TIMER_START: 2225735904132400    事件开始时间

            TIMER_END: 2225735904185200    事件结束时间

           TIMER_WAIT: 52800               TIMER_START-TIMER_WAIT 得到的时间

                SPINS: NULL

        OBJECT_SCHEMA: NULL

          OBJECT_NAME: NULL

           INDEX_NAME: NULL

          OBJECT_TYPE: NULL

OBJECT_INSTANCE_BEGIN: 120996632

     NESTING_EVENT_ID: NULL

   NESTING_EVENT_TYPE: NULL

            OPERATION: lock

      NUMBER_OF_BYTES: NULL

                FLAGS: NULL

1 row in set (0.00 sec)

 

        但该表只能查询到每个线程当前正在进行的事件,一旦线程执行完成某个事情,对应的事件信息就会从该表中清理掉。

 

        _current表中每个线程只保留一条记录,且一旦线程完成工作,该表中不会再记录该线程的事件信息,_history表中记录每个线程已经执行完成的事件信息,但每个线程的事件信息只记录10条,再多就会被覆盖掉

mysql> select THread_id,event_name,timer_wait from events_waits_history order by thread_id limit 21;

+-----------+-----------------------------------------------+------------+

| THread_id | event_name                                    | timer_wait |

+-----------+-----------------------------------------------+------------+

|        10 | wait/synch/mutex/innodb/buf_pool_mutex        |     106920 |

|        10 | wait/synch/mutex/innodb/buf_pool_mutex        |      50600 |

|        10 | wait/synch/mutex/innodb/flush_list_mutex      |     149600 |

|        10 | wait/synch/mutex/innodb/buf_pool_mutex        |      54120 |

|        10 | wait/synch/mutex/innodb/buf_dblwr_mutex       |      55000 |

|        10 | wait/synch/mutex/innodb/buf_pool_mutex        |     128040 |

|        10 | wait/synch/mutex/innodb/buf_pool_mutex        |      52800 |

|        10 | wait/synch/mutex/innodb/flush_list_mutex      |      58520 |

|        10 | wait/synch/mutex/innodb/buf_pool_mutex        |      50600 |

|        10 | wait/synch/mutex/innodb/buf_dblwr_mutex       |      52800 |

|        16 | wait/synch/mutex/innodb/flush_list_mutex      |     123640 |

|        16 | wait/synch/mutex/innodb/flush_list_mutex      |     111320 |

|        16 | wait/synch/mutex/innodb/flush_list_mutex      |     114840 |

|        16 | wait/synch/mutex/innodb/flush_list_mutex      |     111320 |

|        16 | wait/synch/mutex/innodb/flush_list_mutex      |     204600 |

|        16 | wait/synch/mutex/innodb/flush_list_mutex      |      48400 |

|        16 | wait/synch/mutex/innodb/log_flush_order_mutex |     111320 |

|        16 | wait/synch/mutex/innodb/log_sys_mutex         |      48400 |

|        16 | wait/synch/mutex/innodb/flush_list_mutex      |     107800 |

|        16 | wait/synch/mutex/innodb/flush_list_mutex      |     172040 |

|        21 | wait/synch/mutex/innodb/sync_array_mutex      |     493680 |

+-----------+-----------------------------------------------+------------+

21 rows in set (0.04 sec)

 

*_history_long表中记录所有线程的事件信息,但总记录数量是10000行,超过会被覆盖掉

mysql> select count(*) from events_waits_history_long;

+----------+

| count(*) |

+----------+

|    10000 |

+----------+

1 row in set (0.01 sec)

 

前面的events_wait_xx表只是对某个线程产生的事件的流水帐记录,如果要对某个事件进行汇总查询,例如;想按照主机、用户、线程进行汇总查询某些top N开销的事件,可以使用如下表查询(注意,这些视图中的每一行相关数据列都是已经经过sum汇总计算过的值)

mysql> show tables from performance_schema like 'events_waits_summ%';

+---------------------------------------------------+

| Tables_in_performance_schema (events_waits_summ%) |

+---------------------------------------------------+

| events_waits_summary_by_account_by_event_name     |

| events_waits_summary_by_host_by_event_name        |

| events_waits_summary_by_instance                  |

| events_waits_summary_by_thread_by_event_name      |

| events_waits_summary_by_user_by_event_name        |

| events_waits_summary_global_by_event_name         |

+---------------------------------------------------+

6 rows in set (0.00 sec)

 

查看哪些instruments调用的次数最多

mysql> select event_name,count_star from events_waits_summary_global_by_event_name order by count_star desc limit 10;

+-----------------------------------------------+------------+

| event_name                                    | count_star |

+-----------------------------------------------+------------+

| wait/synch/mutex/innodb/buf_pool_mutex        |      70200 |

| wait/synch/mutex/innodb/flush_list_mutex      |      46800 |

| wait/io/file/innodb/innodb_data_file          |      45661 |

| wait/synch/mutex/innodb/buf_dblwr_mutex       |      23400 |

| wait/synch/mutex/innodb/log_sys_mutex         |       8775 |

| wait/synch/mutex/innodb/sync_array_mutex      |       5850 |

| wait/synch/mutex/innodb/log_flush_order_mutex |       2925 |

| wait/synch/mutex/innodb/log_sys_write_mutex   |       2925 |

| wait/synch/mutex/innodb/dict_sys_mutex        |       2925 |

| wait/synch/mutex/innodb/fil_system_mutex      |       2925 |

+-----------------------------------------------+------------+

10 rows in set (0.04 sec)

查看哪些instruments占用最多的时间

mysql> select event_name,sum_timer_wait from events_waits_summary_global_by_event_name order by sum_timer_wait desc limit 10;

+----------------------------------------+------------------+

| event_name                             | sum_timer_wait   |

+----------------------------------------+------------------+

| idle                                   | 9884968840000000 |

| wait/io/file/innodb/innodb_data_file   |   18041546509800 |

| wait/io/file/sql/FRM                   |    1236525224000 |

| wait/io/file/innodb/innodb_log_file    |     267242296200 |

| wait/io/file/mysys/cnf                 |     105668696320 |

| wait/io/file/sql/binlog                |     100375995720 |

| wait/io/file/myisam/kfile              |      39801566200 |

| wait/io/file/sql/binlog_index          |      14210137040 |

| wait/io/file/sql/slow_log              |       8421798000 |

| wait/synch/mutex/innodb/buf_pool_mutex |       7315224400 |

+----------------------------------------+------------------+

10 rows in set (0.01 sec)

 

performance_schema编译时配置

   MySQL 5.5开始就使用cmake编译,所以如何你打算使用编译安装,那么在cmake编译时,可以使用编译选项打开或关闭performance_schema相关instruments,注意:performance_schema是强制打开的,但是其中的一些instruments是可以开关的,如:

cmake .\

 -DDISABLE_PSI_STAGE=1            关闭STAGEG事件监视器

 -DDISABLE_PSI_STATEMENT =1       关闭STATEMENT事件监视器

最好这种参数不要更改,不然用的时候需要重新编译。

 

system variables 共有42个,这些配置参数都是只读的,用于控制performance_schema是否启用以及相关资源的预值控制(例如某个consumers表能够存储多少数据,某个资源能够分配多少数额以及能够分配多少内存等),其中值为-1的是自动计算的,不需要人为指定;如:

这些参数启动前一定要配置好,启动是只读的

mysql> mysql> show variables like  '%performance_schema%';

+----------------------------------------------------------+-------+

| Variable_name                                            | Value |

+----------------------------------------------------------+-------+

| performance_schema                                       | ON    |

| performance_schema_accounts_size                         | -1    |

| performance_schema_digests_size                          | 10000 |

| performance_schema_events_stages_history_long_size       | 10000 |

| performance_schema_events_stages_history_size            | 10    |

| performance_schema_events_statements_history_long_size   | 10000 |

| performance_schema_events_statements_history_size        | 10    |

| performance_schema_events_transactions_history_long_size | 10000 |

| performance_schema_events_transactions_history_size      | 10    |

| performance_schema_events_waits_history_long_size        | 10000 |

| performance_schema_events_waits_history_size             | 10    |

| performance_schema_hosts_size                            | -1    |

| performance_schema_max_cond_classes                      | 80    |

| performance_schema_max_cond_instances                    | -1    |

| performance_schema_max_digest_length                     | 1024  |

| performance_schema_max_file_classes                      | 80    |

| performance_schema_max_file_handles                      | 32768 |

| performance_schema_max_file_instances                    | -1    |

| performance_schema_max_index_stat                        | -1    |

| performance_schema_max_memory_classes                    | 320   |

| performance_schema_max_metadata_locks                    | -1    |

| performance_schema_max_mutex_classes                     | 210   |

| performance_schema_max_mutex_instances                   | -1    |

| performance_schema_max_prepared_statements_instances     | -1    |

| performance_schema_max_program_instances                 | -1    |

| performance_schema_max_rwlock_classes                    | 40    |

| performance_schema_max_rwlock_instances                  | -1    |

| performance_schema_max_socket_classes                    | 10    |

| performance_schema_max_socket_instances                  | -1    |

| performance_schema_max_sql_text_length                   | 1024  |

| performance_schema_max_stage_classes                     | 150   |

| performance_schema_max_statement_classes                 | 193   |

| performance_schema_max_statement_stack                   | 10    |

| performance_schema_max_table_handles                     | -1    |

| performance_schema_max_table_instances                   | -1    |

| performance_schema_max_table_lock_stat                   | -1    |

| performance_schema_max_thread_classes                    | 50    |

| performance_schema_max_thread_instances                  | -1    |

| performance_schema_session_connect_attrs_size            | 512   |

| performance_schema_setup_actors_size                     | -1    |

| performance_schema_setup_objects_size                    | -1    |

| performance_schema_users_size                            | -1    |

+----------------------------------------------------------+-------+

42 rows in set (0.00 sec)

 

 

Performance_schema中的instrumentsconsumers配置表一共6个,在运行时修改配置,可以通过DML语句进行修改,通过select语句进行查询,可以使用update进行修改

mysql> mysql> select table_name from information_schema.tables where table_schema = 'performance_schema' and table_name liup%' or table_name like 'threads';

+-------------------+

| table_name        |

+-------------------+

| setup_actors      |

| setup_consumers   |

| setup_instruments |

| setup_objects     |

| setup_timers      |

| threads           |

+-------------------+

6 rows in set (0.00 sec)

 

先来看看set_instruments配置表,该表的作用是instruments的开关,在MySQL5.7.18版本中共有1028个配置项,其中一些是预设打开的,相当一部分是没有打开 的,如果需要使用,需要修改这些配置项的ENABLEDTIMED列为YES

采集器:

mysql> select * from  setup_instruments limit 10;                                                    

+---------------------------------------------------------+---------+-------+

| NAME                                                    | ENABLED | TIMED |

+---------------------------------------------------------+---------+-------+

| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc               | YES     | YES   |

| wait/synch/mutex/sql/LOCK_des_key_file                  | YES     | YES   |

| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit         | YES     | YES   |

| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue   | YES     | YES   |

| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done           | YES     | YES   |

| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue    | YES     | YES   |

| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index          | YES     | YES   |

| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log            | YES     | YES   |

| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos | YES     | YES   |

| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync           | YES     | YES   |

+---------------------------------------------------------+---------+-------+

10 rows in set (0.00 sec)

采集项

mysql> select count(*) from setup_instruments;

+----------+

| count(*) |

+----------+

|     1028 |

+----------+

1 row in set (0.00 sec)

 

  setup_consumers配置表,该表的作用是consumers的开关,在MySQL5.7.18版本中共有15个配置项,其中一些是预设打开的,相当一部分是没有打开的,如果需要使用,需要修改这些配置项的ENABLED列为YES

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_transactions_current      | NO      |

| events_transactions_history      | NO      |

| events_transactions_history_long | NO      |

| events_waits_current             | YES     |

| events_waits_history             | YES     |

| events_waits_history_long        | YES     |

| global_instrumentation           | YES     |

| thread_instrumentation           | YES     |

| statements_digest                | YES     |

+----------------------------------+---------+

15 rows in set (0.00 sec)

15个表有程级关系

global_instrumentation   全局最高配置项,如果这项没有打开其它的都是空的

thread_instrumentation   这个参数也要打开

 

        setup_actors配置表,该表的作用是控制是否开启按照用户,主机纬度划分的前台线程的监控,默认只有一条配置项,可以全名用insert语句添加或者update语句修改,delete语句删除

    ENABLEDHISTORY列表示是否启用该用户线程的instruments和历史事件日志记录,具体是否生效还需要看其他相关配置表(该表相当于是在instrumentsconsumers配置表的上一层开关配置)

mysql> select * from setup_actors;

+------+------+------+---------+---------+

| HOST | USER | ROLE | ENABLED | HISTORY |

+------+------+------+---------+---------+

| %    | %    | %    | YES     | YES     |

+------+------+------+---------+---------+

1 row in set (0.00 sec)

 

mysql> select * from threads where type='FOREGROUND' limit 1\G;

*************************** 1. row ***************************

          THREAD_ID: 28

               NAME: thread/sql/compress_gtid_table

               TYPE: FOREGROUND

     PROCESSLIST_ID: 1

   PROCESSLIST_USER: NULL

   PROCESSLIST_HOST: NULL

     PROCESSLIST_DB: NULL

PROCESSLIST_COMMAND: Daemon

   PROCESSLIST_TIME: 17133

  PROCESSLIST_STATE: Suspending

   PROCESSLIST_INFO: NULL

   PARENT_THREAD_ID: 1

               ROLE: NULL

       INSTRUMENTED: YES

            HISTORY: YES

    CONNECTION_TYPE: NULL

       THREAD_OS_ID: 5930

1 row in set (0.00 sec)

 

mysql> select * from setup_objects;

+-------------+--------------------+-------------+---------+-------+

| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |

+-------------+--------------------+-------------+---------+-------+

| EVENT       | mysql              | %           | NO      | NO    |

| EVENT       | performance_schema | %           | NO      | NO    |

| EVENT       | information_schema | %           | NO      | NO    |

| EVENT       | %                  | %           | YES     | YES   |

| FUNCTION    | mysql              | %           | NO      | NO    |

| FUNCTION    | performance_schema | %           | NO      | NO    |

| FUNCTION    | information_schema | %           | NO      | NO    |

| FUNCTION    | %                  | %           | YES     | YES   |

| PROCEDURE   | mysql              | %           | NO      | NO    |

| PROCEDURE   | performance_schema | %           | NO      | NO    |

| PROCEDURE   | information_schema | %           | NO      | NO    |

| PROCEDURE   | %                  | %           | YES     | YES   |

| TABLE       | mysql              | %           | NO      | NO    |

| TABLE       | performance_schema | %           | NO      | NO    |

| TABLE       | information_schema | %           | NO      | NO    |

| TABLE       | %                  | %           | YES     | YES   |

| TRIGGER     | mysql              | %           | NO      | NO    |

| TRIGGER     | performance_schema | %           | NO      | NO    |

| TRIGGER     | information_schema | %           | NO      | NO    |

| TRIGGER     | %                  | %           | YES     | YES   |

+-------------+--------------------+-------------+---------+-------+

20 rows in set (0.00 sec)

 

mysql> select * from setup_timers;

+-------------+-------------+

| NAME        | TIMER_NAME  |

+-------------+-------------+

| idle        | MICROSECOND |

| wait        | CYCLE       |

| stage       | NANOSECOND  |

| statement   | NANOSECOND  |

| transaction | NANOSECOND  |

+-------------+-------------+

5 rows in set (0.00 sec)

mysql> select * from performance_timers;

+-------------+-----------------+------------------+----------------+

| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |

+-------------+-----------------+------------------+----------------+

| CYCLE       |      2270208955 |                1 |             24 |

| NANOSECOND  |      1000000000 |                1 |             76 |

| MICROSECOND |         1000000 |                1 |             80 |

| MILLISECOND |            1037 |                1 |             80 |

| TICK        |             102 |                1 |            384 |

+-------------+-----------------+------------------+----------------+

5 rows in set (0.00 sec)

 

如何使用performance_schema  应用示例 DML锁:

打开'wait/lock/metadata/sql/mdl' instruments

查看performance_schema.metadata_locks

mysql> update setup_instruments set enabled='yes' where name='wait/lock/metadata/sql/mdl';

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1  Changed: 0  Warnings: 0

 

mysql> select * from setup_instruments where name='wait/lock/metadata/sql/mdl';

+----------------------------+---------+-------+

| NAME                       | ENABLED | TIMED |

+----------------------------+---------+-------+

| wait/lock/metadata/sql/mdl | YES     | YES   |

+----------------------------+---------+-------+

1 row in set (0.00 sec)

 

seesion1:

mysql> begin;

Query OK, 0 rows affected (0.03 sec)

mysql> select * from users limit 1;

+------+---------------------+-------------------+

| USER | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |

+------+---------------------+-------------------+

| NULL |                  26 |                29 |

+------+---------------------+-------------------+

1 row in set (0.04 sec)

 

session2:

mysql> select * from users limit 2 for update;

+------+---------------------+-------------------+

| USER | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |

+------+---------------------+-------------------+

| NULL |                  26 |                29 |

| root |                   1 |                 4 |

+------+---------------------+-------------------+

2 rows in set (0.00 sec)

 

以上操作产生了死锁,可以通过performance_schema查看详细信息

mysql> select * from metadata_locks where object_schema='performance_schema';

+-------------+--------------------+----------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+

| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE    | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |

+-------------+--------------------+----------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+

| TABLE       | performance_schema | users          |       140412883448096 | SHARED_READ  | TRANSACTION   | GRANTED     | sql_parse.cc:6031 |              32 |           1809 |

| TABLE       | performance_schema | users          |       140412883448704 | SHARED_WRITE | TRANSACTION   | GRANTED     | sql_parse.cc:6031 |              32 |           1828 |

| TABLE       | performance_schema | metadata_locks |       140412883448272 | SHARED_READ  | TRANSACTION   | GRANTED     | sql_parse.cc:6031 |              32 |           1847 |

+-------------+--------------------+----------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+

3 rows in set (0.00 sec)

 

Handler锁:

mysql> update setup_instruments set enabled='yes' where name='wait/io/table/sql/handler';

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1  Changed: 0  Warnings: 0

 

mysql> select * from setup_instruments where name='wait/io/table/sql/handler';

+---------------------------+---------+-------+

| NAME                      | ENABLED | TIMED |

+---------------------------+---------+-------+

| wait/io/table/sql/handler | YES     | YES   |

+---------------------------+---------+-------+

1 row in set (0.00 sec)

 

session1:

use mytest;

 

mysql> lock table test_index1 read;

Query OK, 0 rows affected (0.00 sec)

 

session2:

use mytest;

 

mysql> lock table test_index_1 write;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from performance_schema.table_handles;

+-------------+---------------+--------------+-----------------------+-----------------+----------------+---------------+----------------+

| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME  | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK  |

+-------------+---------------+--------------+-----------------------+-----------------+----------------+---------------+----------------+

| TABLE       | mytest        | test_index1  |       140109488540688 |              29 |            110 | NULL          | READ EXTERNAL  |

| TABLE       | mytest        | test_index_1 |       140109488446768 |               0 |              0 | NULL          | NULL           |

| TABLE       | mytest        | test_index_2 |       140109483620464 |               0 |              0 | NULL          | NULL           |

| TABLE       | mytest        | test_index1  |       140109550778064 |               0 |              0 | NULL          | NULL           |

| TABLE       | mytest        | test_index_1 |       140109550787152 |              30 |             11 | NULL          | WRITE EXTERNAL |

| TABLE       | mytest        | test_index_2 |       140109550812768 |               0 |              0 | NULL          | NULL           |

+-------------+---------------+--------------+-----------------------+-----------------+----------------+---------------+----------------+

6 rows in set (0.00 sec)

 

 

 

目前我们在5.7打开了Performance Schema,使用默认的设置。现在打算新增以下这一项,这样可以监控内存使用情况。 

        performance-schema-instrument='memory/%=COUNTED' 

看了手册,似乎只有一种方法:在my.cnf添加上述项,重启MySQL生效。 

请问 

(1)有没有办法不需要重启MySQL就可以生效? 

可以动态修改生效: 

update performance_schema.setup_instruments set ENABLED='YES' where NAME like 'memory%';

(2)根据了解,添加该采集项对MySQL性能应该没有明显的影响。能否帮忙确认? 

https://mysqlserverteam.com/performance-schema-great-power-comes-without-great-cost/

但是上面的结果是基于performance_schema开启默认规则进行测试的,开启的采集项越多,性能影响肯定越大,建议新增开启采集项后做一下基准性能测试;

(3)除此之外,有没有其他推荐的performance schema采集项?性能影响如何?

我们建议日常只开启默认配置,没有推荐的配置,建议遇到问题后按需开启;

 

 

 

 

 参考文献:

http://www.yunweipai.com/archives/9248.html

https://yq.aliyun.com/articles/207095

http://www.bubuko.com/infodetail-2237830.html

http://blog.itpub.net/26506993/viewspace-2098384/

http://www.bubuko.com/infodetail-2237830.html

http://imysql.com/2017/03/27/howto-findout-oom-with-mysql-5-7-sys-schema.shtml

http://www.ywnds.com/?p=5045

http://www.bkjia.com/Mysql/1222405.html

http://www.innomysql.com/mysql-5-7-oom%E9%97%AE%E9%A2%98%E8%AF%8A%E6%96%AD-%E5%B0%B1%E6%98%AF%E8%BF%99%E4%B9%88%E7%AE%80%E5%8D%95/

网友评论

登录后评论
0/500
评论
关尚
+ 关注