[mythtv-users] MySQL Tuning Script

James Crow crow.jamesm at gmail.com
Thu Sep 16 14:25:29 UTC 2010


Hi all,

  While fighting (still fighting that is) my recent MBE troubles I
wanted to make sure my DB was not the cause of the problem. On the
wiki there are several mysql settings described that could be altered.
I did a little reading and found out what each one was. Of the
settings on the wiki the two most important are key_buffer_size and
table_open_cache. I wanted an easy way to quickly check these while my
MBE was misbehaving. I wrote a little bash script to give me the
settings as well as the mysql status. This script could use a lot of
polish, but it gives me the values I want quickly. I may decide to
expand it to cover all the settings mentioned on the wiki, but no
promises. Any ideas for improvement or problems feel free to let me
know and I will try to fix.

Thanks,
James

#!/bin/bash
# Written by James Crow crow.jamesm <at> google email domain name <dot> com
# 2010-09-16
# This script connects to the MythTV mythconverg database to pull
# the two most important tuning parameters. This script only works
# for MyISAM tables at the moment. The only part that needs to be
# configured is the variables at the top of the script
# ver 0.1

# *** Change these to match you environment ***
mysql='/usr/bin/mysql'
mythconverg='mythconverg'
sql_user='mythtv'
sql_pass='mythtv'
sql_host='192.168.1.2'
# *** You should not need to change anything after this line ***

# determine if we are using MyISAM tables
$mysql -h $sql_host -u $sql_user -p$sql_pass $mythconverg -e 'show
create table record' | grep -q MyISAM
myisam=$?

if [ $myisam -ne 0 ]; then
	echo "You do not appear to be using MyISAM tables for the myth database"
	exit;
fi
#echo $myisam

# The most important tuning parameters are the key_buffer_size and
table_open_cache
# find the key_buffer_size and % in use first
key_blocks_unused=`$mysql -h $sql_host -u $sql_user -p$sql_pass -e
'show status like "key_blocks_unused"' | grep "[Kk]ey_blocks_unused" |
sed 's/[^0-9]//g'`
key_cache_block_size=`$mysql -h $sql_host -u $sql_user -p$sql_pass -e
'show variables like "key_cache_block_size"' | grep
key_cache_block_size | sed 's/[^0-9]//g'`
key_buffer_size=`$mysql -h $sql_host -u $sql_user -p$sql_pass -e 'show
variables like "key_buffer_size"' | grep key_buffer_size | sed
's/[^0-9]//g'`

# using a simple formula from the MySQL doc we get the key_buffer_cache in use
key_buffer_cache_in_use=`echo "scale=2; 1-(($key_blocks_unused *
$key_cache_block_size)/$key_buffer_size)" | bc`
# this next line rounds each value to an even integer, but that is the
best way to display the result
key_buffer_use_pct=`echo "scale=0; $key_buffer_cache_in_use*100" | bc`
key_buffer_use_pct=`echo "($key_buffer_use_pct+0.5)/1" | bc`
#echo "scale=0; $key_buffer_cache_in_use*100"
echo ""
echo "**** key_buffer_cache section *****************************"
if [ $key_buffer_use_pct -eq 100 ]; then
	echo "!!!!!! WARNING: You are using 100% of your key_buffer_cache."
	echo "                You should raise the value!"
elif [ $key_buffer_use_pct -gt 80 ]; then
	echo "NOTE: You are using more than 80% of your key_buffer_cache."
	echo "      You may may want to raise the value."
fi
echo "key_buffer_size: $key_buffer_size, $(($key_buffer_size/1024))
kb, $(($key_buffer_size/(1024*1024))) mb"
echo "key_buffer_cache in use: $key_buffer_use_pct%"
echo "***********************************************************"

# the second of two most important variables is the table_open_cache
open_tables=`$mysql -h $sql_host -u $sql_user -p$sql_pass -e 'show
status like "open_tables"' | grep Open_tables | sed 's/[^0-9]//g'`
open_table_cache=`$mysql -h $sql_host -u $sql_user -p$sql_pass -e
'show variables like "table_open_cache"' | grep table_open_cache | sed
's/[^0-9]//g'`
free_tables=$(($open_table_cache-$open_tables))
open_tables_free_pct=`echo "scale=2; $free_tables/$open_table_cache*100" | bc`
open_tables_free_pct=`echo "($open_tables_free_pct+0.5)/1" | bc`
#echo "scale=0; $free_tables/$open_table_cache*100"
echo ""
echo "**** table_open_cache section *****************************"
if [ $free_tables -eq 0 ]; then
	echo "!!!!!! WARNING: You have no free tables."
	echo "                You should raise the value."
elif [ $open_tables_free_pct -lt 10 ]; then
	echo "NOTE: You have less than 10% free in the table_open_cache."
	echo "      You may want to raise the value."
fi
echo "table_open_cache: $free_tables free out of $open_table_cache,
$open_tables used"
echo "table_open_cache used pct: $((100-$open_tables_free_pct))%"
echo "***********************************************************"


More information about the mythtv-users mailing list