Skip site navigation (1) Skip section navigation (2)

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

From: kah_hang_ang(at)toray(dot)com(dot)my
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
Cc: Jan Cruz <malebug(at)gmail(dot)com>,Qingqing Zhou <zhouqq(at)cs(dot)toronto(dot)edu>,pgsql-bugs(at)postgresql(dot)org
Subject: Re: statement stuck when the connection grew up to 45 or more
Date: 2006-06-15 05:31:24
Message-ID: OFDE52F97E.52034884-ON4825718E.0019C87E@pengroup.com.my (view raw or flat)
Thread:
Lists: pgsql-bugs




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(at)pervasive        To:       kah_hang_ang(at)toray(dot)com(dot)my                                                     
                      .com>                    cc:       Jan Cruz <malebug(at)gmail(dot)com>, Qingqing Zhou <zhouqq(at)cs(dot)toronto(dot)edu>,          
                                                pgsql-bugs(at)postgresql(dot)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(at)toray(dot)com(dot)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(at)gmail(dot)com>         To:       "Qingqing
Zhou" <zhouqq(at)cs(dot)toronto(dot)edu>
>                       Sent by:                    cc:
pgsql-bugs(at)postgresql(dot)org

>                       pgsql-bugs-owner(at)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(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461




pgsql-bugs by date

Next:From: Tetsuo SakataDate: 2006-06-15 07:11:16
Subject: BUG #2480: Installation Error of RMP for RHEL4
Previous:From: Bruce MomjianDate: 2006-06-14 23:19:16
Subject: Re: BUG #2084: Add WIN1253 to CLIENT_ENCODING encodings

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group