Received: from localhost (pluto.hub.org [200.46.204.4])
	by postgresql.org (Postfix) with ESMTP id 131B59FB298
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  2 Nov 2006 12:01:07 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.4]) (amavisd-new, port 10024)
	with ESMTP id 72358-03 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  2 Nov 2006 12:01:02 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from lynet.oppetid.no (oppetid.no [80.65.51.2])
	by postgresql.org (Postfix) with ESMTP id C4A569FB299
	for <pgsql-performance@postgresql.org>;
	Thu,  2 Nov 2006 12:01:01 -0400 (AST)
Received: by lynet.oppetid.no (Postfix, from userid 1019)
	id 8487E11AF9A; Thu,  2 Nov 2006 17:00:57 +0100 (CET)
Date: Thu, 2 Nov 2006 17:00:57 +0100
From: Tobias Brox <tobias@nordicbet.com>
To: Madison Kelly <linux@alteeve.com>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>,
	pgsql-performance@postgresql.org
Subject: Re: Setting "nice" values
Message-ID: <20061102160057.GA26580@oppetid.no>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Organization: Group Nordicbet
User-Agent: Mutt/1.5.9i
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.136 tagged_above=0 required=5 tests=BAYES_50,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200611/30
X-Sequence-Number: 21800

[Madison Kelly - Thu at 10:25:07AM -0500]
> 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. 

I think you are right, and in any case, I don't think the niceness
value won't help much if the bottleneck is iowait.

In our application, I've made a special function for doing
low-priority transactions which I believe is quite smart - though maybe
not always.  Before introducing this logic, we observed we had a tipping
point, too many queries, and the database wouldn't swallow them fast
enough, and the database server just jammed up, trying to work at too
many queries at once, yielding the results far too slow.

In the config file, I now have those two flags set:

 stats_start_collector = on
 stats_command_string = on

This will unfortunately cause some CPU-load, but the benefit is great
- one can actually check what the server is working with at any time:

  select * from pg_stat_activity

with those, it is possible to check a special view pg_stat_activity -
it will contain all the queries the database is working on right now.
My idea is to peek into this table - if there is no active queries,
the database is idle, and it's safe to start our low-priority
transaction.  If this view is full of stuff, one should certainly not
run any low-priority transactions, rather sleep a bit and try again
later.

 select count(*) from pg_stat_activity where not current_query like
 '<IDLE>%' and query_start+?<now()

The algorithm takes four parameters, the time value to put in above,
the maximum number of queries allowed to run, the sleep time between
each attempt, and the amount of attempts to try before giving up.


So here are the cons and drawbacks:

 con: Given small queries and small transactions, one can tune this in
      such a way that the low priority queries (almost) never causes
      significant delay for the higher priority queries.

 con: can be used to block users of an interactive query
      application to cause disturbances on the production database.

 con: can be used for pausing low-priority batch jobs to execute only
      when the server is idle.

 drawback: unsuitable for long-running queries and transactions 

 drawback: with fixed values in the parameters above, one risks that
           the queries never gets run if the server is sufficiently stressed.

 drawback: the stats collection requires some CPU

 drawback: the "select * from pg_stats_activity" query requires some CPU

 drawback: the pg_stats_activity-view is constant within the
           transaction, so one has to roll back if there is activity
           (this is however not a really bad thing, because one
           certainly shouldn't live an idle transaction around if the
           database is stressed).