mysql配置

MySQL 配置详解

1 生产者 消费者

Performance Schema允许在服务器启动时配置 instrument(检测仪器，生产者) 和 consumer configuration（消费者）。

要在服务器启动时控制仪器，请使用以下形式的选项：

--performance-schema-instrument='instrument_name=value'

此处instrument_name是一个仪器名称，例如wait/synch/mutex/sql/LOCK_open，并且value是以下值之一：

• OFF，FALSE或 0：禁用仪器
• ON，TRUE或 1：启用仪器并计时
• COUNTED：启用并计数（而不是时间）仪器

开启所有

每个 --performance-schema-instrument 选项只能指定一个仪器名称，但可以给出多个选项实例来配置多个仪器。此外，仪器名称中允许使用模式来配置与该模式匹配的仪器。要将所有条件同步仪器配置为已启用和已计数，请使用此选项：

--performance-schema-instrument='wait/synch/cond/%=COUNTED'

• 较长的仪器名称字符串优先于较短的模式名称，而与顺序无关。
• 无法识别的仪器名称将被忽略。稍后安装的插件可能会创建仪器，这时会识别并配置该名称。

关闭所有

要禁用所有仪器，请使用以下选项：

--performance-schema-instrument='%=OFF'

例外：memory/performance_schema/% 仪器是内置的，无法在启动时禁用。

要在服务器启动时控制consumer，请使用以下形式的选项：

--performance-schema-consumer-consumer_name=value

此处consumer_name是消费者名称，例如events_waits_history，并且 value是以下值之一：

• OFF，FALSE或 0：不要为消费者收集事件
• ON，TRUE或 1：为消费者收集事件

例如，要启用events_waits_history 使用者，请使用以下选项：

--performance-schema-consumer-events-waits-history=ON

2 编译时配置

Performance Schema 是强制性的，并且始终在其中进行编译。可以排除Performance Schema检测的某些部分。例如，要排除阶段和语句工具，请执行以下操作：

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

2.1 编译安装功能验证

要验证服务器是否使用Performance Schema支持构建，请检查其帮助输出。如果Performance Schema可用，那么输出将提及几个以下名称开头的变量performance_schema：

shell> mysqld --verbose --help
...
  --performance_schema
                      Enable the performance schema.
  --performance_schema_events_waits_history_long_size=#
                      Number of rows in events_waits_history_long.
...

还可以连接到服务器，使用SHOW ENGINES，在以下输出中查找用于PERFORMANCE_SCHEMA命名存储引擎的行。

mysql> SHOW ENGINESG
...
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
...

如果在构建时未将PERFORMANCE_SCHEMA配置到服务器中，则在SHOW ENGINES输出中将不会显示这行 。但是可能会在SHOW DATABASES的输出中看到列出的performance_schema内容，但其中没有表格，将无法使用它。

3 启用时配置

SHOW ENGINES表示PERFORMANCE_SCHEMA可用，而不是已启用。要启用它，必须在服务器启动时这样做。

要使用 MySQL Performance Schema，必须在服务器启动时启用它，以启用 event 集合。

MySQLPerformance Schema则默认情况下是启用的。要显式启用或禁用它，请在将performance_schema 变量设置为适当值的情况下启动服务器。改参数为只读变量，无法在线调整，修改需要重启数据库服务。例如，在my.cnf文件中使用以下行：

[mysqld]
performance_schema=ON

如果服务器在Performance Schema初始化期间无法分配任何内部缓冲区，则Performance Schema会禁用自身并将其设置 performance_schema为 OFF，并且服务器将在没有instrumentation的情况下运行（自我关闭状态）。

3.1 启动选项

performance_schema有哪些启动选项呢？我们可以通过如下命令行命令进行查看:

[root@localhost ~]# mysqld --verbose --help |grep performance-schema |grep -v '--' |sed '1d' |sed '/[0-9]+/d'
......
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 FALSE
performance-schema-consumer-events-transactions-history FALSE
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-instrument  
......

