statement stuck when the connection grew up to 45 or more

<kah_hang_ang@toray.com.my> wrote

Recently I facing one problem, when the connection for postgresql grow
up
to 45 or more, when I trigger a statement from WebApp
this statement will stuck forever.
I try to kill this transaction and then trigger the same statement
again
but it still the same.
But this time I leave the transaction there and try to kill other
connections that is not in use.
It's weird that after I kill around 5-10 unused connections, the
statement
start to run and finish.

I can hardly believe that's Postgres's problem. Are you sure the query
was processing by the server? Try to do:

ps -auxw|grep postgres

to see if you can see the query was stuck there.

Regards,
Qingqing

Hi

Newbie when it comes down to postgre.

OS: Windows XP SP2 Pro Dutch

When I run a DMS (Xinco) that uses postgre (I only use postgre for this), a
service is started. Xinco uses a separate limited Xinco User account.

After starting the service I see several postgre.exe services running of
various sizes. That's fine.

After an hour or so I see several postgre.exe services running (up to 200
and still increasing) and it is the postgre.exe 76kb that is in that huge
number (even when nobody uses Xinco, it still continous).

When I stop the PostgreSQL service then the large kb postgre.exe disappears
from the task manager ...but all those 76kb postgre.exe remain in memory.

Any ideas?

Stefan

This is the statement which having problem:
select count(distinct empno) as counter1 from pay_master_history
where empno in (select empno from pay_batch_basic_history where
organizationid like '015003%')
and processyear = '2006'
and processmonth = '05'
and processbatch = '1'

SELECT COUNT (*) FROM (
SELECT empno as counter1 from pay_master_history as a
INNER JOIN (select empno from pay_batch_basic_history where organizationid
like '015003%' and processyear = '2006'
and processmonth = '05'
and processbatch = '1') as b
ON b.empno = a.empno ) as count_result
-----------------------------
or just create the view and use inner join then count :b

Hi

Newbie when it comes down to postgre.

OS: Windows XP SP2 Pro Dutch

When I run a DMS (Xinco) that uses postgre (I only use
postgre for this), a service is started. Xinco uses a
separate limited Xinco User account.

After starting the service I see several postgre.exe services
running of various sizes. That's fine.

After an hour or so I see several postgre.exe services
running (up to 200 and still increasing) and it is the
postgre.exe 76kb that is in that huge number (even when
nobody uses Xinco, it still continous).

When I stop the PostgreSQL service then the large kb
postgre.exe disappears from the task manager ...but all those
76kb postgre.exe remain in memory.

Any ideas?

If you actually have postgre.exe files there, you need to talk to
whomever yuo got that from, because that's not PostgreSQL.

