Horton

Thread

  1. VACUUM produces odd freespace values

    Bruce Momjian <bruce@momjian.us> — 2010-09-18T00:45:00Z

    Can anyone explain why VACUUM after INSERT shows steadily decreasing
freespace, while DELETE of the same rows does not decrease consistently?

Specifically, after one row is inserted I see:

	SELECT pg_freespace('mvcc_demo');
	 pg_freespace
	--------------
	 (0,8128)
	(1 row)

but after inserting two more rows and deleting those two rows, I see:

	SELECT pg_freespace('mvcc_demo');
	 pg_freespace
	--------------
	 (0,8096)
	(1 row)

Seems that value should be '(0,8128)'.  Is it the unused line pointers
that are causing this?

Another odd thing --- if I change the second VACUUM to VACUUM FULL I
see:

	VACUUM FULL mvcc_demo;
	VACUUM
	SELECT pg_freespace('mvcc_demo');
	 pg_freespace
	--------------
	 (0,0)
	(1 row)

There is still a row in the table, so why is there no free space
reported?  I realize after VACUUM FULL that only the last page has
freespace --- do we assume that will be used as default for the next
addition and just not bother with the free space map? --- makes sense if
we do that.  Does this happen because cluster creates a new relfilenode?

I am attaching the init script, the SQL query script, and the results I
obtained against our CVS HEAD.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  2. Re: VACUUM produces odd freespace values

    Bruce Momjian <bruce@momjian.us> — 2010-09-18T01:00:48Z

    Bruce Momjian wrote:
> Can anyone explain why VACUUM after INSERT shows steadily decreasing
> freespace, while DELETE of the same rows does not decrease consistently?
> 
> Specifically, after one row is inserted I see:
> 
> 	SELECT pg_freespace('mvcc_demo');
> 	 pg_freespace
> 	--------------
> 	 (0,8128)
> 	(1 row)
> 
> but after inserting two more rows and deleting those two rows, I see:
> 
> 	SELECT pg_freespace('mvcc_demo');
> 	 pg_freespace
> 	--------------
> 	 (0,8096)
> 	(1 row)
> 
> Seems that value should be '(0,8128)'.  Is it the unused line pointers
> that are causing this?
> 
> Another odd thing --- if I change the second VACUUM to VACUUM FULL I
> see:
> 
> 	VACUUM FULL mvcc_demo;
> 	VACUUM
> 	SELECT pg_freespace('mvcc_demo');
> 	 pg_freespace
> 	--------------
> 	 (0,0)
> 	(1 row)
> 
> There is still a row in the table, so why is there no free space
> reported?  I realize after VACUUM FULL that only the last page has
> freespace --- do we assume that will be used as default for the next
> addition and just not bother with the free space map? --- makes sense if
> we do that.  Does this happen because cluster creates a new relfilenode?
> 
> I am attaching the init script, the SQL query script, and the results I
> obtained against our CVS HEAD.

Sorry.  Attached is trimmed-down result file that shows just the
problem.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  3. Re: VACUUM produces odd freespace values

    Thom Brown <thom@linux.com> — 2010-09-18T01:15:26Z

    On 18 September 2010 02:00, Bruce Momjian <bruce@momjian.us> wrote:
