X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id B03E711F6832 for ; Wed, 26 Apr 2006 20:34:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 04167-02 for ; Wed, 26 Apr 2006 20:33:57 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.mi8.com (d01gw02.mi8.com [63.240.6.46]) by postgresql.org (Postfix) with ESMTP id BEB6F11F685A for ; Wed, 26 Apr 2006 20:33:54 -0300 (ADT) Received: from 172.16.1.112 by mail.mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D2)); Wed, 26 Apr 2006 19:33:44 -0400 X-Server-Uuid: 7829E76E-BB9E-4995-8473-3C0929DF7DD1 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.160]) by D01SMTP02.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 26 Apr 2006 19:33:44 -0400 Received: from 67.103.45.218 ([67.103.45.218]) by MI8NYCMAIL06.Mi8.com ( [172.16.1.219]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.118]) with Microsoft Exchange Server HTTP-DAV ; Wed, 26 Apr 2006 23:33:44 +0000 User-Agent: Microsoft-Entourage/11.2.3.060209 Date: Wed, 26 Apr 2006 16:33:40 -0700 Subject: Re: [Bizgres-general] Introducing a new linux From: "Luke Lonergan" To: "Jim C. Nasby" , "Steve Poe" , "bizgres-general" cc: pgsql-performance@postgresql.org, "Wu Fengguang" Message-ID: Thread-Topic: [Bizgres-general] [PERFORM] Introducing a new linux readahead framework Thread-Index: AcZpgOwmZtCPuHK5QveaI8Ih0syZkAACOztn In-Reply-To: <20060426222855.GC97354@pervasive.com> MIME-Version: 1.0 X-OriginalArrivalTime: 26 Apr 2006 23:33:44.0668 (UTC) FILETIME=[DBDBA9C0:01C66989] X-WSS-ID: 684EDCD22XS52494611-01-01 Content-Type: multipart/alternative; boundary=B_3228914020_4686675 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/618 X-Sequence-Number: 18702 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3228914020_4686675 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Jim, I=B9m thinking about it, we=B9re 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=20 On 4/26/06 3:28 PM, "Jim C. Nasby" wrote: > (including bizgres-general) >=20 > 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. >=20 > 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.=20 >> > >> > 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 kerne= l: >>> > > 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 particula= r >>> > > interest to postgresql users. It works by peeking into the file cac= he >>> > > and check if there are any history pages present or accessed. In th= is >>> > > 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 case= s: >>> > > - 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_rati= o to >>> > > 100(or even 200:), which helps the readahead window to scale up rap= idly. >>> > > >>> > > - /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 perfor= mance >>> > > with the overhead of some useless pages. But if you prefer not to d= o so, >>> > > set readahead_hit_rate to 1 will disable this feature. >>> > > >>> > > - /sys/block/sd/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 >> > >=20 > -- > 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 >=20 >=20 --B_3228914020_4686675 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Re: [Bizgres-general] [PERFORM] Introducing a new linux readahead fr= amework Jim,<= BR>
I’m thinking about it, we’re already using a fixed read-ahead o= f 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> w= rote:

(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 ker= nel:
> > 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 particu= lar
> > interest to postgresql users. It works by peeking into the file c= ache
> > and check if there are any history pages present or accessed. In = this
> > way it can detect almost all forms of sequential / semi-sequentia= l 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 readahea= d.
> > The adaptive readahead might help performance in the following ca= ses:
> >     - concurrent sequential scans
> >     - sequential scan on a fragmented table > >       (some DBs suffer from this pr= oblem, not sure for pgsql)
> >     - index scan with clustered matches
> >     - index scan on majority rows (in case th= e 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 readahe= ad
> > thrashing is near to zero. In this case, you can set readahead_ra= tio to
> > 100(or even 200:), which helps the readahead window to scale up r= apidly.
> >
> > - /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 perf= ormance
> > 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.co= m/ara/
> >
> > Before compiling, make sure that the following options are enable= d:
> > 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://arc= hives.postgresql.org
>
>
> ---------------------------(end of broadcast)-------------------------= --
> TIP 4: Have you searched our list archives?
>
>            &nbs= p;   http://archives= .postgresql.org
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnas= by@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pe= rvasive.vcf       cell: 512-569-9461
_______________________________________________
Bizgres-general mailing list
Bizgres-general@pgfoundry.org
http://pgfo= undry.org/mailman/listinfo/bizgres-general



 --B_3228914020_4686675--