Re: High context switches occurring

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(at)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(at)sss(dot)pgh(dot)pa(dot)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(at)vantage(dot)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(at)postgresql(dot)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(at)sai(dot)msu(dot)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