ui设计和交互设计培训,南昌做seo的公司有哪些,网站 实施,做网站海报http://space.itpub.net/133735/viewspace-684361 mysql performance_schema 初探#xff1a;mysql 5.5 版本 新增了一个性能优化的引擎#xff1a; PERFORMANCE_SCHEMA 这个功能默认是关闭的#xff1a; 需要设置参数#xff1a; performance_schema 才可以启动该功能mysql 5.5 版本 新增了一个性能优化的引擎 PERFORMANCE_SCHEMA 这个功能默认是关闭的 需要设置参数 performance_schema 才可以启动该功能这个参数是静态参数只能写在my.cnf 中 不能动态修改。 先看看有什么东西吧 mysql use performance_schema; Database changed mysql 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) 这里的数据表分为几类 1 setup table 设置表配置监控选项。 2 current events table : 记录当前那些thread 正在发生什么事情。 3 history table 发生的各种事件的历史记录表 4 summary table 对各种事件的统计表 5 杂项表乱七八糟表。 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 字段。 性能事件表 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 事件汇总表 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 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 | ------------------------------------------------- 如何使用这个来诊断数据库性能问题呢 且听下回分解 之前写过了一个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.sockperformance_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_schema1,或者设定命令行参数。 当服务器启动起来的时候会在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.sockperformance_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.sockperformance_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.sockperformance_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 统计事件/对象可以分为几类 1wait/io io 等待事件 2wait/io/file 文件io等待一般是指等待文件操作完成。 3wait/sync 等待同步对象内容比较广泛他的time_wait 时间包括了 请求对一个对象加锁因为阻塞而导致的等待时间。 4wait/sync/cond 主要用于线程间同步引起的等待事件。 5wait/sync/mutex 对资源访问的的互斥体的请求等待mutex 对资源的 独占式访问的一种锁结构。 6wait/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.sockperformance_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.sockperformance_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 转载于:https://blog.51cto.com/716378/721973
