Thread: PL/Perl Performance Problems
Hi,
Australia's #1 job site If It Exists, You'll Find it on SEEK
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 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).
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
Australia's #1 job site If It Exists, You'll Find it on SEEK
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
actuallyget 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-2minutes.
> 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
moredata 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
firstone 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.us
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Check out Domain Radar NOW! A world FIRST in property search has arrived!
> 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.us
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Check out Domain Radar NOW! A world FIRST in property search has arrived!
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.us
>
> 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
Australia's #1 job site If It Exists, You'll Find it on SEEK
> 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.us
>
> 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
Australia's #1 job site If It Exists, You'll Find it on SEEK
Tom,
> 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.us
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Check out the latest features today Get more out of Hotmail
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 450sec
In 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 10
procs -----------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 forks
FINSELECT [08:32:47] ~ $ vmstat 10
procs -----------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.us
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Check out the latest features today Get more out of Hotmail
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 about400k 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 40days). > 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 Autovacuum
Check out Domain Radar NOW! A world FIRST in property search has arrived!
I run a Vaccum Full Analyze every Sat
I 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 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 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
> 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 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 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
Check out Domain Radar NOW! A world FIRST in property search has arrived!
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...
> 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.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.
Find out how Use Messenger in your Hotmail inbox
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.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.
Find out how Use Messenger in your Hotmail inbox
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.
> 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.org
>
> 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.
Check out Domain Radar NOW! A world FIRST in property search has arrived!
# run-parts
01 * * * * root run-parts /etc/cron.hourly
02 4 * * * root run-parts /etc/cron.daily
22 4 * * 0 root run-parts /etc/cron.weekly
42 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 0anacron
lrwxrwxrwx 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.org
>
> 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.
Check out Domain Radar NOW! A world FIRST in property search has arrived!
On a 2nd thought... where does the cach come into play when i only do inserts and no selects.
Meet singles at ninemsn dating Looking for a great date?
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.org
>
> 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.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 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.org
>
> 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.
>
> --
> 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?
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. > 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.org >> >> 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. >> >> -- >> 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).
> 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.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@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.marlowe@gmail.com
> >> To: aintokyo@hotmail.com
> >> CC: tgl@sss.pgh.pa.us; pgsql-general@postgresql.org
> >>
> >> 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.
> >>
> >> --
> >> 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.
Australia's #1 job site If It Exists, You'll Find it on SEEK
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.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@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.marlowe@gmail.com
> >> To: aintokyo@hotmail.com
> >> CC: tgl@sss.pgh.pa.us; pgsql-general@postgresql.org
> >>
> >> 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.
> >>
> >> --
> >> 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.
Australia's #1 job site If It Exists, You'll Find it on SEEK
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: [GENERAL] PL/Perl Performance Problems
> From: scott.marlowe@gmail.com
> To: aintokyo@hotmail.com
> CC: tgl@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 - <aintokyo@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.marlowe@gmail.com
> >> To: aintokyo@hotmail.com
> >> CC: tgl@sss.pgh.pa.us; pgsql-general@postgresql.org
> >>
> >> 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.
> >>
> >> --
> >> 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
Meet singles at ninemsn dating Looking for a great date?
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
fewextensive 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,
Check out the latest features today Get more out of Hotmail
I am already have started doing that. i.e. running the dummy task to get the tables loaded again and it worked fine today.
regards
Alex
> 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.us
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 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.us
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Check out the latest features today Get more out of Hotmail