User数据库迁移报告

Transcription

1 MyAWR(awr of MySQL) DBA Team 二零一三年五月 文档修订版历史 日期版本说明作者审阅 author 邱伟胜 1

2 目录 目录 MyAWR(awr of MySQL) What is myawr Myawr Data Model Quickstart install db initialize myawr_host add two jobs in crontab Dependencies Mysql WorkLoad Report Contact me

3 1.What is myawr myawr introduction Myawr is a tool for collecting and analyzing performance data for MySQL database (including os info,mysql status info and Slow Query Log all of details). The idea comes from Oracle awr. Myawr periodic collect data and save to the database as snapshots. Myawr was designed as CS architecture.myawr depends on performance schema of MySQL database. Myawr consists of two parts: myawr.pl a perl script for collecting mysql performance data myawrrpt.pl-----a perl script for analyzing mysql performance data Myawr relies on the Percona Toolkit to do the slow query log collection. Specifically you can run pt-query-digest. To parse your slow logs and insert them into your server database for reporting and analyzing. Thanks to orzdba.pl (zhuxu@taobao.com). 3

4 2.Myawr Data Model myawr introduction myawr db include tables list: mysql> show tables; Tables_in_myawr myawr_cpu_info myawr_host myawr_innodb_info myawr_io_info myawr_isam_info myawr_load_info myawr_mysql_info myawr_query_review myawr_query_review_history myawr_snapshot myawr_snapshot_events_waits_summary_by_instance myawr_snapshot_events_waits_summary_global_by_event_name myawr_snapshot_file_summary_by_event_name myawr_snapshot_file_summary_by_instance myawr_swap_net_disk_info rows in set (0.01 sec) 4

5 some key tables: myawr_host-- mysql instance config table myawr_snapshot -- snapshot table,exec myawr.pl a time as a shapshot myawr_query_review_history -- The table in which to store historical values for review trend analysis about slow log. myawr introduction myawr data model: myawr_snapshot.host_id reference myawr_host.id; myawr_query_review_history.hostid_max reference myawr_host.id; myawr_innodb_info.(host_id,snap_id) reference myawr_snapshot.(host_id,snap_id); 5

6 6 myawr introduction

7 3. Quickstart If you are interesting to use this tool, here's what you need: 1. A MySQL database to store snapshot data and slow log analysis data. 2. pt-query-digest by percona 3. A MySQL server with perl-dbd-mysql 4. slow query logs named like slow_ log,you can switch slow logs every day. 3.1 install db Connect to the MySQL database where store the performance data and issue the following command in myawr.sql: CREATE DATABASE `myawr` DEFAULT CHARACTER SET utf8; grant all on myawr.* to 'user'@'%' identified by "111111"; then create tables in db myawr. 7

8 3.2 initialize myawr_host myawr introduction Insert a config record about your mysql instacne,just like: INSERT INTO `myawr_host`(id,host_name,ip_addr,port,db_role,version) VALUES (6, 'db2.11', ' ', 3306, 'master', '5.5.27');. 3.3 add two jobs in crontab * * * * * perl /data/mysql/sh/myawr.pl -u user -p lh P tu user -tp TP th n eth0 -d sdb1 -I 6 >> /data/mysql/sh/myawr_pl.log 2>&1 # * * * /data/mysql/sh/pt-query-digest --user=user --password= review h= ,d=myawr,t=myawr_query_review --review-history h= ,d=myawr,t=myawr_query_review_history --no-report --limit=100\% --filter="\$event->{add_column} = length(\$event->{arg}) and \$event->{hostid}=6" /data/mysql/sh/slow_`date -d "-1 day" +"\%Y\%m\%d"`.log >> /data/mysql/sh/pt-query_run.log 2>&1 myawr.pl Parameters: -h,--help Print Help Info. -i,--interval Time(second) Interval(default 1). -d,--disk Disk Info(can't be null,default sda1). -n,--net Net Info(default eth0). -P,--port Port number to use for local mysql connection(default 3306). -u,--user user name for local mysql(default user). 8

9 -p,--pswd user password for local mysql(can't be null). -lh,--lhost localhost(ip) for mysql where info is got(can't be null). -TP,--tport Port number to use formysql where info is saved (default 3306) -tu,--tuser user name for mysql where info is saved(default user). -tp,--pswd user password for mysql where info is saved(can't be null). -th,--thost host(ip) for mysql where info is saved(can't be null). -I,--tid db instance register id(can't be null,reference myawr_host.id) myawr introduction pt-query-digest Parameters: --user user name for mysql where info is saved --password user password for mysql where info is saved --review Store a sample of each class of query in this DSN h host(ip) for mysql where info is saved D database t table name --review-history The table in which to store historical values for review trend analysis. h host(ip) for mysql where info is saved D database t table name $event->{hostid}=6 db instance register id(reference myawr_host.id) The pt-query-digest only support mechanism for switching a slow log file every day just now, named like slow_ log(slow_date -d "-1 day" +"%Y%m%d".log) 9

10 4. Dependencies perl-dbd-mysql you can install it two way: yum install perl-dbd-mysql or install manually like : mkdir /tmp/mysqldbd-install cp /usr/lib64/mysql/*.a /tmp/mysqldbd-install perl Makefile.PL --libs="-l/tmp/mysqldbd-install -lmysqlclient" make make test make install 5. Mysql WorkLoad Report We can use myawrrpt.pl to generate mysql workload report.you can execute the script on MySQL database machine where store the performance data,but perl-dbd-mysql is required.we also can execute the script in any linux machine with perl-dbd-mysql installed. You can execute it for help Info: perl myawrrpt.pl -h Info : Created By noodba ( 10

11 References: Oracle awr Usage : Command line options : myawr introduction -h,--help Print Help Info. -P,--port Port number to use for local mysql connection(default 3306). -u,--user user name for local mysql(default user). -p,--pswd user password for local mysql(can't be null). -lh,--lhost localhost(ip) for mysql where info is got(can't be null). -I,--tid db instance register id(can't be null,ref myawr_host.id) Sample : shell> perl myawrrpt.pl -p lh I 11 ============================================================================ Let use to generate mysql (db2.11,instance id = 6) workload report: perl myawrrpt.pl -u user -p P lh I 6 =================================================== Welcome to use the myawrrpt tool! Date: Hostname is: db2.11 Ip addr is:

12 Port is: 3306 Db role is: master Server version is: Uptime is: 0y 2m 2d 7h 55mi 33s Min snap_id is: 1 Min snap_time is: :12:02 Max snap_id is: 1147 Max snap_time is: :29:02 snap interval is: 60s =================================================== myawr introduction Listing the last 2 days Snapshots snap_id: 19 snap_time : :30:02 snap_id: 38 snap_time : :49:02 snap_id: 57 snap_time : :08:02 snap_id: 76 snap_time : :27:02 snap_id: 95 snap_time : :46:02... snap_id: 1102 snap_time : :44:02 snap_id: 1121 snap_time : :03:02 snap_id: 1140 snap_time : :22:02 snap_id: 1147 snap_time : :29:02 12

13 Pls select Start and End Snapshot Id Enter value for start_snap:1 Start Snapshot Id Is:1 Enter value for end_snap:589 End Snapshot Id Is:589 Set the Report Name Enter value for report_name:myawr.html Using the report name :myawr.html Generating the mysql report for this analysis... Generate the mysql report Successfully. myawr]$ ls -al total 976 drwxrwxr-x 2 mysql mysql 4096 May 22 09:30. drwx mysql mysql 4096 May 13 10:42.. -rw-rw-r-- 1 mysql mysql May 22 09:30 myawr.html -rw-rw-r-- 1 mysql mysql May 11 16:23 myawrrpt.pl 13

14 Ok,let me show some pictures which come from my test db report: Mysql WorkLoad Report Host Name Ip addr Port Db role Version Uptime db master y 2m 2d 7h 55mi 33s Snap Id Snap Time Threads_connected Threads_running Begin Snap: :12: End Snap: :00: Elapsed: (seconds) Start snap value and end snap value Begin End</TH< tr> query_cache_size :

15 thread_cache_size : table_definition_cache : max_connections : table_open_cache : slow_launch_time : max_heap_table_size : tmp_table_size : open_files_limit : Max_used_connections : Threads_connected : Threads_cached : Threads_created : Threads_running : Connections : key_buffer_size : join_buffer_size :

16 sort_buffer_size : Key_blocks_not_flushed : Key_blocks_unused : Key_blocks_used : Key_read_requests : Key_reads : Key_write_requests : Key_writes : Questions : Com_select : Com_insert : Com_update : Com_delete : Bytes_received : Bytes_sent : Qcache_hits :

17 Qcache_inserts : Select_full_join : Select_scan : Slow_queries : Com_commit : Com_rollback : Open_files : Open_table_definitions : Open_tables : Opened_files : Opened_table_definitions : Opened_tables : Created_tmp_disk_tables : Created_tmp_files : Created_tmp_tables : Binlog_cache_disk_use :

18 Binlog_cache_use : Aborted_clients : Sort_merge_passes : Sort_range : Sort_rows : Sort_scan : Table_locks_immediate : Table_locks_waited : Handler_read_first : Handler_read_key : Handler_read_last : Handler_read_next : Handler_read_prev : Handler_read_rnd : Handler_read_rnd_next : Innodb_rows_inserted :

19 Innodb_rows_updated : Innodb_rows_deleted : Innodb_rows_read : Innodb_buffer_pool_read_requests: Innodb_buffer_pool_reads : Innodb_buffer_pool_pages_data : Innodb_buffer_pool_pages_free : Innodb_buffer_pool_pages_dirty : Innodb_buffer_pool_pages_flushed: Innodb_data_reads : Innodb_data_writes : Innodb_data_read : Innodb_data_written : Innodb_os_log_fsyncs : Innodb_os_log_written : history_list :

20 log_bytes_written : log_bytes_flushed : last_checkpoint : queries_inside : queries_queued : read_views : innodb_open_files : innodb_log_waits : Some Key Load Info Per Second TPS: 347 Com_select(s): 120 Com_insert(s): 336 Com_update(s): 11 20

21 Com_delete(s): 0 Innodb t_row PS: 351 Innodb_rows_inserted(s): 336 Innodb_rows_updated(s): 14 Innodb_rows_deleted(s): 0 Innodb_rows_read(/s): Innodb_data_reads(s): 2 Innodb_data_writes(s): 708 Innodb_data_read(kb/s): 35 Innodb_data_written(kb/s): 1117 Innodb_os_log_fsyncs(s): 1 Innodb_os_log_written(kb/s): 602 Some Key Hits Percentage 21

22 key_buffer_read_hits %: key_buffer_write_hits %: Innodb_buffer_read_hits %: Query_cache_hits %: 0 Thread_cache_hits %: Top 10 Timed Events event_name wait time(picsecond) wait count wait/synch/cond/sql/mysql_bin_log::update_cond wait/synch/cond/mysys/my_thread_var::suspend wait/io/file/myisam/dfile wait/io/file/innodb/innodb_log_file wait/io/file/sql/binlog wait/synch/mutex/mysys/thr_lock::mutex wait/io/file/innodb/innodb_data_file

23 wait/synch/rwlock/innodb/index_tree_rw_lock wait/synch/mutex/sql/log::lock_log wait/synch/mutex/innodb/log_sys_mutex Top 10 read file Events event_name read bytes read count wait/io/file/myisam/dfile wait/io/file/sql/binlog wait/io/file/innodb/innodb_data_file wait/io/file/myisam/kfile wait/io/file/sql/frm wait/io/file/sql/log_event_info 0 0 wait/io/file/archive/metadata 0 0 wait/io/file/csv/update 0 0 wait/io/file/sql/dbopt 0 0 wait/io/file/sql/init

24 Top 10 write file Events myawr introduction event_name read bytes read count wait/io/file/innodb/innodb_data_file wait/io/file/innodb/innodb_log_file wait/io/file/sql/binlog wait/io/file/myisam/dfile wait/io/file/sql/slow_log wait/io/file/myisam/kfile wait/io/file/sql/frm wait/io/file/sql/binlog_index wait/io/file/sql/log_event_info 0 0 wait/io/file/archive/metadata

25 OS Load Info myawr introduction Snap Time load1 load5 load :12: :22: :32: :42: :52: :02: :12: :22: :32: :42: :52: :02: :12:

26 :22: :32: :42: :52: :02: :12: :22: :32: :42: :52: :02: :12: :22: :32: :42: :52:

27 :02: :12: :22: :32: :42: :52: :02: :12: :22: :32: :42: :52: :02: :12: :22: :32:

28 :42: :52: :02: :12: :22: :32: :42: :52: :02: :12: :22: :32: :42: :52: :00:

29 OS CPU Info myawr introduction Snap Time user system idle iowait :12: :22: :32: :42: :52: :02: :12: :22: :32: :42: :52: :02: :12:

30 :22: :32: :42: :52: :02: :12: :22: :32: :42: :52: :02: :12: :22: :32: :42: :52:

31 :02: :12: :22: :32: :42: :52: :02: :12: :22: :32: :42: :52: :02: :12: :22: :32:

32 :42: :52: :02: :12: :22: :32: :42: :52: :02: :12: :22: :32: :42: :52: :00:

33 OS IO Info myawr introduction Snap Time rd_ios_s wr_ios_s rkbs wkbs queue svc_t busy :12: :22: :32: :42: :52: :02: :12: :22: :32: :42: :52: :02: :12:

34 :22: :32: :42: :52: :02: :12: :22: :32: :42: :52: :02: :12: :22: :32: :42: :52:

35 :02: :12: :22: :32: :42: :52: :02: :12: :22: :32: :42: :52: :02: :12: :22: :32:

36 :42: :52: :02: :12: :22: :32: :42: :52: :02: :12: :22: :32: :42: :52: :00:

37 OS Other Info myawr introduction Snap Time swap_in swap_out net_recv net_send file_system total_mb used_mb used_pct mount_point :12: /dev/sdb /data :22: /dev/sdb /data :32: /dev/sdb /data :42: /dev/sdb /data :52: /dev/sdb /data :02: /dev/sdb /data :12: /dev/sdb /data :22: /dev/sdb /data :32: /dev/sdb /data :42: /dev/sdb /data :52: /dev/sdb /data :02: /dev/sdb /data :12: /dev/sdb /data 37

38 :22: /dev/sdb /data :32: /dev/sdb /data :42: /dev/sdb /data :52: /dev/sdb /data :02: /dev/sdb /data :12: /dev/sdb /data :22: /dev/sdb /data :32: /dev/sdb /data :42: /dev/sdb /data :52: /dev/sdb /data :02: /dev/sdb /data :12: /dev/sdb /data :22: /dev/sdb /data :32: /dev/sdb /data :42: /dev/sdb /data :52: /dev/sdb /data 38

39 :02: /dev/sdb /data :12: /dev/sdb /data :22: /dev/sdb /data :32: /dev/sdb /data :42: /dev/sdb /data :52: /dev/sdb /data :02: /dev/sdb /data :12: /dev/sdb /data :22: /dev/sdb /data :32: /dev/sdb /data :42: /dev/sdb /data :52: /dev/sdb /data :02: /dev/sdb /data :12: /dev/sdb /data :22: /dev/sdb /data :32: /dev/sdb /data 39

40 :42: /dev/sdb /data :52: /dev/sdb /data :02: /dev/sdb /data :12: /dev/sdb /data :22: /dev/sdb /data :32: /dev/sdb /data :42: /dev/sdb /data :52: /dev/sdb /data :02: /dev/sdb /data :12: /dev/sdb /data :22: /dev/sdb /data :32: /dev/sdb /data :42: /dev/sdb /data :52: /dev/sdb /data :00: /dev/sdb /data 40

41 TOP 20 SQL myawr introduction checksum db name ts_min ts_max ts_c nt Query_time_ Query_time_pc sum t_95 Lock_time_ sum Lock_time_pc t_95 Rows_sent_ sum Rows_sent_pc t_95 sample test :32:56 19:14: update xxxx_test test :55:12 21:27: test :54:54 21:27: test :48:26 23:15:39 SELECT count(id) as... SELECT count(id) as... SELECT b.id, b test INSERT INTO 41

42 AAAA_A... 01:48:45 21:48: test :03:31 23:59: select last_insert_i test :19:21 18:19:21 SELECT MI... ID, test :05:20 23:51: test :39:24 00:01: test :47:48 21:47:48 select * from ( s... SELECT b.id, b... Update test. bat... 42

43 myawr introduction Update test.exce... select t.operator_i select * from liv test :39:04 23:39: test :09:00 18:27: test select t.id,... 09:54:07 21:21: test :48:53 23:49: test select t.id,... 09:55:48 21:21: test select t.id,

44 :55:15 21:21: test :16:17 23:58: information_sch ema 14:12:02 00:01: test :39:35 17:58: information_sch ema 14:12:02 00:01:02 SELECT id,t.hits FRO... select * from perfor... SELECT MIN(PUBLISH_ T... select VARIABLE_VAL U... TOP SQL DETAIL checksum sql detail 44

45 update test set plag = 2, uptime = now(), opid = 1 where ordid = SELECT count(id) as count FROM zzz_log where code='200001' and ccdoe='100001' SELECT count(id) as count FROM zzzz where code='200001' select VARIABLE_VALUE from information_schema.global_variables where VARIABLE_NAME='PERFORMANCE_SCHEMA'...more......more The End For detail report,pls click myawr.html. 45

46 6. Contact me Any questions,pls contact me freely. Q Q : Phone: (+86)