Обсуждение: to enable O_DIRECT within postgresql
Dear all,<br /> I am trying to enable the direct IO for the disk-resident <br />hash partitions of hashjoin in postgresql.The basic postgres <br />environment settings are:<br /> centos 5.5<br /> kernel 2.6.18<br /> ext3 fs<br /> PostgreSQL 8.4.3 <br /><br /> Previously I added the O_DIRECT flag to the "fileFlags" <br />parameterof open() within BasicOpenFile() (line 505 in <br />src/backend/storage/file/fd.c), but strangely I cannot even<br /> start the server, with error:<br /><br />PANIC: could not read from control file: Invalid argument<br />Aborted<br/><br /> So far what I did is to add the O_DIRECT flag to the <br />"fileFlags" parameter of PathNameOpenFile()(line 992 & 1007 in <br /> src/backend/storage/file/fd.c), which calls the BasicOpenFIle()<br />andpasses the "fileFlags". This time, I can start the sever,<br />but when I submit a hashjoin query from the client, ithappens<br /><br /> ERROR: could not write to hash-join temporary file: Invalid argument<br /><br />Can anyone advisewhat's the reason and how to fix this?<br />Or what's the correct way to enable the direct disk IO within<br />postgres?I appreciate the suggestions and thanks very much!<br /><br />Regards<br />Daniel
Daniel Ng <danielng1985@gmail.com> writes:
> I am trying to enable the direct IO for the disk-resident
> hash partitions of hashjoin in postgresql.
Why would you think that's a good idea?
> Can anyone advise what's the reason and how to fix this?
Per the open(2) man page:
The O_DIRECT flag may impose alignment restrictions on the length and address of userspace buffers and
thefile offset of I/Os. In Linux alignment restrictions vary by file system and kernel version and might be
absententirely. However there is currently no file system-indepen- dent interface for an application to discover
theserestrictions for a given file or file system.
It's unlikely that the code you're hacking makes any attempt to align
the buffers it's using to read/write files.
regards, tom lane
Daniel Ng wrote: > I am trying to enable the direct IO for the disk-resident > hash partitions of hashjoin in postgresql. As Tom already mentioned this isn't working because of alignment issues. I'm not sure what you expect to achieve though. You should be warned that other than the WAL, every experiment I've ever seen that tries to add more direct I/O to the database has failed to improve anything; the result is neither barely noticeable, or a major performance drop. This is particularly futile if you're doing your research on Linux/ext3, where even if your code works delivers a speed up no one will trust it enough to ever merge and deploy it, due to the generally poor quality of that area of the kernel so far. This particular area is magnetic for drawing developer attention as it seems like there's a big win just under the surface if things were improved a bit. There isn't. On operating systems like Solaris where it's possible to prototype here by use mounting options to silently covert parts of the database to direct I/O, experiments in that area have all been disappointing. One of the presentations from Jignesh Shah at Sun covered his experiments in this area, can't seem to find it at the moment but I remember the results were not positive in any way. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
Greg: Thank you very much for your insightful comments on the performance of <br />direct io applied to postgres! That inspiredme a lot.<br /><br />Tom: thank you for the reference to man page!<br /><br /><div class="gmail_quote">On Fri, Jun18, 2010 at 2:02 AM, Greg Smith <span dir="ltr"><<a href="mailto:greg@2ndquadrant.com">greg@2ndquadrant.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div class="im">Daniel Ngwrote:<br /><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204);padding-left: 1ex;"> I am trying to enable the direct IO for the disk-resident<br /> hash partitions of hashjoin inpostgresql.<br /></blockquote><br /></div> As Tom already mentioned this isn't working because of alignment issues. I'mnot sure what you expect to achieve though. You should be warned that other than the WAL, every experiment I've everseen that tries to add more direct I/O to the database has failed to improve anything; the result is neither barely noticeable,or a major performance drop. This is particularly futile if you're doing your research on Linux/ext3, where evenif your code works delivers a speed up no one will trust it enough to ever merge and deploy it, due to the generallypoor quality of that area of the kernel so far.<br /><br /> This particular area is magnetic for drawing developerattention as it seems like there's a big win just under the surface if things were improved a bit. There isn't. On operating systems like Solaris where it's possible to prototype here by use mounting options to silently covertparts of the database to direct I/O, experiments in that area have all been disappointing. One of the presentationsfrom Jignesh Shah at Sun covered his experiments in this area, can't seem to find it at the moment but I rememberthe results were not positive in any way.<br /><font color="#888888"><br /> -- <br /> Greg Smith 2ndQuadrant US Baltimore, MD<br /> PostgreSQL Training, Services and Support<br /> greg@2ndQuadrant.com <a href="http://www.2ndQuadrant.us"target="_blank">www.2ndQuadrant.us</a><br /><br /></font></blockquote></div><br />