pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Richard Huxton <dev@archonet.com>
Cc: Bill Moran <wmoran@potentialtech.com>
Cc: Postgres Performance <pgsql-performance@postgresql.org>
Subject: Re: [ SOLVED ] select count(*)  very slow on an already
Date: Fri, 16 Apr 2004 13:53:50 +0530
Message-ID: <407F9816.2080407@trade-india.com> (raw)
In-Reply-To: <200404151844.33672.dev@archonet.com>
References: <407D7A89.60508@trade-india.com>
	<407E8FF6.4050601@potentialtech.com>
	<407EB605.70502@trade-india.com>
	<200404151844.33672.dev@archonet.com>



I am running an update on the same table

update rfis set inquiry_status='APPROVED' where inquiry_status='a';

Its running for past 20 mins. and top output is below.
The PID which is executing the query above is 6712. Can anyone
tell me why it is in an uninterruptable sleep and does it relate
to the apparent poor performance? Is it problem with the disk
hardware. I know at nite this query will run reasonably fast.

I am running on a decent hardware .



Regds
mallah.



 1:41pm  up 348 days, 21:10,  1 user,  load average: 11.59, 13.69, 11.49
85 processes: 83 sleeping, 1 running, 0 zombie, 1 stopped
CPU0 states:  8.1% user,  2.3% system,  0.0% nice, 89.0% idle
CPU1 states:  3.3% user,  2.3% system,  0.0% nice, 93.2% idle
CPU2 states:  7.4% user,  1.4% system,  0.0% nice, 90.0% idle
CPU3 states:  9.3% user,  7.4% system,  0.0% nice, 82.2% idle
Mem:  2064796K av, 2053964K used,   10832K free,       0K shrd,   22288K 
buff
Swap: 2048244K av,   88660K used, 1959584K free                 1801532K 
cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
* 6712 postgres  16   0 86592  84M 83920 D    11.1  4.1   1:36 postmaster*
13103 postgres  15   0 54584  53M 52556 S     3.5  2.6   0:01 postmaster
13034 root      16   0  1072 1072   848 R     2.1  0.0   0:02 top
13064 postgres  15   0 67256  65M 64516 D     2.1  3.2   0:01 postmaster
13088 postgres  16   0 43324  42M 40812 D     2.1  2.0   0:00 postmaster
13076 postgres  15   0 49016  47M 46628 S     1.9  2.3   0:00 postmaster
26931 postgres  15   0 84880  82M 83888 S     1.7  4.1   3:52 postmaster
13107 postgres  15   0 18400  17M 16488 S     1.5  0.8   0:00 postmaster
13068 postgres  15   0 44632  43M 42324 D     1.3  2.1   0:00 postmaster
13074 postgres  15   0 68852  67M 66508 D     1.3  3.3   0:00 postmaster
13108 postgres  15   0 11692  11M 10496 S     1.3  0.5   0:00 postmaster
13075 postgres  15   0 50860  49M 47680 S     1.1  2.4   0:04 postmaster
13066 postgres  15   0 56112  54M 53724 S     0.9  2.7   0:01 postmaster
13109 postgres  15   0 14528  14M 13272 S     0.9  0.7   0:00 postmaster
24454 postgres  15   0  2532 2380  1372 S     0.7  0.1  11:58 postmaster
   12 root      15   0     0    0     0 SW    0.5  0.0 816:30 bdflush
24455 postgres  15   0  1600 1476  1380 S     0.5  0.0   9:11 postmaster
12528 postgres  15   0 84676  82M 79920 S     0.3  4.0   0:02 postmaster
12575 postgres  15   0 76660  74M 75796 D     0.3  3.7   0:09 postmaster
13038 postgres  15   0 48952  47M 46436 D     0.3  2.3   0:00 postmaster
13069 postgres  15   0 57464  56M 54852 S     0.3  2.7   0:00 postmaster
13102 postgres  15   0 17864  17M 16504 D     0.3  0.8   0:00 postmaster



















Richard Huxton wrote:

>On Thursday 15 April 2004 17:19, Rajesh Kumar Mallah wrote:
>  
>
>>Bill Moran wrote:
>>    
>>
>>>Rajesh Kumar Mallah wrote:
>>>      
>>>
>>>>Hi,
>>>>
>>>>The problem was solved by reloading the Table.
>>>>the query now takes only 3 seconds. But that is
>>>>not a solution.
>>>>        
>>>>
>>>If dropping/recreating the table improves things, then we can reasonably
>>>assume that the table is pretty active with updates/inserts.  Correct?
>>>      
>>>
>>Yes the table results from an import process and under goes lots
>>of inserts and updates , but thats before the vacuum full operation.
>>the table is not accessed during vacuum. What i want to know is
>>is there any wat to automate the dumping and reload of a table
>>individually. will the below be safe and effective:
>>    
>>
>
>Shouldn't be necessary assuming you vacuum (not full) regularly. However, 
>looking back at your original posting, the vacuum output doesn't seem to show 
>any rows that need removing.
>
># VACUUM full verbose eyp_rfi;
>INFO:  vacuuming "public.eyp_rfi"
>INFO:  "eyp_rfi": found 0 removable, 505960 nonremovable row versions in 
>71987 pages
>DETAIL:  0 dead row versions cannot be removed yet.
>
>Since your select count(*) showed 505960 rows, I can't see how 
>dropping/replacing could make a difference on a sequential scan. Since we're 
>not using any indexes I don't see how it could be related to that.
>
>  
>
>>begin work;
>>create table new_tab AS select * from tab;
>>truncate table tab;
>>insert into tab select * from new_tab;
>>drop table new_tab;
>>commit;
>>analyze tab;
>>
>>i havenot tried it but plan to do so.
>>but i feel insert would take ages to update
>>the indexes if any.
>>    
>>
>
>It will have to update them, which will take time.
>
>  
>
>>BTW
>>
>>is there any way to disable checks and triggers on
>>a table temporarily while loading data (is updating
>>reltriggers in pg_class safe?)
>>    
>>
>
>You can take a look at pg_restore and copy how it does it.
>
>  
>

view thread (12+ messages)  latest in thread

Message-ID: <407F9816.2080407@trade-india.com>
Permalink:  ../407F9816.2080407@trade-india.com/
Also on:    postgresql.org/message-id/407F9816.2080407@trade-india.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mallah@trade-india.com, dev@archonet.com, wmoran@potentialtech.com
  Subject: Re: [ SOLVED ] select count(*)  very slow on an already
  In-Reply-To: <407F9816.2080407@trade-india.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox