ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql performance schema 第2部分

mysql performance schema 第2部分

Linux操作系统 作者:SuBaofaCU 时间:2014-01-22 21:07:58 0 删除 编辑
之前写过了一个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/29367190/viewspace-1074287/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2014-01-22

  • 博文量
    2
  • 访问量
    7159