Re: access time performance problem - Mailing list pgsql-general

Sorry, bad url:
use this instead:
http://louis.croisez.free.fr/capture1.jpg

LM Croisez

----- Original Message -----
From: "Louis-Marie Croisez" <louis-marie.croisez@etca.alcatel.be>
To: "scott.marlowe" <scott.marlowe@ihs.com>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, October 10, 2002 10:41 AM
Subject: Re: [GENERAL] access time performance problem


> Thanx for your response Scott.
> In order to illustrate my problem, here is a capture of an Excel graph showing the access peaks I get:
> http://louis.croisez.free.fr/download/capture1.jpg
> The test program makes updates of a table (on 1000 records randomly) every 50ms.
> Approximately, every about 3 seconds, the update process is hanged for about 800ms (these data are measured on the
Compaq.On the 
> IBM, the results are worse).
> This is a great problem for us.
> Could you give me some help to correct that ?
>
> LM Croisez.
>
>
>
> ----- Original Message -----
> From: "scott.marlowe" <scott.marlowe@ihs.com>
> To: "Louis-Marie Croisez" <louis-marie.croisez@etca.alcatel.be>
> Cc: <pgsql-general@postgresql.org>
> Sent: Wednesday, October 09, 2002 6:46 PM
> Subject: Re: [GENERAL] access time performance problem
>
>
> > Quick question, are you regularly vacuuming and analyzing your database?
> >
> > Also, ext3 can definitely slow things down.  If your machine is stable and
> > on a UPS it may be worth your while to just run ext2.
> >
> > Also, have you compared output from bonnie++ on the compaq against the
> > IBM (run it on the same drive that hosts the database of course.)  it's a
> > free program you can download to test your drive subsystem's performance.
> > A SCSI mirror set on 10k drives should be able to read at >30 Megs a
> > second and an IDE drive should be in the 5 to 15 Megs a second range.
> >
> > Since Postgresql is designed more for integrity and transactions, it may
> > not be your best choice here.  I'm not sure what would be your best
> > choice, but Postgresql is not known for being a real time system with
> > performance guarantees on response times.
> >
> > Also, what processor speeds are these two machines?  Just wondering.
> >
> > On Wed, 9 Oct 2002, Louis-Marie Croisez wrote:
> >
> > > 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
sendback 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
desktopPC 
> (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=fsyncand 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
ofthe cache 
> while
> > > working on another part of it, the goal being not to interrupt the whole process ?
> > >
> > > Thanx for your future comments.
> > >
> > > --Louis Croisez.
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>