inferior SCSI performance - Mailing list pgsql-performance

I have been experimenting with a new Seagate Cheetah 10k-RPM SCSI to
compare with a cheaper Seagate Barracuda 7200-RPM IDE (each in a
single-drive configuration). The Cheetah definately dominates the generic
IO tests such as bonnie++, but fares poorly with pgbench (and other
postgresql operations).

I don't understand why switching to a SCSI drive in an otherwise identical
setup would so seriously degrade performance. I would have expected the
opposite. vmstat does not reveal (to me) any bottlenecks in the SCSI
configuration.

The only difference between the two test scenarios is that I stopped the
postmaster, copied the data dir to the other drive and put a symlink to
point to the new path. I ran the tests several times, so these are not
flukes.

Can anyone explain why this might be happening and how to better leverage
this 10k drive?

thanks,
Mike Adler


System info:

Box is a Dell 600SC with Adaptec 39160 SCSI controller.
Linux 2.4.18-bf2.4
CPU: Intel(R) Celeron(R) CPU 2.00GHz stepping 09
Memory: 512684k/524224k available (1783k kernel code, 11156k reserved,
549k data, 280k init, 0k highmem)

postgresql.conf settings:
shared_buffers = 10000
random_page_cost = 0.3
sort_mem = 4096

##################################################
TEST 1:

IDE  Seagate Baracuda
hde: ST340014A, ATA DISK drive
hde: 78165360 sectors (40021 MB) w/2048KiB Cache, CHS=77545/16/63

bonnie++ -f:
Version 1.02b       ------Sequential Output------ --Sequential Input- --Random-
                    -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
dellmar          1G           27001  10 11863   4           20867   3 161.7   0

sample vmstat 1 output during bonnie++:
   procs                      memory    swap          io     system         cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
 1  0  0   9332   4456   5056 467728   0   0 20864     0  429   698   0   5  95
 0  1  1   9332   4380   5056 467728   0   0  5248 27056  361   207   1   4  95
 0  1  1   9332   4376   5056 467728   0   0   384 26936  338    55   0   0 100
 0  1  0   9332   4416   5064 468368   0   0 10112  9764  385   350   0   4  96
 1  0  0   9332   4408   5056 468120   0   0 20608     0  427   684   1   7  92
 1  0  0   9332   4392   5056 467864   0   0 20992     0  431   692   0   5  95


pgbench:
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 2
number of transactions per client: 400
number of transactions actually processed: 800/800
tps = 110.213013(including connections establishing)
tps = 110.563927(excluding connections establishing)

sample "vmstat 1" output during pgbench:
   procs                      memory    swap          io     system         cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
 2  0  0    160   4348  50032 419320   0   0   240  3432  514  3849  34   7  59
 0  2  0    160   4392  50764 418544   0   0   224  3348  500  3701  33   6  61
 2  0  0    160   4364  51652 417688   0   0   240  3908  573  4411  43   8  50
 2  0  0    160   4364  52508 416832   0   0   160  3708  548  4273  44   8  49
 1  1  1    160   4420  53332 415944   0   0   160  3604  541  4174  40  13  48
 0  1  1    160   4420  54160 415120   0   0   104  3552  526  4048  42  14  45
 1  0  0    160   4964  54720 414576   0   0   128  4328  645  5819  69   7  24



########################################################
TEST 2:

SCSI Drive Seagate Cheetah 10k.6
  Vendor: SEAGATE   Model: ST336607LW        Rev: DS08

bonnie++ -f:
Version 1.02b       ------Sequential Output------ --Sequential Input- --Random-
                    -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
dellmar          1G           40249  14 21177   7           46620   7 365.8   0

sample vmstat 1 output during bonnie++:
 0  1  1   8916   4400   1844 467216   0   0   384 42348  475    80   0   0 100
 0  1  1   8916   4392   1844 467216   0   0   512 46420  472   103   0   2  98
 1  0  0   8916   4364   1852 469392   0   0  7168 26552  507   268   0   3  97
 1  0  0   8916   4452   1868 469392   0   0 28544 12312  658   947   1  15  84
 1  0  0   8916   4416   1860 468888   0   0 47744     4  850  1534   0  18  82
 1  0  0   8916   4436   1796 468312   0   0 48384     0  859  1555   0  19  81
 1  0  0   8916   4452   1744 467724   0   0 48640     0  863  1569   2  20  78


pgbench (sounds thrashy):
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 2
number of transactions per client: 400
number of transactions actually processed: 800/800
tps = 33.274922(including connections establishing)
tps = 33.307125(excluding connections establishing)

sample "vmstat 1" output during pgbench:
   procs                      memory    swap          io     system         cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
 0  1  1    160   4356  36568 432772   0   0     0  1120  232  1325  12   2  86
 0  1  1    160   4452  36592 432652   0   0     0  1108  229  1295  14   2  84
 0  1  1    160   4428  36616 432652   0   0     0  1168  234  1370   9   4  87
 0  1  1    160   4392  36636 432668   0   0     0  1120  231  1303  12   3  85
 0  1  0    160   4364  36664 432668   0   0     0  1084  230  1361  16   5  79
 0  1  0    160   4456  36696 432548   0   0     0  1196  234  1300  13   2  85


Mike Adler