Re: ShmemAlloc errors - Mailing list pgsql-general

Tom Lane wrote:
> Nick Burrett <nick@dsvr.net> writes:
>
>>Tom Lane wrote:
>>
>>>Have you thought about
>>>collapsing these into *one* table with an extra key column?  Also, it'd
>>>likely be better to combine the date and time into a timestamp column.
>
>
>>I tried it back in the days when we only had around 1000 tables.
>>Problem was that inserts and deletes took a *very* long time.  IIRC a
>>one row insert was taking over 10 seconds.  I think this was because the
>>index files were growing to several gigabytes.
>
>
> That really shouldn't happen.  If the date/time range that you are
> storing moves over time (ie, you delete old stuff at the same rate you
> are adding new stuff) then I can see that you would have had an
> index-bloat problem, since the old btree code was unable to recycle
> no-longer-needed subtrees.  (This issue should go away in 7.4, FWIW.)
> However, that should only affect the size of the index on disk; I can't
> see that it would have much of an impact on insert or delete time.
> For sure I don't see how an insert could take 10 seconds.  What indexes
> and foreign keys do you have on these tables?
>
> It would be worth revisiting this and trying to understand where the
> performance problem is, rather than just assuming that you have to work
> around it.

Luckily my CVS tree has such information. Basically this database holds
samples of the number of bytes transferred in a five minute period for 3
months for 3000 servers.  The data is used for billing purposes which
work on bytes transferred in a month, so things like RRD and MRTG are
not suitable.  Five minute data is useful for spotting unusual traffic
patterns such that you might get from DoS attacks.

At the start of the fourth month, the data from the first month is
deleted and the database vacummed.

The original table definition was exactly this:

CREATE TABLE fiveminute ( server CHAR(32),
                           stamp TIMESTAMP,
                           bytesin BIGINT CHECK (bytesin >= 0),
                           bytesout BIGINT CHECK (bytesout >= 0));

CREATE UNIQUE INDEX fiveminute_idx ON fiveminute(server,stamp);

It probably would have made sense to just have an index on the server
column, but I can't remember why (decision was made Nov-2000).  I
suspect that is the cause of the index bloat.

The database never survived operational use.  The original import of
data took so long (2+ days) that the re-design was done almost immediately.

Converting back would be most useful.  I might try to do this on a 7.4
beta database.

Regards,


Nick.

--
Nick Burrett
Network Engineer, Designer Servers Ltd.   http://www.dsvr.co.uk