MySQL Performance Schema

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

目前我们在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/

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
7月前
|
SQL Oracle 关系型数据库
MySQL必知必会:MySQL中的Schema与DataBase
MySQL必知必会:MySQL中的Schema与DataBase
|
7月前
|
存储 缓存 关系型数据库
【Mysql】Schema与数据类型优化
【Mysql】Schema与数据类型优化
42 0
|
7月前
|
存储 监控 关系型数据库
深度剖析MySQL Performance Schema内存管理
深度剖析MySQL Performance Schema内存管理:源码分析与改进思路 MySQL Performance Schema(PFS)是MySQL提供的强大的性能监控诊断工具,它能够在运行时检查server内部执行情况。PFS通过监视server内部已注册的事件来收集信息,将收集到的性能数据存储在performance_schema存储引擎中。本文将深入剖析PFS内存分配及释放原理,解读其中存在的问题以及改进思路。
208 2
|
存储 缓存 关系型数据库
《高性能Mysql》读书笔记之Schema与数据类型优化
《高性能Mysql》读书笔记之Schema与数据类型优化
|
Oracle 关系型数据库 MySQL
在Oracle和MySQL上安装hr schema、example和Scott schema
19c examples 安装完成,在$ORACLE_HOME/demo/schema/human_resources 目录下执行hr_main.sql 文件创建 hr用户
173 0
|
SQL 关系型数据库 MySQL
|
存储 关系型数据库 MySQL
MySQL 数据库 Schema 设计的性能优化①:高效的模型设计
前言 很多人都认为性能是在通过编写代码(程序代码或者是数据库代码)的过程中优化出来的,其实这是一个非常大的误区。真正影响性能最大的部分是在设计中就已经产生了的,后期的优化很多时候所能够带来的改善都只是在解决前妻设计所遗留下来的一些问题而已,而且能够解决的问题通常也比较有限。 博主将就如何在 MySQL 数据库 Schema 设计的时候保证尽可能的高效,尽可能减少后期的烦恼会分3篇文章来进行详细介绍!
|
存储 关系型数据库 MySQL
MySQL优化——Schema与数据类型设计
MySQL优化——Schema与数据类型设计
137 0
MySQL优化——Schema与数据类型设计
|
关系型数据库 数据库 PostgreSQL
PostgreSQL 模式(SCHEMA)
PostgreSQL 模式(SCHEMA)
120 0
|
关系型数据库 MySQL 索引
mysql的schema和数据类型优化
mysql的schema和数据类型优化
125 0