Thread: statement stuck when the connection grew up to 45 or more

statement stuck when the connection grew up to 45 or more

From
kah_hang_ang@toray.com.my
Date:
Hi,

I'm using Postgresql 8.1.3.

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.

Could anyone give me some idea how could this be?
Is it a bug of PostgreSQL?

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'

Thanks!

Re: statement stuck when the connection grew up to 45 or more

From
"Qingqing Zhou"
Date:
<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

Infinite increment of postgre.exe in taskmanager

From
"Stefan van Aalst"
Date:
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

Re: statement stuck when the connection grew up to 45 or more

From
"Jan Cruz"
Date:
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

Re: statement stuck when the connection grew up to 45 or more

From
kah_hang_ang@toray.com.my
Date:
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
45or 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

Re: statement stuck when the connection grew up to 45 or more

From
"Jim C. Nasby"
Date:
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
45or 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

Re: statement stuck when the connection grew up to 45 or more

From
kah_hang_ang@toray.com.my
Date:
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
ormore           
                      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