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

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

Author:  arriflex [ Sun Nov 18, 2007 1:02 am ]
Post subject: 

Thank you. I don't know where you find the time, but I appreciate it.

arri

Author:  acronce [ Tue May 27, 2008 8:09 pm ]
Post subject: 

I use the babysitter script (from "thornsoft") in order to deal with occasional problems where the backend isn't running. I'd like to use the optimize script in conjunction with the idle script and a cron job, but found that the babysitter relaunches the backend while the optimize is running.

I think that the babysitter script is designed to do nothing if the frontend is not running. So I'm thinking that if I change the optimize script to also stop/start the frontend, it will then keep the babysitter from relaunching the backend during the optimize.

Does this make sense, or is there a better way to make these scripts coexist peacefully?

Author:  Too Many Secrets [ Tue May 27, 2008 10:11 pm ]
Post subject: 

I've been using both the babysitter script and the optimize script without issue. They play well for me. Does the problem only show up when optimize is used via the idle script from cron? Can you verify the idle>optimize problem just from the command line?

Author:  acronce [ Sun Jun 01, 2008 12:46 pm ]
Post subject: 

Too Many Secrets wrote:
I've been using both the babysitter script and the optimize script without issue. They play well for me. Does the problem only show up when optimize is used via the idle script from cron? Can you verify the idle>optimize problem just from the command line?

I haven't automated the optimize script from a cron job. What happened was that I forgot that I had the babysitter script running. So after I ran the optimize script manually, I looked at the babysitter log output and found that the babysitter had relaunched the backend.

I'm not 100% sure that the backend was relaunched during the optimize. But it's clear from the log entry that the babysitter did relaunch the backend around the same time that the optimize was running.

The conclusion I came to was the babysitter and the optimize script aren't playing nicely together. But that might be a misassumption on my part. Maybe instead there's a window of opportunity when the optimize is done, but before it relaunches the backend the babysitter can get there first?

If that's the case, then it's probably harmless. I guess I need to dig a little deeper into the interactions between the two scripts to see if there's really a problem here or not.

Author:  tjc [ Sun Jun 01, 2008 1:51 pm ]
Post subject: 

If the babysitting script doesn't restart the mysql server too, restarting the backend can't do much harm. On the other hand you _really_ don't want the DB active when you're doing low level work on the tables like this. That's the fast track to DB corruption.

Author:  tjc [ Sat Jun 21, 2008 10:03 am ]
Post subject: 

tjc wrote:
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 just noticed this old message while looking at something else and thought an update might be appropriate. Both the optimize_db.sh script and the idle.sh which which does the safety checking mentioned here will be included with R5.5 out of the box.

Author:  Too Many Secrets [ Fri Jan 09, 2009 6:30 pm ]
Post subject: 

I'm using the idle script to call the optimize/backup scripts, but it often doesn't run do to 'potential recordings'. is there an easy way to have it try to run again say in 2 hours? I don't want it to run every 2 hours, just once a day.

currently I'm running
Code:
(/usr/local/bin/idle.sh -s && /usr/local/bin/optimize_db.sh) >/var/log/optimize.log 2>&1
from cron at 4am

Author:  tjc [ Fri Jan 09, 2009 9:21 pm ]
Post subject: 

Two approaches occur to me right off hand.

- Trigger once a day and sleep an hour or two (maybe with some limit) until you find an idle stretch.

- Trigger every couple hours and continue if the system is idle and the interval since the last run is more than 24 hours.

Either one is pretty straight forward to implement with a simple shell script.

Author:  Too Many Secrets [ Sun Jan 11, 2009 8:11 pm ]
Post subject: 

Just to finish this, I found this thread and it led to the below script which I'm using to automate the optimize and backup utilities. I'm sure there's an easier way, but... Hope this helps someone.

Code:
cat /usr/local/bin/op-check.sh
#!/bin/bash
#ver .02
# Run hourly via crontab to run optimize_db.sh once a day.
# 42 * * * *      root    /usr/local/bin/op-check.sh
NOW=`date +"%D"`
LAST=`cat /tmp/.op-check`
if [ "$NOW" != "$LAST" ]; then
        #echo $NOW > /tmp/.op-check
        /usr/local/bin/idle.sh -s && echo $NOW > /tmp/.op-check && /usr/local/bin/optimize_db.sh >/var/log/optimize.log 2>&1
fi

Edited with date fix.

Author:  tjc [ Sun Jan 11, 2009 10:05 pm ]
Post subject: 

You can actually break it after each && with no problems since && implies continuation. I.e.
Code:
aaa && bbb && ccc

and
Code:
aaa &&
bbb &&
ccc

are equivalent.

Author:  sparky.watson [ Fri Feb 06, 2009 7:21 pm ]
Post subject: 

I'm sorry, I know I've seen the answer to this before, but for the life of me I'm not finding it. Should optimize_db.sh be run as root? I'm guessing it should but I sure would hate to choose unwisely.

Author:  tjc [ Fri Feb 06, 2009 11:47 pm ]
Post subject: 

Must (rather than should) be run as root, since you're starting and stopping system services. I think a more modern version of that script actually checks to make sure...

Author:  acronce [ Mon Feb 09, 2009 11:03 am ]
Post subject: 

Thanks "Too Many Secrets" for the above /usr/local/bin/op-check.sh script. Unfortunately I'm finding that it's performing the optimize hourly, instead of once a day.

I took a peek and see this in this in the temp file:

Code:
root@mythtv:~# cat /tmp/.op-check
Mon Feb 9

But when I execute the date command manually I see this:

Code:
root@mythtv:~# date | cut -b 1-10
Mon Feb  9

Note the extra space before the '9'. I think this makes the comparison fail and makes the script think it needs to execute when it really doesn't.

I'm thinking of changing the date command to something simpler, like this:

Code:
NOW=`date +"%j"`

The above will just output the current day of the year. No need to pipe it to any text editing command.

Author:  Too Many Secrets [ Mon Feb 09, 2009 11:30 am ]
Post subject: 

acronce, I hadn't noticed a problem, it must only happen with single digit dates (ie 1-9) but I do see the problem as I have multiple backups in the last few days.

I'm editing the above script to fix the issue. I've tested it and it seems to work on my machine. Let me know if there is any other issues.

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