Re: fine tuning for logging server

Florin Andrei wrote:

>On Wed, 2005-03-30 at 20:11 -0600, John Arbash Meinel wrote:
>
>
>>Florin Andrei wrote:
>>
>>
>>>For performance reasons, i was thinking to keep the tables append-only,
>>>and simply rotate them out every so often (daily?) and delete those
>>>tables that are too old. Is that a good idea?
>>>
>>>
>>>
>>If you aren't doing updates, then I'm pretty sure the data stays packed
>>pretty well. I don't know that you need daily rotations, but you
>>certainly could consider some sort of rotation schedule.
>>
>>
>
>(sorry for re-asking, i'm coming from a mysql mindset and i still have a
>lot to learn about pgsql)
>
>So, it is indeed a bad idea to delete rows from tables, right? Better
>just rotate to preserve the performance.
>
>
The only problems are if you get a lot of old tuples in places you don't
want them. If you are always appending new values that are increasing,
and you are deleting from the other side, I think vacuum will do a fine
job at cleaning up. It's deleting/updating every 3rd entry that starts
to cause holes (though probably vacuum still does a pretty good job).

>Daily rotation may simplify the application logic - then i'll know that
>each table is one day's worth of data.
>
>
>
I don't think it is necessary, but if you like it, go for it. I would
tend to think that you would want a "today" table, and a "everything
else" table, as it simplifies your queries, and lets you have foreign
keys (though if you are from mysql, you may not be used to using them.)

>>The biggest performance improvement, though, is probably to group
>>inserts into transactions.
>>
>>
>
>Yes, i know that. I have little control over the apps, though. I'll see
>what i can do.
>
>
You could always add a layer inbetween. Or look at my mention of a fast
temp table, with a periodic cron job to pull in the new data. You can
run cron as fast as 1/min which might be just right depending on your needs.
It also means that you could ignore foreign keys and indexes on the temp
table, and only evaluate them on the main table.

>
>
>>How fast is fast? How many log messages are you expecting? 1/s 100/s 1000/s?
>>
>>
>
>More is better. <shrug>
>I guess i'll put it together and give it a spin and see just how far it
>goes.
>
>I actually have some controls over the data that's being sent (in some
>places i can limit the number of events/second), so that might save me
>right there.
>
>
>
Good luck. And remember, tuning your queries can be just as important.
(Though if you are doing append only inserts, there probably isn't much
that you can do).

If all you are doing is append only logging, the fastest thing is
probably just a flat file. You could have something that comes along
later to move it into the database. It doesn't really sound like you are
using any features a database provides. (normalization, foreign keys,
indexes, etc.)

John
=:->