Re: Unusual table size and very slow inserts

Richard Huxton ha scritto:
> On 02/02/10 14:46, Ivano Luberti wrote:
>> Sorry to post this again, but I have seen no response at all and this is
>> strange on this list.
>> Maybe I have not properly submitted my question ?
>
> You've replied to an existing question, which means your message is
> hidden in amidst the replies to that.
>
I don't want to bore the list with this but I don't understand. I have
posted a new message to the list. I didn't reply to anything......or al
least I didn't mean to do that.

>
>> In only one case so far, the "code" table with 442 record has a size of
>> 18MB. If I run an vacuum full and a reindex it shrinks to less than
>> 100KB.
>> If I use the software to delete the rows and reinsert the same records
>> it explodes again to 18MB.
>
> That suggests the autovacuum system isn't checking the table often
> enough. Or, perhaps that you have a long-lived transaction that is
> preventing it from reclaiming space.
>
> Autovacuum is disussed at the bottom of this page:
> http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html
> The "storage parameters" link has details on setting vacuum parameters
> for a single table.
>
> If your application is sat there holding open a transaction without
> doing anything stop doing that. It means the system can't be sure it's
> safe to reclaim the space used by old versions of rows.
>
No the application is doing what is supposed to do: inserting records.
But when the size of the table is so great insert become really slow, so
indeed autovacuum has been canceled a few times.
Moreover when autovacuum runs and the application is idle he is able to
run but not able to claim space.
Then if I run vacuum manually with full, freeze and analyze checked and
also I run reindex everything return to normality.

What really worries and puzzles me is the size of the table is not
coherent with other copies of the same table with similar records number.

--
==================================================
dott. Ivano Mario Luberti
Archimede Informatica societa' cooperativa a r. l.
Sede Operativa
Via Gereschi 36 - 56126- Pisa
tel.: +39-050- 580959
tel/fax: +39-050-9711344
web: www.archicoop.it
==================================================