Обсуждение: BUG #13837: Files in pg_committs not deleted
The following bug has been logged on the website: Bug reference: 13837 Logged by: Alain Laporte Email address: alain.laporte12345@gmail.com PostgreSQL version: 9.4.5 Operating system: Linux Description: Hi, I use PostgreSQL 9.4.5 and I have activated the parameter track_commit_timestamp to use BDR (0.9.3) and to be able to replicate two databases. A directory pg_committs was created (this directory is named pg_commit_ts in PostgreSQL 9.5 => http://www.postgresql.org/docs/9.5/static/storage-file-layout.html), but, his content is not periodically deleted (all files stays after 2 weeks of running). I'm not sure if it's a bug (in PostgreSQL or BDR) or if my configuration is wrong. So, see bellow my configuration: name | setting | ------------------------------------+--------------------------------------------------+ allow_system_table_mods | off | application_name | psql | archive_command | /usr/local/bin/dbconfig_archive_command %p %f | archive_mode | on | archive_timeout | 0 | array_nulls | on | authentication_timeout | 1min | auto_explain.log_analyze | off | auto_explain.log_buffers | off | auto_explain.log_format | text | auto_explain.log_min_duration | -1 | auto_explain.log_nested_statements | off | auto_explain.log_timing | on | auto_explain.log_triggers | off | auto_explain.log_verbose | off | autovacuum | on | autovacuum_analyze_scale_factor | 0.1 | autovacuum_analyze_threshold | 50 | autovacuum_freeze_max_age | 200000000 | autovacuum_max_workers | 3 | autovacuum_multixact_freeze_max_age | 400000000 | autovacuum_naptime | 1min | autovacuum_vacuum_cost_delay | 20ms | autovacuum_vacuum_cost_limit | -1 | autovacuum_vacuum_scale_factor | 0.2 | autovacuum_vacuum_threshold | 50 | autovacuum_work_mem | -1 | backslash_quote | safe_encoding | bdr.conflict_logging_include_tuples | on | bdr.default_apply_delay | 0 | bdr.do_not_replicate | off | bdr.log_conflicts_to_table | off | bdr.permit_ddl_locking | on | bdr.permit_unsafe_ddl_commands | off | bdr.skip_ddl_locking | off | bdr.skip_ddl_replication | off | bdr.synchronous_commit | off | bdr.temp_dump_directory | /tmp | bgwriter_delay | 200ms | bgwriter_lru_maxpages | 100 | bgwriter_lru_multiplier | 2 | block_size | 8192 | bonjour | off | bonjour_name | | bytea_output | hex | check_function_bodies | on | checkpoint_completion_target | 0.9 | checkpoint_segments | 100 | checkpoint_timeout | 5min | checkpoint_warning | 30s | client_encoding | SQL_ASCII | client_min_messages | notice | commit_delay | 0 | commit_siblings | 5 | config_file | /mnt/streams0/pgsql/localdb/data/postgresql.conf | constraint_exclusion | partition | cpu_index_tuple_cost | 0.005 | cpu_operator_cost | 0.0025 | cpu_tuple_cost | 0.01 | cursor_tuple_fraction | 0.1 | data_checksums | on | data_directory | /mnt/streams0/pgsql/localdb/data | DateStyle | ISO, YMD | db_user_namespace | off | deadlock_timeout | 1s | debug_assertions | off | debug_pretty_print | on | debug_print_parse | off | debug_print_plan | off | debug_print_rewritten | off | default_sequenceam | | default_statistics_target | 400 | default_tablespace | | default_text_search_config | pg_catalog.english | default_transaction_deferrable | off | default_transaction_isolation | read committed | default_transaction_read_only | off | default_with_oids | off | dynamic_library_path | $libdir | dynamic_shared_memory_type | posix | effective_cache_size | 969MB | effective_io_concurrency | 1 | enable_bitmapscan | on | enable_hashagg | on | enable_hashjoin | on | enable_indexonlyscan | on | enable_indexscan | on | enable_material | on | enable_mergejoin | on | enable_nestloop | on | enable_seqscan | on | enable_sort | on | enable_tidscan | on | escape_string_warning | on | event_source | PostgreSQL | exit_on_error | off | external_pid_file | | extra_float_digits | 0 | from_collapse_limit | 8 | fsync | on | full_page_writes | on | geqo | on | geqo_effort | 5 | geqo_generations | 0 | geqo_pool_size | 0 | geqo_seed | 0 | geqo_selection_bias | 2 | geqo_threshold | 12 | gin_fuzzy_search_limit | 0 | hba_file | /mnt/streams0/pgsql/localdb/data/pg_hba.conf | hot_standby | off | hot_standby_feedback | off | huge_pages | try | ident_file | /mnt/streams0/pgsql/localdb/data/pg_ident.conf | ignore_checksum_failure | off | ignore_system_indexes | off | integer_datetimes | on | IntervalStyle | postgres | join_collapse_limit | 8 | krb_caseins_users | off | krb_server_keyfile | FILE:/etc/postgresql-common/krb5.keytab | lc_collate | C | lc_ctype | C | lc_messages | C | lc_monetary | C | lc_numeric | C | lc_time | C | listen_addresses | * | lo_compat_privileges | off | local_preload_libraries | | lock_timeout | 0 | log_autovacuum_min_duration | -1 | log_checkpoints | off | log_connections | off | log_destination | syslog | log_directory | pg_log | log_disconnections | off | log_duration | off | log_error_verbosity | default | log_executor_stats | off | log_file_mode | 0600 | log_filename | postgresql-%Y-%m-%d_%H%M%S.log | log_hostname | off | log_line_prefix | | log_lock_waits | on | log_min_duration_statement | -1 | log_min_error_statement | error | log_min_messages | warning | log_parser_stats | off | log_planner_stats | off | log_rotation_age | 1d | log_rotation_size | 10MB | log_statement | ddl | log_statement_stats | off | log_temp_files | 0 | log_timezone | UTC | log_truncate_on_rotation | off | logging_collector | off | maintenance_work_mem | 16MB | max_connections | 408 | max_files_per_process | 1000 | max_function_args | 100 | max_identifier_length | 63 | max_index_keys | 32 | max_locks_per_transaction | 64 | max_pred_locks_per_transaction | 64 | max_prepared_transactions | 0 | max_replication_slots | 20 | max_stack_depth | 7MB | max_standby_archive_delay | 30s | max_standby_streaming_delay | 30s | max_wal_senders | 20 | max_worker_processes | 20 | password_encryption | on | pg_stat_statements.max | 10000 | pg_stat_statements.save | on | pg_stat_statements.track | all | pg_stat_statements.track_utility | on | port | 5432 | post_auth_delay | 0 | pre_auth_delay | 0 | quote_all_identifiers | off | random_page_cost | 3 | restart_after_crash | on | search_path | "$user",public | segment_size | 1GB | seq_page_cost | 1 | server_encoding | UTF8 | server_version | 9.4.5 | server_version_num | 90405 | session_preload_libraries | | session_replication_role | origin | shared_buffers | 639MB | shared_preload_libraries | pg_stat_statements, auto_explain, bdr | sql_inheritance | on | ssl | off | ssl_ca_file | | ssl_cert_file | server.crt | ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL | ssl_crl_file | | ssl_ecdh_curve | prime256v1 | ssl_key_file | server.key | ssl_prefer_server_ciphers | on | ssl_renegotiation_limit | 0 | standard_conforming_strings | on | statement_timeout | 0 | stats_temp_directory | /dev/shm | superuser_reserved_connections | 3 | synchronize_seqscans | on | synchronous_commit | off | synchronous_standby_names | | syslog_facility | local4 | syslog_ident | postgres | tcp_keepalives_count | 5 | tcp_keepalives_idle | 600 | tcp_keepalives_interval | 10 | temp_buffers | 8MB | temp_file_limit | -1 | temp_tablespaces | | TimeZone | localtime | timezone_abbreviations | Default | trace_notify | off | trace_recovery_messages | log | trace_sort | off | track_activities | on | track_activity_query_size | 4096 | track_commit_timestamp | on | track_counts | on | track_functions | all | track_io_timing | off | transaction_deferrable | off | transaction_isolation | read committed | transaction_read_only | off | transform_null_equals | off | unix_socket_directories | /var/run/postgresql | unix_socket_group | | unix_socket_permissions | 0777 | update_process_title | on | vacuum_cost_delay | 0 | vacuum_cost_limit | 200 | vacuum_cost_page_dirty | 20 | vacuum_cost_page_hit | 1 | vacuum_cost_page_miss | 10 | vacuum_defer_cleanup_age | 0 | vacuum_freeze_min_age | 50000000 | vacuum_freeze_table_age | 150000000 | vacuum_multixact_freeze_min_age | 5000000 | vacuum_multixact_freeze_table_age | 150000000 | wal_block_size | 8192 | wal_buffers | 16MB | wal_keep_segments | 0 | wal_level | logical | wal_log_hints | off | wal_receiver_status_interval | 10s | wal_receiver_timeout | 1min | wal_segment_size | 16MB | wal_sender_timeout | 1min | wal_sync_method | fdatasync | wal_writer_delay | 200ms | work_mem | 3MB | xmlbinary | base64 | xmloption | content | zero_damaged_pages | off | Thank you advance, Alain.
On Tue, Dec 29, 2015 at 6:32 AM, <alain.laporte12345@gmail.com> wrote: > The following bug has been logged on the website: > > Bug reference: 13837 > Logged by: Alain Laporte > Email address: alain.laporte12345@gmail.com > PostgreSQL version: 9.4.5 > Operating system: Linux > Description: > > Hi, > > I use PostgreSQL 9.4.5 and I have activated the parameter > track_commit_timestamp to use BDR (0.9.3) and to be able to replicate two > databases. A directory pg_committs was created (this directory is named > pg_commit_ts in PostgreSQL 9.5 => > http://www.postgresql.org/docs/9.5/static/storage-file-layout.html), but, > his content is not periodically deleted (all files stays after 2 weeks of > running). In stock PostgreSQL, pg_commit_ts is truncated when the cluster-wide oldest xid is advanced by autovacuum (or manual vacuum freeze of all databases including templates). With the default autovacuum_freeze_max_age setting, that'll take more than a couple of weeks unless you're doing somewhere around 15 million transactions per day. (I guess that BDR-patched 9.4 is the same, but I don't know.) The documentation describes how autovacuum_freeze_max_age affects the space occupied by pg_clog[1], but the same thing applies to pg_commit_ts. pg_clog uses 2 bits per xid, and it looks like pg_commit_ts uses 10 bytes per xid, so the default autovacuum_freeze_max_age gives you ~50MB of pg_clog and ~2GB of pg_commit_ts. I wonder if doc/src/sgml/maintenance.sgml should be updated to reflect this, maybe with something along the lines of the attached patch. (Isn't it a bit strange that we say that the *sole* disadvantage of setting autovacuum_freeze_max_age to a higher number is disk space usage? Freezing later also has consequences for whether you'll actually be able to complete the freeze before wraparound, especially if you set it to 2 billion as recommended.) [1] http://www.postgresql.org/docs/devel/static/routine-vacuuming.html -- Thomas Munro http://www.enterprisedb.com
Вложения
Finally, pg_committs is well truncated according to your explanation. So, there are no bug in PostgreSQL or BDR. Thanks! Alain. On Tue, Dec 29, 2015 at 4:24 AM, Thomas Munro <thomas.munro@enterprisedb.com > wrote: > On Tue, Dec 29, 2015 at 6:32 AM, <alain.laporte12345@gmail.com> wrote: > > The following bug has been logged on the website: > > > > Bug reference: 13837 > > Logged by: Alain Laporte > > Email address: alain.laporte12345@gmail.com > > PostgreSQL version: 9.4.5 > > Operating system: Linux > > Description: > > > > Hi, > > > > I use PostgreSQL 9.4.5 and I have activated the parameter > > track_commit_timestamp to use BDR (0.9.3) and to be able to replicate two > > databases. A directory pg_committs was created (this directory is named > > pg_commit_ts in PostgreSQL 9.5 => > > http://www.postgresql.org/docs/9.5/static/storage-file-layout.html), > but, > > his content is not periodically deleted (all files stays after 2 weeks of > > running). > > In stock PostgreSQL, pg_commit_ts is truncated when the cluster-wide > oldest xid is advanced by autovacuum (or manual vacuum freeze of all > databases including templates). With the default > autovacuum_freeze_max_age setting, that'll take more than a couple of > weeks unless you're doing somewhere around 15 million transactions per > day. (I guess that BDR-patched 9.4 is the same, but I don't know.) > > The documentation describes how autovacuum_freeze_max_age affects the > space occupied by pg_clog[1], but the same thing applies to > pg_commit_ts. pg_clog uses 2 bits per xid, and it looks like > pg_commit_ts uses 10 bytes per xid, so the default > autovacuum_freeze_max_age gives you ~50MB of pg_clog and ~2GB of > pg_commit_ts. > > I wonder if doc/src/sgml/maintenance.sgml should be updated to reflect > this, maybe with something along the lines of the attached patch. > > (Isn't it a bit strange that we say that the *sole* disadvantage of > setting autovacuum_freeze_max_age to a higher number is disk space > usage? Freezing later also has consequences for whether you'll > actually be able to complete the freeze before wraparound, especially > if you set it to 2 billion as recommended.) > > [1] http://www.postgresql.org/docs/devel/static/routine-vacuuming.html > > -- > Thomas Munro > http://www.enterprisedb.com >
On Tue, Dec 29, 2015 at 4:24 PM, Thomas Munro <thomas.munro@enterprisedb.com> wrote: > On Tue, Dec 29, 2015 at 6:32 AM, <alain.laporte12345@gmail.com> wrote: >> I use PostgreSQL 9.4.5 and I have activated the parameter >> track_commit_timestamp to use BDR (0.9.3) and to be able to replicate two >> databases. A directory pg_committs was created (this directory is named >> pg_commit_ts in PostgreSQL 9.5 => >> http://www.postgresql.org/docs/9.5/static/storage-file-layout.html), but, >> his content is not periodically deleted (all files stays after 2 weeks of >> running). > > In stock PostgreSQL, pg_commit_ts is truncated when the cluster-wide > oldest xid is advanced by autovacuum (or manual vacuum freeze of all > databases including templates). With the default > autovacuum_freeze_max_age setting, that'll take more than a couple of > weeks unless you're doing somewhere around 15 million transactions per > day. (I guess that BDR-patched 9.4 is the same, but I don't know.) > > The documentation describes how autovacuum_freeze_max_age affects the > space occupied by pg_clog[1], but the same thing applies to > pg_commit_ts. pg_clog uses 2 bits per xid, and it looks like > pg_commit_ts uses 10 bytes per xid, so the default > autovacuum_freeze_max_age gives you ~50MB of pg_clog and ~2GB of > pg_commit_ts. > > I wonder if doc/src/sgml/maintenance.sgml should be updated to reflect > this, maybe with something along the lines of the attached patch. > > (Isn't it a bit strange that we say that the *sole* disadvantage of > setting autovacuum_freeze_max_age to a higher number is disk space > usage? Freezing later also has consequences for whether you'll > actually be able to complete the freeze before wraparound, especially > if you set it to 2 billion as recommended.) Rebased, will add to commitfest. -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs