Skip site navigation (1) Skip section navigation (2)

Re: Caching of Queries

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jason Coene" <jcoene(at)gotfrag(dot)com>
Cc: "'Mr Pink'" <mr_pink_is_the_only_pro(at)yahoo(dot)com>,"'Scott Kirkwood'" <scottakirkwood(at)gmail(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Caching of Queries
Date: 2004-09-23 17:35:47
Message-ID: 27750.1095960947@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Jason Coene" <jcoene(at)gotfrag(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Matt ClarkDate: 2004-09-23 17:38:32
Subject: Re: Caching of Queries
Previous:From: Greg StarkDate: 2004-09-23 17:25:25
Subject: Re: Caching of Queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group