Date: Thu, 3 Jan 2013 11:26:37 +0100 (CET)
From: Baptiste LHOSTE <blhoste@alaloop.com>
To: Kevin Grittner <kgrittn@mail.com>
Cc: pgsql-admin@postgresql.org, Sylvain CAILLET <scaillet@alaloop.com>
Message-ID: <1578813003.348817.1357208797415.JavaMail.root@alaloop.com>
In-Reply-To: <20121231164107.223680@gmx.com>
Subject: Re: Autovacuum issues with truncate and create index ...
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Hi,

> Could you show that output you base that on?

EXPLAIN on table which was recently analyzed by the autovacuum process :=20

explain delete from agg_t1343_incoming_a3_src_net_and_dst_net_f5 where star=
t_date < 1353317127200;
                                                                         QU=
ERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20
---------------------------------------------------------------------------=
-----------------------------------------------------------Index Scan using=
 agg_t1343_incoming_a3_src_net_and_dst_net_f5_start_date on agg_t1343_incom=
ing_a3_src_net_and_dst_net_f5 (cost=3D0.00..9.01 rows=3D41 width=3D6)
    Index Cond: (start_date < 1353317127200::bigint)
(2 rows)

Here you can find the duration and the number of deleted rows of previous q=
uery.
Query duration :  4s 538ms  for 15960 rows deleted


EXPLAIN on table which was analyzed four days ago by the autovacuum process=
 :=20

explain delete from agg_t1187_incoming_a6_dst_port_and_proto_f5 where start=
_date < 1353317127200;
                                            QUERY PLAN=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20
---------------------------------------------------------------------------=
-----------------------
 Seq Scan on agg_t1187_incoming_a6_dst_port_and_proto_f5  (cost=3D0.00..580=
63.86 rows=3D3269 width=3D6)
   Filter: (start_date < 1353317127200::bigint)
(2 rows)

Here you can find the duration and the number of deleted rows of previous q=
uery.
Query duration :  52s  368ms  for 21130 rows deleted

The first EXPLAIN mention that an Index scan is used but the second one men=
tion that a sequence scan is used.

That why we concluded that we had to have our index up-to-date.


> When the bottleneck is disk I/O the CPUs count is not going to
> help. Threads which have not been context-switched out, but are
> sitting waiting for the electric motors to drag the disk arm to the
> right cylinder probably don't count against the load average.

My mistake, when I said that the server had a 0.56 load over the last 15 mi=
nutes, it was before the change on autovacuum thresholds, because after tha=
t the load just blow up.=20

> Note that while three autovacuum processes normally don't cause any
> grief, you seem to be near the tipping point anyway, so it may be a
> case of "the straw that broke the camel's back". Especially since
> you made autovacuum many times more resource-hungry than it is by
> default.

We tried to change the autovacuum thresholds without changing autovacuum_va=
cuum_cost_delay and autovacuum_vacuum_cost_limit.

The server managed to handle the work during 25 minutes then it just starte=
d to take too much time on CREATE INDEX statements.

I just figure that when we perform the full process (with deletion of old d=
ata), CREATE INDEX and TUNCATE take too much time.

At the same time the autovacuum process seems to perform more tasks on seco=
nd type of tables.

Best regards,

Baptiste

---=20
Baptiste LHOSTE=20
blhoste@alaloop.com=20

ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart=20
T=C3=A9l=C3=A9phone : +33 (0) 5 59 41 51 10=20
www.alaloop.com=20


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin