Обсуждение: Xeon vs Opteron - tests and questions
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
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
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 >> >> >> > > >
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
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
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 > > >
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
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 > > >
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