Thread: running maintenance tasks on DB
Hello, Approaching completion of an online auction system based on Postgres and mod_perl/Apache I yet have to devise a good way of running certain maintenance tasks on the DB like closing auctions, notifying winners, transferring old records to archive tables, etc. What is the usual way of programming such tasks? Write a backend function (in pl/sql or C) and call it from a cron job? From a mod_perl handler? Thanks in advance for any insight, cheers, -- Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org
On Sat, Sep 23, 2000 at 05:26:47PM +0200, Louis-David Mitterrand wrote: > Approaching completion of an online auction system based on Postgres and > mod_perl/Apache I yet have to devise a good way of running certain > maintenance tasks on the DB like closing auctions, notifying winners, > transferring old records to archive tables, etc. > > What is the usual way of programming such tasks? Write a backend > function (in pl/sql or C) and call it from a cron job? From a mod_perl > handler? I don't know if this is the best way, but I usually just write a Perl script and run it through cron. You just have to make sure that you maintain data integrity while doing maintainence - i.e. there is no point where the data is invalid. This usually means putting stuff into transactions, but that's no big deal. I don't believe you can get backend functions to run at certain times, without using a mechanism like cron to connect to the DB and run them. A mod_perl handler is a totally different matter. That's for doing stuff with HTTP requests - isn't this unrelated? I would think that you'd have mod_perl handlers displaying your content and manipulating the DB, and have some scripts set to run every X minutes/hours which do maintainence. I use Perl, but you can obviously use any language you like, provided it has a Postgres interface. HTH, Neil -- Neil Conway <neilconway@home.com> Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc Encrypted mail welcomed In the beginning the Universe was created. This has made a lot of people very angry and been widely regarded as a bad move. -- Douglas Adams
Attachment
On Sat, Sep 23, 2000 at 12:45:02PM -0400, Neil Conway wrote: > On Sat, Sep 23, 2000 at 05:26:47PM +0200, Louis-David Mitterrand wrote: > > Approaching completion of an online auction system based on Postgres and > > mod_perl/Apache I yet have to devise a good way of running certain > > maintenance tasks on the DB like closing auctions, notifying winners, > > transferring old records to archive tables, etc. > > > > What is the usual way of programming such tasks? Write a backend > > function (in pl/sql or C) and call it from a cron job? From a mod_perl > > handler? > > I don't know if this is the best way, but I usually just write a Perl > script and run it through cron. You just have to make sure that you > maintain data integrity while doing maintainence - i.e. there is no > point where the data is invalid. This usually means putting stuff > into transactions, but that's no big deal. > > I don't believe you can get backend functions to run at certain > times, without using a mechanism like cron to connect to the DB and > run them. It would be really great to have that functionality in the backend, > A mod_perl handler is a totally different matter. That's for doing > stuff with HTTP requests - isn't this unrelated? Indeed I wasn't making much sense talking of mod_perl handlers to perform DB maintenance :) The reason I came to thinking about them is because cron jobs appear as less elegant and integrated with the whole application, they appear as external crutch filling a missing DB functionality (running scheduled tasks). Ideally I'd like the auction application to only rely on its two main components: Postgres and mod_perl/Apache. The mod_perl idea was to install the maintenance script as a ChildInitHandler which is not directly related to content generation. Is that totally harebrained? > I would think that you'd have mod_perl handlers displaying your > content and manipulating the DB, and have some scripts set to run > every X minutes/hours which do maintainence. > > I use Perl, but you can obviously use any language you like, provided > it has a Postgres interface. Thanks for sharing your experience, cheers, -- Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org "You're cute when you're stupid"
On Sat, Sep 23, 2000 at 07:34:31PM +0200, Louis-David Mitterrand wrote: > On Sat, Sep 23, 2000 at 12:45:02PM -0400, Neil Conway wrote: > > I don't believe you can get backend functions to run at certain > > times, without using a mechanism like cron to connect to the DB and > > run them. > > It would be really great to have that functionality in the backend, Yes, this might come in handy. > > A mod_perl handler is a totally different matter. That's for doing > > stuff with HTTP requests - isn't this unrelated? > > Indeed I wasn't making much sense talking of mod_perl handlers to > perform DB maintenance :) The reason I came to thinking about them is > because cron jobs appear as less elegant and integrated with the whole > application, they appear as external crutch filling a missing DB > functionality (running scheduled tasks). > > Ideally I'd like the auction application to only rely on its two main > components: Postgres and mod_perl/Apache. The mod_perl idea was to > install the maintenance script as a ChildInitHandler which is not > directly related to content generation. Is that totally harebrained? Well, (AFAIK) the ChildInitHandler is called every time Apache spawns a new process. This could be *really* bad - for instance, someone starts up Apache w/ your app (lets say @ ebay.com :D). Because your action software is so good, they get ~50 hits per second, so Apache starts spawning children like crazy. Now you have hundreds of Apache children waiting for you to finish DB maintainence. And the DB is getting hammered doing this DB maintainence, and isn't processing any real requests. And you don't really have any control over how often Apache spawns new children. It could theoretically go for days without spawning any new children (for example, if it's serving MaxClients requests all the time). I'd recommend sticking with cron :-) Cron is on every UNIX I've ever used, so it's unlikely an admin will need to install anything beyond Postgres + mod_perl/Apache. If you build the maintainence scripts out of the same components as the rest of the app (Perl, DBI, DBD::Pg, etc), it shouldn't be too bad. HTH, Neil -- Neil Conway <neilconway@home.com> Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc Encrypted mail welcomed Don't worry about people stealing your ideas. If your ideas are any good, you'll have to ram them down people's throats. -- Howard Aiken
Attachment
Louis-David Mitterrand <cunctator@apartia.ch> writes:
>
> Indeed I wasn't making much sense talking of mod_perl handlers to
> perform DB maintenance :) The reason I came to thinking about them is
> because cron jobs appear as less elegant and integrated with the whole
> application, they appear as external crutch filling a missing DB
> functionality (running scheduled tasks).
>
I too try too avoid cron with my customers. I tend too look at running
scheduled tasks as an application issue and I usually have a component of
the application performing these tasks. That's easy too implement using
Java, but I'm not sure how to implement in mod_perl.
regards,
Gunnar