Thread: Background triggers?
Hi guys A bit of a long first story, hope someone is able to help... We have got a Ubuntu 8.04 server running Postfix, which reads its mailing list subscription files, aliases, virtual, domains, etc from a Postgres database. This used to work fine when we had only a couple of e-mail distribution lists. However, recently a lot of new lists have been added and the system becomes very slow. Turns out there a couple of very complicated views using other views, using concatenated strings etc. A SELECT on this view takes about 2.5 seconds. Tuning the resource allocation brought it down to 1.2 seconds. We have looked at the queries and came to the conclusion that it would not be feasible to bring this down any further. The database gets millions of SELECT queries per day, but only a hand full of UPDATE, DELETE or INSERT queries, which made me think. I created a trigger that fires on UPDATE, DELETE, or INSERT, and selects * from the views and puts these calculated results in simple tables. This way the data is cached, which is very fast of course. This seems to work fine, but there is a problem. The database with subscriptions is managed with a (PHP) web interface. Typically, all actions that are being done with the interface are queries that INSERT, UPDATE or DELETE. This means that this interface will be very slow because each action will cause the trigger to fire - calculating * from views taking 1.2 seconds each. Ideally I would like the trigger to fire and then do the actual work in the background, but I found no way of doing this. So I came to NOTIFY/LISTEN. But because there a several pieces of software talking to the database, this is not ideal either. In the end we created a trigger that inserts NOW() into in a table whenever one of the source tables gets a INSERT, UPDATE or DELETE. If so, it runs commands to refresh the content of the cache tables. The fact that the mail server can sometimes see stale data of less than a minute old is no problem. This seems to work fine, but cron does feel a bit kludgey though... Is there a way to trigger stuff using NOTIFY/LISTEN, but WITHOUT an external client doing the magic? Some kind of internal Postgres function that listens for any changes, and then does the magic itself, without tying up any 'real' clients for the duration of that magic? Thanks! -- Dick Visser
Dick Visser <visser@terena.org> writes: > Ideally I would like the trigger to fire and then do the actual work in > the background, but I found no way of doing this. > So I came to NOTIFY/LISTEN. But because there a several pieces of > software talking to the database, this is not ideal either. Well, the traditional solution is to have a trigger or rule that fires the NOTIFY. You don't need any cooperation from the client apps. > Is there a way to trigger stuff using NOTIFY/LISTEN, but WITHOUT an > external client doing the magic? No. You need a dedicated client process that just sits there waiting for notifies. (Well, it might be able to do some other useful work too, but the simplest way is to dedicate a connection for this.) regards, tom lane
On Wed, Aug 19, 2009 at 8:23 PM, Dick Visser<visser@terena.org> wrote: > > Is there a way to trigger stuff using NOTIFY/LISTEN, but WITHOUT an > external client doing the magic? Some kind of internal Postgres function > that listens for any changes, and then does the magic itself, without > tying up any 'real' clients for the duration of that magic? What do you mean "tying up"? Even if there was no client an internal process would be "tying up" the same resources as if there was one. In my experience it's a lot more flexible having an external process connecting to the database anyways since it means you can use whatever language you like, whatever libraries you like, etc. You can also use other resources, close and open the connection, start and stop transactions, etc. Anything within the database has a lot more restrictions. -- greg http://mit.edu/~gsstark/resume.pdf
Hi Dick, On Wed, Aug 19, 2009 at 09:23:22PM +0200, Dick Visser wrote: > A bit of a long first story, hope someone is able to help... > > We have got a Ubuntu 8.04 server running Postfix, which reads its > mailing list subscription files, aliases, virtual, domains, etc from a > Postgres database. > > This used to work fine when we had only a couple of e-mail distribution > lists. However, recently a lot of new lists have been added and the > system becomes very slow. > > Turns out there a couple of very complicated views using other views, > using concatenated strings etc. A SELECT on this view takes about 2.5 > seconds. Tuning the resource allocation brought it down to 1.2 seconds. > We have looked at the queries and came to the conclusion that it would > not be feasible to bring this down any further. Are you sure you have finished tuning at the database level - indices, partitions etc.? What does EXPLAIN ANALYZE of a typical slow query show? BTW: Which version of PG are you running? Tino. -- "What we nourish flourishes." - "Was wir nähren erblüht." www.lichtkreis-chemnitz.de www.craniosacralzentrum.de