Hi everyone,
Just looked on the IBM website for info relating to shared memory and
IPC limits in AIX, and found a few useful-looking documents:
The Interprocess Communication (IPC) Overview
http://www-1.ibm.com/support/docview.wss?rs=0&context=SWG10&q=shmget&uid=aix15f11dd1d98f3551f85256816006a0
People seemed to like the idea:
Add a script to ask system configuration questions and tune
postgresql.conf.
---
Bruce Momjian wrote:
> Peter Eisentraut wrote:
> > Tom Lane writes:
> >
> > > Well, as I com
Bruce Momjian writes:
> Tom Lane wrote:
> > Bruce Momjian <[EMAIL PROTECTED]> writes:
> > > So, my idea is to add a message at the end of initdb that states people
> > > should run the pgtune script before running a production server.
> >
> > Do people read what initdb has to say?
> >
> > IIRC, the
>Nutshell:
> "Easy to install but is horribly slow."
>
> or
>
> "Took a couple of minutes to configure and it rocks!"
Since when is it easy to install on win32?
The easiest way I know of is through Cygwin, then you have to worry about
installing the IPC service (an gettin
On Thu, 13 Feb 2003, Bruce Momjian wrote:
> To auto-tune, you would need to monitor swap usage and other stuff that
> may vary too much based on load from other systems. Only the admin
> knows how to answer some of those questions.
No, to "auto-tune" many parameters that currently require manual
On Fri, 2003-02-14 at 03:00, Daniel Kalchev wrote:
> >>>Jason Hihn said:
> > Pardon my ignorance, but there's no way to auto-tune? Ship it with a thread
> > that gathers statistics and periodically re-tunes the database parameters.
> > Of course, be able to turn it off. People that actually take
>>>Josh Berkus said:
> How about we take this discussion to the Performance List, where it belongs?
I believe the design and addition of code that collects and outputs the usage patterns
of the database (statistics) belongs here.
If we take the approach to providing information to tune PostgreS
>>>Jason Hihn said:
> Pardon my ignorance, but there's no way to auto-tune? Ship it with a thread
> that gathers statistics and periodically re-tunes the database parameters.
> Of course, be able to turn it off. People that actually take the time to run
> tune manually will turn it off as to no
>>>Bruce Momjian said:
>
> I imagined they could run pgtune anytime after install to update those
> performance parameters. It gives them a one-stop location to at least
> do minimal tuning, and as their load changes, they can run it again.
True, but to make reasonably good choice, they will
Bruce Momjian wrote:
> We could prevent the postmaster from starting unless they run pg_tune or
> if they have modified postgresql.conf from the default. Of course,
> that's pretty drastic.
If you're going to do that, then you may as well make the defaults
something that will perform reasonably w
HEY PEOPLE!
How about we take this discussion to the Performance List, where it belongs?
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Tatsuo,
> Sigh. People always complain "pgbench does not reliably producing
> repeatable numbers" or something then say "that's because pgbench's
> transaction has too much contention on the branches table". So I added
> -N option to pgbench which makes pgbench not to do any UPDATE to
> the branch
Josh Berkus wrote:
> > > Uh ... do we have a basis for recommending any particular sets of
> > > parameters for these different scenarios? This could be a good idea
> > > in the abstract, but I'm not sure I know enough to fill in the details.
>
> Sure.
> Mostly-Read database, few users, good ha
2 PM
To: Daniel Kalchev
Cc: PostgresSQL Hackers Mailing List; [EMAIL PROTECTED]
Subject: Re: [pgsql-advocacy] [HACKERS] Changing the default
configuration
I imagined they could run pgtune anytime after install to update those
performance parameters. It gives them a one-stop location to at least
do
an
> Sent: Thursday, February 13, 2003 2:22 PM
> To: Daniel Kalchev
> Cc: PostgresSQL Hackers Mailing List; [EMAIL PROTECTED]
> Subject: Re: [pgsql-advocacy] [HACKERS] Changing the default
> configuration
>
>
>
> I imagined they could run pgtune anytime after install to u
> On Wed, 12 Feb 2003, Bruce Momjian wrote:
> >
> > And part of the reason is because some/most BSD's map the page tables
> > into physical RAM (kernel space) rather than use some shared page table
> > mechanism. This is good because it prevents the shared memory from
> > being swapped out (perfor
I imagined they could run pgtune anytime after install to update those
performance parameters. It gives them a one-stop location to at least
do minimal tuning, and as their load changes, they can run it again.
---
Daniel Ka
I was speaking of the 4.4 BSD. FreeBSD has the merged VM, and I think
NetBSD only recently did that. BSD/OS does do the locking by default
and it maps into the kernel address space. I believe FreeBSD has a
sysctl to control locking of SysV memory.
One advantage of having it all at the same VM
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > So, my idea is to add a message at the end of initdb that states people
> > should run the pgtune script before running a production server.
>
> Do people read what initdb has to say?
>
> IIRC, the RPM install scripts hide initdb's o
Bruce Momjian <[EMAIL PROTECTED]> writes:
> So, my idea is to add a message at the end of initdb that states people
> should run the pgtune script before running a production server.
Do people read what initdb has to say?
IIRC, the RPM install scripts hide initdb's output from the user
entirely.
On Wed, 12 Feb 2003, Bruce Momjian wrote:
> Christopher Kings-Lynne wrote:
>
> > You cannot change SHMMAX on the fly on FreeBSD.
>
> And part of the reason is because some/most BSD's map the page tables
> into physical RAM (kernel space) rather than use some shared page table
> mechanism. This is
>>>Bruce Momjian said:
[...]
> For example, we can ask them how many rows and tables they will be
> changing, on average, between VACUUM runs. That will allow us set the
> FSM params. We can ask them about using 25% of their RAM for shared
> buffers. If they have other major apps running on
Peter Eisentraut wrote:
> Tom Lane writes:
>
> > Well, as I commented later in that mail, I feel that 1000 buffers is
> > a reasonable choice --- but I have to admit that I have no hard data
> > to back up that feeling.
>
> I know you like it in that range, and 4 or 8 MB of buffers by default
> s
>> You cannot change SHMMAX on the fly on FreeBSD.
I think we suffered some topic drift here --- wasn't the last question
about whether SEMMAX can be increased on-the-fly? That wouldn't have
anything to do with memory-mapping strategies...
regards, tom lane
-
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> I know you like it in that range, and 4 or 8 MB of buffers by default
> should not be a problem. But personally I think if the optimal buffer
> size does not depend on both the physical RAM you want to dedicate to
> PostgreSQL and the nature and size
Christopher Kings-Lynne wrote:
> > Seriously, I know Linux can change these on the fly, and I'm pretty sure
> > Solaris can too. I haven't played with BSD for a while so can't speak
> > about that. Anyone else know?
>
> You cannot change SHMMAX on the fly on FreeBSD.
And part of the reason is
--On Thursday, February 13, 2003 09:47:28 +0800 Christopher Kings-Lynne
<[EMAIL PROTECTED]> wrote:
Seriously, I know Linux can change these on the fly, and I'm pretty sure
Solaris can too. I haven't played with BSD for a while so can't speak
about that. Anyone else know?
You cannot change S
> Seriously, I know Linux can change these on the fly, and I'm pretty sure
> Solaris can too. I haven't played with BSD for a while so can't speak
> about that. Anyone else know?
You cannot change SHMMAX on the fly on FreeBSD.
Chris
---(end of broadcast)-
> Had this happen at a previous employer, and it definitely is bad. I
> believe we had to do a reboot to clear it up. And we saw the problem a
> couple of times since the sys admin wasn't able to deduce what had
> happened the first time we got it. IIRC the problem hit somewhere around
> 150 connec
Tom Lane writes:
> Well, as I commented later in that mail, I feel that 1000 buffers is
> a reasonable choice --- but I have to admit that I have no hard data
> to back up that feeling.
I know you like it in that range, and 4 or 8 MB of buffers by default
should not be a problem. But personally
Oh, another setting that should be a "default" for most users is to initdb
automatically with locale of C. If they need a different locale, they
should have to pick it.
The performance of Postgresql with a locale other than C when doing like
and such is a serious ding. I'd much rather have th
On Tue, 11 Feb 2003, Tom Lane wrote:
> "scott.marlowe" <[EMAIL PROTECTED]> writes:
> > ... If he starts running out of semaphores, that's a
> > problem he can address while his database is still up and running in most
> > operating systems, at least in the ones I use.
>
> Back in the day, this
Robert Treat <[EMAIL PROTECTED]> writes:
> Had this happen at a previous employer, and it definitely is bad. I
> believe we had to do a reboot to clear it up. And we saw the problem a
> couple of times since the sys admin wasn't able to deduce what had
> happened the first time we got it. IIRC the
On Tue, Feb 11, 2003 at 05:32:58PM -0700, scott.marlowe wrote:
> So, what OSes would have a problem, if any, with boosting something
> like max connects to 200? What are the breaking points on other OSes?
Solaris 8 broke at 200, the last time I tried on a new box. 150
didn't break it, though.
On Wed, 2003-02-12 at 10:36, Robert Treat wrote:
> On Tue, 2003-02-11 at 21:00, Tatsuo Ishii wrote:
> > >
> > > while 200 may seem high, 32 definitely seems low. So, what IS a good
> > > compromise? for this and ALL the other settings that should probably be a
> > > bit higher. I'm guessing s
On Wed, 2003-02-12 at 11:39, Andrew Sullivan wrote:
> On Tue, Feb 11, 2003 at 05:25:29PM -0700, Rick Gigger wrote:
>
> > The type of person who can't configure it or doesnt' think to try is
> > probably not doing a project that requires any serious performance.
>
> I have piles of email, have fie
On Tue, Feb 11, 2003 at 05:25:29PM -0700, Rick Gigger wrote:
> The type of person who can't configure it or doesnt' think to try is
> probably not doing a project that requires any serious performance.
I have piles of email, have fielded thousands of phone calls, and
have had many conversations w
On Tue, 2003-02-11 at 21:00, Tatsuo Ishii wrote:
> >
> > while 200 may seem high, 32 definitely seems low. So, what IS a good
> > compromise? for this and ALL the other settings that should probably be a
> > bit higher. I'm guessing sort_mem or 4 or 8 meg hits the knee for most
> > folks, an
>>>"scott.marlowe" said:
> On 11 Feb 2003, Greg Copeland wrote:
> > Besides, I'm not sure that it makes sense to let other product needs
> > dictate the default configurations for this one. It would be one thing
> > if the vast majority of people only used PostgreSQL with Apache. I know
> >
On Tuesday 11 Feb 2003 10:56 pm, you wrote:
> Josh Berkus <[EMAIL PROTECTED]> writes:
> > What if we supplied several sample .conf files, and let the user choose
> > which to copy into the database directory? We could have a "high read
> > performance" profile, and a "transaction database" profil
> A separate line of investigation is "what is the lowest common
> denominator nowadays?" I think we've established that SHMMAX=1M
> is obsolete, but what replaces it as the next LCD? 4M seems to be
> correct for some BSD flavors, and I can confirm that that's the
> current default for Mac OS X -
> >> We could retarget to try to stay under SHMMAX=4M, which I think is
> >> the next boundary that's significant in terms of real-world platforms
> >> (isn't that the default SHMMAX on some BSDen?). That would allow us
> >> 350 or so shared_buffers, which is better, but still not really a
> >> se
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Tom Lane writes:
>> We could retarget to try to stay under SHMMAX=4M, which I think is
>> the next boundary that's significant in terms of real-world platforms
>> (isn't that the default SHMMAX on some BSDen?). That would allow us
>> 350 or so shared_
"scott.marlowe" <[EMAIL PROTECTED]> writes:
> ... The original choice of 32 was set because the original
> choice of 64 shared memory blocks as the most we could hope for on common
> OS installs. Now that we're looking at cranking that up to 1000,
> shouldn't max connections get a look too?
Ac
> >After it's all said and done, I would rather someone simply say, "it's
> >beyond my skill set", and attempt to get help or walk away. That seems
> >better than them being able to run it and say, "it's a dog", spreading
> >word-of-mouth as such after they left PostgreSQL behind. Worse yet,
> >t
> > It likely. First you will ran out kernel file descriptors. This could
> > be solved by increasing the kernel table or lowering
> > max_files_per_process, though. Second the total throughput will
> > rapidly descrease if you don't have enough RAM and many
> > CPUs. PostgreSQL can not handle many
On Wed, 12 Feb 2003, Tatsuo Ishii wrote:
> > My other pet peeve is the default max connections setting. This should be
> > higher if possible, but of course, there's always the possibility of
> > running out of file descriptors.
> >
> > Apache has a default max children of 150, and if using PH
On Tue, Feb 11, 2003 at 17:42:06 -0700,
"scott.marlowe" <[EMAIL PROTECTED]> wrote:
>
> The poor performance of Postgresql in it's current default configuration
> HAS cost us users, trust me, I know a few we've almost lost where I work
> that I converted after some quick tweaking of their datab
On Wed, 12 Feb 2003, Curt Sampson wrote:
> On Tue, 11 Feb 2003, Tom Lane wrote:
>
> > It's a lot too conservative. I've been thinking for awhile that we
> > should adjust the defaults.
>
> Some of these issues could be made to Just Go Away with some code
> changes. For example, using mmap rathe
It's interesting that people focus on shared_buffers. From my
experience the most dominating parameter for performance is
wal_sync_method. It sometimes makes ~20% performance difference. On
the otherhand, shared_buffers does very little for
performance. Moreover too many shared_buffers cause perfor
> If I thought that pgbench was representative of anything, or even
> capable of reliably producing repeatable numbers, then I might subscribe
> to results derived this way. But I have little or no confidence in
> pgbench. Certainly I don't see how you'd use it to produce
> recommendations for a
> My other pet peeve is the default max connections setting. This should be
> higher if possible, but of course, there's always the possibility of
> running out of file descriptors.
>
> Apache has a default max children of 150, and if using PHP or another
> language that runs as an apache modu
On Tue, 11 Feb 2003, Rick Gigger wrote:
> The type of person who can't configure it or doesnt' think to try is
> probably not doing a project that requires any serious performance. As long
> as you are running it on decent hardware postgres will run fantastic for
> anything but a very heavy load.
On Tue, 11 Feb 2003, Tom Lane wrote:
> It's a lot too conservative. I've been thinking for awhile that we
> should adjust the defaults.
Some of these issues could be made to Just Go Away with some code
changes. For example, using mmap rather than SysV shared memory
would automatically optimize y
On Tue, 11 Feb 2003, Merlin Moncure wrote:
> Here's the comment I was referring to:
>
> /*
> * InitProcGlobal -
> * initializes the global process table. We put it here so that
> * the postmaster can do this initialization.
> *
> * We also create all the per-process semaphores
> On Tue, 2003-02-11 at 10:20, Tom Lane wrote:
> > "Merlin Moncure" <[EMAIL PROTECTED]> writes:
> > > May I make a suggestion that maybe it is time to start thinking about
> > > tuning the default config file, IMHO its just a little bit too
> > > conservative,
> >
> > It's a lot too conservative.
On Tuesday 11 February 2003 13:03, Robert Treat wrote:
> On Tue, 2003-02-11 at 12:08, Justin Clift wrote:
> > b) Said benchmarking person knows very little about PostgreSQL, so they
> > install the RPM's, packages, or whatever, and "it works". Then they run
> > whatever benchmark they've downloade
Tom Lane writes:
> We could retarget to try to stay under SHMMAX=4M, which I think is
> the next boundary that's significant in terms of real-world platforms
> (isn't that the default SHMMAX on some BSDen?). That would allow us
> 350 or so shared_buffers, which is better, but still not really a
>
True, but even so, 32 max connections is a bit light. I have more
pgsql databases than that on my box now. My point in my previous answer
to Tom was that you HAVE to shut down postgresql to change this. It
doesn't allocate tons of semaphores on startup,
[snip]
is this correct? I recall loo
On 11 Feb 2003, Greg Copeland wrote:
> On Tue, 2003-02-11 at 12:55, Tom Lane wrote:
> > "scott.marlowe" <[EMAIL PROTECTED]> writes:
> > > Is setting the max connections to something like 200 reasonable, or likely
> > > to cause too many problems?
> >
> > That would likely run into number-of-sema
On Tue, 11 Feb 2003, Tom Lane wrote:
> "scott.marlowe" <[EMAIL PROTECTED]> writes:
> > Is setting the max connections to something like 200 reasonable, or likely
> > to cause too many problems?
>
> That would likely run into number-of-semaphores limitations (SEMMNI,
> SEMMNS). We do not seem to
Tom Lane wrote:
I think that what this discussion is really leading up to is that we
are going to decide to apply the same principle to performance. The
out-of-the-box settings ought to give reasonable performance, and if
your system can't handle it, you should have to take explicit action
to ac
On Tue, 2003-02-11 at 12:55, Tom Lane wrote:
> "scott.marlowe" <[EMAIL PROTECTED]> writes:
> > Is setting the max connections to something like 200 reasonable, or likely
> > to cause too many problems?
>
> That would likely run into number-of-semaphores limitations (SEMMNI,
> SEMMNS). We do not
"scott.marlowe" <[EMAIL PROTECTED]> writes:
> Is setting the max connections to something like 200 reasonable, or likely
> to cause too many problems?
That would likely run into number-of-semaphores limitations (SEMMNI,
SEMMNS). We do not seem to have as good documentation about changing
that as
On Tue, 2003-02-11 at 13:01, Tom Lane wrote:
> "Jon Griffin" <[EMAIL PROTECTED]> writes:
> > So it appears that linux at least is way above your 8 meg point, unless I
> > am missing something.
>
> Yeah, AFAIK all recent Linuxen are well above the range of parameters
> that I was suggesting (and ev
My other pet peeve is the default max connections setting. This should be
higher if possible, but of course, there's always the possibility of
running out of file descriptors.
Apache has a default max children of 150, and if using PHP or another
language that runs as an apache module, it is qu
Apology
After Mark calms down and, in fact, sees that Greg was saying the right thing
after all, chagrin is the only word.
I'm sorry.
Greg Copeland wrote:
On Tue, 2003-02-11 at 11:23, mlw wrote:
Greg Copeland wrote:
I'd personally rather have people stumble
On Tue, 2003-02-11 at 12:08, Justin Clift wrote:
> b) Said benchmarking person knows very little about PostgreSQL, so they
> install the RPM's, packages, or whatever, and "it works". Then they run
> whatever benchmark they've downloaded, or designed, or whatever
>
Out of curiosity, how feasibl
mlw <[EMAIL PROTECTED]> writes:
> This attitude sucks. If you want a product to be used, you must put the
> effort into making it usable.
> [snip]
AFAICT, you are flaming Greg for recommending the exact same thing you
are recommending. Please calm down and read again.
re
Justin Clift <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Uh ... do we have a basis for recommending any particular sets of
>> parameters for these different scenarios? This could be a good idea
>> in the abstract, but I'm not sure I know enough to fill in the details.
> Without too much hack
Tom, Justin,
> > Uh ... do we have a basis for recommending any particular sets of
> > parameters for these different scenarios? This could be a good idea
> > in the abstract, but I'm not sure I know enough to fill in the details.
Sure.
Mostly-Read database, few users, good hardware, complex q
Tom Lane wrote:
Uh ... do we have a basis for recommending any particular sets of
parameters for these different scenarios? This could be a good idea
in the abstract, but I'm not sure I know enough to fill in the details.
A lower-tech way to accomplish the same result is to document these
alter
On Tue, 2003-02-11 at 11:23, mlw wrote:
> Greg Copeland wrote:
>
> >
> >
> >I'd personally rather have people stumble trying to get PostgreSQL
> >running, up front, rather than allowing the lowest common denominator
> >more easily run PostgreSQL only to be disappointed with it and move on.
> >
>
Josh Berkus wrote:
Tom, Justin,
What if we supplied several sample .conf files, and let the user choose which
to copy into the database directory? We could have a "high read
performance" profile, and a "transaction database" profile, and a
"workstation" profile, and a "low impact" profile
Josh Berkus <[EMAIL PROTECTED]> writes:
> What if we supplied several sample .conf files, and let the user choose which
> to copy into the database directory? We could have a "high read
> performance" profile, and a "transaction database" profile, and a
> "workstation" profile, and a "low impac
> What if we supplied several sample .conf files, and let the user choose
> which to copy into the database directory? We could have a "high read
Exactly my first thought when reading the proposal for a setting suited for
performance tests.
> performance" profile, and a "transaction database"
On Tue, 2003-02-11 at 12:10, Steve Crawford wrote:
> A quick-'n'-dirty first step would be more comments in postgresql.conf. Most
This will not solve the issue with the large number of users who have no
interest in looking at the config file -- but are interested in
publishing their results.
--
Tom, Justin,
> > What I would really like to do is set the default shared_buffers to
> > 1000. That would be 8 meg worth of shared buffer space. Coupled with
> > more-realistic settings for FSM size, we'd probably be talking a shared
> > memory request approaching 16 meg. This is not enough RAM
Greg Copeland wrote:
I'd personally rather have people stumble trying to get PostgreSQL
running, up front, rather than allowing the lowest common denominator
more easily run PostgreSQL only to be disappointed with it and move on.
After it's all said and done, I would rather someone simply s
Tom Lane wrote:
What I would really like to do is set the default shared_buffers to
1000. That would be 8 meg worth of shared buffer space. Coupled with
more-realistic settings for FSM size, we'd probably be talking a shared
memory request approaching 16 meg. This is not enough RAM to bother
a
Tom Lane wrote:
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
May I make a suggestion that maybe it is time to start thinking about
tuning the default config file, IMHO its just a little bit too
conservative,
It's a lot too conservative. I've been thinking for awhile t
On Tue, 2003-02-11 at 10:20, Tom Lane wrote:
> "Merlin Moncure" <[EMAIL PROTECTED]> writes:
> > May I make a suggestion that maybe it is time to start thinking about
> > tuning the default config file, IMHO its just a little bit too
> > conservative,
>
> It's a lot too conservative. I've been thi
82 matches
Mail list logo
