SEO

Hi there,

I see a very low performance and high context switches on our
dual itanium2 slackware box (Linux ptah 2.6.14 #1 SMP)
with 8Gb of RAM, running 8.1_STABLE. Any tips here ?

postgres@ptah:~/cvs/8.1/pgsql/contrib/pgbench$ time pgbench -s 10 -c 10 -t 3000 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 10
number of transactions per client: 3000
number of transactions actually processed: 30000/30000
tps = 163.817425 (including connections establishing)
tps = 163.830558 (excluding connections establishing)

real 3m3.374s
user 0m1.888s
sys 0m2.472s

output from vmstat 2

2 1 0 4185104 197904 3213888 0 0 0 1456 673 6852 25 1 45 29
6 0 0 4184880 197904 3213888 0 0 0 1456 673 6317 28 2 49 21
0 1 0 4184656 197904 3213888 0 0 0 1464 671 7049 25 2 42 31
3 0 0 4184432 197904 3213888 0 0 0 1436 671 7073 25 1 44 29
0 1 0 4184432 197904 3213888 0 0 0 1460 671 7014 28 1 42 29
0 1 0 4184096 197920 3213872 0 0 0 1440 670 7065 25 2 42 31
0 1 0 4183872 197920 3213872 0 0 0 1444 671 6718 26 2 44 28
0 1 0 4183648 197920 3213872 0 0 0 1468 670 6525 15 3 50 33
0 1 0 4184352 197920 3213872 0 0 0 1584 676 6476 12 2 50 36
0 1 0 4193232 197920 3213872 0 0 0 1424 671 5848 12 1 50 37
0 0 0 4195536 197920 3213872 0 0 0 20 509 104 0 0 99 1
0 0 0 4195536 197920 3213872 0 0 0 1680 573 25 0 0 99 1
0 0 0 4195536 197920 3213872 0 0 0 0 504 22 0 0 100

processor : 1
vendor : GenuineIntel
arch : IA-64
family : Itanium 2
model : 2
revision : 2
archrev : 0
features : branchlong
cpu number : 0
cpu regs : 4
cpu MHz : 1600.010490
itc MHz : 1600.010490
BogoMIPS : 2392.06
siblings : 1



On Mon, 19 Dec 2005, Anjan Dave wrote:


> Re-ran it 3 times on each host -
>
> Sun:
> -bash-3.00$ time pgbench -s 10 -c 10 -t 3000 pgbench
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 1
> number of clients: 10
> number of transactions per client: 3000
> number of transactions actually processed: 30000/30000
> tps = 827.810778 (including connections establishing)
> tps = 828.410801 (excluding connections establishing)
> real 0m36.579s
> user 0m1.222s
> sys 0m3.422s
>
> Intel:
> -bash-3.00$ time pgbench -s 10 -c 10 -t 3000 pgbench
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 1
> number of clients: 10
> number of transactions per client: 3000
> number of transactions actually processed: 30000/30000
> tps = 597.067503 (including connections establishing)
> tps = 597.606169 (excluding connections establishing)
> real 0m50.380s
> user 0m2.621s
> sys 0m7.818s
>
> Thanks,
> Anjan
>
>
> -----Original Message-----
> From: Anjan Dave
> Sent: Wed 12/7/2005 10:54 AM
> To: Tom Lane
> Cc: Vivek Khera; Postgresql Performance
> Subject: Re: [PERFORM] High context switches occurring
>
>
>
> Thanks for your inputs, Tom. I was going after high concurrent clients,
> but should have read this carefully -
>
> -s scaling_factor
> this should be used with -i (initialize) option.
> number of tuples generated will be multiple of the
> scaling factor. For example, -s 100 will imply 10M
> (10,000,000) tuples in the accounts table.
> default is 1. NOTE: scaling factor should be at least
> as large as the largest number of clients you intend
> to test; else you'll mostly be measuring update
> contention.
>
> I'll rerun the tests.
>
> Thanks,
> Anjan
>
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, December 06, 2005 6:45 PM
> To: Anjan Dave
> Cc: Vivek Khera; Postgresql Performance
> Subject: Re: [PERFORM] High context switches occurring
>
> "Anjan Dave" <adave@vantage.com> writes:
> > -bash-3.00$ time pgbench -c 1000 -t 30 pgbench
> > starting vacuum...end.
> > transaction type: TPC-B (sort of)
> > scaling factor: 1
> > number of clients: 1000
> > number of transactions per client: 30
> > number of transactions actually processed: 30000/30000
> > tps = 45.871234 (including connections establishing)
> > tps = 46.092629 (excluding connections establishing)
>
> I can hardly think of a worse way to run pgbench :-(. These numbers are
> about meaningless, for two reasons:
>
> 1. You don't want number of clients (-c) much higher than scaling factor
> (-s in the initialization step). The number of rows in the "branches"
> table will equal -s, and since every transaction updates one
> randomly-chosen "branches" row, you will be measuring mostly row-update
> contention overhead if there's more concurrent transactions than there
> are rows. In the case -s 1, which is what you've got here, there is no
> actual concurrency at all --- all the transactions stack up on the
> single branches row.
>
> 2. Running a small number of transactions per client means that
> startup/shutdown transients overwhelm the steady-state data. You should
> probably run at least a thousand transactions per client if you want
> repeatable numbers.
>
> Try something like "-s 10 -c 10 -t 3000" to get numbers reflecting test
> conditions more like what the TPC council had in mind when they designed
> this benchmark. I tend to repeat such a test 3 times to see if the
> numbers are repeatable, and quote the middle TPS number as long as
> they're not too far apart.
>
> regards, tom lane
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
> ---------------------------(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
>

Regards,
Oleg
__________________________________________________ ___________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

It basically says pg_xlog is the bottleneck and move it to the disk with
the best response time that you can afford. :-)
Increasing checkpoint_segments doesn't seem to help much. Playing with
wal_sync_method might change the behavior.

For proof .. On Solaris, the /tmp is like a RAM Drive...Of course DO NOT
TRY ON PRODUCTION.

-bash-3.00$ time pgbench -s 10 -c 10 -t 3000 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 3000
number of transactions actually processed: 30000/30000
tps = 356.578050 (including connections establishing)
tps = 356.733043 (excluding connections establishing)

real 1m24.396s
user 0m2.550s
sys 0m3.404s
-bash-3.00$ mv pg_xlog /tmp
-bash-3.00$ ln -s /tmp/pg_xlog pg_xlog
-bash-3.00$ time pgbench -s 10 -c 10 -t 3000 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 3000
number of transactions actually processed: 30000/30000
tps = 2413.661323 (including connections establishing)
tps = 2420.754581 (excluding connections establishing)

real 0m12.617s
user 0m2.229s
sys 0m2.950s
-bash-3.00$ rm pg_xlog
-bash-3.00$ mv /tmp/pg_xlog pg_xlog
-bash-3.00$ time pgbench -s 10 -c 10 -t 3000 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 3000
number of transactions actually processed: 30000/30000
tps = 350.227682 (including connections establishing)
tps = 350.382825 (excluding connections establishing)

real 1m27.595s
user 0m2.537s
sys 0m3.386s
-bash-3.00$


Regards,
Jignesh


Oleg Bartunov wrote:

> Hi there,
>
> I see a very low performance and high context switches on our
> dual itanium2 slackware box (Linux ptah 2.6.14 #1 SMP)
> with 8Gb of RAM, running 8.1_STABLE. Any tips here ?
>
> postgres@ptah:~/cvs/8.1/pgsql/contrib/pgbench$ time pgbench -s 10 -c
> 10 -t 3000 pgbench
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 1
> number of clients: 10
> number of transactions per client: 3000
> number of transactions actually processed: 30000/30000
> tps = 163.817425 (including connections establishing)
> tps = 163.830558 (excluding connections establishing)
>
> real 3m3.374s
> user 0m1.888s
> sys 0m2.472s
>
> output from vmstat 2
>
> 2 1 0 4185104 197904 3213888 0 0 0 1456 673 6852
> 25 1 45 29
> 6 0 0 4184880 197904 3213888 0 0 0 1456 673 6317
> 28 2 49 21
> 0 1 0 4184656 197904 3213888 0 0 0 1464 671 7049
> 25 2 42 31
> 3 0 0 4184432 197904 3213888 0 0 0 1436 671 7073
> 25 1 44 29
> 0 1 0 4184432 197904 3213888 0 0 0 1460 671 7014
> 28 1 42 29
> 0 1 0 4184096 197920 3213872 0 0 0 1440 670 7065
> 25 2 42 31
> 0 1 0 4183872 197920 3213872 0 0 0 1444 671 6718
> 26 2 44 28
> 0 1 0 4183648 197920 3213872 0 0 0 1468 670 6525
> 15 3 50 33
> 0 1 0 4184352 197920 3213872 0 0 0 1584 676 6476
> 12 2 50 36
> 0 1 0 4193232 197920 3213872 0 0 0 1424 671 5848
> 12 1 50 37
> 0 0 0 4195536 197920 3213872 0 0 0 20 509 104
> 0 0 99 1
> 0 0 0 4195536 197920 3213872 0 0 0 1680 573 25
> 0 0 99 1
> 0 0 0 4195536 197920 3213872 0 0 0 0 504 22
> 0 0 100
>
> processor : 1
> vendor : GenuineIntel
> arch : IA-64
> family : Itanium 2
> model : 2
> revision : 2
> archrev : 0
> features : branchlong
> cpu number : 0
> cpu regs : 4
> cpu MHz : 1600.010490
> itc MHz : 1600.010490
> BogoMIPS : 2392.06
> siblings : 1
>
>
>
> On Mon, 19 Dec 2005, Anjan Dave wrote:
>
>
>> Re-ran it 3 times on each host -
>>
>> Sun:
>> -bash-3.00$ time pgbench -s 10 -c 10 -t 3000 pgbench
>> starting vacuum...end.
>> transaction type: TPC-B (sort of)
>> scaling factor: 1
>> number of clients: 10
>> number of transactions per client: 3000
>> number of transactions actually processed: 30000/30000
>> tps = 827.810778 (including connections establishing)
>> tps = 828.410801 (excluding connections establishing)
>> real 0m36.579s
>> user 0m1.222s
>> sys 0m3.422s
>>
>> Intel:
>> -bash-3.00$ time pgbench -s 10 -c 10 -t 3000 pgbench
>> starting vacuum...end.
>> transaction type: TPC-B (sort of)
>> scaling factor: 1
>> number of clients: 10
>> number of transactions per client: 3000
>> number of transactions actually processed: 30000/30000
>> tps = 597.067503 (including connections establishing)
>> tps = 597.606169 (excluding connections establishing)
>> real 0m50.380s
>> user 0m2.621s
>> sys 0m7.818s
>>
>> Thanks,
>> Anjan
>>
>>
>> -----Original Message-----
>> From: Anjan Dave
>> Sent: Wed 12/7/2005 10:54 AM
>> To: Tom Lane
>> Cc: Vivek Khera; Postgresql Performance
>> Subject: Re: [PERFORM] High context switches occurring
>>
>>
>>
>> Thanks for your inputs, Tom. I was going after high concurrent
>> clients,
>> but should have read this carefully -
>>
>> -s scaling_factor
>> this should be used with -i (initialize) option.
>> number of tuples generated will be multiple of the
>> scaling factor. For example, -s 100 will imply 10M
>> (10,000,000) tuples in the accounts table.
>> default is 1. NOTE: scaling factor should be at
>> least
>> as large as the largest number of clients you intend
>> to test; else you'll mostly be measuring update
>> contention.
>>
>> I'll rerun the tests.
>>
>> Thanks,
>> Anjan
>>
>>
>> -----Original Message-----
>> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> Sent: Tuesday, December 06, 2005 6:45 PM
>> To: Anjan Dave
>> Cc: Vivek Khera; Postgresql Performance
>> Subject: Re: [PERFORM] High context switches occurring
>>
>> "Anjan Dave" <adave@vantage.com> writes:
>> > -bash-3.00$ time pgbench -c 1000 -t 30 pgbench
>> > starting vacuum...end.
>> > transaction type: TPC-B (sort of)
>> > scaling factor: 1
>> > number of clients: 1000
>> > number of transactions per client: 30
>> > number of transactions actually processed: 30000/30000
>> > tps = 45.871234 (including connections establishing)
>> > tps = 46.092629 (excluding connections establishing)
>>
>> I can hardly think of a worse way to run pgbench :-(. These
>> numbers are
>> about meaningless, for two reasons:
>>
>> 1. You don't want number of clients (-c) much higher than scaling
>> factor
>> (-s in the initialization step). The number of rows in the
>> "branches"
>> table will equal -s, and since every transaction updates one
>> randomly-chosen "branches" row, you will be measuring mostly
>> row-update
>> contention overhead if there's more concurrent transactions than
>> there
>> are rows. In the case -s 1, which is what you've got here, there
>> is no
>> actual concurrency at all --- all the transactions stack up on the
>> single branches row.
>>
>> 2. Running a small number of transactions per client means that
>> startup/shutdown transients overwhelm the steady-state data. You
>> should
>> probably run at least a thousand transactions per client if you want
>> repeatable numbers.
>>
>> Try something like "-s 10 -c 10 -t 3000" to get numbers
>> reflecting test
>> conditions more like what the TPC council had in mind when they
>> designed
>> this benchmark. I tend to repeat such a test 3 times to see if the
>> numbers are repeatable, and quote the middle TPS number as long as
>> they're not too far apart.
>>
>> regards, tom lane
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
>>
>>
>> ---------------------------(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
>>
>
> Regards,
> Oleg
> __________________________________________________ ___________
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings


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

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

Oleg Bartunov <oleg@sai.msu.su> writes:
> I see a very low performance and high context switches on our
> dual itanium2 slackware box (Linux ptah 2.6.14 #1 SMP)
> with 8Gb of RAM, running 8.1_STABLE. Any tips here ?

> postgres@ptah:~/cvs/8.1/pgsql/contrib/pgbench$ time pgbench -s 10 -c 10 -t 3000 pgbench
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 1
> number of clients: 10

You can't expect any different with more clients than scaling factor :-(.

Note that -s is only effective when supplied with -i; it's basically
ignored during an actual test run.

regards, tom lane

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

On Tue, 20 Dec 2005, Tom Lane wrote:

> Oleg Bartunov <oleg@sai.msu.su> writes:
>> I see a very low performance and high context switches on our
>> dual itanium2 slackware box (Linux ptah 2.6.14 #1 SMP)
>> with 8Gb of RAM, running 8.1_STABLE. Any tips here ?
>
>> postgres@ptah:~/cvs/8.1/pgsql/contrib/pgbench$ time pgbench -s 10 -c 10 -t 3000 pgbench
>> starting vacuum...end.
>> transaction type: TPC-B (sort of)
>> scaling factor: 1
>> number of clients: 10
>
> You can't expect any different with more clients than scaling factor :-(.

Argh I copy'n pasted from previous message.

I still wondering with very poor performance of my server. Moving
pgdata to RAID6 helped - about 600 tps. Then, I moved pg_xlog to separate
disk and got strange error messages

postgres@ptah:~$ time pgbench -c 10 -t 3000 pgbench
starting vacuum...end.
Client 0 aborted in state 8: ERROR: integer out of range
Client 7 aborted in state 8: ERROR: integer out of range

dropdb,createdb helped, but performance is about 160 tps.

Low-end AMD64 with SATA disks gives me ~400 tps in spite of
disks on itanium2 faster ( 80MB/sec ) than on AMD64 (60MB/sec).


Regards,
Oleg
__________________________________________________ ___________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

Oleg Bartunov <oleg@sai.msu.su> writes:
> I still wondering with very poor performance of my server. Moving
> pgdata to RAID6 helped - about 600 tps. Then, I moved pg_xlog to separate
> disk and got strange error messages

> postgres@ptah:~$ time pgbench -c 10 -t 3000 pgbench
> starting vacuum...end.
> Client 0 aborted in state 8: ERROR: integer out of range
> Client 7 aborted in state 8: ERROR: integer out of range

I've seen that too, after re-using an existing pgbench database enough
times. I think that the way the test script is written, the adjustments
to the branch balances are always in the same direction, and so
eventually the fields overflow. It's irrelevant to performance though.

regards, tom lane

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