pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSanity check requested
16+ messages / 9 participants
[nested] [flat]
* Sanity check requested
@ 2003-07-14 17:51 Nick Fankhauser <nickf@ontko.com>
0 siblings, 1 reply; 16+ messages in thread
From: Nick Fankhauser @ 2003-07-14 17:51 UTC (permalink / raw)
To: pgsql-performance
Hi folks-
For some time, we've been running Postgres with the default configuration &
getting adequate performance, but the time has come to tune a bit, so I've
been lurking on this list & gathering notes. Now I'm about ready to make a
change & would appreciate it if a few more experienced folks could comment
on whether I appear to be heading in the right direction-
Here's what I'm planning:
Increase SHMMAX and SHMALL in my kernel to 134217728 (128MB)
Increase shared_buffers to 8192 (64MB)
Increase sort_mem to 16384 (16MB)
Increase effective_cache_size to 65536 (1/2 GB)
Here's the environment:
The Hardware is a dual-processor Athlon 1.2 Ghz box with 1 GB of RAM and the
DB on SCSI RAID drives.
The server runs only PostgreSQL
The database size is about 8GB, with the largest table 2.5 GB, and the two
most commonly queried tables at 1 GB each.
The two most commonly queried tables are usually queried based on a
non-unique indexed varchar field typically 20 chars long. The query is a
"like" on people's names with trailing %, so this often gets pushed to seq
scan or returns several thousand records. (As when someone searches on
'Jones%'.
Records from the largest table are always accessed via unique index in
groups of 20 or less.
The OS is Debian Linux kernel 2.4.x (recompiled custom kernel for dual
processor support)
The PostgreSQL version is 7.3.2
We typically have about 30 interactive users on the DB, but they're using a
shared connection pool of 16. Our main problem appears to be when one of the
users fires up a large query and creates a log-jam with resources.
My reasoning is that I'll increase shared_buffers based on anecdotal
recommendations I've seen on this list to 64MB. I'll boost the OS SHMMAX to
twice that value to allow adequate room for other shared memory needs, thus
reserving 128MB. Of the remaining memory, 256MB goes to 16 connections *
16MB sort space, if I leave about 128 MB for headroom, then 1/2 GB should be
left available for the effective cache size.
Any thoughts? Is this a sane plan? Are there other parameters I should
consider changing first?
Thanks!
-Nick
---------------------------------------------------------------------
Nick Fankhauser
nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/
---------------------------------------------------------------------
Nick Fankhauser
nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-15 05:42 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
parent: Nick Fankhauser <nickf@ontko.com>
0 siblings, 2 replies; 16+ messages in thread
From: Shridhar Daithankar @ 2003-07-15 05:42 UTC (permalink / raw)
To: pgsql-performance
On 14 Jul 2003 at 12:51, Nick Fankhauser wrote:
> Any thoughts? Is this a sane plan? Are there other parameters I should
> consider changing first?
Well, everything seems to be in order and nothing much to suggest I guess. But
still..
1. 30 users does not seem to be much of a oevrhead. If possible try doing away
with connection pooling. Buta test benchmark run is highly recommended.
2. While increasing sort memory, try 4/8/16 in that order. That way you will
get a better picture of load behaviour. Though whatever you put appears
reasonable, having more data always help.
3. I don't know how this affects on SCSI drives, but what file system you are
using? Can you try diferent ones? Like reiserfs/ext3 and XFS? See what fits
your bill.
4. OK, this is too much but linux kernel 2.6 is in test and has vastly improved
IO scheduler. May be you should look at it if you are up to experimentation.
HTH
Bye
Shridhar
--
You're dead, Jim. -- McCoy, "The Tholian Web", stardate unknown
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-17 15:41 Nick Fankhauser <nickf@ontko.com>
parent: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1 sibling, 2 replies; 16+ messages in thread
From: Nick Fankhauser @ 2003-07-17 15:41 UTC (permalink / raw)
To: shridhar_daithankar@persistent.co.in; pgsql-performance
Shridhar-
I appreciate your thoughts- I'll be running some before & after tests on
this using one of our development/hot-swap boxes, so I'll report the results
back to the list.
A few more thoughts/questions:
> 1. 30 users does not seem to be much of a oevrhead. If possible
> try doing away with connection pooling.
The application needs to scale up gracefully. We actually have about 200
users that could decide to log on at the same time- 30 is just a typical
load. We'd also prefer to have 20,000 subscribers so we can start making a
living with this business <g>.
> 2. While increasing sort memory, try 4/8/16 in that order. That
> way you will get a better picture of load behaviour. Though whatever you
put appears
> reasonable, having more data always help.
I'll try that approach while testing. Is it the case that the sort memory is
allocated for each connection and becomes unavailable to other processes
while the connection exists? If so, since I'm using a connection pool, I
should be able to control total usage precisely. Without a connection pool,
I could start starving the rest of the system for resources if the number of
users spiked unexpectedly. Correct?
> 3. I don't know how this affects on SCSI drives, but what file
> system you are using? Can you try diferent ones?
> 4. OK, this is too much but linux kernel 2.6 is in test and has
> vastly improved IO...
I'm using ext2. For now, I'll leave this and the OS version alone. If I
change too many variables, I won't be able to discern which one is causing a
change. Although I understand that there's an element of art to tuning, I'm
enough of a neophyte that I don't have a "feeling" for the tuning parameters
yet and hence I have to take a scientific approach of just tweaking a few
variables in an otherwise controlled and unchanged environment. If I can't
reach my goals with the simple approach, I'll consider some of the more
radical ideas.
Again, thanks for the ideas- I'll feed the results back after I've done some
tests
-Nick
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-17 15:57 Josh Berkus <josh@agliodbs.com>
parent: Nick Fankhauser <nickf@ontko.com>
1 sibling, 1 reply; 16+ messages in thread
From: Josh Berkus @ 2003-07-17 15:57 UTC (permalink / raw)
To: nickf@ontko.com; shridhar_daithankar@persistent.co.in; pgsql-performance
Nick,
> I'll try that approach while testing. Is it the case that the sort memory
> is allocated for each connection and becomes unavailable to other processes
> while the connection exists? If so, since I'm using a connection pool, I
> should be able to control total usage precisely. Without a connection pool,
> I could start starving the rest of the system for resources if the number
> of users spiked unexpectedly. Correct?
Wrong, actually. Sort memory is allocated *per sort*, not per connnection or
per query. So a single complex query could easily use 4xsort_mem if it has
several merge joins ... and a pooled connection could use many times sort_mem
depending on activity. Thus connection pooling does not help you with
sort_mem usage at all, unless your pooling mechanism can control the rate at
which queries are fed to the planner.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-17 16:11 Nick Fankhauser <nickf@ontko.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 16+ messages in thread
From: Nick Fankhauser @ 2003-07-17 16:11 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; pgsql-performance
> Wrong, actually. Sort memory is allocated *per sort*, not per
connnection or
> per query. So a single complex query could easily use 4xsort_mem if it
has
> several merge joins ...
Thanks for the correction- it sounds like this is one where usage can't be
precisely controlled in a dynamic user environment & I just need to get a
feel for what works under a load that approximates my production system.
-Nick
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-17 16:40 Joe Conway <mail@joeconway.com>
parent: Nick Fankhauser <nickf@ontko.com>
0 siblings, 0 replies; 16+ messages in thread
From: Joe Conway @ 2003-07-17 16:40 UTC (permalink / raw)
To: nickf@ontko.com; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
Nick Fankhauser wrote:
> Thanks for the correction- it sounds like this is one where usage
> can't be precisely controlled in a dynamic user environment & I just
> need to get a feel for what works under a load that approximates my
> production system.
>
I think the most important point here is that if you set sort_mem too
high, and you have a lot of simultaneous sorts, you can drive the server
into swapping, which obviously is a very bad thing. You want it set as
high as possible, but not so high given your usage patterns that you
wind up swapping.
Joe
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-18 06:35 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
parent: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1 sibling, 1 reply; 16+ messages in thread
From: Shridhar Daithankar @ 2003-07-18 06:35 UTC (permalink / raw)
To: pgsql-performance
On 17 Jul 2003 at 10:41, Nick Fankhauser wrote:
> I'm using ext2. For now, I'll leave this and the OS version alone. If I
I appreciate your approach but it almost proven that ext2 is not the best and
fastest out there.
IMO, you can safely change that to reiserfs or XFS. Or course, testing is
always recommended.
HTH
Bye
Shridhar
--
Newton's Little-Known Seventh Law: A bird in the hand is safer than one
overhead.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-18 07:08 Ang Chin Han <angch@bytecraft.com.my>
parent: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
0 siblings, 1 reply; 16+ messages in thread
From: Ang Chin Han @ 2003-07-18 07:08 UTC (permalink / raw)
To: pgsql-performance
Shridhar Daithankar wrote:
> On 17 Jul 2003 at 10:41, Nick Fankhauser wrote:
>
>>I'm using ext2. For now, I'll leave this and the OS version alone. If I
>
>
> I appreciate your approach but it almost proven that ext2 is not the best and
> fastest out there.
Agreed.
> IMO, you can safely change that to reiserfs or XFS. Or course, testing is
> always recommended.
We've been using ext3fs for our production systems. (Red Hat Advanced
Server 2.1)
And since your (Nick) system is based on Debian, I have done some rough
testing on Debian sarge (testing) (with custom 2.4.20) with ext3fs,
reiserfs and jfs. Can't get XFS going easily on Debian, though.
I used a single partition mkfs'd with ext3fs, reiserfs and jfs one after
the other on an IDE disk. Ran pgbench and osdb-x0.15-0 on it.
jfs's has been underperforming for me. Somehow the CPU usage is higher
than the other two. As for ext3fs and reiserfs, I can't detect any
significant difference. So if you're in a hurry, it'll be easier to
convert your ext2 to ext3 (using tune2fs) and use that. Otherwise, it'd
be nice if you could do your own testing, and post it to the list.
--
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386
GNU/Linux
2:30pm up 204 days, 5:35, 5 users, load average: 5.50, 5.18, 5.13
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-18 15:40 scott.marlowe <scott.marlowe@ihs.com>
parent: Ang Chin Han <angch@bytecraft.com.my>
0 siblings, 0 replies; 16+ messages in thread
From: scott.marlowe @ 2003-07-18 15:40 UTC (permalink / raw)
To: Ang Chin Han <angch@bytecraft.com.my>; +Cc: pgsql-performance
On Fri, 18 Jul 2003, Ang Chin Han wrote:
> Shridhar Daithankar wrote:
> > On 17 Jul 2003 at 10:41, Nick Fankhauser wrote:
> >
> >>I'm using ext2. For now, I'll leave this and the OS version alone. If I
> >
> >
> > I appreciate your approach but it almost proven that ext2 is not the best and
> > fastest out there.
>
> Agreed.
Huh? How can journaled file systems hope to outrun a simple unjournaled
file system? There's just less overhead for ext2 so it's quicker, it's
just not as reliable.
I point you to this link from IBM:
http://www-124.ibm.com/developerworks/opensource/linuxperf/iozone/iozone.php
While ext3 is a clear loser to jfs and rfs, ext2 wins most of the contests
against both reiser and jfs. Note that xfs wasn't tested here. But in
general, ext2 is quite fast nowadays.
>
> > IMO, you can safely change that to reiserfs or XFS. Or course, testing is
> > always recommended.
>
> We've been using ext3fs for our production systems. (Red Hat Advanced
> Server 2.1)
>
> And since your (Nick) system is based on Debian, I have done some rough
> testing on Debian sarge (testing) (with custom 2.4.20) with ext3fs,
> reiserfs and jfs. Can't get XFS going easily on Debian, though.
>
> I used a single partition mkfs'd with ext3fs, reiserfs and jfs one after
> the other on an IDE disk. Ran pgbench and osdb-x0.15-0 on it.
>
> jfs's has been underperforming for me. Somehow the CPU usage is higher
> than the other two. As for ext3fs and reiserfs, I can't detect any
> significant difference. So if you're in a hurry, it'll be easier to
> convert your ext2 to ext3 (using tune2fs) and use that. Otherwise, it'd
> be nice if you could do your own testing, and post it to the list.
I would like to see some tests on how they behave on top of large fast
RAID arrays, like a 10 disk RAID5 or something. It's likely that on a
single IDE drive the most limiting factor is the bandwidth of the drive,
whereas on a large array, the limiting factor would likely be the file
system code.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-18 16:00 Vincent van Leeuwen <pgsql.spam@vinz.nl>
parent: Nick Fankhauser <nickf@ontko.com>
1 sibling, 1 reply; 16+ messages in thread
From: Vincent van Leeuwen @ 2003-07-18 16:00 UTC (permalink / raw)
To: pgsql-performance
On 2003-07-17 10:41:35 -0500, Nick Fankhauser wrote:
> I'm using ext2. For now, I'll leave this and the OS version alone. If I
>
I'd upgrade to a journaling filesystem as soon as possible for reliability.
Testing in our own environment has shown that PostgreSQL performs best on ext3
(yes, better than XFS, JFS or ReiserFS) with a linux 2.4.21 kernel. Be sure to
mount noatime and to create the ext3 partition with the correct stripe size of
your RAID array using the '-R stride=foo' option (see man mke2fs).
Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-18 16:20 Oliver Scheit <oliver.scheit@REALGARANT.DE>
0 siblings, 1 reply; 16+ messages in thread
From: Oliver Scheit @ 2003-07-18 16:20 UTC (permalink / raw)
To: pgsql-performance
> Be sure to mount noatime
I did "chattr -R +A /var/lib/pgsql/data"
that should do the trick as well or am I wrong?
regards,
Oli
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-18 16:28 Vincent van Leeuwen <pgsql.spam@vinz.nl>
parent: Oliver Scheit <oliver.scheit@REALGARANT.DE>
0 siblings, 0 replies; 16+ messages in thread
From: Vincent van Leeuwen @ 2003-07-18 16:28 UTC (permalink / raw)
To: pgsql-performance
On 2003-07-18 18:20:55 +0200, Oliver Scheit wrote:
> > Be sure to mount noatime
>
> I did "chattr -R +A /var/lib/pgsql/data"
> that should do the trick as well or am I wrong?
>
According to the man page it gives the same effect. There are a few things you
should consider though:
- new files won't be created with the same options (I think), so you'll have
to run this command as a daily cronjob or something to that effect
- chattr is probably more filesystem-specific than a noatime mount, although
this isn't a problem on ext[23] ofcourse
Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-18 16:42 Oliver Scheit <oliver.scheit@REALGARANT.DE>
0 siblings, 0 replies; 16+ messages in thread
From: Oliver Scheit @ 2003-07-18 16:42 UTC (permalink / raw)
To: pgsql-performance
>> > Be sure to mount noatime
>>
>> I did "chattr -R +A /var/lib/pgsql/data"
>> that should do the trick as well or am I wrong?
>>
>
> According to the man page it gives the same effect.
> There are a few things you should consider though:
> - new files won't be created with the same options (I think),
> so you'll have to run this command as a daily cronjob or
> something to that effect
This would be a really interesting point to know.
I will look into this.
I think the advantage of "chattr" is that the last access time
is still available for the rest of the filesystem.
(Of course you could have your own filesystem just for the
database stuff, in this case the advantage would be obsolete)
regards,
Oli
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-18 19:01 Nick Fankhauser <nickf@ontko.com>
parent: Vincent van Leeuwen <pgsql.spam@vinz.nl>
0 siblings, 2 replies; 16+ messages in thread
From: Nick Fankhauser @ 2003-07-18 19:01 UTC (permalink / raw)
To: pgsql-performance; +Cc: angch@bytecraft.com.my; Vincent van Leeuwen <pgsql.spam@vinz.nl>
I'm confused:
Ang Chin Han wrote:
> We've been using ext3fs for our production systems. (Red Hat Advanced
> Server 2.1)
Vincent van Leeuwen wrote:
> I'd upgrade to a journaling filesystem as soon as possible for
> reliability.
...About one year ago I considered moving to a journaling file system, but
opted not to because it seems like that's what WAL does for us already. How
does putting a journaling file system under it add more reliability?
I also guessed that a journaling file system would add overhead because now
a write to the WAL file could itself be deferred and logged elsewhere.
...So now I'm really puzzled because folks are weighing in with solid
anecdotal evidence saying that I'll get both better reliability and
performance. Can someone explain what I'm missing about the concept?
-A puzzled Nick
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-18 19:07 Josh Berkus <josh@agliodbs.com>
parent: Nick Fankhauser <nickf@ontko.com>
1 sibling, 0 replies; 16+ messages in thread
From: Josh Berkus @ 2003-07-18 19:07 UTC (permalink / raw)
To: nickf@ontko.com; pgsql-performance; +Cc: angch@bytecraft.com.my; Vincent van Leeuwen <pgsql.spam@vinz.nl>
Nick,
> ...About one year ago I considered moving to a journaling file system, but
> opted not to because it seems like that's what WAL does for us already. How
> does putting a journaling file system under it add more reliability?
It lets you restart your server quickly after an unexpected power-out. Ext2
is notoriously bad about this.
Also, WAL cannot necessarily recover properly if the underlying filesystem is
corrupted.
> I also guessed that a journaling file system would add overhead because now
> a write to the WAL file could itself be deferred and logged elsewhere.
You are correct.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Sanity check requested
@ 2003-07-18 19:12 Rod Taylor <rbt@rbt.ca>
parent: Nick Fankhauser <nickf@ontko.com>
1 sibling, 0 replies; 16+ messages in thread
From: Rod Taylor @ 2003-07-18 19:12 UTC (permalink / raw)
To: nickf@ontko.com; +Cc: pgsql-performance; angch@bytecraft.com.my, Vincent van Leeuwen <pgsql.spam@vinz.nl>
> ...About one year ago I considered moving to a journaling file system, but
> opted not to because it seems like that's what WAL does for us already. How
> does putting a journaling file system under it add more reliability?
WAL only works if the WAL files are actually written to disk and can be
read off it again. Ext2 has a number of deficiencies which can cause
problems with this basic operation (inode corruptions, etc). Journaling
does not directly help.
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2003-07-18 19:12 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-14 17:51 Sanity check requested Nick Fankhauser <nickf@ontko.com>
2003-07-15 05:42 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-17 15:41 ` Nick Fankhauser <nickf@ontko.com>
2003-07-17 15:57 ` Josh Berkus <josh@agliodbs.com>
2003-07-17 16:11 ` Nick Fankhauser <nickf@ontko.com>
2003-07-17 16:40 ` Joe Conway <mail@joeconway.com>
2003-07-18 16:00 ` Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-18 19:01 ` Nick Fankhauser <nickf@ontko.com>
2003-07-18 19:07 ` Josh Berkus <josh@agliodbs.com>
2003-07-18 19:12 ` Rod Taylor <rbt@rbt.ca>
2003-07-18 06:35 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-18 07:08 ` Ang Chin Han <angch@bytecraft.com.my>
2003-07-18 15:40 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-18 16:20 Re: Sanity check requested Oliver Scheit <oliver.scheit@REALGARANT.DE>
2003-07-18 16:28 ` Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-18 16:42 Re: Sanity check requested Oliver Scheit <oliver.scheit@REALGARANT.DE>
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