Thread: Throttling PostgreSQL's CPU usage
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
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/
In response to Daniel Griscom <griscom@suitable.com>: > 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>)? 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/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023
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
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 >
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 --
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/
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/
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 --
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 griscom@suitable.com Suitable Systems http://www.suitable.com/ 1 Centre Street, Suite 204 (781) 665-0053 Wakefield, MA 01880-2400
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/
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 --
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.
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
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, "david@lang.hm" <david@lang.hm> 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 >
> 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 soundslike 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 canavoid 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
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 griscom@suitable.com Suitable Systems http://www.suitable.com/ 1 Centre Street, Suite 204 (781) 665-0053 Wakefield, MA 01880-2400
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 --