|
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? Thanks Alex > Date: Sat, 19 Dec 2009 00:10:36 -0700 > Subject: Re: PL/Perl Performance Problems > From: scott.marlowe@xxxxxxxxx > To: aintokyo@xxxxxxxxxxx > CC: tgl@xxxxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx > > 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@xxxxxxxxxxx> 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: PL/Perl Performance Problems > >> From: scott.marlowe@xxxxxxxxx > >> To: aintokyo@xxxxxxxxxxx > >> CC: tgl@xxxxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx > >> > >> On Fri, Dec 18, 2009 at 11:37 PM, Alex - <aintokyo@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > >> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general Meet singles at ninemsn dating Looking for a great date? |
