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 3F35B53682 for ; Fri, 1 Apr 2005 20:38:45 +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 34315-08 for ; Fri, 1 Apr 2005 19:38:36 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.200]) by svr1.postgresql.org (Postfix) with ESMTP id AE86853232 for ; Fri, 1 Apr 2005 20:38:35 +0100 (BST) Received: by rproxy.gmail.com with SMTP id a41so737961rng for ; Fri, 01 Apr 2005 11:38:37 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=tJ34NjFbMZYn0zxyAfPVVY56Czui2d3UIV6BhIGrj46P7aHa6R3UnnPDMfQ89XJwrISUVjblsFvtfQoiNiFhCpVVWM/4XIu1KWVSiYCpvm+vHb5IF9BuO9uvB453d9PtagLi7MabWPXT3a1qNsBPfEgx+/iKosGpRiIgmbyktxM= Received: by 10.38.69.34 with SMTP id r34mr3076361rna; Fri, 01 Apr 2005 11:38:36 -0800 (PST) Received: by 10.38.207.41 with HTTP; Fri, 1 Apr 2005 11:38:36 -0800 (PST) Message-ID: <59d991c4050401113857086517@mail.gmail.com> Date: Fri, 1 Apr 2005 14:38:36 -0500 From: Christopher Petrilli Reply-To: Christopher Petrilli To: "Marc G. Fournier" Subject: Re: Sustained inserts per sec ... ? Cc: pgsql-performance@postgresql.org In-Reply-To: <20050401140421.P18194@ganymede.hub.org> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit References: <20050401140421.P18194@ganymede.hub.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.228 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/14 X-Sequence-Number: 11411 On Apr 1, 2005 1:06 PM, Marc G. Fournier wrote: > > Just curious, but does anyone have an idea of what we are capable of? I > realize that size of record would affect things, as well as hardware, but > if anyone has some ideas on max, with 'record size', that would be > appreciated ... On a AMD64/3000, 1Gb RAM, 2 SATA drives (1 for log, 1 for data), and inserting using batches of 500-1000 rows, and also using the COPY syntax, I have seen an interesting thing. There are 5 indexes involved here, BTW. This is Linux 2.6 running on an XFS file system (ext3 was even worse for me). I can start at about 4,000 rows/second, but at about 1M rows, it plummets, and by 4M it's taking 6-15 seconds to insert 1000 rows. That's only about 15 rows/second, which is quite pathetic. The problem seems to be related to my indexes, since I have to keep them online (the system in continually querying, as well). This was an application originally written for MySQL/MYISAM, and it's looking like PostgreSQL can't hold up for it, simply because it's "too much database" if that makes sense. The same box, running the MySQL implementation (which uses no transactions) runs around 800-1000 rows/second systained. Just a point of reference. I'm trying to collect some data so that I can provide some charts of the degredation, hoping to find the point where it dies and thereby find the point where it needs attention. Chris -- | Christopher Petrilli | petrilli@gmail.com