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