Thread

1. How to avoid vacuuming a huge logging table

   Mark Stosberg <mark@summersault.com> — 2007-02-21T18:02:16Z

   Our application has a table that is only logged to, and infrequently
   used for reporting. There generally no deletes and updates.
   
   Recently, the shear size (an estimated 36 million rows) caused a serious
   problem because it prevented a "vacuum analyze" on the whole database
   from finishing in a timely manner.
   
   As I understand, a table with this usage pattern wouldn't need to be
   vacuumed anyway.
   
   I'm looking for general advice from people who have faced the same
   issue. I'm looking at a number of alternatives:
   
   1. Once a month, we could delete and archive old rows, for possible
   re-import later if we need to report on them. It would seem this would
   need to be done as proper insert statements for re-importing. (Maybe
   there is a solution for that with table partitioning? )
   
   2. We could find a way to exclude the table for vacuuming, and let it
   grow even larger. Putting the table in it's own database would
   accomplish that, but it would nice to avoid the overhead of a second
   database connection.
   
   3. Take a really different approach. Log in CSV format to text files
   instead, And only import the date ranges we need "on demand" if a report
   is requested on the data.
   
   Thanks for any tips.
   
       Mark
   
   

2. Re: How to avoid vacuuming a huge logging table

   Greg Sabino Mullane <greg@turnstep.com> — 2007-02-21T19:08:58Z

   -----BEGIN PGP SIGNED MESSAGE-----
   Hash: RIPEMD160
   
   
   > Take a really different approach. Log in CSV format to text files
   > instead, And only import the date ranges we need "on demand" if a report
   > is requested on the data.
   
   Seems like more work than a separate database to me. :)
   
   > 2. We could find a way to exclude the table for vacuuming, and let it
   > grow even larger. Putting the table in it's own database would
   > accomplish that, but it would nice to avoid the overhead of a second
   > database connection.
   
   Specific exclusions is generally what I've done for similar problems in 
   the past. If you can live without the per-database summary at the end of 
   the vacuum, you can do something like this:
   
   SET search_path = 'pg_catalog';
   SELECT set_config('search_path',
     current_setting('search_path')||','||quote_ident(nspname),'false')
     FROM pg_namespace
     WHERE nspname <> 'pg_catalog'
     ORDER BY 1;
   
   \t
   \o pop
   SELECT 'vacuum verbose analyze '||quote_ident(relname)||';' 
     FROM pg_class
     WHERE relkind = 'r'
     AND relname <> 'ginormous_table'
     ORDER BY 1;
   \o
   \i pop
   
   Or put any tables you don't want vacuumed by this script into their own schema:
   
   ...
   SELECT 'vacuum verbose analyze '||quote_ident(relname)||';' 
     FROM pg_class c, pg_namespace n
     WHERE relkind = 'r'
     AND relnamespace = n.oid
     AND nspname = 'novac'
     ORDER BY 1;
   ...
   
   Just flip the equality operator, and you've got a way to vacuum just those 
   excluded tables, for example once a week during a slow time.
   
   
   - --
   Greg Sabino Mullane greg@turnstep.com
   End Point Corporation
   PGP Key: 0x14964AC8 200702211402
   http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
   -----BEGIN PGP SIGNATURE-----
   
   iD8DBQFF3JeivJuQZxSWSsgRA7LZAKC7Sfz4XBTAfHuk1CpR+eBl7ixBIACeML8N
   1W2sLLI4HMtdyV4EOoh2XkY=
   =eTUi
   -----END PGP SIGNATURE-----
   
   
   
   

3. Re: How to avoid vacuuming a huge logging table

   Greg Sabino Mullane <greg@turnstep.com> — 2007-02-21T21:58:33Z

   -----BEGIN PGP SIGNED MESSAGE-----
   Hash: RIPEMD160
   
   
   A minor correction to my earlier post: I should have specified the 
   schema as well in the vacuum command for tables with the same 
   name in different schemas:
   
   SET search_path = 'pg_catalog';
   SELECT set_config('search_path',
     current_setting('search_path')||','||quote_ident(nspname),'false')
     FROM pg_namespace
     WHERE nspname <> 'pg_catalog'
     ORDER BY 1;
   
   \t
   \o pop
   SELECT 'vacuum verbose analyze '||quote_ident(nspname)||'.'||quote_ident(relname)||';' 
     FROM pg_class c, pg_namespace n
     WHERE relkind = 'r'
     AND relnamespace = n.oid
     AND nspname = 'novac'
     ORDER BY 1;
   \o
   \i pop
   
   - --
   Greg Sabino Mullane greg@turnstep.com
   End Point Corporation
   PGP Key: 0x14964AC8 200702211652
   http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
   -----BEGIN PGP SIGNATURE-----
   
   iD8DBQFF3L+XvJuQZxSWSsgRAwzeAKDz+YmLmm9K0of/ObjUux/P7fg7jwCfeSoK
   TfVGoSyThrdFjlGXWn1aEGI=
   =/jBZ
   -----END PGP SIGNATURE-----
   
   
   
   

4. Re: How to avoid vacuuming a huge logging table

   D'Arcy Cain <darcy@druid.net> — 2007-02-21T22:04:06Z

   On Wed, 21 Feb 2007 21:58:33 -0000
   "Greg Sabino Mullane" <greg@turnstep.com> wrote:
   > SELECT 'vacuum verbose analyze '||quote_ident(nspname)||'.'||quote_ident(relname)||';' 
   >   FROM pg_class c, pg_namespace n
   >   WHERE relkind = 'r'
   >   AND relnamespace = n.oid
   >   AND nspname = 'novac'
   >   ORDER BY 1;
   
   I assume you meant "AND nspname != 'novac'"
   
   -- 
   D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
   http://www.druid.net/darcy/                |  and a sheep voting on
   +1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.