Baptiste LHOSTE
2012-12-20 13:40:46 UTC
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
***@alaloop.com
ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
Téléphone : +33 (0) 5 59 41 51 10
www.alaloop.com
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
***@alaloop.com
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-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin