From: Daniel Griscom <griscom@suitable.com>
To: pgsql-performance@postgresql.org
Subject: Throttling PostgreSQL's CPU usage
Date: Tue, 8 May 2007 16:27:10 -0400
Message-ID: <p0624080bc266892a91b2@[192.168.1.8]> (raw)
I'm building a kiosk with a 3D front end accessing PostGIS/PostgreSQL
via Apache/PHP. The 3D display is supposed to show smooth motion from
location to location, with PostGIS giving dynamically updated
information on the locations. Everything runs on the same machine,
and it all works, but when I start a query the 3D display stutters
horribly. It looks like PostgreSQL grabs hold of the CPU and doesn't
let go until it's completed the query.
I don't need the PostgreSQL query to return quickly, but I must
retain smooth animation while the query is being processed. In other
words, I need PostgreSQL to spread out its CPU usage so that it
doesn't monopolize the CPU for any significant time (more than 50ms
or so).
Possible solutions:
1: Set the PostgreSQL task priority lower than the 3D renderer task,
and to make sure that the 3D renderer sleep()s enough to let
PostgreSQL get its work done. The obvious objection to this obvious
solution is "Priority inversion!", but I see that as an additional
challenge to be surmounted rather than an absolute prohibition. So,
any thoughts on setting the PostgreSQL task priority (including by
the much-maligned tool shown at
<http://weblog.bignerdranch.com/?p=11;)?
2: Some variation of the Cost-Based Vacuum Delay. Hypothetically,
this would have the PostgreSQL task sleep() periodically while
processing the query, allowing the 3D renderer to continue working at
a reduced frame rate. My understanding, however, is that this only
works during VACUUM and ANALYZE commands, so it won't help during my
SELECT commands. So, any thoughts on using Cost-Based Vacuum Delay as
a Cost-Based Select Delay?
3: ... some other solution I haven't thought of.
Any thoughts, suggestions, ideas?
Thanks,
Dan
--
Daniel T. Griscom griscom@suitable.com
Suitable Systems http://www.suitable.com/
1 Centre Street, Suite 204 (781) 665-0053
Wakefield, MA 01880-2400
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: griscom@suitable.com
Subject: Re: Throttling PostgreSQL's CPU usage
In-Reply-To: <p0624080bc266892a91b2@[192.168.1.8]>
References: <p0624080bc266892a91b2@[192.168.1.8]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox