MySQL性能调优介绍.ppt

MySQL性能调优介绍,2013.10.18DBA王洪权mydbalife新浪微博foreverreturn,内容概要,一性能分析介绍（简单介绍下orazdba,oprofile,ioprofile）二performanceschema介绍,2019/11/19,工具化的启发,工具化的重要性（熟练运用已有的工具，可以快速对数据库进行诊断，时刻了解你的数据库的运行状况）,性能分析,MySQL相关：showsession/globalstatuslike%variables%;showfullprocesslist;showengineinnodbmutex;SHOWENGINEINNODBSTATUS;SHOWPROFILE;MySQLslowquery,性能分析,MySQL之外orzdba(时刻监控innodb的各项指标)pt-query-digest(定位慢查询消耗，进行相关sql优化)oprofile(定位cpu消耗)perftop(定位mysql内部的热点)tcpdump+pt-query-digest（捕捉异常时刻sql）ioprofile(定位系统的写入和相关fsync调用)pstack(定位异常时刻系统的瓶颈)pt-pmp(对pstack扩展)Blktrace+btt（定位io进入到块层的情况）relay-fetch(加速备库预热，解决主从延时),性能分析,其他mytopinnotopmysqltuner.pldba-slow-picker.pl（诊断异常时刻SQL).,2019/11/19,mysqltuner.pl给mysql做个简单报告,2019/11/19,mysqltuner.pl给mysql做个简单报告,2019/11/19,orzdba时刻了解你的数据库,orzdba时刻了解你的数据库,2019/11/19,oprofile介绍,=oprofile是什么=oprofile也是一个开源的profiling工具，它使用硬件调试寄存器来统计信息，进行profiling的开销比较小，而且可以对内核进行profiling。Oprofile是一个全局的抽样统计工具Oprofile是一种细粒度的工具，可以为指令集或者为函数、系统调用或中断处理例程收集采样。Oprofile通过取样来工作。使用收集到的评测数据，用户可以很容易地找出性能问题。,2019/11/19,Oprofile系统工作流图,2019/11/19,oprofile使用场景,cpu无端占用高？应用程序响应慢？苦于没有分析的工具？oprofile利用cpu硬件层面提供的性能计数器(performancecounter)，通过计数采样，帮助我们从进程、函数、代码层面找出占用cpu的罪魁祸首。,2019/11/19,oprofile使用,#加载oprofile内核模块opcontrol-init#我们对内核的取样没兴趣opcontrol-setup-no-vmlinux#在开始收集采样数据前回顾下我们的设置opcontrol-status#清除上一次采样到的数据opcontrol-reset#启动oprofiled守护程序,从内核中拉出采样数据opcontrol-start#运行我们的程序#收集采样数据opcontrol-dump#关闭守护程序,同时准备好采样的数据opcontrol-shutdown,2019/11/19,oprofile获取采样信息,#系统级别的opreport-long-filenames#模块级别的opreportimage:foo-l#源码级别的opannotateimage:foo-s,2019/11/19,oprofile使用,opcontrol-deinitmodprobeoprofiletimer=1$dmesg|grepoprofile|tail-n1(oprofile:usingtimerinterrupt.)opcontrol-resetpcontrol-separate=lib-no-vmlinux-start-image=/home/mysql_user/mysqlhome/bin/mysqldopcontrol-dumpopcontrol-shutdownopreport-l/home/mysql_user/mysqlhome/bin/mysqld,2019/11/19,oprofile诊断你的cpu异常,2019/11/19,ioprofile查看IO情况的利器,2019/11/19,ioprofile查看IO情况的利器,2019/11/19,ioprofile透过进程发现写入状况,2019/11/19,ioprofile产看IO情况的利器,2019/11/19,ioprofile产看IO情况的利器,PERFORMANCE_SCHEMA简介,PerformanceSchema的功能，类似于Oracle数据库提供的丰富的系统表，用于将数据库内部的运行情况展示出来。包括：Mutex，RWLock，IO等等。performanceschema可以使得DBA更加容易的定位系统瓶颈performanceschema也使得mysql的性能更加可测量化,Performanceschema介绍,MySQL5.5FileI/O,Mutexes,RWLocksetcMySQL5.6FileI/OTableI/OQueryDigestStatementTablelocksMySQL5.7memory,replication,2019/11/19,MySQL5.6PS,MySQL5.6默认开启了performance_schema，可以动态的禁用.FileI/O(查看最热的文件和事件)TableI/O（查看最热的表的情况）QueryDigest(定位热点查询和表）Statement（定位热点查询和表）Tablelocks（定位热点表锁）,Performance_schema配置,fperformance_schema_instrument=%=onperformance_schema_consumer_events_stages_current=ONperformance_schema_consumer_events_stages_history=ONperformance_schema_consumer_events_stages_history_long=ONperformance_schema_consumer_events_statements_current=ONperformance_schema_consumer_events_statements_history=ONperformance_schema_consumer_events_statements_history_long=ONperformance_schema_consumer_events_waits_current=ONperformance_schema_consumer_events_waits_history=ONperformance_schema_consumer_events_waits_history_long=ONperformance_schema_consumer_global_instrumentation=ONperformance_schema_consumer_thread_instrumentation=ONperformance_schema_consumer_statements_digest=ON默认启动开启了performance_schema,可以通过DML动态启用相关的instrument和comsumerUPDATEperformance_schema.setup_instrumentsSETENABLED=YES,TIMED=YES;UPDATEperformance_schema.setup_consumersSETENABLED=YES;,2019/11/19,查看performance_schema变量,SHOWGLOBALVARIABLESLIKEperformance_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|+-+-+,2019/11/19,查看performance_schema占用内存大小,SHOWENGINEPERFORMANCE_SCHEMASTATUS;+-+-+-+|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左右,2019/11/19,performance_schema状态变量,SHOWSTATUSLIKE%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|+-+-+26rowsinset(0.00sec),显示由于内存限制导致某些统计信息没有计入PS中,PerformanceSchemasetuptable,showtableslike%setup%;+-+|Tables_in_performance_schema(%setup%)|+-+|setup_actors|setup_consumers|setup_instruments|setup_objects|setup_timers|+-+5rowsinset(0.00sec)SELECT*FROMsetup_actors;+-+-+-+|HOST|USER|ROLE|+-+-+-+|%|%|%|+-+-+-+1rowinset(0.00sec),2019/11/19,默认情况下，监控的表对象排除mysql/PS/IS库的对象，其中IS库下的表，不管是否开启，都不会去监控,Threads表,selectTHREAD_ID,NAME,TYPE,INSTRUMENTEDfromthreads;+-+-+-+-+|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|+-+-+-+-+31rowsinset(0.00sec),instrument,select*fromsetup_instrumentslimit10;+-+-+-+|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|+-+-+-+10rowsinset(0.01sec)updatesetup_instrumentssetENABLED=NO,TIMED=NOwherenamelike%mutex%;QueryOK,0rowsaffected(0.00sec)Rowsmatched:156Changed:0Warnings:0,2019/11/19,instrument,instrument树形结构，主要包括idle/wait/stage/statement，下层包括例如sync,io，再往下层可能为以划分成mutex/cond/rwlock，最后为具体对象或者模块。wait/synch/mutex/innodb/trx_mutexidelsocket的空闲信息，事件记录在socket_instances表中stage命名规则为stage/code_area/stage_namecode_area值为sql/mysys，stage_name表示执行语句过程中的各个阶段stage命名规则为statement/sql或者comsql下为具体的sql类型，com下为服务器的一些命令相关）wait最为关注的部分，包括文件的操作时间，socket事件，表的io的时间统计，主要包含wait/io，wait/lock，wait/synch（mutex(wait/synch/mutex)）、读写锁(wait/synch/rwlock）,consumer,select*fromsetup_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|+-+-+,2019/11/19,其中高级别的consumer决定是否去检查低级别的consumer,2019/11/19,Forfiles,showtableslike%file%;+-+|Tables_in_performance_schema(%file%)|+-+|file_instances|file_summary_by_event_name|file_summary_by_instance|+-+3rowsinset(0.00sec),2019/11/19,查询读写top5的等待,selectEVENT_NAME,COUNT_READReads,COUNT_WRITEWrites,COUNT_MISCMisc,(COUNT_MISC+COUNT_WRITE+COUNT_READ)asSUM_IOfromfile_summary_by_event_nameorderby5desclimit5;+-+-+-+-+-+|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|+-+-+-+-+-+5rowsinset(0.00sec),2019/11/19,查询读写top5的file,selectFILE_NAMEFILE,COUNT_READReads,COUNT_WRITEWrites,COUNT_MISCMisc,(COUNT_MISC+COUNT_WRITE+COUNT_READ)asSUM_IOfromfile_summary_by_instanceorderbySUM_IOdesclimit5;+-+-+-+-+-+|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|+-+-+-+-+-+5rowsinset(0.00sec),2019/11/19,FortableI/OandLockWait相关,showtableslike%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|+-+3rowsinset(0.00sec),2019/11/19,统计在表上锁的top5,selectobject_schema,object_name,count_star,sum_timer_waitfromtable_lock_waits_summary_by_tableorderbycount_stardesclimit5;+-+-+-+-+|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|+-+-+-+-+,统计发生tablelock消耗时间最高的表,selectOBJECT_NAMETABLE,SUM_TIMER_READReadTM,SUM_TIMER_WRITEWriteTM,SUM_TIMER_WAITWaitTMfromtable_lock_waits_summary_by_tableorderbyWaitTMdesclimit5;+-+-+-+-+|TABLE|ReadTM|WriteTM|WaitTM|+-+-+-+-+|sbtest2|19010384820015|6295964398125|25306349218140|sbtest3|18941771513115|6287191966275|25228963479390|sbtest4|18708376530030|6236878257210|24945254787240|sbtest1|18649886894175|6222007612560|24871894506735|t3|0|13488045|13488045|+-+-+-+-+5rowsinset(0.01sec),2019/11/19,table_io_waits_summary_by_table相关,selectobject_name,count_starfromtable_io_waits_summary_by_tableorderbycount_stardesclimit5;+-+-+|object_name|count_star|+-+-+|sbtest1|138902806|sbtest2|138838630|sbtest3|138800938|sbtest4|138638893|t3|4|+-+-+5rowsinset(0.01sec),2019/11/19,table_io_waits_summary_by_table,selectobject_name,COUNT_STARALL,COUNT_READRead,COUNT_WRITEWrite,COUNT_FETCHFetch,COUNT_INSERTInsert,COUNT_UPDATEUpdate,COUNT_DELETEDeLetefromtable_io_waits_summary_by_tableorderby2desclimit5;+-+-+-+-+-+-+-+-+|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|+-+-+-+-+-+-+-+-+5rowsinset(0.01sec),2019/11/19,table_io_waits_summary_by_index_usage相关,selectobject_name,index_name,count_starfromtable_io_waits_summary_by_index_usageorderby3desclimit5;+-+-+-+|object_name|index_name|count_star|+-+-+-+|sbtest1|PRIMARY|139836868|sbtest2|PRIMARY|139788897|sbtest3|PRIMARY|139768021|sbtest4|PRIMARY|139566011|sbtest1|NULL|776869|+-+-+-+5rowsinset(0.02sec),2019/11/19,STATEMENT相关的表,showtableslike%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|+-+10rowsinset(0.00sec)events_statements_summary_by_digest为相同类型的SQL的一个聚合events_statements_current,events_statements_history，events_statements_history_long这三个表中记录了具体的SQL，而非聚合的结果。,2019/11/19,events_statements_summary_by_digest,selectLEFT(DIGEST_TEXT,40)QUERY,digestSQLMD5,SUM_TIMER_WAIT,COUNT_STARfromevents_statements_summary_by_digestorderby3desclimit5;+-+-+-+-+|QUERY|SQLMD5|SUM_TIMER_WAIT|COUNT_STAR|+-+-+-+-+|SELECTcFROMsbtest1WHEREid=?|125fdbb0acedfb306b0d00bdf1d7731f|2872295511000000|7929410|SELECTcFROMsbtest4WHEREid=?|5b05326050a52c5a06277968cf8a5502|2761876049000000|7914298|SELECTcFROMsbtest2WHEREid=?|3587b1bb2868c13297655edee733b96e|2745128042000000|7926359|SELECTcFROMsbtest3WHEREid=?|dc1eb8a7c369e8cfd527ca328919c309|2705968173000000|7924812|SELECTDISTINCTROWcFROMsbtest1WHERE|65b685f605a2acf99f054bdfe773b2b3|996040945000000|793168|+-+-+-+-+5rowsinset(0.01sec),2019/11/19,events_statements_summary_by_digest,select*fromevents_statements_summary_by_digestwhereDIGEST=125fdbb0acedfb306b0d00bdf1d7731fG*1.row*SCHEMA_NAME:sbtestDIGEST:125fdbb0acedfb306b0d00bdf1d7731fDIGEST_TEXT:SELECTcFROMsbtest1WHEREid=?COUNT_STAR:7956690SUM_TIMER_W