1、MySQL 性能調(diào)優(yōu)介紹2013.10.18DBA 王洪權(quán)新浪微博 foreverreturn內(nèi)容概要 一性能分析介紹（簡(jiǎn)單介紹下orazdba,oprofile,ioprofile） 二performance schema介紹工具化的啟發(fā)工具化的重要性（熟練運(yùn)用已有的工具，可以快速對(duì)數(shù)據(jù)庫(kù)進(jìn)行診斷，時(shí)刻了解你的數(shù)據(jù)庫(kù)的運(yùn)行狀況）2018/9/30性能分析MySQL相關(guān)：show session/global status like %variables% ;show full processlist;show engine innodb mutex; SH

2、OW ENGINE INNODB STATUS; SHOW PROFILE;MySQL slow query性能分析MySQL之外orzdba (時(shí)刻監(jiān)控innodb的各項(xiàng)指標(biāo))pt-query-digest(定位慢查詢消耗，進(jìn)行相關(guān)sql優(yōu)化)oprofile(定位cpu消耗)perf top (定位mysql 內(nèi)部的熱點(diǎn))tcpdump + pt-query-digest（捕捉異常時(shí)刻sql） ioprofile (定位系統(tǒng)的寫入和相關(guān)fsync調(diào)用) pstack (定位異常時(shí)刻系統(tǒng)的瓶頸)pt-pmp( 對(duì) pstack 擴(kuò) 展 ) Blktrace+btt（定位io進(jìn)入到塊層的情況）

3、 relay-fetch(加速備庫(kù)預(yù)熱，解決主從延時(shí))性能分析其他mytopinnotop mysqltuner.pldba-slow-picker.pl（診斷異常時(shí)刻SQL).mysqltuner.pl 給mysql做個(gè)簡(jiǎn)單報(bào)告2018/9/30mysqltuner.pl 給mysql做個(gè)簡(jiǎn)單報(bào)告2018/9/30orzdba 時(shí)刻了解你的數(shù)據(jù)庫(kù)2018/9/30orzdba 時(shí)刻了解你的數(shù)據(jù)庫(kù)oprofile 介紹= oprofile 是什么=oprofile也是一個(gè)開源的profiling工具，它使用硬件調(diào)試寄存器來(lái)統(tǒng)計(jì)信息，進(jìn)行profiling的開銷比較小，而且可以對(duì)內(nèi)核進(jìn)行prof

4、iling。Oprofile 是一個(gè)全局的抽樣統(tǒng)計(jì)工具Oprofile是一種細(xì)粒度的工具，可以為指令集或者 為函數(shù)、系統(tǒng)調(diào)用或中斷處理例程收集采樣。Oprofile 通過(guò)取樣來(lái)工作。使用收集到的評(píng)測(cè)數(shù)據(jù)，用戶可以很容易地找出性能問(wèn)題。2018/9/30Oprofile系統(tǒng)工作流圖2018/9/30oprofile 使用場(chǎng)景cpu無(wú)端占用高？應(yīng)用程序響應(yīng)慢？苦于沒(méi)有分析的工具？oprofile利用cpu硬件層面提供的性能計(jì)數(shù)器(performance counter)，通過(guò)計(jì)數(shù)采樣，幫助我們從進(jìn)程、函數(shù)、代碼層面找出占用cpu的罪魁禍?zhǔn)住?018/9/30oprofile使用# 加載oprofi

5、le內(nèi)核模塊opcontrol -init#我們對(duì)內(nèi)核的取樣沒(méi)興趣opcontrol -setup -no-vmlinux#在開始收集采樣數(shù)據(jù)前回顧下我們的設(shè)置opcontrol -status#清除上一次采樣到的數(shù)據(jù)opcontrol -reset#啟動(dòng)oprofiled守護(hù)程序,從內(nèi)核中拉出采樣數(shù)據(jù)opcontrol -start#運(yùn)行我們的程序#收集采樣數(shù)據(jù)opcontrol -dump#關(guān)閉守護(hù)程序, 同時(shí)準(zhǔn)備好采樣的數(shù)據(jù)opcontrol -shutdown2018/9/30oprofile獲取采樣信息#系統(tǒng)級(jí)別的opreport -long-filenames #模塊級(jí)別的opre

6、port image:foo -l# 源 碼 級(jí) 別 的 opannotate image:foo -s2018/9/30oprofile使用opcontrol -deinitmodprobe oprofile timer=1$dmesg|grep oprofile|tail -n 1 (oprofile: using timer interrupt.) opcontrol -resetpcontrol -separate=lib -no-vmlinux-start -image=/home/mysql_user/mysqlhome/bin/mysqld opcontrol -dumpopco

7、ntrol -shutdownopreport -l/home/mysql_user/mysqlhome/bin/mysqld2018/9/30oprofile診斷你的cpu異常2018/9/30ioprofile 查看IO情況的利器2018/9/30ioprofile 查看IO情況的利器2018/9/30ioprofile 透過(guò)進(jìn)程發(fā)現(xiàn)寫入狀況2018/9/30ioprofile 產(chǎn)看IO情況的利器2018/9/30ioprofile 產(chǎn)看IO情況的利器2018/9/30PERFORMANCE_SCHEMA簡(jiǎn)介Performance Schema的功能，類似于Oracle數(shù)據(jù)庫(kù)提供的豐富的系

8、統(tǒng)表，用于將數(shù)據(jù)庫(kù)內(nèi)部的運(yùn)行情況展示出來(lái)。包括：Mutex，RWLock，IO等等。performance schema 可以使得DBA 更加容易的定位系統(tǒng)瓶頸performance schema也使得mysql的性能更加可測(cè)量化Performance schema介紹 MySQL 5.5 File I/O, Mutexes, RW Locks etc MySQL 5.6 File I/OTable I/O Query Digest Statement Table locks MySQL 5.7 memory,replication2018/9/30MySQL 5.6 PSMySQL 5.6

