pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: josh@agliodbs.com
Cc: Joe Conway <mail@joeconway.com>
Cc: scott.marlowe <scott.marlowe@ihs.com>
Cc: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: lutzeb@aeccom.com, pgsql-performance@postgresql.org, Neil Conway <neilc@samurai.com>
Subject: Re: Wierd context-switching issue on Xeon
Date: Mon, 19 Apr 2004 20:01:56 -0400
Message-ID: <1407.1082419316@sss.pgh.pa.us> (raw)
In-Reply-To: <200404191455.04068.josh@agliodbs.com>
References: <Pine.LNX.4.33.0404191410550.17372-100000@css120.ihs.com>
	<40843E63.7050101@joeconway.com>
	<200404191455.04068.josh@agliodbs.com>

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

view thread (77+ messages)  latest in thread

Message-ID: <1407.1082419316@sss.pgh.pa.us>
Permalink:  ../1407.1082419316@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1407.1082419316@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, josh@agliodbs.com, mail@joeconway.com, scott.marlowe@ihs.com, pgman@candle.pha.pa.us, neilc@samurai.com
  Subject: Re: Wierd context-switching issue on Xeon
  In-Reply-To: <1407.1082419316@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox