oldest xmin is far in the past - Mailing list pgsql-hackers
| From | John Snow |
|---|---|
| Subject | oldest xmin is far in the past |
| Date | |
| Msg-id | CAM+o-ApLaXFLaieaVx5Dj1RKCx2OaLm5akJ909j8H8FLL5UJMg@mail.gmail.com Whole thread Raw |
| Responses |
Re: oldest xmin is far in the past
|
| List | pgsql-hackers |
Hi everyone!
Also "age" and "relfrozenxid" doesnt't change.
Trying to make VACUUM FREEZE on PG instance and keep getting this error:
2016-03-18 05:56:51 UTC 46750 WARNING: oldest xmin is far in the past
2016-03-18 05:56:51 UTC 46750 HINT: Close open transactions soon to avoid wraparound problems.
2016-03-18 05:56:51 UTC 46750 DEBUG: transaction ID wrap limit is 2654342112, limited by database with OID 1
2016-03-18 05:56:51 UTC 46750 DEBUG: MultiXactId wrap limit is 2147483648, limited by database with OID 12451
I will show what I'm trying to do step by step:
Executing this command:
SELECT
pg_namespace.nspname
,c.relname AS relname
--,c.oid::regclass as table_name
,greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
,c.relfrozenxid
,t.relfrozenxid
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
LEFT JOIN
pg_namespace
ON pg_namespace.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY
age desc
,1,2;
Then I'm doing: VACUUM FREEZE; and nothing happens, I only get debug and warning messages as I mentioned above.Output looks like this:
nspname relname age relfrozenxid relfrozenxid public action_flows 543567979 506858465 506858465 public advertiser_requests 543567979 506858465 506858465 public authtokens 543567979 506858465 506858465 public blacklist 543567979 506858465 506858465 public blog_categories 543567979 506858465 506858465 public blog_posts 543567979 506858465 506858465 public bp_service_codes 543567979 506858465 506858465 public browsers 543567979 506858465 506858465
Settings on server:
| name | setting | unit |
| autovacuum | on | |
| autovacuum_analyze_scale_factor | 0.1 | |
| autovacuum_max_workers | 20 | |
| autovacuum_vacuum_cost_delay | 0 | ms |
| autovacuum_vacuum_cost_limit | 200 | |
| autovacuum_vacuum_scale_factor | 0.2 | |
| bgwriter_delay | 200 | ms |
| checkpoint_completion_target | 0.9 | |
| checkpoint_segments | 128 | |
| checkpoint_timeout | 1800 | s |
| client_encoding | UTF8 | |
| client_min_messages | debug1 | |
| commit_delay | 5000 | |
| commit_siblings | 15 | |
| DateStyle | ISO, MDY | |
| deadlock_timeout | 1000 | ms |
| debug_pretty_print | on | |
| default_statistics_target | 100 | |
| default_text_search_config | pg_catalog.english | |
| dynamic_shared_memory_type | posix | |
| effective_cache_size | 12582912 | 8kB |
| extra_float_digits | 3 | |
| fsync | on | |
| full_page_writes | off | |
| lc_messages | en_US.UTF-8 | |
| lc_monetary | en_US.UTF-8 | |
| lc_numeric | en_US.UTF-8 | |
| lc_time | en_US.UTF-8 | |
| listen_addresses | * | |
| log_autovacuum_min_duration | 1000 | ms |
| log_checkpoints | on | |
| log_destination | stderr | |
| log_directory | /home/pgsql/data/pg_log | |
| log_filename | postgresql-%a.log | |
| log_line_prefix | %t %h %u %p | |
| log_lock_waits | on | |
| log_min_duration_statement | 1000 | ms |
| log_min_error_statement | debug1 | |
| log_min_messages | debug1 | |
| log_rotation_age | 1440 | min |
| log_rotation_size | 0 | kB |
| log_statement | none | |
| log_timezone | UTC | |
| log_truncate_on_rotation | on | |
| logging_collector | on | |
| maintenance_work_mem | 2097152 | kB |
| max_connections | 800 | |
| max_prepared_transactions | 10 | |
| max_replication_slots | 1 | |
| max_stack_depth | 2048 | kB |
| max_wal_senders | 3 | |
| port | 9125 | |
| random_page_cost | 1.2 | |
| search_path | public | |
| seq_page_cost | 1 | |
| shared_buffers | 6553600 | 8kB |
| synchronous_commit | off | |
| temp_buffers | 16384 | 8kB |
| TimeZone | Europe/Moscow | |
| track_counts | on | |
| update_process_title | off | |
| vacuum_cost_delay | 1 | ms |
| vacuum_freeze_min_age | 75000000 | |
| vacuum_freeze_table_age | 200000000 | |
| vacuum_multixact_freeze_min_age | 5000000 | |
| vacuum_multixact_freeze_table_age | 150000000 | |
| wal_buffers | 2048 | 8kB |
| wal_keep_segments | 128 | |
| wal_level | hot_standby | |
| work_mem | 65536 | kB |
Also:
select txid_current(); - 5345750425
select xmin from stats_y2016_m3 order by ts_spawn desc limit 1; - 1050801875why such difference?
pgsql-hackers by date: