From: Paul Halliday
Date: May 12 2005 11:37am
Subject: Solution to slow queries (Resolved, kinda)
List-Archive: http://lists.mysql.com/mysql/184014
Message-Id: <2dab70a305051204371dd3ed9e@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,

First of all, thanks to everyone that provided pointers on this matter.
The route I chose to take was to make 2 tables. One is for cumulative
network stats; this table can be used for the weekly,monthly,yearly
reports. I also created a table for daily stats which will be dropped
at midnight each day.

So I whipped up a simple shell script that looks like this:

mysql -B --user=3Dflow --password=3Dhi flowdb -e "DROP TABLE traffic"
mysql -B --user=3Dflow --password=3Dhi flowdb < /home/flow/Code/create_flow=
db.sql

and a crontab entry that looks like:

0       0       *       *       *       /home/flow/Code/db_rollover.sh
> /dev/null 2>&1

the report entries look like this:

*/5     *       *       *       *     =20
/home/flow/Reports/incident_report.tcl > /dev/null 2>&1

*/20    *       *       *       *     =20
/home/flow/Reports/traffic_report.tcl > /dev/null 2>&1

Now looking at crons log from last night I see:

May 12 00:00:00 watcher cron[84039]: (flow) CMD
(/home/flow/Code/db_rollover.sh > /dev/null 2>&1)
May 12 00:00:00 watcher cron[84040]: (flow) CMD
(/home/flow/Reports/traffic_report.tcl > /dev/null 2>&1)
May 12 00:00:00 watcher cron[84041]: (flow) CMD
(/home/flow/Reports/incident_report.tcl > /dev/null 2>&1)

So the script did indeed run, yet it did not drop the table. The
script works fine from the command line so I guess because the other
programs were running too it could not drop the table? Those scripts
are just doing selects, no updates, but there is the possibility that
the program which populates the db was running at the same time too.

Is there a way to force the table drop? (Without adding checks to the
shell script)


Thanks.

_________________
Paul Halliday
http://dp.penix.org

"Diplomacy is the art of saying "Nice doggie!" till you can find a rock."