暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MYSQL Performance 内存控制

MYSQL Performance 内存控制

海鲨数据库架构师 2022-12-13
350

前段时间研究PS内存,发现老是测量不准

MYSQL PERFORMACE 内存使用量

0 PS内存理论知识

PFS将采集到的性能数据存储在performance_schema存储引擎中，

performance_schema存储引擎是一个内存表引擎，

也就是所有收集的诊断信息都会保存在内存中。

诊断信息的收集和存储都会带来一定的额外开销，为了尽可能小的影响业务，

PFS的性能和内存管理也显得非常重要了。源代码分析基于MySQL-8.0.24版本。

PS库内存分配模式,有些内存在服务启动的时候分配,有些内存在服务运行的时候分配

从不释放内存在服务运行期间,释放内存会在关闭的时候

结果是放松了内存约束，从而可以使用较少的配置来使用性能模式，并减少内存占用，以便消耗随服务器负载而扩展。

使用的内存取决于实际看到的负载，而不是估计或明确配置的负载


PFS内存管理有几个关键特点：

内存分配以Page为单位，一个Page内可以存储多条record

系统启动时预先分配部分pages，运行期间根据需要动态增长，

但page是只增不回收的模式 record的申请和释放都是无锁的


 

 

 1 PS 总内存

 看到PS内存使用了608MB


SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, FORMAT_BYTES(SUM(current_alloc)) AS current_alloc

        FROM sys.x$memory_global_by_current_bytes

        GROUP BY SUBSTRING_INDEX(event_name,'/',2)

        ORDER BY SUM(current_alloc) DESC;



mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS

    ->        code_area, FORMAT_BYTES(SUM(current_alloc))

    ->        AS current_alloc

    ->        FROM sys.x$memory_global_by_current_bytes

    ->        GROUP BY SUBSTRING_INDEX(event_name,'/',2)

    ->        ORDER BY SUM(current_alloc) DESC;

+---------------------------+---------------+

| code_area                 | current_alloc |

+---------------------------+---------------+

| memory/innodb             | 2.20 GiB      |

| memory/performance_schema | 608.51 MiB    |

| memory/sql                | 58.83 MiB     |

| memory/mysys              | 9.93 MiB      |

| memory/temptable          | 3.00 MiB      |

| memory/mysqld_openssl     | 284.64 KiB    |

| memory/csv                | 61.79 KiB     |

| memory/mysqlx             | 2.67 KiB      |

| memory/myisam             |  696 bytes    |

| memory/blackhole          |   88 bytes    |

| memory/vio                |   16 bytes    |

+---------------------------+---------------+

11 rows in set (0.07 sec)


2 PS 内存细节

SELECT event_name,sys.format_bytes(current_number_of_bytes_used) as CUR_USE_MEM,
 sys.format_bytes(sum_number_of_bytes_alloc) as SUM_USE_MEM,
 sys.format_bytes(high_number_of_bytes_used) as HIGH_USE_MEM
 FROM performance_schema.memory_summary_global_by_event_name
 WHERE EVENT_NAME LIKE 'memory/performance_schema/%'
 order by current_number_of_bytes_used desc;



mysql> SELECT event_name,sys.format_bytes(current_number_of_bytes_used) as CUR_USE_MEM,
    ->  sys.format_bytes(sum_number_of_bytes_alloc) as SUM_USE_MEM,
    ->  sys.format_bytes(high_number_of_bytes_used) as HIGH_USE_MEM
    ->  FROM performance_schema.memory_summary_global_by_event_name
    ->  WHERE EVENT_NAME LIKE 'memory/performance_schema/%'
    ->  order by event_name desc;
+--------------------------------------------------------------------------------+-------------+-------------+--------------+
| event_name                                                                     | CUR_USE_MEM | SUM_USE_MEM | HIGH_USE_MEM |
+--------------------------------------------------------------------------------+-------------+-------------+--------------+
| memory/performance_schema/users                                                | 80.00 KiB   | 80.00 KiB   | 80.00 KiB    |
| memory/performance_schema/threads                                              | 1.05 MiB    | 1.05 MiB    | 1.05 MiB     |
| memory/performance_schema/thread_class                                         | 25.00 KiB   | 25.00 KiB   | 25.00 KiB    |
| memory/performance_schema/table_shares                                         | 4.00 MiB    | 4.00 MiB    | 4.00 MiB     |
| memory/performance_schema/table_lock_waits_summary_by_table                    |    0 bytes  |    0 bytes  |    0 bytes   |
| memory/performance_schema/table_io_waits_summary_by_index_usage                | 2.75 MiB    | 2.75 MiB    | 2.75 MiB     |
| memory/performance_schema/table_handles                                        | 9.06 MiB    | 9.06 MiB    | 9.06 MiB     |
| memory/performance_schema/statement_class                                      | 42.56 KiB   | 42.56 KiB   | 42.56 KiB    |
| memory/performance_schema/stage_class                                          | 43.75 KiB   | 43.75 KiB   | 43.75 KiB    |
| memory/performance_schema/socket_instances                                     | 78.00 KiB   | 78.00 KiB   | 78.00 KiB    |
| memory/performance_schema/socket_class                                         | 3.12 KiB    | 3.12 KiB    | 3.12 KiB     |
| memory/performance_schema/setup_objects                                        | 56.00 KiB   | 56.00 KiB   | 56.00 KiB    |
| memory/performance_schema/setup_actors                                         | 64.00 KiB   | 64.00 KiB   | 64.00 KiB    |
| memory/performance_schema/session_connect_attrs                                | 128.00 KiB  | 128.00 KiB  | 128.00 KiB   |
| memory/performance_schema/scalable_buffer                                      | 3.92 KiB    | 3.92 KiB    | 3.92 KiB     |
| memory/performance_schema/rwlock_instances                                     | 1.38 MiB    | 1.38 MiB    | 1.38 MiB     |
| memory/performance_schema/rwlock_class                                         | 17.50 KiB   | 17.50 KiB   | 17.50 KiB    |
| memory/performance_schema/prepared_statements_instances                        | 1.62 MiB    | 1.62 MiB    | 1.62 MiB     |
| memory/performance_schema/mutex_instances                                      | 560.00 KiB  | 560.00 KiB  | 560.00 KiB   |
| memory/performance_schema/mutex_class                                          | 87.50 KiB   | 87.50 KiB   | 87.50 KiB    |
| memory/performance_schema/metadata_locks                                       | 1.00 MiB    | 1.00 MiB    | 1.00 MiB     |
| memory/performance_schema/memory_summary_global_by_event_name                  | 37.27 KiB   | 37.27 KiB   | 37.27 KiB    |
| memory/performance_schema/memory_summary_by_user_by_event_name                 | 4.66 MiB    | 4.66 MiB    | 4.66 MiB     |
| memory/performance_schema/memory_summary_by_thread_by_event_name               | 9.32 MiB    | 9.32 MiB    | 9.32 MiB     |
| memory/performance_schema/memory_summary_by_host_by_event_name                 | 4.66 MiB    | 4.66 MiB    | 4.66 MiB     |
| memory/performance_schema/memory_summary_by_account_by_event_name              | 4.66 MiB    | 4.66 MiB    | 4.66 MiB     |
| memory/performance_schema/memory_class                                         | 99.38 KiB   | 99.38 KiB   | 99.38 KiB    |
| memory/performance_schema/hosts                                                | 96.00 KiB   | 96.00 KiB   | 96.00 KiB    |
| memory/performance_schema/file_instances                                       | 2.75 MiB    | 2.75 MiB    | 2.75 MiB     |
| memory/performance_schema/file_handle                                          | 256.00 KiB  | 256.00 KiB  | 256.00 KiB   |
| memory/performance_schema/file_class                                           | 25.00 KiB   | 25.00 KiB   | 25.00 KiB    |
| memory/performance_schema/events_waits_summary_by_user_by_event_name           | 2.59 MiB    | 2.59 MiB    | 2.59 MiB     |
| memory/performance_schema/events_waits_summary_by_thread_by_event_name         | 5.19 MiB    | 5.19 MiB    | 5.19 MiB     |
| memory/performance_schema/events_waits_summary_by_host_by_event_name           | 2.59 MiB    | 2.59 MiB    | 2.59 MiB     |
| memory/performance_schema/events_waits_summary_by_account_by_event_name        | 2.59 MiB    | 2.59 MiB    | 2.59 MiB     |
| memory/performance_schema/events_waits_history_long                            | 171.88 KiB  | 171.88 KiB  | 171.88 KiB   |
| memory/performance_schema/events_waits_history                                 | 440.00 KiB  | 440.00 KiB  | 440.00 KiB   |
| memory/performance_schema/events_transactions_summary_by_user_by_event_name    | 11.00 KiB   | 11.00 KiB   | 11.00 KiB    |
| memory/performance_schema/events_transactions_summary_by_thread_by_event_name  | 22.00 KiB   | 22.00 KiB   | 22.00 KiB    |
| memory/performance_schema/events_transactions_summary_by_host_by_event_name    | 11.00 KiB   | 11.00 KiB   | 11.00 KiB    |
| memory/performance_schema/events_transactions_summary_by_account_by_event_name | 11.00 KiB   | 11.00 KiB   | 11.00 KiB    |
| memory/performance_schema/events_transactions_history_long                     | 335.94 KiB  | 335.94 KiB  | 335.94 KiB   |
| memory/performance_schema/events_transactions_history                          | 860.00 KiB  | 860.00 KiB  | 860.00 KiB   |
| memory/performance_schema/events_statements_summary_global_by_event_name       | 40.79 KiB   | 40.79 KiB   | 40.79 KiB    |
| memory/performance_schema/events_statements_summary_by_user_by_event_name      | 5.10 MiB    | 5.10 MiB    | 5.10 MiB     |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name    | 10.20 MiB   | 10.20 MiB   | 10.20 MiB    |
| memory/performance_schema/events_statements_summary_by_program                 | 448.00 KiB  | 448.00 KiB  | 448.00 KiB   |
| memory/performance_schema/events_statements_summary_by_host_by_event_name      | 5.10 MiB    | 5.10 MiB    | 5.10 MiB     |
| memory/performance_schema/events_statements_summary_by_digest.digest_text      | 97.66 MiB   | 97.66 MiB   | 97.66 MiB    |
| memory/performance_schema/events_statements_summary_by_digest                  | 396.73 MiB  | 396.73 MiB  | 396.73 MiB   |
| memory/performance_schema/events_statements_summary_by_account_by_event_name   | 5.10 MiB    | 5.10 MiB    | 5.10 MiB     |
| memory/performance_schema/events_statements_history.sql_text                   | 2.50 MiB    | 2.50 MiB    | 2.50 MiB     |
| memory/performance_schema/events_statements_history.digest_text                | 2.50 MiB    | 2.50 MiB    | 2.50 MiB     |
| memory/performance_schema/events_statements_history_long.sql_text              | 1000.00 KiB | 1000.00 KiB | 1000.00 KiB  |
| memory/performance_schema/events_statements_history_long.digest_text           | 1000.00 KiB | 1000.00 KiB | 1000.00 KiB  |
| memory/performance_schema/events_statements_history_long                       | 1.39 MiB    | 1.39 MiB    | 1.39 MiB     |
| memory/performance_schema/events_statements_history                            | 3.55 MiB    | 3.55 MiB    | 3.55 MiB     |
| memory/performance_schema/events_statements_current.sql_text                   | 2.50 MiB    | 2.50 MiB    | 2.50 MiB     |
| memory/performance_schema/events_statements_current.digest_text                | 2.50 MiB    | 2.50 MiB    | 2.50 MiB     |
| memory/performance_schema/events_statements_current                            | 3.55 MiB    | 3.55 MiB    | 3.55 MiB     |
| memory/performance_schema/events_stages_summary_global_by_event_name           | 5.47 KiB    | 5.47 KiB    | 5.47 KiB     |
| memory/performance_schema/events_stages_summary_by_user_by_event_name          | 700.00 KiB  | 700.00 KiB  | 700.00 KiB   |
| memory/performance_schema/events_stages_summary_by_thread_by_event_name        | 1.37 MiB    | 1.37 MiB    | 1.37 MiB     |
| memory/performance_schema/events_stages_summary_by_host_by_event_name          | 700.00 KiB  | 700.00 KiB  | 700.00 KiB   |
| memory/performance_schema/events_stages_summary_by_account_by_event_name       | 700.00 KiB  | 700.00 KiB  | 700.00 KiB   |
| memory/performance_schema/events_stages_history_long                           | 101.56 KiB  | 101.56 KiB  | 101.56 KiB   |
| memory/performance_schema/events_stages_history                                | 260.00 KiB  | 260.00 KiB  | 260.00 KiB   |
| memory/performance_schema/events_errors_summary_global_by_error                |  640 bytes  |  640 bytes  |  640 bytes   |
| memory/performance_schema/events_errors_summary_by_user_by_error               | 82.00 KiB   | 82.00 KiB   | 82.00 KiB    |
| memory/performance_schema/events_errors_summary_by_thread_by_error             | 164.00 KiB  | 164.00 KiB  | 164.00 KiB   |
| memory/performance_schema/events_errors_summary_by_host_by_error               | 82.00 KiB   | 82.00 KiB   | 82.00 KiB    |
| memory/performance_schema/events_errors_summary_by_account_by_error            | 82.00 KiB   | 82.00 KiB   | 82.00 KiB    |
| memory/performance_schema/cond_instances                                       | 18.00 KiB   | 18.00 KiB   | 18.00 KiB    |
| memory/performance_schema/cond_class                                           | 37.50 KiB   | 37.50 KiB   | 37.50 KiB    |
| memory/performance_schema/accounts                                             | 120.00 KiB  | 120.00 KiB  | 120.00 KiB   |
+--------------------------------------------------------------------------------+-------------+-------------+--------------+
75 rows in set (0.00 sec)

这个是按事件名排序的内存使用细节,最大的是DIEST 397MB

3 PS 内存变量

PS相关的内存变量设置, DIGESTS表设置10万行,最大SQL_TEXT为1024

mysql> show VARIABLES like 'performance%';
+----------------------------------------------------------+--------+
| Variable_name                                            | Value  |
+----------------------------------------------------------+--------+
| performance_schema                                       | ON     |
| performance_schema_accounts_size                         | -1     |
| performance_schema_digests_size                          | 100000 |
| performance_schema_error_size                            | 20     |
| performance_schema_events_stages_history_long_size       | 1000   |
| performance_schema_events_stages_history_size            | 10     |
| performance_schema_events_statements_history_long_size   | 1000   |
| performance_schema_events_statements_history_size        | 10     |
| performance_schema_events_transactions_history_long_size | 1000   |
| performance_schema_events_transactions_history_size      | 10     |
| performance_schema_events_waits_history_long_size        | 1000   |
| performance_schema_events_waits_history_size             | 10     |
| performance_schema_hosts_size                            | -1     |
| 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                    | 530    |
| 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                    | 70     |
| 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                 | 227    |
| 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            | 512    |
| performance_schema_setup_actors_size                     | -1     |
| performance_schema_setup_objects_size                    | -1     |
| performance_schema_show_processlist                      | ON     |
| performance_schema_users_size                            | -1     |
+----------------------------------------------------------+--------+
45 rows in set (0.01 sec)

4 PS状态:

#这里SIZE 是行大小不可改变;COUNT表的行数,MEMORY实际占用内存;

