From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
Cc: 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 15:06:52 -0400
Message-ID: <28414.1095966412@sss.pgh.pa.us> (raw)
In-Reply-To: <200409231156.31450.josh@agliodbs.com>
References: <200409231722.i8NHMZaX014707@ms-smtp-02.nyroc.rr.com>
<27750.1095960947@sss.pgh.pa.us>
<200409231156.31450.josh@agliodbs.com>
Josh Berkus <josh@agliodbs.com> writes:
>> I think you are probably looking at the same problem previously reported
>> by Josh Berkus among others.
> That would be interesting. Previously we've only demonstrated the
> problem on long-running queries, but I suppose it could also affect
> massive concurrent query access.
Well, the test cases we used were designed to get the system into a
tight loop of grabbing and releasing shared buffers --- a long-running
index scan is certainly one of the best ways to do that, but there are
others.
I hadn't focused before on the point that Jason is launching a new
connection for every query. In that scenario I think the bulk of the
cycles are going to go into loading the per-backend catalog caches with
the system catalog rows that are needed to parse and plan the query.
The catalog fetches to get those rows are effectively mini-queries
with preset indexscan plans, so it's not hard to believe that they'd be
hitting the BufMgrLock nearly as hard as a tight indexscan loop. Once
all the pages needed are cached in shared buffers, there's no I/O delays
to break the loop, and so you could indeed get into the context swap
storm regime we saw before.
I concur with the thought that using persistent connections might go a
long way towards alleviating his problem.
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, josh@agliodbs.com, jcoene@gotfrag.com, mr_pink_is_the_only_pro@yahoo.com, scottakirkwood@gmail.com
Subject: Re: Caching of Queries
In-Reply-To: <28414.1095966412@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