pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [Bizgres-general] Introducing a new linux
2+ messages / 2 participants
[nested] [flat]
* Re: [Bizgres-general] Introducing a new linux
@ 2006-04-26 23:33 Luke Lonergan <llonergan@greenplum.com>
0 siblings, 1 reply; 2+ messages in thread
From: Luke Lonergan @ 2006-04-26 23:33 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; Steve Poe <steve.poe@gmail.com>; bizgres-general <bizgres-general@pgfoundry.org>; +Cc: pgsql-performance@postgresql.org, "Wu Fengguang" <wfg@mail.ustc.edu.cn>
Jim,
I¹m thinking about it, we¹re already using a fixed read-ahead of 16MB using
blockdev on the stock Redhat 2.6.9 kernel, it would be nice to not have to
set this so we may try it.
- Luke
On 4/26/06 3:28 PM, "Jim C. Nasby" <jnasby@pervasive.com> wrote:
> (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@pervasive.com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> _______________________________________________
> Bizgres-general mailing list
> Bizgres-general@pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/bizgres-general
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [Bizgres-general] Introducing a new linux
@ 2006-04-26 23:53 Michael Stone <mstone+postgres@mathom.us>
parent: Luke Lonergan <llonergan@greenplum.com>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Stone @ 2006-04-26 23:53 UTC (permalink / raw)
To: pgsql-performance
On Wed, Apr 26, 2006 at 04:33:40PM -0700, Luke Lonergan wrote:
>I¹m thinking about it, we¹re already using a fixed read-ahead of 16MB using
>blockdev on the stock Redhat 2.6.9 kernel, it would be nice to not have to
>set this so we may try it.
FWIW, I never saw much performance difference from doing that. Wu's
patch, OTOH, gave a big boost.
Mike Stone
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-04-26 23:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-26 23:33 Re: [Bizgres-general] Introducing a new linux Luke Lonergan <llonergan@greenplum.com>
2006-04-26 23:53 ` Michael Stone <mstone+postgres@mathom.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox