Autovacuum issues with truncate and create index ...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 about 200 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 second kind 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) 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             |                                            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@xxxxxxxxxxx 

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



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



[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux