以下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)
...
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)
