Thread: Xeon vs Opteron - tests and questions

Xeon vs Opteron - tests and questions

From
Marcin Giedz
Date:
Hello.


I made some test comparing postgresql 8.0.4 based on two servers.

Here is a link:
http://85.128.68.44/


From my point of view it seems like:
    - with opteron I found that it works very FAST periodicaly, why??? Maybe I
did something wrong or.....
    - xeon seems to work almost all time on the same level but has much less
performance than opteron. Is it normal?

Did anyone has similar tests/results?

BR,
Marcin

Re: Xeon vs Opteron - tests and questions

From
"Jim C. Nasby"
Date:
That link isn't working for me, but if you're seeing 'spikey'
performance I'd bet that you need to change the bg_writer settings to
make it more aggressive, especially if you're seeing performance drops
every 5 minutes (assuming default checkpoint settings).

You might also want to up wal_buffers and maybe checkpoint_segments.

On Wed, Nov 09, 2005 at 03:26:51PM +0100, Marcin Giedz wrote:
> Hello.
>
>
> I made some test comparing postgresql 8.0.4 based on two servers.
>
> Here is a link:
> http://85.128.68.44/
>
>
> From my point of view it seems like:
>     - with opteron I found that it works very FAST periodicaly, why??? Maybe I
> did something wrong or.....
>     - xeon seems to work almost all time on the same level but has much less
> performance than opteron. Is it normal?
>
> Did anyone has similar tests/results?
>
> BR,
> Marcin
>
> ---------------------------(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
>

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

Re: Xeon vs Opteron - tests and questions

From
Marcin Giedz
Date:
Jim C. Nasby napisał(a):

>That link isn't working for me,
>

Uppssss sorry... now it should work OK.

>but if you're seeing 'spikey'
>performance I'd bet that you need to change the bg_writer settings to
>make it more aggressive, especially if you're seeing performance drops
>Every 5 minutes (assuming default checkpoint settings).
>
>
>You might also want to up wal_buffers and maybe checkpoint_segments.
>
>

OK many thanks, I will check.


>On Wed, Nov 09, 2005 at 03:26:51PM +0100, Marcin Giedz wrote:
>
>
>>Hello.
>>
>>
>>I made some test comparing postgresql 8.0.4 based on two servers.
>>
>>Here is a link:
>>http://85.128.68.44/
>>
>>
>From my point of view it seems like:
>>    - with opteron I found that it works very FAST periodicaly, why??? Maybe I
>>did something wrong or.....
>>    - xeon seems to work almost all time on the same level but has much less
>>performance than opteron. Is it normal?
>>
>>Did anyone has similar tests/results?
>>
>>BR,
>>Marcin
>>
>>---------------------------(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
>>
>>
>>
>
>
>


Re: Xeon vs Opteron - tests and questions

From
Tom Lane
Date:
Marcin Giedz <marcin.giedz@eulerhermes.pl> writes:
> I made some test comparing postgresql 8.0.4 based on two servers.
> Here is a link:
> http://85.128.68.44/

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

Re: Xeon vs Opteron - tests and questions

From
"Jim C. Nasby"
Date:
The first graph certainly looks like a checkpointing issue.

On Wed, Nov 09, 2005 at 07:49:23PM +0100, Marcin Giedz wrote:
> Jim C. Nasby napisa??(a):
>
> >That link isn't working for me,
> >
>
> Uppssss sorry... now it should work OK.
>
> >but if you're seeing 'spikey'
> >performance I'd bet that you need to change the bg_writer settings to
> >make it more aggressive, especially if you're seeing performance drops
> >Every 5 minutes (assuming default checkpoint settings).
> >
> >
> >You might also want to up wal_buffers and maybe checkpoint_segments.
> >
> >
>
> OK many thanks, I will check.
>
>
> >On Wed, Nov 09, 2005 at 03:26:51PM +0100, Marcin Giedz wrote:
> >
> >
> >>Hello.
> >>
> >>
> >>I made some test comparing postgresql 8.0.4 based on two servers.
> >>
> >>Here is a link:
> >>http://85.128.68.44/
> >>
> >>
> >>From my point of view it seems like:
> >>    - with opteron I found that it works very FAST periodicaly, why???
> >>    Maybe I did something wrong or.....
> >>    - xeon seems to work almost all time on the same level but has much
> >>    less performance than opteron. Is it normal?
> >>
> >>Did anyone has similar tests/results?
> >>
> >>BR,
> >>Marcin
> >>
> >>---------------------------(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
> >>
> >>
> >>
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

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

Re: Xeon vs Opteron - second revision - tests and questions

From
Marcin Giedz
Date:
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
>
>
>


Re: Xeon vs Opteron - second revision - tests and questions

From
Tom Lane
Date:
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

Re: Xeon vs Opteron - second revision - tests and questions

From
Marcin Giedz
Date:
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
>
>
>


Re: Xeon vs Opteron - second revision - tests and questions

From
"Jim C. Nasby"
Date:
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