Thread: Autovacuum on partitioned tables in version 9.1

Hello People,

Before the question, this is the scenario:

I have a postgresql 9.1 cluster with a size of 1.5 TB and composed of 70
databases.

In every database I have 50 tables (master partition), each one have an
associated trigger that insert the record into a child table of its own.

The partition is based on a week period, so every newly created tables
is of the form: tablename_YYYYwWW

The above configuration works great under postgres version 8.4 and
postgres version 9.1, except for one thing in the case of 9.1:

The autovacuum process on version 9.1 keeps vacuuming the master tables
and that takes a lot of time considering the master table have no
records of its own.

The trigger itself insert into the master table, then into the child and
then remove the record from the master, we do that way because we need
to get the inserted row info.

Actually we don't use any features of version 9.1 that are not available
under version 8.4, however I don't want to downgrade to version 8.4 as I
consider that I still have not understood completely how auto vacuuming
process works, and I need to improve on this.

Earlier in this list a user experienced the same behavior:
http://www.postgresql.org/message-id/flat/CABrmO8rEvvbBfhY-NxW2AkNr+3aWdzXEPgWkgrNFLHvTipSHyw@mail.gmail.com

On the same thread another user wrote it could be an issue that is to be
resolved at minor version 9.1.8
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5454344b968d6a189219cfd49af609a3e7d6af33

I currently have  PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled
by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit running on an eight-core
processor and 24 GB RAM with the following options:

maintenance_work_mem = 1GB # pgtune wizard 2014-08-29
checkpoint_completion_target = 0.7 # pgtune wizard 2014-08-29
effective_cache_size = 15GB # pgtune wizard 2014-08-29
work_mem = 40MB # pgtune wizard 2014-08-29
wal_buffers = 4MB # pgtune wizard 2014-08-29
checkpoint_segments = 8 # pgtune wizard 2014-08-29
shared_buffers = 5GB # pgtune wizard 2014-08-29
max_connections = 500 # pgtune wizard 2014-08-29

In order to alleviate the I/O problem I disable autovacuum on all the
master tables like so:

ALTER TABLE public.tablename SET ( autovacuum_enabled = false,
toast.autovacuum_enabled = false);

But I know I can't left the database without vacuuming enabled because
of transaction ID wraparround.

So I need to set up a cron script for this; for every master table there
is a new child table every week then I can start a vacuum process via
cron for the table before the newly created, these tables are only used
for reading after a week.

But I need some clarification on this:

Why is postgresql starting a vacuum on master tables too often ? Why it
takes too much time on version 9.1 ? I guess because it needs to reclaim
unused space due to the insert/remove process, but in version 8.4 that
is unnoticeable.

How do I know which tables needs to be vacuumed ? any sql recipe ?

How do I check when I am near the limit of the transaction ID ?

Do I need to vacuum tables that haven't change a long time ago ?

Anybody have experienced the same behavior and would like to comment on
this ?

Slds.

--
Nestor A. Diaz

TL;DR - the interaction of ANALYZE and inheritance hierarchies seems to be
broken for the uncommon use case where the inserts temporarily remain on the
master table in order to allow RETURNING to work.

Note - I have not played with this scenario personally but


http://www.postgresql.org/message-id/flat/CABrmO8rEvvbBfhY-NxW2AkNr+3aWdzXEPgWkgrNFLHvTipSHyw@mail.gmail.com#CABrmO8rEvvbBfhY-NxW2AkNr+3aWdzXEPgWkgrNFLHvTipSHyw@mail.gmail.com


combined with this post leads me to that conclusion.


Nestor A. Diaz wrote
> The autovacuum process on version 9.1 keeps vacuuming the master tables
> and that takes a lot of time considering the master table have no
> records of its own.
>
> The trigger itself insert into the master table, then into the child and
> then remove the record from the master, we do that way because we need
> to get the inserted row info.

If you say so...but the second paragraph makes your conclusion in the first
paragraph false.

You may wish to read up on Multi-Version Concurrency Control (MVCC)


> Actually we don't use any features of version 9.1 that are not available
> under version 8.4, however I don't want to downgrade to version 8.4 as I
> consider that I still have not understood completely how auto vacuuming
> process works, and I need to improve on this.

Well, you are actually using one right now - community support :)


> Earlier in this list a user experienced the same behavior:
> http://www.postgresql.org/message-id/flat/

> CABrmO8rEvvbBfhY-NxW2AkNr+3aWdzXEPgWkgrNFLHvTipSHyw@.gmail

>
> On the same thread another user wrote it could be an issue that is to be
> resolved at minor version 9.1.8
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5454344b968d6a189219cfd49af609a3e7d6af33
>
> I currently have  PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled
> by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit running on an eight-core
> processor and 24 GB RAM with the following options:

Given that 9.1.14 is current being on 9.1.9 is going to be a problem.

The referenced thread never came to conclusion - the OP never affirmed the
patch fixed their problem - but the patch mentioned affected vacuum while
the preceding conclusion in the post was that ANALYZE was the probable
culprit - specifically that the ANALYZE on the master table cascaded to all
of the children and so took however long it would take to analyze the entire
partition hierarchy.


> In order to alleviate the I/O problem I disable autovacuum on all the
> master tables like so:
>
> ALTER TABLE public.tablename SET ( autovacuum_enabled = false,
> toast.autovacuum_enabled = false);
>
> But I know I can't left the database without vacuuming enabled because
> of transaction ID wraparround.

If things get to a point where this is required it will run regardless of
your configuration.


> So I need to set up a cron script for this; for every master table there
> is a new child table every week then I can start a vacuum process via
> cron for the table before the newly created, these tables are only used
> for reading after a week.
>
> But I need some clarification on this:
>
> Why is postgresql starting a vacuum on master tables too often ? Why it
> takes too much time on version 9.1 ? I guess because it needs to reclaim
> unused space due to the insert/remove process, but in version 8.4 that
> is unnoticeable.

I'd be more concerned with the difference between 8.4 and 9.1 but if you are
indeed physically inserting and the deleting from the master table you need
some kind of vacuum if you want to reclaim that wasted space.

As noted above the ANALYZE is a probable culprit here - and its interaction
with inheritance seems under-documented and incompletely implemented.  I
think this would be more obvious but apparently most people do not write
their trigger sets to leave the inserted record in the master table so as
not to break RETURNING and then delete the record shortly thereafter.
Someone from -hackers needs to comment on this use case and whether
something can and should be done to accommodate it.


> How do I know which tables needs to be vacuumed ? any sql recipe ?
>
> How do I check when I am near the limit of the transaction ID ?
> Do I need to vacuum tables that haven't change a long time ago ?

If a vacuum freeze has been run on a table then in the absence of subsequent
updates it will not require vacuuming.


> Anybody have experienced the same behavior and would like to comment on
> this ?

David J.




--
View this message in context:
http://postgresql.nabble.com/Autovacuum-on-partitioned-tables-in-version-9-1-tp5826595p5826603.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 11/11/2014 07:52 PM, David G Johnston wrote:
> TL;DR - the interaction of ANALYZE and inheritance hierarchies seems to be
> broken for the uncommon use case where the inserts temporarily remain on the
> master table in order to allow RETURNING to work.

Yeah, if I do:

# vacuum verbose public.tablename;

if finish immediately, but if I do:

# vacuum analyze verbose public.tablename;

it takes forever, it seems it analyze every child table.

So for now is just a matter of removing vacuum for the master table and
then doing it manually every night I guess.
Nestor A. Diaz wrote
>> The autovacuum process on version 9.1 keeps vacuuming the master tables
>> and that takes a lot of time considering the master table have no
>> records of its own.
>>
>> The trigger itself insert into the master table, then into the child and
>> then remove the record from the master, we do that way because we need
>> to get the inserted row info.
> If you say so...but the second paragraph makes your conclusion in the first
> paragraph false.

Actually we have trigger like this:


-- Insert Trigger

CREATE TRIGGER insert_tablename_trigger
    BEFORE INSERT ON tablename
    FOR EACH ROW
    EXECUTE PROCEDURE insert_tablename_trigger();

-- Delete Trigger

CREATE TRIGGER delete_tablename_trigger
    AFTER INSERT ON tablename
    FOR EACH ROW
    EXECUTE PROCEDURE delete_tablename_trigger();

-- Insert Function

CREATE OR REPLACE FUNCTION insert_tablename_trigger() RETURNS trigger
AS $BODY$
BEGIN
    EXECUTE 'INSERT INTO partitions.' || quote_ident(_table_name) || '
VALUES ($1.*)' USING NEW;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

-- Delete Function

CREATE OR REPLACE FUNCTION delete_tablename_trigger() RETURNS trigger
AS $BODY$
BEGIN
  DELETE FROM ONLY public."tablename" WHERE "id" = NEW."id"; -- delete
row again.
  RETURN NULL;
END
$BODY$
LANGUAGE plpgsql;

When we insert into the master table, then the row is inserted twice (
in the master and the child ) we return the row inserted at the child
and remove the one inserted at the master, this is how it works.

This is a requirement for most ORM since they usually need an
automatically generated row id column of the table, before that we
didn't set up that way, but in order to keep the application happy we
had do set up that way, or there exist a better approach for this ?

The requirement is simple:

We need partitioning and for every row inserted we need to return the
whole row after an insert, like in: INSERT INTO TABLE  ... RETURNING *;

From the docs:
The optional RETURNING clause causes INSERT to compute and return
value(s) based on each row actually inserted. This is primarily useful
for obtaining values that were supplied by defaults, such as a serial
sequence number. However, any expression using the table's columns is
allowed. The syntax of the RETURNING list is identical to that of the
output list of SELECT.

slds.

--
Nestor A Diaz