pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sean Chittenden <sean@chittenden.org>
To: Martin Foster <martin@ethereal-realms.org>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Cc: PostgreSQL Novice <pgsql-novice@postgresql.org>
Subject: Re: [PERFORM] Extreme high load averages
Date: Thu, 10 Jul 2003 13:22:07 -0700
Message-ID: <20030710202207.GA10120@perrin.int.nxad.com> (raw)
In-Reply-To: <3F0CFBAE.6080002@ethereal-realms.org>
References: <Pine.LNX.4.44.0307071102390.1516-100000@zigo.dhs.org>
	<3F0CFBAE.6080002@ethereal-realms.org>

> 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



view thread (22+ messages)  latest in thread

Message-ID: <20030710202207.GA10120@perrin.int.nxad.com>
Permalink:  ../20030710202207.GA10120@perrin.int.nxad.com/
Also on:    postgresql.org/message-id/20030710202207.GA10120@perrin.int.nxad.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: sean@chittenden.org, martin@ethereal-realms.org, pgsql-novice@postgresql.org
  Subject: Re: [PERFORM] Extreme high load averages
  In-Reply-To: <20030710202207.GA10120@perrin.int.nxad.com>
  References: <Pine.LNX.4.44.0307071102390.1516-100000@zigo.dhs.org> <3F0CFBAE.6080002@ethereal-realms.org> <20030710202207.GA10120@perrin.int.nxad.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox