Re: 7.3.1 New install, large queries are slow

Roman Fail wrote:
> I am trying to get a PostgreSQL server into production (moving from
> MSSQL2K) but having some serious performance issues. PostgreSQL is
> new to me, and I'm only just now comfortable with Linux. So far
> I've succesfully compiled postgres from source and migrated all the
> data from MSSQL. Postgres is primarily accessed using JDBC.

[...]

> POSTGRESQL SYSTEM:
> Red Hat Linux 8.0, PostgreSQL 7.3.1 (dedicated, besides SSH daemon)
> Dell PE6600 Dual Xeon MP 2.0GHz, 2MB L3 cache,HyperThreading enabled
> 4.0 GB Physical RAM
> /dev/sda1: ext3 101MB /boot
> /dev/sda2: ext3 34GB / (sda is 2 disk RAID-1)
> none : swap 1.8GB
> /dev/sdb1: ext3 104GB /usr/local/pgsql/data (sdb is 6 disk RAID-10)
> All 8 drives are 36GB, 15k RPM, Ultra160 SCSI
> PERC3/DC 128MB RAID controller

Ext3, huh? Ext3 is a journalling filesystem that is capable of
journalling data as well as metadata. But if you mount it such that
it journals data, writes will be significantly slower.

The default for ext3 is to do ordered writes: data is written before
the associated metadata transaction commits, but the data itself isn't
journalled. But because PostgreSQL synchronously writes the
transaction log (using fsync() by default, if I'm not mistaken) and
uses sync() during a savepoint, I would think that ordered writes at
the filesystem level would probably buy you very little in the way of
additional data integrity in the event of a crash.

So if I'm right about that, then you might consider using the
"data=writeback" option to ext3 on the /usr/local/pgsql/data
filesystem. I'd recommend the default ("data=ordered") for everything
else.

That said, I doubt the above change will make the orders of magnitude
difference you're looking for. But every little bit helps...

You might also consider experimenting with different filesystems, but
others here may be able to chime in with better information on that.

People, please correct me if I'm wrong in my analysis of PostgreSQL on
ext3 above. If the database on an ext3 filesystem mounted in
writeback mode is subject to corruption upon a crash despite the
efforts PostgreSQL makes to keep things sane, then writeback mode
shouldn't be used! And clearly it shouldn't be used if it doesn't
make a significant performance difference.

--
Kevin Brown kevin(at)sysexperts(dot)com