Re: [PERFORM] Throttling PostgreSQL's CPU usage

[Carlos Moreno]

Daniel Griscom wrote:
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.


As per one of the last suggestions, do consider as well putting a dual 
hard disk
(as in, independent hard disks, to allow for simultaneous access to 
both).  That

way, you can send the WAL (px_log directory) to a separate physical drive
and improve performance (reduce the potential for bottlenecks) if there 
is a

considerable amount of writes.  With Windows, you can mount specific
directories to given HD partitions --- that would do the trick.

Also, of course, do make sure that you give it a generous amount of RAM, so
that an as-large-as-possible fraction of the read operations are done 
directly

off the machine's memory.

BTW, have you considered using a *separate* machine for PostgreSQL?
(that way this machine could be running on Linux or some Unix flavor,
and the Windows machine is dedicated to the ActiveX rendering stuff).
I mean, if you are going to get a new machine because you need to replace
it, you might as well get a new machine not as powerful, since now you
will have the dual-CPU given by the fact that there are two machines.

Good luck!

Carlos
--


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[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

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[Magnus Hagander]

> 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.

Are you sure you're actually cpu limited? The windows schedules is actually 
pretty good at down shifting like that. It sounds like you might be i/o bound 
instead. Especially if you're on ide disks in this machine.

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

Yes. Background processes can run on the other, like the background writer. 
They normally don't use a lot of cpu. You can avoid that as well by setting the 
cpu 
affinity on pg_ctl or postmaster.


> 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.

Again, make sure cpu really is the problem.

/Magnus


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[Luke Lonergan]

You can use the workload management feature that we've contributed to
Bizgres.  That allows you to control the level of statement concurrency by
establishing queues and associating them with roles.

That would provide the control you are seeking.

- Luke


On 5/8/07 4:24 PM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:

> On Tue, 8 May 2007, Carlos Moreno wrote:
> 
>> Daniel Griscom wrote:
>>> 
>>>  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.
>> 
>> My estimate is that yes, you should definitely consider that.
>> 
>>>  I'm only running one query at a time; would that query be guaranteed to
>>>  confine itself to a single processor/core?
>> 
>> From what Joshua mentions, looks like you do have that guarantee.
> 
> isn't there a way to limit how many processes postgres will create?
> 
> if this is limited to 1, what happens when a vaccum run hits (or
> autovaccum)
> 
> David Lang
> 
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
> 



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[david]

On Tue, 8 May 2007, Carlos Moreno wrote:


Daniel Griscom wrote:


 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. 


My estimate is that yes, you should definitely consider that.


 I'm only running one query at a time; would that query be guaranteed to
 confine itself to a single processor/core?


From what Joshua mentions, looks like you do have that guarantee.


isn't there a way to limit how many processes postgres will create?

if this is limited to 1, what happens when a vaccum run hits (or 
autovaccum)


David Lang


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

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[david]

On Tue, 8 May 2007, Daniel Griscom wrote:

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?


it would cause postgres to work harder then it needs to, but it doesn't 
solve the problem of postgres eating cpu that you need for your rendering 
(i.e. it may reduce the stutters, but won't eliminate them)


a single query will confine itself to one core, but if you have a vaccum 
or autovaccum run it will affect the second core.


I don't know what you can do on windows beyond this though.

David Lang

P.S. make sure you get real multi-core cpu's, hyperthreading is _not_ a 
second core for this problem.


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

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[Carlos Moreno]

Daniel Griscom wrote:


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. 


My estimate is that yes, you should definitely consider that.

I'm only running one query at a time; would that query be guaranteed 
to confine itself to a single processor/core?


From what Joshua mentions, looks like you do have that guarantee.



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.


As I mentioned, the important thing is not really raw CPU power as
much as *responsiveness* --- that is what IMO the multi-core/multi-CPU
boosts the most.  The thing is, to guarantee the required responsiveness
with a single-CPU-single-core you would have to increase the CPU
speed (the real speed --- operations per second) by a fraction 
spectacularly

high, so that you that guarantee that the rendering will have the CPU
soon enough ...  When maybe with even less raw CPU power, but
having always one of them ready to process the second task, you
reduce the latency to pretty much zero.

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


Documentation --- look up the postgresql.conf file.

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


Documentation for server-side procedures  (PL/PgSQL --- although as I
understand it, you can write server-side procedures in C, Perl, Java, and
others).

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?


I think it may depend on the version you're running --- but definitely, you
do want to run vacuum analyze (notice, not simply vacuum;  you want a
vacuum analyze) often, and definitely after loading up a lot of new data.
(that is, you definitely want to run a vacuum analyze right away --- that
is, at the earliest opportunity)

HTH,

Carlos
--


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

  http://archives.postgresql.org

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[Steinar H. Gunderson]

On Tue, May 08, 2007 at 07:03:17PM -0400, Daniel Griscom wrote:
> I'm only running one query at a time; would that query be guaranteed to
> confine itself to a single processor/core?

Yes; at least it won't be using two at a time. (Postgres can't guarantee that
Windows won't move it to another core, of course.)

/* Steinar */
-- 
Homepage: http://www.sesse.net/

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[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

[Carlos Moreno]

Joshua D. Drake wrote:
Am I missing something??  There is just *one* instance of this idea 
in, what,
four replies??  I find it so obvious, and so obviously the only 
solution that

has any hope to work, that it makes me think I'm missing something ...

Is it that multiple PostgreSQL processes will end up monopolizing as 
many

CPU cores as you give it?  (ok, that would suck, for sure  :-))


PostgreSQL is process based, so if you have one query that is eating a 
lot of cpu, it is only one cpu... you would have another for your 
render to run on.


There is still the issue that there could be several (many?) queries 
running
concurrently --- but that's much easier to control at the application 
level;

so maybe simply using a multi-CPU/multi-core hardware would be the
simplest solution?

Carlos
--


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[Steinar H. Gunderson]

On Tue, May 08, 2007 at 06:32:14PM -0400, Carlos Moreno wrote:
>> Or use a dual-core system. :-)
> Am I missing something??  There is just *one* instance of this idea in,
> what, four replies??  I find it so obvious, and so obviously the only
> solution that has any hope to work, that it makes me think I'm missing
> something ...

Actually, it should be added that this suggestion was only partially
tongue-in-cheek. I wrote a 3D application as part of an internship a couple
of years ago, and it had a problem that worked vaguely like the given
scenario: Adding a background task (in this case the task that loaded in new
pieces of terrain) would kill the framerate for the user, but nicing down
(actually, down-prioritizing, as this was on Windows) the back-end would
starve it completely of cycles. The solution was to just define that this
would only be run on multiprocessor systems, where both tasks would chug
along nicely :-)

