Re: PL/Perl Performance Problems

Tom, Scott, Alvaro,thanks for the hints on this issue. It looks as if one of the EOD maintenance jobs which does a few extensive queries does push data out of memory leading to this behavior.
Is there a way to permanently cash some tables into memory?
ThanksAlex

> Date: Sat, 19 Dec 2009 00:10:36 -0700
> Subject: Re: [GENERAL] PL/Perl Performance Problems
> From: scott(dot)marlowe(at)gmail(dot)com
> To: aintokyo(at)hotmail(dot)com
> CC: tgl(at)sss(dot)pgh(dot)pa(dot)us; pgsql-general(at)postgresql(dot)org
>
> According to your original post, you do selects in step 1 and 2... Or
> is this a different job and I've lost the thread (happens to me plenty
> :) )
>
> 1. Selects about 20 Records from Table A (
> - loops though the list and deletes in total about 50k records in Table B
> 2. For each record form Table A it then selects Records from Table C
> - loops through these records about 50K in total
> - for each runs a query 3 Tables, 10-20M records
> - inserts a record in Table B .. about 50K
> 3. Returns some stats on the whole operation (100 records).
>
> On Sat, Dec 19, 2009 at 12:07 AM, Alex - <aintokyo(at)hotmail(dot)com> wrote:
> > On a 2nd thought... where does the cach come into play when i only do
> > inserts and no selects.
> > Alex
> >
> >> Date: Fri, 18 Dec 2009 23:45:07 -0700
> >> Subject: Re: [GENERAL] PL/Perl Performance Problems
> >> From: scott(dot)marlowe(at)gmail(dot)com
> >> To: aintokyo(at)hotmail(dot)com
> >> CC: tgl(at)sss(dot)pgh(dot)pa(dot)us; pgsql-general(at)postgresql(dot)org
> >>
> >> On Fri, Dec 18, 2009 at 11:37 PM, Alex - <aintokyo(at)hotmail(dot)com> wrote:
> >> > Hmm...
> >> > how can that be. This is happening every day, so its not a one off or
> >> > happens once in the morning then in the afternoon. There is also no
> >> > other
> >> > task running on the system, its dedicated to postgres.
> >> > Could the Autovacuum cause problems? Starting to invoke Analyze at the
> >> > beginning of the day but the keep silent till the day timestamp breaks ?
> >> > The think is that I have 4 servers setup in a similar way and all have
> >> > exactly the same problem.
> >>
> >> What cron jobs are on that machine that run at night? Note that on
> >> many OSes, maintenance crons are scheduled in a dir something like
> >> /etc/cron.daily etc... On my laptop they all run at midnight. I'm
> >> wondering if they're blowing out your cache so that you just don't
> >> have the same performance the first time you hit a particular dataset
> >> after they've run. Just a guess. You could try disabling them for a
> >> day and see what happens.
> >>
> >> --
> >> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >
> > ________________________________
> > Meet singles at ninemsn dating Looking for a great date?
>
>
>
> --
> When fascism comes to America, it will be intolerance sold as diversity.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

_________________________________________________________________
Looking for a great date? Meet singles at ninemsn dating
http://clk.atdmt.com/NMN/go/150855801/direct/01/