Load spikes on 8.1.11 - Mailing list pgsql-hackers

<div dir="ltr"><font size="-1"><font face="Courier New">Hi All,<br /><br />     I have been perplexed by random load
spikeson an 8.1.11 instance. many a times they are random, in the sense we cannot tie a particular scenario as the
causefor it! But a few times we can see that when we are executing huge scripts, which include DDL as well as DML, the
loadon the box spikes to above 200. We see similar load spikes other times too when we are not running any such task on
theDB.<br /><br />     During these spikes, in the 'top' sessions we see the 'idle' PG processes consuming between 2
and5 % CPU, and since the box has 8 CPUS (</font></font><tt>2 sockets and each CPU is a quad core Intel Xeon
processors</tt><fontsize="-1"><font face="Courier New">) and somewhere around 200 Postgres processes, the load spikes
toabove 200; and it does this very sharply.<br /><br />     We are running the scripts using psql -f, but we can see
theload even while running the commands on by one!<br /><br />     When there's no load, an strace session on an 'idle'
PGprocess looks like:<br /><br /> [postgres@db1 data]$ strace -p 9375<br /> Process 9375 attached - interrupt to
quit<br/> recvfrom(9,  <unfinished ...><br /> Process 9375 detached<br /><br /><br />     But under these heavy
loadonditions, an 'idle' PG process' strace looks like:<br /><br /> [postgres@db1 data]$ strace -p 22994<br /> Process
22994attached - interrupt to quit<br /> select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 10000}) = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 11000}) = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 14000}) = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 17000}) = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 31000}) = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 51000}) = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 1000})  = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 1000})  = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 2000})  = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 4000})  = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 5000})  = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 1000})  = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 2000})  = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 2000})  = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 3000})  = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 6000})  = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 12000}) = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 12000}) = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 23000}) = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 27000}) = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 47000}) = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 70000}) = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 1000})  = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 2000})  = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 4000})  = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 7000})  = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 11000}) = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 16000}) = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 19000}) = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 35000}) = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 53000}) = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 75000}) = 0 (Timeout)<br /> select(0, NULL,
NULL,NULL, {0, 76000}) = 0 (Timeout)<br /> select(0, NULL, NULL, NULL, {0, 102000}) = 0 (Timeout)<br /> Process 22994
detached<br/><br /><br />     So I guess there's something very wrong with the above 'select' calls.<br /><br />    
Cansomebody please shed some light on this? Let me know what OS/hardware specs you need.<br /><br />    Any help is
greatlyappreciated.<br /><br /> Thanks in advance,</font></font><br clear="all" /><br />-- <br
/>gurjeet[.singh]@EnterpriseDB.com<br/>singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com<br /><br
/>EnterpriseDB<a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br /><br />Mail sent from my
BlackLaptopdevice </div>