MySQL5.5 performance - schema的介绍

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

按照相关的标准对进行的事件统计表，

events_waits_summary_global_by_event_name 在mysql5.5.7 以前叫： EVENTS_WAITS_SUMMARY_BY_EVENT_NAME

表也是只读的，只能turcate

performance schema instance 表：

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES -> WHERE TABLE_SCHEMA = 'performance_schema' -> AND TABLE_NAME LIKE '%instances'; +------------------+ | TABLE_NAME | +------------------+ | cond_instances | | file_instances | | mutex_instances | | rwlock_instances | +------------------+

记录各种等待事件涉及到的实例 ： 主要是3类: cond (容器？ ） mutex （互斥锁） ，rwlock （读写锁）

这表是只读的。 乱七八糟表：

mysql> SELECT * FROM performance_timers;

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

| TIMER_NAME | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD | +-------------+-----------------+------------------+----------------+ | CYCLE | 2389029850 | 1 | 72 | | NANOSECOND | NULL | NULL | NULL | | MICROSECOND | 1000000 | 1 | 585 | | MILLISECOND | 1035 | 1 | 738 | | TICK | 101 | 1 | 630 | +-------------+-----------------+------------------+----------------+

这个表式只读表，记录了事件采样频率的设定，我们前面说的setup_timer 表的timer_name 只能区这4个中一个。

mysql> SELECT * FROM threads;

+-----------+----------------+----------------------------------------+ | THREAD_ID | PROCESSLIST_ID | NAME | +-----------+----------------+----------------------------------------+ | 0 | 0 | thread/sql/main |

| 1 | 0 | thread/innodb/io_handler_thread | | 16 | 0 | thread/sql/signal_handler | | 23 | 7 | thread/sql/one_connection | | 5 | 0 | thread/innodb/io_handler_thread | | 12 | 0 | thread/innodb/srv_lock_timeout_thread | | 22 | 6 | thread/sql/one_connection |

这个表记录了系统里当前存在的各种线程。

下面就是 涉及到performance_schema的各个系统参数了：

mysql> SHOW VARIABLES LIKE 'perf%';

+---------------------------------------------------+---------+ | Variable_name | Value | +---------------------------------------------------+---------+ | performance_schema | ON |

| performance_schema_events_waits_history_long_size | 10000 | | performance_schema_events_waits_history_size | 10 | | performance_schema_max_cond_classes | 80 | | performance_schema_max_cond_instances | 1000 | | performance_schema_max_file_classes | 50 | | performance_schema_max_file_handles | 32768 | | performance_schema_max_file_instances | 10000 | | performance_schema_max_mutex_classes | 200 | | performance_schema_max_mutex_instances | 1000000 | | performance_schema_max_rwlock_classes | 30 | | performance_schema_max_rwlock_instances | 1000000 | | performance_schema_max_table_handles | 100000 | | performance_schema_max_table_instances | 50000 | | performance_schema_max_thread_classes | 50 | | performance_schema_max_thread_instances | 1000 | +---------------------------------------------------+---------+ 涉及到系统状态的参数：

mysql> SHOW STATUS LIKE 'perf%'; +------------------------------------------+-------+ | Variable_name | Value | +------------------------------------------+-------+

| Performance_schema_cond_classes_lost | 0 | | Performance_schema_cond_instances_lost | 0 | | Performance_schema_file_classes_lost | 0 | | Performance_schema_file_handles_lost | 0 | | Performance_schema_file_instances_lost | 0 | | Performance_schema_locker_lost | 0 | | Performance_schema_mutex_classes_lost | 0 | | Performance_schema_mutex_instances_lost | 0 |

| Performance_schema_rwlock_classes_lost | 0 | | Performance_schema_rwlock_instances_lost | 0 | | Performance_schema_table_handles_lost | 0 | | Performance_schema_table_instances_lost | 0 | | Performance_schema_thread_classes_lost | 0 | | Performance_schema_thread_instances_lost | 0 | +------------------------------------------+-------+