LinHES Forums
http://forums.linhes.org/

A DB performace utility - optimize_db.sh
http://forums.linhes.org/viewtopic.php?f=3&t=15694
Page 1 of 2

Author:  tjc [ Sun Jun 24, 2007 3:19 pm ]
Post subject:  A DB performace utility - optimize_db.sh

This script was inspired by a conversation with Marc Aronson about DB optimization to reduce buffer overflows/underflows caused by DB latency...

A more sophisticated approach would use the higher level mysql commands like "REPAIR TABLE", "ANALYZE TABLE", and "OPTIMIZE TABLE". On the other hand this is fairly simple and robust and should even work on DBs that have suffered some damage where having the mysqld daemon running may present problems. In an ideal world this might also stop the frontend to keep it from whining about the DB server being inaccessible or causing other mischief.
Code:
#!/bin/bash

fatal () {
    echo "Error! $*"
    exit 1
}

. /usr/local/bin/backupcommon ||
    fatal "Can not find common settings!"

if [ "$1" = '-b' ] ; then # Optionally, make a backup before we start...
    # I'm not sure how useful this is, since both recover and analyze
    # are pretty safe operations...
    /usr/local/bin/mythbackup ||
        fatal "Backup failed! Aborting!"
fi
# The database needs to be idle when we do this...
# Having it auto restarted would also be "bad". ;-)
/etc/init.d/mythtv-backend stop
/etc/init.d/mysql stop

cd $DATABASE_DIR || fatal "Could not cd to $DATABASE_DIR"

for tbl in *.MYI ; do
    # If the fast way fails fall back on the old slow way.
    myisamchk --recover $tbl || {
        echo "Fast recovery of $tbl failed, attempting slower safe recovery..."
        myisamchk --safe-recover $tbl ||
            fatal "Even safe recovery of $tbl failed! Aborting!"
    }
    myisamchk --analyze $tbl
done

# Resatart the servers in the right order...
/etc/init.d/mysql restart
/etc/init.d/mythtv-backend restart

Author:  marc.aronson [ Sun Jun 24, 2007 11:12 pm ]
Post subject: 

TJC, very nice script -- thanks for putting this together!

For those who are still experiencing IOBOUND errors and "Application not reading fast enough" problems, here are three more things to consider:

1. Reducing the number of days of program guide data that I kept in my database from the default of 14 to 9 also reduced the incidence of “IOBOUND� errors for me. Since you already have 14 days of program guide data in your database, it will take 5 days before you see the full benefit of this change. On R5D1: utilities/setup->setup->general->�mythfilldatabase� (screen # 7): Modify the field “mythfilldatabase arguments� to read “--quiet --max-days 9�, without the quotes.

2. If you have any unused video sources hanging around, delete them using the myth-setup utility. This reduces the volume of data downloaded from zap2it into the database. Only do this if the video source is not needed and is not connected to any tuner cards:

3. There are a lot of “watchdog� scripts scattered around various sites that are designed to detect backend failures and execute an automatic restart. Some of these scripts include a call to the status port (6544) to see if the backend is still running. This can be a problem as a call to the status port will issue ~500 database queries. A better approach that works under R5D1 is to invoke the “settings� screen, which only issues ~10 database queries:
Code:
•   Look in your script for a command that reads something like
      â€œlynx -dump http://localhost:6544â€?
•   Replace that command with
      â€œlynx -dump http://localhost/mythweb/settingsâ€?
•   The script may grep an output file for a specific string such as “Scheduleâ€?.
   If your script does this, replace the search string with the following:
         â€œThis is the index pageâ€?


Items 1 & 2 and the data base optimization reduces the I/O load every time the scheduler runs, which is quite frequent.

Marc

Author:  mihanson [ Mon Jun 25, 2007 2:03 pm ]
Post subject:  Re: A DB performace utility - optimize_db.sh

tjc wrote:
Code:
# Resatart the servers in the right order...
/etc/init.d/mysql restart
/etc/init.d/mythtv-backend restart


Should these last 2 lines be:
Code:
/etc/init.d/mysql start
/etc/init.d/mythtv-backend start
:?:

I suppose it does not really matter, but since they were stopped earlier in the script, shouldn't they still be stopped and then require only a "start"?

FWIW, I have a perl script called optimize_mythdb.pl that "optimizes" the db that I got from installing a SVN version of mythtv a while back.
Code:
#!/usr/bin/perl -w
#
# $Date: 2005-08-20 13:21:40 -0700 (Sat, 20 Aug 2005) $
# $Revision: 7095 $
# $Author: xris $
#
#  epair_optimize.pl:
#
#   Connects to the mythtv database and repairs/optimizes the tables that it
#   finds.  Suggested use is to cron it to run once per day.
#

# Load DBI
    use DBI;

# Some variables we'll use here
    my ($db_host, $db_user, $db_name, $db_pass);

# Read the mysql.txt file in use by MythTV.
# could be in a couple places, so try the usual suspects
    my $found = 0;
    my @mysql = ('/usr/local/share/mythtv/mysql.txt',
                 '/usr/share/mythtv/mysql.txt',
                 '/etc/mythtv/mysql.txt',
                 '/usr/local/etc/mythtv/mysql.txt',
                 "$ENV{HOME}/.mythtv/mysql.txt",
                 'mysql.txt'
                );
    foreach my $file (@mysql) {
        next unless (-e $file);
        $found = 1;
        open(CONF, $file) or die "Unable to open $file:  $!\n\n";
        while (my $line = <CONF>) {
        # Cleanup
            next if ($line =~ /^\s*#/);
            $line =~ s/^str //;
            chomp($line);
        # Split off the var=val pairs
            my ($var, $val) = split(/\=/, $line, 2);
            next unless ($var && $var =~ /\w/);
            if ($var eq 'DBHostName') {
                $db_host = $val;
            }
            elsif ($var eq 'DBUserName') {
                $db_user = $val;
            }
            elsif ($var eq 'DBName') {
                $db_name = $val;
            }
            elsif ($var eq 'DBPassword') {
                $db_pass = $val;
            }
        }
        close CONF;
    }
    die "Unable to locate mysql.txt:  $!\n\n" unless ($found && $db_host);

# Connect to the database
    $dbh = DBI->connect("dbi:mysql:database=$db_name:host=$db_host", $db_user, $db_pass)
        or die "Cannot connect to database: $!\n\n";

# Repair and optimize each table
    foreach $table ($dbh->tables) {
        unless ($dbh->do("REPAIR TABLE $table")) {
            print "Skipped:  $table\n";
            next;
        };
        if ($dbh->do("OPTIMIZE TABLE $table")) {
            print "Repaired/Optimized: $table\n";
        }
    }

# Close the database connection
    $dbh->disconnect;

Author:  tjc [ Mon Jun 25, 2007 5:53 pm ]
Post subject: 

Re: start vs. restart - This was intentional "sanity preserving behavior". On a multiuser system it's generally not a good idea to assume that you know the state of given daemon, or that whoever wrote the start script provided enough protection against attempts to start twice. Using restart is cheap insurance.

Re: perl script. - Those are essentially the higher level commands I was talking about. On the other hand the way it hunts for the mysql.txt is a bit risky (a system might have multiple files and there's no guarantee that the first one it finds is the right one).

Author:  mihanson [ Mon Jun 25, 2007 6:30 pm ]
Post subject: 

Code:
mythtv@mythbox:~$ cat .mythtv/mysql.txt
DBHostName=192.168.11.4
DBUserName=mythtv
DBPassword=mythtv
DBName=mythconverg
DBType=QMYSQL3

# Set the following if you want to use something other than the
# machine's real hostname for identifying settings in the database.
# This is useful if your hostname changes often, as otherwise
# you'll need to reconfigure mythtv (or futz with the DB) every time.
# TWO HOSTS MUST NOT USE THE SAME VALUE
#
LocalHostName=mythbox

# If you want your frontend to be able to wake your MySQL server
# using WakeOnLan, have a look at the following settings:
#
# Set the time the frontend waits (in seconds) between reconnect tries.
# This should be the rough time your MySQL server needs for startup
#WOLsqlReconnectWaitTime=0
#
#
# This is the amount of retries to wake the MySQL server until the frontend
# gives up
#WOLsqlConnectRetry=5
#
#
# This is the command executed to wake your MySQL server.
#WOLsqlCommand=echo 'WOLsqlServerCommand not set'


Could that DBType=QMYSQL3 line be the cause of some peoples problems?

Code:
mythtv@mythbox:~$ mysql -V
mysql  Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (i486) using readline 5.2
It seems R5F1 uses mysql v. 5.0.32

Author:  Too Many Secrets [ Wed Nov 07, 2007 12:04 am ]
Post subject: 

I've been using the above script on an 'as needed' basis. But today in reading thru the myth wiki I see that they recommend running the perl script in a daily cron.

Would the above script be useful to run in a daily cron? and if so, will it not run if recording something?

Author:  tjc [ Wed Nov 07, 2007 7:33 pm ]
Post subject: 

A quick scan of the script will show that it has no protections against conflicts with scheduled events. I think this is probably better delegated to a utility wrapper script which takes an estimate of how big a time window is needed and the command to run, and then figures out if it's safe to start. I don't think the Perl script has any checking either, but it may "work" while the DB is running. (Much like mythfilldatabase does, potentially creating enough latency to cause buffer overruns.)

Author:  arriflex [ Mon Nov 12, 2007 3:13 pm ]
Post subject: 

When I run this script, by backend does not restart. What logs might lead me to the reasons why? Likely related is that pressing alt-3 does not restart the backend either...

arri

Author:  tjc [ Mon Nov 12, 2007 6:58 pm ]
Post subject: 

Do you have a newline at the end of the script?

(i.e. - Does it look like this?)
Code:
root@black2:~# hd scripts/backup/optimize_db.sh | tail
000003a0  6b 20 2d 2d 61 6e 61 6c  79 7a 65 20 24 74 62 6c  |k --analyze $tbl|
000003b0  0a 64 6f 6e 65 0a 0a 23  20 52 65 73 61 74 61 72  |.done..# Resatar|
000003c0  74 20 74 68 65 20 73 65  72 76 65 72 73 20 69 6e  |t the servers in|
000003d0  20 74 68 65 20 72 69 67  68 74 20 6f 72 64 65 72  | the right order|
000003e0  2e 2e 2e 0a 2f 65 74 63  2f 69 6e 69 74 2e 64 2f  |..../etc/init.d/|
000003f0  6d 79 73 71 6c 20 72 65  73 74 61 72 74 0a 2f 65  |mysql restart./e|
00000400  74 63 2f 69 6e 69 74 2e  64 2f 6d 79 74 68 74 76  |tc/init.d/mythtv|
00000410  2d 62 61 63 6b 65 6e 64  20 72 65 73 74 61 72 74  |-backend restart|
00000420  0a                                                |.|
00000421


Author:  arriflex [ Tue Nov 13, 2007 11:23 am ]
Post subject: 

I don't think so,

Code:
root@mythtv:/usr/local/bin# hd /usr/local/bin/optimize_db.sh | tail
000003a0  68 6b 20 2d 2d 61 6e 61  6c 79 7a 65 20 24 74 62  |hk --analyze $tb|
000003b0  6c 0a 64 6f 6e 65 0a 0a  23 20 52 65 73 61 74 61  |l.done..# Resata|
000003c0  72 74 20 74 68 65 20 73  65 72 76 65 72 73 20 69  |rt the servers i|
000003d0  6e 20 74 68 65 20 72 69  67 68 74 20 6f 72 64 65  |n the right orde|
000003e0  72 2e 2e 2e 0a 2f 65 74  63 2f 69 6e 69 74 2e 64  |r..../etc/init.d|
000003f0  2f 6d 79 73 71 6c 20 72  65 73 74 61 72 74 0a 2f  |/mysql restart./|
00000400  65 74 63 2f 69 6e 69 74  2e 64 2f 6d 79 74 68 74  |etc/init.d/mytht|
00000410  76 2d 62 61 63 6b 65 6e  64 20 72 65 73 74 61 72  |v-backend restar|
00000420  74 0a                                             |t.|
00000422
root@mythtv:/usr/local/bin#


However, I "upgraded" my F27 installation last night and the script seems to be working again.

Separately, alt 2 kills the backend, but I can't get it running again with alt 3. No biggee, running the script brings it back and cleans things up in the process!

arri

Author:  tjc [ Wed Nov 14, 2007 9:24 pm ]
Post subject: 

Oh bother! Turns out there is a bug with restart in /etc/init.d/mythtv-backend I'll send a patch to the appropriate folks. In the meantime you can fix the script by changing that last line from:
Code:
/etc/init.d/mythtv-backend restart

to:
Code:
/etc/init.d/mythtv-backend stop
/etc/init.d/mythtv-backend start

Author:  arriflex [ Thu Nov 15, 2007 1:57 pm ]
Post subject: 

Fun! So out of curiosity should I look into changing my keybinding for alt-3 to running a short script that sends stop and start commands rather than restart as I believe it is currently configured?

Author:  tjc [ Thu Nov 15, 2007 6:56 pm ]
Post subject: 

Probably better to patch the real source of the problem. This is what I did:
Code:
root@black2:~# diff -c /etc/init.d/mythtv-backend~ /etc/init.d/mythtv-backend
*** /etc/init.d/mythtv-backend~ Wed Nov 14 22:44:52 2007
--- /etc/init.d/mythtv-backend  Wed Nov 14 22:45:28 2007
***************
*** 56,62 ****
    restart|force-reload)
        echo -n "Restarting $DESC: $NAME"
        start-stop-daemon --stop --oknodo --pidfile $RUNDIR/$NAME.pid \
!                 --chuid $USER --exec $DAEMON -- $ARGS
        echo "."
        sleep 3
        start-stop-daemon --start --pidfile $RUNDIR/$NAME.pid \
--- 56,62 ----
    restart|force-reload)
        echo -n "Restarting $DESC: $NAME"
        start-stop-daemon --stop --oknodo --pidfile $RUNDIR/$NAME.pid \
!                 --chuid $USER --exec $DAEMON -- $ARGS || : ignore any errors
        echo "."
        sleep 3
        start-stop-daemon --start --pidfile $RUNDIR/$NAME.pid \

I held off posting it before because I was hoping Human would add that or something similar to the patcher first.

Author:  arriflex [ Thu Nov 15, 2007 11:28 pm ]
Post subject: 

Quote:
root@black2:~# diff -c /etc/init.d/mythtv-backend~ /etc/init.d/mythtv-backend


Sorry about my yet undeveloped linux experience, I understand diff; however I'm guessing the tilde is either something tricky or your way of denoting a backup version of the file...

Thanks for helping me through.

arri
[/quote]

Author:  tjc [ Fri Nov 16, 2007 11:05 pm ]
Post subject: 

Yes, '~' is the character emacs and emacs like editors (in this case jed) use for the previous backup version of a file when you edit it. With a diff that simple it's an easy manual patch. For more complex changes you can feed a context diff like that to the patch utility and have it automatically reproduce the change.

Argh... Apparently I posted the wrong diff. I've corrected it. Some days nothing goes right... :roll:

Page 1 of 2 All times are UTC - 6 hours
Powered by phpBB® Forum Software © phpBB Group
http://www.phpbb.com/