Re: Throttling PostgreSQL's CPU usage

On Tue, 8 May 2007, Daniel Griscom wrote:

> 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>)?

this may or may not help

> 3: ... some other solution I haven't thought of.

take a look at the scheduler discussion that has been takeing place on the
linux-kernel list. there are a number of things being discussed specificly
to address the type of problems that you are running into (CPU hog causes
latencies for graphics processes).

it looks like nothing will go into the 2.6.22 kernel officially, but if
you are willing to test the begezzes out of it before you depend on it, I
suspect that either the SD or CFS schedulers will clean things up for you.

David Lang