Re: Unusual table size and very slow inserts

Ok I definitely food for thought and that is what I was searching for,
as stated in my first message.
Thanks to Richard and Bill for that.
But I have a few things that still I don't understand and I think I had
not outlined enough.

>
>
>> 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.
>>
>
> Have you read the section on how MVCC and autovacuum work?  The size of
> the table is not a function of the # of records.  It's the # of records,
> plus the number of "dead rows" (which are records that have been deleted
> or updated and thus replaced with new rows).
>
>
I was not assuming the proportion between size and number of records.
I was simply comparing the size of the table that troubles me with the
size of other identical tables that have similar usage but are in other
schemas inside the same DB.


> The key is that autovacuum runs often enough that the size stabalizes
> based on usage.  For example, if you have a table that usually has 500
> rows in it and it's common for all the rows to be deleted and replaced,
> then you can expect the table to _always_ be big enough to hold 1000
> rows.  However, if all the rows are updated 5 times between each vacuum
> run, the table will be big enough to hold 2500 rows most of the time.
> Again, this is typical, it's how the system is designed to run.
>

In this table and all her sisters in the other schemas, records are only
inserted and deleted. No update

> I'm guessing (although a lot of the original email has been trimmed) that
> the actual problem you're having is that autovacuum is taking too long,
> and is slowing down modifications to table data.
>
>
This is what I was thinking , but today I was able to look at the
processes running while a client was doing a bunc of inserts. There was
no autovacuum running and every insert was taking many seconds to e
executed.
Moreover every insert seemed to increase a lot the size of the table.
Much more than the size of a record and the related index

Then after the insert autovacuum started and it took a lot of time to
complete. So the following suggestions certainly have interest to me. By
the way we are running 8.4.2 on Windows 2003 server, but the same
problem ha occurred on Windows (S)vista and Windows 7. So no chance to
upgrade :-).
> If autovacuum is taking too long and is slowing down other operations,
> you have a few options (you can do one or many of these):
> * Run autovacuum more frequently.
> * Modify your application so it modifies less rows (yes, sometimes this
>   is possible and the best solution.  The fact that the table blows
>   up to 180x the minimum size is a hint that you may be updating
>   very inefficiently.)
> * Tune PostgreSQL to make more efficient use of RAM for caching (which
>   will speed everything up)
> * Get faster hardware
> * Upgrade to a newer version of PostgreSQL that has more efficient
>   vacuum code (if you mentioned which version you are using, it was
>   lost when the message was trimmed)
>
>



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