Chinaunix首页 | 论坛 | 博客
  • 博客访问: 457479
  • 博文数量: 239
  • 博客积分: 60
  • 博客等级: 民兵
  • 技术积分: 1033
  • 用 户 组: 普通用户
  • 注册时间: 2009-03-22 18:25
文章分类

全部博文(239)

文章存档

2019年(9)

2018年(64)

2017年(2)

2016年(26)

2015年(30)

2014年(41)

2013年(65)

2012年(2)

分类: Python/Ruby

2013-12-24 14:45:11

最近用sysbench给服务器性能做测评,要将不同测试参数下产生的日志的数据提取出来,写到excel里面,用excel生成趋势图。
如果日志比较大,还手工一项项填,那必定是天长地久海枯石烂,不累死也烦死。
幸好有脚本啊!

涉及的知识有sed,awk,bash,python基本语法,python读写excel的模块,都是比较初步的知识

首先,看看我们的数据源--日志
$ ls -1
IO_mode[rndrd]_threads[16]_filesize[10G]_file-block-size[16384]_{20131213030130}.log
IO_mode[rndrd]_threads[16]_filesize[10G]_file-block-size[4096]_{20131213024440}.log
IO_mode[rndrd]_threads[16]_filesize[10G]_file-block-size[8192]_{20131213025302}.log
IO_mode[rndrd]_threads[2]_filesize[10G]_file-block-size[16384]_{20131213014835}.log
IO_mode[rndrd]_threads[2]_filesize[10G]_file-block-size[4096]_{20131213013312}.log
IO_mode[rndrd]_threads[2]_filesize[10G]_file-block-size[8192]_{20131213014052}.log
IO_mode[rndrd]_threads[32]_filesize[10G]_file-block-size[16384]_{20131213032750}.log
IO_mode[rndrd]_threads[32]_filesize[10G]_file-block-size[4096]_{20131213031004}.log
IO_mode[rndrd]_threads[32]_filesize[10G]_file-block-size[8192]_{20131213031848}.log
IO_mode[rndrd]_threads[4]_filesize[10G]_file-block-size[16384]_{20131213021210}.log
IO_mode[rndrd]_threads[4]_filesize[10G]_file-block-size[4096]_{20131213015623}.log
IO_mode[rndrd]_threads[4]_filesize[10G]_file-block-size[8192]_{20131213020414}.log
IO_mode[rndrd]_threads[8]_filesize[10G]_file-block-size[16384]_{20131213023624}.log
IO_mode[rndrd]_threads[8]_filesize[10G]_file-block-size[4096]_{20131213022010}.log
IO_mode[rndrd]_threads[8]_filesize[10G]_file-block-size[8192]_{20131213022815}.log
……

然后看看一个日志里的内容:
$cat   IO_mode\[rndrd\]_threads\[16\]_filesize\[10G\]_file-block-size\[16384\]_\{20131213030130\}.log
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored

Extra file open flags: 0
128 files, 80Mb each
10Gb total file size
Block size 16Kb
Number of IO requests: 10000
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!

Operations performed:  10000 reads, 0 writes, 0 Other = 10000 Total
Read 156.25Mb  Written 0b  Total transferred 156.25Mb  (514.97Mb/sec)
32958.13 Requests/sec executed

General statistics:
    total time:                          0.3034s
    total number of events:              10000
    total time taken by event execution: 4.1829s
    response time:
         min:                                  0.00ms
         avg:                                  0.42ms
         max:                                208.05ms
         approx.  95 percentile:               0.79ms

Threads fairness:
    events (avg/stddev):           625.0000/303.67
    execution time (avg/stddev):   0.2614/0.03

以上标记为蓝色的数据是要提取出来的,填到excel的

先来看看目标长什么样:没错,我们的目标是生成这样的excel:


分2步走:
第一步,是把日志里的数据提取出来,方便写入excel。
写数据提取脚本:
vim grab.sh

  1. #!/bin/bash
 IOlogpath=/tpm/sysbench/log
  1. IOlogfile=/tpm/sysbench/log/IO_mode*

  2. function IO()
  3. {
  4.     for f in `ls $IOlogfile`; do
  5.         input=`echo $f|awk -F "[][]" '{print $1,$2,$3,$4,$5,$6,$7,$8}'` # something like: "IO_mode seqwr _threads 32 _filesize 10G _file-block-size 16384"
  6.         total_time=`sed -n 's/total time:.*\([0-9]\.[0-9]*\).*/\1/p' $f`
  7.         response_time=`sed -n 's/approx..*\([0-9]\.[0-9]*\).*/\1/p' $f`
  8.         tps=`awk -F '[()]' '/Mb\/sec/{print $2}' $f |awk -F'M' '{print $1}'` #transfer data per second
  9.         Reqps=`awk '/Requests\/sec/{print $1}' $f` #Requests per second executed
  10.         echo $input\ total_time $total_time\ response_time $response_time\ tps $tps\ Reqps $Reqps
  11.     done
  12. }

  13. IO |sort -k2,2 -k4,4n -k8,8n > $IOlogpath/IO.rec
