Re: good pc but bad performance,why? - Mailing list pgsql-performance

On Wed, 2004-04-07 at 20:56, huang yaqin wrote:
> Hello, Richard Huxton,
>
>          You said  turning fsync off may cause losing data, that's terrible.
>     I use SCSI disk, and file system is ext3. I tune postgresql.conf and can't get any improvement. So what can I do?
>     Does SCSI disk and IDE disk have difference?

Yes, turning off fsync means that the database is not guaranteeing
consistency of writes to disk any longer.  On the other hand your IDE
system probably never was, because IDE drives just typically turn on
write caching in hardware without telling anyone.

SCSI typically doesn't turn on write caching in the physical drive by
default, as Tom Lane pointed out earlier.  Good SCSI has a battery
backed up cache, and then it is OK to turn on write caching, because the
controller has enough battery to complete all writes in the event of a
power failure.

One thing I recommend is to use ext2 (or almost anything but ext3).
There is no real need (or benefit) from having the database on a
journalled filesystem - the journalling is only trying to give similar
sorts of guarantees to what the fsync in PostgreSQL is doing.

The suggestion someone else made regarding use of software raid is
probably also a good one if you are trying to use the on-board RAID at
the moment.

Finally, I would say that because you are seeing poor performance on one
box and great performance on another, you should look at the hardware,
or at the hardware drivers, for the problem - not so much at PostgreSQL.

Of course if it is application performance you want to achieve, we _can_
help here, but you will need to provide more details of what you are
trying to do in your application, including;
 - confirmation that you have done a VACUUM and ANALYZE of all tables
before you start
 - output from EXPLAIN ANALYZE for slow queries
 - anything else you think is useful.

without that sort of detail we can only give vague suggestions, like
"wrap everything in a transaction" - excellent advice, certainly, but
you can read that in the manual.

There are no magic bullets, but I am sure most of the people on this
list have systems that regularly do way more than 50 inserts / second on
server hardware.

Regards,
                    Andrew McMillan

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053,  Manners St,  Wellington
WEB: http://catalyst.net.nz/             PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201       MOB: +64(21)635-694      OFFICE: +64(4)499-2267
                 http://survey.net.nz/ - any questions?
-------------------------------------------------------------------------