Thread: Multi CPU Queries - Feedback and/or suggestions wanted!
Hi All, we would like to start some work on improving the performance of PostgreSQL in a multi-CPU environment. Dano Vojtek is student at the Faculty of Mathematics and Physics of Charles university in Prague (http://www.mff.cuni.cz) and he is going to cover this topic in his master thesis. He is going to do some investigation in the methods and write down the possibilities and then he is going to implement something from that for PostgreSQL. We want to come out with a serious proposal for this work after collecting the feedback/opinions and doing the more serious investigation. Topics that seem to be of interest and most of them were already discussed at developers meeting in Ottawa are 1.) parallel sorts 2.) parallel query execution 3.) asynchronous I/O 4.) parallel COPY 5.) parallel pg_dump 6.) using threads for parallel processing A scaling with increasing number of CPUs in 1.) and 2.) will face with the I/O bottleneck at some point and the benefit gained here should be nearly the same as for 3.) - the OS or disk could do a better job while scheduling multiple reads from the disk for the same query at the same time. 1.) More merges could be executed on different CPUs. However, one N-way merge on one CPU is probably better than two N/2-way merges on 2 CPUs while sharing the limit of work_mem together for these. This is specific and separate from 2.) or 3.) and if something implemented here it could probably share just the parallel infrastructure code. ======== 2.) Different subtrees (or nodes) of the plan could be executed in parallel on different CPUs and the results of this subtrees could be requested either synchronously or asynchronously. ======== 3.) The simplest possible way is to change the scan nodes that they will send out the asynchronous I/O requests for the next blocks before they manage to run out of tuples in the block they are going through. The more advanced way would arise just by implementing 2.) which will then lead to different scan nodes to be executed on different CPUs at the same time. ======== 4.) and 5.) We do not want to focus here, since there are on-going projects already. ======== 6.) Currently, threads are not used in PostgreSQL (except some cases for Windows OS). Generally using them would bring some problems a) different thread implementations on different OSes b) crash of the whole process if the problem happens in one thread. Backends are isolated and the problem in one backend leads to the graceful shut down of other backends. c) synchronization problems * a) seem just to be more for implementation. Is there any problem with execution of more threads on any supported OS? Like some planning issue that all the threads for the same process end up planned on the same CPU? Or something similar? * b) is fine with using more threads for processing the same query in the same backend - if one crashes others could do the graceful shutdown. * c) does not have to be solved in general because the work of all the threads will be synchronized and we could expect pretty well which data are being accessed by which thread. The memory allocation have to be adjusted to be thread safe and should not affect the performance (Is different memory context for different threads sufficient?). Other common code might need some changes as well. Possibly, the synchronization/critical section exclusion could be done in executor and only if needed. * Using processes instead of threads makes other things more complex - sharing objects between processes might need muchmore coding - more overhead during execution and synchronization ======== It seems to that it makes sense to start working on 2) and 3) and we would like to think of using more threads for processing the same query within one backend. We appreciate feedback, comments and/or suggestions. Cheers Julo
On Mon, Oct 20, 2008 at 12:05 PM, Julius Stroffek <Julius.Stroffek@sun.com> wrote:
I don't see why multiple CPUs can't work on the same node of a plan. For instance, consider a node involving a scan with an expensive condition, like UTF-8 string length. If you have four CPUs you can bring to bear, each CPU could take every fourth page, computing the expensive condition for each tuple in that page. The results of the scan can be retired asynchronously to the next node above.
-jwb
Topics that seem to be of interest and most of them were already
discussed at developers meeting in Ottawa are
1.) parallel sorts
2.) parallel query execution
3.) asynchronous I/O
4.) parallel COPY
5.) parallel pg_dump
6.) using threads for parallel processing
[...]
2.)
Different subtrees (or nodes) of the plan could be executed in parallel
on different CPUs and the results of this subtrees could be requested
either synchronously or asynchronously.
I don't see why multiple CPUs can't work on the same node of a plan. For instance, consider a node involving a scan with an expensive condition, like UTF-8 string length. If you have four CPUs you can bring to bear, each CPU could take every fourth page, computing the expensive condition for each tuple in that page. The results of the scan can be retired asynchronously to the next node above.
-jwb
There is a problem trying to make Postgres do these things in Parallel.
The backend code isn’t thread-safe, so doing a multi-thread implementation requires quite a bit of work.
Using multiple processes has its own problems: The whole way locking works equates one process with one transaction (The proc table is one entry per process). Processes would conflict on locks, deadlocking themselves, as well as many other problems.
It’s all a good idea, but the work is probably far more than you expect.
Async I/O might be easier, if you used pThreads, which is mostly portable, but not to all platforms. (Yes, they do work on Windows)
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Jeffrey Baker
Sent: 2008-10-20 22:25
To: Julius Stroffek
Cc: pgsql-hackers@postgresql.org; Dano Vojtek
Subject: Re: [HACKERS] Multi CPU Queries - Feedback and/or suggestions wanted!
On Mon, Oct 20, 2008 at 12:05 PM, Julius Stroffek <Julius.Stroffek@sun.com> wrote:
Topics that seem to be of interest and most of them were already
discussed at developers meeting in Ottawa are
1.) parallel sorts
2.) parallel query execution
3.) asynchronous I/O
4.) parallel COPY
5.) parallel pg_dump
6.) using threads for parallel processing
[...]
2.)
Different subtrees (or nodes) of the plan could be executed in parallel
on different CPUs and the results of this subtrees could be requested
either synchronously or asynchronously.
I don't see why multiple CPUs can't work on the same node of a plan. For instance, consider a node involving a scan with an expensive condition, like UTF-8 string length. If you have four CPUs you can bring to bear, each CPU could take every fourth page, computing the expensive condition for each tuple in that page. The results of the scan can be retired asynchronously to the next node above.
-jwb
Hi Jeffrey, thank you for the suggestion. Yes, they potentially can, we'll consider this. Julo Jeffrey Baker wrote: > I don't see why multiple CPUs can't work on the same node of a plan. > For instance, consider a node involving a scan with an expensive > condition, like UTF-8 string length. If you have four CPUs you can > bring to bear, each CPU could take every fourth page, computing the > expensive condition for each tuple in that page. The results of the > scan can be retired asynchronously to the next node above. > > -jwb
I can confirm that bringing Postgres code to multi-thread implementation requires quite a bit of ground work. I have been working for a long while with a Postgres 7.* fork that uses pthreads rather than processes. The effort to make all the subsystems thread safe took some time and touched almost every section of the codebase. I recently spent some time trying to optimize for Chip Multi-Threading systems but focused more on total throughput rather than single query performance. The biggest wins came from changing some coarse grained locks in the page buffering system to a finer grained implementation. I also tried to improve single query performance by splitting index and sequential scans into two threads, one to fault in pages and check tuple visibility and the other for everything else. My success was limited and it was hard for me to work the proper costing into the query optimizer so that it fired at the right times. One place that multiple threads really helped was in index building. My code is poorly commented and the build system is a mess (I am only building 64bit SPARC for embedding into another app). However, I am using it in production and source is available if it's of any help. http://weaver2.dev.java.net Myron Scott On Oct 20, 2008, at 11:28 PM, Chuck McDevitt wrote: > There is a problem trying to make Postgres do these things in > Parallel. > > The backend code isn’t thread-safe, so doing a multi-thread > implementation requires quite a bit of work. > > Using multiple processes has its own problems: The whole way > locking works equates one process with one transaction (The proc > table is one entry per process). Processes would conflict on locks, > deadlocking themselves, as well as many other problems. > > It’s all a good idea, but the work is probably far more than you > expect. > > Async I/O might be easier, if you used pThreads, which is mostly > portable, but not to all platforms. (Yes, they do work on Windows) > > From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org > ] On Behalf Of Jeffrey Baker > Sent: 2008-10-20 22:25 > To: Julius Stroffek > Cc: pgsql-hackers@postgresql.org; Dano Vojtek > Subject: Re: [HACKERS] Multi CPU Queries - Feedback and/or > suggestions wanted! > > On Mon, Oct 20, 2008 at 12:05 PM, Julius Stroffek <Julius.Stroffek@sun.com > > wrote: > Topics that seem to be of interest and most of them were already > discussed at developers meeting in Ottawa are > 1.) parallel sorts > 2.) parallel query execution > 3.) asynchronous I/O > 4.) parallel COPY > 5.) parallel pg_dump > 6.) using threads for parallel processing > [...] > 2.) > Different subtrees (or nodes) of the plan could be executed in > parallel > on different CPUs and the results of this subtrees could be requested > either synchronously or asynchronously. > > I don't see why multiple CPUs can't work on the same node of a > plan. For instance, consider a node involving a scan with an > expensive condition, like UTF-8 string length. If you have four > CPUs you can bring to bear, each CPU could take every fourth page, > computing the expensive condition for each tuple in that page. The > results of the scan can be retired asynchronously to the next node > above. > > -jwb
On Mon, 2008-10-20 at 21:05 +0200, Julius Stroffek wrote: > He is going to do some investigation in the methods and > write down the possibilities and then he is going to implement > something from that for PostgreSQL. When will this work be complete? We are days away from completing main work on 8.4, so you won't get much discussion on this for a few months yet. Will it be complete in time for 8.5? Or much earlier even? Julius, you don't mention what your role is in this. In what sense is Dano's master's thesis a "we" thing? -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
Julius Stroffek wrote: > Hi All, > > we would like to start some work on improving the performance of > PostgreSQL in a multi-CPU environment. Dano Vojtek is student at the > Faculty of Mathematics and Physics of Charles university in Prague > (http://www.mff.cuni.cz) and he is going to cover this topic in his > master thesis. He is going to do some investigation in the methods and > write down the possibilities and then he is going to implement something > from that for PostgreSQL. > > We want to come out with a serious proposal for this work after > collecting the feedback/opinions and doing the more serious investigation. Exciting stuff, and clearly a direction we need to explore. > Topics that seem to be of interest and most of them were already > discussed at developers meeting in Ottawa are > 1.) parallel sorts > 2.) parallel query execution > 3.) asynchronous I/O I think the current plan is to use posix_advise() to allow parallel I/O, rather than async I/O becuase posix_advise() will require fewer code changes. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Thu, 23 Oct 2008, Bruce Momjian wrote: > I think the current plan is to use posix_advise() to allow parallel I/O, > rather than async I/O becuase posix_advise() will require fewer code > changes. These are not necessarily mutually exclusive designs. fadvise works fine on Linux, but as far as I know only async I/O works on Solaris. Linux also has an async I/O library, and it's not clear to me yet whether that might work even better than the fadvise approach. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Thu, Oct 23, 2008 at 4:53 PM, Greg Smith <gsmith@gregsmith.com> wrote: >> I think the current plan is to use posix_advise() to allow parallel I/O, >> rather than async I/O becuase posix_advise() will require fewer code >> changes. > > These are not necessarily mutually exclusive designs. fadvise works fine on > Linux, but as far as I know only async I/O works on Solaris. Linux also has > an async I/O library, and it's not clear to me yet whether that might work > even better than the fadvise approach. fadvise is a kludge. While it will help, it still makes us completely reliant on the OS. For performance reasons, we should be supporting a multi-block read directly into shared buffers. IIRC, we currently have support for rings in the buffer pool, which we could read directly into. Though, an LRU-based buffer manager design would be more optimal in this case. -- Jonah H. Harris, Senior DBA myYearbook.com
Jonah H. Harris wrote: > On Thu, Oct 23, 2008 at 4:53 PM, Greg Smith <gsmith@gregsmith.com> wrote: > >> I think the current plan is to use posix_advise() to allow parallel I/O, > >> rather than async I/O becuase posix_advise() will require fewer code > >> changes. > > > > These are not necessarily mutually exclusive designs. fadvise works fine on > > Linux, but as far as I know only async I/O works on Solaris. Linux also has > > an async I/O library, and it's not clear to me yet whether that might work > > even better than the fadvise approach. > > fadvise is a kludge. While it will help, it still makes us completely > reliant on the OS. For performance reasons, we should be supporting a > multi-block read directly into shared buffers. IIRC, we currently > have support for rings in the buffer pool, which we could read > directly into. Though, an LRU-based buffer manager design would be > more optimal in this case. True, it is a kludge but if it gives us 95% of the benfit with 10% of the code, it is a win. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
I couldn't get async I/O to work on Linux. That is it "worked" but performed the same as reading one block at a time. On solaris the situation is reversed. In what way is fadvise a kludge? greg On 24 Oct 2008, at 01:44 AM, Bruce Momjian <bruce@momjian.us> wrote: > Jonah H. Harris wrote: >> On Thu, Oct 23, 2008 at 4:53 PM, Greg Smith <gsmith@gregsmith.com> >> wrote: >>>> I think the current plan is to use posix_advise() to allow >>>> parallel I/O, >>>> rather than async I/O becuase posix_advise() will require fewer >>>> code >>>> changes. >>> >>> These are not necessarily mutually exclusive designs. fadvise >>> works fine on >>> Linux, but as far as I know only async I/O works on Solaris. >>> Linux also has >>> an async I/O library, and it's not clear to me yet whether that >>> might work >>> even better than the fadvise approach. >> >> fadvise is a kludge. While it will help, it still makes us >> completely >> reliant on the OS. For performance reasons, we should be >> supporting a >> multi-block read directly into shared buffers. IIRC, we currently >> have support for rings in the buffer pool, which we could read >> directly into. Though, an LRU-based buffer manager design would be >> more optimal in this case. > > True, it is a kludge but if it gives us 95% of the benfit with 10% of > the code, it is a win. > > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers
Greg Stark wrote: > I couldn't get async I/O to work on Linux. That is it "worked" but > performed the same as reading one block at a time. On solaris the > situation is reversed. > > In what way is fadvise a kludge? I think he is saying AIO gives us more flexibility, but I am unsure we need it. --------------------------------------------------------------------------- > > greg > > On 24 Oct 2008, at 01:44 AM, Bruce Momjian <bruce@momjian.us> wrote: > > > Jonah H. Harris wrote: > >> On Thu, Oct 23, 2008 at 4:53 PM, Greg Smith <gsmith@gregsmith.com> > >> wrote: > >>>> I think the current plan is to use posix_advise() to allow > >>>> parallel I/O, > >>>> rather than async I/O becuase posix_advise() will require fewer > >>>> code > >>>> changes. > >>> > >>> These are not necessarily mutually exclusive designs. fadvise > >>> works fine on > >>> Linux, but as far as I know only async I/O works on Solaris. > >>> Linux also has > >>> an async I/O library, and it's not clear to me yet whether that > >>> might work > >>> even better than the fadvise approach. > >> > >> fadvise is a kludge. While it will help, it still makes us > >> completely > >> reliant on the OS. For performance reasons, we should be > >> supporting a > >> multi-block read directly into shared buffers. IIRC, we currently > >> have support for rings in the buffer pool, which we could read > >> directly into. Though, an LRU-based buffer manager design would be > >> more optimal in this case. > > > > True, it is a kludge but if it gives us 95% of the benfit with 10% of > > the code, it is a win. > > > > -- > > Bruce Momjian <bruce@momjian.us> http://momjian.us > > EnterpriseDB http://enterprisedb.com > > > > + If your life is a hard drive, Christ can be your backup. + > > > > -- > > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Thu, Oct 23, 2008 at 8:44 PM, Bruce Momjian <bruce@momjian.us> wrote: > True, it is a kludge but if it gives us 95% of the benfit with 10% of > the code, it is a win. I'd say, optimistically, maybe 30-45% the benefit over a proper multi-block read using O_DIRECT. -- Jonah H. Harris, Senior DBA myYearbook.com
On Thu, Oct 23, 2008 at 10:36 PM, Greg Stark <greg.stark@enterprisedb.com> wrote: > I couldn't get async I/O to work on Linux. That is it "worked" but performed > the same as reading one block at a time. On solaris the situation is > reversed. Hmm, then obviously you did something wrong, because my tests showed it quite well. Pull the source to iozone or fio. > In what way is fadvise a kludge? non-portable, requires more user-to-system CPU, ... need I go on? -- Jonah H. Harris, Senior DBA myYearbook.com
"Jonah H. Harris" <jonah.harris@gmail.com> writes:
> On Thu, Oct 23, 2008 at 10:36 PM, Greg Stark
>> In what way is fadvise a kludge?
> non-portable, requires more user-to-system CPU, ... need I go on?
I'd be interested to know which of these proposals you claim *is*
portable. The single biggest reason to reject 'em all is that
they aren't.
regards, tom lane
On Fri, Oct 24, 2008 at 12:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> non-portable, requires more user-to-system CPU, ... need I go on? > > I'd be interested to know which of these proposals you claim *is* > portable. The single biggest reason to reject 'em all is that > they aren't. Yes, that was bad wording on my part. What I mean to say was unpredictable. Different OSes and filesystems handle fadvise differently (or not at all), which makes any claim to performance gain configuration-dependent. My preferred method, using O_DIRECT and fetching directly into shared buffers, is not without its issues or challenges as well. However, by abstracting the multi-block read interface, we could use more optimal calls depending on the OS. Having done a bit of research and testing in this area (AIO and buffer management), I don't see any easy solution. fadvise will work on some systems and will likely give some gain on them, but won't work for everyone. The alternative is to abstract prefetching and allow platform-specific code, which we rarely do. While we could build an abstract prefetch interface and simply use fadvise for it now (rather than OS-specific code), I don't see an easy win in any case. -- Jonah H. Harris, Senior DBA myYearbook.com
On 24 Oct 2008, at 04:31 AM, "Jonah H. Harris" <jonah.harris@gmail.com> wrote: > On Thu, Oct 23, 2008 at 10:36 PM, Greg Stark > <greg.stark@enterprisedb.com> wrote: >> I couldn't get async I/O to work on Linux. That is it "worked" but >> performed >> the same as reading one block at a time. On solaris the situation is >> reversed. > > Hmm, then obviously you did something wrong, because my tests showed > it quite well. Pull the source to iozone or fio. I posted the source, feel free to point out what I did wrong. It did work on solaris with and without o_direct so I didn't think it was a bug in my code. >> In what way is fadvise a kludge? > > non-portable, requires more user-to-system CPU, ... need I go on? Well it's just as portable, they're both specified by posix. Actually async I/o is in the real-time extensions so one could argue it's less portable. Also before posix_fadvise there was plain old fadvise so it's portable to older platforms too whereas async I/o isn't. Posix_fadvise does require two syscalls and two trips to the buffer manager. But that doesn't really make it a kludge if the resulting code is cleaner than the async I/o code would be. To use async I/o we would have to pin all the buffers we're reading which would be quite a lot of code changes. I did ask for feedback on precisely this point of whether two trips to the buffer manager was a problem. It would have been nice to get the feedback 6 months ago when I posted it instead of now two weeks before feature freeze.
Based on what? I did test this and posted the data. The results I posted showed that posix_fadvise on Linux performed nearly as well on Linux as async I/O on Solaris on identical hardware. More importantly it scaled with the number if drives. A 15 drive array gets about 15x the performance of a 1 drive array if enough read-ahead is done. Plus an extra boost if the input wasn't already sorted which presumably reflects the better i/o ordering. -- greg On 24 Oct 2008, at 04:29 AM, "Jonah H. Harris" <jonah.harris@gmail.com> wrote: > On Thu, Oct 23, 2008 at 8:44 PM, Bruce Momjian <bruce@momjian.us> > wrote: >> True, it is a kludge but if it gives us 95% of the benfit with 10% of >> the code, it is a win. > > I'd say, optimistically, maybe 30-45% the benefit over a proper > multi-block read using O_DIRECT. > > -- > Jonah H. Harris, Senior DBA > myYearbook.com > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers
We did discuss this in Ottawa and I beleive your comment was "the hint is in the name" referring to posix_fadvise. In any case both aio and posix_fadvise are specified by posix so I don't see either as a problem on that front. I don't think we can ignore any longer that we effectively can't use raid arrays with postgres. If you have many concurrent queries or restrict yourself to sequential scans you're ok but if you're doing data warehousing you're going to be pretty disappointed to see your shiny raid array performing like a single drive. greg On 24 Oct 2008, at 05:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Jonah H. Harris" <jonah.harris@gmail.com> writes: >> On Thu, Oct 23, 2008 at 10:36 PM, Greg Stark >>> In what way is fadvise a kludge? > >> non-portable, requires more user-to-system CPU, ... need I go on? > > I'd be interested to know which of these proposals you claim *is* > portable. The single biggest reason to reject 'em all is that > they aren't. > > regards, tom lane
Jonah H. Harris wrote: > fadvise is a kludge. I don't think it's a kludge at all. posix_fadvise() is a pretty nice and clean interface to hint the kernel what pages you're going to access in the near future. I can't immediately come up with a cleaner interface to do that. Compared to async I/O, it's helluva lot simpler to add a few posix_fadvise() calls to an application, than switch to a completely different paradigm. And while posix_fadvise() is just a hint, allowing the OS to prioritize accordingly, all async I/O requests look the same. > While it will help, it still makes us completely > reliant on the OS. That's not a bad thing in my opinion. The OS knows the I/O hardware, disk layout, utilization, and so forth, and is in a much better position to do I/O scheduling than a user process. The only advantage a user process has is that it knows better what pages it's going to need, and posix_fadvise() is a good interface to let the user process tell the kernel that. > IIRC, we currently have support for rings in the buffer pool, which we could read > directly into. The rings won't help you a bit. It's just a different way to choose victim buffers. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Jonah H. Harris wrote: > On Thu, Oct 23, 2008 at 8:44 PM, Bruce Momjian <bruce@momjian.us> wrote: >> True, it is a kludge but if it gives us 95% of the benfit with 10% of >> the code, it is a win. > > I'd say, optimistically, maybe 30-45% the benefit over a proper > multi-block read using O_DIRECT. Let's try to focus. We're not talking about using O_DIRECT, we're talking about using asynchronous I/O or posix_fadvise(). And without more details on what you mean by benefit, under what circumstances, any numbers like that is just unmeasurable handwaving. In terms of getting the RAID array busy, in Greg's tests posix_fadvise() on Linux worked just as well as async I/O works on Solaris. So it doesn't seem like there's any inherent performance advantage in the async I/O interface over posix_fadvise() + read(), There is differences between different OS implementations of the interfaces. But we're developing software for the future, and for a wide range of platforms, and I'm sure operating systems will develop as well. The decision should not be made on what is the fastest interface on a given operating system in 2008. Async I/O might have a small potential edge on CPU usage, because less system calls are needed. However, let me remind you all that we're talking about how to utilize RAID array to do physical, random, I/O as fast as possible. IOW, the bottleneck is I/O, by definition. The CPU efficiency of the kernel interface to initiate the I/O is insignificant, until we reach a large enough random read throughput to saturate the CPU, and even then there's probably more significant CPU savings to be made elsewhere. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Fri, 2008-10-24 at 00:52 -0400, Jonah H. Harris wrote: > While we could build an > abstract prefetch interface and simply use fadvise for it now (rather > than OS-specific code), I don't see an easy win in any case. When building an abstract interface, always use at least two implementations (I guess that would be fadvise on linux and AIO on solaris in this case). You are much more likely to get the interface right this way. -------------- Hannu
On Fri, Oct 24, 2008 at 7:59 AM, Hannu Krosing <hannu@2ndquadrant.com> wrote: > On Fri, 2008-10-24 at 00:52 -0400, Jonah H. Harris wrote: >> While we could build an >> abstract prefetch interface and simply use fadvise for it now (rather >> than OS-specific code), I don't see an easy win in any case. > > When building an abstract interface, always use at least two > implementations (I guess that would be fadvise on linux and AIO on > solaris in this case). You are much more likely to get the interface > right this way. I agree, I just wasn't sure as to whether Greg's patch supported both methods. -- Jonah H. Harris, Senior DBA myYearbook.com
Jonah H. Harris wrote: > On Fri, Oct 24, 2008 at 7:59 AM, Hannu Krosing <hannu@2ndquadrant.com> wrote: > > On Fri, 2008-10-24 at 00:52 -0400, Jonah H. Harris wrote: > >> While we could build an > >> abstract prefetch interface and simply use fadvise for it now (rather > >> than OS-specific code), I don't see an easy win in any case. > > > > When building an abstract interface, always use at least two > > implementations (I guess that would be fadvise on linux and AIO on > > solaris in this case). You are much more likely to get the interface > > right this way. > > I agree, I just wasn't sure as to whether Greg's patch supported both methods. It does not, and probably will not for the near future; we can only hope Solaris suports posix_fadvise() at some point. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
I thought about how to support both and ran into probblems that would make the resulting solutions quite complex. In the libaio view of the world you initiate io and either get a callback or call another syscall to test if it's complete. Either approach has problems for postgres. If the process that initiated io is in the middle of a long query it might takr a long time ot even never get back to complete the io. The callbacks use threads... And polling for completion has the problem that another process could be waiting on the io and can't issue a read as long as the first process has the buffer locked and io in progress. I think aio makes a lot more sense if you're using threads so you can start a thread to wait for the io to complete. Actually I think it might be doable with a lot of work but I'm worried that it would be a lot of extra complexity even when you're not using it. The current patch doesn't change anything when you're not using it and actually is quite simple. greg On 24 Oct 2008, at 03:18 PM, Bruce Momjian <bruce@momjian.us> wrote: > Jonah H. Harris wrote: >> On Fri, Oct 24, 2008 at 7:59 AM, Hannu Krosing >> <hannu@2ndquadrant.com> wrote: >>> On Fri, 2008-10-24 at 00:52 -0400, Jonah H. Harris wrote: >>>> While we could build an >>>> abstract prefetch interface and simply use fadvise for it now >>>> (rather >>>> than OS-specific code), I don't see an easy win in any case. >>> >>> When building an abstract interface, always use at least two >>> implementations (I guess that would be fadvise on linux and AIO on >>> solaris in this case). You are much more likely to get the interface >>> right this way. >> >> I agree, I just wasn't sure as to whether Greg's patch supported >> both methods. > > It does not, and probably will not for the near future; we can only > hope Solaris suports posix_fadvise() at some point. > > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. +
Also keep in mind that solaris is open source these days. If someone wants it they could always go ahead and add the feature ... greg On 24 Oct 2008, at 03:18 PM, Bruce Momjian <bruce@momjian.us> wrote: > Jonah H. Harris wrote: >> On Fri, Oct 24, 2008 at 7:59 AM, Hannu Krosing >> <hannu@2ndquadrant.com> wrote: >>> On Fri, 2008-10-24 at 00:52 -0400, Jonah H. Harris wrote: >>>> While we could build an >>>> abstract prefetch interface and simply use fadvise for it now >>>> (rather >>>> than OS-specific code), I don't see an easy win in any case. >>> >>> When building an abstract interface, always use at least two >>> implementations (I guess that would be fadvise on linux and AIO on >>> solaris in this case). You are much more likely to get the interface >>> right this way. >> >> I agree, I just wasn't sure as to whether Greg's patch supported >> both methods. > > It does not, and probably will not for the near future; we can only > hope Solaris suports posix_fadvise() at some point. > > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. +
* Greg Stark <greg.stark@enterprisedb.com> [081024 10:48]: > I thought about how to support both and ran into probblems that would > make the resulting solutions quite complex. > > In the libaio view of the world you initiate io and either get a > callback or call another syscall to test if it's complete. Either > approach has problems for postgres. If the process that initiated io > is in the middle of a long query it might takr a long time ot even > never get back to complete the io. The callbacks use threads... > > And polling for completion has the problem that another process could > be waiting on the io and can't issue a read as long as the first > process has the buffer locked and io in progress. I think aio makes a > lot more sense if you're using threads so you can start a thread to > wait for the io to complete. > > Actually I think it might be doable with a lot of work but I'm worried > that it would be a lot of extra complexity even when you're not using > it. The current patch doesn't change anything when you're not using it > and actually is quite simple. In the Solaris async IO, are you bound by direct IO? Does the OS page-cache still get primed by async reads? If so, how about starting async IO into a "throwaway" local buffer; treat async IO in the same way as fadvise, a "pre-load the OS page cache so the real read is quick". Sure, I understand it's not the "perfect model", but it I don't see PostgreSQL being refactored enough to have a pure async model happening any time in the near future... -- Aidan Van Dyk Create like a god, aidan@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
Hi Simon,<br /><br /><blockquote cite="mid:1224662306.27145.204.camel@ebony.2ndQuadrant" type="cite"><blockquote type="cite"><prewrap="">He is going to do some investigation in the methods and write down the possibilities and then he is going to implement something from that for PostgreSQL. </pre></blockquote><pre wrap=""> When will this work be complete? We are days away from completing main work on 8.4, so you won't get much discussion on this for a few months yet. Will it be complete in time for 8.5? Or much earlier even? </pre></blockquote> The first guess is that the work willbe done for 8.6. Dano is supposed to finish the work and defend his thesis in something a bit more than a year.<br /><blockquotecite="mid:1224662306.27145.204.camel@ebony.2ndQuadrant" type="cite"><pre wrap="">Julius, you don't mention whatyour role is in this. In what sense is Dano's master's thesis a "we" thing? </pre></blockquote> I am Dano's mentor and we have a closed contact with Zdenek as well.We would like the project to become a "we" thing as another reason why to work on the project. It seems to be betterto research some ideas at the begging and discuss the stuff during development than just individually writing somepiece of code which could be published afterwards. Especially, when this area seems to be of interest of more people.<br/><br /> Cheers<br /><br /> Julo<br />
>Hi Simon, > >He is going to do some investigation in the methods and >write down the possibilities and then he is going to implement >something from that for PostgreSQL. > >When will this work be complete? We are days away from completing main >work on 8.4, so you won't get much discussion on this for a few months >yet. Will it be complete in time for 8.5? Or much earlier even? > >The first guess is that the work will be done for 8.6. >Dano is supposed to finish the work and defend his thesis in something a bit more than a year. > >Julius, you don't mention what your role is in this. In what sense is >Dano's master's thesis a "we" thing? > >I am Dano's mentor and we have a closed contact with Zdenek as well. >We would like the project to become a "we" thing as another reason why to work on the project. >It seems to be better to research some ideas at the begging and discuss the stuff >during development than just individually writing some piece of code which could >be published afterwards. Especially, when this area seems to be of interest of >more people. Threads are where future performance is going to come from: General purpose-> http://www.setup32.com/hardware/cpuchipset/32core-processors-intel-reach e.php GPU-> http://wwwx.cs.unc.edu/~lastra/Research/GPU_performance.html http://www.cs.unc.edu/~geom/GPUSORT/results.html Database engines that want to exploit the ultimate in performance will utilize multiple threads of execution. True, the same thing can be realized by multiple processes, but a process is more expensive than a thread.
Bruce Momjian wrote:
> Greg Stark wrote:
>
>> I couldn't get async I/O to work on Linux. That is it "worked" but
>> performed the same as reading one block at a time. On solaris the
>> situation is reversed.
>>
>> In what way is fadvise a kludge?
>>
>
> I think he is saying AIO gives us more flexibility, but I am unsure we
> need it.
>
absolutely.
posix_fadvise is easy to implement and i would assume that it takes away
a lot of "guessing" on the OS internals side.
the database usually knows that it is gonna read a lot of data in a
certain way and it cannot be a bad idea to give the kernel a hint here.
especially synchronized seq scans and so on are real winners here as you
stop confusing the kernel with XX concurrent readers on the same file.
this can also be an issue with some controller firmwares and so on.
many thanks,
hans