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

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

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

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

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

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

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

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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq