Re: 7.3.1 New install, large queries are slow
От | Kevin Brown |
---|---|
Тема | Re: 7.3.1 New install, large queries are slow |
Дата | |
Msg-id | 20030116020527.GA28172@filer обсуждение исходный текст |
Ответ на | 7.3.1 New install, large queries are slow ("Roman Fail" <rfail@posportal.com>) |
Список | pgsql-performance |
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@sysexperts.com
В списке pgsql-performance по дате отправления: