X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5389AD1BAF5 for ; Mon, 19 Apr 2004 21:02:50 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 64666-06 for ; Mon, 19 Apr 2004 21:02:51 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id D9C26D1D189 for ; Mon, 19 Apr 2004 21:02:47 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i3K01udf001408; Mon, 19 Apr 2004 20:01:57 -0400 (EDT) To: josh@agliodbs.com Cc: Joe Conway , "scott.marlowe" , Bruce Momjian , lutzeb@aeccom.com, pgsql-performance@postgresql.org, Neil Conway Subject: Re: Wierd context-switching issue on Xeon In-reply-to: <200404191455.04068.josh@agliodbs.com> References: <40843E63.7050101@joeconway.com> <200404191455.04068.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Mon, 19 Apr 2004 14:55:04 -0700" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <1376.1082419088.0@sss.pgh.pa.us> Date: Mon, 19 Apr 2004 20:01:56 -0400 Message-ID: <1407.1082419316@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/281 X-Sequence-Number: 6581 ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1376.1082419088.1@sss.pgh.pa.us> Here is a test case. To set up, run the "test_setup.sql" script once; then launch two copies of the "test_run.sql" script. (For those of you with more than two CPUs, see whether you need one per CPU to make trouble, or whether two test_runs are enough.) Check that you get a nestloops-with-index-scans plan shown by the EXPLAIN in test_run. In isolation, test_run.sql should do essentially no syscalls at all once it's past the initial ramp-up. On a machine that's functioning per expectations, multiple copies of test_run show a relatively low rate of semop() calls --- a few per second, at most --- and maybe a delaying select() here and there. What I actually see on Josh's client's machine is a context swap storm: "vmstat 1" shows CS rates around 170K/sec. strace'ing the backends shows a corresponding rate of semop() syscalls, with a few delaying select()s sprinkled in. top(1) shows system CPU percent of 25-30 and idle CPU percent of 16-20. I haven't bothered to check how long the test_run query takes, but if it ends while you're still examining the behavior, just start it again. Note the test case assumes you've got shared_buffers set to at least 1000; with smaller values, you may get some I/O syscalls, which will probably skew the results. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1376.1082419088.2@sss.pgh.pa.us> Content-Description: test_setup.sql drop table test_data; create table test_data(f1 int); insert into test_data values (random() * 100); insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; create index test_index on test_data(f1); vacuum verbose analyze test_data; checkpoint; ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1376.1082419088.3@sss.pgh.pa.us> Content-Description: test_run.sql -- force nestloop indexscan plan set enable_seqscan to 0; set enable_mergejoin to 0; set enable_hashjoin to 0; explain select count(*) from test_data a, test_data b, test_data c where a.f1 = b.f1 and b.f1 = c.f1; select count(*) from test_data a, test_data b, test_data c where a.f1 = b.f1 and b.f1 = c.f1; ------- =_aaaaaaaaaa0--