Re: System catalog vacuum issues

On 08/15/2013 05:06 AM, Sergey Konoplev wrote:
> On Tue, Aug 13, 2013 at 10:31 PM, Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> wrote:
>> I used to use VACUUM FULL periodically to resolve the issue, but the problem
>> arises again in 2-3 months.
>> Here is the statistics (from pgstattuple). I run VACUUM FULL on 2013-08-07.
>>
>> date | relpages | reltuples | table_len | tuple_count | tuple_percent
>> | dead_tuple_count | dead_tuple_len | free_space | free_percent |
>> autovacuum_count
>> ------------+----------+-----------+-----------+-------------+---------------+------------------+----------------+------------+--------------+------------------
>> 2013-08-08 | 39029 | 109096 | 319725568 | 37950 | 1.66
>> | 52540 | 7355600 | 296440048 | 92.72 |
> Are you sure you did "VACUUM FULL pg_attribute" on Aug 7, could you
> please confirm that free_percent arises from 0 to 92% in one day?
>
> Do you have some processes that intensively create tables or columns
> and then delete them or create them in transaction and rollback the
> transaction?
>
Absolutely. Here is 1-minute statistics on this table. VACUUM FULL was
done on 2013-08-16 00:35:00.
There are many processes that create and drop temporary tables.

time | reltuples | table_len | tuple_count
| tuple_percent | dead_tuple_count | dead_len | free_space |
free_percent | autovacuum_count
-------------------------------+-------------+-----------+-------------+---------------+------------------+------------+------------+--------------+------------------
2013-08-16 00:33:01.977405+09 | 1.50021e+07 | 2211 MB | 38981
| 0.24 | 15505917 | 2070 MB | 8339 kB | 0.37
| 7463
2013-08-16 00:34:01.718696+09 | 1.50021e+07 | 2211 MB | 38875
| 0.23 | 15505952 | 2070 MB | 8349 kB | 0.37
| 7463
2013-08-16 00:35:01.570965+09 | 38875 | 5664 kB | 38875
| 93.84 | 46 | 6440 bytes | 19 kB | 0.34
| 7463
2013-08-16 00:36:01.658131+09 | 38875 | 5664 kB | 38875
| 93.84 | 46 | 6440 bytes | 19 kB | 0.34
| 7463
...
2013-08-16 08:10:01.201473+09 | 47950 | 52 MB | 47685
| 12.22 | 318481 | 43 MB | 229 kB | 0.43
| 7493
2013-08-16 08:11:01.411891+09 | 47950 | 54 MB | 47776
| 11.86 | 329589 | 44 MB | 333 kB | 0.6
| 7493
2013-08-16 08:12:01.623495+09 | 48036 | 56 MB | 47816
| 11.47 | 343932 | 46 MB | 199 kB | 0.35
| 7495
2013-08-16 08:13:01.837192+09 | 48036 | 58 MB | 47903
| 11.11 | 356488 | 48 MB | 286 kB | 0.48
| 7495
2013-08-16 08:14:02.041228+09 | 48036 | 59 MB | 47899
| 10.82 | 366939 | 49 MB | 370 kB | 0.61
| 7495
2013-08-16 08:15:01.254325+09 | 48036 | 61 MB | 48065
| 10.61 | 376192 | 50 MB | 420 kB | 0.68
| 7495
2013-08-16 08:16:01.557785+09 | 48210 | 62 MB | 48290
| 10.36 | 386019 | 52 MB | 696 kB | 1.09
| 7496
2013-08-16 08:17:01.774188+09 | 48210 | 64 MB | 48330
| 10.14 | 392236 | 52 MB | 1188 kB | 1.82
| 7496
2013-08-16 08:18:01.977503+09 | 48210 | 65 MB | 48370
| 9.87 | 79643 | 11 MB | 46 MB | 70.07
| 7496
2013-08-16 08:19:01.154589+09 | 48210 | 68 MB | 48550
| 9.55 | 27483 | 3757 kB | 55 MB | 81.55
| 7496
2013-08-16 08:20:01.321973+09 | 48333 | 69 MB | 48694
| 9.41 | 42512 | 5812 kB | 54 MB | 78.83
| 7497
2013-08-16 08:21:01.48612+09 | 48333 | 69 MB | 48831
| 9.43 | 43172 | 5902 kB | 54 MB | 78.67
| 7497
2013-08-16 08:22:01.668103+09 | 48926 | 69 MB | 48947
| 9.46 | 22677 | 3100 kB | 57 MB | 82.72
| 7498
2013-08-16 08:23:01.83524+09 | 48962 | 69 MB | 48914
| 9.45 | 8655 | 1183 kB | 59 MB | 85.5
| 7499
...
2013-08-16 10:22:01.590888+09 | 52114 | 131 MB | 52395
| 5.33 | 866015 | 116 MB | 1045 kB | 0.78
| 7550
2013-08-16 10:23:01.908792+09 | 52114 | 133 MB | 52579
| 5.29 | 560495 | 75 MB | 44 MB | 33.44
| 7550
2013-08-16 10:24:01.207538+09 | 52114 | 134 MB | 52566
| 5.22 | 222138 | 30 MB | 92 MB | 68.77
| 7550
2013-08-16 10:25:01.485565+09 | 52114 | 136 MB | 52637
| 5.17 | 25493 | 3485 kB | 121 MB | 88.95
| 7550
2013-08-16 10:26:01.747405+09 | 52114 | 138 MB | 52673
| 5.11 | 34411 | 4705 kB | 121 MB | 88.14
| 7550
2013-08-16 10:27:01.025129+09 | 52114 | 139 MB | 52733
| 5.05 | 47331 | 6471 kB | 121 MB | 86.98
| 7550
2013-08-16 10:28:01.301775+09 | 52114 | 142 MB | 52776
| 4.97 | 61860 | 8457 kB | 121 MB | 85.73
| 7550
2013-08-16 10:29:01.600223+09 | 52577 | 142 MB | 52805
| 4.97 | 72307 | 9886 kB | 120 MB | 84.72
| 7551
2013-08-16 10:30:01.883806+09 | 52577 | 142 MB | 52809
| 4.97 | 81656 | 11 MB | 119 MB | 83.81
| 7551
2013-08-16 10:31:01.165272+09 | 52577 | 142 MB | 52869
| 4.98 | 93342 | 12 MB | 117 MB | 82.67
| 7551
2013-08-16 10:32:01.463636+09 | 52577 | 142 MB | 52919
| 4.98 | 108460 | 14 MB | 115 MB | 81.19
| 7551
2013-08-16 10:33:01.772635+09 | 52577 | 142 MB | 52999
| 4.99 | 74781 | 10224 kB | 120 MB | 84.44
| 7551
2013-08-16 10:34:01.059824+09 | 52577 | 142 MB | 52986
| 4.99 | 86221 | 12 MB | 118 MB | 83.33
| 7551
2013-08-16 10:35:01.337787+09 | 52577 | 142 MB | 53163
| 5.01 | 38615 | 5279 kB | 125 MB | 87.92
| 7551
2013-08-16 10:36:01.624472+09 | 18577 | 142 MB | 53226
| 5.01 | 53105 | 7260 kB | 123 MB | 86.52
| 7552
2013-08-16 10:37:01.911377+09 | 25501 | 142 MB | 53056
| 5 | 11712 | 1601 kB | 128 MB | 90.55
| 7553