From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jason Coene <jcoene@gotfrag.com>
Cc: 'Mr Pink' <mr_pink_is_the_only_pro@yahoo.com>
Cc: 'Scott Kirkwood' <scottakirkwood@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Caching of Queries
Date: Thu, 23 Sep 2004 13:35:47 -0400
Message-ID: <27750.1095960947@sss.pgh.pa.us> (raw)
In-Reply-To: <200409231722.i8NHMZaX014707@ms-smtp-02.nyroc.rr.com>
References: <200409231722.i8NHMZaX014707@ms-smtp-02.nyroc.rr.com>
"Jason Coene" <jcoene@gotfrag.com> writes:
> I'm not sure how I go about getting the stack traceback you need. Any info
> on this? Results of "ps" below. System is dual xeon 2.6, 2gb ram, hardware
> raid 10 running FreeBSD 5.2.1.
Hmm. Dual Xeon sets off alarm bells ...
I think you are probably looking at the same problem previously reported
by Josh Berkus among others. Does the rate of context swaps shown by
vmstat go through the roof when this happens? If you strace or ktrace
one of the backends, do you see lots of semop()s and little else?
Check the archives for this thread among others:
http://archives.postgresql.org/pgsql-performance/2004-04/msg00249.php
The test case you are talking about is a tight indexscan loop, which
is pretty much the same scenario as here:
http://archives.postgresql.org/pgsql-performance/2004-04/msg00280.php
The fundamental problem is heavy contention for access to a shared data
structure. We're still looking for good solutions, but in the context
of this thread it's worth pointing out that a shared query-plan cache
would itself be subject to heavy contention, and arguably would make
this sort of problem worse not better.
regards, tom lane
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, jcoene@gotfrag.com, mr_pink_is_the_only_pro@yahoo.com, scottakirkwood@gmail.com
Subject: Re: Caching of Queries
In-Reply-To: <27750.1095960947@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