new full vacuum doesn't work
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 relations
Schema | 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 relations
Schema | 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
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
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
Show quoted text
=# 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.37Regards,
---
Takahiro Itagaki
NTT Open Source Software Center
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
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
Show quoted text
Regards,
---
Takahiro Itagaki
NTT Open Source Software Center