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

Re: Disk filling, CPU filling, renegade inserts and deletes?

From: Richard Plotkin <richard(at)richardplotkin(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Disk filling, CPU filling, renegade inserts and deletes?
Date: 2005-04-22 16:29:39
Message-ID: 9c5b2bf6a0f5a0bc0abeb6b6c5cf7973@richardplotkin.com (view raw or flat)
Thread:
Lists: pgsql-performance
I've also now tried looking at pg_class.relpages.  I compared the  
results before and after vacuum.  The results stayed the same, except  
for five rows that increased after the vacuum.  Here is the select on  
those rows after the vacuum:

              relname             | relnamespace | reltype | relowner |  
relam | relfilenode | reltablespace | relpages | reltuples |  
reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind |  
relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs |  
relhasoids | relhaspkey | relhasrules | relhassubclass |    relacl
---------------------------------+--------------+---------+---------- 
+-------+-------------+---------------+----------+----------- 
+---------------+---------------+-------------+-------------+--------- 
+----------+-----------+-------------+----------+----------+--------- 
+------------+------------+-------------+---------------- 
+---------------
  pg_attribute_relid_attnam_index |           11 |       0 |        1 |   
  403 |       16686 |             0 |      292 |     10250 |              
0 |             0 | f           | f           | i       |        2 |     
      0 |           0 |        0 |        0 |       0 | f          | f    
        | f           | f              |
  pg_class_oid_index              |           11 |       0 |        1 |   
  403 |       16690 |             0 |       18 |      2640 |              
0 |             0 | f           | f           | i       |        1 |     
      0 |           0 |        0 |        0 |       0 | f          | f    
        | f           | f              |
  pg_depend_depender_index        |           11 |       0 |        1 |   
  403 |       16701 |             0 |       52 |      6442 |              
0 |             0 | f           | f           | i       |        3 |     
      0 |           0 |        0 |        0 |       0 | f          | f    
        | f           | f              |
  pg_type_oid_index               |           11 |       0 |        1 |   
  403 |       16731 |             0 |        8 |      1061 |              
0 |             0 | f           | f           | i       |        1 |     
      0 |           0 |        0 |        0 |       0 | f          | f    
        | f           | f              |
  pg_depend                       |           11 |   16677 |        1 |   
    0 |       16676 |             0 |       32 |      4200 |              
0 |             0 | t           | f           | r       |        7 |     
      0 |           0 |        0 |        0 |       0 | f          | f    
        | f           | f              | {=r/postgres}



On Apr 20, 2005, at 1:51 PM, Tom Lane wrote:

> Richard Plotkin <richard(at)richardplotkin(dot)com> writes:
>> I'm having a pretty serious problem with postgresql's performance.
>> Currently, I have a cron task that is set to restart and vacuumdb -faz
>> every six hours.  If that doesn't happen, the disk goes from 10% full
>> to 95% full within 2 days (and it's a 90GB disk...with the database
>> being a 2MB download after dump), and the CPU goes from running at
>> around a 2% load to a 99+% load right away (the stats look like a
>> square wave).
>
> Q: what have you got the FSM parameters set to?
>
> Q: what exactly is bloating?  Without knowing which tables or indexes
> are growing, it's hard to speculate about the exact causes.  Use du and
> oid2name, or look at pg_class.relpages after a plain VACUUM.
>
> It's likely that the real answer is "you need to vacuum more often
> than every six hours", but I'm trying not to jump to conclusions.
>
> 			regards, tom lane
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>       message can get through to the mailing list cleanly
>


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2005-04-22 17:28:32
Subject: Re: Index bloat problem?
Previous:From: David RousselDate: 2005-04-22 16:16:36
Subject: Re: Index bloat problem?

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