Postgres Pro
Downloads
Home   >   mailing lists

Thread: new full vacuum doesn't work

new full vacuum doesn't work

From
Pavel Stehule
Date:
Hello

I am testing vacuum changes, and I found some strange behave:

autovacuum off

[pavel@nemesis src]$ /usr/local/pgsql/bin/pgbench -i -F 10 -s 10 test
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping


test=# \dt+                         List of relationsSchema |       Name       | Type  | Owner |    Size    |
Description
--------+------------------+-------+-------+------------+-------------public | pgbench_accounts | table | pavel | 1302
MB   |public | pgbench_branches | table | pavel | 8192 bytes |public | pgbench_history  | table | pavel | 0 bytes
|public| pgbench_tellers  | table | pavel | 48 kB      |
 
(4 rows)

test=# \x
Expanded display is on.
test=# select * from pgstattuple('pgbench_accounts');
-[ RECORD 1 ]------+-----------
table_len          | 1365336064
tuple_count        | 1000000
tuple_len          | 121000000
tuple_percent      | 8.86
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 1228669388
free_percent       | 89.99

test=# vacuum full pgbench_accounts;
VACUUM

test=# \dt+                         List of relationsSchema |       Name       | Type  | Owner |    Size    |
Description
--------+------------------+-------+-------+------------+-------------public | pgbench_accounts | table | pavel | 1302
MB   |public | pgbench_branches | table | pavel | 8192 bytes |public | pgbench_history  | table | pavel | 0 bytes
|public| pgbench_tellers  | table | pavel | 48 kB      |
 
(4 rows)

test=# select * from pgstattuple('pgbench_accounts');
-[ RECORD 1 ]------+-----------
table_len          | 1365336064
tuple_count        | 1000000
tuple_len          | 128000000
tuple_percent      | 9.37
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 1228669388
free_percent       | 89.99

Regards
Pavel

Re: new full vacuum doesn't work

From
Takahiro Itagaki
Date:
Pavel Stehule <pavel.stehule@gmail.com> wrote:

> I am testing vacuum changes, and I found some strange behave:

Did you need "SET (fillfactor=100)" before vACUUM FULL?

=# select * from pgstattuple('pgbench_accounts');
-[ RECORD 1 ]------+-----------
table_len          | 1365336064
tuple_count        | 1000000
tuple_len          | 121000000
tuple_percent      | 8.86
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 1228669388
free_percent       | 89.99

=# ALTER TABLE pgbench_accounts SET (fillfactor=100);
ALTER TABLE
=# vacuum full pgbench_accounts;
VACUUM
=# select * from pgstattuple('pgbench_accounts');
-[ RECORD 1 ]------+----------
table_len          | 134299648
tuple_count        | 1000000
tuple_len          | 128000000
tuple_percent      | 95.31
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 1840616
free_percent       | 1.37

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

Re: new full vacuum doesn't work

From
Pavel Stehule
Date:
2010/1/8 Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp>:
>
> Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>> I am testing vacuum changes, and I found some strange behave:
>
> Did you need "SET (fillfactor=100)" before vACUUM FULL?

no, I tested it and with FILLFACTOR 100 VACUUM FULL is successful.

Personally I thing, so this behave is bad. Or there is wrong default
fillfactor 0.

Regards
Pavel Stehule

>
> =# select * from pgstattuple('pgbench_accounts');
> -[ RECORD 1 ]------+-----------
> table_len          | 1365336064
> tuple_count        | 1000000
> tuple_len          | 121000000
> tuple_percent      | 8.86
> dead_tuple_count   | 0
> dead_tuple_len     | 0
> dead_tuple_percent | 0
> free_space         | 1228669388
> free_percent       | 89.99
>
> =# ALTER TABLE pgbench_accounts SET (fillfactor=100);
> ALTER TABLE
> =# vacuum full pgbench_accounts;
> VACUUM
> =# select * from pgstattuple('pgbench_accounts');
> -[ RECORD 1 ]------+----------
> table_len          | 134299648
> tuple_count        | 1000000
> tuple_len          | 128000000
> tuple_percent      | 95.31
> dead_tuple_count   | 0
> dead_tuple_len     | 0
> dead_tuple_percent | 0
> free_space         | 1840616
> free_percent       | 1.37
>
> Regards,
> ---
> Takahiro Itagaki
> NTT Open Source Software Center
>
>
>

Re: new full vacuum doesn't work

From
Takahiro Itagaki
Date:
Pavel Stehule <pavel.stehule@gmail.com> wrote:

> Personally I thing, so this behave is bad. Or there is wrong default
> fillfactor 0.

No, you used fillfactor=10 here:
>> [pavel@nemesis src]$ /usr/local/pgsql/bin/pgbench -i -F 10 -s 10 test
      ~~~~~
 
Pgbench sets the table's fillfactor to 10.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

Re: new full vacuum doesn't work

From
Pavel Stehule
Date:
2010/1/8 Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp>:
>
> Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>> Personally I thing, so this behave is bad. Or there is wrong default
>> fillfactor 0.
>
> No, you used fillfactor=10 here:
>>> [pavel@nemesis src]$ /usr/local/pgsql/bin/pgbench -i -F 10 -s 10 test
>                                                        ~~~~~
> Pgbench sets the table's fillfactor to 10.

ok, my error

thank you
Pavel

>
> Regards,
> ---
> Takahiro Itagaki
> NTT Open Source Software Center
>
>
>