Re: Long-running performance (MVCC, Vacuum, etc.) - Any fix?

On Tue, Dec 28, 2004 at 02:40:52AM -0800, Eric Brown wrote:
> I'm building an appliance where I don't want my customers having to
> tune postgresql in any way from the configuration when I install it. I
> don't even want them to know it is there. Yet in my study of posgresql,
> it seems that even conscientious use of a vacuum daemon or cron job in
> vacuum's various forms leaves databases after a while much larger and
> lower performing than the actual space and performance after a
> dump/restore.

<snip>

Firstly, you havn't said what version of PostgreSQL you are using.
Recent versions behave better. Also, the autovacuum daemon will
probably do most of what you want...

> My application is multi-threaded and I could care less about the
> concurrency afforded by MVCC. I'd rather just update the things in
> place and get less postgresql concurrency but more consistent
> long-running performance and disk space utilization.
>
> Is my interpretation correct?

Yes. Although, for a simple counter you might be able to use sequences,
depending on the exact semantics. They don't take any additional space
when you increment them.

> Is there a way to turn off MVCC?

Nope. This is a Relational Database with everything that entails. If
you don't want that, maybe you should look into a database designed to
be embedded (SQLDB iirc).

> Do fixed sized rows help any?

I doubt it.

> Is there anybody using this thing in an appliance type application?

Probably, but I don't know where...

> I'm quite far along with stored-procedures and whatnot, but if
> postgresql really isn't the right solution due to these reasons, I'm
> curious if anybody has alternate OpenSource suggestions? (I'm actually
> migrating from sleepycat bsddb at the moment because I didn't realize
> the licensing costs involved there. My needs really aren't that
> extensive really - a few associative indexes, cascading delete, etc.)

I'd suggest, use the autovacuum daemon, tune your FSM settings to what
you expect the load to be and maybe schedule a database-wide
REINDEX/VACUUM FULL regularly. We found doing it once a month was
enough to keep it tip-top over the long term...

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.