Jérôme,
How many concurrent connections do you have?
Because You've got only 2GB of ram this is important! Postgres process
takes some bytes in memory =) .. I don't exactly how many,
but thinking if it is about 2Mb you'll get about 1Gb of ram used only by
postgres' processes (for 512 connections)!
Don't forget about your 512Mb shared memory setting,
postgres shared libraries and the OS filesystem cache...
I hope your postgres binaries are not statically linked?
Try using connection pooling in your software, or add some RAM, it's cheap.
And I think that work_mem of 65536 is too high for your system...
On Thu, 14 Sep 2006 17:09:25 +0200
Jérôme BENOIS <benois@argia-engineering.fr> wrote:
> Hi Dave,
> Le jeudi 14 septembre 2006 à 10:02 -0500, Dave Dutcher a écrit :
> > > -----Original Message-----
> > > From: pgsql-performance-owner@postgresql.org
> > > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> > > Jérôme BENOIS
> > >
> > explain analyze select distinct
> > > INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_
> > > VALUE,CATEGORY_ID,STRING_VALUE,CATEGORYATTR_ID,NAME from (((
> > > select distinct ei_id as EIID from mpng2_ei_attribute as
> > > reqin1 where reqin1.CATEGORYATTR_ID = 0 AND reqin1.TEXT_VALUE
> > > ilike '' and ei_id in ( select distinct ei_id as EIID from
> > > mpng2_ei_attribute as reqin2 where reqin2.CATEGORYATTR_ID = 0
> > > AND reqin2.TEXT_VALUE ilike '' and ei_id in ( select distinct
> > > ei_id as EIID from mpng2_ei_attribute as reqin3 where
> > > reqin3.NAME = '' AND reqin3.STRING_VALUE = '' ) ) ) ) ) as
> > > req0 join mpng2_ei_attribute on req0.eiid =
> > > mpng2_ei_attribute.ei_id order by ei_id asc;
> >
> >
> > That is a lot of distinct's. Sorts are one thing that can really use up
> > CPU. This query is doing lots of sorts, so its not surprising the CPU usage
> > is high.
> >
> > On the subqueries you have a couple of cases where you say "... in (select
> > distinct ...)" I don’t think the distinct clause is necessary in that case.
> > I'm not a hundred percent sure, but you might want to try removing them and
> > see if the query results are the same and maybe the query will execute
> > faster.
>
> Thanks for your advice, but the load was good with previous version of
> postgres -> 7.4.6 on the same server and same datas, same application,
> same final users ...
>
> So we supect some system parameter, but which ?
>
> With vmstat -s is showing a lot of "pages swapped out", have you an
> idea ?
>
> Thanls a lot,
--
Evgeny Gridasov
Software Engineer
I-Free, Russia