performance_schema配置

80 阅读 0 评论 53 点赞

我是靠谱客的博主寒冷康乃馨，最近开发中收集的这篇文章主要介绍performance_schema配置，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

配置

查看配置

bash-4.4# mysqld --verbose --help | grep performance-schema | grep -v '--'
performance-schema                                           TRUE
performance-schema-accounts-size                             -1
performance-schema-consumer-events-stages-current            FALSE
performance-schema-consumer-events-stages-history            FALSE
performance-schema-consumer-events-stages-history-long       FALSE
performance-schema-consumer-events-statements-current        TRUE
performance-schema-consumer-events-statements-history        TRUE
performance-schema-consumer-events-statements-history-long   FALSE
performance-schema-consumer-events-transactions-current      TRUE
performance-schema-consumer-events-transactions-history      TRUE
performance-schema-consumer-events-transactions-history-long FALSE
performance-schema-consumer-events-waits-current             FALSE
performance-schema-consumer-events-waits-history             FALSE
performance-schema-consumer-events-waits-history-long        FALSE
performance-schema-consumer-global-instrumentation           TRUE
performance-schema-consumer-statements-digest                TRUE
performance-schema-consumer-thread-instrumentation           TRUE
performance-schema-digests-size                              -1
performance-schema-error-size                                5018
performance-schema-events-stages-history-long-size           -1
performance-schema-events-stages-history-size                -1
performance-schema-events-statements-history-long-size       -1
performance-schema-events-statements-history-size            -1
performance-schema-events-transactions-history-long-size     -1
performance-schema-events-transactions-history-size          -1
performance-schema-events-waits-history-long-size            -1
performance-schema-events-waits-history-size                 -1
performance-schema-hosts-size                                -1
performance-schema-instrument                                
performance-schema-max-cond-classes                          150
performance-schema-max-cond-instances                        -1
performance-schema-max-digest-length                         1024
performance-schema-max-digest-sample-age                     60
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                        450
performance-schema-max-metadata-locks                        -1
performance-schema-max-mutex-classes                         350
performance-schema-max-mutex-instances                       -1
performance-schema-max-prepared-statements-instances         -1
performance-schema-max-program-instances                     -1
performance-schema-max-rwlock-classes                        60
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                         175
performance-schema-max-statement-classes                     219
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                        100
performance-schema-max-thread-instances                      -1
performance-schema-session-connect-attrs-size                -1
performance-schema-setup-actors-size                         -1
performance-schema-setup-objects-size                        -1
performance-schema-show-processlist                          FALSE
performance-schema-users-size                                -1

performance_schema中的配置是保存在内存中的，即保存在performance_schema配置表（中的配置项在MySQL实例停止时会全部丢失。所以，需要在MySQL的配置文件中使用启动选项来持久化配置项，让MySQL每次重启时都自动加载配置项，而不需要再重新配置。

关键配置介绍

（1）performance_schema_consumer_events_statements_current=TRUE

是否在MySQL Server启动时就开启events_statements_current表的记录功能（该表记录当前的语句事件信息），启动之后也可以在setup_consumers表中使用UPDATE语句动态更新setup_consumers配置表中的events_statements_current配置项，默认值为TRUE。

（2）performance_schema_consumer_events_statements_history=TRUE

用于配置是否记录语句事件短历史信息，默认值为TRUE。

（3）performance_schema_consumer_global_instrumentation=TRUE

是否在MySQL Server启动时就开启全局表（如：mutex_instances、rwlock_instances、cond_instances、file_instances、users、hostsaccounts、socket_summary_by_event_name、file_summary_by_instance等大部分全局对象计数统计和事件汇总统计信息表）的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句动态更新全局配置项。默认值为TRUE。

（4）performance_schema_consumer_statements_digest=TRUE

是否在MySQL Server启动时就开启events_statements_summary_by_digest表的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句动态更新digest配置项。默认值为TRUE。

（5）performance_schema_consumer_thread_instrumentation=TRUE

是否在MySQL Server启动时就开启events_xxx_summary_by_yyy_by_event_name表的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句动态更新线程配置项。默认值为TRUE。

（6）performance_schema_instrument[=name]

是否在MySQL Server启动时就启用某些采集器。由于instruments配置项多达数千个，所以该配置项支持key-value模式，还支持使用“%”进行通配等。

查看生效的配置

mysql> show variables like '%performance_schema%'
    -> ;
+----------------------------------------------------------+-------+
| Variable_name                                            | Value |
+----------------------------------------------------------+-------+
| performance_schema                                       | ON    |
| performance_schema_accounts_size                         | -1    |
| performance_schema_digests_size                          | 10000 |
| performance_schema_error_size                            | 5018  |
| performance_schema_events_stages_history_long_size       | 10000 |
| performance_s