mysql> show engine performance_schema status ;
+--------------------+-------------------------------------------------------------+-----------+
| Type               | Name                                                        | Status    |
+--------------------+-------------------------------------------------------------+-----------+
| performance_schema | events_waits_current.size                                   | 176       |
| performance_schema | events_waits_current.count                                  | 1536      |
| performance_schema | events_waits_history.size                                   | 176       |
| performance_schema | events_waits_history.count                                  | 2560      |
| performance_schema | events_waits_history.memory                                 | 450560    |
| performance_schema | events_waits_history_long.size                              | 176       |
| performance_schema | events_waits_history_long.count                             | 1000      |
| performance_schema | events_waits_history_long.memory                            | 176000    |
| performance_schema | (pfs_mutex_class).size                                      | 256       |
| performance_schema | (pfs_mutex_class).count                                     | 350       |
| performance_schema | (pfs_mutex_class).memory                                    | 89600     |
| performance_schema | (pfs_rwlock_class).size                                     | 256       |
| performance_schema | (pfs_rwlock_class).count                                    | 70        |
| performance_schema | (pfs_rwlock_class).memory                                   | 17920     |
| performance_schema | (pfs_cond_class).size                                       | 256       |
| performance_schema | (pfs_cond_class).count                                      | 150       |
| performance_schema | (pfs_cond_class).memory                                     | 38400     |
| performance_schema | (pfs_thread_class).size                                     | 256       |
| performance_schema | (pfs_thread_class).count                                    | 100       |
| performance_schema | (pfs_thread_class).memory                                   | 25600     |
| performance_schema | (pfs_file_class).size                                       | 320       |
| performance_schema | (pfs_file_class).count                                      | 80        |
| performance_schema | (pfs_file_class).memory                                     | 25600     |
| performance_schema | mutex_instances.size                                        | 80        |
| performance_schema | mutex_instances.count                                       | 7168      |
| performance_schema | mutex_instances.memory                                      | 573440    |
| performance_schema | rwlock_instances.size                                       | 88        |
| performance_schema | rwlock_instances.count                                      | 16384     |
| performance_schema | rwlock_instances.memory                                     | 1441792   |
| performance_schema | cond_instances.size                                         | 72        |
| performance_schema | cond_instances.count                                        | 256       |
| performance_schema | cond_instances.memory                                       | 18432     |
| performance_schema | threads.size                                                | 4288      |
| performance_schema | threads.count                                               | 256       |
| performance_schema | threads.memory                                              | 1097728   |
| performance_schema | file_instances.size                                         | 704       |
| performance_schema | file_instances.count                                        | 4096      |
| performance_schema | file_instances.memory                                       | 2883584   |
| performance_schema | (pfs_file_handle).size                                      | 8         |
| performance_schema | (pfs_file_handle).count                                     | 32768     |
| performance_schema | (pfs_file_handle).memory                                    | 262144    |
| performance_schema | events_waits_summary_by_thread_by_event_name.size           | 32        |
| performance_schema | events_waits_summary_by_thread_by_event_name.count          | 169984    |
| performance_schema | events_waits_summary_by_thread_by_event_name.memory         | 5439488   |
| performance_schema | (pfs_table_share).size                                      | 1024      |
| performance_schema | (pfs_table_share).count                                     | 4096      |
| performance_schema | (pfs_table_share).memory                                    | 4194304   |
| performance_schema | (pfs_table).size                                            | 9280      |
| performance_schema | (pfs_table).count                                           | 1024      |
| performance_schema | (pfs_table).memory                                          | 9502720   |
| performance_schema | setup_actors.size                                           | 512       |
| performance_schema | setup_actors.count                                          | 128       |
| performance_schema | setup_actors.memory                                         | 65536     |
| performance_schema | setup_objects.size                                          | 448       |
| performance_schema | setup_objects.count                                         | 128       |
| performance_schema | setup_objects.memory                                        | 57344     |
| performance_schema | (pfs_account).size                                          | 960       |
| performance_schema | (pfs_account).count                                         | 128       |
| performance_schema | (pfs_account).memory                                        | 122880    |
| performance_schema | events_waits_summary_by_account_by_event_name.size          | 32        |
| performance_schema | events_waits_summary_by_account_by_event_name.count         | 84992     |
| performance_schema | events_waits_summary_by_account_by_event_name.memory        | 2719744   |
| performance_schema | events_waits_summary_by_user_by_event_name.size             | 32        |
| performance_schema | events_waits_summary_by_user_by_event_name.count            | 84992     |
| performance_schema | events_waits_summary_by_user_by_event_name.memory           | 2719744   |
| performance_schema | events_waits_summary_by_host_by_event_name.size             | 32        |
| performance_schema | events_waits_summary_by_host_by_event_name.count            | 84992     |
| performance_schema | events_waits_summary_by_host_by_event_name.memory           | 2719744   |
| performance_schema | (pfs_user).size                                             | 640       |
| performance_schema | (pfs_user).count                                            | 128       |
| performance_schema | (pfs_user).memory                                           | 81920     |
| performance_schema | (pfs_host).size                                             | 768       |
| performance_schema | (pfs_host).count                                            | 128       |
| performance_schema | (pfs_host).memory                                           | 98304     |
| performance_schema | (pfs_stage_class).size                                      | 256       |
| performance_schema | (pfs_stage_class).count                                     | 175       |
| performance_schema | (pfs_stage_class).memory                                    | 44800     |
| performance_schema | events_stages_history.size                                  | 104       |
| performance_schema | events_stages_history.count                                 | 2560      |
| performance_schema | events_stages_history.memory                                | 266240    |
| performance_schema | events_stages_history_long.size                             | 104       |
| performance_schema | events_stages_history_long.count                            | 1000      |
| performance_schema | events_stages_history_long.memory                           | 104000    |
| performance_schema | events_stages_summary_by_thread_by_event_name.size          | 32        |
| performance_schema | events_stages_summary_by_thread_by_event_name.count         | 44800     |
| performance_schema | events_stages_summary_by_thread_by_event_name.memory        | 1433600   |
| performance_schema | events_stages_summary_global_by_event_name.size             | 32        |
| performance_schema | events_stages_summary_global_by_event_name.count            | 175       |
| performance_schema | events_stages_summary_global_by_event_name.memory           | 5600      |
| performance_schema | events_stages_summary_by_account_by_event_name.size         | 32        |
| performance_schema | events_stages_summary_by_account_by_event_name.count        | 22400     |
| performance_schema | events_stages_summary_by_account_by_event_name.memory       | 716800    |
| performance_schema | events_stages_summary_by_user_by_event_name.size            | 32        |
| performance_schema | events_stages_summary_by_user_by_event_name.count           | 22400     |
| performance_schema | events_stages_summary_by_user_by_event_name.memory          | 716800    |
| performance_schema | events_stages_summary_by_host_by_event_name.size            | 32        |
| performance_schema | events_stages_summary_by_host_by_event_name.count           | 22400     |
| performance_schema | events_stages_summary_by_host_by_event_name.memory          | 716800    |
| performance_schema | (pfs_statement_class).size                                  | 192       |
| performance_schema | (pfs_statement_class).count                                 | 227       |
| performance_schema | (pfs_statement_class).memory                                | 43584     |
| performance_schema | events_statements_history.size                              | 1456      |
| performance_schema | events_statements_history.count                             | 2560      |
| performance_schema | events_statements_history.memory                            | 3727360   |
| performance_schema | events_statements_history_long.size                         | 1456      |
| performance_schema | events_statements_history_long.count                        | 1000      |
| performance_schema | events_statements_history_long.memory                       | 1456000   |
| performance_schema | events_statements_summary_by_thread_by_event_name.size      | 184       |
| performance_schema | events_statements_summary_by_thread_by_event_name.count     | 58112     |
| performance_schema | events_statements_summary_by_thread_by_event_name.memory    | 10692608  |
| performance_schema | events_statements_summary_global_by_event_name.size         | 184       |
| performance_schema | events_statements_summary_global_by_event_name.count        | 227       |
| performance_schema | events_statements_summary_global_by_event_name.memory       | 41768     |
| performance_schema | events_statements_summary_by_account_by_event_name.size     | 184       |
| performance_schema | events_statements_summary_by_account_by_event_name.count    | 29056     |
| performance_schema | events_statements_summary_by_account_by_event_name.memory   | 5346304   |
| performance_schema | events_statements_summary_by_user_by_event_name.size        | 184       |
| performance_schema | events_statements_summary_by_user_by_event_name.count       | 29056     |
| performance_schema | events_statements_summary_by_user_by_event_name.memory      | 5346304   |
| performance_schema | events_statements_summary_by_host_by_event_name.size        | 184       |
| performance_schema | events_statements_summary_by_host_by_event_name.count       | 29056     |
| performance_schema | events_statements_summary_by_host_by_event_name.memory      | 5346304   |
| performance_schema | events_statements_current.size                              | 1456      |
| performance_schema | events_statements_current.count                             | 2560      |
| performance_schema | events_statements_current.memory                            | 3727360   |
| performance_schema | (pfs_socket_class).size                                     | 320       |
| performance_schema | (pfs_socket_class).count                                    | 10        |
| performance_schema | (pfs_socket_class).memory                                   | 3200      |
| performance_schema | socket_instances.size                                       | 312       |
| performance_schema | socket_instances.count                                      | 256       |
| performance_schema | socket_instances.memory                                     | 79872     |
| performance_schema | events_statements_summary_by_digest.size                    | 4160      |
| performance_schema | events_statements_summary_by_digest.count                   | 100000    |
| performance_schema | events_statements_summary_by_digest.memory                  | 416000000 |
| performance_schema | events_statements_summary_by_program.size                   | 448       |
| performance_schema | events_statements_summary_by_program.count                  | 1024      |
| performance_schema | events_statements_summary_by_program.memory                 | 458752    |
| performance_schema | session_connect_attrs.size                                  | 256       |
| performance_schema | session_connect_attrs.count                                 | 512       |
| performance_schema | session_connect_attrs.memory                                | 131072    |
| performance_schema | prepared_statements_instances.size                          | 1664      |
| performance_schema | prepared_statements_instances.count                         | 1024      |
| performance_schema | prepared_statements_instances.memory                        | 1703936   |
| performance_schema | (account_hash).count                                        | 5         |
| performance_schema | (account_hash).size                                         | 8         |
| performance_schema | (digest_hash).count                                         | 970       |
| performance_schema | (digest_hash).size                                          | 1024      |
| performance_schema | (filename_hash).count                                       | 2225      |
| performance_schema | (filename_hash).size                                        | 4096      |
| performance_schema | (host_hash).count                                           | 4         |
| performance_schema | (host_hash).size                                            | 4         |
| performance_schema | (setup_actor_hash).count                                    | 1         |
| performance_schema | (setup_actor_hash).size                                     | 1         |
| performance_schema | (setup_object_hash).count                                   | 20        |
| performance_schema | (setup_object_hash).size                                    | 32        |
| performance_schema | (table_share_hash).count                                    | 290       |
| performance_schema | (table_share_hash).size                                     | 512       |
| performance_schema | (user_hash).count                                           | 3         |
| performance_schema | (user_hash).size                                            | 4         |
| performance_schema | (program_hash).count                                        | 1         |
| performance_schema | (program_hash).size                                         | 1         |
| performance_schema | host_cache.size                                             | 512       |
| performance_schema | (pfs_memory_class).row_size                                 | 192       |
| performance_schema | (pfs_memory_class).row_count                                | 530       |
| performance_schema | (pfs_memory_class).memory                                   | 101760    |
| performance_schema | memory_summary_by_thread_by_event_name.row_size             | 72        |
| performance_schema | memory_summary_by_thread_by_event_name.row_count            | 135680    |
| performance_schema | memory_summary_by_thread_by_event_name.memory               | 9768960   |
| performance_schema | memory_summary_global_by_event_name.row_size                | 72        |
| performance_schema | memory_summary_global_by_event_name.row_count               | 530       |
| performance_schema | memory_summary_global_by_event_name.memory                  | 38160     |
| performance_schema | memory_summary_by_account_by_event_name.row_size            | 72        |
| performance_schema | memory_summary_by_account_by_event_name.row_count           | 67840     |
| performance_schema | memory_summary_by_account_by_event_name.memory              | 4884480   |
| performance_schema | memory_summary_by_user_by_event_name.row_size               | 72        |
| performance_schema | memory_summary_by_user_by_event_name.row_count              | 67840     |
| performance_schema | memory_summary_by_user_by_event_name.memory                 | 4884480   |
| performance_schema | memory_summary_by_host_by_event_name.row_size               | 72        |
| performance_schema | memory_summary_by_host_by_event_name.row_count              | 67840     |
| performance_schema | memory_summary_by_host_by_event_name.memory                 | 4884480   |
| performance_schema | metadata_locks.row_size                                     | 512       |
| performance_schema | metadata_locks.row_count                                    | 2048      |
| performance_schema | metadata_locks.memory                                       | 1048576   |
| performance_schema | events_transactions_history.size                            | 344       |
| performance_schema | events_transactions_history.count                           | 2560      |
| performance_schema | events_transactions_history.memory                          | 880640    |
| performance_schema | events_transactions_history_long.size                       | 344       |
| performance_schema | events_transactions_history_long.count                      | 1000      |
| performance_schema | events_transactions_history_long.memory                     | 344000    |
| performance_schema | events_transactions_summary_by_thread_by_event_name.size    | 88        |
| performance_schema | events_transactions_summary_by_thread_by_event_name.count   | 256       |
| performance_schema | events_transactions_summary_by_thread_by_event_name.memory  | 22528     |
| performance_schema | events_transactions_summary_by_account_by_event_name.size   | 88        |
| performance_schema | events_transactions_summary_by_account_by_event_name.count  | 128       |
| performance_schema | events_transactions_summary_by_account_by_event_name.memory | 11264     |
| performance_schema | events_transactions_summary_by_user_by_event_name.size      | 88        |
| performance_schema | events_transactions_summary_by_user_by_event_name.count     | 128       |
| performance_schema | events_transactions_summary_by_user_by_event_name.memory    | 11264     |
| performance_schema | events_transactions_summary_by_host_by_event_name.size      | 88        |
| performance_schema | events_transactions_summary_by_host_by_event_name.count     | 128       |
| performance_schema | events_transactions_summary_by_host_by_event_name.memory    | 11264     |
| performance_schema | table_lock_waits_summary_by_table.size                      | 344       |
| performance_schema | table_lock_waits_summary_by_table.count                     | 0         |
| performance_schema | table_lock_waits_summary_by_table.memory                    | 0         |
| performance_schema | table_io_waits_summary_by_index_usage.size                  | 352       |
| performance_schema | table_io_waits_summary_by_index_usage.count                 | 8192      |
| performance_schema | table_io_waits_summary_by_index_usage.memory                | 2883584   |
| performance_schema | (history_long_statements_digest_token_array).count          | 1000      |
| performance_schema | (history_long_statements_digest_token_array).size           | 1024      |
| performance_schema | (history_long_statements_digest_token_array).memory         | 1024000   |
| performance_schema | (history_statements_digest_token_array).count               | 2560      |
| performance_schema | (history_statements_digest_token_array).size                | 1024      |
| performance_schema | (history_statements_digest_token_array).memory              | 2621440   |
| performance_schema | (current_statements_digest_token_array).count               | 2560      |
| performance_schema | (current_statements_digest_token_array).size                | 1024      |
| performance_schema | (current_statements_digest_token_array).memory              | 2621440   |
| performance_schema | (history_long_statements_text_array).count                  | 1000      |
| performance_schema | (history_long_statements_text_array).size                   | 1024      |
| performance_schema | (history_long_statements_text_array).memory                 | 1024000   |
| performance_schema | (history_statements_text_array).count                       | 2560      |
| performance_schema | (history_statements_text_array).size                        | 1024      |
| performance_schema | (history_statements_text_array).memory                      | 2621440   |
| performance_schema | (current_statements_text_array).count                       | 2560      |
| performance_schema | (current_statements_text_array).size                        | 1024      |
| performance_schema | (current_statements_text_array).memory                      | 2621440   |
| performance_schema | (statements_digest_token_array).count                       | 100000    |
| performance_schema | (statements_digest_token_array).size                        | 1024      |
| performance_schema | (statements_digest_token_array).memory                      | 102400000 |
| performance_schema | events_errors_summary_by_thread_by_error.size               | 656       |
| performance_schema | events_errors_summary_by_thread_by_error.count              | 256       |
| performance_schema | events_errors_summary_by_thread_by_error.memory             | 167936    |
| performance_schema | events_errors_summary_by_account_by_error.size              | 656       |
| performance_schema | events_errors_summary_by_account_by_error.count             | 128       |
| performance_schema | events_errors_summary_by_account_by_error.memory            | 83968     |
| performance_schema | events_errors_summary_by_user_by_error.size                 | 656       |
| performance_schema | events_errors_summary_by_user_by_error.count                | 128       |
| performance_schema | events_errors_summary_by_user_by_error.memory               | 83968     |
| performance_schema | events_errors_summary_by_host_by_error.size                 | 656       |
| performance_schema | events_errors_summary_by_host_by_error.count                | 128       |
| performance_schema | events_errors_summary_by_host_by_error.memory               | 83968     |
| performance_schema | events_errors_summary_global_by_error.size                  | 640       |
| performance_schema | events_errors_summary_global_by_error.count                 | 1         |
| performance_schema | events_errors_summary_global_by_error.memory                | 640       |
| performance_schema | (pfs_buffer_scalable_container).count                       | 39        |
| performance_schema | (pfs_buffer_scalable_container).memory                      | 4016      |
| performance_schema | (max_global_server_errors).count                            | 20        |
| performance_schema | (max_session_server_errors).count                           | 20        |
| performance_schema | performance_schema.memory                                   | 638065288 |
+--------------------+-------------------------------------------------------------+-----------+
248 rows in set (0.00 sec)


