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 57A3253573 for ; Thu, 31 Mar 2005 03:30:18 +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 39756-09 for ; Thu, 31 Mar 2005 02:30:15 +0000 (GMT) Received: from rwcrmhc12.comcast.net (rwcrmhc12.comcast.net [216.148.227.85]) by svr1.postgresql.org (Postfix) with ESMTP id 1D3DC5348D for ; Thu, 31 Mar 2005 03:30:10 +0100 (BST) Received: from andrei.myip.org ([24.6.189.81]) by comcast.net (rwcrmhc12) with ESMTP id <2005033102301401400dvruqe>; Thu, 31 Mar 2005 02:30:14 +0000 Received: from localhost (weiqi.home.local [127.0.0.1]) by andrei.myip.org (Postfix) with ESMTP id 6696DB4067 for ; Wed, 30 Mar 2005 18:30:14 -0800 (PST) Received: from andrei.myip.org ([127.0.0.1]) by localhost (weiqi.home.local [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00612-08 for ; Wed, 30 Mar 2005 18:30:12 -0800 (PST) Received: from [192.168.2.6] (unknown [192.168.2.6]) by andrei.myip.org (Postfix) with ESMTP id 9ACA0B4063 for ; Wed, 30 Mar 2005 18:30:12 -0800 (PST) Subject: Re: fine tuning for logging server From: Florin Andrei Reply-To: pgsql-performance@postgresql.org To: pgsql-performance@postgresql.org In-Reply-To: <424B5C6F.8090609@arbash-meinel.com> References: <1112233809.10313.31.camel@stantz.corp.sgi.com> <1112234574.12159.1.camel@stantz.corp.sgi.com> <424B5C6F.8090609@arbash-meinel.com> Content-Type: text/plain Date: Wed, 30 Mar 2005 18:30:12 -0800 Message-Id: <1112236212.12159.28.camel@stantz.corp.sgi.com> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-2) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at andrei.myip.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.033 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/671 X-Sequence-Number: 11376 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. Daily rotation may simplify the application logic - then i'll know that each table is one day's worth of data. > 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. > 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. -- Florin Andrei http://florin.myip.org/