mysql performance schema 第2部分

最新推荐文章于 2023-07-26 16:29:06 发布

cudi7618

最新推荐文章于 2023-07-26 16:29:06 发布

阅读量170

点赞数

文章标签：数据库

之前写过了一个mysql performane schema 的一个介绍，接着因为各种原因，放下了这块，

这几天就又捡起来了，就杂七杂八的再写一点吧：

Mysql Performance Schema
DBA/李思亮
我们使用mysql 5.5 也有一段时间了，对于performance schema 引擎的使用问题，
一直没有开展起来，主要原因是资料太少。这段时间花了一些时间专门的关注了
一下，形成本文档：
Mysql 5.5 版本是第一个版本，本文提到的部分数据可能不适用，其中包括了
mysql 5.6 的一些情况。
从数据库内部讲这些表关注了数据库的4 个模块
分别是：
1. Mutex （互斥体，锁相关)
2. RWLOCKs (读写锁）
3. File I/O （文件io）
4. Thread （线程）
包括了全部的mutex 42 个， rwlocks 10 个，以及6 种类型的线程（thread），文
件io 在分为数据(data) , 日志（log），临时文件（temp file i/o）。
Performance schema 记录的信息，比较底层，如果想深入的研究，需要对mysql
的internal 有一定的了解，本篇，我们不关注源代码级的信息，仅从一个使用者
的角度来看，如何使用performance schema 为数据库优化提供一些建议。
Performance schema 下面的表：
mysql.cnt_it.sock@performance_schema> show tables ;
+----------------------------------------------+
| Tables_in_performance_schema |
+----------------------------------------------+
| cond_instances |
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_global_by_event_name |
| file_instances |
| file_summary_by_event_name |
| file_summary_by_instance |
| mutex_instances |
| performance_timers |
| rwlock_instances |
| setup_consumers |
| setup_instruments |
| setup_timers |
| threads |
+----------------------------------------------+
17 rows in set (0.00 sec)
这些表基本可以分为4部分
一类是instance 表，
Instance 表记录的是那种类型的对象被探测或者记录。
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 （读写锁）
这些表是只读的。
一类是Setup 表
Setup 表记录了配置信息，以及监控的参数等等。
setup 表：
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'performance_schema'
-> AND TABLE_NAME LIKE 'setup%';
+-------------------+
| TABLE_NAME |
+-------------------+
| setup_consumers |
| setup_instruments |
| setup_timers |
+-------------------+
setup_consumers 描述各种事件
setup_instruments 描述这个数据库下的表名以及是否开启监控。
setup_timers 描述监控选项已经采样频率的时间间隔
这个要多说一点目前performance-schema 只支持'wait' 时间的监控，代码树
上wait/ 下的函数都可以监控到。
文档上说了只有'wait' 事件的检测，有没有其他的选项呢？
看看源代码：
static row_setup_timers all_setup_timers_data[COUNT_SETUP_TIMERS]=
{
{
{ C_STRING_WITH_LEN("wait") },
&wait_timer
}
};
THR_LOCK table_setup_timers::m_table_lock;
int table_setup_timers::update_row_values(TABLE *table,
const unsigned char *,
unsigned char *,
Field **fields)
{
Field *f;
longlong value;
DBUG_ASSERT(m_row);
for (; (f= *fields) ; fields++)
{
if (bitmap_is_set(table->write_set, f->field_index))
{
switch(f->field_index)
{
case 0: /* NAME */
my_error(ER_WRONG_PERFSCHEMA_USAGE, MYF(0));
return HA_ERR_WRONG_COMMAND;
case 1: /* TIMER_NAME */
value= get_field_enum(f);
if ((value >= FIRST_TIMER_NAME) && (value <=
LAST_TIMER_NAME))
*(m_row->m_timer_name_ptr)= (enum_timer_name) value;
else
return HA_ERR_WRONG_COMMAND;
break;
default:
DBUG_ASSERT(false);
}
}
}
return 0;
}
代码里写死了，只有'wait' 一个值，不排除以后的版本会增加新的关键字，但至
少目前就只有一个啦。
并且这个表的name 字段是不允许修改的的。下面的修改的方法里没有做任何处
理，涉及到name 字段的修改，直接报错。
mysql> SELECT * FROM setup_timers;
+------+------------+
| NAME | TIMER_NAME |
+------+------------+
| wait | CYCLE |
+------+------------+
只有timer_name 可以update 这是一个enum 字段。
一类是event 表
Event 表记录了各种事件，提供了当前事件（current），历史事件（history）以
及history_long 事件，history 表记录了每个线程的最近的10 个事件，history_long
记录了10000 个事件。这两个历史表都是先进先出（FIFO)的规则。
性能事件表：
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'performance_schema'
-> AND TABLE_NAME LIKE '%current';
+----------------------+
| TABLE_NAME |
+----------------------+
| events_waits_current |
+----------------------+
记录当前正在发生的等待事件，这个表是只读的表，不能update ，delete ，但是
可以truncate
具体字段是什么意思就自己去查doc 了，这里不说了。
性能历史表：
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'performance_schema'
-> AND (TABLE_NAME LIKE '%history' OR TABLE_NAME LIKE
'%history_long');
+---------------------------+
| TABLE_NAME |
+---------------------------+
| events_waits_history |
| events_waits_history_long |
+---------------------------+
这些表与前面的性能表的结构是一致的， history 表只保留每个线程（thread）的
最近的10 个事件， history_long 记录最近的10000 个事件。
新事件入表，如果旧表满了，就会丢弃旧的数据，标准的先进先出（FIFO) 这俩
表也是只读表，只能truncate
还有一类是summary 表
Summary 表是对event 的统计数据。
事件汇总表：
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'performance_schema'
-> AND TABLE_NAME LIKE '%summary%';
+----------------------------------------------+
| TABLE_NAME |
+----------------------------------------------+
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_global_by_event_name |
| file_summary_by_event_name |
| file_summary_by_instance |
+----------------------------------------------+
按照相关的标准对进行的事件统计表，
events_waits_summary_global_by_event_name 在mysql5.5.7 以前叫：
EVENTS_WAITS_SUMMARY_BY_EVENT_NAME
表也是只读的，只能turcate
对于performance schema 下的每个表的详细定义，以及字段的解读，请参考手册：
http://dev.mysql.com/doc/refman/5.5/en/performance-schema-table-descriptions.html
大小写问题
另外一个问题，performance schema 库下的表，或字段名，在查询的时候，建议
全部小写，原因是5.5.5 以前是大写，导致在某些平台上
lower_case_table_names =1 出现问题，5.5.8 后全部改为小写字母。
启动设置问题
Performance schama 默认是不启用的，启动的参数为静态参数，需要在my.cnf 里
设置performance_schema=1,或者设定命令行参数。
当服务器启动起来的时候，会在err 日志里记录一条信息：
“100407 16:13:02 [Note] Buffered information: Performance schema enabled.”
如果是关闭的则会显示另外一个信息：
“100407 16:13:02 [Note] Buffered information: Performance schema disabled (reason:
start parameters)”
还有中情况，设置了参数但是performance schema 启动失败，这种情况极少发生，
日志里的信息：
“Performance schema disabled (reason: init failed)”，启动失败情况下，performacne
schema 会关闭自己，并且mysql 数据库会继续启动，起结果就跟关闭启动参数是
一样的。
相应的系统参数：
mysql.cnt_it.sock@performance_schema> show global variables like
'%performance_schema%' ;
+---------------------------------------------------+---------+
| 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 |
+---------------------------------------------------+---------+
详解这些参数：
http://dev.mysql.com/doc/refman/5.5/en/performance-schema-system-variables.html
参考mysql 的官方文档。
对应的状态参数：
mysql.cnt_house.sock@performance_schema> show global status like
'%performance_schema%' ;
+------------------------------------------+-------+
| 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 |
+------------------------------------------+-------+
14 rows in set (0.00 sec)
参数详解：
http://dev.mysql.com/doc/refman/5.5/en/performance-schema-status-variables.html
这些状态值，显示的是因为内存吃紧，或者系统太忙，导致的一些没有performance
schema 记录下来的事件。
对于performance schema 运行状态如何？
可以使用命令show engine status 来看：
mysql.cnt_house.sock@performance_schema> show engine performance_schema
status \G
*************************** 1. row ***************************
Type: performance_schema
Name: events_waits_current.row_size
Status: 136
*************************** 2. row ***************************
Type: performance_schema
Name: events_waits_current.row_count
Status: 3000
*************************** 3. row ***************************
Type: performance_schema
Name: events_waits_history.row_size
Status: 120
*************************** 4. row ***************************
Type: performance_schema
Name: events_waits_history.row_count
Status: 10000
*************************** 5. row ***************************
Type: performance_schema
Name: events_waits_history.memory
Status: 1200000
*************************** 6. row ***************************
Type: performance_schema
Name: events_waits_history_long.row_size
Status: 120
*************************** 7. row ***************************
Type: performance_schema
Name: events_waits_history_long.row_count
Status: 10000
*************************** 8. row ***************************
Type: performance_schema
Name: events_waits_history_long.memory
Status: 1200000
*************************** 9. row ***************************
Type: performance_schema
Name: (pfs_mutex_class).row_size
Status: 248
*************************** 10. row ***************************
Type: performance_schema
Name: (pfs_mutex_class).row_count
Status: 200
*************************** 11. row ***************************
Type: performance_schema
Name: (pfs_mutex_class).memory
Status: 49600
*************************** 12. row ***************************
Type: performance_schema
Name: (pfs_rwlock_class).row_size
Status: 296
*************************** 13. row ***************************
Type: performance_schema
Name: (pfs_rwlock_class).row_count
Status: 30
*************************** 14. row ***************************
Type: performance_schema
Name: (pfs_rwlock_class).memory
Status: 8880
*************************** 15. row ***************************
Type: performance_schema
Name: (pfs_cond_class).row_size
Status: 216
*************************** 16. row ***************************
Type: performance_schema
Name: (pfs_cond_class).row_count
Status: 80
*************************** 17. row ***************************
Type: performance_schema
Name: (pfs_cond_class).memory
Status: 17280
*************************** 18. row ***************************
Type: performance_schema
Name: (pfs_thread_class).row_size
Status: 136
*************************** 19. row ***************************
Type: performance_schema
Name: (pfs_thread_class).row_count
Status: 50
*************************** 20. row ***************************
Type: performance_schema
Name: (pfs_thread_class).memory
Status: 6800
*************************** 21. row ***************************
Type: performance_schema
Name: (pfs_file_class).row_size
Status: 240
*************************** 22. row ***************************
Type: performance_schema
Name: (pfs_file_class).row_count
Status: 50
*************************** 23. row ***************************
Type: performance_schema
Name: (pfs_file_class).memory
Status: 12000
*************************** 24. row ***************************
Type: performance_schema
Name: mutex_instances.row_size
Status: 136
*************************** 25. row ***************************
Type: performance_schema
Name: mutex_instances.row_count
Status: 1000000
*************************** 26. row ***************************
Type: performance_schema
Name: mutex_instances.memory
Status: 136000000
*************************** 27. row ***************************
Type: performance_schema
Name: rwlock_instances.row_size
Status: 200
*************************** 28. row ***************************
Type: performance_schema
Name: rwlock_instances.row_count
Status: 1000000
*************************** 29. row ***************************
Type: performance_schema
Name: rwlock_instances.memory
Status: 200000000
*************************** 30. row ***************************
Type: performance_schema
Name: cond_instances.row_size
Status: 88
*************************** 31. row ***************************
Type: performance_schema
Name: cond_instances.row_count
Status: 1000
*************************** 32. row ***************************
Type: performance_schema
Name: cond_instances.memory
Status: 88000
*************************** 33. row ***************************
Type: performance_schema
Name: threads.row_size
Status: 504
*************************** 34. row ***************************
Type: performance_schema
Name: threads.row_count
Status: 1000
*************************** 35. row ***************************
Type: performance_schema
Name: threads.memory
Status: 504000
*************************** 36. row ***************************
Type: performance_schema
Name: file_instances.row_size
Status: 624
*************************** 37. row ***************************
Type: performance_schema
Name: file_instances.row_count
Status: 10000
*************************** 38. row ***************************
Type: performance_schema
Name: file_instances.memory
Status: 6240000
*************************** 39. row ***************************
Type: performance_schema
Name: (pfs_file_handle).row_size
Status: 8
*************************** 40. row ***************************
Type: performance_schema
Name: (pfs_file_handle).row_count
Status: 32768
*************************** 41. row ***************************
Type: performance_schema
Name: (pfs_file_handle).memory
Status: 262144
*************************** 42. row ***************************
Type: performance_schema
Name: events_waits_summary_by_thread_by_event_name.row_size
Status: 48
*************************** 43. row ***************************
Type: performance_schema
Name: events_waits_summary_by_thread_by_event_name.row_count
Status: 360000
*************************** 44. row ***************************
Type: performance_schema
Name: events_waits_summary_by_thread_by_event_name.memory
Status: 17280000
*************************** 45. row ***************************
Type: performance_schema
Name: (pfs_table_share).row_size
Status: 488
*************************** 46. row ***************************
Type: performance_schema
Name: (pfs_table_share).row_count
Status: 50000
*************************** 47. row ***************************
Type: performance_schema
Name: (pfs_table_share).memory
Status: 24400000
*************************** 48. row ***************************
Type: performance_schema
Name: (pfs_table).row_size
Status: 72
*************************** 49. row ***************************
Type: performance_schema
Name: (pfs_table).row_count
Status: 100000
*************************** 50. row ***************************
Type: performance_schema
Name: (pfs_table).memory
Status: 7200000
*************************** 51. row ***************************
Type: performance_schema
Name: performance_schema.memory
Status: 394468704
51 rows in set (0.00 sec)
三个命令：
Show engine innodb status
Show engine innodb mutex
Show engine performacne_schema status
统计事件/对象可以分为几类：
1）wait/io io 等待事件
2）wait/io/file 文件io等待，一般是指等待文件操作完成。
3）wait/sync 等待同步对象，内容比较广泛，他的time_wait 时间包括了，
请求对一个对象加锁，因为阻塞而导致的等待时间。
4）wait/sync/cond 主要用于线程间同步，引起的等待事件。
5）wait/sync/mutex 对资源访问的的互斥体的请求等待，mutex 对资源的
独占式访问，的一种锁结构。
6）wait/sync/rwlock 对变量访问、修改的锁等待。主意是对变量，主要用
于线程安全的变量访问。
这些表的上的操作限制：
对于setup 表上，有些字段是可以update 的，用于控制监控的开启关闭。
对于event_wait_* 这样的表式可以truncate 的，但是不能delete，
update。可以在特定的诊断任务开始前，清空原来的数据。
对于summary 的表，也可以执行truncate 操作，但是只是把summary的统
计字段置0或者null ，而不会删除里面的行记录。
用performance schema 诊断性能问题的一般步骤：
1.运行测试case
2.打开performance schema 收集信息
3.排除没有干扰的因素，并关闭对应的收集器，例如确定file i/o 不是问题的根本
原因则可以关闭file io 收集，truncate event 表，然后从新收集性能数据。
4.重复1-3，越来越接近根本原因，也可以从events_waits_history_long
表的统计分析，问题部分会越来越集中。
5.一旦确定了问题的原因，就可以着手解决问题，优化性能。
6.mutex_instances.LOCKED_BY_THREAD_ID 和
rwlock_instances.WRITE_LOCKED_BY_THREAD_ID
这两个字段对于发现性能瓶颈，或则死锁是非常重要的。
1）假设thread1 在等待某个mutex
2）那么可以看看thread1 在等待什么资源。
SELECT * FROM events_waits_current WHERE THREAD_ID =
thread_1;
根据其中的OBJECT_INSTANCE_BEGIN 去关联mutex_instances 表确
定是什么当前资源被那个线程占有，
mysql.cnt_it.sock@performance_schema> SELECT * FROM
mutex_instances where OBJECT_INSTANCE_BEGIN = 15806424;
+------------------------------------+------------------
-----+---------------------+
| NAME | OBJECT_INSTANCE_BEGIN |
LOCKED_BY_THREAD_ID |
+------------------------------------+------------------
-----+---------------------+
| wait/synch/mutex/sql/LOG::LOCK_log |
15805160 | 1305458 |
+------------------------------------+------------------
-----+---------------------+
1 row in set (0.02 sec)
3）。我们就可以去看看这线程当前正在做什么。
mysql.cnt_it.sock@performance_schema> select * from
events_waits_current where thread_id =1305458 \G
*************************** 1. row
***************************
THREAD_ID: 1305458
EVENT_ID: 76094
EVENT_NAME:
wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond
SOURCE: log.cc:5535
TIMER_START: 8958895120369531348
TIMER_END: NULL
TIMER_WAIT: NULL
SPINS: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 15806424
NESTING_EVENT_ID: NULL
OPERATION: timed_wait
NUMBER_OF_BYTES: NULL
FLAGS: 0
1 row in set (0.00 sec)
这是一个示例，我们测试环境，捕捉不到，我就拿了一个bin log 的写线程。
Sql 示例：
1等待事件的top N
mysql> SELECT EVENT_NAME, SUM(TIMER_WAIT), COUNT(*), SOURCE
-> FROM EVENTS_WAITS_HISTORY_LONG
-> WHERE EVENT_NAME LIKE “%innodb%”
-> GROUP BY SOURCE
-> ORDER BY SUM(TIMER_WAIT) DESC;
2平均等待时间top N :
mysql> SELECT EVENT_NAME, SUM(TIMER_WAIT)/count(*), source
-> FROM EVENTS_WAITS_HISTORY_LONG
-> WHERE EVENT_NAME LIKE “%innodb%”
-> GROUP BY source
-> ORDER BY SUM(TIMER_WAIT) / COUNT(*) DESC;
EVENTS_WAITS_HISTORY 每个线程10 行记录，
EVENTS_WAITS_HISTORY_LONG 表默认是10000 行记录，如果认为尺寸不够
大，可以通过修改参数：
“performance_schema_events_waits_history_size”
“performance_schema_events_waits_history_long_size”
这两个变量来调整， history_long 表的最大尺寸是100W 行记录。
3最热的mutex ：
这里的时间单位是微秒（pico-second，或者说是cpu 频率周期）
mysql> SELECT EVENT_NAME, COUNT_STAR,SUM_TIMER_WAIT,
AVG_TIMER_WAIT
FROM events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%innodb%'
order BY COUNT_STAR DESC;
从下面的结果，我们可以大体了解下，我们电脑网计数器的情况：
Buff pool 和日志和undo 是比较忙的，需要优化的。
*************************** 1. row
***************************
EVENT_NAME: wait/synch/mutex/innodb/kernel_mutex
COUNT_STAR: 183768448476
SUM_TIMER_WAIT: 4527315253253694
AVG_TIMER_WAIT: 24635
*************************** 2. row
***************************
EVENT_NAME: wait/synch/mutex/innodb/buf_pool_mutex
COUNT_STAR: 15121853525
SUM_TIMER_WAIT: 3285302101091527
AVG_TIMER_WAIT: 217255
*************************** 3. row
***************************
EVENT_NAME: wait/synch/mutex/innodb/log_sys_mutex
COUNT_STAR: 7142949915
SUM_TIMER_WAIT: 1559525178971936
AVG_TIMER_WAIT: 218330
*************************** 4. row
***************************
EVENT_NAME:
wait/synch/mutex/innodb/log_flush_order_mutex
COUNT_STAR: 6649384948
SUM_TIMER_WAIT: 691589270730534
AVG_TIMER_WAIT: 104008
*************************** 5. row
***************************
EVENT_NAME: wait/synch/mutex/innodb/trx_undo_mutex
COUNT_STAR: 3615217180
SUM_TIMER_WAIT: 112854228995684
AVG_TIMER_WAIT: 31216
PERFORMANCE SCHEMA 的性能问题：
这部分没有做过测试，数据来自网上
全部默认开启performance schema 大约会有8% 左右的性能消耗，
如果开启了performance schema ，但是在setup 表中关闭事件记录，大约
会有4% 左右的性能损失。
我们的建议：
安装mysql 的时候，开启performance schema 参数，
并在setup 表中关闭事件记录，这样便于在遇到严重问题，需要打开性能日志
的时候，需要重启mysql

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/133735/viewspace-705525/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/133735/viewspace-705525/

cudi7618

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql performance schema 第2部分

之前写过了一个mysql performane schema 的一个介绍，接着因为各种原因，放下了这块，这几天就又捡起来了，就杂七杂八的再写一点吧： Mysql Performance SchemaDBA/李思亮我们使用mysq...
复制链接

扫一扫