From: Y Sidhu <ysidhu@gmail.com>
To: Mark Harris <mharris@esri.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: reading large BYTEA type is slower than expected
Date: Fri, 18 May 2007 10:50:25 -0700
Message-ID: <b09064f30705181050u4cb6a71akc4d92c44e860e8d5@mail.gmail.com> (raw)
In-Reply-To: <D7BFFE348C53EF4E8AA0698B1E395FA9085ABD49@flybywire.esri.com>
References: <D7BFFE348C53EF4E8AA0698B1E395FA9085ABD49@flybywire.esri.com>
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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ysidhu@gmail.com, mharris@esri.com
Subject: Re: reading large BYTEA type is slower than expected
In-Reply-To: <b09064f30705181050u4cb6a71akc4d92c44e860e8d5@mail.gmail.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