MySQL

Performance_schema 如何启用

Posted by 道行尚浅 on August 2, 2021

前提条件

如想在performance_schmema中使用performance_schema,需要满足两个条件

performance_schmema 参数设位置ON


mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

编译时没有禁用performance_schema

在进行源码安装的时候,指定开启performance_schema监控


shell> cmake . \
        -DDISABLE_PSI_STAGE=1 \
        -DDISABLE_PSI_STATEMENT=1

选项列表如下

选项 作用 默认值
DISABLE_PSI_COND Exclude Performance Schema condition instrumentation OFF
DISABLE_PSI_FILE Exclude Performance Schema file instrumentation OFF
DISABLE_PSI_IDLE Exclude Performance Schema idle instrumentation OFF
DISABLE_PSI_MEMORY Exclude Performance Schema memory instrumentation OFF
DISABLE_PSI_METADATA Exclude Performance Schema metadata instrumentation OFF
DISABLE_PSI_MUTEX Exclude Performance Schema mutex instrumentation OFF
DISABLE_PSI_PS Exclude the performance schema prepared statements OFF
DISABLE_PSI_RWLOCK Exclude Performance Schema rwlock instrumentation OFF
DISABLE_PSI_SOCKET Exclude Performance Schema socket instrumentation OFF
DISABLE_PSI_SP Exclude Performance Schema stored program instrumentation OFF
DISABLE_PSI_STAGE Exclude Performance Schema stage instrumentation OFF
DISABLE_PSI_STATEMENT Exclude Performance Schema statement instrumentation OFF
DISABLE_PSI_STATEMENT_DIGEST Exclude Performance Schema statements_digest instrumentation OFF
DISABLE_PSI_TABLE Exclude Performance Schema table instrumentation OFF
DISABLE_PSI_THREAD Exclude the performance schema thread instrumentation OFF
DISABLE_PSI_TRANSACTION Exclude the performance schema transaction instrumentation OFF

启用方式

命令行启用


[root@etlbi ~]# mysqld --verbose --help | grep performance 
2021-08-12T07:55:06.786500Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
                      increase this to get more performance
                      can increase this to get more performance
                      negative impact on performance (off by default)
                      Helps in performance tuning in heavily concurrent
                      Helps in performance tuning in heavily concurrent
  --performance-schema 
                      Enable the performance schema.
                      (Defaults to on; use --skip-performance-schema to disable.)
  --performance-schema-accounts-size=# 
  --performance-schema-consumer-events-stages-current 
  --performance-schema-consumer-events-stages-history 
  --performance-schema-consumer-events-stages-history-long 
  --performance-schema-consumer-events-statements-current 
                      (Defaults to on; use --skip-performance-schema-consumer-events-statements-current to disable.)
  --performance-schema-consumer-events-statements-history 
                      (Defaults to on; use --skip-performance-schema-consumer-events-statements-history to disable.)
  --performance-schema-consumer-events-statements-history-long 
  --performance-schema-consumer-events-transactions-current 
  --performance-schema-consumer-events-transactions-history 
  --performance-schema-consumer-events-transactions-history-long 
  --performance-schema-consumer-events-waits-current 
  --performance-schema-consumer-events-waits-history 
  --performance-schema-consumer-events-waits-history-long 
  --performance-schema-consumer-global-instrumentation 
                      (Defaults to on; use --skip-performance-schema-consumer-global-instrumentation to disable.)
  --performance-schema-consumer-statements-digest 
                      (Defaults to on; use --skip-performance-schema-consumer-statements-digest to disable.)
  --performance-schema-consumer-thread-instrumentation 
                      (Defaults to on; use --skip-performance-schema-consumer-thread-instrumentation to disable.)
  --performance-schema-digests-size=# 
  --performance-schema-events-stages-history-long-size=# 
  --performance-schema-events-stages-history-size=# 
  --performance-schema-events-statements-history-long-size=# 
  --performance-schema-events-statements-history-size=# 
  --performance-schema-events-transactions-history-long-size=# 
  --performance-schema-events-transactions-history-size=# 
  --performance-schema-events-waits-history-long-size=# 
  --performance-schema-events-waits-history-size=# 
  --performance-schema-hosts-size=# 
  --performance-schema-instrument[=name] 
                      Default startup value for a performance schema
  --performance-schema-max-cond-classes=# 
  --performance-schema-max-cond-instances=# 
  --performance-schema-max-digest-length=# 
                      performance_schema tables.
  --performance-schema-max-file-classes=# 
  --performance-schema-max-file-handles=# 
  --performance-schema-max-file-instances=# 
  --performance-schema-max-index-stat=# 
  --performance-schema-max-memory-classes=# 
  --performance-schema-max-metadata-locks=# 
  --performance-schema-max-mutex-classes=# 
  --performance-schema-max-mutex-instances=# 
  --performance-schema-max-prepared-statements-instances=# 
  --performance-schema-max-program-instances=# 
  --performance-schema-max-rwlock-classes=# 
  --performance-schema-max-rwlock-instances=# 
  --performance-schema-max-socket-classes=# 
  --performance-schema-max-socket-instances=# 
  --performance-schema-max-sql-text-length=# 
  --performance-schema-max-stage-classes=# 
  --performance-schema-max-statement-classes=# 
  --performance-schema-max-statement-stack=# 
  --performance-schema-max-table-handles=# 
  --performance-schema-max-table-instances=# 
  --performance-schema-max-table-lock-stat=# 
  --performance-schema-max-thread-classes=# 
  --performance-schema-max-thread-instances=# 
  --performance-schema-session-connect-attrs-size=# 
  --performance-schema-setup-actors-size=# 
  --performance-schema-setup-objects-size=# 
  --performance-schema-users-size=#

参数文件中启用

在mysql 参数文件中添加配置选项


[mysqld]

performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000

运行时启用

通过修改setup 系列表内容启用


mysql> use performance_schema ;
Database changed
mysql> show tables like '%setup%' ;
+----------------------------------------+
| Tables_in_performance_schema (%setup%) |
+----------------------------------------+
| setup_actors                           |
| setup_consumers                        |
| setup_instruments                      |
| setup_objects                          |
| setup_timers                           |
+----------------------------------------+
5 rows in set (0.00 sec)

mysql> select * from setup_instruments limit 5 ;
+-------------------------------------------------------+---------+-------+
| 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   |
+-------------------------------------------------------+---------+-------+
5 rows in set (0.00 sec)