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/
Alex - <aintokyo@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
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: aintokyo@hotmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PL/Perl Performance Problems
Date: Fri, 18 Dec 2009 15:25:16 -0500
From: tgl@sss.pgh.pa.usAlex - <aintokyo@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/
Alex - <aintokyo@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
Thank, I will check that out.
To: aintokyo@hotmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PL/Perl Performance Problems
Date: Fri, 18 Dec 2009 15:36:15 -0500
From: tgl@sss.pgh.pa.usAlex - <aintokyo@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/
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.
Here are the stats for this 400k inserts in the morning.
vmstat -s 10 18482752 total memory 18381116 used memory 7014556 active memory 11040672 inactive memory 101636 free memory 102168 buffer memory 17757988 swap cache 8193140 total swap 1468 used swap 8191672 free swap 1781577 non-nice user cpu ticks 13 nice user cpu ticks 52619 system cpu ticks 40813639 idle cpu ticks 297259 IO-wait cpu ticks 241 IRQ cpu ticks 6253 softirq cpu ticks 0 stolen cpu ticks 26348975 pages paged in 46203834 pages paged out 0 pages swapped in 361 pages swapped out 116011398 interrupts 20586721 CPU context switches 1261071430 boot time 27428 forks [08:27:07] ~ $ vmstat 10procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 1 1 1468 101976 102260 17750836 0 0 62 108 70 48 4 0 95 1 0 1 0 1468 98996 102280 17753148 0 0 2090 6692 3066 3742 8 1 75 16 0 0 1 1468 97376 102276 17755204 0 0 2462 2475 1723 4403 9 1 75 15 0 0 1 1468 101308 102284 17750644 0 0 2608 3281 1741 4666 10 1 75 14 0 0 1 1468 96852 102308 17755328 0 0 2567 2376 1707 4658 10 1 75 14 0 0 1 1468 103104 102308 17748396 0 0 2815 3378 1813 5018 11 1 75 13 0 0 1 1468 96264 102344 17755588 0 0 3014 2582 1814 5391 12 2 75 12 0 1 0 1468 92816 102372 17758244 0 0 3141 3358 1840 5645 12 2 75 12 0 1 0 1468 187184 102408 17664804 0 0 1956 3802 1622 3920 17 1 75 7 0 0 2 1468 154272 102604 17684708 0 0 1547 50848 10246 602 12 4 70 15 0 0 2 1468 125416 102692 17716672 0 0 299 20352 5416 273 3 2 65 31 0 1 0 1468 98716 102908 17739988 0 0 1906 46720 9263 415 14 3 73 9 0 1 0 1468 102608 103000 17703556 0 0 1932 11837 1339 348 22 1 73 4 0 1 0 1468 107096 103388 17333928 0 0 1164 1328 1234 300 14 1 74 11 0 0 1 1468 105224 103584 17336044 0 0 1702 2286 1354 362 17 0 75 8 0 0 1 1468 108516 103628 17333236 0 0 1236 6901 2389 358 19 1 74 6 0 1 0 1468 92184 103688 17349856 0 0 975 9852 3408 344 15 1 75 9 0 1 0 1468 101324 103764 17340572 0 0 1042 8904 3174 334 17 1 75 8 0 2 0 1468 102996 103616 17049288 0 0 1290 11402 3701 492 33 2 58 8 0 1 1 1468 92336 103704 17060584 0 0 1091 8918 3076 336 40 1 50 10 0 1 1 1468 102392 103824 17044704 0 0 1041 7743 2563 364 38 1 50 11 0 1 2 1468 95864 103956 17037800 0 0 1432 1398 1519 609 28 0 50 22 0 1 2 1468 94488 104212 17030460 0 0 1610 2448 1685 826 30 0 48 22 0 1 2 1468 187308 104376 16940672 0 0 1751 3508 1752 994 29 1 40 31 0 1 2 1468 159824 104708 16966516 0 0 1837 3309 1818 997 30 0 43 27 0 1 1 1468 149196 105040 16976736 0 0 1966 2930 1812 1030 29 0 45 26 0 1 1 1468 417112 105240 17004556 0 0 1962 2730 1727 915 18 0 61 20 0 0 2 1468 389056 105628 17031620 0 0 1988 2594 1799 776 5 0 74 20 0
FINSELECT [08:32:42] ~ $ vmstat -s 18482752 total memory 18121980 used memory 7993020 active memory 9782060 inactive memory 360772 free memory 105860 buffer memory 17059320 swap cache 8193140 total swap 1468 used swap 8191672 free swap 1803616 non-nice user cpu ticks 13 nice user cpu ticks 53801 system cpu ticks 40905481 idle cpu ticks 317949 IO-wait cpu ticks 245 IRQ cpu ticks 6648 softirq cpu ticks 0 stolen cpu ticks 26978271 pages paged in 48982990 pages paged out 0 pages swapped in 361 pages swapped out 116958150 interrupts 21299728 CPU context switches 1261071430 boot time 27505 forksFINSELECT [08:32:47] ~ $ vmstat 10procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 1 1 1468 354412 105920 17065284 0 0 63 114 72 49 4 0 95 1 0 0 2 1468 327640 106260 17091364 0 0 1932 2460 1744 743 5 0 75 20 0 0 2 1468 318956 106472 17099540 0 0 1519 2248 1661 609 5 0 74 21 0 1 0 1468 294088 106768 17121952 0 0 2032 2290 1586 752 4 0 74 21 0 1 1 1468 278312 107084 17136264 0 0 2548 2706 1711 914 6 0 75 19 0 0 2 1468 244156 107208 17168232 0 0 2284 3154 1635 858 6 0 74 19 0 2 0 1468 222268 107420 17185572 0 0 2035 3388 1555 717 12 0 71 17 0 0 1 1464 229088 107464 17199960 0 0 1279 5878 2048 302 21 0 73 6 0 1 0 1464 220524 107488 17208804 0 0 1116 3497 1301 270 19 0 75 6 0 1 0 1464 204912 107520 17224208 0 0 1321 3519 1321 282 20 0 75 5 0 1 0 1464 182580 107556 17246984 0 0 1446 3726 1371 329 20 0 75 5 0 1 0 1464 170096 107600 17259620 0 0 1270 3300 1307 289 20 0 75 5 0 0 1 1464 148800 107636 17280656 0 0 1393 3710 1351 319 19 0 75 6 0 1 0 1464 136432 107700 17292188 0 0 1384 3291 1308 291 19 0 75 5 0 1 0 1464 118672 107808 17308308 0 0 1383 3656 1365 410 21 1 73 6 0 0 1 1464 110880 107840 17317024 0 0 1534 6340 2207 403 17 1 74 8 0 1 0 1464 106996 107848 17321024 0 0 1142 7217 2932 340 15 1 75 10 0 0 1 1464 93096 107876 17335540 0 0 1268 7923 2944 387 14 1 74 11 0 0 1 1464 109156 107900 17318800 0 0 1333 7776 2871 380 15 1 75 9 0 0 1 1464 93360 107928 17335100 0 0 1300 8944 3393 378 17 1 75 8 0 0 1 1464 96200 107972 17331752 0 0 1136 8365 3101 369 16 1 74 9 0 1 0 1464 105692 108020 17321832 0 0 1171 7041 2744 352 14 1 75 11 0 1 0 1464 106068 108056 17321288 0 0 1176 6564 2765 347 14 0 75 11 0 0 1 1464 100424 108100 17326996 0 0 1236 6687 2755 360 14 1 74 10 0 1 0 1464 104532 108140 17322680 0 0 1194 5106 2230 342 13 0 75 12 0 1 0 1464 98196 107216 17328388 0 0 1238 6720 2611 352 15 1 75 10 0
To: aintokyo@hotmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PL/Perl Performance Problems
Date: Fri, 18 Dec 2009 15:36:15 -0500
From: tgl@sss.pgh.pa.usAlex - <aintokyo@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
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 Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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: alvherre@commandprompt.com
To: aintokyo@hotmail.com
CC: tgl@sss.pgh.pa.us; pgsql-general@postgresql.org
Subject: Re: [GENERAL] PL/Perl Performance ProblemsAlex - 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 Herrera http://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/
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.
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.marlowe@gmail.com
To: aintokyo@hotmail.com
CC: tgl@sss.pgh.pa.us; pgsql-general@postgresql.orgNote 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
On Fri, Dec 18, 2009 at 11:37 PM, Alex - <aintokyo@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.
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 root 0 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.marlowe@gmail.com
To: aintokyo@hotmail.com
CC: tgl@sss.pgh.pa.us; pgsql-general@postgresql.orgOn Fri, Dec 18, 2009 at 11:37 PM, Alex - <aintokyo@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/
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.marlowe@gmail.com
To: aintokyo@hotmail.com
CC: tgl@sss.pgh.pa.us; pgsql-general@postgresql.orgOn Fri, Dec 18, 2009 at 11:37 PM, Alex - <aintokyo@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/
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@hotmail.com> wrote:
On a 2nd thought... where does the cach come into play when i only do
inserts and no selects.
AlexDate: Fri, 18 Dec 2009 23:45:07 -0700
Subject: Re: [GENERAL] PL/Perl Performance Problems
From: scott.marlowe@gmail.com
To: aintokyo@hotmail.com
CC: tgl@sss.pgh.pa.us; pgsql-general@postgresql.orgOn Fri, Dec 18, 2009 at 11:37 PM, Alex - <aintokyo@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.
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.marlowe@gmail.com
To: aintokyo@hotmail.com
CC: tgl@sss.pgh.pa.us; pgsql-general@postgresql.orgAccording 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@hotmail.com> wrote:
On a 2nd thought... where does the cach come into play when i only do
inserts and no selects.
AlexDate: Fri, 18 Dec 2009 23:45:07 -0700
Subject: Re: [GENERAL] PL/Perl Performance Problems
From: scott.marlowe@gmail.com
To: aintokyo@hotmail.com
CC: tgl@sss.pgh.pa.us; pgsql-general@postgresql.orgOn Fri, Dec 18, 2009 at 11:37 PM, Alex - <aintokyo@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/
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.marlowe@gmail.com
To: aintokyo@hotmail.com
CC: tgl@sss.pgh.pa.us; pgsql-general@postgresql.orgAccording 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@hotmail.com> wrote:
On a 2nd thought... where does the cach come into play when i only do
inserts and no selects.
AlexDate: Fri, 18 Dec 2009 23:45:07 -0700
Subject: Re: [GENERAL] PL/Perl Performance Problems
From: scott.marlowe@gmail.com
To: aintokyo@hotmail.com
CC: tgl@sss.pgh.pa.us; pgsql-general@postgresql.orgOn Fri, Dec 18, 2009 at 11:37 PM, Alex - <aintokyo@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/
Alex - <aintokyo@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
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: aintokyo@hotmail.com
CC: scott.marlowe@gmail.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] PL/Perl Performance Problems
Date: Sun, 20 Dec 2009 22:39:00 -0500
From: tgl@sss.pgh.pa.usAlex - <aintokyo@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