This is a spinoff of the current work on compression...
I've discovered that linux doesn't apply readahead to sparse files.
So I added a little readahead in seq scans.
Then I realized this might also be beneficial for the standard Postgres.
On my RAID1 it shows some pretty drastic effects.
The PC :
- RAID1 of 2xSATA disks, reads at about 60 MB/s
- RAID5 of 3xSATA disks, reads at about 210 MB/s
Both RAIDs are Linux Software RAID.
Test data :
A 9.3GB table with not too small rows, so count(*) doesn't use lots of CPU.
The problem :
- On the RAID5 there is no problem, count(*) maxes out the disk.
- On the RAID1, count(*) also maxes out the disk, but there are 2 disks.
One works, one sits idle. It does nothing.
Linux Software RAID cannot use 2 disks on sequential reads, at least on my
kernel version. What do your boxes do in such a situation ?
For standard postgres, iostat says :
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 3,00 0,00 40,00 0 40
sdb 727,00 116600,00 40,00 116600 40
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 124,00 23408,00 0,00 23408 0
sdb 628,00 101640,00 0,00 101640 0
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 744,00 124536,00 0,00 124536 0
sdb 0,00 0,00 0,00 0 0
Basically it is reading the disks in turn, but not at the same time.
The solution :
Somehow coerce Linux Software RAID to stripe reads across the 2 mirrors to
get more throughput.
After a bit of fiddling, this seems to do it :
- for each page read in a seq scan
Strategy 0 : do nothing (this is the current strategy)
Strategy 1 : issue a Prefetch call 4096 pages ahead (32MB) of current
position
Strategy 2 : if (the current page & 4096) == 1, issue a Prefetch call 4096
pages ahead (32MB) of current position
Strategy 3 : issue a prefetch at 32MB * ((the current page & 4096) ? 1 :
2) ahead of current position
Results to seq scan 9.3GB of data on the RAID5 :
Strategy 0 :46.4 s
It maxes out the disk anyway, so I didn't try the others.
However RAID1 is better for not so read-only databases...
Results to seq scan 9.3GB of data on the RAID1 :
Strategy 0 :162.8 s
Strategy 1 :152.9 s
Strategy 2 :105.2 s
Strategy 3 :152.3 s
Strategy 2 cuts the seq scan duration by 35%, ie. disk bandwidth gets a
+54% boost.
For strategy 2, iostat says :
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 625,00 105288,00 0,00 105288 0
sdb 820,00 105968,00 0,00 105968 0
Both RAID1 volumes are exploited at the same time.
I guess it would need some experimenting with the values, and a
per-tablespace setting, but since lots of people use Linux Software RAID1
on servers, this might be interesting...
You guys want to try it ?
Patch attached.
