Thread: Poor performance on SCSI machines, good on IDE?

Poor performance on SCSI machines, good on IDE?

From
Eric Crampton
Date:
I'm seeing some strange performance behavior with PostgreSQL. In the
following four examples, I'm using the PGDG RPMs on Redhat Linux
7.2. On the machines with IDE disks, I'm seeing *MUCH* faster INSERTs
than the machines with SCSI. I'm trying to figure out why. Disk
benchmarking programs show the SCSI machines should have much faster
seek and transfer times.

setup.sql contains two CREATEs, one for a table, one for an index on
that table. insert_test.c is a simple program which does a bunch of
PQexec()'s to insert 20,000 rows into the table. In all runs,
PostgreSQL runs on the same machine as insert_test.

To make a long story short, the IDE machines accomplish this in around
40-60 seconds, whereas the (much beefier) SCSI based machines
accomplish this in around 9-10 *MINUTES*. What gives?

I've placed a tarball of the setup.sql and insert_test.c files at:
http://www.atdesk.com/eric/dbbench.tar.gz.

I know I could get better performance by wrapping everything with a
single transaction, but I don't want to do that because that's
(unfortunately) not the way my real workload can be done (each INSERT
must be complete and committed before I can proceed to the next). At
some points in the day, I'll be doing as many as 500 INSERTs in a
given second. I'm having a hard time getting that out of PostgreSQL,
but I don't know how to tune it very well, yet.

Incidently, if I turn fsync off on the SCSI machines, the performance
absolutely soars. Of course, I don't really want to do this in
production.

----------------------------------------------------------------------
Methodology:

1. Setup configuration:
   sort_mem = 512
   shared_buffers = 3000
   fsync = true

2. Start Postgres (as root):
   # /etc/rc.d/init.d/postgresql start

3. Initialize database (as postgres):
   $ createdb test
   $ psql -f setup.sql test
   $ gcc -o insert_test insert_test.c -lpq
   $ time ./insert

----------------------------------------------------------------------

Results:

elmo:
Dual 850 MHz Pentium III, 1 GB RAM, one disk, kernel 2.4.9-13smp
Uniform Multi-Platform E-IDE driver Revision: 6.31
ide: Assuming 33MHz PCI bus speed for PIO modes; override with idebus=xx
PIIX4: IDE controller on PCI bus 00 dev 39
hda: Maxtor 5T060H6, ATA DISK drive
hda: 120103200 sectors (61493 MB) w/2048KiB Cache, CHS=7476/255/63, UDMA(33)
system otherwise idle
time: real    0m57.876s

shara:
Dual 850 MHz Pentium III, 512 MB RAM, one disk, kernel 2.4.2-2smp
Uniform Multi-Platform E-IDE driver Revision: 6.31
ide: Assuming 33MHz system bus speed for PIO modes; override with idebus=xx
PIIX4: IDE controller on PCI bus 00 dev 39
hda: QUANTUM FIREBALLP LM30, ATA DISK drive
hda: 58633344 sectors (30020 MB) w/1900KiB Cache, CHS=3649/255/63, UDMA(33)
time: real    0m38.748s

borg:
Dual 1 GHz Pentium III, 2 GB RAM, one SCSI disk, kernel 2.4.9-13smp
Four ethernet interfaces (2xi82557 100Mb, 2x3C985 gigabit ethernet)
Adaptec AIC-7892 Ultra 160 adapter w/one disk w/ext3
Attached scsi disk sda at scsi0, channel 0, id 0, lun 0
SCSI device sda: 72205440 512-byte hdwr sectors (36969 MB)
time: real    9m52.588s (Yes, almost *10* minutes)

testamd:
Dual 1.2 GHz Athlons, 1 GB RAM, one SCSI disk, kernel 2.4.9-7smp
Adaptec AIC-7899 Ultra 160 adapter w/one disk w/ext3
Attached scsi disk sda at scsi0, channel 0, id 0, lun 0
SCSI device sda: 72205440 512-byte hdwr sectors (36969 MB)
time: real    8m44.697s (Yes, almost *9* minutes)

--
"Premature optimization is the root of all evil." --Donald Knuth

Re: Poor performance on SCSI machines, good on IDE?

From
Helge Bahmann
Date:
On Fri, 16 Nov 2001, Eric Crampton wrote:

> I'm seeing some strange performance behavior with PostgreSQL. In the
> following four examples, I'm using the PGDG RPMs on Redhat Linux
> 7.2. On the machines with IDE disks, I'm seeing *MUCH* faster INSERTs
> than the machines with SCSI. I'm trying to figure out why. Disk
> benchmarking programs show the SCSI machines should have much faster
> seek and transfer times.

I guess your IDE disks are set to write caching enabled. You can turn
this off (in theory) with

    hdparm -W 0 /dev/hd?

(cf. man-page to hdparm)

Note that not all IDE drives support turning off write caching, most will
do write-caching irregardless of the setting of this flag (with obvious
consequences with respect to data integrity on the one hand and performance
on the other hand).

Regards
--
Helge Bahmann <bahmann@math.tu-freiberg.de>             /| \__
Network admin, systems programmer                      /_|____\
                                                     _/\ |   __)
$ ./configure                                        \\ \|__/__|
checking whether build environment is sane... yes     \\/___/ |
checking for AIX... no (we already did this)            |


Re: Poor performance on SCSI machines, good on IDE?

From
Tom Lane
Date:
Eric Crampton <eric@atdesk.com> writes:
> Incidently, if I turn fsync off on the SCSI machines, the performance
> absolutely soars. Of course, I don't really want to do this in
> production.

Have you tried experimenting with the WAL_SYNC_METHOD parameter?
On some kernels that can make a big difference.  Not sure why
the apparent correlation to disk drive type, though.

            regards, tom lane