Re: reading large BYTEA type is slower than expected

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(at)esri(dot)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