X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1445BD1C4C3 for ; Fri, 16 Apr 2004 05:22:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 11556-02 for ; Fri, 16 Apr 2004 05:22:10 -0300 (ADT) Received: from trade-india.com (unknown [61.16.154.82]) by svr1.postgresql.org (Postfix) with SMTP id 149F9D1BCB8 for ; Fri, 16 Apr 2004 05:22:07 -0300 (ADT) Received: (qmail 7040 invoked from network); 16 Apr 2004 08:08:56 -0000 Received: from valium.trade-india-local.com (HELO trade-india.com) (192.168.0.72) by system66.trade-india-local.com with SMTP; 16 Apr 2004 08:08:56 -0000 Message-ID: <407F9816.2080407@trade-india.com> Date: Fri, 16 Apr 2004 13:53:50 +0530 From: Rajesh Kumar Mallah Organization: Infocom Network Limited User-Agent: Mozilla Thunderbird 0.5 (X11/20040208) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Richard Huxton Cc: Bill Moran , Postgres Performance Subject: Re: [ SOLVED ] select count(*) very slow on an already References: <407D7A89.60508@trade-india.com> <407E8FF6.4050601@potentialtech.com> <407EB605.70502@trade-india.com> <200404151844.33672.dev@archonet.com> In-Reply-To: <200404151844.33672.dev@archonet.com> Content-Type: multipart/alternative; boundary="------------020309060308030400050407" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/202 X-Sequence-Number: 6502 This is a multi-part message in MIME format. --------------020309060308030400050407 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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. > > > --------------020309060308030400050407 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit

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.

  

--------------020309060308030400050407--