> Bruce Momjian wrote:
>> Can anyone explain why VACUUM after INSERT shows steadily decreasing
>> freespace, while DELETE of the same rows does not decrease consistently?
>>
>> Specifically, after one row is inserted I see:
>>
>>       SELECT pg_freespace('mvcc_demo');
>>        pg_freespace
>>       --------------
>>        (0,8128)
>>       (1 row)
>>
>> but after inserting two more rows and deleting those two rows, I see:
>>
>>       SELECT pg_freespace('mvcc_demo');
>>        pg_freespace
>>       --------------
>>        (0,8096)
>>       (1 row)
>>
>> Seems that value should be '(0,8128)'.  Is it the unused line pointers
>> that are causing this?
>>
>> Another odd thing --- if I change the second VACUUM to VACUUM FULL I
>> see:
>>
>>       VACUUM FULL mvcc_demo;
>>       VACUUM
>>       SELECT pg_freespace('mvcc_demo');
>>        pg_freespace
>>       --------------
>>        (0,0)
>>       (1 row)
>>
>> There is still a row in the table, so why is there no free space
>> reported?  I realize after VACUUM FULL that only the last page has
>> freespace --- do we assume that will be used as default for the next
>> addition and just not bother with the free space map? --- makes sense if
>> we do that.  Does this happen because cluster creates a new relfilenode?
>>
>> I am attaching the init script, the SQL query script, and the results I
>> obtained against our CVS HEAD.
>
> Sorry.  Attached is trimmed-down result file that shows just the
> problem.
>
> --
>  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
>  EnterpriseDB                             http://enterprisedb.com
>
>  + It's impossible for everything to be true. +
>
>
> 00-init.sql
> ------------------
> This script is designed to run in a database called test
> and requires installation of /contrib/pageinspect and
> /contrib/pg_freespacemap.
> You are now connected to database "test" as user "postgres".
> DROP TABLE IF EXISTS mvcc_demo;
> DROP TABLE
> CREATE TABLE mvcc_demo (val INTEGER);
> CREATE TABLE
> DROP VIEW IF EXISTS mvcc_demo_page0;
> DROP VIEW
> CREATE VIEW mvcc_demo_page0 AS
>        SELECT  '(0,' || lp || ')' AS ctid,
>                CASE lp_flags
>                        WHEN 0 THEN 'Unused'
>                        WHEN 1 THEN 'Normal'
>                        WHEN 2 THEN 'Redirect to ' || lp_off
>                        WHEN 3 THEN 'Dead'
>                END,
>                t_xmin::text::int8 AS xmin,
>                t_xmax::text::int8 AS xmax,
>                t_ctid
>        FROM heap_page_items(get_raw_page('mvcc_demo', 0))
>        ORDER BY lp;
> CREATE VIEW
>
> 31-vacuum-freesp.sql
> ------------------
> TRUNCATE mvcc_demo;
> TRUNCATE TABLE
> VACUUM mvcc_demo;
> VACUUM
> SELECT pg_freespace('mvcc_demo');
>  pg_freespace
> --------------
> (0 rows)
>
> INSERT INTO mvcc_demo VALUES (1);
> INSERT 0 1
> VACUUM mvcc_demo;
> VACUUM
> SELECT pg_freespace('mvcc_demo');
>  pg_freespace
> --------------
>  (0,8128)
> (1 row)
>
> INSERT INTO mvcc_demo VALUES (2);
> INSERT 0 1
> VACUUM mvcc_demo;
> VACUUM
> SELECT pg_freespace('mvcc_demo');
>  pg_freespace
> --------------
>  (0,8096)
> (1 row)
>
> INSERT INTO mvcc_demo VALUES (3);
> INSERT 0 1
> VACUUM mvcc_demo;
> VACUUM
> SELECT pg_freespace('mvcc_demo');
>  pg_freespace
> --------------
>  (0,8064)
> (1 row)
>
> DELETE FROM mvcc_demo WHERE val = 3;
> DELETE 1
> VACUUM mvcc_demo;
> VACUUM
> SELECT pg_freespace('mvcc_demo');
>  pg_freespace
> --------------
>  (0,8096)
> (1 row)
>
> DELETE FROM mvcc_demo WHERE val = 2;
> DELETE 1
> VACUUM mvcc_demo;
> VACUUM
> SELECT pg_freespace('mvcc_demo');
>  pg_freespace
> --------------
>  (0,8096)
> (1 row)
>
> SELECT * FROM mvcc_demo_page0;
>  ctid  |  case  | xmin | xmax | t_ctid
> -------+--------+------+------+--------
>  (0,1) | Normal | 1339 |    0 | (0,1)
>  (0,2) | Unused |      |      |
>  (0,3) | Unused |      |      |
> (3 rows)
>
> DELETE FROM mvcc_demo WHERE val = 1;
> DELETE 1
> VACUUM mvcc_demo;
> VACUUM
> SELECT pg_freespace('mvcc_demo');
>  pg_freespace
> --------------
> (0 rows)
>
> VACUUM mvcc_demo;
> VACUUM
> SELECT pg_relation_size('mvcc_demo');
>  pg_relation_size
> ------------------
>                0
> (1 row)
>

That's odd.  When I delete val 2, the freespace goes back up in 9.0rc1
(attached).

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

  4. Re: VACUUM produces odd freespace values

    Bruce Momjian <bruce@momjian.us> — 2010-09-18T01:34:35Z

    Thom Brown wrote:
> > VACUUM mvcc_demo;
> > VACUUM
> > SELECT pg_relation_size('mvcc_demo');
> > ?pg_relation_size
> > ------------------
> > ? ? ? ? ? ? ? ?0
> > (1 row)
> >
> 
> That's odd.  When I delete val 2, the freespace goes back up in 9.0rc1
> (attached).

Your numbers are odd too.  With one row after INSERT you show:

 (0,8128)

but after delete you with one row left you show:

 (0,8096)

You also dip to (0,8032), which I don't see.  I now see we only track
range of free space values. For example, freespace.c has:

 * We use just one byte to store the amount of free space on a page, so we
 * divide the amount of free space a page can have into 256 different
 * categories. The highest category, 255, represents a page with at least
 * MaxFSMRequestSize bytes of free space, and the second highest category
 * represents the range from 254 * FSM_CAT_STEP, inclusive, to
 * MaxFSMRequestSize, exclusive.
 *
 * MaxFSMRequestSize depends on the architecture and BLCKSZ, but assuming
 * default 8k BLCKSZ, and that MaxFSMRequestSize is 24 bytes, the categories
 * look like this
 *
 *
 * Range     Category
 * 0    - 31   0
 * 32   - 63   1
 * ...    ...  ...
 * 8096 - 8127 253
 * 8128 - 8163 254
 * 8164 - 8192 255

So, my guess is that the unused item pointers are causing the free space
to fall into a smaller category than we had after the first INSERT.  I
bet if I pulled more columns from heap_page_items() I could see it.

Anyway, I think I have my explaination now.  Thanks.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +