pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [HACKERS] Changing the default configuration
12+ messages / 8 participants
[nested] [flat]
* Re: [HACKERS] Changing the default configuration
@ 2003-02-12 01:10 Tatsuo Ishii <t-ishii@sra.co.jp>
0 siblings, 2 replies; 12+ messages in thread
From: Tatsuo Ishii @ 2003-02-12 01:10 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: justin@postgresql.org; josh@agliodbs.com; merlin.moncure@rcsonline.com; pgsql-hackers@postgresql.org; pgsql-advocacy@postgresql.org
> 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 range of application scenarios, when it's only
> one very narrow scenario itself.
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 branches table. But still people continue to complian...
There should be many factors that would produce non-repeatable
results exist, for instance kenel buffer, PostgreSQL's buffer manager,
pgbench itself etc. etc... So far it seems no one has ever made clean
explanation why non-repeatable results happen...
--
Tatsuo Ishii
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing the default configuration
@ 2003-02-12 01:10 Tatsuo Ishii <t-ishii@sra.co.jp>
0 siblings, 1 reply; 12+ messages in thread
From: Tatsuo Ishii @ 2003-02-12 01:10 UTC (permalink / raw)
To: scott.marlowe@ihs.com; +Cc: pgsql-hackers@postgresql.org
> 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 quite possible to use up all
> the pgsql backend slots before using up all the apache child slots.
>
> Is setting the max connections to something like 200 reasonable, or likely
> to cause too many problems?
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 concurrent
connections/transactions effectively. I recommend to employ some kind
of connection pooling software and lower the max connections.
--
Tatsuo Ishii
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing the default configuration
@ 2003-02-12 01:10 Tatsuo Ishii <t-ishii@sra.co.jp>
0 siblings, 0 replies; 12+ messages in thread
From: Tatsuo Ishii @ 2003-02-12 01:10 UTC (permalink / raw)
To: matthew@zeut.net; +Cc: pgsql-hackers@postgresql.org; tgl@sss.pgh.pa.us
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 performance
degration! I guess this is due to the poor design of bufmgr. Until it
is fixed, just increasing the number of shared_buffers a bit, say
1024, is enough IMHO.
--
Tatsuo Ishii
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing the default configuration
@ 2003-02-12 01:23 scott.marlowe <scott.marlowe@ihs.com>
parent: Tatsuo Ishii <t-ishii@sra.co.jp>
0 siblings, 1 reply; 12+ messages in thread
From: scott.marlowe @ 2003-02-12 01:23 UTC (permalink / raw)
To: Tatsuo Ishii <t-ishii@sra.co.jp>; +Cc: pgsql-hackers@postgresql.org
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 PHP or another
> > language that runs as an apache module, it is quite possible to use up all
> > the pgsql backend slots before using up all the apache child slots.
> >
> > Is setting the max connections to something like 200 reasonable, or likely
> > to cause too many problems?
>
> 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 concurrent
> connections/transactions effectively. I recommend to employ some kind
> of connection pooling software and lower the max connections.
Don't know if you saw my other message, but increasing max connects to 200
used about 10% of all my semaphores and about 10% of my file handles.
That was while running pgbench to create 200 simo sessions.
Keep in mind, on my fairly small intranet database server, I routinely
have >32 connections, most coming from outside my webserver. Probably no
more than 4 or 5 connects at a time come from there. These are all things
like Windows boxes with ODBC running access or something similar. Many of
the connections are idle 98% of the time, and use little or no real
resources, even getting swapped out should the server need the spare
memory (it doesn't :-) that machine is set to 120 max simos if I remember
correctly.
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, and the max fsm settings tom has suggested make sense.
What wal_sync method should we make default? Or should we pick one based
on the OS the user is running?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing the default configuration
@ 2003-02-12 02:00 Tatsuo Ishii <t-ishii@sra.co.jp>
parent: scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tatsuo Ishii @ 2003-02-12 02:00 UTC (permalink / raw)
To: scott.marlowe@ihs.com; +Cc: pgsql-hackers@postgresql.org
> > 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 concurrent
> > connections/transactions effectively. I recommend to employ some kind
> > of connection pooling software and lower the max connections.
>
> Don't know if you saw my other message, but increasing max connects to 200
> used about 10% of all my semaphores and about 10% of my file handles.
> That was while running pgbench to create 200 simo sessions.
I'm not talking about semaphores. You see the low usage of file
descriptors is just because pgbench uses very few tables.
> Keep in mind, on my fairly small intranet database server, I routinely
> have >32 connections, most coming from outside my webserver. Probably no
> more than 4 or 5 connects at a time come from there. These are all things
> like Windows boxes with ODBC running access or something similar. Many of
> the connections are idle 98% of the time, and use little or no real
> resources, even getting swapped out should the server need the spare
> memory (it doesn't :-) that machine is set to 120 max simos if I remember
> correctly.
>
> 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, and the max fsm settings tom has suggested make sense.
32 is not too low if the kernel file descriptors is not
increased. Beware that running out of the kernel file descriptors is a
serious problem for the entire system, not only for PostgreSQL.
> What wal_sync method should we make default? Or should we pick one based
> on the OS the user is running?
It's really depending on the OS or kernel version. I saw open_sync is
best for certain version of Linux kernel, while fdatasync is good for
another version of kernel. I'm not sure, but it could be possible that
the file system type might affect the wal_sync choice.
--
Tatsuo Ishii
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing the default configuration
@ 2003-02-12 16:36 Robert Treat <xzilla@users.sourceforge.net>
parent: Tatsuo Ishii <t-ishii@sra.co.jp>
0 siblings, 4 replies; 12+ messages in thread
From: Robert Treat @ 2003-02-12 16:36 UTC (permalink / raw)
To: Tatsuo Ishii <t-ishii@sra.co.jp>; +Cc: scott.marlowe@ihs.com; pgsql-hackers@postgresql.org
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, and the max fsm settings tom has suggested make sense.
>
> 32 is not too low if the kernel file descriptors is not
> increased. Beware that running out of the kernel file descriptors is a
> serious problem for the entire system, not only for PostgreSQL.
>
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 connections, so we ran with 128 max. I think this is a safe number
on most servers these days (running linux as least) though out of the
box I might be more inclined to limit it to 64. If you do hit a file
descriptor problem, *you are hosed*.
Robert Treat
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing the default configuration
@ 2003-02-12 16:43 Greg Copeland <greg@CopelandConsulting.Net>
parent: Robert Treat <xzilla@users.sourceforge.net>
3 siblings, 0 replies; 12+ messages in thread
From: Greg Copeland @ 2003-02-12 16:43 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: Tatsuo Ishii <t-ishii@sra.co.jp>; scott.marlowe@ihs.com, PostgresSQL Hackers Mailing List <pgsql-hackers@postgresql.org>
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 sort_mem or 4 or 8 meg hits the knee for most
> > > folks, and the max fsm settings tom has suggested make sense.
> >
> > 32 is not too low if the kernel file descriptors is not
> > increased. Beware that running out of the kernel file descriptors is a
> > serious problem for the entire system, not only for PostgreSQL.
> >
>
> 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 connections, so we ran with 128 max. I think this is a safe number
> on most servers these days (running linux as least) though out of the
> box I might be more inclined to limit it to 64. If you do hit a file
> descriptor problem, *you are hosed*.
>
That does seem like a more reasonable upper limit. I would rather see
people have to knowingly increase the limit rather than bump into system
upper limits and start scratching their heads trying to figure out what
the heck is going on.
--
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing the default configuration
@ 2003-02-12 16:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robert Treat <xzilla@users.sourceforge.net>
3 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2003-02-12 16:48 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: Tatsuo Ishii <t-ishii@sra.co.jp>; scott.marlowe@ihs.com; pgsql-hackers@postgresql.org
Robert Treat <xzilla@users.sourceforge.net> 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 problem hit somewhere around
> 150 connections, so we ran with 128 max. I think this is a safe number
> on most servers these days (running linux as least) though out of the
> box I might be more inclined to limit it to 64. If you do hit a file
> descriptor problem, *you are hosed*.
If you want to run lots of connections, it's a real good idea to set
max_files_per_process to positively ensure Postgres won't overflow
your kernel file table, ie, max_connections * max_files_per_process
should be less than the file table size.
Before about 7.2, we didn't have max_files_per_process, and would
naively believe whatever sysconf() told us was an okay number of files
to open. Unfortunately, way too many kernels promise more than they
can deliver ...
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing the default configuration
@ 2003-02-12 18:30 scott.marlowe <scott.marlowe@ihs.com>
parent: Robert Treat <xzilla@users.sourceforge.net>
3 siblings, 0 replies; 12+ messages in thread
From: scott.marlowe @ 2003-02-12 18:30 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
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 the user experience the
faster searches first, then get to test with other locales and see if
performance is good enough, than to start out slow and wonder why they
need to change their initdb settings to get decent performance on a where
clause with like in it.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing the default configuration
@ 2003-02-13 01:43 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Robert Treat <xzilla@users.sourceforge.net>
3 siblings, 0 replies; 12+ messages in thread
From: Christopher Kings-Lynne @ 2003-02-13 01:43 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; Tatsuo Ishii <t-ishii@sra.co.jp>; +Cc: scott.marlowe@ihs.com; pgsql-hackers@postgresql.org
> 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 connections, so we ran with 128 max. I think this is a safe number
> on most servers these days (running linux as least) though out of the
> box I might be more inclined to limit it to 64. If you do hit a file
> descriptor problem, *you are hosed*.
Just yesterday I managed to hose my new Postgres installation during a
particular benchmarking run. Postgres did restart itself nicely though. I
have no idea why that particular run caused problems when all other runs
with identical settings didn't. I checked the log and saw file descriptor
probs. I was doing 128 connections with 128 max connetions. This was the
log:
> 2003-02-12 04:16:15 LOG: PGSTAT: cannot open temp stats file
> /usr/local/pgsql/data/global/pgstat.tmp.41388: Too many open files in
> system
> 2003-02-12 04:16:15 LOG: PGSTAT: cannot open temp stats file
> /usr/local/pgsql/data/global/pgstat.tmp.41388: Too many open files in
> system
> 2003-02-12 04:16:39 PANIC: could not open transaction-commit log
> directory
> (/usr/local/pgsql/data/pg_clog): Too many open files in system
> 2003-02-12 04:16:39 LOG: statement: SET autocommit TO 'on';VACUUM
> ANALYZE
> 2003-02-12 04:16:39 LOG: PGSTAT: cannot open temp stats file
> /usr/local/pgsql/data/global/pgstat.tmp.41388: Too many open files in
> system
This was the MIB:
> kern.maxfiles: 1064
> kern.maxfilesperproc: 957
This was the solution:
> sysctl -w kern.maxfiles=65536
> sysctl -w kern.maxfilesperproc=8192
>
> .. and then stick
>
> kern.maxfiles=65536
> kern.maxfilesperproc=8192
>
> in /etc/sysctl.conf so its set during a reboot.
Which just goes to highlight the importance of rigorously testing a
production installation...
Chris
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [HACKERS] Changing the default configuration
@ 2003-02-14 04:00 Josh Berkus <josh@agliodbs.com>
parent: Tatsuo Ishii <t-ishii@sra.co.jp>
1 sibling, 0 replies; 12+ messages in thread
From: Josh Berkus @ 2003-02-14 04:00 UTC (permalink / raw)
To: Tatsuo Ishii <t-ishii@sra.co.jp>; tgl@sss.pgh.pa.us; +Cc: justin@postgresql.org; merlin.moncure@rcsonline.com; pgsql-hackers@postgresql.org; pgsql-advocacy@postgresql.org
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 branches table. But still people continue to complian...
Hey, pg_bench is a good start on a Postgres performance tester, and it's much,
much better than what there was before you came along ... which was nothing.
Thank you again for contributing it.
pg_bench is, however, only a start on a performance tester, and we'd need to
build it up before we could use it as the basis of a PG tuner.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [HACKERS] Changing the default configuration
@ 2003-04-29 04:41 Matthew T. O'Connor <matthew@zeut.net>
parent: Tatsuo Ishii <t-ishii@sra.co.jp>
1 sibling, 0 replies; 12+ messages in thread
From: Matthew T. O'Connor @ 2003-04-29 04:41 UTC (permalink / raw)
To: Tatsuo Ishii <t-ishii@sra.co.jp>; +Cc: tgl@sss.pgh.pa.us; justin@postgresql.org; josh@agliodbs.com; merlin.moncure@rcsonline.com; pgsql-hackers@postgresql.org; pgsql-advocacy@postgresql.org
On Tue, 2003-02-11 at 20:10, Tatsuo Ishii wrote:
> 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 branches table. But still people continue to complian...
What exactly does the -N option do? I see no mention of it in the
README.pgbench, which might be part of reason people "continue to
complain".
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2003-04-29 04:41 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-12 01:10 Re: [HACKERS] Changing the default configuration Tatsuo Ishii <t-ishii@sra.co.jp>
2003-02-14 04:00 ` Josh Berkus <josh@agliodbs.com>
2003-04-29 04:41 ` Matthew T. O'Connor <matthew@zeut.net>
2003-02-12 01:10 Re: Changing the default configuration Tatsuo Ishii <t-ishii@sra.co.jp>
2003-02-12 01:23 ` scott.marlowe <scott.marlowe@ihs.com>
2003-02-12 02:00 ` Tatsuo Ishii <t-ishii@sra.co.jp>
2003-02-12 16:36 ` Robert Treat <xzilla@users.sourceforge.net>
2003-02-12 16:43 ` Greg Copeland <greg@CopelandConsulting.Net>
2003-02-12 16:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-12 18:30 ` scott.marlowe <scott.marlowe@ihs.com>
2003-02-13 01:43 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-12 01:10 Re: Changing the default configuration Tatsuo Ishii <t-ishii@sra.co.jp>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox