Re: Measuring relation free space

On Thu, Dec 15, 2011 at 4:11 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> On Sun, Nov 06, 2011 at 10:20:49PM +0100, Bernd Helmle wrote:
>> --On 6. November 2011 01:08:11 -0200 Greg Smith <greg(at)2ndQuadrant(dot)com> wrote:
>>
>>> Attached patch adds a new function to the pageinspect extension for measuring
>>> total free space, in either tables or indexes.
>>
>> I wonder if that should be done in the pgstattuple module, which output
>> some similar numbers.
>
> Indeed, pgstattuple already claims to show precisely the same measure.  Its
> reckoning is right in line for heaps, but the proposed pageinspect function
> finds more free space in indexes:
>
> [local] test=# SELECT t.free_percent, relation_free_space('pg_proc'), i.free_percent, relation_free_space('pg_proc_proname_args_nsp_index') FROM pgstattuple('pg_proc') t, pgstattuple('pg_proc_proname_args_nsp_index') i;
>  free_percent | relation_free_space | free_percent | relation_free_space
> --------------+---------------------+--------------+---------------------
>         2.53 |           0.0253346 |         8.61 |            0.128041
> (1 row)
>
> Is one of those index figures simply wrong, or do they measure two senses of
> free space, both of which are interesting to DBAs?
>

i created a test env using pgbench -s 20 -F 90, i then create a new
table (that keep tracks actions that happens the the pgbench tables,
so insert only) and changed a few fillfactors:
"""
relname | reltuples | reloptions
-------------------------------------+---- -------+------------------
audit_log | 804977 |
pgbench_accounts | 1529890 | {fillfactor=90}
pgbench_accounts_pkey | 1529890 | {fillfactor=50}
pgbench_branches | 20 | {fillfactor=100}
pgbench_branches_pkey | 20 |
pgbench_history | 94062 |
pgbench_tellers | 200 | {fillfactor=100}
pgbench_tellers_pkey | 200 |
(8 rows)
"""

and after running "pgbench -n -c 4 -j 2 -T 300" a few times, i used
attached free_space.sql to see what pg_freespacemap, pgstattuple and
relation_free_space had to say about these tables. the result is
attached in result_free_space.out

my first conclusion is that pg_freespacemap is unreliable when indexes
are involved (and looking at the documentation of that module confirms
that), also the fact that FSM is not designed for accuracy make me
think is not an option.

pgstattuple and relation_free_space are very close in all the numbers
except for 2 indexes pgbench_branches_pkey and pgbench_tellers_pkey;
after a VACUUM FULL and a REINDEX (and the difference persistence) i
checked pgbench_tellers_pkey contents (it has only one page besides
the metapage) and the numbers that i look at where:

page size: 8192
free size: 4148

which in good romance means 50% of free space... so, answering Noah's
question: if that difference has some meaning i can't see it but
looking at the evidence the measure relation_free_space() is giving is
the good one

so, tomorrow (or ...looking at the clock... later today) i will update
the relation_free_space() patch to accept toast tables and other kind
of indexes and add it to the commitfest unless someone says that my
math is wrong and somehow there is a more accurate way of getting the
free space (which is entirely possible)

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación