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 47AC1D1B1B7 for ; Thu, 8 Jul 2004 06:02:47 -0300 (ADT) 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 14100-07 for ; Thu, 8 Jul 2004 09:02:48 +0000 (GMT) Received: from frodo.hserus.net (frodo.hserus.net [204.74.68.40]) by svr1.postgresql.org (Postfix) with ESMTP id 35725D1B1B3 for ; Thu, 8 Jul 2004 06:02:45 -0300 (ADT) Received: from gateway.pspl.co.in ([203.199.147.2]:3133 helo=frodo.hserus.net) by frodo.hserus.net with asmtp (Cipher TLSv1:DHE-RSA-AES256-SHA:256) (Exim 4.34 #0) id 1BiUnn-000LQD-8b by authid with plain; Thu, 08 Jul 2004 14:32:47 +0530 Message-ID: <40ED0DB3.3080209@frodo.hserus.net> Date: Thu, 08 Jul 2004 14:32:43 +0530 From: Shridhar Daithankar User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4 X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Missner, T. R." Cc: pgsql-performance@postgresql.org Subject: Re: inserting into brand new database faster than old database References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/32 X-Sequence-Number: 7423 Missner, T. R. wrote: > Hello, > > I have been a happy postgresql developer for a few years now. Recently > I have discovered a very strange phenomenon in regards to inserting > rows. > > My app inserts millions of records a day, averaging about 30 rows a > second. I use autovac to make sure my stats and indexes are up to date. > Rarely are rows ever deleted. Each day a brand new set of tables is > created and eventually the old tables are dropped. The app calls > functions which based on some simple logic perform the correct inserts. Have you profiled where the time goes in a brand new schema and a degraded database? Is it IO? Is it CPU? Is the function making decision becoming bottleneck? > The problem I am seeing is that after a particular database gets kinda > old, say a couple of months, performance begins to degrade. Even after > creating brand new tables my insert speed is slow in comparison ( by a > magnitude of 5 or more ) with a brand new schema which has the exact > same tables. I am running on an IBM 360 dual processor Linux server > with a 100 gig raid array spanning 5 scsi disks. The machine has 1 gig > of ram of which 500 meg is dedicated to Postgresql. > > Just to be clear, the question I have is why would a brand new db schema > allow inserts faster than an older schema with brand new tables? Since > the tables are empty to start, vacuuming should not be an issue at all. > Each schema is identical in every way except the db name and creation > date. You can do few things. - Get explain analyze. See the difference between actual and projected timings. The difference is the hint about where planner is going wrong. - Is IO your bottleneck? Are vacuum taking longer and longer? If yes then you could try the vacuum delay patch. If your IO is saturated for any reason, everything is going to crawl - Are your indexes bloat free? If you are using pre7.x,vacuum does not clean up indexes. You need to reindex. - Have you vacuumed the complete database? If the catalogs collect dead space it could cause degradation too but that is just a guess. Basically monitor slow inserts and try to find out where time is spent. HTH Shridhar