X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EB0F453573 for ; Thu, 31 Mar 2005 03:41:49 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 45275-05 for ; Thu, 31 Mar 2005 02:41:41 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id CF96C52956 for ; Thu, 31 Mar 2005 03:41:40 +0100 (BST) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j2V2fjAq016451 for ; (envelope-from ) Wed, 30 Mar 2005 20:41:45 -0600 (CST) Received: from [192.168.1.13] (12-215-118-172.client.mchsi.com [12.215.118.172]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j2V2fhcx019683 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256) for ; (envelope-from ) Wed, 30 Mar 2005 20:41:44 -0600 (CST) Message-ID: <424B6367.8040009@arbash-meinel.com> Date: Wed, 30 Mar 2005 20:41:43 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: fine tuning for logging server References: <1112233809.10313.31.camel@stantz.corp.sgi.com> <1112234574.12159.1.camel@stantz.corp.sgi.com> <424B5C6F.8090609@arbash-meinel.com> <1112236212.12159.28.camel@stantz.corp.sgi.com> In-Reply-To: <1112236212.12159.28.camel@stantz.corp.sgi.com> X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig6EC7BA1BE449431FF7EF6949" X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/673 X-Sequence-Number: 11378 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig6EC7BA1BE449431FF7EF6949 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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. >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 =:-> --------------enig6EC7BA1BE449431FF7EF6949 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCS2NnJdeBCYSNAAMRApaUAJkB/Zgs/bGo3G4x0l3YrFrQY4qRHgCfTiIP ZpBOL0pfeOd1hkM6uR3xR8M= =snjm -----END PGP SIGNATURE----- --------------enig6EC7BA1BE449431FF7EF6949--