Thread

1. Re: disk space usage enlarging despite vacuuming

   Ron Snyder <snyder@roguewave.com> — 2003-05-19T23:01:56Z

   
   > -----Original Message-----
   > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
   > Sent: Monday, May 19, 2003 3:29 PM
   > To: Ron Snyder
   > Cc: Tzvetan Tzankov; pgsql-general@postgresql.org
   > Subject: Re: [GENERAL] disk space usage enlarging despite vacuuming 
   > 
   > 
   > Ron Snyder <snyder@roguewave.com> writes:
   > >> What's your turnover rate for updating or deleting large objects?
   > > There's probably only about 10K additions/day, and there 
   > should be about
   > > 7500 deletions/day.
   > 
   > How large are the objects in question?
   
   They average 24K (or less).
   
   > 
   > >> I'm guessing that you have the FSM parameters (in 
   > postgresql.conf) set
   > >> too small to allow the system to keep track of all the 
   > free space in 
   > >> pg_largeobject.
   > 
   > > Doh! I meant to include this info, because I knew you'd want it.
   > > max_fsm_pages is 100K, and max_fsm_relations is left at the 
   > default (of
   > > 100).
   > 
   > 100 is almost certainly too small for max_fsm_relations (we've changed
   > the default to 1000 as of 7.3.something).  How many active 
   > databases do
   > you have, and how many user tables?
   
   In that database cluster, there are 4 databases (template0, template1, pgqv,
   quickview).  A '\d' for the first three says "No relations", and for the
   last one lists 17. (15 tables, 1 view, 1 sequence).  Running the following
   query for the quickview database:
   "Select count(*) from pg_indexes where tablename not like 'pg%';"
   count
   -----
   31
   
   Thanks,
   -ron
   
   

2. Re: disk space usage enlarging despite vacuuming

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-19T23:35:27Z

   Ron Snyder <snyder@roguewave.com> writes:
   >>>> What's your turnover rate for updating or deleting large objects?
   >>> There's probably only about 10K additions/day, and there 
   >>> should be about 7500 deletions/day.
   >> 
   >> How large are the objects in question?
   
   > They average 24K (or less).
   
   So an average update or delete touches at least three pages of
   pg_largeobject, probably more.  It'd probably be reasonable to estimate
   that about 5 * 17500 pages of pg_largeobject have free space on them
   after a typical day's activity.  That means you need 87500 FSM page
   slots just to keep track of pg_largeobject space, never mind what's
   going on in your user tables.
   
   You didn't say how large your user tables are, or what kind of update
   traffic they see, but I'll bet 100K slots is not near enough for you.
   
   >> 100 is almost certainly too small for max_fsm_relations (we've changed
   >> the default to 1000 as of 7.3.something).  How many active 
   >> databases do
   >> you have, and how many user tables?
   
   > In that database cluster, there are 4 databases (template0, template1, pgqv,
   > quickview).  A '\d' for the first three says "No relations", and for the
   > last one lists 17. (15 tables, 1 view, 1 sequence).
   
   Let's see ... in 7.2 there are 30 FSM-able system catalogs per database
   (count the pg_class entries with relkind 'r' or 't').  Ignoring
   template0 which is never vacuumed, you have 105 FSM-able relations in this
   cluster.  I'd suggest bumping up the setting at least a little bit...
   
   			regards, tom lane
   
   

3. Re: disk space usage enlarging despite vacuuming

   Mike Benoit <mikeb@netnation.com> — 2003-05-20T17:06:01Z

   I assume your talking about the MAX_FSM_RELATIONS setting in
   postgresql.conf? 
   
   What are the drawbacks to setting this too high? My database has about
   1million (very small row) inserts, and 1 million deletes each day, with
   1 table exceeding 5.5million rows, and another just under 1million. 
   
   Currently MAX_FSM_RELATIONS is set to 10,000.
   
   select count(*) from pg_class where not relkind in ('i','v');
    count
   -------
      144
   (1 row)
   
   select sum(relpages) from pg_class where relkind in ('r','t');
     sum
   -------
    77918
   (1 row)
   
   I remember reading MAX_FSM_RELATIONS should be higher then the first
   query, and lower then the last query, but thats a huge difference. What
   would be the advantages/disadvantages to setting MAX_FSM_RELATIONS to
   75,000?
   
   Where does MAX_FSM_PAGES fall in to this?
   
   
   On Mon, 2003-05-19 at 16:35, Tom Lane wrote:
   > Ron Snyder <snyder@roguewave.com> writes:
   > >>>> What's your turnover rate for updating or deleting large objects?
   > >>> There's probably only about 10K additions/day, and there 
   > >>> should be about 7500 deletions/day.
   > >> 
   > >> How large are the objects in question?
   > 
   > > They average 24K (or less).
   > 
   > So an average update or delete touches at least three pages of
   > pg_largeobject, probably more.  It'd probably be reasonable to estimate
   > that about 5 * 17500 pages of pg_largeobject have free space on them
   > after a typical day's activity.  That means you need 87500 FSM page
   > slots just to keep track of pg_largeobject space, never mind what's
   > going on in your user tables.
   > 
   > You didn't say how large your user tables are, or what kind of update
   > traffic they see, but I'll bet 100K slots is not near enough for you.
   > 
   > >> 100 is almost certainly too small for max_fsm_relations (we've changed
   > >> the default to 1000 as of 7.3.something).  How many active 
   > >> databases do
   > >> you have, and how many user tables?
   > 
   > > In that database cluster, there are 4 databases (template0, template1, pgqv,
   > > quickview).  A '\d' for the first three says "No relations", and for the
   > > last one lists 17. (15 tables, 1 view, 1 sequence).
   > 
   > Let's see ... in 7.2 there are 30 FSM-able system catalogs per database
   > (count the pg_class entries with relkind 'r' or 't').  Ignoring
   > template0 which is never vacuumed, you have 105 FSM-able relations in this
   > cluster.  I'd suggest bumping up the setting at least a little bit...
   > 
   > 			regards, tom lane
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
   -- 
   Best Regards,
    
   Mike Benoit
   NetNation Communications Inc.
   Systems Engineer
   Tel: 604-684-6892 or 888-983-6600
    ---------------------------------------
    
    Disclaimer: Opinions expressed here are my own and not 
    necessarily those of my employer
   
   
   

4. Re: disk space usage enlarging despite vacuuming

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-20T17:13:00Z

   Mike Benoit <mikeb@netnation.com> writes:
   > I assume your talking about the MAX_FSM_RELATIONS setting in
   > postgresql.conf? 
   
   > What are the drawbacks to setting this too high?
   
   You waste space in shared memory.  IIRC the multiplier is about 50 bytes
   per FSM_RELATION slot, so setting it to 1000 costs you ~50K ... hardly
   enough to sneeze at anymore.
   
   > Currently MAX_FSM_RELATIONS is set to 10,000.
   
   Well, that's half a meg, which might be more than you care to waste
   (certainly the space would be more usefully spent on FSM_PAGES slots).
   Unless you're planning a vast expansion of number of tables or
   databases, I would think 1000 would do ya.
   
   > Where does MAX_FSM_PAGES fall in to this?
   
   RELATIONS is the number of tables to track in FSM.  PAGES is the total
   number of pages to track (across all tables).  You want to be sure you
   can track all the pages that have useful amounts of free space in them.
   
   			regards, tom lane