Re: [GENERAL] PL/Perl Performance Problems
Thanks,I am already have started doing that. i.e. running the dummy task to get the tables loaded again and it worked fine today. regardsAlex To: ainto...@hotmail.com CC: scott.marl...@gmail.com; pgsql-general@postgresql.org Subject: Re: [GENERAL] PL/Perl Performance Problems Date: Sun, 20 Dec 2009 22:39:00 -0500 From: t...@sss.pgh.pa.us Alex - ainto...@hotmail.com writes: 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? Not as such, and if there were it probably wouldn't be an overall performance win anyway, because you'd hurt your maintenance tasks. What you might consider doing is, at the end of the EOD sequence, run some dummy queries that scan the tables you use normally, causing them to get swapped back in so the cache is already primed when people come to work in the morning. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Get more out of Hotmail Check out the latest features today http://windowslive.ninemsn.com.au/hotmail/article/878466/your-hotmail-is-about-to-get-even-better
Re: [GENERAL] 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.marl...@gmail.com To: ainto...@hotmail.com CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.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 - ainto...@hotmail.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.marl...@gmail.com To: ainto...@hotmail.com CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org On Fri, Dec 18, 2009 at 11:37 PM, Alex - ainto...@hotmail.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@postgresql.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@postgresql.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/
Re: [GENERAL] PL/Perl Performance Problems
Alex - ainto...@hotmail.com writes: 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? Not as such, and if there were it probably wouldn't be an overall performance win anyway, because you'd hurt your maintenance tasks. What you might consider doing is, at the end of the EOD sequence, run some dummy queries that scan the tables you use normally, causing them to get swapped back in so the cache is already primed when people come to work in the morning. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/Perl Performance Problems
Hi, I am experiencing some strange behavior when executing a not too complicated pl/perl function. The Function is not too complicated. It does... 1. Selects about 20 Records from Table A ( - loops though the list and deletes in total about 50k records in Table B2. 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 50K3. Returns some stats on the whole operation (100 records). I am using PL/Perl for this and everything runs pretty well. In a single execution i.e. if I only process 1 records the whole process is done within a few milliseconds. The system has 16BG of Memory, and fast disks. Now here is what I noticed. a) if I run it in the morning, processing starts very slow, but after a few thousand records it will speed up until I actually get about 100 records processed per millisecond. b) it sometime takes about 5-10k records till i really get up to speed. meaning the first few hundreds can take up to 1-2 minutes. c) if i run the same job a few hrs later (we run it twice a day) it generally runs much faster. Even though we added more data to one of the big tables it selects from. d) this however starts again the next day. ( not much data has been changed between the 2nd run of the day and the first one of the next one, but yet it will start crawling again. e) sometime the 2nd run of the day can also be slow and even though the data in the system does not change by a large margin, run times of the jobs vary by a large amount. from 17-50 minutes. Here are my questions: A) I am running the Auto vacuum. Is it possible that this for some reason slows down the system? B) Are the query planner stats re-set over night i.e. date change? This behavior is consistent. i.e. Every morning the processing is slow, afternoon generally much faster. C) Does pl/perl have some memory issues? D) If i run above job processing 50 records, but stop the process after 10K, then run it again it will run fast for the first 10K then slow down. My assumption is that the query planner keeps the stats for a particular record based on a time stamp (used in a select) and every day it will forget about that. Is there a way to let the query planner keep stats for a function permanently? Thanks for any advise. Alex _ If It Exists, You'll Find it on SEEK Australia's #1 job site http://clk.atdmt.com/NMN/go/157639755/direct/01/
Re: [GENERAL] PL/Perl Performance Problems
Alex - ainto...@hotmail.com writes: Now here is what I noticed. a) if I run it in the morning, processing starts very slow, but after a few thousand records it will speed up until I actually get about 100 records processed per millisecond. b) it sometime takes about 5-10k records till i really get up to speed. meaning the first few hundreds can take up to 1-2 minutes. c) if i run the same job a few hrs later (we run it twice a day) it generally runs much faster. Even though we added more data to one of the big tables it selects from. d) this however starts again the next day. ( not much data has been changed between the 2nd run of the day and the first one of the next one, but yet it will start crawling again. What this sounds like is it's fast when most of the data has been swapped in to kernel disk cache, and slow when the data actually has to be read from disk. Probably the reason it's slow in the morning is there are some unrelated tasks that run overnight and cause memory to get filled with all their data instead of the PG tables. You could check this theory by watching with vmstat or similar tool to see how much actual disk I/O is happening in the slow and fast cases. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl Performance Problems
I actually looked at that too, but there is no swapping going on. The system also has 16GB memory allocated to postgres and during the processing there is not other process really active. To: ainto...@hotmail.com CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] PL/Perl Performance Problems Date: Fri, 18 Dec 2009 15:25:16 -0500 From: t...@sss.pgh.pa.us Alex - ainto...@hotmail.com writes: Now here is what I noticed. a) if I run it in the morning, processing starts very slow, but after a few thousand records it will speed up until I actually get about 100 records processed per millisecond. b) it sometime takes about 5-10k records till i really get up to speed. meaning the first few hundreds can take up to 1-2 minutes. c) if i run the same job a few hrs later (we run it twice a day) it generally runs much faster. Even though we added more data to one of the big tables it selects from. d) this however starts again the next day. ( not much data has been changed between the 2nd run of the day and the first one of the next one, but yet it will start crawling again. What this sounds like is it's fast when most of the data has been swapped in to kernel disk cache, and slow when the data actually has to be read from disk. Probably the reason it's slow in the morning is there are some unrelated tasks that run overnight and cause memory to get filled with all their data instead of the PG tables. You could check this theory by watching with vmstat or similar tool to see how much actual disk I/O is happening in the slow and fast cases. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ A world FIRST in property search has arrived! Check out Domain Radar NOW! http://clk.atdmt.com/NMN/go/157631292/direct/01/
Re: [GENERAL] PL/Perl Performance Problems
Alex - ainto...@hotmail.com writes: I actually looked at that too, but there is no swapping going on. If you were only watching for swapping, that wouldn't catch what I'm talking about. Replacing cached disk buffers with other disk data doesn't count as swapping in any system I've used. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl Performance Problems
Thank, I will check that out. To: ainto...@hotmail.com CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] PL/Perl Performance Problems Date: Fri, 18 Dec 2009 15:36:15 -0500 From: t...@sss.pgh.pa.us Alex - ainto...@hotmail.com writes: I actually looked at that too, but there is no swapping going on. If you were only watching for swapping, that wouldn't catch what I'm talking about. Replacing cached disk buffers with other disk data doesn't count as swapping in any system I've used. regards, tom lane _ If It Exists, You'll Find it on SEEK Australia's #1 job site http://clk.atdmt.com/NMN/go/157639755/direct/01/
Re: [GENERAL] PL/Perl Performance Problems
609 5 0 74 21 0 1 0 1468 294088 106768 1712195200 2032 2290 1586 752 4 0 74 21 0 1 1 1468 278312 107084 1713626400 2548 2706 1711 914 6 0 75 19 0 0 2 1468 244156 107208 1716823200 2284 3154 1635 858 6 0 74 19 0 2 0 1468 68 107420 1718557200 2035 3388 1555 717 12 0 71 17 0 0 1 1464 229088 107464 1719996000 1279 5878 2048 302 21 0 73 6 0 1 0 1464 220524 107488 1720880400 1116 3497 1301 270 19 0 75 6 0 1 0 1464 204912 107520 1722420800 1321 3519 1321 282 20 0 75 5 0 1 0 1464 182580 107556 1724698400 1446 3726 1371 329 20 0 75 5 0 1 0 1464 170096 107600 1725962000 1270 3300 1307 289 20 0 75 5 0 0 1 1464 148800 107636 1728065600 1393 3710 1351 319 19 0 75 6 0 1 0 1464 136432 107700 1729218800 1384 3291 1308 291 19 0 75 5 0 1 0 1464 118672 107808 1730830800 1383 3656 1365 410 21 1 73 6 0 0 1 1464 110880 107840 173170240 0 1534 6340 2207 403 17 1 74 8 0 1 0 1464 106996 107848 173210240 0 1142 7217 2932 340 15 1 75 10 0 0 1 1464 93096 107876 17335540 00 1268 7923 2944 387 14 1 74 11 0 0 1 1464 109156 107900 17318800 00 1333 7776 2871 380 15 1 75 9 0 0 1 1464 93360 107928 1733510000 1300 8944 3393 378 17 1 75 8 0 0 1 1464 96200 107972 1733175200 1136 8365 3101 369 16 1 74 9 0 1 0 1464 105692 108020 1732183200 1171 7041 2744 352 14 1 75 11 0 1 0 1464 106068 108056 1732128800 1176 6564 2765 347 14 0 75 11 0 0 1 1464 100424 108100 1732699600 1236 6687 2755 360 14 1 74 10 0 1 0 1464 104532 108140 1732268000 1194 5106 2230 342 13 0 75 12 0 1 0 1464 98196 107216 1732838800 1238 6720 2611 352 15 1 75 10 0 To: ainto...@hotmail.com CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] PL/Perl Performance Problems Date: Fri, 18 Dec 2009 15:36:15 -0500 From: t...@sss.pgh.pa.us Alex - ainto...@hotmail.com writes: I actually looked at that too, but there is no swapping going on. If you were only watching for swapping, that wouldn't catch what I'm talking about. Replacing cached disk buffers with other disk data doesn't count as swapping in any system I've used. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Get more out of Hotmail Check out the latest features today http://windowslive.ninemsn.com.au/hotmail/article/878466/your-hotmail-is-about-to-get-even-better
Re: [GENERAL] PL/Perl Performance Problems
Alex - wrote: Tom, I am logging these stats now, but i am having a similar issue. both jobs in the morning and after noon insert about 400k records (200k each) In the morning that job takes 450secIn the afternoon only 150; No select, simple parsing a file and insert the records These two tables don't grow as at night a cleanup job removes about the same amount of historical data (older about 40 days). Its kind of the same behavior and i don't really see where the swapping fits in. Are you running vacuum of some sort on this table regularly? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl Performance Problems
I run AutovacuumI run a Vaccum Full Analyze every SatI re-index the tables every Sat Date: Fri, 18 Dec 2009 21:20:23 -0300 From: alvhe...@commandprompt.com To: ainto...@hotmail.com CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org Subject: Re: [GENERAL] PL/Perl Performance Problems Alex - wrote: Tom, I am logging these stats now, but i am having a similar issue. both jobs in the morning and after noon insert about 400k records (200k each) In the morning that job takes 450secIn the afternoon only 150; No select, simple parsing a file and insert the records These two tables don't grow as at night a cleanup job removes about the same amount of historical data (older about 40 days). Its kind of the same behavior and i don't really see where the swapping fits in. Are you running vacuum of some sort on this table regularly? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ A world FIRST in property search has arrived! Check out Domain Radar NOW! http://clk.atdmt.com/NMN/go/157631292/direct/01/
Re: [GENERAL] PL/Perl Performance Problems
Note that you seem to have a lot more IO wait in the first run than in the second, which means that the task is hitting the disks more in the first run than in the second one. Once IO wait starts to climb, performance starts to dive, generally. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl Performance Problems
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. Date: Fri, 18 Dec 2009 23:00:16 -0700 Subject: Re: [GENERAL] PL/Perl Performance Problems From: scott.marl...@gmail.com To: ainto...@hotmail.com CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org Note that you seem to have a lot more IO wait in the first run than in the second, which means that the task is hitting the disks more in the first run than in the second one. Once IO wait starts to climb, performance starts to dive, generally. _ Use Messenger in your Hotmail inbox Find out how http://windowslive.ninemsn.com.au/hotmail/article/823454/web-im-for-hotmail-is-here
Re: [GENERAL] PL/Perl Performance Problems
On Fri, Dec 18, 2009 at 11:37 PM, Alex - ainto...@hotmail.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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl Performance Problems
I have the standard ones and no others. # run-parts01 * * * * root run-parts /etc/cron.hourly02 4 * * * root run-parts /etc/cron.daily22 4 * * 0 root run-parts /etc/cron.weekly42 4 1 * * root run-parts /etc/cron.monthly ls -l /etc/cron*-rw-r--r-- 1 root root0 Apr 6 2009 /etc/cron.deny-rw-r--r-- 1 root root 255 Jan 6 2007 /etc/crontab/etc/cron.d:-rw-r--r-- 1 root root 192 Mar 12 2009 sysstat/etc/cron.daily:-rwxr-xr-x 1 root root 379 Mar 28 2007 0anacronlrwxrwxrwx 1 root root 39 Apr 6 2009 0logwatch - /usr/share/logwatch/scripts/logwatch.pl-rwxr-xr-x 1 root root 118 Jan 21 2009 cups-rwxr-xr-x 1 root root 180 Mar 12 2009 logrotate-rwxr-xr-x 1 root root 418 Jan 6 2007 makewhatis.cron-rwxr-xr-x 1 root root 137 Mar 12 2009 mlocate.cron-rwxr-xr-x 1 root root 2181 Jun 21 2006 prelink-rwxr-xr-x 1 root root 296 Jan 21 2009 rpm-rwxr-xr-x 1 root root 328 Feb 26 2009 tmpwatch/etc/cron.hourly:-rwxr-xr-x 1 root root 71 Jan 10 2007 mcelog.cron/etc/cron.monthly:-rwxr-xr-x 1 root root 381 Mar 28 2007 0anacron/etc/cron.weekly:-rwxr-xr-x 1 root root 380 Mar 28 2007 0anacron-rwxr-xr-x 1 root root 414 Jan 6 2007 makewhatis.cron Date: Fri, 18 Dec 2009 23:45:07 -0700 Subject: Re: [GENERAL] PL/Perl Performance Problems From: scott.marl...@gmail.com To: ainto...@hotmail.com CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org On Fri, Dec 18, 2009 at 11:37 PM, Alex - ainto...@hotmail.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. _ A world FIRST in property search has arrived! Check out Domain Radar NOW! http://clk.atdmt.com/NMN/go/157631292/direct/01/
Re: [GENERAL] PL/Perl Performance Problems
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.marl...@gmail.com To: ainto...@hotmail.com CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org On Fri, Dec 18, 2009 at 11:37 PM, Alex - ainto...@hotmail.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@postgresql.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/
Re: [GENERAL] PL/Perl Performance Problems
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 - ainto...@hotmail.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.marl...@gmail.com To: ainto...@hotmail.com CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org On Fri, Dec 18, 2009 at 11:37 PM, Alex - ainto...@hotmail.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@postgresql.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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl Performance Problems
Yes I do, but this is the pl/perl function called by a batch job i run. before the pl/perl function is called i insert 2x200k records into 2 tables (200k per table). First i thought that it might be a problem with the perl function, but then i noticed that it even started earlier with the simple inserts. after the insert the job will call the function and there i have the same issues. runs slow in the morning, and fast in the afternoon. it will pick up speed after 5-10k records thanks for your help Date: Sat, 19 Dec 2009 00:10:36 -0700 Subject: Re: [GENERAL] PL/Perl Performance Problems From: scott.marl...@gmail.com To: ainto...@hotmail.com CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.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 - ainto...@hotmail.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.marl...@gmail.com To: ainto...@hotmail.com CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org On Fri, Dec 18, 2009 at 11:37 PM, Alex - ainto...@hotmail.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@postgresql.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. _ If It Exists, You'll Find it on SEEK Australia's #1 job site http://clk.atdmt.com/NMN/go/157639755/direct/01/