If you have postgres.exe files, which is the PostgreSQL backend exe,
then you need to check your logs (both eventlog and the pg_log
directory, depending on how it's configured) for error messagesn that
will indicate what yourp roblem is.

//Magnus

[list re-added]

In the eventlog I found quite often the following (therefore
I assume it has to do with those many postgres.exe's). Does
this give an indication where to look for the problem/solution?

Yes, it definitly does.

gegevens zijn deel van de gebeurtenis: LOG: unexpected EOF
on client connection (Can't find description of event-ID (0)
in the source (PosgreSQL) The local computer might not have
the necessary registry data or DLL-message files to display
messages from an external computer. You might want to try
the option /AUXsource= to obtain these descriptions, see Help
and support for more details. The following data are part of
the event: LOG: unexpected EOF on the client connection)

First - the fact that it can't find the DLL message file shows that
PostgreSQL is not properly installed. I take it it wasn't installed
using the official installer?

The "unepxected EOF" message is normal for any client that exits without
explicitly closing the connection.

support for more details. The following data are part of the
event: FATAL: could not duplicate socket 1772 for use in the
backend: error code 10038)

This is a not too uncommon error, meaning that something broke your
TCP/IP stack. We've seen this many times, and it's usually either
antivirus or personal firewall software. If you have any of those on the
machine, try uninstalling them and see if that helps (sometimes it's
enough to disable them for the pg processes, sometimes you need to
uninstall them completely).

If that doesn't help, search for LSPfix - it's a tool that can help you
fix it.

//Magnus

I had check using ps -auxw|grep postgres & using select * from
pg_stat_activity
and it shows that the is really running and use 99% of the CPU.

Is there any wrong with the query?
As suggested by Jan Cruz, it is to fine tune the performance of the query.
Even if I not fine tune the query it should able to run without problem.

What I not understand is why this query stuck when the connection growth up
to certain limit.

I did try execute the query with the connection more than 45 and it really
stuck there.
I wait for around 10 minutes but it still running.
I kill about 10 connections then the query start to run and finish within
30 sec.

May I know what will be the possible cause of this problem?

Regards,
KH Ang

"Jan Cruz"
<malebug@gmail.com> To: "Qingqing Zhou" <zhouqq@cs.toronto.edu>
Sent by: cc: pgsql-bugs@postgresql.org
pgsql-bugs-owner@pos Subject: Re: [BUGS] statement stuck when the connection grew up to 45 or more
tgresql.org

06/02/2006 03:08 PM

This is the statement which having problem:
select count(distinct empno) as counter1 from pay_master_history
where empno in (select empno from pay_batch_basic_history where
organizationid like '015003%')
and processyear = '2006'
and processmonth = '05'
and processbatch = '1'

SELECT COUNT (*) FROM (
SELECT empno as counter1 from pay_master_history as a
INNER JOIN (select empno from pay_batch_basic_history where organizationid
like '015003%' and processyear = '2006'
and processmonth = '05'
and processbatch = '1') as b
ON b.empno = a.empno ) as count_result
-----------------------------
or just create the view and use inner join then count :b

On Fri, Jun 09, 2006 at 05:29:08PM +0800, kah_hang_ang@toray.com.my wrote:

I had check using ps -auxw|grep postgres & using select * from
pg_stat_activity
and it shows that the is really running and use 99% of the CPU.

Is there any wrong with the query?
As suggested by Jan Cruz, it is to fine tune the performance of the query.
Even if I not fine tune the query it should able to run without problem.

What I not understand is why this query stuck when the connection growth up
to certain limit.

I did try execute the query with the connection more than 45 and it really
stuck there.
I wait for around 10 minutes but it still running.

Was the box swapping during this time? What are the specs on the
machine?

I kill about 10 connections then the query start to run and finish within
30 sec.

May I know what will be the possible cause of this problem?

Regards,
KH Ang

"Jan Cruz"
<malebug@gmail.com> To: "Qingqing Zhou" <zhouqq@cs.toronto.edu>
Sent by: cc: pgsql-bugs@postgresql.org
pgsql-bugs-owner@pos Subject: Re: [BUGS] statement stuck when the connection grew up to 45 or more
tgresql.org

06/02/2006 03:08 PM

This is the statement which having problem:
select count(distinct empno) as counter1 from pay_master_history
where empno in (select empno from pay_batch_basic_history where
organizationid like '015003%')
and processyear = '2006'
and processmonth = '05'
and processbatch = '1'

SELECT COUNT (*) FROM (
SELECT empno as counter1 from pay_master_history as a
INNER JOIN (select empno from pay_batch_basic_history where organizationid
like '015003%' and processyear = '2006'
and processmonth = '05'
and processbatch = '1') as b
ON b.empno = a.empno ) as count_result
-----------------------------
or just create the view and use inner join then count :b

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Was the box swapping during this time?
No.

Spec for the machine:
AMD Opteron 2.4Ghz
4GB RAM
120G hard disk

"Jim C. Nasby"
<jnasby@pervasive To: kah_hang_ang@toray.com.my
.com> cc: Jan Cruz <malebug@gmail.com>, Qingqing Zhou <zhouqq@cs.toronto.edu>,
pgsql-bugs@postgresql.org
06/10/2006 01:33 Subject: Re: [BUGS] statement stuck when the connection grew up to 45 or more
AM

On Fri, Jun 09, 2006 at 05:29:08PM +0800, kah_hang_ang@toray.com.my wrote:

I had check using ps -auxw|grep postgres & using select * from
pg_stat_activity
and it shows that the is really running and use 99% of the CPU.

Is there any wrong with the query?
As suggested by Jan Cruz, it is to fine tune the performance of the

query.

Even if I not fine tune the query it should able to run without problem.

What I not understand is why this query stuck when the connection growth

up

to certain limit.

I did try execute the query with the connection more than 45 and it

really

stuck there.
I wait for around 10 minutes but it still running.

Was the box swapping during this time? What are the specs on the
machine?

I kill about 10 connections then the query start to run and finish within
30 sec.

May I know what will be the possible cause of this problem?

Regards,
KH Ang

"Jan Cruz"

<malebug@gmail.com> To: "Qingqing

Zhou" <zhouqq@cs.toronto.edu>

Sent by: cc:

pgsql-bugs@postgresql.org

pgsql-bugs-owner@pos Subject: Re: [BUGS]

statement stuck when the connection grew up to 45 or more

tgresql.org

06/02/2006 03:08 PM

This is the statement which having problem:
select count(distinct empno) as counter1 from pay_master_history
where empno in (select empno from pay_batch_basic_history

where

organizationid like '015003%')
and processyear = '2006'
and processmonth = '05'
and processbatch = '1'

SELECT COUNT (*) FROM (
SELECT empno as counter1 from pay_master_history as a
INNER JOIN (select empno from pay_batch_basic_history where

organizationid

like '015003%' and processyear = '2006'
and processmonth = '05'
and processbatch = '1') as b
ON b.empno = a.empno ) as count_result
-----------------------------
or just create the view and use inner join then count :b

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461