access time performance problem

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.

Responses

Browse pgsql-general by date

  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