Re: PostgreSQL reads each 8k block - no larger blocks are used - even on sequential scans

On Sun, 27 Sep 2009, Sam Mason wrote:

> On Sun, Sep 27, 2009 at 06:05:51PM +0200, Gerhard Wiesinger wrote:
>> A google research has shown that Gregory Stark already worked on that issue
>> (see references below) but as far as I saw only on bitmap heap scans.
>
> Greg Stark's patches are about giving the IO subsystem enough
> information about where the random accesses will be ending up next.
> This is important, but almost completely independent from the case
> where you know you're doing sequential IO, which is what you seem to be
> talking about.
>

I'm talking about 2 cases
1.) Sequential scans
2.) Bitmap index scans
which both hopefully end physically in blocks which are after each other
and were larger block sizes can benefit.

>> I think this is one of the most critical performance showstopper of
>> PostgreSQL on the I/O side.
>
> PG's been able to handle data as fast as it can come back from the disk
> in my tests. When you start doing calculations then it will obviously
> slow down, but what you were talking about wouldn't help here either.
>
> Then again, I don't have a particularly amazing IO subsystem. What
> sort of performance do your disks give you and at what rate is PG doing
> sequential scans for you?
>

Hello Sam,

Detailed benchmarks are below, the original one from PostgreSQL have
already been posted. So i would expect at least about 60-80MB in reading
for PostgreSQL (when larger block sizes are read)in practical issues on
sequence scans but they are at about 30MB/s. See also pgiosim below.

Setup is:
Disk Setup: SW RAID 5 with 3x1TB SATA 7200 RPM disks
Linux Kernel: 2.6.30.5-43.fc11.x86_64
CPU: Quad Core: AMD Phenom(tm) II X4 940 Processor, 3GHz
RAM: 8GB

Thnx.

Ciao,
Gerhard

--
http://www.wiesinger.com/

http://pgfoundry.org/projects/pgiosim/

#################################################
# Performance benchmarks:
#################################################
dd if=/dev/zero of=test.txt bs=8192 count=1310720 conv=fdatasync
1310720+0 records in
1310720+0 records out
10737418240 bytes (11 GB) copied, 169.482 s, 63.4 MB/s

dd if=test.txt of=/dev/null bs=8192
1310720+0 records in
1310720+0 records out
10737418240 bytes (11 GB) copied, 86.4457 s, 124 MB/s

#################################################
# RANDOM
#################################################

# Random 8k block reads
echo 3 > /proc/sys/vm/drop_caches;./pgiosim -b 10000 test.txt
Arg: 1
Added test.txt
Elapsed: 148.22
Read 10000 blocks Wrote 0 blocks
67.47 op/sec, 539.75kB/sec

# Random 8k block reads & writes
echo 3 > /proc/sys/vm/drop_caches;./pgiosim -b 10000 -w 100 test.txt
Write Mode: 100%
Arg: 1
Added test.txt
Elapsed: 201.44
Read 10000 blocks Wrote 10000 blocks
49.64 op/sec, 397.14kB/sec

# Random 8k block reads & writes, sync after each block
echo 3 > /proc/sys/vm/drop_caches;./pgiosim -b 10000 -w 100 -y test.txt
Write Mode: 100%
fsync after each write
Arg: 1
Added test.txt
Elapsed: 282.30
Read 10000 blocks Wrote 10000 blocks
35.42 op/sec, 283.39kB/sec

#################################################
# SEQUENTIAL
#################################################

# Sequential 8k block reads
echo 3 > /proc/sys/vm/drop_caches;./pgiosim -s -b 1000000 test.txt
Seq Scan
Arg: 1
Added test.txt
Elapsed: 71.88
Read 1000000 blocks Wrote 0 blocks
13911.40 op/sec, 111291.17kB/sec

# Sequential 8k block reads & writes
echo 3 > /proc/sys/vm/drop_caches;./pgiosim -s -b 1000000 -w 100 test.txt
Seq Scan
Write Mode: 100%
Arg: 1
Added test.txt
Elapsed: 261.24
Read 1000000 blocks Wrote 1000000 blocks
3827.90 op/sec, 30623.18kB/sec

# Sequential 8k block reads & writes, sync after each block
echo 3 > /proc/sys/vm/drop_caches;./pgiosim -s -b 10000 -w 100 -y test.txt
Seq Scan
Write Mode: 100%
fsync after each write
Arg: 1
Added test.txt
Elapsed: 27.03
Read 10000 blocks Wrote 10000 blocks
369.96 op/sec, 2959.68kB/sec

#################################################