extremely slow disk access (using SCSI, RAID) - Mailing list pgsql-general

Hello,


we are running 7.1.3 on a linux machine. By running simple 'group by'
queries on large tables we are we are experiencing __extremely slow__
disk read rates.
The size of the database is cca. 40GB+. Size of a table cca. 1GB

CONFIGURATION

CPU - Dual Intel PIII 1,133
Mem - 2GB Kingston ECC
Storage - RAID 5, 3 x IBM SCSI 36GB (10,000rpm)
Raid controller - Adaptec 3210

TEST QUERY

select field, count(*) from table where date between '2001-1-1' and
'2002-1-1' group by field order by count;

DISK I/O

argosz@it:~$ vmstat -n 1
   procs                      memory    swap          io     system
     cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs
us  sy  id
 0  0  0  24744 1050124  47660 835532   0   0     0     0  160   113
1   3  96
 0  0  0  24744 1050128  47660 835532   0   0     0     0  227   201
1   3  96
 1  0  0  24744 1050032  47660 835632   0   0    64   128  203   169
5   2  93
 BEGINNING OF QUERY
 0  0  0  24744 1049272  47664 836368   0   0   500     0  210   234
4   4  92
 0  0  0  24744 1048768  47672 836800   0   0   296     0  249   277
1   3  96
 3  0  0  24744 1048296  47676 837188   0   0   220     0  171   127
1   3  96
 2  0  0  24744 1043928  47680 840760   0   0  1744     0  322   425
23   1  76
 1  0  0  24744 1040568  47680 844092   0   0  1416   128  333   423
24   9  67
 1  0  0  24744 1036880  47680 847776   0   0  1536     0  284   386
36   3  61
 1  0  0  24744 1032924  47680 851724   0   0  1640     0  301   417
32   5  63
 1  0  0  24744 1029420  47684 854996   0   0  1328    52  334   480
33   6  61
 1  0  0  24744 1026172  47684 858344   0   0  1424     0  286   366
28   5  67
 1  0  0  24744 1022572  47684 861932   0   0  1424     0  284   403
34   2  64
 1  0  0  24744 1019244  47688 865592   0   0  1548     0  308   398
29   3  68
 1  0  0  24744 1015216  47688 869608   0   0  1664     0  298   406
27   7  66
 2  0  0  24744 1011464  47688 873468   0   0  1536     0  361   450
32   5  63
 1  0  0  24744 1007740  47692 877180   0   0  1620   256  309   441
20   5  75
 1  0  0  24744 1004948  47692 879968   0   0  1144     0  262   340
21   4  75
 1  0  0  24744 1001040  47696 883864   0   0  1628     0  311   446
27   5  68
 1  0  0  24744 997192  47700 887700   0   0  1540     0  285   373
30   2  68
 1  0  0  24744 992972  47700 891896   0   0  1776     0  319   443
30   5  65
 1  0  0  24744 989212  47700 895652   0   0  1584   256  310   409
31   2  67
 1  0  0  24744 985440  47704 899408   0   0  1500     0  320   436
38   4  58
 1  0  0  24744 982676  47704 902176   0   0  1120     0  248   321
24   4  72
 1  0  0  24744 978656  47704 906188   0   0  1664     0  324   439
37   2  61
 1  0  0  24744 975008  47708 909828   0   0  1540     0  273   386
35   2  63
 1  0  0  24744 971300  47712 913532   0   0  1492   256  330   435
30   5  65
 1  0  0  24744 968260  47712 916568   0   0  1304     0  262   334
21   5  74
 1  0  1  24744 967072  47712 917612   0   0   256     0  146   117
86   6   8
 1  0  0  24744 967016  47712 917612   0   0     0     0  126    62
93   7   0
 1  0  0  24744 966908  47712 917760   0   0    96     0  139   101
95   5   0
 END OF QUERY
 0  0  0  24744 981816  47712 903864   0   0     0   256  161   142
41   5  54
 0  0  0  24744 981392  47712 904272   0   0   256     0  161   129
3   1  96


Sometimes the io-bi is cca. 3-5000/sec, but it's still way too low in
my mind.
What is the best rate we can achieve with this RAID 5 configuration
and how?

Also, can you give me a idea what rate we should expect using RAID
0+1?

As a comparison I quoted the output of vmstat when we read a simple
file from the disk. (Still not very fast, but faster)

argosz@it:~$ cat /var/log/apache/default-access-log.log > /dev/null

   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  24636 238876  49268 1595264   0   0  3656     0  208   399
3   6  91
 2  0  0  24636 211156  49296 1622272   0   0 27036     0  587   957
3  12  85
 1  0  0  24636 181732  49324 1650952   0   0 28700     0  648  1077
6  11  83
 2  0  0  24636 157812  49332 1674268   0   0 23316     0  494   863
37  14  49
 2  0  0  24636 140100  49332 1691548   0   0 17280   128  647   834
5   8  87
 2  0  0  24636 121072  49332 1710112   0   0 18560     0  705   943
1  10  89
 0  0  0  24636 109020  49332 1721948   0   0 11796     0  505   726
3  11  86




Thanks for your help

Peter