Re: four template0 databases after vacuum
От | Adrian Klaver |
---|---|
Тема | Re: four template0 databases after vacuum |
Дата | |
Msg-id | 56B77F8A.4030501@aklaver.com обсуждение исходный текст |
Ответ на | four template0 databases after vacuum (Kazuaki Fujikura <fujya@fujya.com>) |
Ответы |
Re: four template0 databases after vacuum
(Kazuaki Fujikura <fujya@fujya.com>)
|
Список | pgsql-general |
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@aklaver.com
В списке pgsql-general по дате отправления: