noodba同学写的基于mysql的 WORKLOAD REPOSITORY工具,目前还没有开源 非常的给力！！
主要思想为一台管理机器,集中了所有client端的数据,client 为mysql database 使用host_id来区分 如 host_id=1代表mysql database 1,host_id=2 代表mysql database 2.
具体采集AWR过程如下：
[mysql@hadooptest2 myawr]$ perl myawrrpt.pl
==========================================================================================
Info :
Created By qwsh (www.noodba.com).
References: Oracle awr
Usage :
Command line options :
-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 dbauser).
-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).
Sample :
shell> perl myawrrpt.pl -p 111111 -lh 192.168.1.111 -I 11
==========================================================================================
[mysql@hadooptest2 myawr]$ perl myawrrpt.pl -u myawruser -p xxxxx -P xxxxx -lh 10.0.1.92 -I 4
===================================================
| Welcome to use the myawrrpt tool !
| Date: 2013-05-06
|
| Hostname is: pis24
| Ip addr is: 10.0.2.24
| Port is: 3306
| Db role is: master
|Server version is: 5.5.25a
| Uptime is: 0y 3m 15d 2h 10mi 41s
|
| Min snap_id is: 1
| Min snap_time is: 2013-05-03 10:41:41
| Max snap_id is: 4546
| Max snap_time is: 2013-05-06 14:33:11
| snap interval is: 59s
===================================================
Listing the last 2 days of Completed Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
snap_id: 792 snap_time : 2013-05-03 23:59:06
snap_id: 855 snap_time : 2013-05-04 01:02:05
snap_id: 918 snap_time : 2013-05-04 02:05:06
snap_id: 981 snap_time : 2013-05-04 03:08:06
snap_id: 1044 snap_time : 2013-05-04 04:11:06
snap_id: 1107 snap_time : 2013-05-04 05:14:06
snap_id: 1170 snap_time : 2013-05-04 06:17:06
snap_id: 1233 snap_time : 2013-05-04 07:20:06
snap_id: 1296 snap_time : 2013-05-04 08:23:06
snap_id: 1359 snap_time : 2013-05-04 09:26:07
snap_id: 1422 snap_time : 2013-05-04 10:29:06
snap_id: 1485 snap_time : 2013-05-04 11:32:07
snap_id: 1548 snap_time : 2013-05-04 12:35:06
snap_id: 1611 snap_time : 2013-05-04 13:38:07
snap_id: 1674 snap_time : 2013-05-04 14:41:07
snap_id: 1737 snap_time : 2013-05-04 15:44:07
snap_id: 1800 snap_time : 2013-05-04 16:47:07
snap_id: 1863 snap_time : 2013-05-04 17:50:08
snap_id: 1926 snap_time : 2013-05-04 18:53:08
snap_id: 1989 snap_time : 2013-05-04 19:56:08
snap_id: 2052 snap_time : 2013-05-04 20:59:08
snap_id: 2115 snap_time : 2013-05-04 22:02:07
snap_id: 2178 snap_time : 2013-05-04 23:05:08
snap_id: 2241 snap_time : 2013-05-05 00:08:08
snap_id: 2304 snap_time : 2013-05-05 01:11:08
snap_id: 2367 snap_time : 2013-05-05 02:14:08
snap_id: 2430 snap_time : 2013-05-05 03:17:08
snap_id: 2493 snap_time : 2013-05-05 04:20:08
snap_id: 2556 snap_time : 2013-05-05 05:23:08
snap_id: 2619 snap_time : 2013-05-05 06:26:08
snap_id: 2682 snap_time : 2013-05-05 07:29:09
snap_id: 2745 snap_time : 2013-05-05 08:32:09
snap_id: 2808 snap_time : 2013-05-05 09:35:09
snap_id: 2871 snap_time : 2013-05-05 10:38:09
snap_id: 2934 snap_time : 2013-05-05 11:41:09
snap_id: 2997 snap_time : 2013-05-05 12:44:09
snap_id: 3060 snap_time : 2013-05-05 13:47:09
snap_id: 3123 snap_time : 2013-05-05 14:50:09
snap_id: 3186 snap_time : 2013-05-05 15:53:10
snap_id: 3249 snap_time : 2013-05-05 16:56:10
snap_id: 3312 snap_time : 2013-05-05 17:59:10
snap_id: 3375 snap_time : 2013-05-05 19:02:10
snap_id: 3438 snap_time : 2013-05-05 20:05:10
snap_id: 3501 snap_time : 2013-05-05 21:08:10
snap_id: 3564 snap_time : 2013-05-05 22:11:10
snap_id: 3627 snap_time : 2013-05-05 23:14:10
snap_id: 3690 snap_time : 2013-05-06 00:17:11
snap_id: 3753 snap_time : 2013-05-06 01:20:11
snap_id: 3816 snap_time : 2013-05-06 02:23:11
snap_id: 3879 snap_time : 2013-05-06 03:26:10
snap_id: 3942 snap_time : 2013-05-06 04:29:11
snap_id: 4005 snap_time : 2013-05-06 05:32:11
snap_id: 4068 snap_time : 2013-05-06 06:35:11
snap_id: 4131 snap_time : 2013-05-06 07:38:11
snap_id: 4194 snap_time : 2013-05-06 08:41:12
snap_id: 4257 snap_time : 2013-05-06 09:44:12
snap_id: 4320 snap_time : 2013-05-06 10:47:11
snap_id: 4383 snap_time : 2013-05-06 11:50:12
snap_id: 4446 snap_time : 2013-05-06 12:53:11
snap_id: 4509 snap_time : 2013-05-06 13:56:11
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:1107
Begin Snapshot Id specified:1107
Enter value for end_snap:1863
End Snapshot Id specified:1863
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
Enter value for report_name:yihaodian_mysqlawr.html
Using the report name :yihaodian_mysqlawr.html
Generating the mysql report for this analysis ...
Generating the mysql report Successfully.这套测试环境采用一台管理机器,表的配置如下：
mysql> use myawr Database changed 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.00 sec) mysql> select * from myawr_host; +----+-----------+-----------+------+---------+---------+-----------------------+---------------------+ | id | host_name | ip_addr | port | db_role | version | uptime | check_time | +----+-----------+-----------+------+---------+---------+-----------------------+---------------------+ | 3 | db-74 | 10.0.0.74 | 3306 | master | 5.5.27 | 0y 1m 8d 22h 32mi 33s | 2013-05-03 13:09:02 | | 4 | pis24 | 10.0.2.24 | 3306 | master | 5.5.25a | 0y 3m 15d 1h 46mi 41s | 2013-05-06 14:09:12 | +----+-----------+-----------+------+---------+---------+-----------------------+---------------------+ 2 rows in set (0.00 sec)
可以看到host_id=3监控的机器为10.0.0.74 依此类推, 1…xxx 可以无限扩展。
下面为client 端部署的脚本：
[mysql@DCB-SRV-0220 ~]$ crontab -l
* * * * * perl /data/mysql/sh/myawr.pl -u dbauser -p xxxxx -lh 10.0.2.24 -P 3306 -tu myawruser -tp xxxxxx -TP 3306 -th 10.0.1.92 -n eth2 -d c0d1p1 -I 4 >> /data/mysql/sh/myawr_pl.log 2>&1
myawr会自动上传这台mysql机器的所有状态值到管理机器。
下面以10.0.2.24这台mysql 作为一个样本 展示一下AWR报告,目前版本为version 1.0 后期会在github上开源