下面将对这些启动选项进行简单描述(这些启动选项是用于指定consumers和instruments配置项在MySQL启动时是否跟随打开的，之所以叫做启动选项，是因为这些需要在mysqld启动时就需要通过命令行指定或者需要在my.cnf中指定，启动之后通过show variables命令无法查看，因为他们不属于system variables。

3.1.1 当前语句事件

• performance_schema_consumer_events_statements_current=TRUE

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

3.1.2 历史语句事件

• performance_schema_consumer_events_statements_history=TRUE

与performance_schema_consumer_events_statements_current选项类似，但该选项是用于配置是否记录语句事件短历史信息，默认为TRUE

3.1.3 历史语句事件(long)

• performance_schema_consumer_events_stages_history_long=FALSE

与performance_schema_consumer_events_statements_current选项类似，但该选项是用于配置是否记录语句事件长历史信息，默认为FALSE

3.1.4 其他类型事件

• 除了statement(语句)事件之外，还支持：wait(等待)事件、state(阶段)事件、transaction(事务)事件，他们与statement事件一样都有三个启动项分别进行配置，但这些等待事件默认未启用，如果需要在MySQL Server启动时一同启动，则通常需要写进my.cnf配置文件中

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 FALSE
performance-schema-consumer-events-transactions-history FALSE
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

3.1.5 全局表

• 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

3.1.6 语句摘要表

• performance_schema_consumer_statements_digest=TRUE

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

4 配置状态

可以通过检查setup_consumers表找到启用的消费者名称 。不允许使用模式。setup_consumers表中的消费者名称使用下划线，但是对于在启动时设置的消费者，名称中的短划线和下划线是等效的。

Performance Schema 包含几个提供 configuration 信息的系统变量：

mysql> SHOW VARIABLES LIKE 'perf%';
+--------------------------------------------------------+---------+
| Variable_name                                          | Value   |
+--------------------------------------------------------+---------+
| performance_schema                                     | ON      |
| performance_schema_accounts_size                       | 100     |
| performance_schema_digests_size                        | 200     |
| 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_waits_history_long_size      | 10000   |
| performance_schema_events_waits_history_size           | 10      |
| performance_schema_hosts_size                          | 100     |
| performance_schema_max_cond_classes                    | 80      |
| performance_schema_max_cond_instances                  | 1000    |
...

该performance_schema变量是ON或OFF指示performance_schema是启用还是禁用。其他变量指示表大小（行数）或内存分配值。

启用performance_schema后，performance_schema实例的数量可能会在很大程度上影响服务器的内存占用。Performance Schema自动缩放许多参数以仅根据需要使用内存。请参见 第25.17节“性能模式内存分配模型”。

要更改Performance Schema系统变量的值，请在服务器启动时进行设置。例如，将以下行放入 my.cnf文件中，以更改等待事件的历史记录表的大小：

[mysqld]
performance_schema
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000

4.1 自动调整

如果未明确设置，performance_schema将在服务器启动时自动调整其几个参数的值。

例如，它通过这种方式调整控制events 等待表大小的参数。Performance Schema以递增方式分配 memory，将其内存使用量扩展到实际服务器负载，而不是在服务器启动期间分配其所需的所有内存。因此，根本不需要设置许多size参数。要查看哪些参数是自动调整大小或自动缩放的，请使用mysqld --verbose --help并检查选项说明，或参见 第25.15节“性能模式系统变量”。

对于服务器启动时未设置的每个自动调整大小的参数，performance_schema将根据以下系统值来确定如何设置其值，这些系统值被视为有关如何配置MySQL服务器的“ 提示 ”：

max_connections
open_files_limit
table_definition_cache
table_open_cache

要覆盖指定参数的自动调整大小或自动缩放，请在启动时将其设置为-1以外的值。在这种情况下，performance_schema将为其分配指定的值。

在运行时，SHOW VARIABLES显示自动调整参数大小设置的实际值。自动缩放的参数显示为-1。

如果禁用了 Performance Schema，则其自动调整和自动缩放的参数将保持设置为-1，[SHOW VARIABLES显示-1。