9、默認(rèn)開啟了performance_schema，可以動(dòng)態(tài)的禁用.File I/O Table I/O Query Digest Statement (查看最熱的文件和)（查看最熱的表的情況）(定位熱點(diǎn)查詢和表）（定位熱點(diǎn)查詢和表）Table locks （定位熱點(diǎn)表鎖）Performance_schema 配置fperformance_schema_instrument = %=on performance_schema_consumer_events_stages_current = ON performance_schema_consumer_events_stages_hist

10、ory = 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

11、= 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默認(rèn)啟動(dòng)開啟了performance_s

12、chema,可以通過(guò)DML 動(dòng)態(tài)啟用相關(guān)的instrument和comsumerUPDATE performance_schema.setup_instruments SET ENABLED = YES, TIMED = YES;UPDATE performance_schema.setup_consumers SET ENABLED = YES;2018/9/30查看performance_schema變量SHOW GLOBAL VARIABLES LIKE performance_schema%;+ + +| Variable_name | Value |+ + +| performanc

13、e_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_eve

14、nts_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_fi

15、le_classes | 50 | performance_schema_max_file_handles | 32768 | performance_schema_max_file_instances | 7856 |+ + +2018/9/30查看performance_schema占用內(nèi)存大小SHOW ENGINE PERFORMANCE_SCHEMA STATUS;+ + + +| Type | Name | Status |+ + + +| performance_schema | events_waits_current.size | 184 | performance_schem

16、a | 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 | even

17、ts_waits_history_long.count | 10000 | performance_schema | events_waits_history_long.memory | 1840000 | performance_schema | performance_schema.memory | 700101608 |+ + + + |performance_schema.memory 代表占用內(nèi)存大小700M左右2018/9/30performance_schema 狀態(tài)變量SHOW STATUS LIKE %perf%;+ + +| Variable_name | Value |+

18、 + +| 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 |

19、 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 | Perfor

20、mance_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_stateme

21、nt_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 |顯示由于內(nèi)存限制導(dǎo)致某些統(tǒng)計(jì)信息沒(méi)有計(jì)入PS中+ + +26 rows in set (0.00 sec) Perform

22、ance 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

23、in set (0.00 sec)默認(rèn)情況下，監(jiān)控的表對(duì)象排除mysql/PS/IS庫(kù)的對(duì)象，其中IS庫(kù)下的表，不管是否開啟，都不會(huì)去監(jiān)控2018/9/30Threads表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 |

24、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 |

25、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 |

26、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_wri

27、te_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_

28、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

29、| 31 | thread/sql/one_connection | FOREGROUND | YES | 32 | thread/sql/one_connection | FOREGROUND | YES |+ + + + +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

30、 | 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_L

31、OG: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

32、 name like %mutex%; Query OK, 0 rows affected (0.00 sec)Rows matched: 156 Changed: 0 Warnings: 02018/9/30instrumentinstrument樹形結(jié)構(gòu)，主要包括idle/wait/stage/statement，下層包括例如sync,io，再往下層可能為以劃分成mutex/cond/rwlock，最后為具體對(duì)象或者模塊。wait/synch/mutex/innodb/trx_mutexidel socket的空閑信息，記錄在socket_instances表中stage 命名規(guī)則為sta

33、ge/code_area/stage_name code_area值為sql/mysys， stage_name表示執(zhí)行語(yǔ)句過(guò)程中的各個(gè)階段stage 命名規(guī)則為statement/sql或者com sql下為具體的sql類型，com下為服務(wù)器的一些命令相關(guān)）wait 最為關(guān)注的部分，包括文件的操作時(shí)間，socket，表的io的時(shí)間統(tǒng)計(jì)，主要包含wait/io，wait/lock，wait/synch（mutex(wait/synch/mutex)）、讀寫鎖(wait/synch/rwlock）consumerselect * from setup_consumers;+ + +| NAME

34、| 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 | N

35、O | global_instrumentation | YES | thread_instrumentation | YES | statements_digest | YES |其中高級(jí)別的consumer決定是否去檢查低級(jí)別的consumer+ + +2018/9/30For filesshow tables like %file%;+ +| Tables_in_performance_schema (%file%) |+ +| file_instances | file_summary_by_event_name | file_summary_by_instance |+ +3 row

36、s in set (0.00 sec)2018/9/30查詢讀寫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/

37、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)20

38、18/9/30查詢讀寫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.000

39、043 | 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)2018/9/30For table

40、I/O and Lock Wait相關(guān)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)2018/9/30統(tǒng)計(jì)在表上鎖的top 5select object_schema,object_name,count_star,sum_t

41、imer_waitfrom 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 | s

42、btest | sbtest4 | 25951200 | 24132248145765 | ps_helper | t3 | 8 | 13488045 |+ + + +2018/9/30統(tǒng)計(jì)發(fā)生table lock消耗時(shí)間最高的表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 | Re

43、adTM | WriteTM | WaitTM |+ + + + +| sbtest2 | 19010384820015 | 6295964398125 | 25306349218140 | sbtest3 | 18941771513115 | 6287191966275 | 25228963479390 | sbtest4 | 18708376530030 | 6236878257210 | 24945254787240 | sbtest1 | 18649886894175 | 6222007612560 | 24871894506735 | t3 | 0 | 13488045 | 1348

44、8045 |+ + + + +5 rows in set (0.01 sec)2018/9/30table_io_waits_summary_by_table 相關(guān)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 | 1

45、38638893 | t3 | 4 |+ + +5 rows in set (0.01 sec)2018/9/30table_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 |