[PERFORM] Throttling PostgreSQL's CPU usage

2007-05-08 Thread Daniel Griscom
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 
)?


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 [EMAIL PROTECTED]
Suitable Systems  http://www.suitable.com/
1 Centre Street, Suite 204(781) 665-0053
Wakefield, MA  01880-2400

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PERFORM] Throttling PostgreSQL's CPU usage

2007-05-08 Thread Daniel Griscom
Thanks for all the feedback. Unfortunately I didn't specify that this 
is running on a WinXP machine (the 3D renderer is an ActiveX plugin), 
and I don't even think "nice" is available. I've tried using the 
Windows Task Manager to set every postgres.exe process to a low 
priority, but that didn't make a difference.


Several people have mentioned having multiple processors; my current 
machine is a uni-processor machine, but I believe we could spec the 
actual runtime machine to have multiple processors/cores. I'm only 
running one query at a time; would that query be guaranteed to 
confine itself to a single processor/core?


In terms of performance, I don't think simply more power will do the 
trick; I've got an AMD 3200+, and even doubling the power/halving the 
stutter time won't be good enough.


Someone suggested "setting the CPU costs higher"; where would I learn 
about that?


Someone else mentioned having a custom function that sleep()ed on 
every row access; where would I learn more about that?


I've also been reading up on VACUUM. I haven't explicitly run it in 
the several days since I've installed the database (by loading a 
humongous data.sql file); might this be part of the performance 
problem?



Thanks again,
Dan

--
Daniel T. Griscom [EMAIL PROTECTED]
Suitable Systems  http://www.suitable.com/
1 Centre Street, Suite 204(781) 665-0053
Wakefield, MA  01880-2400

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PERFORM] Throttling PostgreSQL's CPU usage

2007-05-09 Thread Daniel Griscom
Thanks again for all the feedback. Running on a dual processor/core 
machine is clearly a first step, and I'll look into the other 
suggestions as well.



Thanks,
Dan

--
Daniel T. Griscom [EMAIL PROTECTED]
Suitable Systems  http://www.suitable.com/
1 Centre Street, Suite 204(781) 665-0053
Wakefield, MA  01880-2400

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings