Обсуждение: Re: New project launched : PostgreSQL GUI Installer for
> -----Original Message----- > From: Tino Wildenhain [mailto:tino@wildenhain.de] > Sent: 31 January 2006 10:53 > To: Dave Page > Cc: Rick Gigger; Marc G. Fournier; Joshua D. Drake; > Christopher Browne; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] New project launched : PostgreSQL GUI > Installer for > > Dave Page schrieb: > > > ... > >>As was said, a gui to produce postgresql.conf files (off host) > >>can be of value. > > > > > > pgAdmin? > > Well, strictly spoken a gui text editor is a gui... but I rather > had in mind something guided with buttons, select boxes and stuff > and references to documentation, calculations and the like. > > :-) Err, yes. pgAdmin? It's somewhat more than a simple text editor. :-) /D
Dave Page schrieb: ... >> >>Well, strictly spoken a gui text editor is a gui... but I rather >>had in mind something guided with buttons, select boxes and stuff >>and references to documentation, calculations and the like. >> >> :-) > > > Err, yes. pgAdmin? It's somewhat more than a simple text editor. Ah, right ;) Didnt see it in action before :-) Now when I actually load a postgresql.conf file I see what you mean. Nice job :-) Figuring out the correct values for some of the buffers and costs is still a bit tough. Otoh, I guess there is no easy way to predict all these. Regards Tino
Tino Wildenhain wrote: > > Figuring out the correct values for some of the buffers and costs > is still a bit tough. Otoh, I guess there is no easy way to predict > all these. pgAdmin has a mechanism to suggest values (currently for autovacuum and listen_address only), which waits for expansion :-) I could think of a wizard that asks decent questions, resulting in proposals. Whether implemented as GUI or not, a questionaire and suggested algorithms to calculate settings (eyeballed from Core) would be a good starting point. Regards, Andreas
On Tue, Jan 31, 2006 at 11:46:03AM +0000, Andreas Pflug wrote: > Tino Wildenhain wrote: > > > > >Figuring out the correct values for some of the buffers and costs > >is still a bit tough. Otoh, I guess there is no easy way to predict > >all these. > > pgAdmin has a mechanism to suggest values (currently for autovacuum and > listen_address only), which waits for expansion :-) I could think of a > wizard that asks decent questions, resulting in proposals. > > Whether implemented as GUI or not, a questionaire and suggested > algorithms to calculate settings (eyeballed from Core) would be a good > starting point. PostgreSQL *desperately* needs a better means of dealing with configuration (though I guess I shouldn't be pushing too hard for this since the current state of affairs brings me business). Any improvement in this area would be very welcome. http://pgfoundry.org/projects/configurator/ is something worth looking at. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, 2006-01-31 at 13:02 -0600, Jim C. Nasby wrote: > On Tue, Jan 31, 2006 at 11:46:03AM +0000, Andreas Pflug wrote: > > Tino Wildenhain wrote: > > > > > > > >Figuring out the correct values for some of the buffers and costs > > >is still a bit tough. Otoh, I guess there is no easy way to predict > > >all these. > > > > pgAdmin has a mechanism to suggest values (currently for autovacuum and > > listen_address only), which waits for expansion :-) I could think of a > > wizard that asks decent questions, resulting in proposals. > > > > Whether implemented as GUI or not, a questionaire and suggested > > algorithms to calculate settings (eyeballed from Core) would be a good > > starting point. > > PostgreSQL *desperately* needs a better means of dealing with > configuration (though I guess I shouldn't be pushing too hard for this > since the current state of affairs brings me business). Any improvement > in this area would be very welcome. > http://pgfoundry.org/projects/configurator/ is something worth looking > at. An ideal facility would be a program that analyzes the workload at runtime and adjusts accordingly. That doesn't sound too hard, within some unambitious boundary. If anyone would like to work on this, I'd be happy to contribute. -jwb
Jeffery, > > PostgreSQL *desperately* needs a better means of dealing with > > configuration (though I guess I shouldn't be pushing too hard for this > > since the current state of affairs brings me business). Any > > improvement in this area would be very welcome. > > http://pgfoundry.org/projects/configurator/ is something worth looking > > at. > > An ideal facility would be a program that analyzes the workload at > runtime and adjusts accordingly. That doesn't sound too hard, within > some unambitious boundary. If anyone would like to work on this, I'd be > happy to contribute. It seems pretty hard to *me*, compared with static configuration. If you have ideas for runtime analysis of configuration criteria, I'd be thrilled to hear them. From my perspective, most of them depend on backend monitoring that we don't have yet (like querying how full the FSM is). -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Tue, 2006-01-31 at 12:11 -0800, Josh Berkus wrote: > Jeffery, > > > > PostgreSQL *desperately* needs a better means of dealing with > > > configuration (though I guess I shouldn't be pushing too hard for this > > > since the current state of affairs brings me business). Any > > > improvement in this area would be very welcome. > > > http://pgfoundry.org/projects/configurator/ is something worth looking > > > at. > > > > An ideal facility would be a program that analyzes the workload at > > runtime and adjusts accordingly. That doesn't sound too hard, within > > some unambitious boundary. If anyone would like to work on this, I'd be > > happy to contribute. > > It seems pretty hard to *me*, compared with static configuration. If you > have ideas for runtime analysis of configuration criteria, I'd be thrilled > to hear them. From my perspective, most of them depend on backend > monitoring that we don't have yet (like querying how full the FSM is). I agree that some instrumentation of the backend might be needed. But several of the performance-critical parameters seem tractable: Effective cache size - should be easy to monitor the system for this Shared buffers - easy to start from a rule-of-thumb and monitor usage Work mem - trace the size and frequency of temp files Wal buffers - trace the average or 80th percentile number of pages generated by transactions Commit delay - track the concurrency level and avg distance btw commits Checkpoint segments - should be very easy to auto-adjust Random page cost - should possible to determine this at runtime Vacuum* - may be possible to determine vacuum impact on concurrent queries
Jeffrey, > I agree that some instrumentation of the backend might be needed. But > several of the performance-critical parameters seem tractable: > > Effective cache size - should be easy to monitor the system for this > Shared buffers - easy to start from a rule-of-thumb and monitor usage > Work mem - trace the size and frequency of temp files > Wal buffers - trace the average or 80th percentile number of pages > generated by transactions > Commit delay - track the concurrency level and avg distance btw commits > Checkpoint segments - should be very easy to auto-adjust > Random page cost - should possible to determine this at runtime > Vacuum* - may be possible to determine vacuum impact on concurrent > queries Great. Wanna join the configurator project? I won't have much time to work on it before March, but anyone with ideas is welcome. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Tue, Jan 31, 2006 at 12:36:31PM -0800, Jeffrey W. Baker wrote: > Random page cost - should possible to determine this at runtime Before worrying about random_page_cost at all it makes a lot more sense to look at the query cost estimates, some of which are pretty far out of wack. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, 2006-01-31 at 17:06 -0600, Jim C. Nasby wrote: > On Tue, Jan 31, 2006 at 12:36:31PM -0800, Jeffrey W. Baker wrote: > > Random page cost - should possible to determine this at runtime > > Before worrying about random_page_cost at all it makes a lot more sense > to look at the query cost estimates, some of which are pretty far out of > wack. I agree, but this problem is orthogonal, no?
On Tue, Jan 31, 2006 at 03:11:50PM -0800, Jeffrey W. Baker wrote: > On Tue, 2006-01-31 at 17:06 -0600, Jim C. Nasby wrote: > > On Tue, Jan 31, 2006 at 12:36:31PM -0800, Jeffrey W. Baker wrote: > > > Random page cost - should possible to determine this at runtime > > > > Before worrying about random_page_cost at all it makes a lot more sense > > to look at the query cost estimates, some of which are pretty far out of > > wack. > > I agree, but this problem is orthogonal, no? No. The problem is that random_page_cost has the wrong effect in some of the cost functions, so even if you set it more accurately you're not buying anything. In fact, you might well be hurting performance. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461