Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id A33629FC38B for ; Fri, 18 May 2007 14:50:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 92404-04 for ; Fri, 18 May 2007 14:50:25 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.245]) by postgresql.org (Postfix) with ESMTP id D290E9FC386 for ; Fri, 18 May 2007 14:50:26 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id d17so243606and for ; Fri, 18 May 2007 10:50:25 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=k+DDhRQOnaTFsv2mqMel6ZL38ehm3/9jroGFaycDtTP1hXMIMBqVpEduBSUDZTJuj4LjdYNgC9o5dDIpWVNCoCuOsCzTr0cY9gZipqYlaqLS0cJE+hDT7yG4tg6sgM5BgWL+ZF7mtHrzNJtBJK5w68IKJFeOBMxX7MnYNMJzScU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=a60n6uxYN//lYn+OZ3NTAFeYbqv3GQH8GWXlcMsNuijXsTeG3Vj1E73i4TMMiikfp1c/Pk63t9HYUK57C6eyTlIZrL99uzyi76q2zuIuD/HVZjEN3vhd629i0gHaCkh/vmOhYxamG9is9aNj6Xgp6yB8q4d0PlsD+dmV2/KDBYo= Received: by 10.100.165.9 with SMTP id n9mr1292198ane.1179510625518; Fri, 18 May 2007 10:50:25 -0700 (PDT) Received: by 10.100.6.20 with HTTP; Fri, 18 May 2007 10:50:25 -0700 (PDT) Message-ID: Date: Fri, 18 May 2007 10:50:25 -0700 From: "Y Sidhu" To: "Mark Harris" Subject: Re: reading large BYTEA type is slower than expected Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_76962_11284557.1179510625231" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/330 X-Sequence-Number: 24720 ------=_Part_76962_11284557.1179510625231 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Mark, I am no expert but this looks like a file system I/O thing. I set hw.ata.wc=1 for a SATA drive and =0 for a SCSI drive in /boot/loader.conf on my FreeBSD systems. That seems to provide some needed tweaking. Yudhvir ========== On 5/18/07, Mark Harris wrote: > > We have recently ported our application to the postgres database. For the > most part performance has not been an issue; however there is one situation > that is a problem and that is the initial read of rows containing BYTEA > values that have an average size of 2 kilobytes or greater. For BYTEA values > postgres requires as much 3 seconds to read the values from disk into its > buffer cache. After the initial read into buffer cache, performance is > comparable to other commercial DBMS that we have ported to. As would be > expected the commercial DBMS are also slower to display data that is not > already in the buffer cache, but the magnitude of difference for postgres > for this type of data read from disk as opposed to read from buffer cache is > much greater. > > > > We have vacuumed the table and played around with the database > initialization parameters in the postgresql.conf. Neither helped with this > problem. > > > > Does anyone have any tips on improving the read from disk performance of > BYTEA data that is typically 2KB or larger? > > > > Mark > -- Yudhvir Singh Sidhu 408 375 3134 cell ------=_Part_76962_11284557.1179510625231 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Mark,

I am no expert but this looks like a file system I/O thing. I set hw.ata.wc=1 for a SATA drive and =0 for a SCSI drive in /boot/loader.conf on my FreeBSD systems. That seems to provide some needed tweaking.

Yudhvir
==========
On 5/18/07, Mark Harris <mharris@esri.com> wrote:

We have recently ported our application to the postgres database. For the most part performance has not been an issue; however there is one situation that is a problem and that is the initial read of rows containing BYTEA values that have an average size of 2 kilobytes or greater. For BYTEA values postgres requires as much 3 seconds to read the values from disk into its buffer cache. After the initial read into buffer cache, performance is comparable to other commercial DBMS that we have ported to. As would be expected the commercial DBMS are also slower to display data that is not already in the buffer cache, but the magnitude of difference for postgres for this type of data read from disk as opposed to read from buffer cache is much greater.

 

We have vacuumed the table and played around with the database initialization parameters in the postgresql.conf. Neither helped with this problem.

 

Does anyone have any tips on improving the read from disk performance of BYTEA data that is typically 2KB or larger?

 

Mark




--
Yudhvir Singh Sidhu
408 375 3134 cell ------=_Part_76962_11284557.1179510625231--