Re: ERROR: REINDEX DATABASE: Can be executed only on the currently open database. - Mailing list pgsql-general

 --- Tom Lane <tgl@sss.pgh.pa.us> escreveu: >
> >  --- Tom Lane <tgl@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/