Thank you for your comments.
First, I think I need to tell you our database situation
- 3 physical databases (installed in different servers. 1master, 2 slave servers.)
- more than logical 1100 databases in each servers
[Karsten and Melvin]
It shows 0 records in template0 with the query you provided.
===============================
schema | table | index | idx_scan | idx_tup_read | idx_tup_fetch | type | pg_get_indexdef | statusi | size_in_bytes | size
--------+-------+-------+----------+--------------+---------------+------+-----------------+---------+---------------+------
(0 rosw)
===============================
[Adrian]
>> [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?
Any logical database (we have more than 1100 databases) which age (relfrozenxid) is more than 2 hundreds
million shows that autovacuum runs repeatedly (it starts and stops autovacuum process repeatedly with no
vacuum processing).
>> - so, autovacuum did not go next database
>
>Which was?
- I saw autovacuum stops at template0 because it can not run vacuum freeze against it
- I thought it was because the age of template0 exceeds the config parameter of autovacuum kick, which is 2 hundreds milliions
- So, I wanted to reduce the age of template0 (I don't know why it increases though)
>Not finish on what?
I could finish vacuum manually.
But, autovacuum was not finished.
>> This time, the age did not reduce with manual vacuum.
>Age of what?
age(relfrozenxid) of template0.
>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?
We have more than 1100 databases and create new database every day
whenever new customer comes.
Number of transactions are more than ten millions in total of 1100+ database.
>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.
I run vacuum full because I could not change the value of relfrozenxid of pg_database with vacuum/vacuum freeze.
Except template0 database, I can change relfrozenxid if I run vacuum full pg_database.
>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.
===============================
postgres=# SELECT oid,ctid,* from pg_database where datname = 'template0' ;
oid | ctid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace |
datacl
-------+---------+-----------+--------+----------+------------+----------+---------------+--------------+--------------+---------------+--------------+---------------+--------
-----------------------------
12772 | (36,25) | template0 | 10 | 6 | C | C | t | f | -1 | 12772 | 2412920847 | 1663 | {=c/pos
tgres,postgres=CTc/postgres}
12772 | (36,26) | template0 | 10 | 6 | C | C | t | f | -1 | 12772 | 2264969019 | 1663 | {=c/pos
tgres,postgres=CTc/postgres}
12772 | (36,27) | template0 | 10 | 6 | C | C | t | f | -1 | 12772 | 2264969019 | 1663 | {=c/pos
tgres,postgres=CTc/postgres}
12772 | (36,28) | template0 | 10 | 6 | C | C | t | f | -1 | 12772 | 2264969019 | 1663 | {=c/pos
tgres,postgres=CTc/postgres}
(4 rows)
===============================
oid is same value.
But ctid is different values.
The rest of records has our customer name. If you need more info from here, I can send you the whole data.
Best regards,
Kazuaki Fujikura
