MySQL 淘寶網(wǎng)開源監(jiān)控工具orzdba安裝、使用

轉(zhuǎn)載：http://blog.51cto.com/arthur376/1888931

orzdba是淘寶DBA團(tuán)隊開發(fā)出來的一個perl監(jiān)控腳本,主要功能是監(jiān)控mysql數(shù)據(jù)庫,也有一些磁盤和cpu的監(jiān)控選項,好不好用就見仁見智,畢竟各公司需求不盡相同.

安裝:

既然是perl腳本,那當(dāng)然是先裝各種perl相關(guān)的依賴包和控件:

yum?install-y?perl-Test-Simple.x86_64?perl-Time-HiRes?perl-ExtUtils-CBuilder?

yum?install-y?perl-ExtUtils-MakeMaker?perl-DBD-MySQL?perl-DBI?perl-Module-Build

還有一個依賴命令:

#先下載下來

wget?http://github.com/downloads/Lowercases/tcprstat/tcprstat-static.v0.3.1.x86_64

#移動到/usr/bin

mvtcprstat-static.v0.3.1.x86_64??/usr/bin/

#做個連接

ln-sf?/usr/bin/tcprstat-static.v0.3.1.x86_64?/usr/bin/tcprstat

然后來下載腳本和依賴包,

http://code.taobao.org/svn/orzdba/trunk

或者這樣也可以

#先安裝svn客戶端

yum?install-y?subversion

#或

apt-get?install-y?subversion

#然后下載

svn?co?http://code.taobao.org/svn/orzdba/trunk

A????trunk/orzdba_rt_depend_perl_module.tar.gz

A????trunk/orzdba

A????trunk/orzdba工具使用說明.pdf

取出版本?4。

----------------------------------------------------------------------------------

這個我已經(jīng)上傳到51cto了,大家可以直接去下載,全部在里面

http://down.51cto.com/data/2277755

然后這個也是修改過的腳本,直接修改26-29行的變量就行,不用跳到160行修改,

catorzdba

my??$used?=?'root';????#用戶名

my??$pswd?=?'****';????#密碼

my??$ipdz?=?'127.0.0.1';????#ip地址

my??$port?=?3306;???????#?-P?端口號

-----------------------------------------------------------------------------------

下載回來的包會有個壓縮包,要安裝里面的控件,

#先解壓

tarxf?orzdba_rt_depend_perl_module.tar.gz

#進(jìn)去解壓后的文件夾

cdPerl_Module

#里面有幾個壓縮包,一個個來安裝

tarxf?version-0.99.tar.gz

cdversion-0.99

perl?Makefile.PL

make

makeinstall

cd..

tarxf?File-Lockfile-v1.0.5.tar.gz?

cdFile-Lockfile-v1.0.5?

perl?Build.PL

perl?./Build

perl?./Buildinstall

cd..

tarxf?Class-Data-Inheritable-0.08.tar.gz

cdClass-Data-Inheritable-0.08?

perl?Makefile.PL?

make

makeinstall

cd..

tarxf?Module-Build-0.31.tar.gz?

cdModule-Build-0.31?

perl?Build.PL?

./Build

./Buildinstall

當(dāng)然了,還要改下配置,不然你怎么確認(rèn)他連的是哪里呢?

#需要在代碼160行左右配置MySQL的相關(guān)驗證信息,如username,password,host，port，sock等,改成類似下面這樣

#如果你是用我上傳到51cto的包，那就改26-29行就行了

grep-n?'my?$MYSQL'orzdba

160:my?$MYSQL?=?qq{mysql?-s?--skip-column-names?-uroot?-p123123?-h127.0.0.1?-P$port?};

#然后改一下host信息,不然會報錯(不要在意我的計算機(jī)名)

cat/etc/hosts

172.17.0.2????32044b19ae8c

#最后,加個執(zhí)行權(quán)限吧

chmod+x?orzdba

這個時候就可以用了.

使用:

當(dāng)然了,有很多參數(shù),請看最后面一一列舉,現(xiàn)在來看看怎么用:

./orzdba-lazy?-rt?2>/dev/null


.=================================================.

|???????Welcome?to?use?the?orzdba?tool?!??????????|?

|??????????Yep...Chinese?English~?????????????????|

'===============?Date?:?2017-01-04?==============='


HOST:?32044b19ae8c???IP:?172.17.0.2

DB??:?XXX|XXX|XXX

Var?:?binlog_format[ROW]?max_binlog_cache_size[17179869184G]?max_binlog_size[500M]?

??????max_connect_errors[100000]?max_connections[200]?max_user_connections[150]?

