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