| From: | "Karsten Hilbert" <Karsten(dot)Hilbert(at)gmx(dot)net> |
|---|---|
| To: | |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: four template0 databases after vacuum |
| Date: | 2016-02-07 09:51:01 |
| Message-ID: | trinity-3c73c862-f4ed-4209-a087-5d91a323c9c1-1454838661514@3capp-gmx-bs56 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>
<div>Just a shot in the dark for a possible lead to follow down (sorry for top-posting):</div>
<div> </div>
<div>Is there index corruption on system tables ?</div>
<div> </div>
<div>(like, several index entries pointing to the one template0 row)</div>
<div> </div>
<div>Karsten</div>
<div> </div>
<div>
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;"><b>Gesendet:</b> Sonntag, 07. Februar 2016 um 03:43 Uhr<br/>
<b>Von:</b> "Kazuaki Fujikura" <fujya(at)fujya(dot)com><br/>
<b>An:</b> pgsql-general(at)postgresql(dot)org<br/>
<b>Betreff:</b> [GENERAL] four template0 databases after vacuum</div>
<div name="quoted-content">
<div>
<div><span style="font-size: 14.0px;">Hi there,</span></div>
<div> </div>
<div style="font-size: 14.0px;">Version: 9.1.6 running since Dec, 2014</div>
<div style="font-size: 14.0px;">We have 3 different databases. </div>
<div style="font-size: 14.0px;"> </div>
<div>
<div style="font-size: 14.0px;">[problem history/background]</div>
<div style="font-size: 14.0px;"> </div>
<div style="font-size: 14.0px;">Jan 10th, 2016:</div>
<div style="font-size: 14.0px;">The first problem was autovacuum issue.</div>
<span style="font-size: 14.0px;">- autovacuum could not finish successfully.</span>
<div style="font-size: 14.0px;">- I set autovacuum_freeze_max_age to 2 hundreds million.</div>
<div style="font-size: 14.0px;">- autovacuum immediately finished against the database which age was over 2 hundreds million.</div>
<div style="font-size: 14.0px;">- so, autovacuum did not go next database</div>
<div style="font-size: 14.0px;">- I then run "vacuumdb -az" and run vacuum freeze analyze against template0 after setting datallowconn to true</div>
<div style="font-size: 14.0px;">- I set datallowconn to false</div>
<div style="font-size: 14.0px;"> </div>
<div style="font-size: 14.0px;">============================================================</div>
<div style="font-size: 14.0px;">$ vacuumdb -az</div>
<div style="font-size: 14.0px;">
<div style="color: rgb(80,0,80);">
<div>$ psql template1</div>
<div>template1=# UPDATE pg_database SET datallowconn = TRUE where datname = 'template0';</div>
<div>UPDATE 1</div>
<div>template1=# \c template0</div>
<div>template0=# VACUUM FREEZE ANALYZE ;</div>
<div>VACUUM</div>
<div>template0=# \c template1</div>
<div>template1=# UPDATE pg_database SET datallowconn = FALSE where datname = 'template0';</div>
<div>UPDATE 1</div>
<div><span style="color: rgb(34,34,34);">============================================================</span></div>
<div> </div>
</div>
</div>
<div style="font-size: 14.0px;">- At this point, there is one template0 only</div>
<div style="font-size: 14.0px;"> </div>
<div style="font-size: 14.0px;"> </div>
<div>
<div style="font-size: 14.0px;">
<div>Jan 30th, 2016:</div>
After three weeks, I again hit the same issue - autovacuum could not finish.</div>
<div style="font-size: 14.0px;">This time, the age did not reduce with manual vacuum.</div>
<div style="font-size: 14.0px;">I then run vacuum full to pg_database. The age of pg_database becomes minus value.</div>
<div style="font-size: 14.0px;">Then, autovacuum started again.</div>
<div style="font-size: 14.0px;"> </div>
<div style="font-size: 14.0px;">============================================================</div>
<div>
<div><span style="font-size: 14.0px;">target_db=# SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' ;</span></div>
<div><span style="font-size: 14.0px;"> relname | age </span></div>
<div><span style="font-size: 14.0px;">------------------------------------------------+-----------</span></div>
<div><span style="font-size: 14.0px;"> pg_database | 219383067</span></div>
<div><span style="font-size: 14.0px;">target_db=# VACUUM FREEZE ;</span></div>
<div><span style="font-size: 14.0px;">VACUUM</span></div>
<div><span style="font-size: 14.0px;">target_db=# SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' ;</span></div>
<div><span style="font-size: 14.0px;"> relname | age </span></div>
<div><span style="font-size: 14.0px;">------------------------------------------------+-----------</span></div>
<div><span style="font-size: 14.0px;"> pg_database | 219387307</span></div>
<div><span style="font-size: 14.0px;">target_db=# VACUUM FULL ;</span></div>
<div><span style="font-size: 14.0px;">VACUUM</span></div>
<div><span style="font-size: 14.0px;"> relname | age </span></div>
<div><span style="font-size: 14.0px;">------------------------------------------------+-----------</span></div>
<div><span style="font-size: 14.0px;"> pg_database | -1861408089</span></div>
</div>
</div>
<div style="font-size: 14.0px;">============================================================</div>
<div style="font-size: 14.0px;"> </div>
<div style="font-size: 14.0px;"> </div>
<div style="font-size: 14.0px;"> </div>
<div>
<div style="font-size: 14.0px;">Yesterday:<br/>
</div>
<div style="font-size: 14.0px;">I run the following command to run vacuum full to all pg_database.</div>
<div style="font-size: 14.0px;">Then, I run vacuum freeze analyze and vacuum full to template0 after setting datallowconn.</div>
<div style="font-size: 14.0px;"> </div>
<div style="font-size: 14.0px;">============================================================</div>
<div style="font-size: 14.0px;"><span style="color: rgb(80,0,80);">$ psql -lt | awk '{print $1}' | grep -v ^$ | grep -v ^\| | while read line; do psql ${line} -c "VACUUM FULL pg_database;"; done</span></div>
<div style="font-size: 14.0px;">VACUUM ....</div>
<div style="font-size: 14.0px;"> </div>
<div>
<div><span style="font-size: 14.0px;">$ psql template1</span></div>
<div><span style="font-size: 14.0px;">template1=# UPDATE pg_database SET datallowconn = TRUE where datname = 'template0';</span></div>
<div><span style="font-size: 14.0px;">UPDATE 1</span></div>
<div><span style="font-size: 14.0px;">template1=# \c template0</span></div>
<div><span style="font-size: 14.0px;">template0=# VACUUM FULL ;</span></div>
<div><span style="font-size: 14.0px;">VACUUM</span></div>
<div><span style="font-size: 14.0px;">template0=# \c template1</span></div>
<div><span style="font-size: 14.0px;">template1=# UPDATE pg_database SET datallowconn = FALSE where datname = 'template0';</span></div>
<div><span style="font-size: 14.0px;">UPDATE 4</span></div>
<span style="font-size: 14.0px;">============================================================</span></div>
<div> </div>
<div style="font-size: 14.0px;">***Then I HAVE FOUR template0 DATABASES***</div>
</div>
<div style="font-size: 14.0px;"> </div>
<div style="font-size: 14.0px;"> </div>
<div style="font-size: 14.0px;"> </div>
<div>
<div style="font-size: 14.0px;color: rgb(34,34,34);"><span class="im">[Current problems]</span><br/>
</div>
<div style="font-size: 14.0px;color: rgb(34,34,34);"><span class="im">We now have three issues in our production.</span><br/>
</div>
<div style="font-size: 14.0px;color: rgb(34,34,34);"><span class="im">1. It looks four template0 databases exist</span></div>
<div style="font-size: 14.0px;color: rgb(34,34,34);"><span class="im">2. Xid of template0 keeps growing</span></div>
<div style="font-size: 14.0px;color: rgb(34,34,34);"><span class="im">3. Can not freeze xid of template0</span></div>
<div style="font-size: 14.0px;color: rgb(34,34,34);"> </div>
<div style="font-size: 14.0px;color: rgb(34,34,34);"> </div>
<div style="font-size: 14.0px;color: rgb(34,34,34);"> </div>
<div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">1. It looks four template0 databases exist</span></font></span></div>
<div> </div>
<div><span class="im"><span style="color: rgb(34,34,34);font-size: 14.0px;">============================================================</span><span style="font-size: 14.0px;color: rgb(34,34,34);"> </span></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">$ psql -l | grep template0 </span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> template0 | postgres | UTF8 | C | C | =c/postgres +</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> template0 | postgres | UTF8 | C | C | =c/postgres +</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> template0 | postgres | UTF8 | C | C | =c/postgres +</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> template0 | postgres | UTF8 | C | C | =c/postgres +</span></font></span></div>
<div><span class="im"><span style="color: rgb(34,34,34);font-size: 14.0px;">============================================================</span></span></div>
<div> </div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">These have same dataid.</span></font></span></div>
<div> </div>
<div><span class="im"><span style="color: rgb(34,34,34);font-size: 14.0px;">============================================================</span><font color="#222222"><span style="font-size: 14.0px;"> </span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">postgres=# SELECT datid, datname FROM pg_stat_database where datname = 'template0';</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> datid | datname </span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">-------+-----------</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> 12772 | template0</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> 12772 | template0</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> 12772 | template0</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> 12772 | template0</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">(4 rows)</span></font></span></div>
<div><span class="im"><span style="color: rgb(34,34,34);font-size: 14.0px;">============================================================</span></span></div>
<div> </div>
<div> </div>
<div> </div>
<div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">2. Xid of template0 keeps growing</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> </span></font></span></div>
<div><span class="im"><span style="color: rgb(34,34,34);font-size: 14.0px;">============================================================</span></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">postgres=# SELECT datname, age(datfrozenxid) FROM pg_database order by age desc; </span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> datname | age </span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">------------------------------------------------+-----------</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> template0 | 198431852</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> template0 | 198431852</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> template0 | 198431852</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> template0 | 50480024</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> template1 | 45629585</span></font></span></div>
<div><span class="im"><span style="color: rgb(34,34,34);font-size: 14.0px;">============================================================</span></span></div>
<div> </div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">At this moment, the maximum age value of all databases is template0.</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">The age value keeps growing.</span></font></span></div>
<div> </div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">One of 4 template0 is young (504080024). Other three template0s are still old.</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> </span></font></span></div>
<div> </div>
</div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> 3. Can not freeze xid of template0</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> </span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">To reset xid of template0, I did vacuum full/ vacuum freeze to template0. But,</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">the age of three template0 did not change. Only of of 4 template0 had successfully</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">changed the age young.</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;"> </span></font></span></div>
<div><span class="im"><span style="color: rgb(34,34,34);font-size: 14.0px;">============================================================</span></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">$ psql template1</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">template1=# UPDATE pg_database SET datallowconn = TRUE where datname = 'template0';</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">UPDATE 4</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">template1=# \c template0</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">template0=# VACUUM FREEZE ANALYZE ;</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">VACUUM</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">template0=# VACUUM FULL ;</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">VACUUM</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">template0=# \c template1</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">template1=# UPDATE pg_database SET datallowconn = FALSE where datname = 'template0';</span></font></span></div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">UPDATE 4</span></font></span></div>
<div><span class="im"><span style="color: rgb(34,34,34);font-size: 14.0px;">============================================================</span></span></div>
<div> </div>
<div><span class="im"><font color="#222222"><span style="font-size: 14.0px;">I run the commands above. But, I could not change the age of three template0 databases.</span></font></span></div>
<div> </div>
</div>
<div style="font-size: 14.0px;color: rgb(34,34,34);"> </div>
<div>
<div> </div>
<div><span class="im"><span style="font-size: 14.0px;">[My idea to fix this]</span></span></div>
<div> </div>
<div><span class="im"><span style="font-size: 14.0px;">If I don't do anything about this, I think our production service will be down because it exceeds </span><span style="font-size: 14.0px;">the limit of xid.</span></span></div>
<div> </div>
<div><span class="im"><span style="font-size: 14.0px;">I guess if I drop all template0 and create template0 again, then everything gets back normal.</span></span></div>
<div><span class="im"><span style="font-size: 14.0px;">But I am not quite sure if my approach is right.</span></span></div>
<div> </div>
<div><span class="im"><span style="font-size: 14.0px;">I would appreciate any suggestion/comments.</span></span></div>
<div> </div>
<div><span class="im"><span style="font-size: 14.0px;">Best regards,</span></span></div>
<div><span class="im"><span style="font-size: 14.0px;">Kazuaki Fujikura</span></span></div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div></div></body></html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 20.3 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | FarjadFarid(ChkNet) | 2016-02-07 13:39:57 | Re: Asp.net 5 and EF6 |
| Previous Message | Kazuaki Fujikura | 2016-02-07 02:43:39 | four template0 databases after vacuum |