Was the box swapping during this time?
Spec for the machine:
AMD Opteron 2.4Ghz
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>,
06/10/2006 01:33 Subject: Re: [BUGS] statement stuck when the connection grew up to 45 or more
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
> 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
> 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
> to certain limit.
> I did try execute the query with the connection more than 45 and it
> 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
> 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?
> KH Ang
> "Jan Cruz"
> <malebug(at)gmail(dot)com> To: "Qingqing
> Sent by: cc:
> pgsql-bugs-owner(at)pos Subject: Re: [BUGS]
statement stuck when the connection grew up to 45 or more
> 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
> 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
> 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 Sakata||Date: 2006-06-15 07:11:16|
|Subject: BUG #2480: Installation Error of RMP for RHEL4|
|Previous:||From: Bruce Momjian||Date: 2006-06-14 23:19:16|
|Subject: Re: BUG #2084: Add WIN1253 to CLIENT_ENCODING encodings|