1. If you go the route of using nice, you might want to run the 3D
front-end at a higher priority instead of running PG at a lower
priority. That way apache, php and the other parts all run at the same
priority as PG and just the one task that you want to run smoothly is
elevated.
2. You may not even need separate priorities if you're running on Linux
with a recent kernel and you enable the sleep() calls that you would
need anyway for solution #1 to work. This is because Linux kernels are
getting pretty good nowadays about rewarding tasks with a lot of sleeps,
although there are some further kernel changes still under development
that look even more promising.
-- Mark
On Tue, 2007-05-08 at 16:27 -0400, 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>)?
>
> 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
>
