Обсуждение: Sanity check requested

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/

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

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

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

> 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

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

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.

I have two tables in the database which are used almost every time
someone makes use of the webpages themselves.   The first, is some sort
of database side parameter list which stores parameters from session to
session.  While the other, is a table that handles the posting activity
of all the rooms and chatters.

The first is required in all authentication with the system and when
entries are missing you are challenged by the system to prove your
identity.   This table is based on a randomized order, as in the unique
number changes pseudo randomly and this table sees a reduction in
entries every hour on the hour as to keep it's information fresh and
manageable.

The other table follows a sequential order and carries more columns of
information.   However, this table clears it's entry nightly and with
current settings will delete roughly a days traffic sitting at 50K rows
of information.

The difference is as follows:   Without making the use of vacuum every
hour the parameter table performs very well, showing no loss in service
or degradation.    Since people authenticate more then post, it is safe
to assume that it removes more rows daily then the posting table.

The posting table often drags the system down in performance when a day
has been skipped, which includes the use of VACUUM ANALYZE EXPLAIN.
This seems to be an indication that the process of a daily delete is
actually a very wise step to take, even if the information itself is not
needed for very long.

A VACUUM FULL will correct the issue, but put the site out of commission
for roughly 20 minutes as the drive crunches the information.

My question is, should the purging of rows be done more often then once
a day for both tables.   Is this why performance seems to take a hit
specifically?  As there were too many rows purged for vacuum to
accurately keep track of?

    Martin Foster
    Creator/Designer Ethereal Realms
    martin@ethereal-realms.org

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

On 18/07/2003 07:55 Martin Foster wrote:
> [snip]
> A VACUUM FULL will correct the issue, but put the site out of commission
> for roughly 20 minutes as the drive crunches the information.
>
> My question is, should the purging of rows be done more often then once
> a day for both tables.   Is this why performance seems to take a hit
> specifically?  As there were too many rows purged for vacuum to
> accurately keep track of?

ISTR that there are setting in postgresql.conf which affect how many
tables/rows vacuum can reclaim. The docs say that the default setting of
max_fsm_pages is 10000. Maybe this should be increased for your situation?

HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

On Fri, Jul 18, 2003 at 12:55:12AM -0600, Martin Foster wrote:
> The other table follows a sequential order and carries more columns of
> information.   However, this table clears it's entry nightly and with
> current settings will delete roughly a days traffic sitting at 50K rows
> of information.

> has been skipped, which includes the use of VACUUM ANALYZE EXPLAIN.
> This seems to be an indication that the process of a daily delete is
> actually a very wise step to take, even if the information itself is not
> needed for very long.
>
> A VACUUM FULL will correct the issue, but put the site out of commission
> for roughly 20 minutes as the drive crunches the information.

During your "clearing period", why not do the deletes in batches, and
VACUUM the table periodically.  That will allow you to reclaim the
space gradually, and ensure that you don't end up with a big "bald
spot".  But you probably want to increase your FSM settings.  See the
docs.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Martin Foster <martin@ethereal-realms.org> writes:
> My question is, should the purging of rows be done more often then once
> a day for both tables.   Is this why performance seems to take a hit
> specifically?

Given that the hourly purge seems to work well for you, I'd suggest
trying it on both tables.

Non-FULL vacuum is intended to be run *frequently*, say as often as
you've updated or deleted 10% to 50% of the rows in a table.  Delaying
it until you've had multiple complete turnovers of the table contents
will cost you.

> As there were too many rows purged for vacuum to
> accurately keep track of?

Only possible if you don't have the FSM parameters set high enough.
Infrequent vacuuming means you need more FSM space, btw.

            regards, tom lane

Thanks for the suggestions in the FS types- especially the Debian oriented
info. I'll start by playing with the memory allocation parameters that I
originally listed (seems like they should provide results in a way that is
unaffected by the disk IO). Then once I have them at optimal values, move on
to trying different file systems.

I assume that as I make changes that affect the disk IO performance, I'll
then need to do some testing to find new values for the IO cost for the
planner- Do you folks have some ballpark numbers to start with for this
based on your experience? I'm departing in three ways from the simple IDE
model that (I presume) the default random page cost of 4 is based on- The
disks are SCSI & RAID and the FS would be different.

At this point, I can't think of any better way to test this than simply
running my local test suite with various values and recording the wall-clock
results. Is there a different approach that might make more sense? (This
means that my results will be skewed to my environment, but I'll post them
anyway.)

I'll post results back to the list as I get to it- It might be a slow
process Since I spend about 18 hours of each day keeping the business
running, I'll have to cut back on sleep & do this in the other 10 hours. <g>

-NF


> Shridhar Daithankar wrote:
> I appreciate your approach but it almost proven that ext2 is
> not the best and fastest out there.
>
> Agreed.

> Ang Chin Han wrote:
> 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
>

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.

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/

> 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

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/

>> > 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

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

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

> ...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.

"Nick Fankhauser" <nickf@ontko.com> writes:
> I'm departing in three ways from the simple IDE
> model that (I presume) the default random page cost of 4 is based on- The
> disks are SCSI & RAID and the FS would be different.

Actually, the default 4 is based on experiments I did quite awhile back
on HPUX (with a SCSI disk) and Linux (with an IDE disk, and a different
filesystem).  I didn't see too much difference between 'em.  RAID might
alter the equation, or not.

            regards, tom lane