pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
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: 23 Sep 2004 13:25:25 -0400
Message-ID: <87d60cc2yi.fsf@stark.xeocode.com> (raw)
In-Reply-To: <200409231653.i8NGrUaX017580@ms-smtp-02.nyroc.rr.com>
References: <200409231653.i8NGrUaX017580@ms-smtp-02.nyroc.rr.com>


"Jason Coene" <jcoene@gotfrag.com> writes:

> All of our "postgres" processes end up in the "semwai" state - seemingly
> waiting on other queries to complete.  If the system isn't taxed in CPU or
> disk, I have a good feeling that this may be the cause.  

Well, it's possible contention of some sort is an issue but it's not clear
that it's planning related contention.

> We're running on SELECT's, and the number of locks on our "high traffic"
> tables grows to the hundreds.  

Where are you seeing this? What information do you have about these locks?

> I've looked at PREPARE, but apparently it only lasts per-session - that's
> worthless in our case (web based service, one connection per data-requiring
> connection).

Well the connection time in postgres is pretty quick. But a lot of other
things, including prepared queries but also including other factors are a lot
more effective if you have long-lived sessions.

I would strongly recommend you consider some sort of persistent database
connection for your application. Most web based services run queries from a
single source base where all the queries are written in-house. In that
situation you can ensure that one request never leaves the session in an
unusual state (like setting guc variables strangely, or leaving a transaction
open, or whatever).

That saves you the reconnect time, which as I said is actually small, but
could still be contributing to your problem. I think it also makes the buffer
cache more effective as well. And It also means you can prepare all your
queries and reuse them on subsequent requests.

The nice thing about web based services is that while each page only executes
each query once, you tend to get the same pages over and over thousands of
times. So if they prepare their queries the first time around they can reuse
those prepared queries thousands of times.

Using a text cache of the query string on the server side is just a
work-around for failing to do that on the client side. It's much more
efficient and more flexible to do it on the client-side.

-- 
greg




view thread (72+ messages)  latest in thread

Message-ID: <87d60cc2yi.fsf@stark.xeocode.com>
Permalink:  ../87d60cc2yi.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/87d60cc2yi.fsf@stark.xeocode.com

 · 

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: gsstark@mit.edu, jcoene@gotfrag.com, mr_pink_is_the_only_pro@yahoo.com, scottakirkwood@gmail.com
  Subject: Re: Caching of Queries
  In-Reply-To: <87d60cc2yi.fsf@stark.xeocode.com>

* 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