执行grab.sh生成IO.rec文件

看看IO.rec里面是什么:
IO_mode rndrd _threads 2 _filesize 10G _file-block-size 4096 total_time 0.5035 response_time 0.19 tps 77.581 Reqps 19860.69
IO_mode rndrd _threads 2 _filesize 10G _file-block-size 8192 total_time 0.5295 response_time 0.20 tps 147.53 Reqps 18884.25
IO_mode rndrd _threads 2 _filesize 10G _file-block-size 16384 total_time 0.8527 response_time 0.35 tps 183.24 Reqps 11727.44
IO_mode rndrd _threads 4 _filesize 10G _file-block-size 4096 total_time 0.2972 response_time 0.22 tps 131.44 Reqps 33648.44
IO_mode rndrd _threads 4 _filesize 10G _file-block-size 8192 total_time 0.3419 response_time 0.26 tps 228.52 Reqps 29250.71
IO_mode rndrd _threads 4 _filesize 10G _file-block-size 16384 total_time 0.3699 response_time 0.29 tps 422.38 Reqps 27032.54
IO_mode rndrd _threads 8 _filesize 10G _file-block-size 4096 total_time 0.2671 response_time 0.33 tps 146.24 Reqps 37437.47
IO_mode rndrd _threads 8 _filesize 10G _file-block-size 8192 total_time 0.2807 response_time 0.49 tps 278.28 Reqps 35619.99
IO_mode rndrd _threads 8 _filesize 10G _file-block-size 16384 total_time 0.2825 response_time 0.50 tps 553.14 Reqps 35400.78
IO_mode rndrd _threads 16 _filesize 10G _file-block-size 4096 total_time 0.2295 response_time 0.75 tps 170.21 Reqps 43574.90
IO_mode rndrd _threads 16 _filesize 10G _file-block-size 8192 total_time 0.2657 response_time 0.87 tps 294.02 Reqps 37634.59
IO_mode rndrd _threads 16 _filesize 10G _file-block-size 16384 total_time 0.3034 response_time 0.79 tps 514.97 Reqps 32958.13
IO_mode rndrd _threads 32 _filesize 10G _file-block-size 4096 total_time 0.2095 response_time 1.44 tps 186.49 Reqps 47741.06
IO_mode rndrd _threads 32 _filesize 10G _file-block-size 8192 total_time 0.3147 response_time 1.94 tps 248.28 Reqps 31779.53
IO_mode rndrd _threads 32 _filesize 10G _file-block-size 16384 total_time 0.2250 response_time 1.82 tps 694.33 Reqps 44437.09
…………
…………

接下来进行第二步:
把IO.rec里面的数据写进excel
读和写excel分别是用xlrd、xlwt这两个模块
先安装模块
pip install xlwt

然后,写导入脚本:
vim txt2excel.py

  1. # -* - coding: UTF-8 -* -
  2. from tempfile import TemporaryFile
  3. from xlwt import Workbook

  4. book = Workbook()

  5. def wexcel(sheetname,file):
  6.     sheet = book.add_sheet(sheetname)
  7.     i=0
  8.     for line in open(file):
  9.         line=line.strip('\n') # 去掉换行符"\n"如果不去掉换行符,导入到excel中会使最后一列带换行符,造成的格式问题会导致最后一列不显示
  10.         data=line.split(' ')
  11.         row=sheet.row(i)
  12.         i+=1
  13.         j=0
  14.         for col in range(1,len(data)): # 轮询列,写当前行
  15.             row.write(j,str(data[col]))
  16.             j+=1

  17. def main():
  18.     wexcel('IO',"/tpm/sysbench/log/IO.rec")
  19.     book.save('simple.xls')
  20.     book.save(TemporaryFile())

  21. if __name__=="__main__":
  22.     main()


执行 txt2excel.py脚本,生成simple.xls  excel文件,大功告成!

阅读(870) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册