5 #计算PS内存 下面是参数文件关于PS的设置

[MYSQLD]
performance_schema=ON
performance_schema_events_statements_history_size=100
performance_schema_events_statements_history_long_size=1000
performance_schema_digests_size=100000
performance_schema_max_sql_text_length=1024
performance_schema_max_digest_length=1024


从下面计算公式可以人工计算出内存和查询的内存对的上. 上次没有计算是少了线程数.

    5.1
    | memory/performance_schema/events_statements_summary_by_digest.digest_text      | 97.66 MiB   | 97.66 MiB   | 97.66 MiB    |
    | memory/performance_schema/events_statements_summary_by_digest                  | 396.73 MiB  | 396.73 MiB  | 396.73 MiB   |
     公式:performance_schema_max_digest_length * performance_schema_digests_size
        =1024 * 100000 
        =97.65MB
        
        =events_statements_summary_by_digest.size*performance_schema_digests_size
        =4160*10,0000
        =396MB
        # 从表定义来说才781个字节,4160字节把TEXT统计进去了?
    5.2
    | memory/performance_schema/events_statements_history_long.sql_text              | 1000.00 KiB | 1000.00 KiB | 1000.00 KiB  |
    | memory/performance_schema/events_statements_history_long.digest_text           | 1000.00 KiB | 1000.00 KiB | 1000.00 KiB  |
    | memory/performance_schema/events_statements_history_long                       | 1.39 MiB    | 1.39 MiB    | 1.39 MiB     |




    performance_schema_max_digest_length * performance_schema_events_stages_history_long_size
    =1024*1000
    =1000KB




    events_statements_history_long=events_statements_history_long.size *performance_schema_events_stages_history_long_size
    =1456*1000
    =1.388MB




    5.3
    | memory/performance_schema/events_statements_history.sql_text                   | 2.50 MiB    | 2.50 MiB    | 2.50 MiB     |
    | memory/performance_schema/events_statements_history.digest_text                | 2.50 MiB    | 2.50 MiB    | 2.50 MiB     |
    | memory/performance_schema/events_statements_history                            | 3.55 MiB    | 3.55 MiB    | 3.55 MiB     |
    performance_schema_max_digest_length * performance_schema_events_statements_history_size*performance_schema_max_thread_instances(状态:threads.count)
    =1024*10*-1(256)
    =2.50MB


    6 限制PS内存 

    下面有些参数是自动缩放的，不需要显式配置，除非您想建立内存分配的显式限制：0 是禁止 -1 无限 N 可限制,默认是无限-1

      performance_schema_accounts_size = 10
      performance_schema_hosts_size     =20
      performance_schema_max_cond_instances =256    #256
      performance_schema_max_file_instances =1024  #4096
      performance_schema_max_index_stat  =1024            #性能架构维护其统计信息的最大索引数
      performance_schema_max_metadata_locks  =256   #2048
      performance_schema_max_mutex_instances =1024   #7168
      performance_schema_max_prepared_statements_instances =256   #1024
      performance_schema_max_program_instances =256            #存储过程
      performance_schema_max_rwlock_instances =1024  #16384
      performance_schema_max_socket_instances =128   #256
      performance_schema_max_table_handles     =1024
      performance_schema_max_table_instances   =1024
      performance_schema_max_table_lock_stat   =256
      performance_schema_max_thread_instances =128  #256
      performance_schema_users_size =128

      上面 #号后面跟的数字是从STATUS获得的,有的没有找到! 从而觉得改小多少

      具体是什么意思可以参考官方文档. 官方文档-1的比较多,文档未必100%准确

      https://dev.mysql.com/doc/refman/8.0/en/performance-schema-system-variables.html



      7 重启后验证

      mysql>  SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, FORMAT_BYTES(SUM(current_alloc)) AS current_alloc
          ->         FROM sys.x$memory_global_by_current_bytes
          ->         GROUP BY SUBSTRING_INDEX(event_name,'/',2)
          ->         ORDER BY SUM(current_alloc) DESC;
      +---------------------------+---------------+
      | code_area                 | current_alloc |
      +---------------------------+---------------+
      | memory/innodb             | 2.17 GiB      |
      | memory/performance_schema | 540.93 MiB    |
      | memory/sql                | 10.02 MiB     |
      | memory/mysys              | 8.62 MiB      |
      | memory/temptable          | 3.00 MiB      |
      | memory/mysqld_openssl     | 219.66 KiB    |
      | memory/vio                | 3.18 KiB      |
      | memory/mysqlx             | 2.67 KiB      |
      | memory/myisam             |  696 bytes    |
      | memory/csv                |   88 bytes    |
      | memory/blackhole          |   88 bytes    |
      +---------------------------+---------------+
      11 rows in set (0.04 sec)

      明显从608MB 减少到了540MB

      PS 内存细节

      mysql> SELECT event_name,sys.format_bytes(current_number_of_bytes_used) as CUR_USE_MEM,
          ->  sys.format_bytes(sum_number_of_bytes_alloc) as SUM_USE_MEM,
          ->  sys.format_bytes(high_number_of_bytes_used) as HIGH_USE_MEM
          ->  FROM performance_schema.memory_summary_global_by_event_name
          ->  WHERE EVENT_NAME LIKE 'memory/performance_schema/%'
          ->  order by current_number_of_bytes_used desc;
      +--------------------------------------------------------------------------------+-------------+-------------+--------------+
      | event_name                                                                     | CUR_USE_MEM | SUM_USE_MEM | HIGH_USE_MEM |
      +--------------------------------------------------------------------------------+-------------+-------------+--------------+
      | memory/performance_schema/events_statements_summary_by_digest                  | 396.73 MiB  | 396.73 MiB  | 396.73 MiB   |
      | memory/performance_schema/events_statements_summary_by_digest.digest_text      | 97.66 MiB   | 97.66 MiB   | 97.66 MiB    |
      | memory/performance_schema/events_statements_summary_by_thread_by_event_name    | 5.10 MiB    | 5.10 MiB    | 5.10 MiB     |
      | memory/performance_schema/events_statements_summary_by_user_by_event_name      | 5.10 MiB    | 5.10 MiB    | 5.10 MiB     |
      | memory/performance_schema/memory_summary_by_thread_by_event_name               | 4.66 MiB    | 4.66 MiB    | 4.66 MiB     |
      | memory/performance_schema/memory_summary_by_user_by_event_name                 | 4.66 MiB    | 4.66 MiB    | 4.66 MiB     |
      | memory/performance_schema/events_waits_summary_by_thread_by_event_name         | 2.59 MiB    | 2.59 MiB    | 2.59 MiB     |
      | memory/performance_schema/events_waits_summary_by_user_by_event_name           | 2.59 MiB    | 2.59 MiB    | 2.59 MiB     |
      | memory/performance_schema/events_statements_history                            | 1.78 MiB    | 1.78 MiB    | 1.78 MiB     |
      | memory/performance_schema/events_statements_current                            | 1.78 MiB    | 1.78 MiB    | 1.78 MiB     |
      | memory/performance_schema/events_statements_history_long                       | 1.39 MiB    | 1.39 MiB    | 1.39 MiB     |
      | memory/performance_schema/events_statements_history.digest_text                | 1.25 MiB    | 1.25 MiB    | 1.25 MiB     |
      | memory/performance_schema/events_statements_history.sql_text                   | 1.25 MiB    | 1.25 MiB    | 1.25 MiB     |
      | memory/performance_schema/events_statements_current.digest_text                | 1.25 MiB    | 1.25 MiB    | 1.25 MiB     |
      | memory/performance_schema/events_statements_current.sql_text                   | 1.25 MiB    | 1.25 MiB    | 1.25 MiB     |
      | memory/performance_schema/table_shares                                         | 1.00 MiB    | 1.00 MiB    | 1.00 MiB     |
      | memory/performance_schema/events_statements_history_long.sql_text              | 1000.00 KiB | 1000.00 KiB | 1000.00 KiB  |
      | memory/performance_schema/events_statements_history_long.digest_text           | 1000.00 KiB | 1000.00 KiB | 1000.00 KiB  |
      | memory/performance_schema/events_statements_summary_by_host_by_event_name      | 815.78 KiB  | 815.78 KiB  | 815.78 KiB   |
      | memory/performance_schema/memory_summary_by_host_by_event_name                 | 745.31 KiB  | 745.31 KiB  | 745.31 KiB   |
      | memory/performance_schema/file_instances                                       | 704.00 KiB  | 704.00 KiB  | 704.00 KiB   |
      | memory/performance_schema/events_stages_summary_by_thread_by_event_name        | 700.00 KiB  | 700.00 KiB  | 700.00 KiB   |
      | memory/performance_schema/events_stages_summary_by_user_by_event_name          | 700.00 KiB  | 700.00 KiB  | 700.00 KiB   |
      | memory/performance_schema/threads                                              | 536.00 KiB  | 536.00 KiB  | 536.00 KiB   |
      | memory/performance_schema/events_transactions_history                          | 430.00 KiB  | 430.00 KiB  | 430.00 KiB   |
      | memory/performance_schema/events_waits_summary_by_host_by_event_name           | 415.00 KiB  | 415.00 KiB  | 415.00 KiB   |
      | memory/performance_schema/events_statements_summary_by_account_by_event_name   | 407.89 KiB  | 407.89 KiB  | 407.89 KiB   |
      | memory/performance_schema/memory_summary_by_account_by_event_name              | 372.66 KiB  | 372.66 KiB  | 372.66 KiB   |
      | memory/performance_schema/table_io_waits_summary_by_index_usage                | 352.00 KiB  | 352.00 KiB  | 352.00 KiB   |
      | memory/performance_schema/events_transactions_history_long                     | 335.94 KiB  | 335.94 KiB  | 335.94 KiB   |
      | memory/performance_schema/file_handle                                          | 256.00 KiB  | 256.00 KiB  | 256.00 KiB   |
      | memory/performance_schema/events_waits_history                                 | 220.00 KiB  | 220.00 KiB  | 220.00 KiB   |
      | memory/performance_schema/events_waits_summary_by_account_by_event_name        | 207.50 KiB  | 207.50 KiB  | 207.50 KiB   |
      | memory/performance_schema/events_waits_history_long                            | 171.88 KiB  | 171.88 KiB  | 171.88 KiB   |
      | memory/performance_schema/mutex_instances                                      | 160.00 KiB  | 160.00 KiB  | 160.00 KiB   |
      | memory/performance_schema/events_stages_history                                | 130.00 KiB  | 130.00 KiB  | 130.00 KiB   |
      | memory/performance_schema/metadata_locks                                       | 128.00 KiB  | 128.00 KiB  | 128.00 KiB   |
      | memory/performance_schema/events_statements_summary_by_program                 | 112.00 KiB  | 112.00 KiB  | 112.00 KiB   |
      | memory/performance_schema/events_stages_summary_by_host_by_event_name          | 109.38 KiB  | 109.38 KiB  | 109.38 KiB   |
      | memory/performance_schema/events_stages_history_long                           | 101.56 KiB  | 101.56 KiB  | 101.56 KiB   |
      | memory/performance_schema/memory_class                                         | 99.38 KiB   | 99.38 KiB   | 99.38 KiB    |
      | memory/performance_schema/rwlock_instances                                     | 88.00 KiB   | 88.00 KiB   | 88.00 KiB    |
      | memory/performance_schema/mutex_class                                          | 87.50 KiB   | 87.50 KiB   | 87.50 KiB    |
      | memory/performance_schema/events_errors_summary_by_thread_by_error             | 82.00 KiB   | 82.00 KiB   | 82.00 KiB    |
      | memory/performance_schema/events_errors_summary_by_user_by_error               | 82.00 KiB   | 82.00 KiB   | 82.00 KiB    |
      | memory/performance_schema/users                                                | 80.00 KiB   | 80.00 KiB   | 80.00 KiB    |
      | memory/performance_schema/session_connect_attrs                                | 64.00 KiB   | 64.00 KiB   | 64.00 KiB    |
      | memory/performance_schema/setup_actors                                         | 64.00 KiB   | 64.00 KiB   | 64.00 KiB    |
      | memory/performance_schema/setup_objects                                        | 56.00 KiB   | 56.00 KiB   | 56.00 KiB    |
      | memory/performance_schema/events_stages_summary_by_account_by_event_name       | 54.69 KiB   | 54.69 KiB   | 54.69 KiB    |
      | memory/performance_schema/stage_class                                          | 43.75 KiB   | 43.75 KiB   | 43.75 KiB    |
      | memory/performance_schema/statement_class                                      | 42.56 KiB   | 42.56 KiB   | 42.56 KiB    |
      | memory/performance_schema/events_statements_summary_global_by_event_name       | 40.79 KiB   | 40.79 KiB   | 40.79 KiB    |
      | memory/performance_schema/socket_instances                                     | 39.00 KiB   | 39.00 KiB   | 39.00 KiB    |
      | memory/performance_schema/cond_class                                           | 37.50 KiB   | 37.50 KiB   | 37.50 KiB    |
      | memory/performance_schema/memory_summary_global_by_event_name                  | 37.27 KiB   | 37.27 KiB   | 37.27 KiB    |
      | memory/performance_schema/thread_class                                         | 25.00 KiB   | 25.00 KiB   | 25.00 KiB    |
      | memory/performance_schema/file_class                                           | 25.00 KiB   | 25.00 KiB   | 25.00 KiB    |
      | memory/performance_schema/cond_instances                                       | 18.00 KiB   | 18.00 KiB   | 18.00 KiB    |
      | memory/performance_schema/rwlock_class                                         | 17.50 KiB   | 17.50 KiB   | 17.50 KiB    |
      | memory/performance_schema/hosts                                                | 15.00 KiB   | 15.00 KiB   | 15.00 KiB    |
      | memory/performance_schema/events_errors_summary_by_host_by_error               | 12.81 KiB   | 12.81 KiB   | 12.81 KiB    |
      | memory/performance_schema/events_transactions_summary_by_user_by_event_name    | 11.00 KiB   | 11.00 KiB   | 11.00 KiB    |
      | memory/performance_schema/events_transactions_summary_by_thread_by_event_name  | 11.00 KiB   | 11.00 KiB   | 11.00 KiB    |
      | memory/performance_schema/accounts                                             | 9.38 KiB    | 9.38 KiB    | 9.38 KiB     |
      | memory/performance_schema/events_errors_summary_by_account_by_error            | 6.41 KiB    | 6.41 KiB    | 6.41 KiB     |
      | memory/performance_schema/events_stages_summary_global_by_event_name           | 5.47 KiB    | 5.47 KiB    | 5.47 KiB     |
      | memory/performance_schema/socket_class                                         | 3.12 KiB    | 3.12 KiB    | 3.12 KiB     |
      | memory/performance_schema/scalable_buffer                                      | 1.77 KiB    | 1.77 KiB    | 1.77 KiB     |
      | memory/performance_schema/events_transactions_summary_by_host_by_event_name    | 1.72 KiB    | 1.72 KiB    | 1.72 KiB     |
      | memory/performance_schema/events_transactions_summary_by_account_by_event_name |  880 bytes  |  880 bytes  |  880 bytes   |
      | memory/performance_schema/events_errors_summary_global_by_error                |  640 bytes  |  640 bytes  |  640 bytes   |
      | memory/performance_schema/table_handles                                        |    0 bytes  |    0 bytes  |    0 bytes   |
      | memory/performance_schema/table_lock_waits_summary_by_table                    |    0 bytes  |    0 bytes  |    0 bytes   |
      | memory/performance_schema/prepared_statements_instances                        |    0 bytes  |    0 bytes  |    0 bytes   |
      +--------------------------------------------------------------------------------+-------------+-------------+--------------+
      75 rows in set, 1 warning (0.05 sec)

      PS 变量 -1 无限还剩下两个

      mysql> show VARIABLES like 'performance%';
      +----------------------------------------------------------+--------+
      | Variable_name                                            | Value  |
      +----------------------------------------------------------+--------+
      | performance_schema                                       | ON     |
      | performance_schema_accounts_size                         | 10     |
      | performance_schema_digests_size                          | 100000 |
      | performance_schema_error_size                            | 20     |
      | performance_schema_events_stages_history_long_size       | 1000   |
      | performance_schema_events_stages_history_size            | 10     |
      | performance_schema_events_statements_history_long_size   | 1000   |
      | performance_schema_events_statements_history_size        | 10     |
      | performance_schema_events_transactions_history_long_size | 1000   |
      | performance_schema_events_transactions_history_size      | 10     |
      | performance_schema_events_waits_history_long_size        | 1000   |
      | performance_schema_events_waits_history_size             | 10     |
      | performance_schema_hosts_size                            | 20     |
      | performance_schema_max_cond_classes                      | 150    |
      | performance_schema_max_cond_instances                    | 256    |
      | 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                    | 1024   |
      | performance_schema_max_index_stat                        | 1024   |
      | performance_schema_max_memory_classes                    | 530    |
      | performance_schema_max_metadata_locks                    | 256    |
      | performance_schema_max_mutex_classes                     | 350    |
      | performance_schema_max_mutex_instances                   | 1024   |
      | performance_schema_max_prepared_statements_instances     | 256    |
      | performance_schema_max_program_instances                 | 256    |
      | performance_schema_max_rwlock_classes                    | 70     |
      | performance_schema_max_rwlock_instances                  | 1024   |
      | performance_schema_max_socket_classes                    | 10     |
      | performance_schema_max_socket_instances                  | 128    |
      | performance_schema_max_sql_text_length                   | 1024   |
      | performance_schema_max_stage_classes                     | 175    |
      | performance_schema_max_statement_classes                 | 227    |
      | performance_schema_max_statement_stack                   | 10     |
      | performance_schema_max_table_handles                     | 1024   |
      | performance_schema_max_table_instances                   | 1024   |
      | performance_schema_max_table_lock_stat                   | 256    |
      | performance_schema_max_thread_classes                    | 100    |
      | performance_schema_max_thread_instances                  | 128    |
      | performance_schema_session_connect_attrs_size            | 512    |
      | performance_schema_setup_actors_size                     | -1     |
      | performance_schema_setup_objects_size                    | -1     |
      | performance_schema_show_processlist                      | ON     |
      | performance_schema_users_size                            | 128    |
      +----------------------------------------------------------+--------+
      45 rows in set (0.01 sec)

      PS 状态
      下面的状态.COUNT都被限制了,另外SIZE行大小感觉都比定义字段总和还大

      mysql>  show engine performance_schema status ;
      +--------------------+-------------------------------------------------------------+-----------+
      | Type               | Name                                                        | Status    |
      +--------------------+-------------------------------------------------------------+-----------+
      | performance_schema | events_waits_current.size                                   | 176       |
      | performance_schema | events_waits_current.count                                  | 768       |
      | performance_schema | events_waits_history.size                                   | 176       |
      | performance_schema | events_waits_history.count                                  | 1280      |
      | performance_schema | events_waits_history.memory                                 | 225280    |
      | performance_schema | events_waits_history_long.size                              | 176       |
      | performance_schema | events_waits_history_long.count                             | 1000      |
      | performance_schema | events_waits_history_long.memory                            | 176000    |
      | performance_schema | (pfs_mutex_class).size                                      | 256       |
      | performance_schema | (pfs_mutex_class).count                                     | 350       |
      | performance_schema | (pfs_mutex_class).memory                                    | 89600     |
      | performance_schema | (pfs_rwlock_class).size                                     | 256       |
      | performance_schema | (pfs_rwlock_class).count                                    | 70        |
      | performance_schema | (pfs_rwlock_class).memory                                   | 17920     |
      | performance_schema | (pfs_cond_class).size                                       | 256       |
      | performance_schema | (pfs_cond_class).count                                      | 150       |
      | performance_schema | (pfs_cond_class).memory                                     | 38400     |
      | performance_schema | (pfs_thread_class).size                                     | 256       |
      | performance_schema | (pfs_thread_class).count                                    | 100       |
      | performance_schema | (pfs_thread_class).memory                                   | 25600     |
      | performance_schema | (pfs_file_class).size                                       | 320       |
      | performance_schema | (pfs_file_class).count                                      | 80        |
      | performance_schema | (pfs_file_class).memory                                     | 25600     |
      | performance_schema | mutex_instances.size                                        | 80        |
      | performance_schema | mutex_instances.count                                       | 2048      |
      | performance_schema | mutex_instances.memory                                      | 163840    |
      | performance_schema | rwlock_instances.size                                       | 88        |
      | performance_schema | rwlock_instances.count                                      | 1024      |
      | performance_schema | rwlock_instances.memory                                     | 90112     |
      | performance_schema | cond_instances.size                                         | 72        |
      | performance_schema | cond_instances.count                                        | 256       |
      | performance_schema | cond_instances.memory                                       | 18432     |
      | performance_schema | threads.size                                                | 4288      |
      | performance_schema | threads.count                                               | 128       |
      | performance_schema | threads.memory                                              | 548864    |
      | performance_schema | file_instances.size                                         | 704       |
      | performance_schema | file_instances.count                                        | 1024      |
      | performance_schema | file_instances.memory                                       | 720896    |
      | performance_schema | (pfs_file_handle).size                                      | 8         |
      | performance_schema | (pfs_file_handle).count                                     | 32768     |
      | performance_schema | (pfs_file_handle).memory                                    | 262144    |
      | performance_schema | events_waits_summary_by_thread_by_event_name.size           | 32        |
      | performance_schema | events_waits_summary_by_thread_by_event_name.count          | 84992     |
      | performance_schema | events_waits_summary_by_thread_by_event_name.memory         | 2719744   |
      | performance_schema | (pfs_table_share).size                                      | 1024      |
      | performance_schema | (pfs_table_share).count                                     | 1024      |
      | performance_schema | (pfs_table_share).memory                                    | 1048576   |
      | performance_schema | (pfs_table).size                                            | 9280      |
      | performance_schema | (pfs_table).count                                           | 0         |
      | performance_schema | (pfs_table).memory                                          | 0         |
      | performance_schema | setup_actors.size                                           | 512       |
      | performance_schema | setup_actors.count                                          | 128       |
      | performance_schema | setup_actors.memory                                         | 65536     |
      | performance_schema | setup_objects.size                                          | 448       |
      | performance_schema | setup_objects.count                                         | 128       |
      | performance_schema | setup_objects.memory                                        | 57344     |
      | performance_schema | (pfs_account).size                                          | 960       |
      | performance_schema | (pfs_account).count                                         | 10        |
      | performance_schema | (pfs_account).memory                                        | 9600      |
      | performance_schema | events_waits_summary_by_account_by_event_name.size          | 32        |
      | performance_schema | events_waits_summary_by_account_by_event_name.count         | 6640      |
      | performance_schema | events_waits_summary_by_account_by_event_name.memory        | 212480    |
      | performance_schema | events_waits_summary_by_user_by_event_name.size             | 32        |
      | performance_schema | events_waits_summary_by_user_by_event_name.count            | 84992     |
      | performance_schema | events_waits_summary_by_user_by_event_name.memory           | 2719744   |
      | performance_schema | events_waits_summary_by_host_by_event_name.size             | 32        |
      | performance_schema | events_waits_summary_by_host_by_event_name.count            | 13280     |
      | performance_schema | events_waits_summary_by_host_by_event_name.memory           | 424960    |
      | performance_schema | (pfs_user).size                                             | 640       |
      | performance_schema | (pfs_user).count                                            | 128       |
      | performance_schema | (pfs_user).memory                                           | 81920     |
      | performance_schema | (pfs_host).size                                             | 768       |
      | performance_schema | (pfs_host).count                                            | 20        |
      | performance_schema | (pfs_host).memory                                           | 15360     |
      | performance_schema | (pfs_stage_class).size                                      | 256       |
      | performance_schema | (pfs_stage_class).count                                     | 175       |
      | performance_schema | (pfs_stage_class).memory                                    | 44800     |
      | performance_schema | events_stages_history.size                                  | 104       |
      | performance_schema | events_stages_history.count                                 | 1280      |
      | performance_schema | events_stages_history.memory                                | 133120    |
      | performance_schema | events_stages_history_long.size                             | 104       |
      | performance_schema | events_stages_history_long.count                            | 1000      |
      | performance_schema | events_stages_history_long.memory                           | 104000    |
      | performance_schema | events_stages_summary_by_thread_by_event_name.size          | 32        |
      | performance_schema | events_stages_summary_by_thread_by_event_name.count         | 22400     |
      | performance_schema | events_stages_summary_by_thread_by_event_name.memory        | 716800    |
      | performance_schema | events_stages_summary_global_by_event_name.size             | 32        |
      | performance_schema | events_stages_summary_global_by_event_name.count            | 175       |
      | performance_schema | events_stages_summary_global_by_event_name.memory           | 5600      |
      | performance_schema | events_stages_summary_by_account_by_event_name.size         | 32        |
      | performance_schema | events_stages_summary_by_account_by_event_name.count        | 1750      |
      | performance_schema | events_stages_summary_by_account_by_event_name.memory       | 56000     |
      | performance_schema | events_stages_summary_by_user_by_event_name.size            | 32        |
      | performance_schema | events_stages_summary_by_user_by_event_name.count           | 22400     |
      | performance_schema | events_stages_summary_by_user_by_event_name.memory          | 716800    |
      | performance_schema | events_stages_summary_by_host_by_event_name.size            | 32        |
      | performance_schema | events_stages_summary_by_host_by_event_name.count           | 3500      |
      | performance_schema | events_stages_summary_by_host_by_event_name.memory          | 112000    |
      | performance_schema | (pfs_statement_class).size                                  | 192       |
      | performance_schema | (pfs_statement_class).count                                 | 227       |
      | performance_schema | (pfs_statement_class).memory                                | 43584     |
      | performance_schema | events_statements_history.size                              | 1456      |
      | performance_schema | events_statements_history.count                             | 1280      |
      | performance_schema | events_statements_history.memory                            | 1863680   |
      | performance_schema | events_statements_history_long.size                         | 1456      |
      | performance_schema | events_statements_history_long.count                        | 1000      |
      | performance_schema | events_statements_history_long.memory                       | 1456000   |
      | performance_schema | events_statements_summary_by_thread_by_event_name.size      | 184       |
      | performance_schema | events_statements_summary_by_thread_by_event_name.count     | 29056     |
      | performance_schema | events_statements_summary_by_thread_by_event_name.memory    | 5346304   |
      | performance_schema | events_statements_summary_global_by_event_name.size         | 184       |
      | performance_schema | events_statements_summary_global_by_event_name.count        | 227       |
      | performance_schema | events_statements_summary_global_by_event_name.memory       | 41768     |
      | performance_schema | events_statements_summary_by_account_by_event_name.size     | 184       |
      | performance_schema | events_statements_summary_by_account_by_event_name.count    | 2270      |
      | performance_schema | events_statements_summary_by_account_by_event_name.memory   | 417680    |
      | performance_schema | events_statements_summary_by_user_by_event_name.size        | 184       |
      | performance_schema | events_statements_summary_by_user_by_event_name.count       | 29056     |
      | performance_schema | events_statements_summary_by_user_by_event_name.memory      | 5346304   |
      | performance_schema | events_statements_summary_by_host_by_event_name.size        | 184       |
      | performance_schema | events_statements_summary_by_host_by_event_name.count       | 4540      |
      | performance_schema | events_statements_summary_by_host_by_event_name.memory      | 835360    |
      | performance_schema | events_statements_current.size                              | 1456      |
      | performance_schema | events_statements_current.count                             | 1280      |
      | performance_schema | events_statements_current.memory                            | 1863680   |
      | performance_schema | (pfs_socket_class).size                                     | 320       |
      | performance_schema | (pfs_socket_class).count                                    | 10        |
      | performance_schema | (pfs_socket_class).memory                                   | 3200      |
      | performance_schema | socket_instances.size                                       | 312       |
      | performance_schema | socket_instances.count                                      | 128       |
      | performance_schema | socket_instances.memory                                     | 39936     |
      | performance_schema | events_statements_summary_by_digest.size                    | 4160      |
      | performance_schema | events_statements_summary_by_digest.count                   | 100000    |
      | performance_schema | events_statements_summary_by_digest.memory                  | 416000000 |
      | performance_schema | events_statements_summary_by_program.size                   | 448       |
      | performance_schema | events_statements_summary_by_program.count                  | 256       |
      | performance_schema | events_statements_summary_by_program.memory                 | 114688    |
      | performance_schema | session_connect_attrs.size                                  | 128       |
      | performance_schema | session_connect_attrs.count                                 | 512       |
      | performance_schema | session_connect_attrs.memory                                | 65536     |
      | performance_schema | prepared_statements_instances.size                          | 1664      |
      | performance_schema | prepared_statements_instances.count                         | 0         |
      | performance_schema | prepared_statements_instances.memory                        | 0         |
      | performance_schema | (account_hash).count                                        | 4         |
      | performance_schema | (account_hash).size                                         | 4         |
      | performance_schema | (digest_hash).count                                         | 15        |
      | performance_schema | (digest_hash).size                                          | 16        |
      | performance_schema | (filename_hash).count                                       | 1024      |
      | performance_schema | (filename_hash).size                                        | 1024      |
      | performance_schema | (host_hash).count                                           | 3         |
      | performance_schema | (host_hash).size                                            | 4         |
      | performance_schema | (setup_actor_hash).count                                    | 1         |
      | performance_schema | (setup_actor_hash).size                                     | 1         |
      | performance_schema | (setup_object_hash).count                                   | 20        |
      | performance_schema | (setup_object_hash).size                                    | 32        |
      | performance_schema | (table_share_hash).count                                    | 54        |
      | performance_schema | (table_share_hash).size                                     | 64        |
      | performance_schema | (user_hash).count                                           | 3         |
      | performance_schema | (user_hash).size                                            | 4         |
      | performance_schema | (program_hash).count                                        | 1         |
      | performance_schema | (program_hash).size                                         | 1         |
      | performance_schema | host_cache.size                                             | 512       |
      | performance_schema | (pfs_memory_class).row_size                                 | 192       |
      | performance_schema | (pfs_memory_class).row_count                                | 530       |
      | performance_schema | (pfs_memory_class).memory                                   | 101760    |
      | performance_schema | memory_summary_by_thread_by_event_name.row_size             | 72        |
      | performance_schema | memory_summary_by_thread_by_event_name.row_count            | 67840     |
      | performance_schema | memory_summary_by_thread_by_event_name.memory               | 4884480   |
      | performance_schema | memory_summary_global_by_event_name.row_size                | 72        |
      | performance_schema | memory_summary_global_by_event_name.row_count               | 530       |
      | performance_schema | memory_summary_global_by_event_name.memory                  | 38160     |
      | performance_schema | memory_summary_by_account_by_event_name.row_size            | 72        |
      | performance_schema | memory_summary_by_account_by_event_name.row_count           | 5300      |
      | performance_schema | memory_summary_by_account_by_event_name.memory              | 381600    |
      | performance_schema | memory_summary_by_user_by_event_name.row_size               | 72        |
      | performance_schema | memory_summary_by_user_by_event_name.row_count              | 67840     |
      | performance_schema | memory_summary_by_user_by_event_name.memory                 | 4884480   |
      | performance_schema | memory_summary_by_host_by_event_name.row_size               | 72        |
      | performance_schema | memory_summary_by_host_by_event_name.row_count              | 10600     |
      | performance_schema | memory_summary_by_host_by_event_name.memory                 | 763200    |
      | performance_schema | metadata_locks.row_size                                     | 512       |
      | performance_schema | metadata_locks.row_count                                    | 256       |
      | performance_schema | metadata_locks.memory                                       | 131072    |
      | performance_schema | events_transactions_history.size                            | 344       |
      | performance_schema | events_transactions_history.count                           | 1280      |
      | performance_schema | events_transactions_history.memory                          | 440320    |
      | performance_schema | events_transactions_history_long.size                       | 344       |
      | performance_schema | events_transactions_history_long.count                      | 1000      |
      | performance_schema | events_transactions_history_long.memory                     | 344000    |
      | performance_schema | events_transactions_summary_by_thread_by_event_name.size    | 88        |
      | performance_schema | events_transactions_summary_by_thread_by_event_name.count   | 128       |
      | performance_schema | events_transactions_summary_by_thread_by_event_name.memory  | 11264     |
      | performance_schema | events_transactions_summary_by_account_by_event_name.size   | 88        |
      | performance_schema | events_transactions_summary_by_account_by_event_name.count  | 10        |
      | performance_schema | events_transactions_summary_by_account_by_event_name.memory | 880       |
      | performance_schema | events_transactions_summary_by_user_by_event_name.size      | 88        |
      | performance_schema | events_transactions_summary_by_user_by_event_name.count     | 128       |
      | performance_schema | events_transactions_summary_by_user_by_event_name.memory    | 11264     |
      | performance_schema | events_transactions_summary_by_host_by_event_name.size      | 88        |
      | performance_schema | events_transactions_summary_by_host_by_event_name.count     | 20        |
      | performance_schema | events_transactions_summary_by_host_by_event_name.memory    | 1760      |
      | performance_schema | table_lock_waits_summary_by_table.size                      | 344       |
      | performance_schema | table_lock_waits_summary_by_table.count                     | 0         |
      | performance_schema | table_lock_waits_summary_by_table.memory                    | 0         |
      | performance_schema | table_io_waits_summary_by_index_usage.size                  | 352       |
      | performance_schema | table_io_waits_summary_by_index_usage.count                 | 1024      |
      | performance_schema | table_io_waits_summary_by_index_usage.memory                | 360448    |
      | performance_schema | (history_long_statements_digest_token_array).count          | 1000      |
      | performance_schema | (history_long_statements_digest_token_array).size           | 1024      |
      | performance_schema | (history_long_statements_digest_token_array).memory         | 1024000   |
      | performance_schema | (history_statements_digest_token_array).count               | 1280      |
      | performance_schema | (history_statements_digest_token_array).size                | 1024      |
      | performance_schema | (history_statements_digest_token_array).memory              | 1310720   |
      | performance_schema | (current_statements_digest_token_array).count               | 1280      |
      | performance_schema | (current_statements_digest_token_array).size                | 1024      |
      | performance_schema | (current_statements_digest_token_array).memory              | 1310720   |
      | performance_schema | (history_long_statements_text_array).count                  | 1000      |
      | performance_schema | (history_long_statements_text_array).size                   | 1024      |
      | performance_schema | (history_long_statements_text_array).memory                 | 1024000   |
      | performance_schema | (history_statements_text_array).count                       | 1280      |
      | performance_schema | (history_statements_text_array).size                        | 1024      |
      | performance_schema | (history_statements_text_array).memory                      | 1310720   |
      | performance_schema | (current_statements_text_array).count                       | 1280      |
      | performance_schema | (current_statements_text_array).size                        | 1024      |
      | performance_schema | (current_statements_text_array).memory                      | 1310720   |
      | performance_schema | (statements_digest_token_array).count                       | 100000    |
      | performance_schema | (statements_digest_token_array).size                        | 1024      |
      | performance_schema | (statements_digest_token_array).memory                      | 102400000 |
      | performance_schema | events_errors_summary_by_thread_by_error.size               | 656       |
      | performance_schema | events_errors_summary_by_thread_by_error.count              | 128       |
      | performance_schema | events_errors_summary_by_thread_by_error.memory             | 83968     |
      | performance_schema | events_errors_summary_by_account_by_error.size              | 656       |
      | performance_schema | events_errors_summary_by_account_by_error.count             | 10        |
      | performance_schema | events_errors_summary_by_account_by_error.memory            | 6560      |
      | performance_schema | events_errors_summary_by_user_by_error.size                 | 656       |
      | performance_schema | events_errors_summary_by_user_by_error.count                | 128       |
      | performance_schema | events_errors_summary_by_user_by_error.memory               | 83968     |
      | performance_schema | events_errors_summary_by_host_by_error.size                 | 656       |
      | performance_schema | events_errors_summary_by_host_by_error.count                | 20        |
      | performance_schema | events_errors_summary_by_host_by_error.memory               | 13120     |
      | performance_schema | events_errors_summary_global_by_error.size                  | 640       |
      | performance_schema | events_errors_summary_global_by_error.count                 | 1         |
      | performance_schema | events_errors_summary_global_by_error.memory                | 640       |
      | performance_schema | (pfs_buffer_scalable_container).count                       | 16        |
      | performance_schema | (pfs_buffer_scalable_container).memory                      | 1808      |
      | performance_schema | (max_global_server_errors).count                            | 20        |
      | performance_schema | (max_session_server_errors).count                           | 20        |
      | performance_schema | performance_schema.memory                                   | 567320424 |
      +--------------------+-------------------------------------------------------------+-----------+
      248 rows in set (0.00 sec)

      最后两个 也可以设置,放入参数文件,名字一样

        | performance_schema_setup_actors_size                     | 1024   |
        | performance_schema_setup_objects_size                    | 1024   |



        认识 performance_schema 01

        认识 performance_schema 02

        认识 performance_schema 03

        认识 performance_schema 04

        认识 performance_schema 05


        mysqlmemory
        文章转载自海鲨数据库架构师，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

        评论