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:

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 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

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

On Thursday 10 Oct 2002 9:51 am, Louis-Marie Croisez wrote:

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

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 ?

I'm no expert on fs issues, but I do remember reading about bursts of write
activity occuring with ext3 (which you mentioned using in a previous mail,
and Scott remarked upon). IIRC it was something to do with ext3 buffering for
a period and then performing a bunch of writes at the same time. This looks
like a plausible candidate for your problem. The bad news is you'll have to
do some googling, since I can't remember where I heard about it. The good
news is that I seem to remember tuning that would help.

You might find vmstat/iostat useful to identify where the slowdown is
occuring.

- Richard Huxton

Hi Richard,
I have test it right now mounting the ext3 partition with ext2.
The result is identical.

LM Croisez

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

On Thursday 10 Oct 2002 9:51 am, Louis-Marie Croisez wrote:

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

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 ?

I'm no expert on fs issues, but I do remember reading about bursts of write
activity occuring with ext3 (which you mentioned using in a previous mail,
and Scott remarked upon). IIRC it was something to do with ext3 buffering for
a period and then performing a bunch of writes at the same time. This looks
like a plausible candidate for your problem. The bad news is you'll have to
do some googling, since I can't remember where I heard about it. The good
news is that I seem to remember tuning that would help.

You might find vmstat/iostat useful to identify where the slowdown is
occuring.

- Richard Huxton

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Thursday 10 Oct 2002 12:40 pm, Louis-Marie Croisez wrote:

Hi Richard,
I have test it right now mounting the ext3 partition with ext2.
The result is identical.

Hmm - that's ruled the journalling out then. Logically, it can only be two
things:

1. PostgreSQL flushing buffers or similar.
2. Another process flushing buffers or similar.

Can you try it updating batches of 200 records rather than 1000 - if the time
between delays increases that would indicate it's PG doing something.
Otherwise it could be related to logging, another process or the OS.

Try tracing activity with vmstat/iostat - that should show you what's
happening in a general sense. Then we can see if we can pin it down in
detail.

--
Richard Huxton

On Thu, 10 Oct 2002, Louis-Marie Croisez wrote:

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

I'm gonna make a guess here that it's WAL activity, or log activity.

So first off, are you saving postgresql's output to a log? if so, try
logging to a different drive than your data drive or turning off logging
for a quick test.

If it's WAL writes that are causing the problem, you can either try to
change the settings for commit_delay and commit_siblings or symlinking the
pg_xlog directory somewhere else. If you aren't sure how to do that,
don't learn how to on a live production server with real data.