I have an IBM Xseries 300 single cpu with RH installed, 512Mb RAM and SCSI drive with hardware mirroring.
Postgresql database is on a partition with ext3 (journalized file system).
My greatest table contains about 30.000 records.
Postgresql in my project is used to feed/get data from an external hardware as quick as possible.
The external device ask the IBM for its configuration data, and the goal is to do a fetch on the database and to send
backthe info
as quick as possible.
The second scenario is when the external device wants to back up its configuration.
A mean time of 50ms between database accesses is foreseen.
For both scenario I have chosen auto-commit mode, because every record has to be on disc as quick as possible.
I have remarked very bad database access time performances. I have then tried with another computer : a common desktop
PC(compaq),
IDE drive, less memory and less CPU speed. I got better database access time.
Here is the results:
delete_records insert_records update_records
Compaq mean access time: 2.7ms 4.5ms 4.8ms
IBM mean access time: 22.9ms 24.6ms 25.9ms
When browsing newsgroups, I found that playing with wal_sync_method parameter could give better results.
I tried with wal_sync_method=open_sync and here are the results:
delete_records insert_records update_records
Compaq mean access time: 1.0ms 2.6ms 2.6ms
IBM mean access time: 4.0ms 1.3ms 1.3ms
My first question is: how is it possible to have such gain in time for the IBM between the case wal_sync_method=fsync
andthe case
wal_sync_method=open_sync ?
Another problem is the following:
about every 1000 database access (not regular), the database accesses are hanged during approximately 2500ms.
I suppose that this time is used by the OS to flush the memory cache to hard disk.
My second question is: how is it possible to avoid such hanging of the database ? Is it possible to flush a part of the
cachewhile
working on another part of it, the goal being not to interrupt the whole process ?
Thanx for your future comments.
--Louis Croisez.
