目前我们在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表示用于监视某个资源使用消耗的仪器,consumers对instruments采集到的数据进行展示,存储的地方,两者采用生产者/消费者模型,instruments为生产者,consumers为消费者,两者都各自有一个setup_xx配置表。
数据库刚刚初始化启动时,并非所有instruments和consumers都启用了,所以默认不会收集所有的事件,可能你需要检测的事件并没有打开,需要进行设置,可以使用如下语句打开对应的instruments和consumers(下面以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中的instruments和consumers配置表一共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个配置项,其中一些是预设打开的,相当一部分是没有打开 的,如果需要使用,需要修改这些配置项的ENABLED和TIMED列为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语句删除
对ENABLED和HISTORY列表示是否启用该用户线程的instruments和历史事件日志记录,具体是否生效还需要看其他相关配置表(该表相当于是在instruments和consumers配置表的上一层开关配置)
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