From: Andrzej Nakonieczny <dzemik-pgsql-performance@e-list.pingwin.eu.org>
To: pgsql-performance@postgresql.org
Cc: Jose Ildefonso Camargo Tolosa <ildefonso.camargo@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Craig James <craig_james@emolecules.com>
Subject: Re: Large rows number, and large objects
Date: Wed, 20 Jul 2011 21:33:53 +0200
Message-ID: <4E272DA1.6080205@e-list.pingwin.eu.org> (raw)
In-Reply-To: <CAETJ_S-2CwPD+oCzRmnM4i66S6o-ih_qBzdKDknSBCdJFwQvzA@mail.gmail.com>
References: <BANLkTikXvotWjfWBra=6+66WZKfHkie4AQ@mail.gmail.com>
<BANLkTimrZuk8kvftwuwRakxKD0DRS61hAQ@mail.gmail.com>
<4DFE1A88.4000304@emolecules.com>
<BANLkTi=2tGT+aR13bihXfoOM4UkVS+36Qg@mail.gmail.com>
<CA+TgmoYU73jnHa07ZuRZau3EiCUvYu0aBbf_4MnTHkq1O5uuLw@mail.gmail.com>
<CAETJ_S-2CwPD+oCzRmnM4i66S6o-ih_qBzdKDknSBCdJFwQvzA@mail.gmail.com>
W dniu 20.07.2011 17:57, Jose Ildefonso Camargo Tolosa pisze:
[...]
> Many of the advantages of partitioning have to do with maintenance
> tasks. For example, if you gather data on a daily basis, it's faster
> to drop the partition that contains Thursday's data than it is to do a
> DELETE that finds the rows and deletes them one at a time. And VACUUM
> can be a problem on very large tables as well, because only one VACUUM
> can run on a table at any given time. If the frequency with which the
> table needs to be vacuumed is less than the time it takes for VACUUM
> to complete, then you've got a problem.
>
>
> And.... pg_largeobject table doesn't get vacuumed? I mean, isn't that
> table just as any other table?
Vacuum is a real problem on big pg_largeobject table. I have 1.6 TB
database mostly with large objects and vacuuming that table on fast SAN
takes about 4 hours:
now | start | time | datname |
current_query
---------------------+---------------------+----------+------------+----------------------------------------------
2011-07-20 20:12:03 | 2011-07-20 16:21:20 | 03:50:43 | bigdb |
autovacuum: VACUUM pg_catalog.pg_largeobject
(1 row)
LO generates a lot of dead tuples when object are adding:
relname | n_dead_tup
------------------+------------
pg_largeobject | 246731
Adding LO is very fast when table is vacuumed. But when there is a lot
of dead tuples adding LO is very slow (50-100 times slower) and eats
100% of CPU.
It looks that better way is writing object directly as a bytea on
paritioned tables althought it's a bit slower than LO interface on a
vacuumed table.
Regards,
Andrzej
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: dzemik-pgsql-performance@e-list.pingwin.eu.org, ildefonso.camargo@gmail.com, robertmhaas@gmail.com, craig_james@emolecules.com
Subject: Re: Large rows number, and large objects
In-Reply-To: <4E272DA1.6080205@e-list.pingwin.eu.org>
* 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