access time performance problem
От | Louis-Marie Croisez |
---|---|
Тема | access time performance problem |
Дата | |
Msg-id | 02b401c26f73$7b5b7a30$3970cb8a@telchoi.alcatel.be обсуждение исходный текст |
Ответы |
Re: access time performance problem
|
Список | 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 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.
В списке pgsql-general по дате отправления: