On 02/06/2016 06:43 PM, Kazuaki Fujikura wrote:
Hi there,
Version: 9.1.6 running since Dec, 2014
We have 3 different databases.
See comment in line.
[problem history/background]
Jan 10th, 2016:
The first problem was autovacuum issue.
- autovacuum could not finish successfully.
- I set autovacuum_freeze_max_age to 2 hundreds million.
- autovacuum immediately finished against the database which age was
over 2 hundreds million.
Which was?
- so, autovacuum did not go next database
Which was?
- I then run "vacuumdb -az" and run vacuum freeze analyze against
template0 after setting datallowconn to true
Why? template0 is by default read-only there should be nothing happening
it to require vacuuming.
- I set datallowconn to false
============================================================
$ vacuumdb -az
$ psql template1
template1=# UPDATE pg_database SET datallowconn = TRUE where datname =
'template0';
UPDATE 1
template1=# \c template0
template0=# VACUUM FREEZE ANALYZE ;
VACUUM
template0=# \c template1
template1=# UPDATE pg_database SET datallowconn = FALSE where datname =
'template0';
UPDATE 1
============================================================
- At this point, there is one template0 only
Jan 30th, 2016:
After three weeks, I again hit the same issue - autovacuum could not finish.
Not finish on what?
This time, the age did not reduce with manual vacuum.
Age of what?
I then run vacuum full to pg_database. The age of pg_database becomes
minus value.
Then, autovacuum started again.
============================================================
target_db=# SELECT relname, age(relfrozenxid) FROM pg_class WHERE
relkind = 'r' ;
relname | age
------------------------------------------------+-----------
pg_database | 219383067
target_db=# VACUUM FREEZE ;
VACUUM
target_db=# SELECT relname, age(relfrozenxid) FROM pg_class WHERE
relkind = 'r' ;
relname | age
------------------------------------------------+-----------
pg_database | 219387307
target_db=# VACUUM FULL ;
VACUUM
relname | age
------------------------------------------------+-----------
pg_database | -1861408089
============================================================
Yesterday:
I run the following command to run vacuum full to all pg_database.
This I do not get, how the xid count on pg_database so quickly and to
such an extent that it needs a VACUUM FULL?
Is there a script that is creating and dropping databases rapidly?
Then, I run vacuum freeze analyze and vacuum full to template0 after
setting datallowconn.
============================================================
$ psql -lt | awk '{print $1}' | grep -v ^$ | grep -v ^\| | while read
line; do psql ${line} -c "VACUUM FULL pg_database;"; done
VACUUM ....
What are you trying to do with the above?
I do not think it is a coincidence that the first time the above was run
in this sequence, shortly after 4 template0 databases appear.
$ psql template1
template1=# UPDATE pg_database SET datallowconn = TRUE where datname =
'template0';
UPDATE 1
template1=# \c template0
template0=# VACUUM FULL ;
VACUUM
template0=# \c template1
template1=# UPDATE pg_database SET datallowconn = FALSE where datname =
'template0';
UPDATE 4
============================================================
***Then I HAVE FOUR template0 DATABASES***
[Current problems]
We now have three issues in our production.
1. It looks four template0 databases exist
2. Xid of template0 keeps growing
3. Can not freeze xid of template0
1. It looks four template0 databases exist
============================================================
$ psql -l | grep template0
template0 | postgres | UTF8
| C | C | =c/postgres +
template0 | postgres | UTF8
| C | C | =c/postgres +
template0 | postgres | UTF8
| C | C | =c/postgres +
template0 | postgres | UTF8
| C | C | =c/postgres +
============================================================
These have same dataid.
============================================================
postgres=# SELECT datid, datname FROM pg_stat_database where datname =
'template0';
datid | datname
-------+-----------
12772 | template0
12772 | template0
12772 | template0
12772 | template0
(4 rows)
============================================================
2. Xid of template0 keeps growing
============================================================
postgres=# SELECT datname, age(datfrozenxid) FROM pg_database order by
age desc;
datname | age
------------------------------------------------+-----------
template0 | 198431852
template0 | 198431852
template0 | 198431852
template0 | 50480024
template1 | 45629585
============================================================
Can you show?:
SELECT * from pg_database;
If you do not want to show the whole cluster, then at least the
databases involved in this discussion.
At this moment, the maximum age value of all databases is template0.
The age value keeps growing.
One of 4 template0 is young (504080024). Other three template0s are
still old.
3. Can not freeze xid of template0
To reset xid of template0, I did vacuum full/ vacuum freeze to
template0. But,
the age of three template0 did not change. Only of of 4 template0 had
successfully
changed the age young.
============================================================
$ psql template1
template1=# UPDATE pg_database SET datallowconn = TRUE where datname =
'template0';
UPDATE 4
template1=# \c template0
template0=# VACUUM FREEZE ANALYZE ;
VACUUM
template0=# VACUUM FULL ;
VACUUM
template0=# \c template1
template1=# UPDATE pg_database SET datallowconn = FALSE where datname =
'template0';
UPDATE 4
============================================================
I run the commands above. But, I could not change the age of three
template0 databases.
[My idea to fix this]
If I don't do anything about this, I think our production service will
be down because it exceeds the limit of xid.
I guess if I drop all template0 and create template0 again, then
everything gets back normal.
But I am not quite sure if my approach is right.
I would appreciate any suggestion/comments.
Best regards,
Kazuaki Fujikura
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
