pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: FreeBSD page size
13+ messages / 5 participants
[nested] [flat]
* Re: FreeBSD page size
@ 2003-09-03 19:16 Vivek Khera <khera@kcilink.com>
0 siblings, 3 replies; 13+ messages in thread
From: Vivek Khera @ 2003-09-03 19:16 UTC (permalink / raw)
To: pgsql-performance
Ok... simple tests have completed. Here are some numbers.
FreeBSD 4.8
PG 7.4b2
4GB Ram
Dual Xeon 2.4GHz processors
14 U320 SCSI disks attached to Dell PERC3/DC RAID controller in RAID 5
config with 32k stripe size
Dump file:
-rw-r--r-- 1 vivek wheel 1646633745 Aug 28 11:01 19-Aug-2003.dump
When restored (after deleting one index that took up ~1Gb -- turned
out it was redundant to another multi-column index):
% df -k /u/d02
Filesystem 1K-blocks Used Avail Capacity Mounted on
/dev/amrd1s1e 226408360 18067260 190228432 9% /u/d02
postgresql.conf alterations from standard:
shared_buffers = 60000
sort_mem = 8192
vacuum_mem=131702
max_fsm_pages=1000000
effective_cache_size=25600
random_page-cost = 2
restore time: 14777 seconds
vacuum analyze time: 30 minutes
select count(*) from user_list where owner_id=315; 50388.64 ms
the restore complained often about checkpoints occurring every few
seconds:
Sep 2 11:57:14 d02 postgres[49721]: [5-1] LOG: checkpoints are occurring too frequently (15 seconds apart)
Sep 2 11:57:14 d02 postgres[49721]: [5-2] HINT: Consider increasing CHECKPOINT_SEGMENTS.
The HINT threw me off since I had to set checkpoint_segments in
postgresql.conf, where as CHECKPOINT_SEGMENTS implied to me a
compile-time constant.
Anyhow, so I deleted the PG data directory, and made these two
changes:
checkpoint_segments=50
sort_mem = 131702
This *really* improved the time for the restore:
restore time: 11594 seconds
then I reset the checkpoint_segments and sort_mem back to old
values...
vacuum analyze time is still 30 minutes
select count(*) from user_list where owner_id=315; 51363.98 ms
so the select appears a bit slower but it is hard to say why. the
system is otherwise idle as it is not in production yet.
Then I took the suggestion to update PG's page size to 16k and did the
same increase on sort_mem and checkpoint_segments as above. I also
halved the shared_buffers and max_fsm_pages (probably should have
halved the effective_cache_size too...)
restore time: 11322 seconds
vacuum analyze time: 27 minutes
select count(*) from user_list where owner_id=315; 48267.66 ms
Granted, given this simple test it is hard to say whether the 16k
blocks will make an improvement under live load, but I'm gonna give it
a shot. The 16k block size shows me roughly 2-6% improvement on these
tests.
So throw in my vote for 16k blocks on FreeBSD (and annotate the docs
to tell which parameters need to be halved to account for it).
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FreeBSD page size
@ 2003-09-03 19:35 Sean Chittenden <sean@chittenden.org>
parent: Vivek Khera <khera@kcilink.com>
2 siblings, 1 reply; 13+ messages in thread
From: Sean Chittenden @ 2003-09-03 19:35 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance
> Ok... simple tests have completed. Here are some numbers.
>
> FreeBSD 4.8
> PG 7.4b2
> 4GB Ram
> Dual Xeon 2.4GHz processors
> 14 U320 SCSI disks attached to Dell PERC3/DC RAID controller in RAID 5
> config with 32k stripe size
[snip]
> Then I took the suggestion to update PG's page size to 16k and did the
> same increase on sort_mem and checkpoint_segments as above. I also
> halved the shared_buffers and max_fsm_pages (probably should have
> halved the effective_cache_size too...)
>
> restore time: 11322 seconds
> vacuum analyze time: 27 minutes
> select count(*) from user_list where owner_id=315; 48267.66 ms
>
>
> Granted, given this simple test it is hard to say whether the 16k
> blocks will make an improvement under live load, but I'm gonna give it
> a shot. The 16k block size shows me roughly 2-6% improvement on these
> tests.
>
> So throw in my vote for 16k blocks on FreeBSD (and annotate the docs
> to tell which parameters need to be halved to account for it).
I haven't had a chance to run any tests yet (ELIFE), but there was a
suggestion that 32K blocks was a better performer than 16K blocks
(!!??!!??). I'm not sure why this is and my only guess is that it
relies more heavily on the disk cache to ease IO. Since you have the
hardware setup, Vivek, would it be possible for you to run a test with
32K blocks?
I've started writing a threaded benchmarking program called pg_crush
that I hope to post here in a few days that'll time connection startup
times, INSERTs, DELETEs, UPDATEs, and both sequential scans as well as
index scans for random and sequentially ordered tuples. It's similar
to pgbench, except it generates its own data, uses pthreads (chears on
KSE!), and returns more fine grained timing information for the
various activities.
-sc
--
Sean Chittenden
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FreeBSD page size
@ 2003-09-03 19:41 Vivek Khera <khera@kcilink.com>
parent: Sean Chittenden <sean@chittenden.org>
0 siblings, 0 replies; 13+ messages in thread
From: Vivek Khera @ 2003-09-03 19:41 UTC (permalink / raw)
To: pgsql-performance
>>>>> "SC" == Sean Chittenden <sean@chittenden.org> writes:
SC> hardware setup, Vivek, would it be possible for you to run a test with
SC> 32K blocks?
Will do. What's another 4 hours... ;-)
I guess I'll halve the buffer size parameters again...
SC> I've started writing a threaded benchmarking program called pg_crush
SC> that I hope to post here in a few days that'll time connection startup
Ok. Please post it when it is ready. I've decided to wait until 7.4
is final before going to production so I've got this very expensive
very fast box doing not much of anything for a little while...
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FreeBSD page size
@ 2003-09-03 20:32 Marc G. Fournier <scrappy@hub.org>
parent: Vivek Khera <khera@kcilink.com>
2 siblings, 1 reply; 13+ messages in thread
From: Marc G. Fournier @ 2003-09-03 20:32 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance
Just curious, but Bruce(?) mentioned that apparently a 32k block size was
found to show a 15% improvement ... care to run one more test? :)
On Wed, 3 Sep 2003, Vivek Khera wrote:
> Ok... simple tests have completed. Here are some numbers.
>
> FreeBSD 4.8
> PG 7.4b2
> 4GB Ram
> Dual Xeon 2.4GHz processors
> 14 U320 SCSI disks attached to Dell PERC3/DC RAID controller in RAID 5
> config with 32k stripe size
>
> Dump file:
> -rw-r--r-- 1 vivek wheel 1646633745 Aug 28 11:01 19-Aug-2003.dump
>
> When restored (after deleting one index that took up ~1Gb -- turned
> out it was redundant to another multi-column index):
>
> % df -k /u/d02
> Filesystem 1K-blocks Used Avail Capacity Mounted on
> /dev/amrd1s1e 226408360 18067260 190228432 9% /u/d02
>
>
>
> postgresql.conf alterations from standard:
> shared_buffers = 60000
> sort_mem = 8192
> vacuum_mem=131702
> max_fsm_pages=1000000
> effective_cache_size=25600
> random_page-cost = 2
>
>
> restore time: 14777 seconds
> vacuum analyze time: 30 minutes
> select count(*) from user_list where owner_id=315; 50388.64 ms
>
>
> the restore complained often about checkpoints occurring every few
> seconds:
>
> Sep 2 11:57:14 d02 postgres[49721]: [5-1] LOG: checkpoints are occurring too frequently (15 seconds apart)
> Sep 2 11:57:14 d02 postgres[49721]: [5-2] HINT: Consider increasing CHECKPOINT_SEGMENTS.
>
> The HINT threw me off since I had to set checkpoint_segments in
> postgresql.conf, where as CHECKPOINT_SEGMENTS implied to me a
> compile-time constant.
>
> Anyhow, so I deleted the PG data directory, and made these two
> changes:
>
> checkpoint_segments=50
> sort_mem = 131702
>
> This *really* improved the time for the restore:
>
> restore time: 11594 seconds
>
> then I reset the checkpoint_segments and sort_mem back to old
> values...
>
> vacuum analyze time is still 30 minutes
> select count(*) from user_list where owner_id=315; 51363.98 ms
>
> so the select appears a bit slower but it is hard to say why. the
> system is otherwise idle as it is not in production yet.
>
>
> Then I took the suggestion to update PG's page size to 16k and did the
> same increase on sort_mem and checkpoint_segments as above. I also
> halved the shared_buffers and max_fsm_pages (probably should have
> halved the effective_cache_size too...)
>
> restore time: 11322 seconds
> vacuum analyze time: 27 minutes
> select count(*) from user_list where owner_id=315; 48267.66 ms
>
>
> Granted, given this simple test it is hard to say whether the 16k
> blocks will make an improvement under live load, but I'm gonna give it
> a shot. The 16k block size shows me roughly 2-6% improvement on these
> tests.
>
> So throw in my vote for 16k blocks on FreeBSD (and annotate the docs
> to tell which parameters need to be halved to account for it).
>
>
> --
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Vivek Khera, Ph.D. Khera Communications, Inc.
> Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
> AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FreeBSD page size
@ 2003-09-03 23:29 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Vivek Khera <khera@kcilink.com>
2 siblings, 2 replies; 13+ messages in thread
From: Bruce Momjian @ 2003-09-03 23:29 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance
Vivek Khera wrote:
> the restore complained often about checkpoints occurring every few
> seconds:
>
> Sep 2 11:57:14 d02 postgres[49721]: [5-1] LOG: checkpoints are occurring too frequently (15 seconds apart)
> Sep 2 11:57:14 d02 postgres[49721]: [5-2] HINT: Consider increasing CHECKPOINT_SEGMENTS.
>
> The HINT threw me off since I had to set checkpoint_segments in
> postgresql.conf, where as CHECKPOINT_SEGMENTS implied to me a
> compile-time constant.
Woo hoo, my warning worked. Great.
I uppercased it because config parameters are uppercased in the
documentation. Do we mention config parameters in any other error
messages? Should it be lowercased?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FreeBSD page size
@ 2003-09-03 23:49 Rod Taylor <rbt@rbt.ca>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: Rod Taylor @ 2003-09-03 23:49 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Vivek Khera <khera@kcilink.com>; pgsql-performance
> I uppercased it because config parameters are uppercased in the
> documentation. Do we mention config parameters in any other error
> messages? Should it be lowercased?
How about changing the hint?
Consider increasing CHECKPOINT_SEGMENTS in your postgresql.conf
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FreeBSD page size
@ 2003-09-04 00:04 Marc G. Fournier <scrappy@hub.org>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 13+ messages in thread
From: Marc G. Fournier @ 2003-09-04 00:04 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Vivek Khera <khera@kcilink.com>; pgsql-performance
On Wed, 3 Sep 2003, Bruce Momjian wrote:
> Vivek Khera wrote:
> > the restore complained often about checkpoints occurring every few
> > seconds:
> >
> > Sep 2 11:57:14 d02 postgres[49721]: [5-1] LOG: checkpoints are occurring too frequently (15 seconds apart)
> > Sep 2 11:57:14 d02 postgres[49721]: [5-2] HINT: Consider increasing CHECKPOINT_SEGMENTS.
> >
> > The HINT threw me off since I had to set checkpoint_segments in
> > postgresql.conf, where as CHECKPOINT_SEGMENTS implied to me a
> > compile-time constant.
>
> Woo hoo, my warning worked. Great.
>
> I uppercased it because config parameters are uppercased in the
> documentation. Do we mention config parameters in any other error
> messages? Should it be lowercased?
k, to me upper case denotes a compiler #define, so I would have been
confused ... I'd go with lower case and single quotes around it to denote
its a variable to be changed ...
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FreeBSD page size
@ 2003-09-04 00:36 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Marc G. Fournier <scrappy@hub.org>
0 siblings, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 2003-09-04 00:36 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@hub.org>; +Cc: Vivek Khera <khera@kcilink.com>; pgsql-performance
Marc G. Fournier wrote:
> On Wed, 3 Sep 2003, Bruce Momjian wrote:
>
> > Vivek Khera wrote:
> > > the restore complained often about checkpoints occurring every few
> > > seconds:
> > >
> > > Sep 2 11:57:14 d02 postgres[49721]: [5-1] LOG: checkpoints are occurring too frequently (15 seconds apart)
> > > Sep 2 11:57:14 d02 postgres[49721]: [5-2] HINT: Consider increasing CHECKPOINT_SEGMENTS.
> > >
> > > The HINT threw me off since I had to set checkpoint_segments in
> > > postgresql.conf, where as CHECKPOINT_SEGMENTS implied to me a
> > > compile-time constant.
> >
> > Woo hoo, my warning worked. Great.
> >
> > I uppercased it because config parameters are uppercased in the
> > documentation. Do we mention config parameters in any other error
> > messages? Should it be lowercased?
>
> k, to me upper case denotes a compiler #define, so I would have been
> confused ... I'd go with lower case and single quotes around it to denote
> its a variable to be changed ...
Done.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FreeBSD page size
@ 2003-09-04 16:09 Vivek Khera <khera@kcilink.com>
parent: Marc G. Fournier <scrappy@hub.org>
0 siblings, 1 reply; 13+ messages in thread
From: Vivek Khera @ 2003-09-04 16:09 UTC (permalink / raw)
To: pgsql-performance
>>>>> "MGF" == Marc G Fournier <scrappy@hub.org> writes:
MGF> Just curious, but Bruce(?) mentioned that apparently a 32k block size was
MGF> found to show a 15% improvement ... care to run one more test? :)
Well, it is hard to tell based on my quick and dirty test:
16k page size:
restore time: 11322 seconds
vacuum analyze time: 1663 seconds (27 minutes)
select count(*) from user_list where owner_id=315; 56666.64 ms
32k page size:
restore time: 11430 seconds
vacuum analyze time: 1346 seconds
select count(*) from user_list where owner_id=315; 63275.73 ms
one anomaly I note is that if I re-run the select count(*) query
above, the large the page size, the longer the query takes. In the
standard 8k page size, it was on the order of 306ms, with 16k page
size it was over 1400, and with 32k page size nearly 3000ms.
Another anomaly I note is that for the larger indexes, the relpages
doesn't scale as expected. ie, I'd expect roughly half the relpages
per index for 32k page size as for 16k page size, but this is not
always the case... some are about the same size and some are about 2/3
and some are about 1/2. The smaller indexes are often the same number
of pages (when under 20 pages).
I think I'm going to write a synthetic load generator that does a
bunch of inserts to some linked tables with several indexes, then goes
thru and pounds on it (update/select) from multiple children with
occasional vacuum's thrown in. That's the only way to get 'real'
numbers, it seems.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FreeBSD page size
@ 2003-09-04 18:17 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Vivek Khera <khera@kcilink.com>
0 siblings, 1 reply; 13+ messages in thread
From: Bruce Momjian @ 2003-09-04 18:17 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance
Vivek Khera wrote:
> >>>>> "MGF" == Marc G Fournier <scrappy@hub.org> writes:
>
> MGF> Just curious, but Bruce(?) mentioned that apparently a 32k block size was
> MGF> found to show a 15% improvement ... care to run one more test? :)
>
>
> Well, it is hard to tell based on my quick and dirty test:
The 32k number is from Tatsuo testing a few years ago.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FreeBSD page size
@ 2003-09-04 20:56 Vivek Khera <khera@kcilink.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 2 replies; 13+ messages in thread
From: Vivek Khera @ 2003-09-04 20:56 UTC (permalink / raw)
To: pgsql-performance
>>>>> "BM" == Bruce Momjian <pgman@candle.pha.pa.us> writes:
BM> The 32k number is from Tatsuo testing a few years ago.
Can you verify for me that these parameters in postgresql.conf are
based on the BLCKSZ (ie one buffer is the size of the BLCKSIZ macro):
shared_buffers
effective_cache_size
Logically it makes sense, but I want to be sure I'm adjusting my
postgresql.conf parameters accordingly when I try different block
sizes.
Thanks.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FreeBSD page size
@ 2003-09-04 22:12 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Vivek Khera <khera@kcilink.com>
1 sibling, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 2003-09-04 22:12 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance
Vivek Khera wrote:
> >>>>> "BM" == Bruce Momjian <pgman@candle.pha.pa.us> writes:
>
>
> BM> The 32k number is from Tatsuo testing a few years ago.
>
> Can you verify for me that these parameters in postgresql.conf are
> based on the BLCKSZ (ie one buffer is the size of the BLCKSIZ macro):
>
> shared_buffers
> effective_cache_size
>
> Logically it makes sense, but I want to be sure I'm adjusting my
> postgresql.conf parameters accordingly when I try different block
> sizes.
Uh, yes, I think they have to be the same because they are pages in the
shared buffer cache, not disk blocks.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FreeBSD page size
@ 2003-09-04 22:13 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Vivek Khera <khera@kcilink.com>
1 sibling, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 2003-09-04 22:13 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance
Vivek Khera wrote:
> >>>>> "BM" == Bruce Momjian <pgman@candle.pha.pa.us> writes:
>
>
> BM> The 32k number is from Tatsuo testing a few years ago.
>
> Can you verify for me that these parameters in postgresql.conf are
> based on the BLCKSZ (ie one buffer is the size of the BLCKSIZ macro):
>
> shared_buffers
> effective_cache_size
>
> Logically it makes sense, but I want to be sure I'm adjusting my
> postgresql.conf parameters accordingly when I try different block
> sizes.
Also, to check, you can use ipcs to see the shared memory sizes
allocated.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2003-09-04 22:13 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-03 19:16 Re: FreeBSD page size Vivek Khera <khera@kcilink.com>
2003-09-03 19:35 ` Sean Chittenden <sean@chittenden.org>
2003-09-03 19:41 ` Vivek Khera <khera@kcilink.com>
2003-09-03 20:32 ` Marc G. Fournier <scrappy@hub.org>
2003-09-04 16:09 ` Vivek Khera <khera@kcilink.com>
2003-09-04 18:17 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-09-04 20:56 ` Vivek Khera <khera@kcilink.com>
2003-09-04 22:12 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-09-04 22:13 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-09-03 23:29 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-09-03 23:49 ` Rod Taylor <rbt@rbt.ca>
2003-09-04 00:04 ` Marc G. Fournier <scrappy@hub.org>
2003-09-04 00:36 ` Bruce Momjian <pgman@candle.pha.pa.us>
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