\u4e4b\u524d\u6839\u636e\u4e00\u4e2a\u811a\u672c\u6587\u4ef6\uff0c\u5199\u4e86\u4e00\u4e2apython\u7684\u68c0\u6d4b\u811a\u672c\u3002\u5199\u7684\u5f88\u4e45\u4e86\uff0c\u4ee3\u7801\u53ef\u4ee5\u4f18\u5316\u7684\u5730\u65b9\u6709\u5f88\u591a\uff0c\u4e0d\u8fc7\u8fd8\u53ef\u4ee5\u4f7f\u7528\u3002<\/p>\n
#!\/usr\/bin\/env python\r\nimport os\r\nimport sys\r\nimport time\r\nimport string\r\nimport re\r\nimport pwd\r\nimport subprocess\r\nimport commands\r\nimport getpass\r\ndebug=0\r\n\r\ntext_color={\r\n 'black':'\\033[0m',\r\n 'boldblack':'\\033[1;0m',\r\n 'red':'\\033[31m',\r\n 'boldred':'\\033[1;31m',\r\n 'green':'\\033[32m',\r\n 'boldgreen':'\\033[1;32m',\r\n 'yellow':'\\033[33m',\r\n 'boldyellow':'\\033[1;33m',\r\n 'blue':'\\033[34m',\r\n 'boldblue':'\\033[1;34m',\r\n 'magenta':'\\033[35m',\r\n 'boldmagenta':'\\033[1;35m',\r\n 'cyan':'\\033[36m',\r\n 'boldcyan':'\\033[1;36m',\r\n 'white':'\\033[37m',\r\n 'boldwhite':'\\033[1;37m'\r\n }\r\n\r\n\r\n'''\r\nThe Script for Log File.The Log File = \"\/var\/log\/message\"\r\n'''\r\ndef log_func(msg):\r\n msg=str(msg)\r\n logfile=open(\"\/var\/log\/mysql\",\"a\") \r\n logtime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())\r\n ftime=(\"*** %-63s***\" % logtime)\r\n logfile.write(ftime)\r\n logfile.write(\"\\n\")\r\n logfile.write(msg)\r\n logfile.write(\"\\n\")\r\n logfile.close()\r\nglobal logfile\r\nenv = os._Environ(os.environ)\r\nlogfile = env.get(\"LOGFILE\")\r\ndef execute_command(command_args,user=None,env=None):\r\n global logfile\r\n ret = -1\r\n res = []\r\n curdir = os.getcwd()\r\n if user is None:\r\n homedir = pwd.getpwuid(os.getuid())[5]\r\n else:\r\n try:\r\n int(user)\r\n homedir = pwd.getpwuid(int(user))[5]\r\n except:\r\n try:\r\n homedir = pwd.getpwnam(user)[5]\r\n except:\r\n homedir = pwd.getpwuid(os.getuid())[5]\r\n\r\n subproc_args = { 'stdin': subprocess.PIPE,\r\n 'stdout': subprocess.PIPE,\r\n 'stderr': subprocess.STDOUT,\r\n# 'shell': True,\r\n 'cwd': homedir,\r\n 'close_fds': True,\r\n }\r\n\r\n if env is not None:\r\n subproc_args['env'] = env\r\n \r\n if logfile is None:\r\n logfile = \"\/dev\/null\"\r\n\r\n logf = open(logfile, \"a\")\r\n try:\r\n pp = subprocess.Popen(command_args, **subproc_args)\r\n logf.write(\"Exec: '%s'\\n\" % (command_args))\r\n except OSError:\r\n logf.write(\"Exec: '%s' failed.\\n\" % (command_args))\r\n logf.close()\r\n return [ret,res]\r\n (stdouterr, stdin) = (pp.stdout, pp.stdin)\r\n while True:\r\n line = stdouterr.readline()\r\n ##logf.write(line)\r\n if not line:\r\n break\r\n line = line.rstrip()\r\n res.append(line)\r\n ret = pp.wait()\r\n logf.close()\r\n return [ret,res]\r\n'''\r\nThe Script for set color for print text on terminal\r\n'''\r\nclass setColor(object):\r\n def __init__(self):\r\n self.message=\"No message passed.\"\r\n self.color=\"black\"\r\n def text_fun(self,message=\"No message passed.\",color=\"black\"):\r\n self.color=color\r\n self.msg=message\r\n try:\r\n os.system(\"tput sgr0\")\r\n print text_color[self.color]\r\n print (\"%s\" % self.msg)\r\n os.system(\"tput sgr0\")\r\n except Exception ,e:\r\n print (\"%s\" % self.msg)\r\n log_func(e)\r\n def text_banner(self):\r\n msg='''\r\n -----------------------------------------------------\t\r\n -- SElinux+ MYSQL Performance Tunning Script --\r\n -----------------------------------------------------\r\n -by qfong \r\n '''\r\n self.text_fun(msg,\"boldblue\")\r\n def world_fun(self,message=\"No message passed.\",color=\"black\"):\r\n self.color=color\r\n self.msg=message\r\n try:\r\n os.system(\"tput sgr0\")\r\n sys.stdout.write( text_color[self.color])\r\n sys.stdout.write(\"%s\" % self.msg)\r\n reset=os.popen('tput sgr0').read()\r\n sys.stdout.write(reset)\r\n except Exception ,e:\r\n print (\"%s\" % self.msg)\r\n log_func(e)\r\nglobal mysql\r\ncount=0\r\n\r\ninfo={\r\n \"user\" : \"root\",\r\n \"pwd\": \"\",\r\n \"host\": \"localhost\",\r\n \"port\" : \"3306\"\r\n }\r\nclass checkMysql(object):\r\n color=setColor()\r\n def __init__(self):\r\n pass\r\n\r\n def check_mysql_socket(self):\r\n socket=None \r\n (ret,res)=execute_command([\"cat\",\".my.cnf\"])\r\n if ret == 0: \r\n cnf_socket_path=(re.findall(r\"(?<=socket=).+?(?=')\",str(res))) \r\n else:\r\n cnf_socket_path=None\r\n if cnf_socket_path:\r\n if not os.system(\"test -S %s\" % (cnf_socket_path[0])):\r\n socket= cnf_socket_path\r\n elif not os.system(\"test -S \/var\/lib\/mysql\/mysql.sock\"):\r\n socket=\"\/var\/lib\/mysql\/mysql.sock\"\r\n elif not os.system(\"test -S \/var\/run\/mysqld\/mysqld.sock\"):\r\n socket=\"\/var\/run\/mysqld\/mysqld.sock\"\r\n elif not os.system(\"test -S \/tmp\/mysql.sock\"):\r\n socket=\"\/tmp\/mysql.sock\"\r\n else:\r\n status, output = commands.getstatusoutput(\"netstat -ln | awk '\/mysql(d)?\\.sock\/ { print $9 }' | head -1\")\r\n if not status:\r\n output=0 \r\n socket=output\r\n if debug:\r\n print \"Test for check_mysql_socket functions\"\r\n print (\"socket :%s\" % socket)\r\n\r\n return socket\r\n def connect(self,pwd,user,port,host):\r\n if pwd:\r\n mysql = (\"mysql -u%s -p%s -h%s -P%s\" % (user,pwd,host,port))\r\n\r\n else: \r\n mysql = (\"mysql -u%s -h%s -P%s\" % (user,host,port))\r\n\r\n status, output = commands.getstatusoutput(\"%s -Bse \\\"SELECT SUBSTRING_INDEX(VERSION(), '.', +2)\\\"\" % mysql)\r\n\r\n if not status:\r\n return True\r\n \r\n def inputpwd(self):\r\n try:\r\n user=raw_input('User[root]: ')\r\n inPassword=getpass.getpass('Password[Null]: ')\r\n port=raw_input('Port[3306]: ')\r\n host=raw_input('Host[localhost]: ')\r\n if not user :\r\n user='root'\r\n if not port :\r\n port='3306'\r\n if not host:\r\n host='localhost'\r\n\r\n \r\n \r\n if self.connect(inPassword,user,port,host):\r\n #print inPassword\r\n #return inPassword\r\n info['user']=user\r\n info['pwd']=inPassword\r\n info['host']=host\r\n info['port']=port\r\n \r\n\r\n else:\r\n self.inputpwd()\t\t\t\r\n \r\n except KeyboardInterrupt ,e:\r\n print e\r\n sys.exit(0)\r\n def mysql_connect(self):\r\n\r\n socket=self.check_mysql_socket()\r\n \r\n user_info={\r\n \"user\" : \"root\",\r\n \"pwd\": \"\",\r\n \"ipa\": \"localhost\",\r\n \"socket\" : \"socket\"\r\n }\r\n\r\n #user_info['pwd']=''.join(self.inputpwd())\r\n \r\n user_info['pwd']=info['pwd']\r\n user_info['user']=info['user']\r\n user_info['ipa']=info['host']\r\n \r\n user_info['socket']=''.join(self.check_mysql_socket())\r\n \r\n global mysql\r\n if user_info['pwd']:\r\n mysql = (\"mysql -u%s -p%s -h%s -S%s\" % (user_info['user'],user_info['pwd'],user_info['ipa'],user_info['socket']))\r\n else:\r\n mysql = (\"mysql -u%s -h%s -S%s\" % (user_info['user'],user_info['ipa'],user_info['socket']))\r\n\r\n return mysql\r\n \r\n def mysql_variable(self,parameter):\r\n \r\n status, output = commands.getstatusoutput(\"%s -Bse \\\"show \/*!50000 global *\/ variables like %s\\\"|awk '{print $2}' \" % (mysql,parameter))\r\n if debug:\r\n print \"Test for mysql_variable\"\r\n print (\"output:%s\" % output)\r\n print (\"status:%s\" % status)\r\n if status == 0:\r\n return output\r\n \r\n def mysql_variableTab(self,parameter):\r\n\r\n status, output = commands.getstatusoutput(\"%s -Bse \\\"show \/*!50000 global *\/ variables like %s\\\"|awk -F'\\t' '{print $2}' \" % (mysql,parameter))\r\n if debug:\r\n print \"Test for mysql_variableTAB\"\r\n print (\"output:%s\" % output)\r\n print (\"status:%s\" % status)\r\n if status == 0:\r\n return output\r\n \r\n\r\n def mysql_status(self,parameter):\r\n\r\n status, output = commands.getstatusoutput(\"%s -Bse \\\"show \/*!50000 global *\/ status like %s\\\"|awk -F'\\t' '{print $2}' \" % (mysql,parameter))\r\n if debug:\r\n print \"Test for mysql_status\"\r\n print (\"output:%s\" % output)\r\n print (\"status:%s\" % status)\r\n if status == 0:\r\n return output\r\n\r\n '''\r\n format time for human read\r\n '''\r\n def forhumantime(self,time):\r\n day=int(time)\/86400\r\n hour=int(time)%86400\/3600\r\n minutes=int(time)%86400%3600\/60\r\n second=int(time)%86400%3600%60\r\n return (\"%s day %sh:%sm:%ss\" % (day,hour,minutes,second))\r\n if debug:\r\n print (\"%s day %sh:%sm:%ss\" % (day,hour,minutes,second))\r\n def forhumansize(self,size):\r\n size=float(size)\r\n if size > 1073741824:\r\n util='G'\r\n level=3\r\n elif size > 1048576:\r\n util='M'\r\n level=2\r\n elif size > 1024:\r\n util='K'\r\n level=1\r\n else :\r\n util='B'\r\n level=0\r\n dividend=1\r\n for i in range(0 ,level): \r\n dividend *= 1024\r\n rsize =size\/dividend\r\n return (\"%4.2f %s\" % (rsize,util))\r\n\r\n def mysql_info(self):\r\n '''\r\n mysql share info\r\n '''\r\n mysql=self.mysql_connect()\r\n\r\n status, output = commands.getstatusoutput(\"%s -Bse \\\"SELECT SUBSTRING_INDEX(VERSION(), '.', +2)\\\"\" % mysql)\r\n mysqlmajor_version=output\r\n status, output = commands.getstatusoutput(\"%s -Bse \\\"SELECT LEFT(REPLACE(SUBSTRING_INDEX(VERSION(), '-', +1), '.', ''),4)\\\"\" % mysql)\r\n mysql_version_num=output\r\n '''\r\n mysql base info\r\n '''\r\n self.color.text_fun(\"-MySQL INFO:\",\"boldblue\")\r\n version=self.mysql_variable(\"\\'version\\'\")\r\n self.color.world_fun(\"MySQL Version: %s \\n\" % version )\r\n\r\n uptime=self.mysql_status(\"\\'Uptime\\'\")\r\n rduptime=self.forhumantime(uptime)\r\n self.color.world_fun(\"Uptime = %s \\n\" % rduptime )\r\n questions=self.mysql_status (\"\\'Questions\\'\")\r\n question_per_sec=int(questions)\/int(uptime) \r\n self.color.world_fun(\"Avg. qps = %s \\n\" % question_per_sec) \r\n self.color.world_fun(\"Total Questions = %s \\n\" % questions)\r\n threads=self.mysql_status(\"\\'Threads_connected\\'\") \r\n self.color.world_fun(\"Threads Connected = %s \\n\" % threads)\r\n if uptime < 172800:\r\n self.color.text_fun(\"Server has been running for over 48hrs.\")\r\n else:\r\n self.color.text_fun(\"Warning: Server not been running for at least 48hrs.\",\"boldred\")\r\n\r\n\r\n '''\r\n mysql SLOW QUERIES\r\n '''\r\n self.color.text_fun(\"-MySQL SLOW QUERIES:\",\"boldblue\")\r\n slow_queries=self.mysql_status(\"\\'Slow_queries\\'\")\r\n long_query_time=self.mysql_variable(\"\\'long_query_time\\'\")\r\n log_slow_queries=self.mysql_variable(\"\\'log%queries\\'\")\r\n if os.path.isfile(\"\/etc\/my.cnf\"):\r\n if not log_slow_queries:\r\n status, output = commands.getstatusoutput(\"grep log_slow_queries \/etc\/my.cnf \") \r\n log_slow_queries=output\r\n if log_slow_queries == 'ON' : \r\n self.color.text_fun(\"The slow query log is enabled.\")\r\n elif log_slow_queries == 'OFF':\r\n self.color.world_fun(\"The slow query log is\")\r\n self.color.world_fun(\" NOT \",\"boldred\")\r\n self.color.world_fun(\"enabled.\\n\")\r\n elif not log_slow_queries:\r\n self.color.world_fun(\"The slow query log is\")\r\n self.color.world_fun(\" NOT \",\"boldred\")\r\n self.color.world_fun(\"enabled.\\n\")\r\n else:\r\n self.color.text_fun((\"Error:%s\"% log_slow_queries),\"boldred\")\r\n self.color.world_fun('Current long_query_time =%s sec\\n'% long_query_time)\r\n self.color.world_fun(\"You have \")\r\n self.color.world_fun(slow_queries,\"boldred\")\r\n self.color.world_fun(\" out of \")\r\n self.color.world_fun(questions,\"boldred\")\r\n self.color.world_fun(\" that take longer than %s sec. to complete\\n\" % long_query_time)\r\n prefered_query_time=5\r\n if float(long_query_time) < float(prefered_query_time):\r\n self.color.text_fun((\"Your long_query_time may be too high, I typically set this under %s sec\" % prefered_query_time),\"red\") \r\n else:\r\n self.color.world_fun(\"Your long_query_time seems to be fine\\n\",\"green\")\r\n if debug: \r\n print \"Test for mysql slow queries\"\r\n print (\"log_slow_queries %s:\" % log_slow_queries)\r\n self.color.world_fun((\"Error:%s\\n\"% log_slow_queries),\"boldred\")\r\n\r\n '''\r\n BINARY UPDATE LOG\r\n '''\r\n self.color.text_fun(\"-MySQL BINARY UPDATE LOG:\",\"boldblue\")\r\n log_bin=self.mysql_variable(\"\\'log_bin\\'\")\r\n max_binlog_size=self.mysql_variable(\"\\'max_binlog_size\\'\")\r\n expire_logs_days=self.mysql_variable(\"\\'expire_logs_days\\'\")\r\n sync_binlog=self.mysql_variable(\"\\'sync_binlog\\'\")\r\n max_binlog_cache_size=self.mysql_variable(\"\\'max_binlog_cache_size\\'\")\r\n if debug:\r\n print (\"log_bin:%s\" % log_bin)\r\n print (\"max_binlog_size:%s\" % max_binlog_size)\r\n print (\"expire_logs_days:%s\" % expire_logs_days)\r\n print (\"sync_binlog:%s\" % sync_binlog)\r\n print (\"max_binlog_cache_size:%s\" % max_binlog_cache_size)\r\n if log_bin == 'ON':\r\n self.color.world_fun(\"The binary update log is enabled\\n\")\r\n if expire_logs_days is None:\r\n self.color.text_fun(\"The max_binlog_size is not set. The binary log will rotate when it reaches 1GB\",\"red\")\r\n if expire_logs_days == '0':\r\n self.color.world_fun(\"The expire_logs_days is NOT set.\\n\",\"boldred\")\r\n self.color.world_fun(\"The mysqld will retain the entire binary log until \\n\",\"red\")\r\n self.color.world_fun(\"RESET MASTER or PURGE MASTER LOGS commands are run manually \\n\",\"red\")\r\n self.color.world_fun(\"Setting expire_logs_days will allow you to remove old binary logs automatically \\n\",\"yellow\")\r\n if sync_binlog == '0' :\r\n self.color.world_fun(\"Binlog sync is not enabled, you could loose binlog records during a server crash \\n\",\"red\") \r\n else:\r\n self.color.world_fun(\"The binary update log is \")\r\n self.color.world_fun(\"NOT \",\"boldred\")\r\n self.color.world_fun(\"enabled.\\n\")\r\n self.color.world_fun(\"You will not be able to do point in time recovery.\\n\",\"red\")\r\n\r\n '''\r\n MAX CONNECTIONS\r\n '''\r\n self.color.text_fun(\"-MySQL MAX CONNECTIONS:\",\"boldblue\")\r\n max_connections=self.mysql_variable(\"\\'max_connections\\'\")\r\n max_used_connections=self.mysql_status(\"\\'Max_used_connections\\'\")\r\n threads_connected=self.mysql_status(\"\\'Threads_connected\\'\")\r\n connections_ratio=float(max_used_connections)*100\/float(max_connections)\r\n if debug:\r\n print (\"max_connections : %s\" % max_connections)\r\n print (\"max_used_connections : %s\" % max_used_connections)\r\n print (\"threads_connected : %s\" % threads_connected)\r\n print (\"connections_ratio : %s\" % connections_ratio)\r\n \r\n self.color.world_fun(\"Current max_connections = %s \\n\" % max_connections)\r\n self.color.world_fun(\"Current threads_connected = %s \\n\" % threads_connected)\r\n self.color.world_fun(\"Currentmax_used_connections = %s \\n\" % max_used_connections)\r\n self.color.world_fun(\"The number of used connections is \")\r\n text_color=None\r\n error='0'\r\n if int(connections_ratio) > 84:\r\n text_color=\"red\"\r\n error='1'\r\n elif int(connections_ratio) < 16:\r\n text_color=\"red\"\r\n error='2'\r\n else:\r\n text_color=\"green\"\r\n error='0'\r\n #print text_color \r\n self.color.world_fun((\"%4.2f%%\" % connections_ratio),text_color)\r\n self.color.world_fun(\" of the configured maximum.\\n\")\r\n if error=='1':\r\n self.color.world_fun(\"You should raise max_connections.\\n\",text_color)\r\n elif error=='2':\r\n self.color.world_fun(\"You are using less than 10% of your configured max_connections.\\n\",text_color)\r\n self.color.world_fun(\"Lowering max_connections could help to avoid an over-allocation of memory\\n\",text_color)\r\n self.color.world_fun(\"See \\\"MEMORY USAGE\\\" section to make sure you are not over-allocating\\n\",text_color)\r\n else:\r\n self.color.world_fun(\"Your max_connections variable seems to be fine.\\n\",text_color)\r\n text_color=None \r\n\r\n '''\r\n WORKER THREADS\r\n '''\r\n self.color.text_fun(\"-MySQL WORKER THREADS:\",\"boldblue\")\r\n threads_created1=self.mysql_status(\"\\'Threads_connected\\'\")\r\n time.sleep(1)\r\n threads_created2=self.mysql_status(\"\\'Threads_connected\\'\")\r\n threads_cached=self.mysql_status(\"\\'Threads_cached\\'\")\r\n uptime=self.mysql_status(\"\\'Uptime\\'\")\r\n thread_cache_size=self.mysql_variable(\"\\'thread_cache_size\\'\")\r\n historic_threads_per_sec=float(threads_created1)\/float(uptime)\r\n current_threads_per_sec=int(threads_created2)-int(threads_created1)\r\n self.color.world_fun(\"Current thread_cache_size = %s \\n\" % thread_cache_size)\r\n self.color.world_fun(\"Current threads_cached = %s \\n\" % threads_cached)\r\n self.color.world_fun(\"Current threads_per_sec = %s \\n\" % current_threads_per_sec)\r\n self.color.world_fun(\"Historic threads_per_sec = %4.2f \\n\" % historic_threads_per_sec)\r\n if int(historic_threads_per_sec) > 2 and int(threads_cached) < 1 :\r\n self.color.world_fun(\"Threads created per\/sec are overrunning threads cached\\n\",\"red\")\r\n elif int(historic_threads_per_sec) > 2:\r\n self.color.world_fun(\"Threads created per\/sec are overrunning threads cached\\n\",\"red\")\r\n self.color.world_fun(\"You should raise thread_cache_sized\\n\",\"red\")\r\n else:\r\n self.color.world_fun(\"Your thread_cache_size is fine\\n\",\"green\")\r\n if debug:\r\n print (\"threads_created1 : %s\" % threads_created1)\r\n print (\"threads_created2 : %s\" % threads_created2)\r\n print (\"threads_cached : %s\" % threads_cached)\r\n print (\"uptime : %s\" % uptime)\r\n print (\"thread_cache_size : %s\" % thread_cache_size)\r\n print (\"historic_threads_per_sec : %s\" % historic_threads_per_sec)\r\n print (\"current_threads_per_sec : %s\" % current_threads_per_sec)\r\n\r\n '''\r\n KEY BUFFER\r\n '''\r\n self.color.text_fun(\"-MySQL KEY BUFFER:\",\"boldblue\")\r\n key_read_requests=self.mysql_status(\"\\'Key_read_requests\\'\")\r\n key_reads=self.mysql_status(\"\\'Key_reads\\'\")\r\n key_blocks_used=self.mysql_status(\"\\'Key_blocks_used\\'\")\r\n key_blocks_unused=self.mysql_status(\"\\'Key_blocks_unused\\'\")\r\n key_cache_block_size=self.mysql_variable(\"\\'key_cache_block_size\\'\")\r\n key_buffer_size=self.mysql_variable(\"\\'key_buffer_size\\'\")\r\n datadir=self.mysql_variable(\"\\'datadir\\'\")\r\n mysql_version_compile_machine=self.mysql_variable(\"\\'version_compile_machine\\'\")\r\n #mysql=\"mysql\"\r\n status, output = commands.getstatusoutput(\"%s -Bse \\\"\/*!50000 SELECT IFNULL(SUM(INDEX_LENGTH),0) from information_schema.TABLES where ENGINE=\\'MyISAM\\' *\/ \\\" \" % (mysql))\r\n myisam_indexes=output\r\n if not myisam_indexes :\r\n status, output = commands.getstatusoutput(\"find %s -name '*.MYI' -exec du -b '{}' \\; 2>&1 | awk '{ s += $1 } END { printf(\\\"%%.0f\\\\n\\\", s )}' \" % (datadir))\r\n myisam_indexes=output\r\n if key_reads == '0':\r\n self.color.world_fun(\"No key reads?!\\n\",\"boldred\")\r\n self.color.world_fun(\"Seriously look into using some indexes\\n\",\"red\")\r\n key_cache_miss_rate=0\r\n key_buffer_free=float(key_blocks_unused)*float(key_cache_block_size)\/float(key_buffer_size)*100\r\n key_buffer_freeINT=int(key_buffer_free)\r\n else :\r\n key_cache_miss_rate=float(key_read_requests)\/float(key_reads)\r\n if not key_blocks_unused:\r\n key_buffer_free=float(key_blocks_unused)*float(key_cache_block_size)\/float(key_buffer_size)*100\r\n key_buffer_freeINT=int(key_buffer_free)\r\n else:\r\n key_buffer_free='Unknown'\r\n key_buffer_freeINT=75\r\n \r\n myisam_indexesHR=self.forhumansize(myisam_indexes);\r\n key_buffer_sizeHR=self.forhumansize(key_buffer_size);\r\n self.color.world_fun(\"Current MyISAM index space = %s \\n\" % myisam_indexesHR)\r\n self.color.world_fun(\"Current key_buffer_size = %s \\n\" % key_buffer_sizeHR)\r\n self.color.world_fun(\"Key cache miss rate is : %s \\n\" % key_cache_miss_rate)\r\n self.color.world_fun(\"Key buffer free ratio = %s \\n\" % key_buffer_freeINT)\r\n if mysqlmajor_version == '5.1' and int(mysql_version_num) < 5123:\r\n self.color.world_fun(\"Using key_buffer_size > 4GB will cause instability in versions prior to 5.1.23\\n\",\"boldred\")\r\n if int(key_cache_miss_rate) <= 100 and int(key_cache_miss_rate) <= 0 and int(key_buffer_freeINT) <= 0 :\r\n self.color.world_fun(\"You could increase key_buffer_size\\n\",\"boldred\")\r\n self.color.world_fun(\"It is safe to raise this up to 1\/4 of total system memory;\\n\",\"red\")\r\n self.color.world_fun(\"assuming this is a dedicated database server.\\n\",\"red\")\r\n elif int(key_buffer_freeINT) <= 20 and int(key_buffer_size) <= int(myisam_indexes):\r\n self.color.world_fun(\"You could increase key_buffer_size\\n\",\"boldred\")\r\n self.color.world_fun(\"It is safe to raise this up to 1\/4 of total system memory;\\n\",\"red\")\r\n self.color.world_fun(\"assuming this is a dedicated database server.\\n\",\"red\")\r\n elif int(key_buffer_freeINT) <= 50 and int(key_cache_miss_rate) >= 10000 :\r\n self.color.world_fun(\"Your key_buffer_size seems to be too high;\\n\",\"boldred\")\r\n self.color.world_fun(\"Perhaps you can use these resources elsewhere\\n\",\"red\")\r\n else :\r\n self.color.world_fun(\"Your key_buffer_size seems to be fine.\\n\",\"green\")\r\n if debug:\r\n print (\"key_read_requests : %s\" % key_read_requests)\r\n print (\"key_reads : %s\" % key_reads)\r\n print (\"key_blocks_used : %s\" % key_blocks_used)\r\n print (\"key_blocks_unused : %s\" % key_blocks_unused)\r\n print (\"key_cache_block_size : %s\" % key_cache_block_size)\r\n print (\"key_buffer_size : %s\" % key_buffer_size)\r\n print (\"datadir : %s\" % datadir)\r\n print (\"mysql_version_compile_machine : %s\" % mysql_version_compile_machine)\r\n print (\"myisam_indexes : %s\" % myisam_indexes)\r\n print (\"key_cache_miss_rate : %s\" % key_cache_miss_rate)\r\n print (\"key_buffer_freeINT : %s\" % key_buffer_freeINT)\r\n print (\"myisam_indexesHR : %s\" % myisam_indexesHR)\r\n print (\"key_buffer_sizeHR : %s\" % key_buffer_sizeHR) \r\n \r\n '''\r\n QUERY CACHE\r\n '''\r\n self.color.text_fun(\"-MySQL QUERY CACHE:\",\"boldblue\")\r\n mysql_version=self.mysql_variable(\"\\'version\\'\")\r\n query_cache_size=self.mysql_variable(\"\\'query_cache_size\\'\")\r\n query_cache_limit=self.mysql_variable(\"\\'query_cache_limit\\'\")\r\n query_cache_min_res_unit=self.mysql_variable(\"\\'query_cache_min_res_unit\\'\")\r\n qcache_free_memory=self.mysql_status(\"\\'Qcache_free_memory\\'\")\r\n qcache_total_blocks=self.mysql_status(\"\\'Qcache_total_blocks\\'\")\r\n qcache_free_blocks=self.mysql_status(\"\\'Qcache_free_blocks\\'\")\r\n qcache_lowmem_prunes=self.mysql_status(\"\\'Qcache_lowmem_prunes\\'\")\r\n if not query_cache_size:\r\n self.color.world_fun(\"You are using MySQL $mysql_version, no query cache is supported\\n\",\"red\")\r\n elif query_cache_size == '0':\r\n self.color.world_fun(\"Query cache is supported but not enabled\\n\",\"red\")\r\n else:\r\n qcache_used_memory=int(query_cache_size)-int(qcache_free_memory)\r\n qcache_mem_fill_ratio=float(qcache_used_memory)* 100 \/ float(query_cache_size)\r\n qcache_mem_fill_ratioINT=int(qcache_mem_fill_ratio)\r\n self.color.world_fun(\"Query cache is enabled\\n\",\"green\")\r\n query_cache_sizeHR=self.forhumansize(query_cache_size)\r\n self.color.world_fun(\"Current query_cache_size = %s \\n\"% query_cache_sizeHR)\r\n qcache_used_memoryHR=self.forhumansize(qcache_used_memory)\r\n self.color.world_fun(\"Current query_cache_used = %s \\n\"% qcache_used_memoryHR)\r\n query_cache_limitHR=self.forhumansize(query_cache_limit)\r\n self.color.world_fun(\"Current query_cache_limit = %s \\n\"% query_cache_limitHR)\r\n self.color.world_fun(\"Query cache Memory fill ratio = %4.2f%% \\n\" % qcache_mem_fill_ratio)\r\n if not query_cache_min_res_unit:\r\n self.color.world_fun(\"No query_cache_min_res_unit is defined. Using MySQL < 4.1 cache fragmentation can be inpredictable\\n\",\"yellow\")\r\n else:\r\n query_cache_min_res_unitHR=self.forhumansize(query_cache_min_res_unit) \r\n self.color.world_fun(\"Current query_cache_min_res_unit = %s \\n\" % query_cache_min_res_unitHR)\r\n if int(qcache_free_blocks) > 2 and int(qcache_total_blocks) < 0 :\r\n qcache_percent_fragmented=float(qcache_free_blocks)*100\/float(qcache_total_blocks)\r\n qcache_percent_fragmentedINT=int(qcache_percent_fragmented)\r\n if int(qcache_percent_fragmentedINT) > 20 :\r\n self.color.world_fun((\"Query Cache is %s%% fragmented \\n\" % qcache_percent_fragmentedINT),\"red\")\r\n self.color.world_fun(\"Run \\\"FLUSH QUERY CACHE\\\" periodically to defragment the query cache memor \\n\",\"red\")\r\n self.color.world_fun(\"If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation. \\n\" ,\"red\")\r\n if int(qcache_mem_fill_ratioINT) < 25 :\r\n self.color.world_fun(\"Your query_cache_size seems to be too high\\n\",\"red\")\r\n self.color.world_fun(\"Perhaps you can use these resources elsewhere\\n\",\"red\")\r\n if int(qcache_lowmem_prunes) >= 50 and qcache_mem_fill_ratioINT >= 80:\r\n self.color.world_fun(\"However,\") \r\n self.color.world_fun((\"%s \" % qcache_lowmem_prunes ),\"boldred\")\r\n self.color.world_fun(\"queries have been removed from the query cache due to lack of memory \\n\") \r\n self.color.world_fun(\"Perhaps you should raise query_cache_size.\\n\",\"boldred\")\r\n self.color.world_fun(\"MySQL won't cache query results that are larger than query_cache_limit in size.\\n\",\"yellow\")\r\n\r\n if debug:\r\n print (\"mysql_version : %s\" % mysql_version)\r\n print (\"query_cache_size : %s\" % query_cache_size)\r\n print (\"query_cache_limit : %s\" % query_cache_limit)\r\n print (\"query_cache_min_res_unit : %s\" % query_cache_min_res_unit)\r\n print (\"qcache_free_memory : %s\" % qcache_free_memory)\r\n print (\"qqcache_total_blocks : %s\" % qcache_total_blocks)\r\n print (\"qcache_free_blocks : %s\" % qcache_free_blocks)\r\n print (\"qcache_lowmem_prunes : %s\" % qcache_lowmem_prunes)\r\n print (\"qcache_used_memory : %s\" % qcache_used_memory)\r\n print (\"qcache_mem_fill_ratio : %s\" % qcache_mem_fill_ratio)\r\n\r\n '''\r\n SORT OPERATIONS\r\n\r\n '''\r\n self.color.text_fun(\"-MySQL SORT OPERATIONS:\",\"boldblue\")\r\n sort_merge_passes=self.mysql_status(\"\\'Sort_merge_passes\\'\")\r\n sort_scan=self.mysql_status(\"\\'Sort_scan\\'\")\r\n sort_range=self.mysql_status(\"\\'Sort_range\\'\")\r\n sort_buffer_size=self.mysql_variable(\"\\'sort_buffer%\\'\")\r\n read_rnd_buffer_size=self.mysql_variable(\"\\'read_rnd_buffer_size\\'\")\r\n total_sorts=int(sort_scan)+int(sort_range)\r\n if not read_rnd_buffer_size:\r\n read_rnd_buffer_size=self.mysql_variable(\"\\'record_buffer\\'\")\r\n sort_buffer_size=int(sort_buffer_size)+8\r\n read_rnd_buffer_size=int(read_rnd_buffer_size)+8 \r\n sort_buffer_sizeHR=self.forhumansize(sort_buffer_size)\r\n self.color.world_fun(\"Current sort_buffer_size = %s \\n\" % sort_buffer_sizeHR)\r\n read_rnd_buffer_sizeHR=self.forhumansize(read_rnd_buffer_size) \r\n self.color.world_fun(\"read_rnd_buffer_size = %s \\n\" % read_rnd_buffer_sizeHR)\r\n if int(total_sorts) == 0 : \r\n self.color.world_fun(\"No sort operations have been performed\\n\")\r\n passes_per_sort=0\r\n if int(sort_merge_passes) != 0:\r\n passes_per_sort=float(sort_merge_passes)\/float(total_sorts)\r\n else:\r\n passes_per_sort=0\r\n if passes_per_sort >= 2:\r\n self.color.world_fun(\"On average\")\r\n self.color.world_fun(passes_per_sort,\"boldred\")\r\n self.color.world_fun(\"sort merge passes are made per sort operation\\n\")\r\n self.color.world_fun(\"You should raise your sort_buffer_size read_rnd_buffer_size \\n\")\r\n else:\r\n self.color.world_fun(\"Sort buffer seems to be fine\\n\",\"green\")\r\n if debug:\r\n print (\"sort_merge_passes : %s\" % sort_merge_passes)\r\n print (\"sort_scan : %s\" % sort_scan)\r\n print (\"sort_range : %s\" % sort_range)\r\n print (\"sort_buffer_size : %s\" % sort_buffer_size)\r\n print (\"read_rnd_buffer_size : %s\" % read_rnd_buffer_size)\r\n print (\"total_sorts : %s\" % total_sorts)\r\n print (\"passes_per_sort : %s\" % passes_per_sort)\r\n '''\r\n JOINS\r\n '''\r\n self.color.text_fun(\"-MySQL JOINS:\",\"boldblue\")\r\n select_full_join=self.mysql_status(\"\\'Select_full_join\\'\")\r\n select_range_check=self.mysql_status(\"\\'Select_range_check\\'\")\r\n join_buffer_size=self.mysql_variable(\"\\'join_buffer%\\'\")\r\n join_buffer_size=int(join_buffer_size)+4096\r\n join_buffer_sizeHR=self.forhumansize(join_buffer_size)\r\n self.color.world_fun(\"Current join_buffer_size = %s \\n\" % join_buffer_sizeHR)\r\n self.color.world_fun((\"You have had %s queries where a join could not use an index properly \\n\" % select_full_join))\r\n if int(select_range_check) == 0 and int(select_full_join) == 0:\r\n self.color.world_fun(\"Your joins seem to be using indexes properly \\n\",\"green\")\r\n print_error=None\r\n raise_buffer=None\r\n if int(select_full_join) > 0 :\r\n print_error= 'true'\r\n raise_buffer= 'true'\r\n if int(select_range_check) > 0:\r\n self.color.world_fun((\"You have had %s joins without keys that check for key usage after each row\\n\" % select_range_check),\"green\")\r\n print_error= 'true'\r\n raise_buffer= 'true'\r\n if int(join_buffer_size) > 4194304 :\r\n self.color.world_fun(\"join_buffer_size >= 4 M\\nThis is not advised \\n\",\"boldred\")\r\n if print_error :\r\n self.color.world_fun(\"hen look for non indexed joins in the slow query log \\n\",\"red\")\r\n if print_error :\r\n self.color.world_fun(\"If you are unable to optimize your queries you may want to increase your join_buffer_size to accommodate larger joins in one pass\\n\",\"red\") \r\n\r\n if debug:\r\n print (\"select_full_join : %s\" % select_full_join)\r\n print (\"select_range_check : %s\" % select_range_check)\r\n print (\"join_buffer_size : %s\" % join_buffer_size)\r\n print (\"join_buffer_sizeHR : %s\" % join_buffer_sizeHR)\r\n\r\n\r\n '''\r\n TEMP TABLES\r\n '''\r\n self.color.text_fun(\"-MySQL TEMP TABLES:\",\"boldblue\")\r\n created_tmp_tables=self.mysql_status(\"\\'Created_tmp_tables\\'\")\r\n created_tmp_disk_tables=self.mysql_status(\"\\'Created_tmp_disk_tables\\'\")\r\n tmp_table_size=self.mysql_variable(\"\\'tmp_table_size\\'\")\r\n max_heap_table_size=self.mysql_variable(\"\\'max_heap_table_size\\'\")\r\n if int(created_tmp_tables) == 0:\r\n tmp_disk_tables=0\r\n else:\r\n tmp_disk_tables=float(created_tmp_disk_tables)*100\/(int(created_tmp_tables)+int(created_tmp_disk_tables))\r\n max_heap_table_sizeHR=self.forhumansize(max_heap_table_size)\r\n self.color.world_fun(\"Current max_heap_table_size = %s \\n\" % max_heap_table_sizeHR)\r\n tmp_table_sizeHR=self.forhumansize(tmp_table_size)\r\n self.color.world_fun(\"Current tmp_table_size = %s \\n\" % tmp_table_sizeHR)\r\n self.color.world_fun(\"Of %s temp tables, %4.2f%% were created on disk\\n\" % (created_tmp_tables,tmp_disk_tables))\r\n if int(tmp_disk_tables) > int(max_heap_table_size):\r\n self.color.world_fun(\"Effective in-memory tmp_table_size is limited to max_heap_table_size.\\n\",\"yellow\")\r\n if int(tmp_disk_tables) >= 25:\r\n self.color.world_fun(\"Perhaps you should increase your tmp_table_size and\/or max_heap_table_size.\\n\",\"boldred\")\r\n self.color.world_fun(\"to reduce the number of disk-based temporary tables\\n\",\"boldred\")\r\n self.color.world_fun(\"Note! BLOB and TEXT columns are not allow in memory tables.\\n\",\"yellow\")\r\n self.color.world_fun(\"If you are using these columns raising these values might not impact your\\nratio of on disk temp tables.\\n\",\"yellow\")\r\n else :\r\n self.color.world_fun(\"Created disk tmp tables ratio seems fine\\n\",\"green\")\r\n\r\n if debug:\r\n print (\"created_tmp_tables : %s\" % created_tmp_tables)\r\n print (\"created_tmp_disk_tables : %s\" % created_tmp_disk_tables)\r\n print (\"tmp_table_size : %s\" % tmp_table_size)\r\n print (\"max_heap_table_size : %s\" % max_heap_table_size)\r\n print (\"tmp_disk_tables : %s\" % tmp_disk_tables)\r\n\r\n \r\n '''\r\n OPEN FILES LIMIT\r\n '''\r\n self.color.text_fun(\"-MySQL OPEN FILES LIMIT:\",\"boldblue\")\r\n open_files_limit=self.mysql_variable(\"\\'open_files_limit\\'\")\r\n open_files=self.mysql_status(\"\\'Open_files\\'\")\r\n if not open_files_limit or open_files_limit == 0:\r\n open_files_limit=os.popen(\"ulimit -n\").read()\r\n cant_override=1\r\n #print open_files_limit\r\n else:\r\n cant_override=0\r\n self.color.world_fun(\"Current open_files_limit = %s \\n\" % open_files_limit)\r\n open_files_ratio=float(open_files)*100\/float(open_files_limit)\r\n self.color.world_fun(\"The open_files_limit should typically be set to at least 2x-3x\\n\",\"yellow\") \r\n self.color.world_fun(\"that of table_cache if you have heavy MyISAM usage.\\n\",\"yellow\") \r\n if int(open_files_ratio) >= 75:\r\n self.color.world_fun(\"You currently have open more than 75% of your open_files_limit\\n\",\"boldred\")\r\n if cant_override == '1':\r\n self.color.world_fun(\"You should set a higer value for ulimit -u in the mysql startup script then restart mysqld\\n\",\"boldred\") \r\n elif cant_override == '0':\r\n self.color.world_fun(\"You should set a higher value for open_files_limit in my.cnf\\n\",\"boldred\") \r\n else:\r\n self.color.world_fun(\"ERROR can't determine if mysqld override of ulimit is allowed\\n\",\"boldred\")\r\n else:\r\n self.color.world_fun(\"Your open_files_limit value seems to be fine\\n\",\"green\") \r\n if debug:\r\n print (\"open_files_limit : %s\" % open_files_limit)\r\n print (\"open_files : %s\" % open_files)\r\n print (\"open_files_ratio : %s\" % open_files_ratio)\r\n '''\r\n TABLE CACHE\r\n '''\r\n self.color.text_fun(\"-MySQL TABLE CACHE:\",\"boldblue\")\r\n datadir=self.mysql_variable(\"\\'datadir\\'\")\r\n table_cache=self.mysql_variable(\"\\'table_cache\\'\")\r\n table_open_cache=self.mysql_variable(\"\\'table_open_cache\\'\")\r\n table_definition_cache=self.mysql_variable(\"\\'table_definition_cache\\'\")\r\n open_tables=self.mysql_status(\"\\'Open_tables\\'\")\r\n opened_tables=self.mysql_status(\"\\'Opened_tables\\'\")\r\n open_table_definitions=self.mysql_status(\"\\'Open_table_definitions\\'\")\r\n #mysql=\"mysql\"\r\n status, output = commands.getstatusoutput(\"%s -Bse \\\"\/*!50000 SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' *\/\\\"\" % (mysql))\r\n table_count=output\r\n if not table_count:\r\n table_count=os.popen(\"find $datadir 2>&1 | grep -c .frm$\").read()\r\n if table_open_cache :\r\n table_cache=table_open_cache\r\n table_cache_hit_rate=\"unkowning\"\r\n table_cache_fill=\"unkowning\"\r\n if opened_tables != '0' and table_cache != 0 :\r\n table_cache_hit_rate = float(open_tables)*100\/float(opened_tables)\r\n table_cache_fill=float(open_tables)*100\/float(table_cache)\r\n elif opened_tables == '0' and table_cache != 0 :\r\n table_cache_hit_rate = 100\r\n table_cache_fill=float(open_tables)*100\/float(table_cache)\r\n else:\r\n self.color.world_fun(\"ERROR no table_cache ?!\\n\",\"boldred\")\r\n if table_cache and not table_open_cache :\r\n self.color.world_fun(\"Current table_open_cache = %s tables \\n\" % table_cache)\r\n if table_cache :\r\n self.color.world_fun(\"Current table_open_cache = %s tables \\n\" % table_open_cache) \r\n self.color.world_fun(\"Current table_definition_cache = %s tables \\n\" % table_definition_cache) \r\n if table_count :\r\n self.color.world_fun(\"You have a total of %s tables \\n\" % table_count) \r\n if int(table_cache_fill) < 95:\r\n self.color.world_fun(\"You have \")\r\n self.color.world_fun(open_tables,\"green\") \r\n self.color.world_fun(\" open tables \\n\")\r\n self.color.world_fun(\"The table_cache value seems to be fine \\n\",\"green\")\r\n elif int(table_cache_hit_rate) <= 85 or int(table_cache_fill) >= 95 : \r\n self.color.world_fun(\"You have \")\r\n self.color.world_fun(open_tables,\"boldred\") \r\n self.color.world_fun(\" open tables \\n\")\r\n self.color.world_fun(\"Current table_cache hit rate is \") \r\n self.color.world_fun((\"%4.2f\" % table_cache_hit_rate),\"boldred\") \r\n self.color.world_fun(\", while \")\r\n self.color.world_fun((\"%4.2f\" % table_cache_fill),\"boldred\") \r\n self.color.world_fun(\" of your table cache is in use\\n\")\r\n self.color.world_fun(\"You should probably increase your table_cache \\n\",\"red\") \r\n else : \r\n self.color.world_fun(\"Current table_cache hit rate is \") \r\n self.color.world_fun((\"%4.2f\" % table_cache_hit_rate),\"green\") \r\n self.color.world_fun(\", while \")\r\n self.color.world_fun((\"%4.2f\" % table_cache_fill),\"green\") \r\n self.color.world_fun(\" of your table cache is in use\\n\")\r\n self.color.world_fun(\"The table cache value seems to be fine \\n\",\"green\") \r\n if int(table_definition_cache) and int(table_definition_cache) <= int(table_count) and int(table_count) < 100 :\r\n self.color.world_fun(\"You should probably increase your table_definition_cache value. \\n\",\"red\") \r\n \r\n if debug:\r\n print (\"datadir : %s\" % datadir)\r\n print (\"table_cache : %s\" % table_cache)\r\n print (\"table_open_cache : %s\" % table_open_cache)\r\n print (\"table_definition_cache : %s\" % table_definition_cache)\r\n print (\"open_tables : %s\" % open_tables)\r\n print (\"opened_tables : %s\" % opened_tables)\r\n print (\"open_table_definitions : %s\" % open_table_definitions)\r\n print (\"table_count : %s\" % table_count)\r\n print (\"table_cache_hit_rate : %s\" % table_cache_hit_rate)\r\n print (\"table_cache_fill : %s\" % table_cache_fill)\r\n '''\r\n TABLE LOCKING\r\n '''\r\n self.color.text_fun(\"-MySQL TABLE LOCKING:\",\"boldblue\")\r\n table_locks_waited=self.mysql_status(\"\\'Table_locks_waited\\'\")\r\n table_locks_immediate=self.mysql_status(\"\\'Table_locks_immediate\\'\")\r\n concurrent_insert=self.mysql_variable(\"\\'concurrent_insert\\'\")\r\n low_priority_updates=self.mysql_variable(\"\\'low_priority_updates\\'\")\r\n self.color.world_fun(\"Current Lock Wait ratio = \")\r\n if int(table_locks_waited) > 0 :\r\n immediate_locks_miss_rate = float(table_locks_immediate)\/float(table_locks_waited)\r\n self.color.world_fun((\"1 : %4.2f \\n\" % immediate_locks_miss_rate),\"red\")\r\n else :\r\n immediate_locks_miss_rate = 9999 \r\n self.color.world_fun((\"0 : %s \\n\" % questions),\"red\")\r\n if int(immediate_locks_miss_rate) < 5000 :\r\n self.color.world_fun(\"You may benefit from selective use of InnoDB \\n\")\r\n if low_priority_updates == 'OFF':\r\n self.color.world_fun(\"If you have long running SELECT's against MyISAM tables and perform \\nfrequent updates consider setting 'low_priority_updates=1' \\n\")\r\n if int(concurrent_insert) <= 1 and mysqlmajor_version == '5.0' or mysqlmajor_version == '5.1' : \r\n self.color.world_fun(\"If you have a high concurrency of inserts on Dynamic row-length tables \\nonsider setting 'concurrent_insert=2' \\n\")\r\n else:\r\n self.color.world_fun(\"The table locking seems to be fine \\n\",\"green\")\r\n \r\n \r\n if debug:\r\n print (\"table_locks_waited : %s\" % table_locks_waited)\r\n print (\"table_locks_immediate : %s\" % table_locks_immediate)\r\n print (\"concurrent_insert : %s\" % concurrent_insert)\r\n print (\"low_priority_updates : %s\" % low_priority_updates)\r\n '''\r\n TABLE SCANS\r\n '''\r\n self.color.text_fun(\"-MySQL TABLE SCANS:\",\"boldblue\")\r\n com_select=self.mysql_status(\"\\'Com_select\\'\")\r\n read_rnd_next=self.mysql_status(\"\\'Handler_read_rnd_next\\'\")\r\n read_buffer_size=self.mysql_variable(\"\\'read_buffer_size\\'\")\r\n if not read_buffer_size :\r\n read_buffer_size=self.mysql_variable(\"\\'record_buffer\\'\")\r\n read_buffer_sizeHR=self.forhumansize(read_buffer_size)\r\n self.color.world_fun(\"Current read_buffer_size = %s \\n\"% read_buffer_sizeHR) \r\n if int(com_select) > 0 : \r\n full_table_scans= float(read_rnd_next)\/float(com_select)\r\n self.color.world_fun(\"Current table scan ratio = %4.2f : 1 \\n\"% full_table_scans)\r\n if int(full_table_scans) >= 4000 and int(read_buffer_size) <= 2097152 :\r\n self.color.world_fun(\"You have a high ratio of sequential access requests to SELECTs \\n\",\"red\") \r\n self.color.world_fun(\"You may benefit from raising 'read_buffer_size' and\/or improving your use of indexes \\n\",\"red\") \r\n elif int(read_buffer_size) > 8388608 :\r\n self.color.world_fun(\"read_buffer_size is over 8 MB \\n\",\"red\") \r\n self.color.world_fun(\"there is probably no need for such a large read_buffe\\n\",\"red\") \r\n else:\r\n self.color.world_fun(\"read_buffer_size seems to be fine\\n\",\"green\")\r\n else:\r\n self.color.world_fun(\"read_buffer_size seems to be fine\\n\",\"green\")\r\n \r\n if debug:\r\n print (\"com_select : %s\" % com_select)\r\n print (\"read_rnd_next : %s\" % read_rnd_next)\r\n print (\"read_buffer_size : %s\" % read_buffer_size)\r\n\r\n '''\r\n INNODB STATUS\r\n '''\r\n self.color.text_fun(\"-MySQL INNODB STATUS:\",\"boldblue\")\r\n have_innodb=self.mysql_variable(\"\\'have_innodb\\'\")\r\n if have_innodb == 'YES':\r\n innodb_buffer_pool_size=self.mysql_variable(\"\\'innodb_buffer_pool_size\\'\")\r\n innodb_additional_mem_pool_size=self.mysql_variable(\"\\'innodb_additional_mem_pool_size\\'\")\r\n innodb_fast_shutdown=self.mysql_variable(\"\\'innodb_fast_shutdown\\'\")\r\n innodb_flush_log_at_trx_commit=self.mysql_variable(\"\\'innodb_flush_log_at_trx_commit\\'\")\r\n innodb_locks_unsafe_for_binlog=self.mysql_variable(\"\\'innodb_locks_unsafe_for_binlog\\'\")\r\n innodb_log_buffer_size=self.mysql_variable(\"\\'innodb_log_buffer_size\\'\")\r\n innodb_log_file_size=self.mysql_variable(\"\\'innodb_log_file_size\\'\")\r\n innodb_log_files_in_group=self.mysql_variable(\"\\'innodb_log_files_in_group\\'\")\r\n innodb_safe_binlog=self.mysql_variable(\"\\'innodb_safe_binlog\\'\")\r\n innodb_thread_concurrency=self.mysql_variable(\"\\'innodb_thread_concurrency\\'\")\r\n \r\n status, output1 = commands.getstatusoutput(\"%s -Bse \\\"\/*!50000 SELECT IFNULL(SUM(INDEX_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB' *\/\\\"\" % (mysql))\r\n\t\t \r\n innodb_indexes=output1\r\n status, output2 = commands.getstatusoutput(\"%s -Bse \\\"\/*!50000 SELECT IFNULL(SUM(DATA_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB' *\/\\\"\" % (mysql))\r\n innodb_data=output2\r\n if innodb_indexes :\r\n innodb_buffer_pool_pages_data=self.mysql_status(\"\\'Innodb_buffer_pool_pages_data\\'\")\r\n innodb_buffer_pool_pages_misc=self.mysql_status(\"\\'Innodb_buffer_pool_pages_misc\\'\")\r\n innodb_buffer_pool_pages_free=self.mysql_status(\"\\'Innodb_buffer_pool_pages_free\\'\")\r\n innodb_buffer_pool_pages_total=self.mysql_status(\"\\'Innodb_buffer_pool_pages_total\\'\")\r\n innodb_buffer_pool_read_ahead_seq=self.mysql_status(\"\\'Innodb_buffer_pool_read_ahead_seq\\'\")\r\n innodb_buffer_pool_read_requests=self.mysql_status(\"\\'Innodb_buffer_pool_read_requests\\'\")\r\n innodb_os_log_pending_fsyncs=self.mysql_status(\"\\'Innodb_os_log_pending_fsyncs\\'\")\r\n innodb_os_log_pending_writes=self.mysql_status(\"\\'Innodb_os_log_pending_writes\\'\")\r\n innodb_log_waits=self.mysql_status(\"\\'Innodb_log_waits\\'\")\r\n innodb_row_lock_time=self.mysql_status(\"\\'Innodb_row_lock_time\\'\")\r\n innodb_row_lock_time=self.mysql_status(\"\\'Innodb_row_lock_waits\\'\")\r\n innodb_indexesHR=self.forhumansize(innodb_indexes)\r\n self.color.world_fun(\"Current InnoDB index space = %s \\n\"% innodb_indexesHR) \r\n innodb_dataHR=self.forhumansize(innodb_data)\r\n self.color.world_fun(\"Current InnoDB data space = %s \\n\"% innodb_dataHR) \r\n percent_innodb_buffer_pool_free=float(innodb_buffer_pool_pages_free)*100\/float(innodb_buffer_pool_pages_total)\r\n self.color.world_fun(\"Current InnoDB buffer pool free = %4.2f%%\\n\"% percent_innodb_buffer_pool_free) \r\n else:\r\n self.color.world_fun(\"Cannot parse InnoDB stats prior to 5.0.x\\n\",\"red\")\r\n innodb_buffer_pool_sizeHR=self.forhumansize(innodb_buffer_pool_size)\r\n self.color.world_fun(\"Current innodb_buffer_pool_size = %s \\n\"% innodb_buffer_pool_sizeHR) \r\n self.color.world_fun(\"Depending on how much space your innodb indexes take up it may be safe\\n\") \r\n self.color.world_fun(\"to increase this value to up to 2 \/ 3 of total system memory\\n\") \r\n else:\r\n self.color.world_fun(\"No InnoDB Support Enabled!\\n\",\"boldred\")\r\n\r\n if debug:\r\n print (\"have_innodb : %s\" % have_innodb )\r\n print (\"innodb_buffer_pool_size : %s\" % innodb_buffer_pool_size )\r\n print (\"innodb_additional_mem_pool_size : %s\" % innodb_additional_mem_pool_size )\r\n print (\"innodb_fast_shutdown : %s\" % innodb_fast_shutdown )\r\n print (\"innodb_flush_log_at_trx_commit : %s\" % innodb_flush_log_at_trx_commit)\r\n print (\"innodb_locks_unsafe_for_binlog : %s\" % innodb_locks_unsafe_for_binlog)\r\n print (\"innodb_log_buffer_size : %s\" % innodb_log_buffer_size )\r\n print (\"innodb_log_file_size : %s\" % innodb_log_file_size )\r\n print (\"innodb_log_files_in_group : %s\" % innodb_log_files_in_group)\r\n print (\"innodb_thread_concurrency : %s\" % innodb_thread_concurrency)\r\n print (\"innodb_buffer_pool_pages_data : %s\" % innodb_buffer_pool_pages_data )\r\n print (\"innodb_buffer_pool_pages_misc : %s\" % innodb_buffer_pool_pages_misc )\r\n print (\"innodb_buffer_pool_pages_free : %s\" % innodb_buffer_pool_pages_free)\r\n print (\"innodb_buffer_pool_pages_total : %s\" % innodb_buffer_pool_pages_total)\r\n print (\"innodb_buffer_pool_read_ahead_seq : %s\" % innodb_buffer_pool_read_ahead_seq )\r\n print (\"innodb_buffer_pool_read_requests : %s\" % innodb_buffer_pool_read_requests)\r\n print (\"innodb_os_log_pending_fsyncs : %s\" % innodb_os_log_pending_fsyncs )\r\n print (\"innodb_os_log_pending_writes : %s\" % innodb_os_log_pending_writes)\r\n print (\"innodb_log_waits : %s\" % innodb_log_waits )\r\n print (\"innodb_row_lock_time : %s\" % innodb_row_lock_time)\r\n print (\"innodb_row_lock_time : %s\" % innodb_row_lock_time)\r\n '''\r\n MEMORY USAGE\r\n '''\r\n self.color.text_fun(\"-MySQL MEMORY USAGE:\",\"boldblue\")\r\n read_buffer_size=self.mysql_variable(\"\\'read_buffer_size\\'\")\r\n read_rnd_buffer_size=self.mysql_variable(\"\\'read_rnd_buffer_size\\'\")\r\n sort_buffer_size=self.mysql_variable(\"\\'sort_buffer_size\\'\")\r\n thread_stack=self.mysql_variable(\"\\'thread_stack\\'\")\r\n max_connections=self.mysql_variable(\"\\'max_connections\\'\")\r\n join_buffer_size=self.mysql_variable(\"\\'join_buffer_size\\'\")\r\n tmp_table_size=self.mysql_variable(\"\\'tmp_table_size\\'\")\r\n max_heap_table_size=self.mysql_variable(\"\\'max_heap_table_size\\'\")\r\n log_bin=self.mysql_variable(\"\\'log_bin\\'\")\r\n max_used_connections=self.mysql_variable(\"\\'Max_used_connections\\'\")\r\n if log_bin == 'ON':\r\n binlog_cache_size=self.mysql_variable(\"\\'binlog_cache_size\\'\")\r\n\r\n else:\r\n binlog_cache_size=0\r\n if int(max_heap_table_size) <= int(tmp_table_size):\r\n effective_tmp_table_size=max_heap_table_size\r\n else:\r\n effective_tmp_table_size=tmp_table_size\r\n \r\n per_thread_buffers=(int(read_buffer_size)+int(read_rnd_buffer_size)+int(sort_buffer_size)+int(thread_stack)+int(join_buffer_size)+int(binlog_cache_size))*int(max_connections)\r\n if not max_used_connections:\r\n max_used_connections=max_connections\r\n per_thread_max_buffers=(int(read_buffer_size)+int(read_rnd_buffer_size)+int(sort_buffer_size)+int(thread_stack)+int(join_buffer_size)+int(binlog_cache_size)) * int(max_used_connections)\r\n innodb_buffer_pool_size=self.mysql_variable(\"\\'innodb_buffer_pool_size\\'\")\r\n if not innodb_buffer_pool_size:\r\n innodb_buffer_pool_size=0\r\n innodb_additional_mem_pool_size=self.mysql_variable(\"\\'innodb_additional_mem_pool_size\\'\")\r\n if not innodb_additional_mem_pool_size:\r\n innodb_additional_mem_pool_size=0\r\n innodb_log_buffer_size=self.mysql_variable(\"\\'innodb_log_buffer_size\\'\")\r\n if not innodb_log_buffer_size:\r\n innodb_log_buffer_size=0\r\n key_buffer_size=self.mysql_variable(\"\\'key_buffer_size\\'\")\r\n query_cache_size=self.mysql_variable(\"\\'query_cache_size\\'\")\r\n if not query_cache_size:\r\n query_cache_size=0\r\n global_buffers=int(innodb_buffer_pool_size)+int(innodb_additional_mem_pool_size)+int(innodb_log_buffer_size)+int(key_buffer_size)+int(query_cache_size)\r\n max_memory=int(global_buffers)+int(per_thread_max_buffers)\r\n total_memory=int(global_buffers)+int(per_thread_buffers)\r\n physical_memory=os.popen(\"awk '\/^MemTotal\/ { printf(\\\"%.0f\\\", $2*1024 ) }' < \/proc\/meminfo\").read()\r\n pct_of_sys_mem=float(total_memory)*100\/float(physical_memory)\r\n if int(pct_of_sys_mem) > 90 :\r\n perror=1\r\n txt_color=\"boldred\"\r\n else:\r\n txt_color=\"black\"\r\n perror=0\r\n #txt_color=\"boldred\"\r\n max_memoryHR=self.forhumansize(max_memory)\r\n self.color.world_fun((\"Max Memory Ever Allocated = %s \\n\"% max_memoryHR),txt_color)\r\n per_thread_buffersHR=self.forhumansize(per_thread_buffers)\r\n self.color.world_fun((\"Configured Max Per-thread Buffers = %s \\n\"% per_thread_buffersHR),txt_color)\r\n global_buffersHR=self.forhumansize(global_buffers)\r\n self.color.world_fun((\"Configured Max Global Buffers = %s \\n\"% global_buffersHR),txt_color)\r\n total_memoryHR=self.forhumansize(total_memory)\r\n self.color.world_fun((\"Configured Max Memory Limit = %s \\n\"% total_memoryHR),txt_color)\r\n effective_tmp_table_sizeHR=self.forhumansize(effective_tmp_table_size)\r\n self.color.world_fun((\"Plus %s per temporary table created \\n\"% effective_tmp_table_sizeHR),txt_color)\r\n physical_memoryHR=self.forhumansize(physical_memory)\r\n self.color.world_fun((\"Physical Memory = %s \\n\"% physical_memoryHR),txt_color)\r\n if int(perror) == 1:\r\n self.color.world_fun(\"Max memory limit exceeds 90% of physical memory\\n\",txt_color)\r\n else:\r\n self.color.world_fun(\"Max memory limit seem to be within acceptable norms\\n\",\"green\")\r\n\r\n if debug:\r\n print (\"read_buffer_size : %s\" % read_buffer_size )\r\n print (\"read_rnd_buffer_size : %s\" % read_rnd_buffer_size )\r\n print (\"sort_buffer_size : %s\" % sort_buffer_size )\r\n print (\"thread_stack : %s\" % thread_stack )\r\n print (\"max_connections : %s\" % max_connections )\r\n print (\"join_buffer_size : %s\" % join_buffer_size )\r\n print (\"tmp_table_size : %s\" % tmp_table_size )\r\n print (\"max_heap_table_size : %s\" % max_heap_table_size)\r\n print (\"log_bin : %s\" % log_bin )\r\n print (\"max_used_connections : %s\" % max_used_connections )\r\n print (\"innodb_buffer_pool_size : %s\" % innodb_buffer_pool_size )\r\n print (\"innodb_additional_mem_pool_size : %s\" % innodb_additional_mem_pool_size )\r\n print (\"innodb_log_buffer_size : %s\" % innodb_log_buffer_size )\r\n print (\"key_buffer_size : %s\" % key_buffer_size )\r\n print (\"query_cache_size : %s\" % query_cache_size )\r\n\r\n '''\r\n '''\r\n\r\n\r\n \r\nif __name__ == \"__main__\":\r\n app=setColor()\r\n app.text_banner()\r\n try:\r\n mysqlck=checkMysql()\r\n socket=mysqlck.check_mysql_socket()\r\n if not socket :\r\n app.world_fun(\"The mysqld process is not running or it is installed in a custom location.\\n\",\"boldred\")\r\n sys.exit()\r\n except Exception,e:\r\n app.text_fun(e)\r\n try:\r\n mysqlck.mysql_info()\r\n #pass\r\n except Exception,e:\r\n app.text_fun(e)\r\n sys.exit(0)<\/pre>\n","protected":false},"excerpt":{"rendered":"\u4e4b\u524d\u6839\u636e\u4e00\u4e2a\u811a\u672c\u6587\u4ef6\uff0c\u5199\u4e86\u4e00\u4e2apython\u7684\u68c0\u6d4b\u811a…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5],"tags":[57],"_links":{"self":[{"href":"http:\/\/www.selinuxplus.com\/index.php?rest_route=\/wp\/v2\/posts\/348"}],"collection":[{"href":"http:\/\/www.selinuxplus.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.selinuxplus.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.selinuxplus.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.selinuxplus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=348"}],"version-history":[{"count":1,"href":"http:\/\/www.selinuxplus.com\/index.php?rest_route=\/wp\/v2\/posts\/348\/revisions"}],"predecessor-version":[{"id":349,"href":"http:\/\/www.selinuxplus.com\/index.php?rest_route=\/wp\/v2\/posts\/348\/revisions\/349"}],"wp:attachment":[{"href":"http:\/\/www.selinuxplus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=348"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.selinuxplus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=348"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.selinuxplus.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=348"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}