Обсуждение: ERROR: REINDEX DATABASE: Can be executed only on the currently open database.

I suspect there is something wrong because it takes 73s to delete a single line
from a table whith 140 lines. So I tried to reindex the database:

bash-2.05b$ postgres -P -O -D /var/lib/pgsql/data KakaoStats
LOG:  database system was shut down at 2004-04-11 19:55:10 BRT
LOG:  checkpoint record is at 7/9505D650
LOG:  redo record is at 7/9505D650; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 24438; next oid: 1850892
LOG:  database system is ready

POSTGRES backend interactive interface
1.307.2.1 2003/01/01 21:57:18

backend> reindex database KakaoStats
ERROR:  REINDEX DATABASE: Can be executed only on the currently open database.
backend>


______________________________________________________________________

Yahoo! Messenger - Fale com seus amigos online. Instale agora!
http://br.download.yahoo.com/messenger/

=?iso-8859-1?q?Clodoaldo=20Pinto=20Neto?= <clodoaldo_pinto@yahoo.com.br> writes:
> I suspect there is something wrong because it takes 73s to delete a single line
> from a table whith 140 lines. So I tried to reindex the database:

It's fairly unlikely that that is the solution you need.  How often do
you vacuum this table?  Does it participate in any foreign keys, or do
you have any user-defined triggers on it?

> backend> reindex database KakaoStats
> ERROR:  REINDEX DATABASE: Can be executed only on the currently open database.

Double quotes would help here.

            regards, tom lane

 --- 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.

>  Does it participate in any foreign keys, or do
> you have any user-defined triggers on it?

It participates in two foreign keys. No ud triggers on it.

> > backend> reindex database KakaoStats
> > ERROR:  REINDEX DATABASE: Can be executed only on the currently open
> database.
>
> Double quotes would help here.
>
I will try it.


______________________________________________________________________

Yahoo! Messenger - Fale com seus amigos online. Instale agora!
http://br.download.yahoo.com/messenger/

=?iso-8859-1?q?Clodoaldo=20Pinto=20Neto?= <clodoaldo_pinto@yahoo.com.br> writes:
>  --- 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.

What does "vacuum verbose" have to say about the table?

            regards, tom lane

 --- 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/

=?iso-8859-1?q?Clodoaldo=20Pinto=20Neto?= <clodoaldo_pinto@yahoo.com.br> writes:
> [ reasonable-looking VACUUM VERBOSE stats ]

AFAICS those are reasonable-size indexes; REINDEX is unlikely to make
them much smaller.  Since you say you do have foreign keys involved,
that is probably where the problem is.  Check that the data types of
referencing and referenced columns match, and that you have indexes
on the referencing columns.  PG only forces you to have indexes on
the referenced columns, but if you lack the others then deletes require
seqscans through the referencing tables ...

            regards, tom lane