By

Contents

自动化采集脚本，采集性能评估指标
MySQL服务器的性能指标一般包括：负载、CPU、SWAP、磁盘、网络、MySQL状态。
基础状态搜集可以采用dstat，地址：http://dag.wieers.com/home-made/dstat/dstat-0.7.2.tar.bz2，看了下源码，用Python写的，值得参考学习。
老师分享了一个很不错的用perl写的一个工具-orzdba,地址：http://code.taobao.org/p/orzdba/src/trunk/ ，包含了许多MySQL状态的搜集及展示，值得学习。另外找到一个Python实现的orzdba，地址：https://github.com/cnlubo/orzdba ，感兴趣的朋友可以去看看。

自己用bash写了一个练习脚本，非常原始简单，为完成作业吧。——–干货在上面。

[mysql@guo ~]$ cat tpcc.sh
#!/bin/sh

user=guotest
pass=guotest

load_avg() {
  echo '-----load-avg----'
  echo ' 1m    5m    15m'
  cat /proc/loadavg |awk '{print $1"  "$2"  "$3}'
}

mysql_status() {
  mysql -u$user -p$pass -e 'show global status where Variable_name in   ("Com_select","Com_insert","Com_update","Com_delete","Innodb_buffer_pool_read_requests","Innodb_buffer_p  ool_reads","Innodb_rows_inserted","Innodb_rows_updated","Innodb_rows_deleted","Innodb_rows_read","Threa  ds_running","Threads_connected","Threads_cached","Threads_created","Bytes_received","Bytes_sent","Innodb_  buffer_pool_pages_data","Innodb_buffer_pool_pages_free","Innodb_buffer_pool_pages_dirty","Innodb_buffer_p  ool_pages_flushed","Innodb_data_reads","Innodb_data_writes","Innodb_data_read","Innodb_data_written","Inn  odb_os_log_fsyncs","Innodb_os_log_written")'
}

usage() {
  echo "Usage:"
  echo "  `basename $0` {-l   #show load_avg | -m    #show mysql gernal info}"
}

case "$1" in
  l)
     load_avg
     ;;
  m)
       mysql_status
       ;;
  *)
     usage
      exit 3
      ;;
esac
[mysql@guo ~]$

[mysql@guo ~]$ sh tpcc.sh l
-----load-avg----
 1m    5m    15m
0.07  0.02  0.00
[mysql@guo ~]$ sh tpcc.sh m
Warning: Using a password on the command line interface can be insecure.
+----------------------------------+---------+
| Variable_name                    | Value   |
+----------------------------------+---------+
| Bytes_received                   | 6012    |
| Bytes_sent                       | 5785    |
| Com_delete                       | 0       |
| Com_insert                       | 0       |
| Com_select                       | 11      |
| Com_update                       | 0       |
| Innodb_buffer_pool_pages_dirty   | 0       |
| Innodb_buffer_pool_pages_free    | 7996    |
| Innodb_buffer_pool_read_requests | 1675    |
| Innodb_data_read                 | 2641920 |
| Innodb_data_reads                | 170     |
| Innodb_data_writes               | 72      |
| Innodb_data_written              | 3138048 |
| Innodb_os_log_written            | 55296   |
| Innodb_rows_deleted              | 0       |
| Innodb_rows_inserted             | 0       |
| Innodb_rows_read                 | 0       |
| Innodb_rows_updated              | 0       |
| Threads_cached                   | 0       |
| Threads_connected                | 1       |
| Threads_created                  | 1       |
+----------------------------------+---------+
[mysql@guo ~]$

1、《TPC Benchmarks》http://www.tpc.org/information/benchmarks.asp
2、《TPC BENCHMARK™ C》
3、《fio》http://freecode.com/projects/fio
4、《Fio Output Explained》http://tobert.github.io/post/2014-04-17-fio-output-explained.html
5、《TPCC-MySQL使用说明》http://blog.chinaunix.net/uid-26896862-id-3188313.html
6、《TPCC-MySQL输出结果详解 》http://blog.chinaunix.net/uid-26896862-id-3563600.html
7、《DBT》http://sourceforge.net/apps/mediawiki/osdldbt/index.php?title=Main_Page
8、《DBT2使用说明 》http://blog.chinaunix.net/uid-26896862-id-3188314.html
9、《Sysbench》http://sysbench.sourceforge.net/
10、《SysBench: a system performance benchmark》https://code.launchpad.net/sysbench
11、《Using Lua-enabled sysbench》https://blog.mariadb.org/using-lua-enabled-sysbench/
12、《oltp.lua》http://www.percona.com/docs/wiki/benchmark:sysbench:olpt.lua
13、《Sysbench with support of multi-tables workload》http://www.mysqlperformanceblog.com/2011/04/29/sysbench-with-support-of-multi-tables-workload/
14、《tcpcopy》http://code.google.com/p/tcpcopy/
15、《阿里2013双十一备战中的技术突破》http://www.kankanews.com/ICkengine/archives/73645.shtml

Contents