[mythtv-users] MySQL Tuning Script

Dan Smith mrplowdan at gmail.com
Thu Sep 16 19:58:55 UTC 2010


On Thu, Sep 16, 2010 at 7:25 AM, James Crow <crow.jamesm at gmail.com> wrote:
> 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 "***********************************************************"
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>

I'm getting a bunch of errors because it looks like your scripted has
been wrapped by your email editor or mine, I'd like to try it out so
could you upload it to a pastebin type site?


More information about the mythtv-users mailing list