• 1. MySQL 性能调优介绍2013.10.18 DBA 王洪权 mydbalife@gmail.com 新浪微博 @foreverreturn
  • 2. 内容概要 一 性能分析介绍(简单介绍下orazdba,oprofile,ioprofile) 二 performance schema介绍
  • 3. 2018/10/21工具化的启发工具化的重要性(熟练运用已有的工具,可以快速对数据库进行诊断,时刻了解你的数据库的运行状况)
  • 4. 性能分析MySQL相关: show session/global status like '%variables%' ; show full processlist; show engine innodb mutex; SHOW ENGINE INNODB STATUS; SHOW PROFILE; MySQL slow query
  • 5. 性能分析 MySQL之外 orzdba (时刻监控innodb的各项指标) pt-query-digest(定位慢查询消耗,进行相关sql优化) oprofile(定位cpu消耗) perf top (定位mysql 内部的热点) tcpdump + pt-query-digest(捕捉异常时刻sql) ioprofile (定位系统的写入和相关fsync调用) pstack (定位异常时刻系统的瓶颈) pt-pmp(对pstack扩展) Blktrace+btt(定位io进入到块层的情况) relay-fetch(加速备库预热,解决主从延时)
  • 6. 性能分析其他 mytop innotop mysqltuner.pl dba-slow-picker.pl(诊断异常时刻SQL) ..........
  • 7. 2018/10/21mysqltuner.pl 给mysql做个简单报告
  • 8. 2018/10/21mysqltuner.pl 给mysql做个简单报告
  • 9. 2018/10/21orzdba 时刻了解你的数据库
  • 10. orzdba 时刻了解你的数据库
  • 11. 2018/10/21oprofile 介绍== oprofile 是什么 == oprofile也是一个开源的profiling工具,它使用硬件调试寄存器来统计信息,进行profiling的开销比较小,而且可以对内核进行profiling。 Oprofile 是一个全局的抽样统计工具 Oprofile是一种细粒度的工具,可以为指令集或者为函数、系统调用或中断处理例程收集采样。Oprofile 通过取样来工作。使用收集到的评测数据,用户可以很容易地找出性能问题。
  • 12. 2018/10/21Oprofile系统工作流图
  • 13. 2018/10/21oprofile 使用场景cpu无端占用高?应用程序响应慢?苦于没有分析的工具? oprofile利用cpu硬件层面提供的性能计数器(performance counter),通过计数采样,帮助我们从进程、函数、代码层面找出占用cpu的"罪魁祸首"。
  • 14. 2018/10/21oprofile使用# 加载oprofile内核模块 opcontrol --init   #我们对内核的取样没兴趣 opcontrol --setup --no-vmlinux #在开始收集采样数据前回顾下我们的设置 opcontrol --status #清除上一次采样到的数据 opcontrol --reset  #启动oprofiled守护程序,从内核中拉出采样数据 opcontrol --start    #运行我们的程序   #收集采样数据 opcontrol --dump #关闭守护程序, 同时准备好采样的数据 opcontrol --shutdown
  • 15. 2018/10/21oprofile获取采样信息  #系统级别的 opreport --long-filenames #模块级别的 opreport image:foo -l #源码级别的 opannotate image:foo -s
  • 16. 2018/10/21oprofile使用 opcontrol --deinit modprobe oprofile timer=1 $dmesg|grep oprofile|tail -n 1 (oprofile: using timer interrupt.) opcontrol --reset pcontrol --separate=lib --no-vmlinux --start --image=/home/mysql_user/mysqlhome/bin/mysqld opcontrol --dump opcontrol --shutdown opreport -l /home/mysql_user/mysqlhome/bin/mysqld
  • 17. 2018/10/21oprofile 诊断你的cpu异常
  • 18. 2018/10/21ioprofile 查看IO情况的利器
  • 19. 2018/10/21ioprofile 查看IO情况的利器
  • 20. 2018/10/21ioprofile 透过进程发现写入状况
  • 21. 2018/10/21ioprofile 产看IO情况的利器
  • 22. 2018/10/21ioprofile 产看IO情况的利器
  • 23. PERFORMANCE_SCHEMA简介Performance Schema的功能,类似于Oracle数据库提供的丰富的系统表,用于将数据库内部的运行情况展示出来。包括:Mutex,RWLock,IO等等。 performance schema 可以使得DBA 更加容易的定位系统瓶颈 performance schema也使得mysql的性能更加可测量化
  • 24. Performance schema介绍 • MySQL 5.5 File I/O, Mutexes, RW Locks etc • MySQL 5.6 File I/O Table I/O Query Digest Statement Table locks MySQL 5.7 memory,replication2018/10/21
  • 25. MySQL 5.6 PSMySQL 5.6 默认开启了performance_schema,可以动态的禁用. ● File I/O (查看最热的文件和事件) ● Table I/O (查看最热的表的情况) ● Query Digest (定位热点查询和表) ● Statement (定位热点查询和表) ● Table locks (定位热点表锁)
  • 26. Performance_schema 配置my.cnf performance_schema_instrument = ‘%=on’ performance_schema_consumer_events_stages_current = ON performance_schema_consumer_events_stages_history = ON performance_schema_consumer_events_stages_history_long = ON performance_schema_consumer_events_statements_current = ON performance_schema_consumer_events_statements_history = ON performance_schema_consumer_events_statements_history_long = ON performance_schema_consumer_events_waits_current = ON performance_schema_consumer_events_waits_history = ON performance_schema_consumer_events_waits_history_long = ON performance_schema_consumer_global_instrumentation = ON performance_schema_consumer_thread_instrumentation = ON performance_schema_consumer_statements_digest = ON 默认启动开启了performance_schema,可以通过DML 动态启用相关的instrument和comsumer UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'; UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'; 2018/10/21
  • 27. 查看performance_schema变量SHOW GLOBAL VARIABLES LIKE 'performance\_schema%'; +--------------------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------------------+-------+ | performance_schema | ON | | performance_schema_accounts_size | 100 | | performance_schema_digests_size | 10000 | | performance_schema_events_stages_history_long_size | 10000 | | performance_schema_events_stages_history_size | 10 | | performance_schema_events_statements_history_long_size | 10000 | | performance_schema_events_statements_history_size | 10 | | performance_schema_events_waits_history_long_size | 10000 | | performance_schema_events_waits_history_size | 10 | | performance_schema_hosts_size | 100 | | performance_schema_max_cond_classes | 80 | | performance_schema_max_cond_instances | 6948 | | performance_schema_max_file_classes | 50 | | performance_schema_max_file_handles | 32768 | | performance_schema_max_file_instances | 7856 | +--------------------------------------------------------+-------+2018/10/21
  • 28. 查看performance_schema占用内存大小SHOW ENGINE PERFORMANCE_SCHEMA STATUS; +--------------------+-----------------------------------------------------------+-----------+ | Type | Name | Status | +--------------------+-----------------------------------------------------------+-----------+ | performance_schema | events_waits_current.size | 184 | | performance_schema | events_waits_current.count | 12600 | | performance_schema | events_waits_history.size | 184 | | performance_schema | events_waits_history.count | 21000 | | performance_schema | events_waits_history.memory | 3864000 | | performance_schema | events_waits_history_long.size | 184 | | performance_schema | events_waits_history_long.count | 10000 | | performance_schema | events_waits_history_long.memory | 1840000 …………………………………………………………………………………………………………………………… | performance_schema | performance_schema.memory | 700101608 | +--------------------+-----------------------------------------------------------+----------+ | performance_schema.memory 代表占用内存大小700M左右2018/10/21
  • 29. performance_schema 状态变量 SHOW STATUS LIKE "%perf%"; +-----------------------------------------------+-------+ | Variable_name | Value | +-----------------------------------------------+-------+ | Performance_schema_accounts_lost | 0 | | Performance_schema_cond_classes_lost | 0 | | Performance_schema_cond_instances_lost | 0 | | Performance_schema_digest_lost | 0 | | Performance_schema_file_classes_lost | 0 | | Performance_schema_file_handles_lost | 0 | | Performance_schema_file_instances_lost | 0 | | Performance_schema_hosts_lost | 0 | | Performance_schema_locker_lost | 0 | | Performance_schema_memory_classes_lost | 0 | | Performance_schema_mutex_classes_lost | 0 | | Performance_schema_mutex_instances_lost | 0 | | Performance_schema_nested_statement_lost | 0 | | Performance_schema_program_lost | 0 | | Performance_schema_rwlock_classes_lost | 0 | | Performance_schema_rwlock_instances_lost | 0 | | Performance_schema_session_connect_attrs_lost | 0 | | Performance_schema_socket_classes_lost | 0 | | Performance_schema_socket_instances_lost | 0 | | Performance_schema_stage_classes_lost | 0 | | Performance_schema_statement_classes_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 | | Performance_schema_users_lost | 0 | +-----------------------------------------------+-------+ 26 rows in set (0.00 sec)显示由于内存限制导致某些统计信息没有计入PS中
  • 30. Performance Schema set up tableshow tables like '%setup%'; +----------------------------------------+ | Tables_in_performance_schema (%setup%) | +----------------------------------------+ | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_timers | +----------------------------------------+ 5 rows in set (0.00 sec) SELECT * FROM setup_actors; +------+------+------+ | HOST | USER | ROLE | +------+------+------+ | % | % | % | +------+------+------+ 1 row in set (0.00 sec) 2018/10/21默认情况下,监控的表对象排除mysql/PS/IS库的对象,其中IS库下的表,不管是否开启,都不会去监控
  • 31. Threads表 select THREAD_ID,NAME,TYPE,INSTRUMENTED from threads; +-----------+----------------------------------------+------------+--------------+ | THREAD_ID | NAME | TYPE | INSTRUMENTED | +-----------+----------------------------------------+------------+--------------+ | 1 | thread/sql/main | BACKGROUND | YES | | 2 | thread/innodb/io_ibuf_thread | BACKGROUND | YES | | 3 | thread/innodb/io_read_thread | BACKGROUND | YES | | 4 | thread/innodb/io_read_thread | BACKGROUND | YES | | 5 | thread/innodb/io_log_thread | BACKGROUND | YES | | 6 | thread/innodb/io_read_thread | BACKGROUND | YES | | 7 | thread/innodb/io_read_thread | BACKGROUND | YES | | 8 | thread/innodb/io_read_thread | BACKGROUND | YES | | 9 | thread/innodb/io_read_thread | BACKGROUND | YES | | 10 | thread/innodb/io_read_thread | BACKGROUND | YES | | 11 | thread/innodb/io_read_thread | BACKGROUND | YES | | 12 | thread/innodb/io_write_thread | BACKGROUND | YES | | 13 | thread/innodb/io_write_thread | BACKGROUND | YES | | 14 | thread/innodb/io_write_thread | BACKGROUND | YES | | 15 | thread/innodb/io_write_thread | BACKGROUND | YES | | 16 | thread/innodb/io_write_thread | BACKGROUND | YES | | 17 | thread/innodb/io_write_thread | BACKGROUND | YES | | 18 | thread/innodb/io_write_thread | BACKGROUND | YES | | 19 | thread/innodb/io_write_thread | BACKGROUND | YES | | 21 | thread/innodb/srv_lock_timeout_thread | BACKGROUND | YES | | 22 | thread/innodb/srv_error_monitor_thread | BACKGROUND | YES | | 23 | thread/innodb/srv_monitor_thread | BACKGROUND | YES | | 24 | thread/innodb/srv_master_thread | BACKGROUND | YES | | 25 | thread/innodb/srv_purge_thread | BACKGROUND | YES | | 26 | thread/innodb/page_cleaner_thread | BACKGROUND | YES | | 27 | thread/sql/signal_handler | BACKGROUND | YES | | 28 | thread/sql/one_connection | FOREGROUND | YES | | 29 | thread/sql/one_connection | FOREGROUND | YES | | 30 | thread/sql/one_connection | FOREGROUND | YES | | 31 | thread/sql/one_connection | FOREGROUND | YES | | 32 | thread/sql/one_connection | FOREGROUND | YES | +-----------+----------------------------------------+------------+--------------+ 31 rows in set (0.00 sec)
  • 32. instrumentselect * from setup_instruments limit 10; +---------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +---------------------------------------------------------+---------+-------+ | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | YES | YES | | wait/synch/mutex/sql/LOCK_des_key_file | YES | YES | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit | YES | YES | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue | YES | YES | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done | YES | YES | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue | YES | YES | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index | YES | YES | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log | YES | YES | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos | YES | YES | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync | YES | YES | +---------------------------------------------------------+---------+-------+ 10 rows in set (0.01 sec) update setup_instruments set ENABLED='NO', TIMED='NO' where name like '%mutex%'; Query OK, 0 rows affected (0.00 sec) Rows matched: 156 Changed: 0 Warnings: 02018/10/21
  • 33. instrumentinstrument树形结构,主要包括idle/wait/stage/statement,下层包括例如sync,io,再往下层可能为以划分成mutex/cond/rwlock,最后为具体对象或者模块。 wait/synch/mutex/innodb/trx_mutex ● idel socket的空闲信息,事件记录在socket_instances表中 ● stage 命名规则为stage/code_area/stage_name code_area值为sql/mysys,stage_name表示执行语句过程中的各个阶段 ● stage 命名规则为statement/sql或者com sql下为具体的sql类型,com下为服务器的一些命令相关) ● wait 最为关注的部分,包括文件的操作时间,socket事件,表的io的时间统计,主要包含wait/io,wait/lock,wait/synch(mutex(wait/synch/mutex))、读写锁(wait/synch/rwlock)
  • 34. consumer select * from setup_consumers; +--------------------------------+---------+ | NAME | ENABLED | +--------------------------------+---------+ | events_stages_current | NO | | events_stages_history | NO | | events_stages_history_long | NO | | events_statements_current | YES | | events_statements_history | NO | | events_statements_history_long | NO | | events_waits_current | NO | | events_waits_history | NO | | events_waits_history_long | NO | | global_instrumentation | YES | | thread_instrumentation | YES | | statements_digest | YES | +--------------------------------+---------+2018/10/21其中高级别的consumer决定是否去检查低级别的consumer
  • 35. 2018/10/21For files show tables like '%file%'; +---------------------------------------+ | Tables_in_performance_schema (%file%) | +---------------------------------------+ | file_instances | | file_summary_by_event_name | | file_summary_by_instance | +---------------------------------------+ 3 rows in set (0.00 sec)
  • 36. 2018/10/21查询读写top 5 的等待 select EVENT_NAME, COUNT_READ 'Reads', COUNT_WRITE Writes, COUNT_MISC Misc, (COUNT_MISC + COUNT_WRITE + COUNT_READ) as SUM_IO from file_summary_by_event_name order by 5 desc limit 5; +--------------------------------------+-------+--------+------+--------+ | EVENT_NAME | Reads | Writes | Misc | SUM_IO | +--------------------------------------+-------+--------+------+--------+ | wait/io/file/sql/binlog | 31307 | 31308 | 0 | 62615 | | wait/io/file/innodb/innodb_data_file | 2985 | 18661 | 1797 | 23443 | | wait/io/file/innodb/innodb_log_file | 0 | 167 | 167 | 334 | | wait/io/file/sql/slow_log | 0 | 57 | 0 | 57 | | wait/io/file/archive/FRM | 0 | 0 | 0 | 0 | +--------------------------------------+-------+--------+------+--------+ 5 rows in set (0.00 sec)
  • 37. 2018/10/21查询读写top 5的fileselect FILE_NAME FILE, COUNT_READ 'Reads', COUNT_WRITE Writes, COUNT_MISC Misc, (COUNT_MISC + COUNT_WRITE + COUNT_READ) as SUM_IO from file_summary_by_instance order by SUM_IO desc limit 5; +-----------------------------------+-------+--------+------+--------+ | FILE | Reads | Writes | Misc | SUM_IO | +-----------------------------------+-------+--------+------+--------+ | /data/mysql_57/mysql-bin.000043 | 23042 | 23042 | 0 | 46084 | | /data/mysql_57/sbtest/sbtest4.ibd | 2174 | 5847 | 255 | 8276 | | /data/mysql_57/ibdata1 | 0 | 523 | 347 | 870 | | /data/mysql_57/ib_logfile1 | 0 | 83 | 83 | 166 | | /data/mysql_57/localhost-slow.log | 0 | 50 | 0 | 50 | +-----------------------------------+-------+--------+------+--------+ 5 rows in set (0.00 sec)
  • 38. 2018/10/21For table I/O and Lock Wait相关show tables like '%table%'; +----------------------------------------+ | Tables_in_performance_schema (%table%) | +----------------------------------------+ | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | +----------------------------------------+ 3 rows in set (0.00 sec)
  • 39. 2018/10/21统计在表上锁的top 5 select object_schema,object_name,count_star,sum_timer_wait from table_lock_waits_summary_by_table order by count_star desc limit 5; +---------------+-------------+------------+----------------+ | object_schema | object_name | count_star | sum_timer_wait | +---------------+-------------+------------+----------------+ | sbtest | sbtest2 | 25994516 | 24486751926030 | | sbtest | sbtest1 | 25990180 | 24051565063080 | | sbtest | sbtest3 | 25980844 | 24401812260030 | | sbtest | sbtest4 | 25951200 | 24132248145765 | | ps_helper | t3 | 8 | 13488045 | +---------------+-------------+------------+----------------+
  • 40. 统计发生table lock消耗时间最高的表select OBJECT_NAME 'TABLE', SUM_TIMER_READ ReadTM, SUM_TIMER_WRITE WriteTM, SUM_TIMER_WAIT WaitTM from table_lock_waits_summary_by_table order by WaitTM desc limit 5; +---------+----------------+---------------+----------------+ | TABLE | ReadTM | WriteTM | WaitTM | +---------+----------------+---------------+----------------+ | sbtest2 | 19010384820015 | 6295964398125 | 25306349218140 | | sbtest3 | 18941771513115 | 6287191966275 | 25228963479390 | | sbtest4 | 18708376530030 | 6236878257210 | 24945254787240 | | sbtest1 | 18649886894175 | 6222007612560 | 24871894506735 | | t3 | 0 | 13488045 | 13488045 | +---------+----------------+---------------+----------------+ 5 rows in set (0.01 sec)2018/10/21
  • 41. table_io_waits_summary_by_table 相关 select object_name, count_star from table_io_waits_summary_by_table order by count_star desc limit 5; +-------------+------------+ | object_name | count_star | +-------------+------------+ | sbtest1 | 138902806 | | sbtest2 | 138838630 | | sbtest3 | 138800938 | | sbtest4 | 138638893 | | t3 | 4 | +-------------+------------+ 5 rows in set (0.01 sec)2018/10/21
  • 42. table_io_waits_summary_by_tableselect object_name, COUNT_STAR 'ALL', COUNT_READ 'Read', COUNT_WRITE 'Write', COUNT_FETCH 'Fetch', COUNT_INSERT 'Insert', COUNT_UPDATE 'Update', COUNT_DELETE 'DeLete' from table_io_waits_summary_by_table order by 2 desc limit 5; +-------------+-----------+-----------+---------+-----------+--------+---------+--------+ | object_name | ALL | Read | Write | Fetch | Insert | Update | DeLete | +-------------+-----------+-----------+---------+-----------+--------+---------+--------+ | sbtest1 | 139483816 | 136401303 | 3082513 | 136401303 | 770628 | 1541257 | 770628 | | sbtest2 | 139422240 | 136341095 | 3081145 | 136341095 | 770286 | 1540573 | 770286 | | sbtest3 | 139400772 | 136320090 | 3080682 | 136320090 | 770171 | 1540340 | 770171 | | sbtest4 | 139221894 | 136145170 | 3076724 | 136145170 | 769181 | 1538362 | 769181 | | t3 | 4 | 0 | 4 | 0 | 4 | 0 | 0 | +-------------+-----------+-----------+---------+-----------+--------+---------+--------+ 5 rows in set (0.01 sec)2018/10/21
  • 43. table_io_waits_summary_by_index_usage 相关select object_name, index_name, count_star from table_io_waits_summary_by_index_usage order by 3 desc limit 5; +-------------+------------+------------+ | object_name | index_name | count_star | +-------------+------------+------------+ | sbtest1 | PRIMARY | 139836868 | | sbtest2 | PRIMARY | 139788897 | | sbtest3 | PRIMARY | 139768021 | | sbtest4 | PRIMARY | 139566011 | | sbtest1 | NULL | 776869 | +-------------+------------+------------+ 5 rows in set (0.02 sec)2018/10/21
  • 44. STATEMENT 相关的表show tables like '%statement%'; +----------------------------------------------------+ | Tables_in_performance_schema (%statement%) | +----------------------------------------------------+ | events_statements_current | | events_statements_history | | events_statements_history_long | | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_program | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | +----------------------------------------------------+ 10 rows in set (0.00 sec) events_statements_summary_by_digest为相同类型的SQL的一个聚合 events_statements_current, events_statements_history, events_statements_history_long这三个表中记录了具体的SQL,而非聚合的结果。2018/10/21
  • 45. events_statements_summary_by_digest select LEFT(DIGEST_TEXT, 40) 'QUERY' ,digest "SQL MD5", SUM_TIMER_WAIT, COUNT_STAR from events_statements_summary_by_digest order by 3 desc limit 5; +------------------------------------------+----------------------------------+------------------+------------+ | QUERY | SQL MD5 | SUM_TIMER_WAIT | COUNT_STAR | +------------------------------------------+----------------------------------+------------------+------------+ | SELECT c FROM sbtest1 WHERE id = ? | 125fdbb0acedfb306b0d00bdf1d7731f | 2872295511000000 | 7929410 | | SELECT c FROM sbtest4 WHERE id = ? | 5b05326050a52c5a06277968cf8a5502 | 2761876049000000 | 7914298 | | SELECT c FROM sbtest2 WHERE id = ? | 3587b1bb2868c13297655edee733b96e | 2745128042000000 | 7926359 | | SELECT c FROM sbtest3 WHERE id = ? | dc1eb8a7c369e8cfd527ca328919c309 | 2705968173000000 | 7924812 | | SELECT DISTINCTROW c FROM sbtest1 WHERE | 65b685f605a2acf99f054bdfe773b2b3 | 996040945000000 | 793168 | +------------------------------------------+----------------------------------+------------------+------------+ 5 rows in set (0.01 sec) 2018/10/21
  • 46. events_statements_summary_by_digest select * from events_statements_summary_by_digest where DIGEST = '125fdbb0acedfb306b0d00bdf1d7731f' \G *************************** 1. row *************************** SCHEMA_NAME: sbtest DIGEST: 125fdbb0acedfb306b0d00bdf1d7731f DIGEST_TEXT: SELECT c FROM sbtest1 WHERE id = ? COUNT_STAR: 7956690 SUM_TIMER_WAIT: 2879882788000000 MIN_TIMER_WAIT: 56000000 AVG_TIMER_WAIT: 361944000 MAX_TIMER_WAIT: 2283464000000 SUM_LOCK_TIME: 467653283000000 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 7958257 SUM_ROWS_EXAMINED: 7958474 SUM_CREATED_TMP_DISK_TABLES: 0 SUM_CREATED_TMP_TABLES: 0 SUM_SELECT_FULL_JOIN: 0 SUM_SELECT_FULL_RANGE_JOIN: 0 SUM_SELECT_RANGE: 0 SUM_SELECT_RANGE_CHECK: 0 SUM_SELECT_SCAN: 0 SUM_SORT_MERGE_PASSES: 0 SUM_SORT_RANGE: 0 SUM_SORT_ROWS: 0 SUM_SORT_SCAN: 0 SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2013-10-18 17:43:09 LAST_SEEN: 2013-10-19 09:48:31 1 row in set (0.01 sec)2018/10/21当记录超过performance_schema_digests_size大小时,SCHEMA_NAME,DIGEST,DIGEST_TEXT会为NULL,当counter明显很高时,这种情况是需要调整适时调整performance_schema_digests_size大小DIEGEST_TEXT列的长度为1024,超过了就以字符串“…”代替
  • 47. event show tables like '%event_wait%'; event_waits_current event_waits_history event_waits_history_long event_waits_summary_by_event_name
  • 48. TOP event
  • 49. TOP event
  • 50. Performance schema扩展 ps_helper(A great tool by Mark Leith) http://www.markleith.co.uk/ps_helper 一系列的视图和存储过程,把information_Schema中的部分表和performance schema中的关联表起来了 安装: git clone https://github.com/MarkLeith/dbahelper.git dbahelper cd dbahelper mysql -u user -p < ps_helper_.sql 目前有for 5.5,5.6,5.7 ps_tool(Jesper Krogh) http://mysql.wisborg.dk/ 2018/10/21
  • 51. ps_helper存储过程函数CALL disable_current_thread(); CALL enable_current_thread(); call disable_background_threads(); call enable_background_threads(); call currently_enabled(TRUE, TRUE); call only_enable('%'); call only_enable(NULL); call only_enable('wait/io/%'); format_statement() format_path() format_time() format_bytes()
  • 52. 慢查询诊断 Performance Schema – events_statements_summary_by_digest ps_helper – statement_analysis – statements_with_full_table_scans – statements_with_runtimes_in_95th_percentile – statements_with_sorting – statements_with_temp_tables2018/10/21
  • 53. statement_analysisselect * from statement_analysis limit 3; +-------------------------------------------------------------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+--------------+------------+-----------------+-------------+-------------------+----------------------------------+ | query | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | lock_latency | rows_sent | rows_sent_avg | rows_scanned | tmp_tables | tmp_disk_tables | rows_sorted | sort_merge_passes | digest | +-------------------------------------------------------------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+--------------+------------+-----------------+-------------+-------------------+----------------------------------+ | SELECT `t` . * , `tt` . * , `t ... ..) ORDER BY `t` . `name` ASC | | 21679 | 0 | 0 | 43.86 s | 146.05 ms | 2.02 ms | 3.31 s | 274475 | 13 | 1372375 | 21679 | 21679 | 274475 | 0 | 8b68304a8eb97c2d076cff62a21167d2 | | SELECT SQL_CALC_FOUND_ROWS `wp ... `post_date` DESC LIMIT ?, ... | | 4810 | 0 | 0 | 41.85 s | 22.71 ms | 8.70 ms | 774.85 ms | 46201 | 10 | 5387435 | 0 | 0 | 0 | 0 | 490fa20bc36de6c43dca95aeef7d83d0 | | SELECT `option_name` , `option ... options` WHERE `autoload` = ? | * | 19968 | 0 | 0 | 34.50 s | 38.19 ms | 1.73 ms | 2.25 s | 5111808 | 256 | 5910528 | 0 | 0 | 0 | 0 | b7a64987e6573a883cf0fa3fb17e5739 | +-------------------------------------------------------------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+--------------+------------+-----------------+-------------+-------------------+----------------------------------+ 3 rows in set (0.00 sec)
  • 54. 全表扫描top 10SELECT * FROM statements_with_full_table_scans LIMIT 10; +-------------------------------------------------------------------+------------+---------------------+--------------------------+-------------------+-----------+---------------+----------------------------------+ | query | exec_count | no_index_used_count | no_good_index_used_count | no_index_used_pct | rows_sent | rows_examined | digest | +-------------------------------------------------------------------+------------+---------------------+--------------------------+-------------------+-----------+---------------+----------------------------------+ | SELECT `option_name` , `option ... options` WHERE `autoload` = ? | 17085 | 17085 | 0 | 100 | 4373760 | 5057160 | b7a64987e6573a883cf0fa3fb17e5739 | | SELECT fid , tid , SUBJECT , a ... DESC , dateline DESC LIMIT ? | 402 | 402 | 0 | 100 | 0 | 5095089 | 1447705fdb9e6c28e29dfb5a428585f2 | | SELECT f . fid , f . fup , f . ... Y f . type , f . displayorder | 400 | 400 | 0 | 100 | 52000 | 158400 | 709247de7b7f78df14308fa07a5dd7ec | | SELECT styleid , NAME FROM cdb_styles WHERE available = ? | 241 | 241 | 0 | 100 | 241 | 3133 | ddf7bbd08d6ca8f98f42fa73609e13cd | | SHOW TABLES LIKE ? | 51 | 51 | 0 | 100 | 51 | 51 | aac8cc51f1ff864d4b6d4493fc588cce | | SELECT uid FROM cdb_memberfields mf WHERE nickname = ? | 30 | 30 | 0 | 100 | 315010 | 429244 | b57defaff24a4a1a31178cacff0a50f6 | | SELECT m . * , mf . * , u . gr ... r . postshigher DESC LIMIT ? | 30 | 30 | 0 | 100 | 30 | 244 | 4420b303e7e7b549f46126bbfd1d5b52 | | SELECT nextrun FROM cdb_crons ... > ? ORDER BY nextrun LIMIT ? | 24 | 24 | 0 | 100 | 24 | 336 | 890a5fe0b530690038dd4bf59b386003 | +-------------------------------------------------------------------+------------+---------------------+--------------------------+-------------------+-----------+---------------+----------------------------------+ 10 rows in set (0.01 sec)2018/10/21
  • 55. 查看具体sql SCHEMA_NAME: uedcwp DIGEST: b7a64987e6573a883cf0fa3fb17e5739 DIGEST_TEXT: SELECT `option_name` , `option_value` FROM `wp_options` WHERE `autoload` = ? COUNT_STAR: 17118 SUM_TIMER_WAIT: 29704802064000 MIN_TIMER_WAIT: 875949000 AVG_TIMER_WAIT: 1735296000 MAX_TIMER_WAIT: 38191066000 SUM_LOCK_TIME: 1922030000000 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 4382208 SUM_ROWS_EXAMINED: 5066928 SUM_CREATED_TMP_DISK_TABLES: 0 SUM_CREATED_TMP_TABLES: 0 SUM_SELECT_FULL_JOIN: 0 SUM_SELECT_FULL_RANGE_JOIN: 0 SUM_SELECT_RANGE: 0 SUM_SELECT_RANGE_CHECK: 0 SUM_SELECT_SCAN: 17118 SUM_SORT_MERGE_PASSES: 0 SUM_SORT_RANGE: 0 SUM_SORT_ROWS: 0 SUM_SORT_SCAN: 0 SUM_NO_INDEX_USED: 17118 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2013-10-15 22:04:44 LAST_SEEN: 2013-10-17 22:41:46 1 row in set (0.01 sec)SELECT * FROM performance_schema.events_statements_summary_by_digest where DIGEST='b7a64987e6573a883cf0fa3fb17e5739'\G;*************************** 1. row ***************************
  • 56. 查看具体执行计划 mysql> SELECT CURRENT_SCHEMA,SQL_TEXT FROM performance_schema.events_statements_history_long where DIGEST='b7a64987e6573a883cf0fa3fb17e5739' limit 1; +----------------+-------------------------------------------------------------------------+ | CURRENT_SCHEMA | SQL_TEXT | +----------------+-------------------------------------------------------------------------+ | uedcwp | SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes' | +----------------+-------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT option_name, option_value FROM uedcwp.wp_options WHERE autoload = 'yes'; +----+-------------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | wp_options | ALL | NULL | NULL | NULL | NULL | 296 | Using where | +----+-------------+------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) 2018/10/21
  • 57. 排序top 10 SELECT query, exec_count, avg_sort_merges, sorts_using_scans, sort_using_range, rows_sorted, avg_rows_sorted ,digest FROM ps_helper.statements_with_sorting LIMIT 10; | query | exec_count | avg_sort_merges | sorts_using_scans | sort_using_range | rows_sorted | avg_rows_sorted | digest | +-------------------------------------------------------------------+------------+-----------------+-------------------+------------------+-------------+-----------------+----------------------------------+ | SELECT * FROM `wp_posts` WHERE ... ? ) ORDER BY `menu_order` ASC | 8415 | 1 | 0 | 8415 | 25245 | 3 | f4661a1918600f1785d98bb2c4bd71c1 | | SELECT * FROM `wp_links` INNER ... = ? ORDER BY `link_name` ASC | 8412 | 1 | 0 | 8412 | 126180 | 15 | 9b5a10d47cd3df7f9e2b4107e8e353f1 | | SELECT * FROM `wp_comments` WH ... DER BY `comment_date_gmt` ASC | 5791 | 1 | 0 | 5791 | 69920 | 12 | 2b1abb8bc1ca6402235dfe301ae3161b | | SELECT SQL_CALC_FOUND_ROWS `wp ... ment_count` DESC LIMIT ?, ... | 5596 | 1 | 0 | 5596 | 33576 | 6 | 572d54dfe460bb8e0cad54c488b81283 | | SELECT t . * FROM cdb_threads ... . dateline DESC LIMIT ?, ... | 2869 | 1 | 0 | 2869 | 143515 | 50 | de2abb7c22ed0d0d6dbdff354a3e92d8 | | SELECT SQL_CALC_FOUND_ROWS `wp ... ment_count` DESC LIMIT ?, ... | 2462 | 1 | 0 | 2462 | 487476 | 198 | 1a4069d51d8d1243cb765d25e6ad9f43 | | SELECT `wp_posts` . * FROM `wp ... `wp_posts` . `post_date` DESC | 1885 | 1 | 0 | 1885 | 1831 | 1 | 04231b8825e34962753219f0f49cfc48 | | SELECT t . tid , t . readperm ... BY t . dateline DESC LIMIT ? | 1870 | 1 | 0 | 1870 | 211339 | 113 | 7c315771ea2972be02e79083722092ad | | SELECT `post_modified_gmt` FRO ... st_modified_gmt` DESC LIMIT ? | 1156 | 1 | 0 | 1156 | 1156 | 1 | 4b04513ae90f75762efdde8e92f2f9f3 | | SELECT `post_date_gmt` FROM `w ... `post_date_gmt` DESC LIMIT ? | 1156 | 1 | 0 | 1156 | 1156 | 1 | ea3d1d4396bd80f2ee8c0cf2515a270f | +-------------------------------------------------------------------+------------+-----------------+-------------------+------------------+-------------+-----------------+----------------------------------+ 10 rows in set (0.00 sec)2018/10/21
  • 58. 临时表top 10SELECT query, exec_count, memory_tmp_tables, disk_tmp_tables,digest FROM ps_helper.statements_with_temp_tables LIMIT 10; +-------------------------------------------------------------------+------------+-------------------+-----------------+----------------------------------+ | query | exec_count | memory_tmp_tables | disk_tmp_tables | digest | +-------------------------------------------------------------------+------------+-------------------+-----------------+----------------------------------+ | SELECT `t` . * , `tt` . * , `t ... ..) ORDER BY `t` . `name` ASC | 18937 | 18937 | 18937 | 8b68304a8eb97c2d076cff62a21167d2 | | SELECT `t` . * , `tt` . * , `t ... (?) ORDER BY `t` . `name` ASC | 10987 | 10987 | 10987 | d371d08b300a49a19a729097ad52b33c | | SELECT `t` . * , `tt` . * FROM ... (?) ORDER BY `t` . `name` ASC | 8417 | 8417 | 8417 | 6a5d0b3d0654e7ada3367b2b17231c0d | | SELECT `t` . * , `tt` . * FROM ... > ? ORDER BY `t` . `name` ASC | 8416 | 8416 | 8416 | fafcb9b5afbf26e938294ad68cca0ff5 | | SELECT `t` . * , `tt` . * FROM ... (?) ORDER BY `t` . `name` ASC | 4110 | 4110 | 4110 | b6871c5ed97d846362682632b3792f20 | | SELECT SQL_CALC_FOUND_ROWS `wp ... `post_date` DESC LIMIT ?, ... | 827 | 826 | 826 | 62b646ba35fdf893056bf629c1828867 | | SELECT SQL_CALC_FOUND_ROWS `wp ... `post_date` DESC LIMIT ?, ... | 138 | 138 | 138 | cac4a5ae9402bc5437e0742eff77f792 | | SELECT `members` . `id` AS `t0 ... TER JOIN `member_roles` ON ... | 6 | 6 | 6 | 4d5ba1606826a06f80d3a4286378cdf1 | | SELECT `information_schema` . ... TICS` . `INDEX_TYPE` UNION ... | 1 | 7 | 3 | ae52e0db739083c9787386574a466db2 | | CREATE SQL SECURITY INVOKER VI ... ORDER BY `DB` , `OBJECT_TYPE` | 1 | 6 | 3 | 6d3eb1ffaec182e7a547620e488dc1e6 | +-------------------------------------------------------------------+------------+-------------------+-----------------+----------------------------------+ 10 rows in set (0.01 sec)
  • 59. 磁盘io相关latest_file_io io_by_thread_by_latency io_global_by_file_by_bytes io_global_by_file_by_latency io_global_by_wait_by_bytes io_global_by_wait_by_latency2018/10/21
  • 60. 列举最近的10次 ioselect * from latest_file_io limit 10; +---------------------------------------+---------------------------------+-----------+-----------+-----------+ | thread | file | latency | operation | requested | +---------------------------------------+---------------------------------+-----------+-----------+-----------+ | repl@hz0-12-106.inter.163.com:63681:4 | /data/mysql_56/mysql-bin.000027 | 2.54 us | read | 309 bytes | | repl@hz0-12-106.inter.163.com:63681:4 | /data/mysql_56/mysql-bin.000027 | 780.11 ns | read | 0 bytes | | repl@hz0-12-106.inter.163.com:63681:4 | /data/mysql_56/mysql-bin.000027 | 891.55 ns | read | 0 bytes | | repl@hz0-12-106.inter.163.com:63681:4 | /data/mysql_56/mysql-bin.000027 | 2.94 us | read | 313 bytes | | repl@hz0-12-106.inter.163.com:63681:4 | /data/mysql_56/mysql-bin.000027 | 704.81 ns | read | 0 bytes | | repl@hz0-12-106.inter.163.com:63681:4 | /data/mysql_56/mysql-bin.000027 | 870.47 ns | read | 0 bytes | +---------------------------------------+---------------------------------+-----------+-----------+-----------+ 6 rows in set (0.08 sec)2018/10/21
  • 61. 查询 io top 10文件SELECT * FROM io_global_by_file_by_bytes LIMIT 10; +-----------------------------------------------+------------+------------+------------+-------------+---------------+-----------+------------+-----------+ | file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct | +-----------------------------------------------+------------+------------+------------+-------------+---------------+-----------+------------+-----------+ | /data/mysql_56/uedcwp/wp_posts.MYD | 10561338 | 53.77 GiB | 5.34 KiB | 0 | 0 bytes | 0 bytes | 53.77 GiB | 0.00 | | /data/mysql_56/uedcwp/wp_options.MYD | 18994 | 9.28 GiB | 512.06 KiB | 552 | 80.73 KiB | 150 bytes | 9.28 GiB | 0.00 | | /data/mysql_56/discuz/cdb_threads.MYD | 2259129 | 3.54 GiB | 1.65 KiB | 6037 | 2.87 MiB | 498 bytes | 3.55 GiB | 0.08 | | /data/mysql_56/loganalyser/chinanetcenter.MYD | 16711200 | 2.40 GiB | 154 bytes | 27360 | 4.02 MiB | 154 bytes | 2.40 GiB | 0.16 | | /data/mysql_56/loganalyser/chinacache.MYD | 193046 | 712.29 MiB | 3.78 KiB | 2880 | 433.13 KiB | 154 bytes | 712.71 MiB | 0.06 | | /data/mysql_56/uedcwp/wp_postmeta.MYD | 14026967 | 281.10 MiB | 21 bytes | 6730 | 233.26 KiB | 35 bytes | 281.33 MiB | 0.08 | | /data/mysql_56/uedcwp/wp_usermeta.MYD | 6802839 | 200.16 MiB | 31 bytes | 0 | 0 bytes | 0 bytes | 200.16 MiB | 0.00 | | /data/mysql_56/discuz/cdb_posts.MYD | 223933 | 139.22 MiB | 652 bytes | 127 | 96.10 KiB | 775 bytes | 139.31 MiB | 0.07 | | /data/mysql_56/mysql-bin.000027 | 359963 | 63.46 MiB | 185 bytes | 120937 | 63.44 MiB | 550 bytes | 126.90 MiB | 49.99 | | /data/mysql_56/discuz/cdb_activityapplies.MYD | 77760 | 93.73 MiB | 1.23 KiB | 110 | 134.55 KiB | 1.22 KiB | 93.87 MiB | 0.14 | +-----------------------------------------------+------------+------------+------------+-------------+---------------+-----------+------------+-----------+ 10 rows in set (0.00 sec)2018/10/21
  • 62. 查询发生io最高的10次等待事件 SELECT event_name,count_star,total_latency,total_read,total_written, total_requested FROM io_global_by_wait_by_bytes LIMIT 10; +-------------------------+------------+---------------+------------+---------------+-----------------+ | event_name | count_star | total_latency | total_read | total_written | total_requested | +-------------------------+------------+---------------+------------+---------------+-----------------+ | myisam/dfile | 73923032 | 00:01:57.91 | 71.44 GiB | 565.22 MiB | 71.99 GiB | | sql/binlog | 481418 | 8.86 s | 65.48 MiB | 63.45 MiB | 128.93 MiB | | myisam/kfile | 1341306 | 13.15 s | 18.84 MiB | 38.25 MiB | 57.09 MiB | | sql/relaylog | 146668 | 2.16 s | 18.45 MiB | 18.45 MiB | 36.90 MiB | | innodb/innodb_data_file | 2183 | 3.05 s | 11.17 MiB | 15.22 MiB | 26.39 MiB | | sql/slow_log | 23436 | 2.28 s | 0 bytes | 7.20 MiB | 7.20 MiB | | sql/FRM | 3615 | 18.65 ms | 616.18 KiB | 0 bytes | 616.18 KiB | | sql/file_parser | 424 | 926.78 ms | 0 bytes | 171.80 KiB | 171.80 KiB | | innodb/innodb_log_file | 218 | 2.47 s | 68.00 KiB | 96.00 KiB | 164.00 KiB | | sql/ERRMSG | 5 | 130.65 us | 56.17 KiB | 0 bytes | 56.17 KiB | +-------------------------+------------+---------------+------------+---------------+-----------------+ 10 rows in set (0.00 sec)2018/10/21
  • 63. Schema相关schema_object_overview schema_index_statistics schema_table_statistics schema_table_statistics_with_buffer schema_tables_with_full_table_scans schema_unused_indexes 2018/10/21
  • 64. Schema相关了解内存中最热的表的情况 了解最热索引的情况 了解相关内存中载入的表的情况 找出没有使用的索引的表2018/10/21
  • 65. 统计表的使用情况select * from schema_object_overview limit 10; +------------+------------------+-------+ | db | object_type | count | +------------+------------------+-------+ | 17wiki | BASE TABLE | 49 | | 17wiki | INDEX (BTREE) | 220 | | 17wiki | INDEX (FULLTEXT) | 2 | | adm_addon | BASE TABLE | 1 | | adm_addon | INDEX (BTREE) | 1 | | ad_fld | BASE TABLE | 3 | | ad_fld | INDEX (BTREE) | 3 | | bugtracker | BASE TABLE | 31 | | bugtracker | INDEX (BTREE) | 74 | | chyrp | BASE TABLE | 8 | +------------+------------------+-------+ 10 rows in set (0.06 sec) SELECT * FROM schema_tables_with_full_table_scans limit 10; +---------------+------------------+-------------------+ | object_schema | object_name | rows_full_scanned | +---------------+------------------+-------------------+ | discuz | cdb_threads | 5384934 | | uedcwp | wp_options | 5145525 | | loganalyser | chinacache | 4656961 | | uedcwp | wp_posts | 1204163 | | discuz | cdb_memberfields | 457891 | | discuz | cdb_sessions | 324761 | | uedcwp | wp_links | 127140 | | discuz | cdb_stats | 74541 | | uedcwp | wp_comments | 71974 | | discuz | cdb_forums | 56855 | +---------------+------------------+-------------------+ 10 rows in set (0.01 sec) 2018/10/21
  • 66. 统计最热索引的使用情况 SELECT * FROM schema_index_statistics LIMIT 10; +--------------+----------------+------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+ | table_schema | table_name | index_name | rows_selected | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency | +--------------+----------------+------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+ | uedcwp | wp_posts | type_status_date | 6331887 | 57.86 s | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | uedcwp | wp_postmeta | post_id | 8721798 | 29.27 s | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | loganalyser | chinanetcenter | name | 16711219 | 28.23 s | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | uedcwp | wp_usermeta | user_id | 2320653 | 7.80 s | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | uedcwp | wp_usermeta | meta_key | 1226553 | 5.38 s | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | discuz | cdb_threads | typeid | 3099261 | 4.05 s | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | uedcwp | wp_options | option_name | 164089 | 2.21 s | 0 | 0 ps | 507 | 28.70 ms | 0 | 0 ps | | uedcwp | wp_posts | PRIMARY | 204080 | 2.11 s | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | uedcwp | wp_users | PRIMARY | 183799 | 2.00 s | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | | uedcwp | wp_terms | PRIMARY | 294929 | 1.81 s | 0 | 0 ps | 0 | 0 ps | 0 | 0 ps | +--------------+----------------+------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+ 10 rows in set (0.01 sec)
  • 67. 统计没有使用的索引select * from schema_unused_indexes where object_schema='uedcwp'; +---------------+------------------------+----------------+ | object_schema | object_name | index_name | +---------------+------------------------+----------------+ | uedcwp | wp_commentmeta | comment_id | | uedcwp | wp_commentmeta | PRIMARY | | uedcwp | wp_comments | comment_parent | | uedcwp | wp_links | PRIMARY | | uedcwp | wp_options | PRIMARY | | uedcwp | wp_postmeta | PRIMARY | | uedcwp | wp_posts | post_author | | uedcwp | wp_redirection_groups | module_id | | uedcwp | wp_redirection_groups | status | | uedcwp | wp_redirection_items | url | | uedcwp | wp_redirection_items | regex | | uedcwp | wp_redirection_items | status | | uedcwp | wp_redirection_modules | PRIMARY | | uedcwp | wp_terms | name | | uedcwp | wp_usermeta | PRIMARY | | uedcwp | wp_users | user_nicename | +---------------+------------------------+----------------+ 16 rows in set (0.01 sec)2018/10/21
  • 68. 查看BP中表情况 SELECT * FROM schema_table_statistics_with_buffer LIMIT 2\G; *************************** 1. row *************************** table_schema: sbtest table_name: sbtest1 rows_fetched: 326998193 fetch_latency: 1.68h rows_inserted: 1230019 insert_latency: 00:01:21.11 rows_updated: 2460038 update_latency: 00:02:32.16 rows_deleted: 1230019 delete_latency: 50.20 s io_read_requests: 7 io_read: 961 bytes io_read_latency: 25.89 us io_write_requests: 0 io_write: 0 bytes io_write_latency: 0 ps io_misc_requests: 8 io_misc_latency: 173.79 us innodb_buffer_allocated: 933.45 MiB innodb_buffer_data: 806.46 MiB innodb_buffer_pages: 59741 innodb_buffer_pages_hashed: 59741 innodb_buffer_pages_old: 59741 innodb_buffer_rows_cached: 68171762018/10/21
  • 69. performance schema加强in mysql 5.7 memory 信息的汇总 replication 信息的存储
  • 70. performance schema加强 show tables like '%memory%'; +-----------------------------------------+ | Tables_in_performance_schema (%memory%) | +-----------------------------------------+ | memory_summary_by_account_by_event_name | | memory_summary_by_host_by_event_name | | memory_summary_by_thread_by_event_name | | memory_summary_by_user_by_event_name | | memory_summary_global_by_event_name | +-----------------------------------------+ 5 rows in set (0.00 sec) show tables like '%repli%'; +-------------------------------------------+ | Tables_in_performance_schema (%repli%) | +-------------------------------------------+ | replication_connection_configuration | | replication_connection_status | | replication_execute_configuration | | replication_execute_status | | replication_execute_status_by_coordinator | | replication_execute_status_by_worker | +-------------------------------------------+ 6 rows in set (0.00 sec)
  • 71. 2018/10/21 Q & A