MyAWR(awr of MySQL) DBA Team 二零一三年五月 文档修订版历史 日期版本说明作者审阅 2013-05-21 1.0 author 邱伟胜 1
目录 目录 MyAWR(awr of MySQL)... 1 1.What is myawr... 3 2.Myawr Data Model... 4 3. Quickstart... 7 3.1 install db... 7 3.2 initialize myawr_host... 8 3.3 add two jobs in crontab... 8 4. Dependencies... 10 5. Mysql WorkLoad Report... 10 6. Contact me... 46 2
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
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 +----------------------------------------------------------+ 15 rows in set (0.01 sec) 4
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 myawr introduction
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_20130521.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
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', '192.168.2.11', 3306, 'master', '5.5.27');. 3.3 add two jobs in crontab * * * * * perl /data/mysql/sh/myawr.pl -u user -p 111111 -lh 192.168.2.11 -P 3306 -tu user -tp 111111 -TP 3306 -th 192.168.1.92 -n eth0 -d sdb1 -I 6 >> /data/mysql/sh/myawr_pl.log 2>&1 # 15 14 * * * /data/mysql/sh/pt-query-digest --user=user --password=111111 --review h=192.168.1.92,d=myawr,t=myawr_query_review --review-history h=192.168.1.92,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
-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_20130521.log(slow_date -d "-1 day" +"%Y%m%d".log) 9
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 (www.noodba.com). 10
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 111111 -lh 192.168.1.111 -I 11 ============================================================================ Let use to generate mysql (db2.11,instance id = 6) workload report: perl myawrrpt.pl -u user -p 111111 -P 3306 -lh 192.168.1.92 -I 6 =================================================== Welcome to use the myawrrpt tool! Date: 2013-05-22 Hostname is: db2.11 Ip addr is: 192.168.2.11 11
Port is: 3306 Db role is: master Server version is: 5.5.27 Uptime is: 0y 2m 2d 7h 55mi 33s Min snap_id is: 1 Min snap_time is: 2013-05-21 14:12:02 Max snap_id is: 1147 Max snap_time is: 2013-05-22 09:29:02 snap interval is: 60s =================================================== myawr introduction Listing the last 2 days Snapshots --------------------------------- snap_id: 19 snap_time : 2013-05-21 14:30:02 snap_id: 38 snap_time : 2013-05-21 14:49:02 snap_id: 57 snap_time : 2013-05-21 15:08:02 snap_id: 76 snap_time : 2013-05-21 15:27:02 snap_id: 95 snap_time : 2013-05-21 15:46:02... snap_id: 1102 snap_time : 2013-05-22 08:44:02 snap_id: 1121 snap_time : 2013-05-22 09:03:02 snap_id: 1140 snap_time : 2013-05-22 09:22:02 snap_id: 1147 snap_time : 2013-05-22 09:29:02 12
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. [mysql@test2 myawr]$ ls -al total 976 drwxrwxr-x 2 mysql mysql 4096 May 22 09:30. drwx------ 19 mysql mysql 4096 May 13 10:42.. -rw-rw-r-- 1 mysql mysql 73074 May 22 09:30 myawr.html -rw-rw-r-- 1 mysql mysql 53621 May 11 16:23 myawrrpt.pl 13
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 db2.11 192.168.2.11 3306 master 5.5.27 0y 2m 2d 7h 55mi 33s Snap Id Snap Time Threads_connected Threads_running Begin Snap: 1 2013-05-21 14:12:02 481.00 3.00 End Snap: 589 2013-05-22 00:00:02 450.00 2.00 Elapsed: 35280 (seconds) Start snap value and end snap value Begin End</TH< tr> query_cache_size : 536870912.00 536870912.00 14
thread_cache_size : 8.00 8.00 table_definition_cache : 400.00 400.00 max_connections : 1000.00 1000.00 table_open_cache : 512.00 512.00 slow_launch_time : 2.00 2.00 max_heap_table_size : 16777216.00 16777216.00 tmp_table_size : 67108864.00 67108864.00 open_files_limit : 131072.00 131072.00 Max_used_connections : 546.00 546.00 Threads_connected : 481.00 450.00 Threads_cached : 7.00 7.00 Threads_created : 69201.00 70825.00 Threads_running : 3.00 2.00 Connections : 354956.00 360120.00 key_buffer_size : 4294967296.00 4294967296.00 join_buffer_size : 131072.00 131072.00 15
sort_buffer_size : 2097152.00 2097152.00 Key_blocks_not_flushed : 0.00 0.00 Key_blocks_unused : 2375290.00 2375230.00 Key_blocks_used : 1969607.00 1969607.00 Key_read_requests : 3487777792.00 3534557184.00 Key_reads : 1707226.00 1707233.00 Key_write_requests : 146567904.00 146596912.00 Key_writes : 68096480.00 68112368.00 Questions : 2205072128.00 2221643520.00 Com_select : 296839808.00 301102016.00 Com_insert : 1641965312.00 1653826688.00 Com_update : 222941536.00 223330160.00 Com_delete : 379099.00 400600.00 Bytes_received : 1289763160064.00 1299087491072.00 Bytes_sent : 2367515000832.00 2383360819200.00 Qcache_hits : 189425152.00 189425152.00 16
Qcache_inserts : 13178355.00 13178355.00 Select_full_join : 115250.00 115250.00 Select_scan : 79686792.00 79825888.00 Slow_queries : 78863800.00 79023552.00 Com_commit : 1026895.00 1028760.00 Com_rollback : 43041.00 43293.00 Open_files : 270.00 269.00 Open_table_definitions : 266.00 267.00 Open_tables : 512.00 512.00 Opened_files : 9150943.00 9270557.00 Opened_table_definitions : 1002.00 1004.00 Opened_tables : 8417.00 8429.00 Created_tmp_disk_tables : 1984868.00 2014532.00 Created_tmp_files : 149046.00 149955.00 Created_tmp_tables : 3449305.00 3488825.00 Binlog_cache_disk_use : 10621.00 10627.00 17
Binlog_cache_use : 1658462336.00 1670670848.00 Aborted_clients : 6962.00 7029.00 Sort_merge_passes : 627265.00 628456.00 Sort_range : 24359336.00 24971876.00 Sort_rows : 76120592.00 76849456.00 Sort_scan : 302621.00 305819.00 Table_locks_immediate : 75422326784.00 75864784896.00 Table_locks_waited : 4291.00 29649.00 Handler_read_first : 78804400.00 78925768.00 Handler_read_key : 5417383936.00 5433953792.00 Handler_read_last : 5363.00 5440.00 Handler_read_next : 29760935936.00 29854246912.00 Handler_read_prev : 224503360.00 224840768.00 Handler_read_rnd : 1990567296.00 1991156480.00 Handler_read_rnd_next : 577819115520.00 582355714048.00 Innodb_rows_inserted : 1687403904.00 1699259392.00 18
Innodb_rows_updated : 81918272.00 82447104.00 Innodb_rows_deleted : 414019.00 436102.00 Innodb_rows_read : 566820798464.00 570683752448.00 Innodb_buffer_pool_read_requests: 173554647040.00 174656290816.00 Innodb_buffer_pool_reads : 2949612.00 3018912.00 Innodb_buffer_pool_pages_data : 2583711.00 2573302.00 Innodb_buffer_pool_pages_free : 0.00 0.00 Innodb_buffer_pool_pages_dirty : 18480.00 18114.00 Innodb_buffer_pool_pages_flushed: 76138168.00 76705496.00 Innodb_data_reads : 8187181.00 8266072.00 Innodb_data_writes : 3397593856.00 3422599424.00 Innodb_data_read : 134015893504.00 135308443648.00 Innodb_data_written : 5456696705024.00 5497065308160.00 Innodb_os_log_fsyncs : 5927693.00 5968877.00 Innodb_os_log_written : 2961621385216.00 2983398735872.00 history_list : 1204.00 3359.00 19
log_bytes_written : 3579557183488.00 3588847042560.00 log_bytes_flushed : 3579556921344.00 3588847042560.00 last_checkpoint : 3579222163456.00 3588507041792.00 queries_inside : 0.00 0.00 queries_queued : 0.00 0.00 read_views : 1.00 1.00 innodb_open_files : 300.00 300.00 innodb_log_waits : 0.00 0.00 Some Key Load Info Per Second TPS: 347 Com_select(s): 120 Com_insert(s): 336 Com_update(s): 11 20
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): 109494 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
key_buffer_read_hits %: 99.99 key_buffer_write_hits %: 45.22 Innodb_buffer_read_hits %: 99.99 Query_cache_hits %: 0 Thread_cache_hits %: 68.55 Top 10 Timed Events event_name wait time(picsecond) wait count wait/synch/cond/sql/mysql_bin_log::update_cond 34636103255361957 11871685 wait/synch/cond/mysys/my_thread_var::suspend 7008666931980153 25357 wait/io/file/myisam/dfile 3241117556648256 4230709979 wait/io/file/innodb/innodb_log_file 405232994361639 24434707 wait/io/file/sql/binlog 332197260993717 48314620 wait/synch/mutex/mysys/thr_lock::mutex 216558956818824 883945858 wait/io/file/innodb/innodb_data_file 174810511964073 776877 22
wait/synch/rwlock/innodb/index_tree_rw_lock 115895344353141 18558610 wait/synch/mutex/sql/log::lock_log 100521073634538 97564100 wait/synch/mutex/innodb/log_sys_mutex 39056470050048 150783962 Top 10 read file Events event_name read bytes read count wait/io/file/myisam/dfile 96111835055 2474810480 wait/io/file/sql/binlog 7338313313 36062679 wait/io/file/innodb/innodb_data_file 1292550144 78891 wait/io/file/myisam/kfile 13431700 59328 wait/io/file/sql/frm 6098 14 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 0 0 23
Top 10 write file Events myawr introduction event_name read bytes read count wait/io/file/innodb/innodb_data_file 24677580800 611919 wait/io/file/innodb/innodb_log_file 21779363840 24393556 wait/io/file/sql/binlog 7338313320 12251921 wait/io/file/myisam/dfile 1056829067 718535 wait/io/file/sql/slow_log 38778959 159751 wait/io/file/myisam/kfile 14356888 440529 wait/io/file/sql/frm 7145 84 wait/io/file/sql/binlog_index 203 7 wait/io/file/sql/log_event_info 0 0 wait/io/file/archive/metadata 0 0 24
OS Load Info myawr introduction Snap Time load1 load5 load15 2013-05-21 14:12:02 0.08 0.07 0.07 2013-05-21 14:22:02 1.16 0.87 0.45 2013-05-21 14:32:02 0.06 0.43 0.47 2013-05-21 14:42:02 0.02 0.09 0.25 2013-05-21 14:52:02 0.03 0.09 0.16 2013-05-21 15:02:03 0.05 0.05 0.09 2013-05-21 15:12:02 0.05 0.03 0.06 2013-05-21 15:22:02 0.10 0.09 0.06 2013-05-21 15:32:02 0.11 0.12 0.09 2013-05-21 15:42:03 0.00 0.02 0.05 2013-05-21 15:52:02 0.01 0.05 0.04 2013-05-21 16:02:02 0.17 0.10 0.05 2013-05-21 16:12:02 0.12 0.14 0.09 25
2013-05-21 16:22:03 0.31 0.13 0.10 2013-05-21 16:32:02 0.13 0.08 0.08 2013-05-21 16:42:02 0.10 0.09 0.08 2013-05-21 16:52:02 0.10 0.14 0.13 2013-05-21 17:02:03 0.09 0.06 0.08 2013-05-21 17:12:02 0.20 0.09 0.07 2013-05-21 17:22:02 0.15 0.09 0.06 2013-05-21 17:32:02 0.04 0.06 0.04 2013-05-21 17:42:02 0.00 0.03 0.02 2013-05-21 17:52:02 0.05 0.06 0.02 2013-05-21 18:02:02 1.03 0.54 0.20 2013-05-21 18:12:02 2.01 1.81 1.04 2013-05-21 18:22:02 1.12 1.27 1.11 2013-05-21 18:32:02 1.02 1.08 1.08 2013-05-21 18:42:02 1.03 1.06 1.07 2013-05-21 18:52:02 1.17 1.14 1.10 26
2013-05-21 19:02:02 1.00 1.03 1.05 2013-05-21 19:12:03 0.95 1.05 1.06 2013-05-21 19:22:02 0.00 0.23 0.63 2013-05-21 19:32:02 0.00 0.05 0.33 2013-05-21 19:42:03 0.17 0.08 0.19 2013-05-21 19:52:02 0.03 0.06 0.11 2013-05-21 20:02:02 0.35 0.14 0.10 2013-05-21 20:12:02 0.05 0.07 0.07 2013-05-21 20:22:02 0.01 0.03 0.03 2013-05-21 20:32:02 0.01 0.03 0.00 2013-05-21 20:42:02 0.06 0.02 0.00 2013-05-21 20:52:02 0.33 0.08 0.02 2013-05-21 21:02:02 0.14 0.11 0.03 2013-05-21 21:12:02 0.06 0.06 0.01 2013-05-21 21:22:02 1.39 1.18 0.54 2013-05-21 21:32:03 0.15 0.57 0.56 27
2013-05-21 21:42:02 0.05 0.12 0.31 2013-05-21 21:52:02 0.16 0.11 0.19 2013-05-21 22:02:02 0.15 0.13 0.16 2013-05-21 22:12:02 0.08 0.13 0.13 2013-05-21 22:22:02 0.11 0.10 0.09 2013-05-21 22:32:02 0.03 0.09 0.08 2013-05-21 22:42:02 0.02 0.04 0.06 2013-05-21 22:52:02 0.05 0.05 0.04 2013-05-21 23:02:02 0.16 0.12 0.07 2013-05-21 23:12:03 0.00 0.03 0.04 2013-05-21 23:22:02 0.08 0.04 0.02 2013-05-21 23:32:02 0.03 0.03 0.00 2013-05-21 23:42:02 0.28 0.13 0.04 2013-05-21 23:52:02 0.10 0.11 0.06 2013-05-22 00:00:02 0.02 0.04 0.03 28
OS CPU Info myawr introduction Snap Time user system idle iowait 2013-05-21 14:12:02 1.00 0.00 99.00 0.00 2013-05-21 14:22:02 7.00 1.00 93.00 0.00 2013-05-21 14:32:02 1.00 0.00 99.00 0.00 2013-05-21 14:42:02 1.00 0.00 99.00 0.00 2013-05-21 14:52:02 1.00 0.00 98.00 0.00 2013-05-21 15:02:03 1.00 0.00 98.00 0.00 2013-05-21 15:12:02 1.00 0.00 99.00 0.00 2013-05-21 15:22:02 1.00 0.00 99.00 0.00 2013-05-21 15:32:02 1.00 0.00 99.00 0.00 2013-05-21 15:42:03 1.00 0.00 99.00 0.00 2013-05-21 15:52:02 1.00 0.00 99.00 0.00 2013-05-21 16:02:02 1.00 0.00 99.00 0.00 2013-05-21 16:12:02 1.00 0.00 98.00 0.00 29
2013-05-21 16:22:03 1.00 0.00 99.00 0.00 2013-05-21 16:32:02 1.00 0.00 99.00 0.00 2013-05-21 16:42:02 1.00 0.00 98.00 0.00 2013-05-21 16:52:02 1.00 0.00 99.00 0.00 2013-05-21 17:02:03 1.00 0.00 99.00 0.00 2013-05-21 17:12:02 1.00 0.00 99.00 0.00 2013-05-21 17:22:02 1.00 0.00 99.00 0.00 2013-05-21 17:32:02 1.00 0.00 99.00 0.00 2013-05-21 17:42:02 1.00 0.00 99.00 0.00 2013-05-21 17:52:02 1.00 0.00 99.00 0.00 2013-05-21 18:02:02 2.00 3.00 95.00 0.00 2013-05-21 18:12:02 6.00 3.00 91.00 0.00 2013-05-21 18:22:02 2.00 3.00 95.00 0.00 2013-05-21 18:32:02 2.00 2.00 95.00 0.00 2013-05-21 18:42:02 2.00 3.00 95.00 0.00 2013-05-21 18:52:02 3.00 3.00 94.00 0.00 30
2013-05-21 19:02:02 3.00 3.00 94.00 0.00 2013-05-21 19:12:03 2.00 3.00 95.00 0.00 2013-05-21 19:22:02 1.00 0.00 99.00 0.00 2013-05-21 19:32:02 1.00 0.00 99.00 0.00 2013-05-21 19:42:03 1.00 0.00 99.00 0.00 2013-05-21 19:52:02 1.00 0.00 99.00 0.00 2013-05-21 20:02:02 1.00 0.00 99.00 0.00 2013-05-21 20:12:02 1.00 0.00 99.00 0.00 2013-05-21 20:22:02 1.00 0.00 99.00 0.00 2013-05-21 20:32:02 1.00 0.00 99.00 0.00 2013-05-21 20:42:02 1.00 0.00 99.00 0.00 2013-05-21 20:52:02 1.00 0.00 99.00 0.00 2013-05-21 21:02:02 1.00 0.00 99.00 0.00 2013-05-21 21:12:02 1.00 0.00 99.00 0.00 2013-05-21 21:22:02 4.00 0.00 95.00 0.00 2013-05-21 21:32:03 1.00 0.00 99.00 0.00 31
2013-05-21 21:42:02 1.00 0.00 99.00 0.00 2013-05-21 21:52:02 1.00 0.00 99.00 0.00 2013-05-21 22:02:02 1.00 0.00 99.00 0.00 2013-05-21 22:12:02 1.00 0.00 99.00 0.00 2013-05-21 22:22:02 4.00 0.00 96.00 0.00 2013-05-21 22:32:02 0.00 0.00 99.00 0.00 2013-05-21 22:42:02 1.00 0.00 99.00 0.00 2013-05-21 22:52:02 4.00 0.00 96.00 0.00 2013-05-21 23:02:02 1.00 0.00 99.00 0.00 2013-05-21 23:12:03 1.00 0.00 99.00 0.00 2013-05-21 23:22:02 1.00 0.00 99.00 0.00 2013-05-21 23:32:02 1.00 0.00 99.00 0.00 2013-05-21 23:42:02 0.00 0.00 99.00 0.00 2013-05-21 23:52:02 1.00 0.00 99.00 0.00 2013-05-22 00:00:02 1.00 0.00 99.00 0.00 32
OS IO Info myawr introduction Snap Time rd_ios_s wr_ios_s rkbs wkbs queue svc_t busy 2013-05-21 14:12:02 0.00 10.79 0.00 758.94 0.00 0.17 0.18 2013-05-21 14:22:02 8.01 13.01 896.75 1092.91 0.02 0.95 2.00 2013-05-21 14:32:02 0.00 21.34 0.00 1135.44 0.01 0.15 0.32 2013-05-21 14:42:02 0.00 13.07 0.00 699.79 0.00 0.08 0.10 2013-05-21 14:52:02 0.00 30.00 0.00 1008.75 0.00 0.06 0.19 2013-05-21 15:02:03 0.00 16.64 0.00 1201.31 0.00 0.16 0.26 2013-05-21 15:12:02 0.00 21.50 0.00 827.77 0.00 0.04 0.09 2013-05-21 15:22:02 0.00 16.39 0.00 952.29 0.00 0.18 0.29 2013-05-21 15:32:02 0.00 15.80 0.00 709.87 0.00 0.24 0.37 2013-05-21 15:42:03 0.00 17.94 0.00 1012.12 0.01 0.16 0.28 2013-05-21 15:52:02 0.00 24.48 0.00 1317.81 0.01 0.12 0.31 2013-05-21 16:02:02 0.00 29.18 0.00 1068.69 0.00 0.06 0.18 2013-05-21 16:12:02 0.00 27.66 0.00 1453.09 0.01 0.17 0.46 33
2013-05-21 16:22:03 0.00 26.09 0.00 898.30 0.01 0.09 0.23 2013-05-21 16:32:02 0.00 25.17 0.00 944.90 0.00 0.12 0.29 2013-05-21 16:42:02 0.00 25.67 0.00 1100.90 0.00 0.08 0.21 2013-05-21 16:52:02 0.00 28.24 0.00 952.47 0.00 0.03 0.09 2013-05-21 17:02:03 0.00 25.24 0.00 1060.12 0.01 0.38 0.95 2013-05-21 17:12:02 0.97 12.58 3.87 789.68 0.01 0.71 0.97 2013-05-21 17:22:02 0.00 28.08 0.00 1460.18 0.01 0.16 0.44 2013-05-21 17:32:02 0.00 21.69 0.00 860.56 0.00 0.04 0.09 2013-05-21 17:42:02 0.00 12.08 0.00 929.81 0.00 0.08 0.10 2013-05-21 17:52:02 0.00 42.77 0.00 1078.16 0.01 0.12 0.50 2013-05-21 18:02:02 0.00 31.88 0.00 791.25 0.00 0.06 0.19 2013-05-21 18:12:02 0.00 36.65 0.00 669.07 0.00 0.08 0.28 2013-05-21 18:22:02 0.00 24.41 0.00 713.62 0.00 0.12 0.28 2013-05-21 18:32:02 0.00 36.65 0.00 1369.39 0.00 0.07 0.27 2013-05-21 18:42:02 0.00 53.48 0.00 1568.73 0.01 0.09 0.47 2013-05-21 18:52:02 0.00 30.54 0.00 1166.83 0.01 0.10 0.32 34
2013-05-21 19:02:02 0.00 29.99 0.00 711.70 0.00 0.07 0.20 2013-05-21 19:12:03 0.00 39.72 0.00 835.10 0.02 0.05 0.18 2013-05-21 19:22:02 0.00 15.35 0.00 818.76 0.00 0.07 0.10 2013-05-21 19:32:02 0.00 12.40 0.00 694.16 0.00 0.15 0.19 2013-05-21 19:42:03 0.00 12.12 0.00 758.13 0.00 0.27 0.33 2013-05-21 19:52:02 0.00 22.42 0.00 1588.17 0.00 0.12 0.28 2013-05-21 20:02:02 0.00 24.78 0.00 800.99 0.00 0.08 0.20 2013-05-21 20:12:02 0.00 8.87 0.00 692.05 0.00 0.10 0.09 2013-05-21 20:22:02 0.00 11.81 0.00 687.25 0.00 0.00 0.00 2013-05-21 20:32:02 0.00 13.93 0.00 679.25 0.00 0.06 0.09 2013-05-21 20:42:02 0.00 11.18 0.00 708.35 0.00 0.08 0.09 2013-05-21 20:52:02 0.00 11.56 0.00 704.98 0.00 0.08 0.10 2013-05-21 21:02:02 0.00 25.96 0.00 797.22 0.00 0.04 0.09 2013-05-21 21:12:02 0.00 33.67 0.00 1442.30 0.00 0.08 0.27 2013-05-21 21:22:02 0.00 22.88 0.00 724.67 0.00 0.17 0.38 2013-05-21 21:32:03 0.00 24.50 0.00 840.52 0.00 0.04 0.09 35
2013-05-21 21:42:02 0.00 9.62 0.00 708.26 0.00 0.00 0.00 2013-05-21 21:52:02 0.00 12.12 0.00 1006.60 0.00 0.15 0.19 2013-05-21 22:02:02 0.00 14.12 0.00 988.72 0.00 0.08 0.12 2013-05-21 22:12:02 0.00 12.41 0.00 794.48 0.01 0.25 0.31 2013-05-21 22:22:02 0.00 13.33 0.00 693.33 0.00 0.13 0.18 2013-05-21 22:32:02 0.00 25.12 0.00 1142.22 0.00 0.07 0.17 2013-05-21 22:42:02 0.00 22.32 0.00 684.50 0.00 0.05 0.11 2013-05-21 22:52:02 0.00 10.28 0.00 706.27 0.00 0.18 0.19 2013-05-21 23:02:02 0.00 17.73 0.00 617.07 0.00 0.10 0.18 2013-05-21 23:12:03 0.00 16.07 0.00 1072.92 0.00 0.00 0.00 2013-05-21 23:22:02 0.00 18.52 0.00 802.40 0.00 0.11 0.21 2013-05-21 23:32:02 0.00 18.64 0.00 1168.41 0.01 0.17 0.31 2013-05-21 23:42:02 0.00 22.02 0.00 660.55 0.00 0.08 0.18 2013-05-21 23:52:02 0.00 26.91 0.00 1877.74 0.01 0.15 0.40 2013-05-22 00:00:02 0.00 56.86 0.00 1250.84 0.01 0.12 0.66 36
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 2013-05-21 14:12:02 0.00 0.00 676864.00 2111744.00 /dev/sdb1 1967606.00 1621472.00 87.00 /data 2013-05-21 14:22:02 0.00 0.00 2247584.00 5089303.00 /dev/sdb1 1967606.00 1621705.00 87.00 /data 2013-05-21 14:32:02 0.00 0.00 1494519.00 5022274.00 /dev/sdb1 1967606.00 1621941.00 87.00 /data 2013-05-21 14:42:02 0.00 0.00 445235.00 399025.00 /dev/sdb1 1967606.00 1622174.00 87.00 /data 2013-05-21 14:52:02 0.00 0.00 869100.00 1750227.00 /dev/sdb1 1967606.00 1622424.00 87.00 /data 2013-05-21 15:02:03 0.00 0.00 1028981.00 3944281.00 /dev/sdb1 1967606.00 1622659.00 87.00 /data 2013-05-21 15:12:02 0.00 0.00 707387.00 748465.00 /dev/sdb1 1967606.00 1622892.00 87.00 /data 2013-05-21 15:22:02 8.00 0.00 678920.00 622935.00 /dev/sdb1 1967606.00 1623142.00 87.00 /data 2013-05-21 15:32:02 0.00 0.00 506476.00 459623.00 /dev/sdb1 1967606.00 1623375.00 87.00 /data 2013-05-21 15:42:03 0.00 0.00 527133.00 565192.00 /dev/sdb1 1967606.00 1623609.00 87.00 /data 2013-05-21 15:52:02 0.00 0.00 678439.00 624455.00 /dev/sdb1 1967606.00 1623861.00 87.00 /data 2013-05-21 16:02:02 2.00 0.00 725471.00 1549546.00 /dev/sdb1 1967606.00 1624121.00 87.00 /data 2013-05-21 16:12:02 0.00 0.00 1075014.00 3930267.00 /dev/sdb1 1967606.00 1624369.00 87.00 /data 37
2013-05-21 16:22:03 0.00 0.00 857507.00 2321507.00 /dev/sdb1 1967606.00 1624622.00 87.00 /data 2013-05-21 16:32:02 0.00 0.00 679753.00 628464.00 /dev/sdb1 1967606.00 1624868.00 88.00 /data 2013-05-21 16:42:02 0.00 0.00 812187.00 1416445.00 /dev/sdb1 1967606.00 1625115.00 88.00 /data 2013-05-21 16:52:02 5.00 0.00 559153.00 511832.00 /dev/sdb1 1967606.00 1625359.00 88.00 /data 2013-05-21 17:02:03 0.00 0.00 601014.00 550670.00 /dev/sdb1 1967606.00 1625588.00 88.00 /data 2013-05-21 17:12:02 0.00 0.00 569139.00 709423.00 /dev/sdb1 1967606.00 1625824.00 88.00 /data 2013-05-21 17:22:02 0.00 0.00 612136.00 561918.00 /dev/sdb1 1967606.00 1626066.00 88.00 /data 2013-05-21 17:32:02 0.00 0.00 722392.00 719460.00 /dev/sdb1 1967606.00 1626321.00 88.00 /data 2013-05-21 17:42:02 0.00 0.00 775741.00 2252267.00 /dev/sdb1 1967606.00 1626548.00 88.00 /data 2013-05-21 17:52:02 0.00 0.00 1008192.00 3360773.00 /dev/sdb1 1967606.00 1626777.00 88.00 /data 2013-05-21 18:02:02 0.00 0.00 514139.00 477544.00 /dev/sdb1 1967606.00 1626999.00 88.00 /data 2013-05-21 18:12:02 0.00 0.00 384897.00 356495.00 /dev/sdb1 1967606.00 1627220.00 88.00 /data 2013-05-21 18:22:02 0.00 0.00 504705.00 466993.00 /dev/sdb1 1967606.00 1627419.00 88.00 /data 2013-05-21 18:32:02 0.00 0.00 446308.00 411714.00 /dev/sdb1 1967606.00 1627619.00 88.00 /data 2013-05-21 18:42:02 0.00 0.00 525266.00 488981.00 /dev/sdb1 1967606.00 1627823.00 88.00 /data 2013-05-21 18:52:02 0.00 0.00 636915.00 596432.00 /dev/sdb1 1967606.00 1628053.00 88.00 /data 38
2013-05-21 19:02:02 0.00 1.00 597816.00 2079870.00 /dev/sdb1 1967606.00 1628255.00 88.00 /data 2013-05-21 19:12:03 0.00 0.00 544558.00 510752.00 /dev/sdb1 1967606.00 1628478.00 88.00 /data 2013-05-21 19:22:02 0.00 0.00 441585.00 398257.00 /dev/sdb1 1967606.00 1628687.00 88.00 /data 2013-05-21 19:32:02 0.00 0.00 416017.00 369934.00 /dev/sdb1 1967606.00 1628878.00 88.00 /data 2013-05-21 19:42:03 0.00 0.00 413446.00 378489.00 /dev/sdb1 1967606.00 1629081.00 88.00 /data 2013-05-21 19:52:02 0.00 0.00 658825.00 665914.00 /dev/sdb1 1967606.00 1629289.00 88.00 /data 2013-05-21 20:02:02 0.00 0.00 618088.00 1368997.00 /dev/sdb1 1967606.00 1629505.00 88.00 /data 2013-05-21 20:12:02 0.00 0.00 494640.00 449590.00 /dev/sdb1 1967606.00 1629701.00 88.00 /data 2013-05-21 20:22:02 0.00 0.00 434987.00 401775.00 /dev/sdb1 1967606.00 1629902.00 88.00 /data 2013-05-21 20:32:02 0.00 0.00 719177.00 2208004.00 /dev/sdb1 1967606.00 1630079.00 88.00 /data 2013-05-21 20:42:02 0.00 0.00 544593.00 494996.00 /dev/sdb1 1967606.00 1630267.00 88.00 /data 2013-05-21 20:52:02 0.00 0.00 627816.00 940006.00 /dev/sdb1 1967606.00 1630488.00 88.00 /data 2013-05-21 21:02:02 0.00 0.00 410082.00 370759.00 /dev/sdb1 1967606.00 1630663.00 88.00 /data 2013-05-21 21:12:02 0.00 0.00 487333.00 444037.00 /dev/sdb1 1967606.00 1630856.00 88.00 /data 2013-05-21 21:22:02 0.00 0.00 425702.00 400337.00 /dev/sdb1 1967606.00 1631065.00 88.00 /data 2013-05-21 21:32:03 0.00 0.00 597705.00 541170.00 /dev/sdb1 1967606.00 1631253.00 88.00 /data 39
2013-05-21 21:42:02 0.00 0.00 484211.00 440031.00 /dev/sdb1 1967606.00 1631449.00 88.00 /data 2013-05-21 21:52:02 0.00 0.00 651420.00 682513.00 /dev/sdb1 1967606.00 1631696.00 88.00 /data 2013-05-21 22:02:02 0.00 0.00 531016.00 481571.00 /dev/sdb1 1967606.00 1631895.00 88.00 /data 2013-05-21 22:12:02 0.00 0.00 406891.00 364716.00 /dev/sdb1 1967606.00 1632103.00 88.00 /data 2013-05-21 22:22:02 0.00 0.00 379475.00 412177.00 /dev/sdb1 1967606.00 1632302.00 88.00 /data 2013-05-21 22:32:02 0.00 0.00 454113.00 410740.00 /dev/sdb1 1967606.00 1632500.00 88.00 /data 2013-05-21 22:42:02 0.00 0.00 461560.00 788269.00 /dev/sdb1 1967606.00 1632689.00 88.00 /data 2013-05-21 22:52:02 0.00 1.00 630641.00 660117.00 /dev/sdb1 1967606.00 1632903.00 88.00 /data 2013-05-21 23:02:02 0.00 0.00 460634.00 418825.00 /dev/sdb1 1967606.00 1633089.00 88.00 /data 2013-05-21 23:12:03 0.00 0.00 1015481.00 4152029.00 /dev/sdb1 1967606.00 1633301.00 88.00 /data 2013-05-21 23:22:02 0.00 0.00 432938.00 391443.00 /dev/sdb1 1967606.00 1633499.00 88.00 /data 2013-05-21 23:32:02 0.00 0.00 624282.00 575975.00 /dev/sdb1 1967606.00 1633683.00 88.00 /data 2013-05-21 23:42:02 0.00 0.00 408901.00 372704.00 /dev/sdb1 1967606.00 1633909.00 88.00 /data 2013-05-21 23:52:02 0.00 0.00 616048.00 625541.00 /dev/sdb1 1967606.00 1634151.00 88.00 /data 2013-05-22 00:00:02 4.00 0.00 417361.00 381697.00 /dev/sdb1 1967606.00 1634326.00 88.00 /data 40
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 175180854702907 54732 2013-05- 2013-05- 3170 test 21 21 5 09:32:56 19:14:40 11346.3 0.526151 6573.05 0.35612 0 0 update xxxx_test... 878323190489624 5142 336017598366088 7134 133096531796313 20993 2013-05- 2013-05- test 21 21 894 5128.98 6.98463 0.044442 0.0000632544 894 1 08:55:12 21:27:49 2013-05- 2013-05- test 21 21 151 1861.87 15.2466 0.007739 0.0000697379 151 1 08:54:54 21:27:23 2013-05- 2013-05- test 21 21 293 265.682 0.992137 0.031267 0.000119276 145903 487.094 21:48:26 23:15:39 SELECT count(id) as... SELECT count(id) as... SELECT b.id, b... 145654640257172 test 2013-05- 2013-05- 20 239.178 16.8094 0.00456 0.000316473 0 0 INSERT INTO 41
98162 21 21 AAAA_A... 01:48:45 21:48:35 133129955690393 81386 2013-05- 2013-05- 3211 test 21 21 66 00:03:31 23:59:56 65.3112 0.000204002 19.4566 0.0000807304 321166 1 select last_insert_i... 103545740566486 09809 2013-05- 2013-05- test 21 21 1 41.0879 41.0879 0.00011 0.00011 20 20 18:19:21 18:19:21 SELECT MI... ID, 497734220985120 500 963783068970645 8981 141261618006005 46731 2013-05- 2013-05- test 21 21 1913 17.1919 0.0416167 0.202465 0.000131501 18598 9.82808 00:05:20 23:51:39 2013-05- 2013-05- test 21 22 76 8.10788 0.140929 0.008022 0.000119276 38000 500 23:39:24 00:01:16 2013-05- 2013-05- test 21 21 1 4.76375 4.76375 0.000105 0.000105 0 0 21:47:48 21:47:48 select * from ( s... SELECT b.id, b... Update test. bat... 42
174712703981244 73757 168324708332283 84350 myawr introduction Update test.exce... select t.operator_i... 131917487054075 57481 126705722903004 57907 select * from liv... 730772576490268 1358 441461047919072 052 2013-05- 2013-05- test 21 21 1 2.35301 2.35301 0.000077 0.000077 0 0 23:39:04 23:39:04 2013-05- 2013-05- test 21 21 25 2.11104 0.134218 0.005074 0.000214202 154 7.70056 10:09:00 18:27:46 2013-05- 2013-05- test 21 21 51 1.18354 0.0243324 0.01047 0.0000697379 7 0.992137 select t.id,... 09:54:07 21:21:21 2013-05- 2013-05- test 21 21 195 0.907769 0.00486338 0.015995 0.0000934555 161 0.992137 01:48:53 23:49:09 2013-05- 2013-05- test 21 21 41 0.888554 0.0231737 0.002063 0.0000664171 7 0.992137 select t.id,... 09:55:48 21:21:21 2013-05- 2013-05- test 32 0.707033 0.0231737 0.001794 0.0000632544 7 0.992137 select t.id,... 21 21 43
595949646946529 0778 784003635829748 5213 166759223910261 7093 458870709824252 2987 09:55:15 21:21:21 2013-05- 2013-05- test 21 21 2453 0.627288 0.000273382 0.151339 0.0000768861 2298 0.992137 00:16:17 23:58:42 2013-05- 2013-05- information_sch 21 22 590 0.385329 0.000799713 0.033036 0.0000632544 131570 223 ema 14:12:02 00:01:02 2013-05- 2013-05- test 21 21 13 0.306861 0.0481765 0.000869 0.0000732248 13 1 09:39:35 17:58:50 2013-05- 2013-05- information_sch 21 22 590 0.29407 0.000568341 0.054093 0.000113596 590 1 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
17518085470290754732 update test set plag = 2, uptime = now(), opid = 1 where ordid = 116459410 8783231904896245142 SELECT count(id) as count FROM zzz_log where code='200001' and ccdoe='100001' 3360175983660887134 SELECT count(id) as count FROM zzzz where code='200001' 4588707098242522987 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
6. Contact me Any questions,pls contact me freely. EMAIL: qiuwsh@gmail.com Q Q : 570182914 Phone: (+86)13817963180 46