blockdev --setra read ahead performance

以下CentOS 6.4 x64上的测试结果,

硬盘OCZ RevoDrive3 X2 240G

读取94393左右个数据块, 

在read ahead=256(默认)情况下的时间为2376毫秒左右. (当然还有除掉cpu tuples operator的时间, 这里不额外叙述, 只是表面read ahead的影响.)

在read ahead=0时, 时间为18773毫秒左右.

在read ahead=512时, 时间为2115毫秒左右.

在read ahead=1024时, 时间为2082毫秒左右.

一般情况下, 默认的设置256就差不多了, 无需多调.

每次查询前清理OS CACHE.
[root@db-172-16-3-150 ~]# sync
[root@db-172-16-3-150 ~]#  echo 3 > /proc/sys/vm/drop_caches

设置块设备read ahead
[root@db-172-16-3-150 ~]# blockdev --setra 512 /dev/sdb
[root@db-172-16-3-150 ~]# blockdev --setra 1024 /dev/sdb
...

digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;
                                                               QUERY PLAN                                                           

------------------------------------------------------------------------------------------------------------------------------------
----
 Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=2.742..2115.588 rows=10100000 loops
=1)
   Output: id, info, crt_time
   Buffers: shared hit=256 read=94137
 Total runtime: 3148.863 ms
(4 rows)

-- 清理OS CACHE
digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;
                                                               QUERY PLAN                                                           

------------------------------------------------------------------------------------------------------------------------------------
----
 Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=2.502..2082.989 rows=10100000 loops
=1)
   Output: id, info, crt_time
   Buffers: shared hit=288 read=94105
 Total runtime: 3103.415 ms
(4 rows)

-- 清理OS CACHE
digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;
                                                               QUERY PLAN                                                           

------------------------------------------------------------------------------------------------------------------------------------
-----
 Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=0.652..18773.211 rows=10100000 loop
s=1)
   Output: id, info, crt_time
   Buffers: shared read=94393
 Total runtime: 19824.287 ms
(4 rows)
...

[参考]
1. http://unix.stackexchange.com/questions/71364/persistent-blockdev-setra-read-ahead-setting

2. man blockdev

BLOCKDEV(8)                                                        BLOCKDEV(8)

NAME
       blockdev - call block device ioctls from the command line

SYNOPSIS
       blockdev [options] commands devices
       blockdev --report [devices]

DESCRIPTION
       The utility blockdev allows one to call block device ioctls from the command line.

OPTIONS
       -V     Print version and exit.

       -q     Be quiet.

       -v     Be verbose.

       --report
              Print a report for devices.  Note that the partition StartSec is in 512-byte sectors.

COMMANDS
       --setro
              Set read-only.

       --setrw
              Set read-write.

       --getro
              Get read-only. Print 1 if the device is read-only, 0 otherwise.

       --getss
              Print sectorsize in bytes - usually 512.

       --getbsz
              Print blocksize in bytes.
       --setbsz N
              Set blocksize to N bytes.

       --getsize
              Print device size in sectors (BLKGETSIZE). Deprecated in favor of the --getsz option.

       --getsize64
              Print device size in bytes (BLKGETSIZE64)

       --getsz
              Get size in 512-byte sectors (BLKGETSIZE64 / 512).

       --setra N
              Set readahead to N 512-byte sectors.

       --getra
              Print readahead (in 512-byte sectors).

       --setfra N
              Set filesystem readahead (same like --setra on 2.6 kernels).

       --getfra
              Get filesystem readahead.

       --flushbufs
              Flush buffers.

       --rereadpt
              Reread partition table.

AUTHOR
       blockdev was written by Andries E. Brouwer.

AVAILABILITY
       The   blockdev   command   is   part  of  the  util-linux-ng  package  and  is  available  from  ftp://ftp.ker-
       nel.org/pub/linux/utils/util-linux-ng/.

                                   Jun 2007                        BLOCKDEV(8)