View unanswered posts    View active topics

All times are UTC - 6 hours





Post new topic Reply to topic  [ 29 posts ] 
Go to page 1, 2  Next

Print view Previous topic   Next topic  
Author Message
Search for:
PostPosted: Sun Jun 24, 2007 3:19 pm 
Offline
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location: Arlington, MA
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


Top
 Profile  
 
 Post subject:
PostPosted: Sun Jun 24, 2007 11:12 pm 
Offline
Joined: Tue Jan 18, 2005 2:07 am
Posts: 1532
Location: California
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


Top
 Profile  
 
PostPosted: Mon Jun 25, 2007 2:03 pm 
Offline
Joined: Sun Sep 25, 2005 3:50 pm
Posts: 1013
Location: Los Angeles
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;

_________________
Mike
My Hardware Profile


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jun 25, 2007 5:53 pm 
Offline
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location: Arlington, MA
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).


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jun 25, 2007 6:30 pm 
Offline
Joined: Sun Sep 25, 2005 3:50 pm
Posts: 1013
Location: Los Angeles
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

_________________
Mike
My Hardware Profile


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 07, 2007 12:04 am 
Offline
Joined: Fri Oct 20, 2006 12:04 pm
Posts: 905
Location: LA, CA
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?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 07, 2007 7:33 pm 
Offline
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location: Arlington, MA
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.)


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 12, 2007 3:13 pm 
Offline
Joined: Thu Feb 23, 2006 2:41 pm
Posts: 68
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

_________________
Athlon 64 X2 4200+ 2GB Ram
MSI K8N Neo4-F NF4 939
ASUS N EN6200TC256/TD/64M
500gb SATA
1x Plextor PX-M402U
1x HD5000
R5.5 Upgrade from F27


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 12, 2007 6:58 pm 
Offline
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location: Arlington, MA
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



Top
 Profile  
 
 Post subject:
PostPosted: Tue Nov 13, 2007 11:23 am 
Offline
Joined: Thu Feb 23, 2006 2:41 pm
Posts: 68
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

_________________
Athlon 64 X2 4200+ 2GB Ram
MSI K8N Neo4-F NF4 939
ASUS N EN6200TC256/TD/64M
500gb SATA
1x Plextor PX-M402U
1x HD5000
R5.5 Upgrade from F27


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 14, 2007 9:24 pm 
Offline
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location: Arlington, MA
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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 15, 2007 1:57 pm 
Offline
Joined: Thu Feb 23, 2006 2:41 pm
Posts: 68
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?

_________________
Athlon 64 X2 4200+ 2GB Ram
MSI K8N Neo4-F NF4 939
ASUS N EN6200TC256/TD/64M
500gb SATA
1x Plextor PX-M402U
1x HD5000
R5.5 Upgrade from F27


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 15, 2007 6:56 pm 
Offline
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location: Arlington, MA
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.


Last edited by tjc on Fri Nov 16, 2007 11:06 pm, edited 1 time in total.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 15, 2007 11:28 pm 
Offline
Joined: Thu Feb 23, 2006 2:41 pm
Posts: 68
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]

_________________
Athlon 64 X2 4200+ 2GB Ram
MSI K8N Neo4-F NF4 939
ASUS N EN6200TC256/TD/64M
500gb SATA
1x Plextor PX-M402U
1x HD5000
R5.5 Upgrade from F27


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 16, 2007 11:05 pm 
Offline
Joined: Thu Mar 25, 2004 11:00 am
Posts: 9551
Location: Arlington, MA
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:


Top
 Profile  
 

Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 29 posts ] 
Go to page 1, 2  Next



All times are UTC - 6 hours




Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group

Theme Created By ceyhansuyu