process hangs using perl?

[Patrick Hatcher]

Running a perl script against my dev server (pg 7.3.4) that currently runs
fine on the production server. And for some reason Pg always seems to hang
during the process (see below for Top print out). This happens randomily
throughout the script and can be on a SELECT, INSERT, or UPDATE statement,
but doesn't cause the Pg server to go down (which is good). Any
suggestions on where to start looking?

Perl version 5.6.1


---Top print out

4:08pm up 23 days, 4:46, 3 users, load average: 1.00, 1.05, 1.39
78 processes: 75 sleeping, 2 running, 1 zombie, 0 stopped
CPU0 states: 30.4% user, 4.0% system, 0.0% nice, 64.4% idle
CPU1 states: 47.0% user, 3.3% system, 0.0% nice, 49.0% idle
Mem: 513640K av, 505332K used, 8308K free, 0K shrd, 13176K
buff
Swap: 2044056K av, 48624K used, 1995432K free 356028K
cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
16094 postgres 14 0 98.8M 97M 18900 R 85.2 19.4 31:38 postgres:
phatcher mdc_oz [local] SELECT
16655 postgres 9 0 1052 1052 836 R 0.1 0.2 0:00 top
14397 postgres 8 0 1964 1840 1708 S 0.0 0.3 0:00
/usr/local/postgresql7.3.4/bin/postmaster
14398 postgres 9 0 2932 2780 1700 S 0.0 0.5 0:03 postgres:
stats buffer process
14399 postgres 9 0 2168 2016 1704 S 0.0 0.3 0:05 postgres:
stats collector process
16074 postgres 9 0 23836 20M 18884 S 0.0 4.1 16:24 postgres:
phatcher mdc_oz [local] SELECT waiting
16076 postgres 9 0 21792 20M 18916 S 0.0 4.1 644:42 postgres:
phatcher mdc_oz [local] SELECT waiting
16546 postgres 9 0 17988 16M 17208 S 0.0 3.3 0:11 postgres:
phatcher mdc_oz [local] SELECT waiting
16650 postgres 9 0 1332 1332 1060 S 0.0 0.2 0:00 /bin/bash
TIA

Patrick Hatcher

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Greg Patnude]

""Patrick Hatcher"" <PH******@macys.com> wrote in message
news:OF******************************************* ************@fds.com...

Running a perl script against my dev server (pg 7.3.4) that currently runs
fine on the production server. And for some reason Pg always seems to hang during the process (see below for Top print out). This happens randomily
throughout the script and can be on a SELECT, INSERT, or UPDATE statement, but doesn't cause the Pg server to go down (which is good). Any
suggestions on where to start looking?

Perl version 5.6.1


---Top print out

4:08pm up 23 days, 4:46, 3 users, load average: 1.00, 1.05, 1.39
78 processes: 75 sleeping, 2 running, 1 zombie, 0 stopped
CPU0 states: 30.4% user, 4.0% system, 0.0% nice, 64.4% idle
CPU1 states: 47.0% user, 3.3% system, 0.0% nice, 49.0% idle
Mem: 513640K av, 505332K used, 8308K free, 0K shrd, 13176K
buff
Swap: 2044056K av, 48624K used, 1995432K free 356028K
cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
16094 postgres 14 0 98.8M 97M 18900 R 85.2 19.4 31:38 postgres:
phatcher mdc_oz [local] SELECT
16655 postgres 9 0 1052 1052 836 R 0.1 0.2 0:00 top
14397 postgres 8 0 1964 1840 1708 S 0.0 0.3 0:00
/usr/local/postgresql7.3.4/bin/postmaster
14398 postgres 9 0 2932 2780 1700 S 0.0 0.5 0:03 postgres:
stats buffer process
14399 postgres 9 0 2168 2016 1704 S 0.0 0.3 0:05 postgres:
stats collector process
16074 postgres 9 0 23836 20M 18884 S 0.0 4.1 16:24 postgres:
phatcher mdc_oz [local] SELECT waiting
16076 postgres 9 0 21792 20M 18916 S 0.0 4.1 644:42 postgres:
phatcher mdc_oz [local] SELECT waiting
16546 postgres 9 0 17988 16M 17208 S 0.0 3.3 0:11 postgres:
phatcher mdc_oz [local] SELECT waiting
16650 postgres 9 0 1332 1332 1060 S 0.0 0.2 0:00 /bin/bash
TIA

Patrick Hatcher

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Looks to me like you probably have a bad SELECT holding up all of the
others... Any chance its a "SELECT .. FOR UPDATE ??? that's hung and you
have a transaction open ? That'll cause everything else to wait...


--
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
[phone]

[Network Administrator]

Patrick,

Can you provide the code (or at least the relevant parts) for us to look at?
Quoting Patrick Hatcher <PH******@macys.com>:

Running a perl script against my dev server (pg 7.3.4) that currently runs
fine on the production server. And for some reason Pg always seems to hang
during the process (see below for Top print out). This happens randomily
throughout the script and can be on a SELECT, INSERT, or UPDATE statement,
but doesn't cause the Pg server to go down (which is good). Any
suggestions on where to start looking?

Perl version 5.6.1


---Top print out

4:08pm up 23 days, 4:46, 3 users, load average: 1.00, 1.05, 1.39
78 processes: 75 sleeping, 2 running, 1 zombie, 0 stopped
CPU0 states: 30.4% user, 4.0% system, 0.0% nice, 64.4% idle
CPU1 states: 47.0% user, 3.3% system, 0.0% nice, 49.0% idle
Mem: 513640K av, 505332K used, 8308K free, 0K shrd, 13176K
buff
Swap: 2044056K av, 48624K used, 1995432K free 356028K
cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
16094 postgres 14 0 98.8M 97M 18900 R 85.2 19.4 31:38 postgres:
phatcher mdc_oz [local] SELECT
16655 postgres 9 0 1052 1052 836 R 0.1 0.2 0:00 top
14397 postgres 8 0 1964 1840 1708 S 0.0 0.3 0:00
/usr/local/postgresql7.3.4/bin/postmaster
14398 postgres 9 0 2932 2780 1700 S 0.0 0.5 0:03 postgres:
stats buffer process
14399 postgres 9 0 2168 2016 1704 S 0.0 0.3 0:05 postgres:
stats collector process
16074 postgres 9 0 23836 20M 18884 S 0.0 4.1 16:24 postgres:
phatcher mdc_oz [local] SELECT waiting
16076 postgres 9 0 21792 20M 18916 S 0.0 4.1 644:42 postgres:
phatcher mdc_oz [local] SELECT waiting
16546 postgres 9 0 17988 16M 17208 S 0.0 3.3 0:11 postgres:
phatcher mdc_oz [local] SELECT waiting
16650 postgres 9 0 1332 1332 1060 S 0.0 0.2 0:00 /bin/bash
TIA

Patrick Hatcher

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly