Date: Thu, 20 Dec 2012 14:40:46 +0100 (CET)
From: Baptiste LHOSTE <blhoste@alaloop.com>
To: pgsql-admin@postgresql.org
Message-ID: <661420693.2636092.1356010846043.JavaMail.root@alaloop.com>
In-Reply-To: <1069806286.2625266.1356008576139.JavaMail.root@alaloop.com>
Subject: 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 everybody,

We are having issues with the autovacuum process.

Our database is composed by two kinds of tables :
 - the first ones are partitions,
 - the second ones are classic tables.

Each five minutes we execute the following process :
 - we drop constraint of the target partition
 - we drop indexes (2) of the target partition
 - we truncate all data of the target partition
 - we copy new data (around 150 000 rows) in the target partition
 - we create indexes (2) in the target partition
 - we create the constraint of the target partition
 - we aggregate data from main table of the previous partition and we inser=
t them in the second kind of tables (max 200 rows)
 - finally we delete old data of the second kind of tables

Our problem is that we can not perform the last action (deletion of old dat=
a) because it take too much time (2-3min for about 200 rows). Some tables h=
ave about 5 000 000 000 of rows to delete so we have to proceed this action.

So I look in the pg_stat_all_tables, and I see that each partition of first=
 kind of tables is analyzed once a day, but second kind of tables are analy=
zed only one time each 5 or 6 days.

I have tried to decrease autovacuum thresholds for these tables with the fo=
llowing command :
ALTER TABLE tableName SET (autovacuum_vacuum_threshold=3D20, autovacuum_vac=
uum_scale_factor=3D0.01, autovacuum_analyze_threshold=3D10, autovacuum_anal=
yze_scale_factor=3D0.005, autovacuum_vacuum_cost_delay=3D10, autovacuum_vac=
uum_cost_limit=3D1000);

Then the autovacuum process starts to work on the second kind of tables, bu=
t our process blocks into step 3 (truncate) or step 5 (create index).

As soon as I reset the autovacuum thresholds for the second kind of tables,=
 our process run again perfectly.

Can our process could create deadlock with the autovacuum process ?

Do we do something wrong in the autovacuum process configuration ?

Our postgresql is on a server with 16 CPUs 16Go RAM 4Go SWAP.


            name             |                                            c=
urrent_setting=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
-----------------------------+---------------------------------------------=
----------------------------------------------------------
 version                     | PostgreSQL 8.4.11 on x86_64-pc-linux-gnu, co=
mpiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
 autovacuum_naptime          | 30s
 checkpoint_segments         | 64
 constraint_exclusion        | partition
 custom_variable_classes     | alaloop
 effective_cache_size        | 14GB
 external_pid_file           | /var/run/postgresql/8.4-main.pid
 lc_collate                  | en_US.UTF-8
 lc_ctype                    | en_US.UTF-8
 listen_addresses            | *
 log_autovacuum_min_duration | 0
 log_line_prefix             | %t=20
 maintenance_work_mem        | 1GB
 max_connections             | 100
 max_locks_per_transaction   | 256
 max_prepared_transactions   | 100
 max_stack_depth             | 5MB
 port                        | 5432
 server_encoding             | UTF8
 shared_buffers              | 756MB
 ssl                         | on
 stats_temp_directory        | /dev/shm
 temp_buffers                | 1536
 TimeZone                    | localtime
 unix_socket_directory       | /var/run/postgresql
 work_mem                    | 756MB


---=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