mysqlawr

MyAWR : MySQL Workload Report V3 release

December 2, 2014 Architecture, MYSQL, performance No comments

myawr-v3 release now !

history of myawr:

————————–
v1: create by noodba

mysql load information and system information 

v2: create by louis liu

myawr_v2 add :

1 more os information module.

2 multiple instance support (extend table by db_port)

3 modified some bugs (mistake value)

4 add different statistics views

v3: create by louis liu

base on myawr_v2

myawr_v3 add sys schema information

(statment statistics/index&table stat/IO latency/file&table IO stat and so on)

including performance schema and information schema

so you need open performance schema on your mysql database

myawr_v3 now support mysql 5.6 GA and will continue support mysql feature version

before install myawr_v3 you need run sys_schema to create formatted views for data collecting.

Overview: Myawr_v3 mysql performance web show

Download:https://github.com/ylouis83/myawr/tree/master

Some data collection display

August 21, 2014 MYSQL, performance No comments , , ,

Reference Oracle AWR. These two was developed by python and perl.

Both of these are following data collection method.

Use these tools can help quick identify problem.

MyAWR v2 –collection of mysql and os information

MongoAWR v1 — collection of mongodb and os information

MyAWR another mysql awr –That is Great !!

May 22, 2013 Architecture, MYSQL, performance No comments

MyAWR 开源了,第一版的reference 已经放出, tx to noodba

MyAWR_Reference_Guid
slideshare地址:MyAWR (awr of MySQL)
MyAWR template:MySQL WorkLoad Report

MySQL AWR – Myawr

May 6, 2013 Architecture, MYSQL, performance No comments

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上开源

Yihaodian_Mysql_AWR.