Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 3B63F635820 for ; Thu, 14 Jan 2010 12:08:26 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 29267-09 for ; Thu, 14 Jan 2010 16:08:15 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from 63.mail-out.ovh.net (63.mail-out.ovh.net [91.121.185.56]) by mail.postgresql.org (Postfix) with SMTP id C5D1A6342C0 for ; Thu, 14 Jan 2010 12:08:10 -0400 (AST) Received: (qmail 26155 invoked by uid 503); 14 Jan 2010 16:08:53 -0000 Received: from b6.ovh.net (HELO mail180.ha.ovh.net) (213.186.33.56) by 63.mail-out.ovh.net with SMTP; 14 Jan 2010 16:08:53 -0000 Received: from b0.ovh.net (HELO queueout) (213.186.33.50) by b0.ovh.net with SMTP; 14 Jan 2010 16:08:08 -0000 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO soyouz) (88.161.102.87) by ns0.ovh.net with SMTP; 14 Jan 2010 16:08:04 -0000 Date: Thu, 14 Jan 2010 17:08:59 +0100 To: "fkater@googlemail.com" , pgsql-performance@postgresql.org Subject: Re: Inserting 8MB bytea: just 25% of disk perf used? From: =?utf-8?Q?Pierre_Fr=C3=A9d=C3=A9ric_Caillau?= =?utf-8?Q?d?= Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 References: <20100114142903.GB2973@comppasch2> Content-Transfer-Encoding: 7bit Message-ID: In-Reply-To: <20100114142903.GB2973@comppasch2> User-Agent: Opera Mail/9.62 (Linux) X-Ovh-Tracer-Id: 4711891112230258251 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|U 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.589 tagged_above=-10 required=5 tests=BAYES_00=-2.599, FH_DATE_PAST_20XX=3.188 X-Spam-Level: X-Archive-Number: 201001/285 X-Sequence-Number: 37165 > However the harddisk (sata) could write 43 MB/s in the worst > case! Why is write performance limited to 16 MB/s? > > Some more hints what I do: > > I use PQexecParams() and the INSERT ... $001 notation to NOT > create a real escapted string from the data additionally but > use a pointer to the 8MB data buffer. > > I altered the binary column to STORAGE EXTERNAL. > > Some experiments with postgresql.conf (fsync off, > shared_buffers=1000MB, checkpoint_segments=256) did not > change the 50s- much (somtimes 60s sometimes a little less). > > 4 Core CPU 3 Ghz, WinXP, 1 TB SATA disk. Big CPU and slow disk... You should add another disk just for the WAL -- disks are pretty cheap these days. Writing the WAL on a second disk is the first thing to do on a configuration like yours, if you are limited by writes. It also reduces the fsync lag a lot since the disk is only doing WAL.