From: | "Louis-Marie Croisez" <louis-marie(dot)croisez(at)etca(dot)alcatel(dot)be> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | access time performance problem |
Date: | 2002-10-09 09:08:46 |
Message-ID: | 02b401c26f73$7b5b7a30$3970cb8a@telchoi.alcatel.be |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 back the 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 and the 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 cache while
working on another part of it, the goal being not to interrupt the whole process ?
Thanx for your future comments.
--Louis Croisez.
From | Date | Subject | |
---|---|---|---|
Next Message | Savita | 2002-10-09 09:15:41 | Re: How to find out about index |
Previous Message | Shridhar Daithankar | 2002-10-09 08:25:28 | Re: Large databases, performance |