Chinaunix首页 | 论坛 | 博客
  • 博客访问: 480213
  • 博文数量: 99
  • 博客积分: 3621
  • 博客等级: 中校
  • 技术积分: 1089
  • 用 户 组: 普通用户
  • 注册时间: 2010-06-22 16:29
文章存档

2012年(21)

2011年(28)

2010年(50)

分类: Mysql/postgreSQL

2012-06-05 09:49:47

#原shell版

点击(此处)折叠或打开

  1. #!/bin/bash
  2. # Script Name: mysql_status_check.sh
  3. # Description: check mysql servers status
  4. # Author: Xinggang Wang - OpsEye.com
  5. # Create Date: 2012/3/30
  6. #获取MySQL所在服务器IP/端口/用户名/密码
  7. read -p "Host=" HOST
  8. read -p "Port=" PORT
  9. read -p "User=" USER
  10. read -sp "Password=" PASSWORD
  11. echo
  12. #默认为127.0.0.1/3306/root
  13. if [ "${HOST}" = "" ]
  14. then
  15. HOST='127.0.0.1'
  16. fi
  17. if [ "${PORT}" = "" ]
  18. then
  19. PORT='3306'
  20. fi
  21. if [ "${USER}" = "" ]
  22. then
  23. USER='root'
  24. fi
  25. #注意密码为空的时候的格式
  26. mysql_list="
  27. $HOST:$PORT:$USER:$PASSWORD
  28. "
  29. #计算函数,提高脚本效率
  30. compute(){
  31. formula="$1"
  32. awk 'BEGIN{printf("%.2f",'$formula')}' 2>/dev/null &&
  33. echo $value || echo NULL
  34. }
  35. for mysql in $mysql_list
  36. {
  37. host=${mysql%%:*}
  38. port=$(echo $mysql|awk -F: '{print $2}')
  39. user=$(echo $mysql|awk -F: '{print $3}')
  40. passwd=${mysql##*:}
  41. [ -z "$passwd" ] && mysql="mysql -h$host -P$port -u$user" ||
  42. mysql="mysql -h$host -P$port -u$user -p$passwd"
  43. unset Uptime
  44. # 把show global status的值赋给相应的参数名称(这里相当于大量的变量赋值操作)
  45. eval $( $mysql -e "show global status" | awk '{print $1"=\x27"$2"\047"}')
  46. [ X = X"$Uptime" ] && continue
  47. # Mysql VER
  48. VER=`$mysql -e"status;"|grep 'Server version'|awk '{print $3}'`
  49. # Uptime
  50. UPTIME=`compute "$Uptime/3600/24"`
  51. # Threads_connected
  52. threads_connected=`compute "$Threads_connected"`
  53. # QPS Questions/Uptime
  54. qps=`compute "$Questions/$Uptime"`
  55. # TPS (Com_commit + Com_rollback)/Uptime
  56. tps=`compute "($Com_commit+$Com_rollback)/$Uptime"`
  57. # Reads Com_select + Qcache_hits
  58. reads=`compute "$Com_select+$Qcache_hits"`
  59. # Writes Com_insert + Com_update + Com_delete + Com_replace
  60. writes=`compute "$Com_insert+$Com_update+$Com_delete+$Com_replace"`
  61. # Read/Writes Ratio reads/writes*100%
  62. rwratio=`compute "$reads/$writes*100"`%
  63. # MyISAM Key_buffer_read_hits (1 - Key_reads/Key_read_requests) * 100
  64. key_buffer_read_hits=`compute "(1-$Key_reads/$Key_read_requests)*100"`%
  65. # MyISAM Key_buffer_write_hits (1 - Key_writes/Key_write_requests) * 100
  66. key_buffer_write_hits=`compute "(1-$Key_writes/$Key_write_requests)*100"`%
  67. # Query_cache_hits (Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100%
  68. query_cache_hits=`compute "$Qcache_hits/($Qcache_hits+$Qcache_inserts)*100"`%
  69. # Innodb_buffer_read_hits (1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100
  70. innodb_buffer_read_hits=`compute "(1-$Innodb_buffer_pool_reads/$Innodb_buffer_pool_read_requests)*100"`%
  71. # Thread_cache_hits (1 - Threads_created / Connections) * 100%
  72. thread_cache_hits=`compute "(1-$Threads_created/$Connections)*100"`%
  73. # Slow_queries_per_second Slow_queries / Uptime * 60
  74. slow_queries_per_second=`compute "$Slow_queries/$Uptime"`
  75. # Select_full_join_per_second Select_full_join / Uptime * 60
  76. select_full_join_per_second=`compute "$Select_full_join/$Uptime*60"`
  77. # select_full_join_in_all_select (Select_full_join / Com_select) * 100
  78. select_full_join_in_all_select=`compute "($Select_full_join/$Com_select)*100"`%
  79. # MyISAM Lock Contention (Table_locks_waited / Table_locks_immediate) * 100
  80. myisam_lock_contention=`compute "($Table_locks_waited/$Table_locks_immediate)*100"`%
  81. # Temp_tables_to_disk (Created_tmp_disk_tables / Created_tmp_tables) * 100
  82. temp_tables_to_disk_ratio=`compute "($Created_tmp_disk_tables/$Created_tmp_tables)*100"`%
  83. # print formated MySQL status report
  84. title="******************** MySQL--${HOST}--${PORT} ***********************"
  85. width=$((`echo "$title"|wc -c`-1))
  86. echo "$title"
  87. export IFS=':'
  88. while read name value ;do
  89. printf "%36s :\t%10s\n" $name $value
  90. done <
  91. Mysql Ver:$VER
  92. Uptime:$UPTIME days
  93. Threads connected:$threads_connected
  94. QPS:$qps
  95. TPS:$tps
  96. Reads:$reads
  97. Writes:$writes
  98. Read/Writes Ratio:$rwratio
  99. MyISAM Key buffer read hits(>99%):$key_buffer_read_hits
  100. MyISAM Key buffer write hits:$key_buffer_write_hits
  101. Query cache hits:$query_cache_hits
  102. InnoDB buffer read hits(>95%):$innodb_buffer_read_hits
  103. Thread cache hits(>90%):$thread_cache_hits
  104. Slow queries per second:$slow_queries_per_second
  105. Select full join per second:$select_full_join_per_second
  106. Select full join in all select:$select_full_join_in_all_select
  107. MyiSAM lock contention(<1%):$myisam_lock_contention
  108. Temp tables to disk ratio:$temp_tables_to_disk_ratio
  109. EOF
  110. unset IFS
  111. for i in `seq $width`;{ echo -n "*";};echo
  112. }
  113. exit 0
#Python版

点击(此处)折叠或打开

  1. #!/usr/bin/env python
  2. #-*- coding: utf-8 -*-
  3. # Script Name: mysql_status_check.py
  4. # Description: check mysql servers status
  5. # Author: Bruce.Zuo
  6. # Create Date: 2012/06/05
  7. import os,sys
  8. import MySQLdb
  9. import getpass

  10. host=raw_input("host:")
  11. user=raw_input("user:")
  12. password=getpass.getpass()

  13. try:
  14.    conn = MySQLdb.connect(host = host, user=user ,passwd = password, db = 'test')
  15. except MySQLdb.ERROR,e:
  16.    print "Error %d:%s"%(e.args[0],e.args[1])
  17.    exit(1)
  18. cursor=conn.cursor()

  19. cursor.execute('show global status;')
  20. result_set=cursor.fetchall()
  21. cursor.close()
  22. conn.close()

  23. def get_value(key_name):
  24.         for rows in result_set:
  25.                 if rows[0]==key_name:
  26.                         return float(rows[1])

  27. print ('MySQL-'+host+'-3306').center(60,'*')
  28. print 'Uptime:'.rjust(40),get_value('Uptime')
  29. print 'Threads_connected:'.rjust(40),get_value('Threads_connected')
  30. print 'QPS:'.rjust(40),round(get_value('Questions') / get_value('Uptime'),2)
  31. print 'TPS:'.rjust(40),round(get_value('Com_commit')+get_value('Com_rollback') / get_value('Uptime'),2)
  32. reads=get_value('Com_select')+ get_value('Qcache_hits')
  33. writes=get_value('Com_insert')+get_value('Com_update')+get_value('Com_delete')+get_value('Com_replace')
  34. print 'Reads:'.rjust(40),get_value('Com_select')+ get_value('Qcache_hits')
  35. print 'Writes:'.rjust(40),get_value('Com_insert')+get_value('Com_update')+get_value('Com_delete')+get_value('Com_replace')
  36. print 'Read/Writes Ratio:'.rjust(40),round(reads / writes,2),'%'
  37. print 'MyISAM Key buffer read hits(>99%):'.rjust(40),round(1-get_value('Key_reads') / (get_value('Key_read_requests')*100),2),'%'
  38. print 'MyISAM Key buffer write hits:'.rjust(40),round(1-get_value('Key_writes') / (get_value('Key_write_requests')*100),2),'%'
  39. print 'Query cache hits:'.rjust(40),round(get_value('Qcache_hits') / (get_value('Qcache_hits')+get_value('Qcache_inserts'))*100,2),'%'
  40. print 'InnoDB buffer read hits(>95%):'.rjust(40),round(1-get_value('Innodb_buffer_pool_reads') / (get_value('Innodb_buffer_pool_read_requests')*100),2),'%'
  41. print 'Thread cache hits(>90%):'.rjust(40),round(1-get_value('Threads_created') / (get_value('Connections')*100),2),'%'
  42. print 'Slow queries per second:'.rjust(40),round(get_value('Slow_queries') / get_value('Uptime'),2)
  43. print 'Select full join per second:'.rjust(40),round(get_value('Select_full_join') / get_value('Uptime'),2)
  44. print 'Select full join in all select:'.rjust(40),round(get_value('Select_full_join') / (get_value('Com_select')*100),2),'%'
  45. print 'MyiSAM lock contention(<1%):'.rjust(40),round(get_value('Table_locks_waited') / (get_value('Table_locks_immediate')*100),2),'%'
  46. print 'Temp tables to disk ratio:'.rjust(40),round(get_value('Created_tmp_disk_tables') / (get_value('Created_tmp_tables')*100),2),'%'
  47. print '*'*60
#根据这个方法,可以添加更多的状态项。

效果图:
阅读(4394) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~