Re: [HACKERS] oldest xmin is far in the past

2016-03-20 Thread Jim Nasby 

On 3/19/16 11:32 AM, Tomas Vondra wrote:

Hi,

On 03/19/2016 06:29 AM, John Snow wrote:

There is no any long transaction neither prepared transaction.


Can you show us pg_stat_activity? Particularly the xmin values for
backends attached to the two databases mentioned in the log (1 and 12451).

FWIW the second OID is a bit weird - the first OID assigned to normal
objects is defined as 16384, and none of the so I wonder how you managed
to create a database with such DB?


On my 9.4, template1 has oid 1.

BTW, John mentioned Slony; if this is on one of the replicas then it's 
certainly understandable that all the tables have ages that are almost 
identical. That happens because the initial COPY of each table takes 
place in a single transaction, and the only other activity that's 
generating XIDs is the normal replay process. Depending on your 
settings, I'd expect that you're only generating a couple XIDs/minute, 
so even if it took 10 days to do the initial copy you'd still only have 
a span of ~30k transactions. That means autovac will suddenly want to 
freeze the whole database in one shot. It's a good idea to run a manual 
vacuum freeze after the initial copy is done to prevent this.


To answer one of your other questions, it look like all the ages are 
~500M XIDs, which means you've got another ~1B to go before this becomes 
a serious concern.



* freeze_min_age
* vacuum_freeze_min_age
* autovacuum_freeze_max_age (we already know this one)

What values are set for those?


Better yet, can you just run this query?

SELECT name, setting, unit, source
  FROM pg_settings
  WHERE name ~ 'freeze|vacuum' OR source !~ 'default|override'
;

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

[HACKERS] oldest xmin is far in the past

2016-03-20 Thread John Snow 
Hi everyone!

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

Also "age" and "relfrozenxid" doesnt't change.

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;

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

Then I'm doing: VACUUM FREEZE; and nothing happens, I only get debug and
warning messages as I mentioned above.

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 7500
vacuum_freeze_table_age 2
vacuum_multixact_freeze_min_age 500
vacuum_multixact_freeze_table_age 15000
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; - 1050801875

why such difference?

Re: [HACKERS] oldest xmin is far in the past

2016-03-19 Thread Tomas Vondra 

Hi,

On 03/19/2016 06:29 AM, John Snow wrote:

There is no any long transaction neither prepared transaction.


Can you show us pg_stat_activity? Particularly the xmin values for 
backends attached to the two databases mentioned in the log (1 and 12451).


FWIW the second OID is a bit weird - the first OID assigned to normal 
objects is defined as 16384, and none of the so I wonder how you managed 
to create a database with such DB?


Unless it's one of the template databases, but I got different OIDs when 
I tried a fresh initdb on 9.4.



#autovacuum_freeze_max_age = 2 - default value


After looking at the code a bit more, I see it uses some additional 
configuration options:


* freeze_min_age
* vacuum_freeze_min_age
* autovacuum_freeze_max_age (we already know this one)

What values are set for those?

regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] oldest xmin is far in the past

2016-03-19 Thread John Snow 
There is no any long transaction neither prepared transaction.
#autovacuum_freeze_max_age = 2 - default value

I have 9.4.5 version. Also it all started after I've setup Slony
replication(mb just a coincidence). All tables in public schema have the
same "age", I believe this is weird.

How can I calculate how long DB can live in this stage?

2016-03-19 0:28 GMT+03:00 Tomas Vondra :

> Hi,
>
> On 03/18/2016 09:42 AM, John Snow wrote:
>
>> Hi everyone!
>>
>> 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
>>
>> Also "age" and "relfrozenxid" doesnt't change.
>>
>
> That probably means there's an old transaction somewhere - either a
> regular one (check pg_stat_activity) or a prepared one (pg_prepared_xacts).
>
> The meaning of "old" depends on autovacuum_freeze_max_age - what value is
> set in the session running the VACUUM FREEZE?
>
> regards
>
> --
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] oldest xmin is far in the past

2016-03-18 Thread Tomas Vondra 

Hi,

On 03/18/2016 09:42 AM, John Snow wrote:

Hi everyone!

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

Also "age" and "relfrozenxid" doesnt't change.


That probably means there's an old transaction somewhere - either a 
regular one (check pg_stat_activity) or a prepared one (pg_prepared_xacts).


The meaning of "old" depends on autovacuum_freeze_max_age - what value 
is set in the session running the VACUUM FREEZE?


regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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