??????open_files_limit[65535]?sync_binlog[0]?table_definition_cache[656]?

??????table_open_cache[512]?thread_cache_size[51]?


??????innodb_adaptive_flushing[ON]?innodb_adaptive_hash_index[ON]?innodb_buffer_pool_size[512M]?

??????innodb_file_per_table[ON]?innodb_flush_log_at_trx_commit[2]?innodb_flush_method[]?

??????innodb_io_capacity[200]?innodb_lock_wait_timeout[10]?innodb_log_buffer_size[16M]?

??????innodb_log_file_size[512M]?innodb_log_files_in_group[2]?innodb_max_dirty_pages_pct[35]?

??????innodb_open_files[65535]?innodb_read_io_threads[4]?innodb_thread_concurrency[0]?

??????innodb_write_io_threads[4]?


--------?-----load-avg----?---cpu-usage---?---swap---?????????????????????-QPS-?-TPS-?????????-Hit%-?--------tcprstat(us)--------?

??time|??1m????5m???15m?|usr?sys?idl?iow|???si???so|??ins???upd???del????sel???iud|?????lor????hit|??count????avg?95-avg?99-avg|

14:43:31|?0.36??0.23??0.17|??1???0??98???0|????0????0|????0?????0?????0??????0?????0|???????0?100.00|??????0??????0??????0??????0|

14:43:32|?0.36??0.23??0.17|??3???1??95???0|????0????0|????0?????0?????0??????1?????0|???????0?100.00|??????0??????0??????0??????0|

14:43:33|?0.36??0.23??0.17|??4???2??94???0|????0????0|????0?????0?????0??????1?????0|???????0?100.00|??????0??????0??????0??????0|

14:43:34|?0.36??0.23??0.17|??4???1??95???0|????0????0|????0?????0?????0??????1?????0|???????0?100.00|??????0??????0??????0??????0|

14:43:35|?0.33??0.23??0.17|??2???1??97???0|????0????0|????0?????0?????0??????1?????0|???????0?100.00|??????0??????0??????0??????0|

14:43:36|?0.33??0.23??0.17|??3???1??95???0|????0????0|????0?????0?????0??????1?????0|???????0?100.00|??????0??????0??????0??????0|

14:43:37|?0.33??0.23??0.17|??4???1??95???0|????0????0|????0?????0?????0??????1?????0|???????0?100.00|??????0??????0??????0??????0|

14:43:38|?0.33??0.23??0.17|??3???1??97???0|????0????0|????0?????0?????0??????1?????0|???????0?100.00|??????0??????0??????0??????0|

14:43:39|?0.30??0.22??0.16|??2???1??97???0|????0????0|????0?????0?????0??????1?????0|???????0?100.00|??????0??????0??????0??????0|

14:43:40|?0.30??0.22??0.16|??3???1??96???0|????0????0|????0?????0?????0??????8?????0|??????16?100.00|??????0??????0??????0??????0|

14:43:41|?0.30??0.22??0.16|??3???1??97???0|????0????0|????0?????0?????0??????1?????0|???????0?100.00|??????0??????0??????0??????0|

14:43:42|?0.30??0.22??0.16|??2???1??97???0|????0????0|????0?????0?????0??????1?????0|???????0?100.00|??????0??????0??????0??????0|

注釋：

count：此間隔內(nèi)處理完成的請求數(shù)量

avg：此間隔內(nèi)所有完成的請求，響應(yīng)的平均時間

95_avg：此間隔內(nèi)，95%的請求量的平均響應(yīng)時間，單位微妙，該值較能體現(xiàn)MySQL Server的查詢平均響應(yīng)時間

注意:在mysql5.6版本之后,如果明文輸出密碼會有警告提示,造成輸出異常,所以我們需要加上這個參數(shù):2>/dev/null,來把它過濾掉.

監(jiān)控InnoDB的各項指標(biāo):

./orzdba-innodb?2>/dev/null


.=================================================.

|???????Welcome?to?use?the?orzdba?tool?!??????????|?

|??????????Yep...Chinese?English~?????????????????|

'===============?Date?:?2017-01-04?==============='


HOST:?32044b19ae8c???IP:?172.17.0.2

DB??:?xxx|xxx|xxx

Var?:?binlog_format[ROW]?max_binlog_cache_size[17179869184G]?max_binlog_size[500M]?

??????max_connect_errors[100000]?max_connections[200]?max_user_connections[150]?

??????open_files_limit[65535]?sync_binlog[0]?table_definition_cache[656]?

??????table_open_cache[512]?thread_cache_size[51]?


??????innodb_adaptive_flushing[ON]?innodb_adaptive_hash_index[ON]?innodb_buffer_pool_size[512M]?

??????innodb_file_per_table[ON]?innodb_flush_log_at_trx_commit[2]?innodb_flush_method[]?

??????innodb_io_capacity[200]?innodb_lock_wait_timeout[10]?innodb_log_buffer_size[16M]?

??????innodb_log_file_size[512M]?innodb_log_files_in_group[2]?innodb_max_dirty_pages_pct[35]?

??????innodb_open_files[65535]?innodb_read_io_threads[4]?innodb_thread_concurrency[0]?

??????innodb_write_io_threads[4]?


--------?---innodb?bp?pages?status--?-----innodb?data?status----?--innodb?log--???his?--log(byte)--??read---query---?

??time|???data???freedirty?flush|?reads?writes??readwritten|fsyncs?written|?list?uflush??uckpt??view?inside??que|

14:48:03|??????0??????0??????0?????0|?????0??????0??????0??????0|?????0???????0|????0??????0??????0?????0?????0?????0|

14:48:04|??29961???1024??????0?????0|?????0??????0??????0??????0|?????0???????0|?1754??????0???????0????0?????0?????0|

14:48:05|??29961???1024??????0?????0|?????0??????0??????0??????0|?????0???????0|?1754??????0???????0????0?????0?????0|

14:48:06|??29961???1024??????0?????0|?????0??????0??????0??????0|?????0???????0|?1754??????0???????0????0?????0?????0|

14:48:07|??29961???1024??????0?????0|?????0??????0??????0??????0|?????0???????0|?1754??????0???????0????0?????0?????0|

14:48:08|??29961???1024??????0?????0|?????0??????0??????0??????0|?????0???????0|?1754??????0???????0????0?????0?????0|

14:48:09|??29961???1024??????0?????0|?????0??????0??????0??????0|?????0???????0|?1754??????0???????0????0?????0?????0|

14:48:10|??29961???1024??????0?????0|?????0??????0??????0??????0|?????0???????0|?1754??????0???????0????0?????0?????0|

14:48:11|??29961???1024??????0?????0|?????0??????0??????0??????0|?????0???????0|?1754??????0???????0????0?????0?????0|

監(jiān)控MySQL Server性能:

./orzdba-mysql?2>/dev/null


.=================================================.

|???????Welcome?to?use?the?orzdba?tool?!??????????|?

|??????????Yep...Chinese?English~?????????????????|

'===============?Date?:?2017-01-04?==============='


HOST:?32044b19ae8c???IP:?172.17.0.2

DB??:?xxx|xxx|xxx

Var?:?binlog_format[ROW]?max_binlog_cache_size[17179869184G]?max_binlog_size[500M]?

??????max_connect_errors[100000]?max_connections[200]?max_user_connections[150]?

??????open_files_limit[65535]?sync_binlog[0]?table_definition_cache[656]?

??????table_open_cache[512]?thread_cache_size[51]?


??????innodb_adaptive_flushing[ON]?innodb_adaptive_hash_index[ON]?innodb_buffer_pool_size[512M]?

??????innodb_file_per_table[ON]?innodb_flush_log_at_trx_commit[2]?innodb_flush_method[]?

??????innodb_io_capacity[200]?innodb_lock_wait_timeout[10]?innodb_log_buffer_size[16M]?

??????innodb_log_file_size[512M]?innodb_log_files_in_group[2]?innodb_max_dirty_pages_pct[35]?

??????innodb_open_files[65535]?innodb_read_io_threads[4]?innodb_thread_concurrency[0]?

??????innodb_write_io_threads[4]?


--------?????????????????????-QPS-?-TPS-?????????-Hit%-?------threads------?-----bytes----?

??time|??ins???upd???del????sel???iud|?????lor????hit|?run??con??cre??cac|???recv???send|

14:49:30|????0?????0?????0??????0?????0|???????0?100.00|???0????0????0????0|??????0??????0|

14:49:31|????0?????0?????0??????4?????0|?????179?100.00|???1???79????0???15|?????1k????74k|

14:49:32|????0?????0?????0??????5?????0|????4228?100.00|???1???79????0???15|?????2k?????6k|

14:49:33|????0?????0?????0??????1?????0|???????0?100.00|???1???79????0???15|????846?????1k|

14:49:34|????0?????0?????0??????1?????0|???????0?100.00|???1???79????0???15|????846?????1k|

14:49:35|????0?????0?????0??????1?????0|???????0?100.00|???1???79????0???15|????846?????1k|

14:49:36|????0?????0?????0??????1?????0|???????0?100.00|???1???79????0???15|????846?????1k|

14:49:37|????0?????0?????0??????1?????0|???????0?100.00|???1???79????0???15|????846?????1k|

14:49:38|????0?????0?????0??????1?????0|???????0?100.00|???1???79????0???15|????972?????1k|

14:49:39|????0?????0?????0??????1?????0|???????0?100.00|???1???79????0???15|????846?????1k|

14:49:40|????0?????0?????0??????8?????0|??????16?100.00|???1???79????0???15|?????2k?????4k|

14:49:41|????0?????0?????0??????1?????0|???????0?100.00|???1???79????0???15|????846?????1k|

14:49:42|????0?????0?????0??????1?????0|???????0?100.00|???1???79????0???15|????846?????1k|

還有幾個稍微列舉下,各位自己試試了


#查看Linux主機(jī)指標(biāo)

./orzdba-sys?-C?10?-i?1?-t?-d?sda

-sys：打印系統(tǒng)信息，包括-t(打印當(dāng)前時間)、-l(打印負(fù)載信息，分1分鐘、5分鐘、15分鐘)、-c(打印cpu信息)、-s(打印交換分區(qū)信息)

-d：打印磁盤信息，需要指點磁盤設(shè)備名

-n：打印網(wǎng)絡(luò)信息，接收和發(fā)送大小，需要指點網(wǎng)卡設(shè)備名

查看DISK（-d）和NET（-n）需要帶具體的設(shè)備名（具體可以查看/proc/diskstats和/proc/net/dev中的設(shè)備或者可以取自iostat

和sar -n DEV）

Linux指標(biāo)的數(shù)據(jù)都來自/proc目錄下的相關(guān)系統(tǒng)元數(shù)據(jù)：

LOAD : /proc/loadavg

CPU : /proc/stat

SWAP : /proc/vmstat

DISK : /proc/diskstats

NET : /proc/net/dev

#查看MySQL響應(yīng)時間(rt)

./orzdba-rt?-C?10?-i?1?-t?-d?sda

-C : 打印10次

-i : 間隔1秒

注釋:

./orzdba --help

==========================================================================================

Info? :

Created By zhuxu@taobao.com

Usage :

Command line options :

-h,--help?????????? Print Help Info.?

-i,--interval?????? Time(second) Interval.

-C,--count????????? Times.?

-t,--time?????????? Print The Current Time.

-nocolor??????????? Print NO Color.

-l,--load?????????? Print Load Info.

-c,--cpu??????????? Print Cpu? Info.

-s,--swap?????????? Print Swap Info.

-d,--disk?????????? Print Disk Info.

-n,--net??????????? Print Net? Info.

-P,--port?????????? Port number to use for mysql connection(default 3306).

-S,--socket???????? Socket file to use for mysql connection.

-com??????????????? Print MySQL Status(Com_select,Com_insert,Com_update,Com_delete).

-hit??????????????? Print Innodb Hit%.

-innodb_rows??????? Print Innodb Rows Status(Innodb_rows_inserted/updated/deleted/read).

-innodb_pages?????? Print Innodb Buffer Pool Pages Status(Innodb_buffer_pool_pages_data/free/dirty/flushed)

-innodb_data??????? Print Innodb Data Status(Innodb_data_reads/writes/read/written)

-innodb_log???????? Print Innodb Log? Status(Innodb_os_log_fsyncs/written)

-innodb_status????? Print Innodb Status from Command: 'Show Engine Innodb Status'

(history list/ log unflushed/uncheckpointed bytes/ read views/ queries inside/queued)

-T,--threads??????? Print Threads Status(Threads_running,Threads_connected,Threads_created,Threads_cached).

-rt???????????????? Print MySQL DB RT(us).

-B,--bytes????????? Print Bytes received from/send to MySQL(Bytes_received,Bytes_sent).

-mysql????????????? Print MySQLInfo (include -t,-com,-hit,-T,-B).

-innodb???????????? Print InnodbInfo(include -t,-innodb_pages,-innodb_data,-innodb_log,-innodb_status)

-sys??????????????? Print SysInfo?? (include -t,-l,-c,-s).

-lazy?????????????? Print Info????? (include -t,-l,-c,-s,-com,-hit).?

-L,--logfile??????? Print to Logfile.

-logfile_by_day???? One day a logfile,the suffix of logfile is 'yyyy-mm-dd';

and is valid with -L.

Sample :

shell> nohup ./orzdba -lazy -d sda -C 5 -i 2 -L /tmp/orzdba.log? > /dev/null 2>&1 &

==========================================================================================

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容