Search This Blog

2015-01-14

Postgres Vacuum Info

Find out if a vacuum is running by seeing:
- which processes are not waiting for anything
- which processes are actually running "not idle"
- sort by query start to see the oldest (aka. longest running queries)

select * from pg_stat_activity where waiting='f' and state NOT LIKE '%idle%' order by query_start;
-[ RECORD 1 ]----+------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
datid | 9942542
datname | labdb
pid | 7028
usesysid | 10
usename | postgres
application_name |
client_addr |
client_hostname |
client_port |
backend_start | 2015-01-13 19:36:15.405989-05
xact_start | 2015-01-13 19:36:15.437042-05
query_start | 2015-01-13 19:36:15.437042-05
state_change | 2015-01-13 19:36:15.437043-05
waiting | f
state | active
query | autovacuum: VACUUM public.inv_comp_run (to prevent wraparound)



Find tables xid_age "how close they are to hitting 200 million xid when postgres by default does xid vacuum cleanup"

SELECT relname, age(relfrozenxid) as xid_age,
pg_size_pretty(pg_table_size(oid)) as table_size
FROM pg_class
WHERE relkind = 'r' and pg_table_size(oid) > 1073741824
ORDER BY age(relfrozenxid) DESC LIMIT 20;


Example output or a table hitting xid wraparound threshold in which it requires autovacuum:

-[ RECORD 1 ]---------------------------------
relname | inv_comp_run
xid_age | 200438022
table_size | 50 GB


Find processes that might have been backed up or delayed by vacuum

select * from pg_stat_activity where waiting='t' order by query_start;
-[ RECORD 1 ]----+-------------------------------------------------------------
datid | 9942542
datname | labdb
pid | 30629
usesysid | 17261
usename | labuser
application_name |
client_addr | 10.128.2.204
client_hostname | WTLLAB-TEST-4.phaedrus.sandvine.com
client_port | 21015
backend_start | 2015-01-13 20:27:57.718403-05
xact_start | 2015-01-13 20:27:57.722504-05
query_start | 2015-01-13 20:27:57.722504-05
state_change | 2015-01-13 20:27:57.722506-05
waiting | t
state | active
query | BEGIN;SELECT candidate_resource_lock()


In this case there are some locking queries that are likely backed up by vacuum process.

To describe functions to figure out what the function is actually doing

\df+ candidate_resource_lock
List of functions
-[ RECORD 1 ]-------+---------------------------------------------------------------------------------------------------------------------------------------------------
-----
Schema | public
Name | candidate_resource_lock
Result data type | void
Argument data types |
Type | normal
Volatility | volatile
Owner | labadmin
Language | sql
Source code | LOCK TABLE reservation, reserve_res IN EXCLUSIVE MODE;
Description | Used to lock the reserve_res table before querying candidate resources so that we keep others from reserving our candidates while we're evaluating
them


References:
http://www.databasesoup.com/2012/09/freezing-your-tuples-off-part-1.html
http://stackoverflow.com/questions/23404235/postgresql-database-is-not-accepting-commands-to-avoid-wraparound-data-loss

No comments:

Post a Comment