Autovacuum issues with truncate and create index ... - Mailing list pgsql-admin

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 insert 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 data) because it take too much time (2-3min for
about200 rows). Some tables have 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
secondkind of tables are analyzed only one time each 5 or 6 days. 

I have tried to decrease autovacuum thresholds for these tables with the following command :
ALTER TABLE tableName SET (autovacuum_vacuum_threshold=20, autovacuum_vacuum_scale_factor=0.01,
autovacuum_analyze_threshold=10,autovacuum_analyze_scale_factor=0.005, autovacuum_vacuum_cost_delay=10,
autovacuum_vacuum_cost_limit=1000);

Then the autovacuum process starts to work on the second kind of tables, but our process blocks into step 3 (truncate)
orstep 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             |                                            current_setting
             

-----------------------------+-------------------------------------------------------------------------------------------------------
 version                     | PostgreSQL 8.4.11 on x86_64-pc-linux-gnu, compiled 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
 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


---
Baptiste LHOSTE
blhoste@alaloop.com

ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
Téléphone : +33 (0) 5 59 41 51 10
www.alaloop.com