pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Xeon vs Opteron - second revision - tests and questions
4+ messages / 3 participants
[nested] [flat]
* Re: Xeon vs Opteron - second revision - tests and questions
@ 2005-11-11 18:39 Marcin Giedz <marcin.giedz@eulerhermes.pl>
2005-11-11 19:11 ` Re: Xeon vs Opteron - second revision - tests and questions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Marcin Giedz @ 2005-11-11 18:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Jim C. Nasby <jnasby@pervasive.com>; +Cc: pgsql-admin
Hello,
First of all: many thanks Tom for pointed me out what I made wrong and
Jim for 'spikes'.
I made some new tests:
available at http://85.128.68.44
What have changed since last time:
- database engine - now it's 8.1.0
- number of clients - "-c 20 "
- number of transactions - "-t 1000"
Really seems that you have made EXCELLENT work. Performance increased
about 25% in 8.1.0.
I also made some changes related with bg_write and wal (according to Jim):
bg_writer_all_percent = 0.433
bg_writer_all_maxpages = 15
wal_buffers = 8192
However I still can see 'spikey' performance but not as much as before
changes. What can I do more to eliminate or smooth these spikes? I
tried to increase bg_writer_all_xxx parameters but.... spikes still
exists ;).
Regards,
Marcin
>This is pretty nearly an ideal example of how NOT to use pg_bench :-(
>
>In the first place, don't use a number of clients (-c) much exceeding
>the scale factor (-s). If you do, then almost all you are measuring is
>the effects of update contention. There are only scale-factor rows in
>the branches table, and every pgbench transaction wants to update one of
>the branches rows, so with -c 100 and -s 20 there are on average going
>to be 5 transactions simultaneously trying to modify the same branches
>row. 4 of them are going to be waiting. Does that really correspond
>to a real-world situation that you want to optimize?
>
>In the second place, you need a run length considerably longer than
>-t 100 to avoid getting swamped by noise of startup/shutdown overhead.
>I usually use at least -t 1000 if I want repeatable numbers.
>
>BTW, PG 8.1 will probably do better than 8.0 on multi-CPU hardware.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Xeon vs Opteron - second revision - tests and questions
2005-11-11 18:39 Re: Xeon vs Opteron - second revision - tests and questions Marcin Giedz <marcin.giedz@eulerhermes.pl>
@ 2005-11-11 19:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-11 19:26 ` Re: Xeon vs Opteron - second revision - tests and questions Marcin Giedz <marcin.giedz@eulerhermes.pl>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2005-11-11 19:11 UTC (permalink / raw)
To: Marcin Giedz <marcin.giedz@eulerhermes.pl>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; pgsql-admin
Marcin Giedz <marcin.giedz@eulerhermes.pl> writes:
> However I still can see 'spikey' performance but not as much as before
> changes. What can I do more to eliminate or smooth these spikes?
The spikes are certainly caused by checkpoints. You can fool with the
checkpoint timing via checkpoint_segments and checkpoint_timeout.
Usually people put them as far apart as they can stand (the constraint
on this is mainly how long you'd like to wait for recovery after a
system crash, and how much disk space you can spare for WAL logs).
Increasing the bg_writer parameters can be expected to dampen the spikes
but not eliminate them completely.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Xeon vs Opteron - second revision - tests and questions
2005-11-11 18:39 Re: Xeon vs Opteron - second revision - tests and questions Marcin Giedz <marcin.giedz@eulerhermes.pl>
2005-11-11 19:11 ` Re: Xeon vs Opteron - second revision - tests and questions Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-11-11 19:26 ` Marcin Giedz <marcin.giedz@eulerhermes.pl>
2005-11-14 17:50 ` Re: Xeon vs Opteron - second revision - tests and questions Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 4+ messages in thread
From: Marcin Giedz @ 2005-11-11 19:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; pgsql-admin
Tom Lane napisaĆ(a):
>Marcin Giedz <marcin.giedz@eulerhermes.pl> writes:
>
>
>>However I still can see 'spikey' performance but not as much as before
>>changes. What can I do more to eliminate or smooth these spikes?
>>
>>
>
>The spikes are certainly caused by checkpoints. You can fool with the
>checkpoint timing via checkpoint_segments and checkpoint_timeout.
>
>
I didn't mention before but I changed checkpoint_segments to 300 but no
checkpoint_time. Disk space doesn't matter at all ... time to
recovery.... shouldn't be very long (I don't have much experiences with
this as I do online backup every day). Can anyone please tell me what
values of these two parametres are reasonable?
Regards,
Marcin
>Usually people put them as far apart as they can stand (the constraint
>on this is mainly how long you'd like to wait for recovery after a
>system crash, and how much disk space you can spare for WAL logs).
>Increasing the bg_writer parameters can be expected to dampen the spikes
>but not eliminate them completely.
>
> regards, tom lane
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Xeon vs Opteron - second revision - tests and questions
2005-11-11 18:39 Re: Xeon vs Opteron - second revision - tests and questions Marcin Giedz <marcin.giedz@eulerhermes.pl>
2005-11-11 19:11 ` Re: Xeon vs Opteron - second revision - tests and questions Tom Lane <tgl@sss.pgh.pa.us>
2005-11-11 19:26 ` Re: Xeon vs Opteron - second revision - tests and questions Marcin Giedz <marcin.giedz@eulerhermes.pl>
@ 2005-11-14 17:50 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jim C. Nasby @ 2005-11-14 17:50 UTC (permalink / raw)
To: Marcin Giedz <marcin.giedz@eulerhermes.pl>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On Fri, Nov 11, 2005 at 08:26:41PM +0100, Marcin Giedz wrote:
> Tom Lane napisa?(a):
>
> >Marcin Giedz <marcin.giedz@eulerhermes.pl> writes:
> >
> >
> >>However I still can see 'spikey' performance but not as much as before
> >>changes. What can I do more to eliminate or smooth these spikes?
> >>
> >>
> >
> >The spikes are certainly caused by checkpoints. You can fool with the
> >checkpoint timing via checkpoint_segments and checkpoint_timeout.
> >
> >
> I didn't mention before but I changed checkpoint_segments to 300 but no
> checkpoint_time. Disk space doesn't matter at all ... time to
> recovery.... shouldn't be very long (I don't have much experiences with
> this as I do online backup every day). Can anyone please tell me what
> values of these two parametres are reasonable?
What Tom was refering to about 'time to recovery' is recovering from an
unexpected database shutdown by replaying the WAL. If you go 5 minutes
between syncing WAL to the mainline storage, then a worst-case recovery
will take ~5 minutes (well, it could probably take longer, but it should
be a linear relationship). The longer you make checkpoint interval, the
longer you'll have to wait for the database to come back.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-11-14 17:50 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-11 18:39 Re: Xeon vs Opteron - second revision - tests and questions Marcin Giedz <marcin.giedz@eulerhermes.pl>
2005-11-11 19:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-11 19:26 ` Marcin Giedz <marcin.giedz@eulerhermes.pl>
2005-11-14 17:50 ` Jim C. Nasby <jnasby@pervasive.com>
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