Re: PL/Perl Performance Problems

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(at)hotmail(dot)com
> CC: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] PL/Perl Performance Problems
> Date: Fri, 18 Dec 2009 15:36:15 -0500
> From: tgl(at)sss(dot)pgh(dot)pa(dot)us
>
> Alex - <aintokyo(at)hotmail(dot)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(at)postgresql(dot)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