On Thu, 2006-11-02 at 09:25, Madison Kelly wrote: > Scott Marlowe wrote: > > On Thu, 2006-11-02 at 09:14, Madison Kelly wrote: > >> Hi all, > >> > >> I've got a script (perl, in case it matters) that I need to run once > >> a month to prepare statements. This script queries and updates the > >> database a *lot*. I am not concerned with the performance of the SQL > >> calls so much as I am about the impact it has on the server's load. > >> > >> Is there a way to limit queries speed (ie: set a low 'nice' value on > >> a query)? This might be an odd question, or I could be asking the > >> question the wrong way, but hopefully you the idea. :) > > > > While you can safely set the priority lower on the calling perl script, > > setting db backend priorities lower can result in problems caused by > > "priority inversion" Look up that phrase on the pgsql admin, perform, > > general, or hackers lists for an explanation, or go here: > > > > http://en.wikipedia.org/wiki/Priority_inversion > > > > I have a simple script that grabs raw data from an oracle db and shoves > > it into a postgresql database for reporting purposes. Every 100 rows I > > put into postgresql, I usleep 10 or so and the load caused by that > > script on both systems is minimal. You might try something like that. > > Will the priority of the script pass down to the pgsql queries it calls? > I figured (likely incorrectly) that because the queries were executed by > the psql server the queries ran with the server's priority. If this > isn't the case, then perfect. :) nope, the priorities don't pass down. you connect via a client lib to the server, which spawns a backend process that does the work for you. The backend process inherits its priority from the postmaster that spawns it, and they all run at the same priority. > Thanks for the tip, too, it's something I will try. Sometimes it's the simple solutions that work best. :) Welcome to the world of pgsql, btw...
