From: | Gerhard Wiesinger <lists(at)wiesinger(dot)com> |
---|---|
To: | Sam Mason <sam(at)samason(dot)me(dot)uk> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PostgreSQL reads each 8k block - no larger blocks are used - even on sequential scans |
Date: | 2009-09-27 19:04:31 |
Message-ID: | alpine.LFD.2.00.0909272055550.23512@bbs.intern |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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://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
#################################################
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-09-27 19:08:48 | Re: CREATE LANGUAGE workaround |
Previous Message | InterRob | 2009-09-27 18:45:19 | Re: generic modelling of data models; enforcing constraints dynamically... |