Thread: expanding our usage of POSIX_FADVISE
Hello,<br /><p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><br />I wonder if POSIX_FADV_RANDOM and POSIX_FADV_SEQUENTIAL arestill innacurate for postgreSQL ?<br /><p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><br />I find <br /> «A relatedproblem is that the smgr uses the same FD to access the same relation no matter how many scans are in progress. Thinkabout a complex query that is doing both a seqscan and an indexscan on the same relation (a self-join could easily dothis). You'd really need to change this if you want POSIX_FADV_SEQUENTIAL and <span style=" font-weight:600;">POSIX_FADV_RANDOM</span>to get set usefully. <br /> » (tom lane, 2003)<br /><p style="-qt-paragraph-type:empty;margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><br />And also :<br /> «<br /> Surely POSIX_FADV_SEQUENTIAL is theone intended to hint seq scans, and <span style=" font-weight:600;">POSIX_FADV_RANDOM</span> to hint random access. No?<br /><p style=" margin-top:12px; margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;-qt-user-state:0;">ISTM, _WILLNEED seems just right for small random-access blocks. <p style=" margin-top:12px;margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Anyway,for those who want to see what they do in Linux, <a href="http://www.gelato.unsw.edu.au/lxr/source/mm/fadvise.c"><spanstyle=" text-decoration: underline; color:#0057ae;">http://www.gelato.unsw.edu.au/lxr/source/mm/fadvise.c</span></a>Pretty scary that Bruce said it could makeolder linuxes dump core - there isn't a lot of code there. <p style=" margin-top:12px; margin-bottom:12px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">» (ron mayer, 2006)<p style="-qt-paragraph-type:empty;margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><br />But that seems a bit old. <br /> ----<br /> Cédric Villemain<br/> Administrateur de Base de Données<br /> Cel: +33 (0)6 74 15 56 53<br /> http://dalibo.com - http://dalibo.org
On Wed, Aug 12, 2009 at 3:07 PM, Cédric Villemain<cedric.villemain@dalibo.com> wrote: > > I wonder if POSIX_FADV_RANDOM and POSIX_FADV_SEQUENTIAL are still innacurate > for postgreSQL ? > > I find > «A related problem is that the smgr uses the same FD to access the same > relation no matter how many scans are in progress. Think about a complex > query that is doing both a seqscan and an indexscan on the same relation (a > self-join could easily do this). You'd really need to change this if you > want POSIX_FADV_SEQUENTIAL and POSIX_FADV_RANDOM to get set usefully. > » (tom lane, 2003) I had a version of the POSIX_FADV_SEQUENTIAL patch going which set the appropriate mode before every block read (skipping it if it was the same mode as last set -- just like we handle lseek). I couldn't measure any consistent improvement on sequential scans though which, at least on Linux, already saturdate any i/o system I tested. Mileage on other operating systems or i/o systems may vary of course. I think the real benefit of this would be avoiding polluting the filesystem cache with blocks which we have no intention of reading. That will be a hard benefit to measure though. Especially since just because we're doing a random i/o doesn't actually mean we won't read nearby blocks eventually. If we're scanning an index range and the table is actually mostly clustered then our random i/o won't be so random after all... -- greg http://mit.edu/~gsstark/resume.pdf
Le mercredi 12 août 2009, Greg Stark a écrit : > On Wed, Aug 12, 2009 at 3:07 PM, Cédric > > Villemain<cedric.villemain@dalibo.com> wrote: > > I wonder if POSIX_FADV_RANDOM and POSIX_FADV_SEQUENTIAL are still > > innacurate for postgreSQL ? > > > > I find > > «A related problem is that the smgr uses the same FD to access the same > > relation no matter how many scans are in progress. Think about a complex > > query that is doing both a seqscan and an indexscan on the same relation > > (a self-join could easily do this). You'd really need to change this if > > you want POSIX_FADV_SEQUENTIAL and POSIX_FADV_RANDOM to get set usefully. > > » (tom lane, 2003) > > I had a version of the POSIX_FADV_SEQUENTIAL patch going which set the > appropriate mode before every block read (skipping it if it was the > same mode as last set -- just like we handle lseek). I couldn't > measure any consistent improvement on sequential scans though which, > at least on Linux, already saturdate any i/o system I tested. Mileage > on other operating systems or i/o systems may vary of course. yes as stated before by Greg Smith, some OS use more or less the POSIX_FADV_* depending on their default. Linux is agresive and the POSIX_FADV_SEQUENTIAL have probably only poor benefit on it. I wonder what happen with the POSIX_FADV_RANDOM one. > > I think the real benefit of this would be avoiding polluting the > filesystem cache with blocks which we have no intention of reading. and be sure we readhead when needed, bypassing system default. > That will be a hard benefit to measure though. Especially since just > because we're doing a random i/o doesn't actually mean we won't read > nearby blocks eventually. If we're scanning an index range and the > table is actually mostly clustered then our random i/o won't be so > random after all... Probably, yes... :/ ---- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org