X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 83ED69DC831 for ; Tue, 20 Dec 2005 02:53:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 19399-01 for ; Tue, 20 Dec 2005 02:53:38 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from brmea-mail-4.sun.com (brmea-mail-4.Sun.COM [192.18.98.36]) by postgresql.org (Postfix) with ESMTP id 3A3F79DC822 for ; Tue, 20 Dec 2005 02:53:35 -0400 (AST) Received: from fe-amer-05.sun.com ([192.18.108.179]) by brmea-mail-4.sun.com (8.12.10/8.12.9) with ESMTP id jBK6rWD7018844 for ; Mon, 19 Dec 2005 23:53:36 -0700 (MST) Received: from conversion-daemon.mail-amer.sun.com by mail-amer.sun.com (Sun Java System Messaging Server 6.2-4.02 (built Sep 9 2005)) id <0IRS00101ATYGJ00@mail-amer.sun.com> (original mail from J.K.Shah@Sun.COM) for pgsql-performance@postgresql.org; Mon, 19 Dec 2005 23:53:32 -0700 (MST) Received: from [129.150.32.102] by mail-amer.sun.com (Sun Java System Messaging Server 6.2-4.02 (built Sep 9 2005)) with ESMTPSA id <0IRS00CJ9B57G2N0@mail-amer.sun.com>; Mon, 19 Dec 2005 23:53:31 -0700 (MST) Date: Tue, 20 Dec 2005 01:44:39 -0500 From: "Jignesh K. Shah" Subject: Re: High context switches occurring In-reply-to: To: Oleg Bartunov Cc: Anjan Dave , Tom Lane , Vivek Khera , Postgresql Performance Message-id: <43A7A857.4060308@sun.com> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7BIT X-Accept-Language: en-us, en References: <4BAFBB6B9CC46F41B2AD7D9F4BBAF785098F14@vt-pe2550-001.vantage.vantage.com> User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[UNPARSEABLE_RELAY=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200512/477 X-Sequence-Number: 16298 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" 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