X-Original-To: pgsql-novice-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id D2C79CF0D3; Thu, 10 Jul 2003 23:53:26 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 07493-06; Thu, 10 Jul 2003 20:53:10 -0300 (ADT) Received: from perrin.int.nxad.com (internal.ext.nxad.com [69.1.70.251]) by svr1.postgresql.org (Postfix) with ESMTP id 1B546CB993; Thu, 10 Jul 2003 17:21:53 -0300 (ADT) Received: by perrin.int.nxad.com (Postfix, from userid 1001) id D6B2F20F00; Thu, 10 Jul 2003 13:22:07 -0700 (PDT) Date: Thu, 10 Jul 2003 13:22:07 -0700 From: Sean Chittenden To: Martin Foster Cc: PostgreSQL Performance , PostgreSQL Novice Subject: Re: [PERFORM] Extreme high load averages Message-ID: <20030710202207.GA10120@perrin.int.nxad.com> References: <3F0CFBAE.6080002@ethereal-realms.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3F0CFBAE.6080002@ethereal-realms.org> X-PGP-Key: finger seanc@FreeBSD.org X-PGP-Fingerprint: 3849 3760 1AFE 7B17 11A0 83A6 DD99 E31F BC84 B341 X-Web-Homepage: http://sean.chittenden.org/ User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/91 X-Sequence-Number: 7626 > I actually got a bit more respect for PostgreSQL tonight. It seems > that one of my scripts was not committing changes after maintenance > was conducted. Meaning that rows that would normally be removed > after offline archiving was completed were in fact still around. > > Normally at any given point in time this table would grow 50K rows > during a day, be archived that night and then loose rows that were > no longer needed. This process, is what allowed MySQL to maintain > any stability as the size of this table can balloon significantly. > > PostgreSQL with tweaking was handling a table with nearly 300K rows. > That size alone would of dragged the MySQL system down to a near > grind, and since most of those rows are not needed. One can imagine > that queries are needlessly processing rows that should be outright > ignored. Having used MySQL once upon a time and run into it's problems when you have more than 1M rows in a table, it took me a while when 1st using PostgreSQL to trust that PostgreSQL can reliably handle millions or billions of rows without crapping out randomly and corrupting itself. If you would have let this grow, you'd run out of disk space long before you hit anything close to a stability, reliability, or performance problem with PostgreSQL. I have one table in particular that has about 1.9B rows in it right now and it conservatively takes about 0.04ms for non-complex queries to run against the table. In MySQL land, I wouldn't dare let something grow that big... which would've been a huge problem because the table mentioned above isn't logging data or something I can routinely purge. It's a strange feeling at first to not have to design your application around size or tuple limitations of the database any more. :) I'm glad you're enjoying PostgreSQL. -sc -- Sean Chittenden