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 D56BB5389A for ; Tue, 5 Apr 2005 23:55:48 +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 08445-09 for ; Tue, 5 Apr 2005 22:55:45 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.198]) by svr1.postgresql.org (Postfix) with ESMTP id 990AE53884 for ; Tue, 5 Apr 2005 23:55:44 +0100 (BST) Received: by rproxy.gmail.com with SMTP id a41so24476rng for ; Tue, 05 Apr 2005 15:55:42 -0700 (PDT) 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=N7NuHqdXGykQcVky7bDEGlSY8cYGBZPg0TJBQzhpeQZdD1TSZ37lPBZLB8sGphHCULuaW2zTCwORS1eqPoRf7BJjSkRjqK+NmPgvAcDeUBNKV5Mhib1ASuLNVtXTDLMkckl5kdt4GezsVm/lme01RgcPx7Pm0WYhYEUzqE3iaTM= Received: by 10.38.160.49 with SMTP id i49mr132633rne; Tue, 05 Apr 2005 15:55:42 -0700 (PDT) Received: by 10.38.207.41 with HTTP; Tue, 5 Apr 2005 15:55:42 -0700 (PDT) Message-ID: <59d991c405040515555c77d1e@mail.gmail.com> Date: Tue, 5 Apr 2005 18:55:42 -0400 From: Christopher Petrilli Reply-To: Christopher Petrilli To: Simon Riggs Subject: Re: Sustained inserts per sec ... ? Cc: Tom Lane , jd@commandprompt.com, "Marc G. Fournier" , pgsql-performance@postgresql.org In-Reply-To: <1112730509.16721.918.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit References: <20050401140421.P18194@ganymede.hub.org> <1112388818.3487.34.camel@jd2.commandprompt.com> <59d991c4050401125956d6069d@mail.gmail.com> <59d991c40504040648457808e9@mail.gmail.com> <26454.1112629979@sss.pgh.pa.us> <59d991c405040409094e7bbd17@mail.gmail.com> <26669.1112631807@sss.pgh.pa.us> <59d991c40504041846bc39a6@mail.gmail.com> <5217.1112668589@sss.pgh.pa.us> <1112730509.16721.918.camel@localhost.localdomain> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.318 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/88 X-Sequence-Number: 11485 On Apr 5, 2005 3:48 PM, Simon Riggs wrote: > B-trees aren't unique to PostgreSQL; the explanation developed here > would work equally well for any database system that used tree-based > indexes. Do we still think that MySQL can do this when PostgreSQL > cannot? How? > > Do we have performance test results showing the same application load > without the degradation? We don't need to look at the source code to > measure MySQL performance... http://www.amber.org/~petrilli/diagrams/comparison_mysql_pgsql.png That chart shows MySQL (using INSERT against MyISAM tables) and PostgreSQL (using COPY) running with the exact same code otherwise. Note that MySQL does hit a bit of a wall, but nothing as drastic as PostgreSQL and actually maintains something "more flat". The red and blue dashed lines are the 95th percentile point. My suspicion is that what we're seeing is WAL issues, not particularly index issues. The indices just fill up the WAL faster because there's more data. This is a wag basically, but it would seem to explain the difference. In both cases, the indices were identical. Five on each. One interesting thing... PostgreSQL starts out a good bit faster, but looses in the end. Chris -- | Christopher Petrilli | petrilli@gmail.com