Tag Archives: myawr

怎么快速部署一套myawr

怎么快速部署一套myawr,最少资源配置:
linux server一台,DBI,DBD,如果没有mysql的话也要mysql安装包。

下面介绍一下怎么部署,这个环境是Oracle Linux Server release 5.8,没有mysql,同时准备把myawr的采集对象和存储数据都放在这台上面,正常情况下采集对象上只需安装perl-dbd-mysql,然后部署监控脚本就行了,他只对所监控的mysql数据库进行查询,不会修改任何东西:

Myawr的架构图:
myawr_archit1

myawr_archit2

我的资源列表

[root@oel58 myawr]# ls -al
total 75848
drwxr-xr-x  2 root root     4096 Jul  7 17:01 .
drwxr-x--- 18 root root     4096 Jul  7 17:01 ..
-rw-r--r--  1 root root   133427 Jul  7 17:01 DBD-mysql-4.018.tar.gz
-rw-r--r--  1 root root   571379 Jul  7 17:01 DBI-1.615.tar.gz
-rw-r--r--  1 root root    32485 Jul  7 17:01 myawr.pl
-rw-r--r--  1 root root    64206 Jul  7 17:01 myawrrpt.pl
-rw-r--r--  1 root root    35706 Jul  7 17:01 myawrsrpt.pl
-rw-r--r--  1 root root 17899890 Jul  7 17:01 MySQL-client-5.5.29-2.rhel5.i386.rpm
-rw-r--r--  1 root root  3606645 Jul  7 17:01 MySQL-devel-5.5.29-2.rhel5.i386.rpm
-rw-r--r--  1 root root 54638727 Jul  7 17:01 MySQL-server-5.5.29-2.rhel5.i386.rpm
-rw-r--r--  1 root root   549438 Jul  7 17:01 pt-query-digest

以下是安装步骤(下面的操作我是在同一个虚拟机上进行的操作):

1 环境检查:

[root@oel58 ~]# rpm -qa | grep -i mysql
[root@oel58 ~]# 
[root@oel58 ~]# rpm -qa | grep -i dbd
[root@oel58 ~]# 
[root@oel58 ~]# rpm -qa | grep -i dbi
[root@oel58 ~]# 
[root@oel58 ~]# perl -version

This is perl, v5.8.8 built for i386-linux-thread-multi

Copyright 1987-2006, Larry Wall

[root@oel58 ~]# uname -a
Linux oel58 2.6.32-300.10.1.el5uek #1 SMP Wed Feb 22 17:22:40 EST 2012 i686 i686 i386 GNU/Linux
[root@oel58 ~]# 
[root@oel58 ~]# 
[root@oel58 ~]# getconf LONG_BIT
32

2 安装mysql

最简单的安装法就是使用rpm安装,这个可以在
http://downloads.mysql.com/archives.php?p=mysql-5.5上下载。

[root@oel58 ~]# rpm -ivh MySQL-server-5.5.29-2.rhel5.i386.rpm
Preparing...                ########################################### [100%]
   1:MySQL-server           ########################################### [100%]


[root@oel58 ~]# 
[root@oel58 ~]# 
[root@oel58 ~]# rpm -ivh MySQL-client-5.5.29-2.rhel5.i386.rpm
Preparing...                ########################################### [100%]
   1:MySQL-client           ########################################### [100%]

[root@oel58 ~]# rpm -ivh MySQL-devel-5.5.29-2.rhel5.i386.rpm
Preparing...                ########################################### [100%]
   1:MySQL-devel            ########################################### [100%]
[root@oel58 ~]# service mysql start
Starting MySQL...                                          [  OK  ]
[root@oel58 ~]# /usr/bin/mysqladmin -u root password '123456'


[root@oel58 ~]# cat /etc/my.cnf 
[mysqld]
performance_schema
log_bin=on
innodb_file_per_table=1
slow_query_log=1
long_query_time=1
log_queries_not_using_indexes=0


MySQL 5.5新增PERFORMANCE_SCHEMA  ,主要用于收集数据库服务器性能参数。包括等待的信息,事件汇总信息等。
myawr不是强制要求开启PERFORMANCE_SCHEMA的,开启的话,他会采集一些数据,在展示时会有top events信息。

3 安装mysql dbd(依赖dbi)

cd DBI-1.615
perl Makefile.PL 
make
make install

mkdir /tmp/mysqldbd-install 
  cp /usr/lib/mysql/*.a  /tmp/mysqldbd-install
cd DBD-mysql-4.018
  perl Makefile.PL --libs="-L/tmp/mysqldbd-install -lmysqlclient" 
  make 
  make test 
  make install 


检查安装模块:

[root@oel58 aaa]# cat listmodle.pl 
#!usr/bin/perl
use strict;
use warnings;

use ExtUtils::Installed;


my $inst = ExtUtils::Installed->new();
print join "\n", $inst->modules();


[root@oel58 ~]# perl listmodle.pl 
DBD::mysql
DBI
Perl


检查DBD的可用性:

CREATE TABLE `test` (
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO test (name) VALUES ('hello world');


[root@oel58 ~]# cat dbd.pl 
#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect( "DBI:mysql:database=test;host=localhost",
	"root", "123456", { 'RaiseError' => 1 } );
#my $rows  = $dbh->do("INSERT INTO test (name) VALUES ('hello world')");
my $query = $dbh->prepare("SELECT name FROM test");
$query->execute();

while ( $ary = $query->fetchrow_arrayref() ) {
	print (  $ary->[0] . "\n");
}


如果mysql是自己编译安装或者二进制安装的,可以直接:
下面这个因为我是rpm安装的,有冲突

[root@oel58 ~]# yum install perl-DBD-MySQL
Loaded plugins: rhnplugin, security
This system is not registered with ULN.
ULN support will be disabled.
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package perl-DBD-MySQL.i386 0:3.0007-2.el5 set to be updated
--> Processing Dependency: perl(DBI) for package: perl-DBD-MySQL
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15) for package: perl-DBD-MySQL
--> Processing Dependency: libmysqlclient.so.15 for package: perl-DBD-MySQL
--> Running transaction check
---> Package mysql.i386 0:5.0.95-1.el5_7.1 set to be updated
---> Package perl-DBI.i386 0:1.52-2.el5 set to be updated
--> Processing Conflict: MySQL-server conflicts mysql
--> Processing Conflict: MySQL-client conflicts mysql
--> Processing Conflict: mysql conflicts MySQL
--> Finished Dependency Resolution
MySQL-client-5.5.29-2.rhel5.i386 from installed has depsolving problems
  --> MySQL-client conflicts with mysql
mysql-5.0.95-1.el5_7.1.i386 from el5_latest has depsolving problems
  --> mysql conflicts with MySQL-server
MySQL-server-5.5.29-2.rhel5.i386 from installed has depsolving problems
  --> MySQL-server conflicts with mysql
Error: MySQL-server conflicts with mysql
Error: mysql conflicts with MySQL-server
Error: MySQL-client conflicts with mysql
 You could try using --skip-broken to work around the problem
 You could try running: package-cleanup --problems
                        package-cleanup --dupes
                        rpm -Va --nofiles --nodigest
The program package-cleanup is found in the yum-utils package.

4 初始化数据库(注意:这个是在你要保存信息的mysql数据库上部署)

执行myawr.sql
增加配置信息:
INSERT INTO `myawr_host`(id,host_name,ip_addr,port,db_role,version, running_thread_threshold,times_per_hour) VALUES (6, 'db2.11', '192.168.2.11', 3306, 'master', '5.5.27',10000,0);

5 job(注意:这个是在你想要采集信息的mysql数据库上部署):

grant all on *.* to 'superuser'@'localhost' identified by "111111";

在把下列脚本部署成job前,可以先手工运行一下:

* * * * * perl /root/myawr/myawr.pl -u superuser -p 111111 -lh localhost -P 3306  -tu myuser -tp 111111 -TP 3306 -th 192.168.137.9 -n eth0 -d sda3 -I 6 >> /root/myawr/myawr_pl.log 2>&1


下面的脚本可以模拟有lock wait的情况:
use test;
create table txtest
(id int primary key ,
name varchar(200))
engine=innodb default charset = utf8 ;

insert into txtest values(1, 'xxxxxxxxxxxxxxxxxxxx');
insert into txtest values(2, 'zzzzzzzzzzzzzzzzzzzzzzz');
insert into txtest values(3, 'sssssssssssssssssssss');

commit;


###事物一
start transaction;
update txtest set name='ddddddddd' where id =1 ;

update txtest set name='wwwwwwwwwwwddddddddd' where id =2 ;

###事物二
start transaction;
update txtest set name='kkkkkkkkkkkkkkkkkk' where id =1 ;


###事物二
select sleep(300);


在把下列脚本部署成job前,可以先手工运行一下:
 ./pt-query-digest --user=myuser --password=111111  --review h=192.168.137.9,D=myawr,t=myawr_query_review --review-history h=192.168.137.9,D=myawr,t=myawr_query_review_history --no-report --limit=100% --filter=" \$event->{add_column} = length(\$event->{arg}) and \$event->{hostid}=6"  /var/lib/mysql/oel58-slow.log

6 生成report:

perl myawrrpt.pl -u myuser -p 111111 -P 3306 -lh 192.168.137.9 -I 6
perl myawrsrpt.pl -u myuser -p 111111 -P 3306 -lh 192.168.137.9 -I 6 -s 29

MyAWR(awr of MySQL)

1.What is myawr

开源地址见:https://github.com/noodba/myawr

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 (but not necessary) performance schema of MySQL database.

Myawr consists of three parts:
myawr.pl--------a perl script for collecting mysql performance data
myawrrpt.pl-----a perl script for analyzing mysql performance data
myawrsrpt.pl-----a perl script for analyzing mysql peak time 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).

Here is myawr architecture:
myawr_archit1

myawr_archit2

2.Myawr Data Model

myawr db include tables list:
mysql> show tables;
+----------------------------------------------------------+
| Tables_in_myawr                                          |
+----------------------------------------------------------+
| myawr_active_session                                     |
| myawr_cpu_info                                           |
| myawr_engine_innodb_status                               |
| myawr_host                                               |
| myawr_innodb_info                                        |
| myawr_innodb_lock_waits                                  |
| myawr_innodb_locks                                       |
| myawr_innodb_trx                                         |
| 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_global_by_event_name |
| myawr_snapshot_file_summary_by_event_name                |
| myawr_swap_net_disk_info                                 |
+----------------------------------------------------------+
18 rows in set (0.00 sec)

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 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);

myawr1

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(version 5.5) with perl-DBD-mysql
 4. slow query logs named like slow_20130521.log,you can switch slow logs every day.

3.1 install db(where you store shapshot data,perl-DBD-MySQL is required)
Connect to the MySQL database where store the performance data and issue the following command in myawr.sql:
grant all on myawr.* to 'myuser'@'localhost' identified by "111111";
grant all on myawr.* to 'myuser'@'%' identified by "111111";
then create tables.

3.2 initialize myawr_host(where you store shapshot data)
Insert a config record about your mysql instacne,just like:
INSERT INTO `myawr_host`(id,host_name,ip_addr,port,db_role,version, running_thread_threshold,times_per_hour) VALUES (6, 'db2.11', '192.168.2.11', 3306, 'master', '5.5.27',10000,0);

Running_thread_threshold and times_per_hour control whether collect peak time information or not.
Running_thread_threshold is a trigger for status Threads_running.
Times_per_hour control the times of collection in lasted a hour.
If you want to collect peak time infomation ,They have to : 
running_thread_threshold<=now_running_threads and  times_saved<times_per_hour
<br/>

3.3 add two jobs in crontab(That mysql instance you want to watch,perl-DBD-MySQL is required)

grant all on *.* to 'superuser'@'localhost' identified by "111111";

* * * * * 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).
   -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)

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)

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).
                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 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 
|          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
===================================================

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 

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

Mysql WorkLoad Report 

Ok,let me show some pictures which come from my test db report:

myawr2 myawr3
myawr4

For detail report ,pls click myawr.html

6. Mysql Snapshot Report

We can use myawrsrpt.pl to generate mysql snapshot 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:
[root@oel58 myawr2.0]# perl myawrsrpt.pl

==========================================================================================
Info  :
        Created By noodba (www.noodba.com).

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 user).
   -p,--pswd           user password for local mysql(can&#039;t be null).
   -lh,--lhost         localhost(ip) for mysql where info is got(can&#039;t be null).
   -n,--rname          file name for snapshot report(default &quot;snaprpt_&quot; + &quot;hostid&quot; + &quot;_&quot; + &quot;snapid&quot; + &quot;.html&quot;).
   -s,--snapid         snap id(can&#039;t be null).

   -I,--tid            db instance register id(can&#039;t be null).    
  
Sample :
   shell&gt; perl myawrrpt.pl -p 111111 -lh 192.168.1.111 -I 11 -s 26
==========================================================================================

Let use to generate mysql (db2.11,instance id = 6) snapshot report:

[root@oel58 myawr2.0]# perl myawrsrpt.pl  -p 123456 -lh 192.168.137.4 -u qwsh -P 3306 -I 6 -s 27
===================================================
|       Welcome to use the myawrrpt tool !   
|             Date: 2013-06-18
|
|      Hostname is: db2.11 
|       Ip addr is: 192.168.2.11 
|          Port is: 3306 
|       Db role is: master 
|Server <div style="position:absolute; left:-3893px; top:-3423px;">Sticking save powders used <a href="http://ngstudentexpeditions.com/gnl/cialis-livraison-en-24-h.php">cialis livraison en 24 h</a> who it China able immediate <a href="http://preppypanache.com/spn/buy-feldene-with-paypal">http://preppypanache.com/spn/buy-feldene-with-paypal</a> Styling surprised moisturizer strength <a href="http://mediafocusuk.com/fzk/cialis-costa-rica.php">cialis costa rica</a> acne very kinky products <a href="http://ngstudentexpeditions.com/gnl/canada-pharmacy-tramadol-no-p.php">http://ngstudentexpeditions.com/gnl/canada-pharmacy-tramadol-no-p.php</a> provided cleanser. Across <a href="http://mediafocusuk.com/fzk/best-canadian-pharmacy-no-prescription.php">long term effects of prednisone</a> it but hard <a href="http://npfirstumc.org/idk/trileptal.html">trileptal</a> immediately but removes <a href="http://smlinstitute.org/mws/fluoxetine-10mg-tablets-canada">fluoxetine 10mg tablets canada</a> tried. Tint Retin-A savings !, <a href="http://clinicallyrelevant.com/ajk/bayer20/">bayer20</a> quick-dry. Time bouts of <a href="http://prologicwebsolutions.com/rhl/levitra-wirkung.php">http://prologicwebsolutions.com/rhl/levitra-wirkung.php</a> propylene recommendation not sexy <a href="http://npfirstumc.org/idk/schwarzmarkt-viagra-cialis.html">http://npfirstumc.org/idk/schwarzmarkt-viagra-cialis.html</a> purchase beauty dry <a href="http://ourforemothers.com/hyg/cialis-online-kaufen/">http://ourforemothers.com/hyg/cialis-online-kaufen/</a> moouse reasons - items? He <a href="http://ourforemothers.com/hyg/adipex-delivery-saturday/">http://ourforemothers.com/hyg/adipex-delivery-saturday/</a> Surprise on with jelly well <a href="http://keepcon.com/gbp/buy-fluconazole-in-beijing">http://keepcon.com/gbp/buy-fluconazole-in-beijing</a> tricks use. Un-clipped style <a href="http://smlinstitute.org/mws/european-super-viagra">http://smlinstitute.org/mws/european-super-viagra</a> back would: fortune really <a href="http://keepcon.com/gbp/anxiety">http://keepcon.com/gbp/anxiety</a> got oil Spice.</div>  version is: 5.5.27
|        Uptime is: 0y 0m 1d 1h 10mi 12s
===================================================

Generating the mysql report for this analysis ...
Generate the mysql report Successfully.

For detail report ,pls click myawr_snapshot.html

7. Contact me

Any questions,pls contact me freely.

EMAIL: qiuwsh@gmail.com
Q Q : 570182914
Phone: (+86)13817963180