--- Tom Lane <tg*@sss.pgh.pa.us> escreveu: >
--- Tom Lane <tg*@sss.pgh.pa.us> escreveu: > It's fairly unlikely that that is the solution you need. How often do
you vacuum this table?
A vacuum is executed every tree hours to the whole database after it is
updated with more than 300.000 inserts and deletes.
And is that *sufficient*? Is the table size staying constant, or
growing? It seems likely to me that you have an increasing amount
of wasted space in the table, and that the real solution requires
more frequent vacuums and/or increased FSM settings.
The tables are growing slowly.
What does "vacuum verbose" have to say about the table?
The total pages for the table usuarios is 200,000+. The default for
max_fsm_pages is 20,000, isn't it (7.3.4)? So I should set it to say, 250,000?
KakaoStats=# vacuum verbose datas;
INFO: --Relation public.datas--
INFO: Index data_ndx: Pages 2; Tuples 109: Deleted 2.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: Index datas_data_key: Pages 2; Tuples 109: Deleted 2.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Removed 2 tuples in 1 pages.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Pages 1: Changed 1, Empty 0; Tup 109: Vac 2, Keep 0, UnUsed 16.
Total CPU 0.00s/0.00u sec elapsed 0.03 sec.
VACUUM
KakaoStats=# vacuum verbose times;
INFO: --Relation public.times--
INFO: Index times_pkey: Pages 2936; Tuples 1021116: Deleted 18498.
CPU 0.38s/0.68u sec elapsed 5.95 sec.
INFO: Removed 18498 tuples in 101 pages.
CPU 0.00s/0.02u sec elapsed 0.04 sec.
INFO: Pages 5717: Changed 101, Empty 0; Tup 1021116: Vac 18498, Keep 0, UnUsed
17998.
Total CPU 0.66s/0.77u sec elapsed 6.36 sec.
VACUUM
KakaoStats=# vacuum verbose usuarios;
INFO: --Relation public.usuarios--
INFO: Index usuarios_data: Pages 88896; Tuples 33277223: Deleted 607555.
CPU 10.68s/16.75u sec elapsed 302.68 sec.
INFO: Removed 607555 tuples in 3575 pages.
CPU 0.41s/0.54u sec elapsed 3.08 sec.
INFO: Pages 202794: Changed 3575, Empty 0; Tup 33277223: Vac 607555, Keep 0,
UnUsed 590054.
Total CPU 23.01s/19.71u sec elapsed 383.32 sec.
VACUUM
shared_buffers = 3000 # min max_connections*2 or 16, 8KB each
#max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
#max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8 # min 4, typically 8KB each
Regards,
Clodoaldo
__________________________________________________ ____________________
Yahoo! Messenger - Fale com seus amigos online. Instale agora!
http://br.download.yahoo.com/messenger/
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org