(including bizgres-general) Has anyone done any testing on bizgres? It's got some patches that eliminate a lot of IO bottlenecks, so it might present even larger gains. On Wed, Apr 26, 2006 at 03:08:59PM -0500, Steve Poe wrote: > I found an average 14% improvement Using Pg 7.4.11 with odbc-bench as my > test bed with Wu's kernel patch. I have not tried version 8.x yet. > > Thanks Wu. > > Steve Poe > > Using Postgresql 7.4.11, on an dual Opteron with 4GB > > On Fri, 2006-04-21 at 09:38 +0800, Wu Fengguang wrote: > > Greetings, > > > > I'd like to introduce a new readahead framework for the linux kernel: > > http://www.ussg.iu.edu/hypermail/linux/kernel/0603.2/1021.html > > > > HOW IT WORKS > > > > In adaptive readahead, the context based method may be of particular > > interest to postgresql users. It works by peeking into the file cache > > and check if there are any history pages present or accessed. In this > > way it can detect almost all forms of sequential / semi-sequential read > > patterns, e.g. > > - parallel / interleaved sequential scans on one file > > - sequential reads across file open/close > > - mixed sequential / random accesses > > - sparse / skimming sequential read > > > > It also have methods to detect some less common cases: > > - reading backward > > - seeking all over reading N pages > > > > WAYS TO BENEFIT FROM IT > > > > As we know, postgresql relies on the kernel to do proper readahead. > > The adaptive readahead might help performance in the following cases: > > - concurrent sequential scans > > - sequential scan on a fragmented table > > (some DBs suffer from this problem, not sure for pgsql) > > - index scan with clustered matches > > - index scan on majority rows (in case the planner goes wrong) > > > > TUNABLE PARAMETERS > > > > There are two parameters which are described in this email: > > http://www.ussg.iu.edu/hypermail/linux/kernel/0603.2/1024.html > > > > Here are the more oriented guidelines for postgresql users: > > > > - /proc/sys/vm/readahead_ratio > > Since most DB servers are bounty of memory, the danger of readahead > > thrashing is near to zero. In this case, you can set readahead_ratio to > > 100(or even 200:), which helps the readahead window to scale up rapidly. > > > > - /proc/sys/vm/readahead_hit_rate > > Sparse sequential reads are read patterns like {0, 2, 4, 5, 8, 11, ...}. > > In this case we might prefer to do readahead to get good I/O performance > > with the overhead of some useless pages. But if you prefer not to do so, > > set readahead_hit_rate to 1 will disable this feature. > > > > - /sys/block/sd<X>/queue/read_ahead_kb > > Set it to a large value(e.g. 4096) as you used to do. > > RAID users might want to use a bigger number. > > > > TRYING IT OUT > > > > The latest patch for stable kernels can be downloaded here: > > http://www.vanheusden.com/ara/ > > > > Before compiling, make sure that the following options are enabled: > > Processor type and features -> Adaptive file readahead > > Processor type and features -> Readahead debug and accounting > > > > HELPING AND CONTRIBUTING > > > > The patch is open to fine-tuning advices :) > > Comments and benchmarking results are highly appreciated. > > > > Thanks, > > Wu > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
