pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: 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