MySQL性能调优介绍.ppt

MySQL 性能调优介绍,2013.10.18 DBA 王洪权 新浪微博 foreverreturn,内容概要,一 性能分析介绍（简单介绍下orazdba,oprofile,ioprofile） 二 performance schema介绍,2019/6/30,工具化的启发,工具化的重要性（熟练运用已有的工具，可以快速对数据库进行诊断，时刻了解你的数据库的运行状况）,性能分析,MySQL相关： show session/global status like %variables% ; show full processlist; show engine innodb mutex; SHOW ENGINE INNODB STATUS; SHOW PROFILE; MySQL slow query,性能分析,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(加速备库预热，解决主从延时),性能分析,其他 mytop innotop mysqltuner.pl dba-slow-picker.pl（诊断异常时刻SQL) ,2019/6/30,mysqltuner.pl 给mysql做个简单报告,2019/6/30,mysqltuner.pl 给mysql做个简单报告,2019/6/30,orzdba 时刻了解你的数据库,orzdba 时刻了解你的数据库,2019/6/30,oprofile 介绍,= oprofile 是什么 = oprofile也是一个开源的profiling工具，它使用硬件调试寄存器来统计信息，进行profiling的开销比较小，而且可以对内核进行profiling。 Oprofile 是一个全局的抽样统计工具 Oprofile是一种细粒度的工具，可以为指令集或者为函数、系统调用或中断处理例程收集采样。Oprofile 通过取样来工作。使用收集到的评测数据，用户可以很容易地找出性能问题。,2019/6/30,Oprofile系统工作流图,2019/6/30,oprofile 使用场景,cpu无端占用高？应用程序响应慢？苦于没有分析的工具？ oprofile利用cpu硬件层面提供的性能计数器(performance counter)，通过计数采样，帮助我们从进程、函数、代码层面找出占用cpu的“罪魁祸首“。,2019/6/30,oprofile使用,# 加载oprofile内核模块 opcontrol -init #我们对内核的取样没兴趣 opcontrol -setup -no-vmlinux #在开始收集采样数据前回顾下我们的设置 opcontrol -status #清除上一次采样到的数据 opcontrol -reset #启动oprofiled守护程序,从内核中拉出采样数据 opcontrol -start #运行我们的程序 #收集采样数据 opcontrol -dump #关闭守护程序, 同时准备好采样的数据 opcontrol -shutdown,2019/6/30,oprofile获取采样信息,#系统级别的 opreport -long-filenames #模块级别的 opreport image:foo -l #源码级别的 opannotate image:foo -s,2019/6/30,oprofile使用,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,2019/6/30,oprofile 诊断你的cpu异常,2019/6/30,ioprofile 查看IO情况的利器,2019/6/30,ioprofile 查看IO情况的利器,2019/6/30,ioprofile 透过进程发现写入状况,2019/6/30,ioprofile 产看IO情况的利器,2019/6/30,ioprofile 产看IO情况的利器,PERFORMANCE_SCHEMA简介,Performance Schema的功能，类似于Oracle数据库提供的丰富的系统表，用于将数据库内部的运行情况展示出来。包括：Mutex，RWLock，IO等等。 performance schema 可以使得DBA 更加容易的定位系统瓶颈 performance schema也使得mysql的性能更加可测量化,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,replication,2019/6/30,MySQL 5.6 PS,MySQL 5.6 默认开启了performance_schema，可以动态的禁用. File I/O (查看最热的文件和事件) Table I/O （查看最热的表的情况） Query Digest (定位热点查询和表） Statement （定位热点查询和表） Table locks （定位热点表锁）,Performance_schema 配置,f 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;,2019/6/30,查看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 | +-+-+,2019/6/30,查看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左右,2019/6/30,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中,Performance Schema set up table,show 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),2019/6/30,默认情况下，监控的表对象排除mysql/PS/IS库的对象，其中IS库下的表，不管是否开启，都不会去监控,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),instrument,select * 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: 0,2019/6/30,instrument,instrument树形结构，主要包括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）,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 | +-+-+,2019/6/30,其中高级别的consumer决定是否去检查低级别的consumer,2019/6/30,For 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),2019/6/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/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),2019/6/30,查询读写top 5的file,select 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),2019/6/30,For 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),2019/6/30,统计在表上锁的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 | +-+-+-+-+,统计发生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),2019/6/30,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),2019/6/30,table_io_waits_summary_by_table,select 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 | I