/* Steinar */
-- 
Homepage: http://www.sesse.net/

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[Joshua D. Drake]

Carlos Moreno wrote:

Steinar H. Gunderson wrote:

Or use a dual-core system. :-)


Am I missing something??  There is just *one* instance of this idea in, 
what,
four replies??  I find it so obvious, and so obviously the only solution 
that

has any hope to work, that it makes me think I'm missing something ...

Is it that multiple PostgreSQL processes will end up monopolizing as many
CPU cores as you give it?  (ok, that would suck, for sure  :-))



PostgreSQL is process based, so if you have one query that is eating a 
lot of cpu, it is only one cpu... you would have another for your render 
to run on.


Joshua D. Drake



If there is a way to guarantee (or at least to encourage) that PG will 
not use

more than one, or even two cores, then a quad-core machine looks like a
promising solution...  One thing feels kind of certain to me:  the kind of
system that the OP describes has a most-demanding need for *extremely
high* CPU power --- multi-core, or multi-CPU, would seem the better
solution anyway, since it promotes responsiveness more than raw CPU
power.

Carlos
--


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




--

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

  http://archives.postgresql.org

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[Carlos Moreno]

Steinar H. Gunderson wrote:

Or use a dual-core system. :-)


Am I missing something??  There is just *one* instance of this idea in, 
what,
four replies??  I find it so obvious, and so obviously the only solution 
that

has any hope to work, that it makes me think I'm missing something ...

Is it that multiple PostgreSQL processes will end up monopolizing as many
CPU cores as you give it?  (ok, that would suck, for sure  :-))

If there is a way to guarantee (or at least to encourage) that PG will 
not use

more than one, or even two cores, then a quad-core machine looks like a
promising solution...  One thing feels kind of certain to me:  the kind of
system that the OP describes has a most-demanding need for *extremely
high* CPU power --- multi-core, or multi-CPU, would seem the better
solution anyway, since it promotes responsiveness more than raw CPU
power.

Carlos
--


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

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[Mark Lewis]

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

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[david]

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


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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[Bill Moran]

In response to Daniel Griscom <[EMAIL PROTECTED]>:

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

If it's all PostgreSQL processes that you want take a backseat to your
rendering process, why not just nice the initial PostgreSQL daemon?  All
children will inherit the nice value, and there's no chance of priority
inversion because all the PostgreSQL backends are running at the same
priority.

Just a thought.

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

[EMAIL PROTECTED]
Phone: 412-422-3463x4023

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] Throttling PostgreSQL's CPU usage

[Steinar H. Gunderson]

On Tue, May 08, 2007 at 04:27:10PM -0400, Daniel Griscom wrote:
> 3: ... some other solution I haven't thought of.

On a wild guess, could you try setting the CPU costs higher, to make the
planner choose a less CPU-intensive plan?

Other (weird) suggestions would include calling a user-defined function that
sleep()ed for you between every row. Or use a dual-core system. :-)

/* Steinar */
-- 
Homepage: http://www.sesse.net/

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