Re: Parallel Executors [was RE: [HACKERS] Threaded Sorting]
Curtis Faith wrote: The current transaction/user state seems to be stored in process global space. This could be changed to be a sointer to a struct stored in a back-end specific shared memory area which would be accessed by the executor process at execution start. The backend would destroy and recreate the shared memory and restart execution in the case where an executor process dies much like the postmaster does with backends now. To the extent the executor process might make changes to the state, which I'd try to avoid if possible (don't know if it is), the executors could obtain locks, otherwise if the executions were constrained to isolated elements (changes to different indexes for example) it seems like it would be possible using an architecture where you have: Imagine there is a PL/Tcl function. On the first call in a session, the PL/Tcl interpreter get's created (that's during execution, okay?). Now the procedure that's called inside of that interpreter creates a global variable ... a global Tcl variable inside of that interpreter, which is totally unknown to the backend since it doesn't know what Tcl is at all and that variable is nothing than an entry in a private hash table inside of that interpreter. On a subsequent call to any PL/Tcl function during that session, it might be good if that darn hashtable entry exists. How do you propose to let this happen? And while at it, the Tcl procedure next calls spi_exec, causing the PL/Tcl function handler to call SPI_exec(), so your isolated executor all of the sudden becomes a fully operational backend, doing the parsing, planning and optimizing, or what? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Parallel Executors [was RE: [HACKERS] Threaded Sorting]
Curtis Faith wrote: The current transaction/user state seems to be stored in process global space. This could be changed to be a sointer to a struct stored in a back-end specific shared memory area which would be accessed by the executor process at execution start. The backend would destroy and recreate the shared memory and restart execution in the case where an executor process dies much like the postmaster does with backends now. To the extent the executor process might make changes to the state, which I'd try to avoid if possible (don't know if it is), the executors could obtain locks, otherwise if the executions were constrained to isolated elements (changes to different indexes for example) it seems like it would be possible using an architecture where you have: Jan Wieck replied: Imagine there is a PL/Tcl function. On the first call in a session, the PL/Tcl interpreter get's created (that's during execution, okay?). Now the procedure that's called inside of that interpreter creates a global variable ... a global Tcl variable inside of that interpreter, which is totally unknown to the backend since it doesn't know what Tcl is at all and that variable is nothing than an entry in a private hash table inside of that interpreter. On a subsequent call to any PL/Tcl function during that session, it might be good if that darn hashtable entry exists. How do you propose to let this happen? And while at it, the Tcl procedure next calls spi_exec, causing the PL/Tcl function handler to call SPI_exec(), so your isolated executor all of the sudden becomes a fully operational backend, doing the parsing, planning and optimizing, or what? You bring up a good point, we couldn't do what I propose for all situations. I had never anticipated that splitting things up would be the rule. For example, the optimizer would have to decide whether it made sense to split up a query from a strictly performance perspective. So now, if we consider the fact that some things could not be done with split backend execution, the logic becomes: if ( splitting is possible splitting is faster ) do the split execution; else do the normal execution; Since the design already splits the backend internally into a separate execution phase, it seems like one could keep the current current implementation for the typical case where splitting doesn't buy anything or cases where there is complex state information that needs to be maintained. If there are no triggers or functions that will be accessed by a given query then I don't see your concerns applying. If there are triggers or other conditions which preclude multi-process execution, we can keep exactly the same behavior as now. The plan execution entry could easily be a place where it either A) did the same thing it currently does or B) passed execution off to a pool as per the original proposal. I have to believe that most SELECTs won't be affected by your concerns. Additionally, even in the case of an UPDATE, many times there are large portions of the operation's actual work that wouldn't be affected even if there are lots of triggers on the tables being updated. The computation of the inside of the WHERE could often be split out without causing any problems with context or state information. The master executor could always be the original backend as it is now and this would be the place where the UPDATE part would be processed after the WHERE tuples had been identified. As with any optimization, it is more complicated and won't handle all the cases. It's just an idea to handle common cases that would otherwise be much slower. That having been said, I'm sure there are much lower hanging fruit on the performance tree and likely will be for a little while. - Curtis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Parallel Executors [was RE: [HACKERS] Threaded Sorting]
tom lane wrote: Curtis Faith [EMAIL PROTECTED] writes: What about splitting out parsing, optimization and plan generation from execution and having a separate pool of exececutor processes. As an optimizer finished with a query plan it would initiate execution by grabbing an executor from a pool and passing it the plan. So different executors would potentially handle the queries from a single transaction? How will you deal with pushing transaction-local state from one to the other? Even if you restrict it to switching at transaction boundaries, you still have session-local state (at minimum user ID and SET settings) to worry about. Hmmm, what transaction boundaries did you mean? Since we are talking about single statement parallization, there must be some specific internal semantics that you believe need isolation. It seems like we'd be able to get most of the benefit and restrict the parallization in a way that would preserve this isolation but I'm curious what you were specifically referring to? The current transaction/user state seems to be stored in process global space. This could be changed to be a sointer to a struct stored in a back-end specific shared memory area which would be accessed by the executor process at execution start. The backend would destroy and recreate the shared memory and restart execution in the case where an executor process dies much like the postmaster does with backends now. To the extent the executor process might make changes to the state, which I'd try to avoid if possible (don't know if it is), the executors could obtain locks, otherwise if the executions were constrained to isolated elements (changes to different indexes for example) it seems like it would be possible using an architecture where you have: Main Executor: Responsible for updating global meta data from each sub-executor and assembling the results of multiple executions. In the case of multiple executor sorts, the main executor would perform a merge sort on the results of it and it's subordinates pre-sorted sub-sets of the relation. Subordinate Executor: Executes sub-plans and returns results or meta-data update information into front-end shared memory directly. To make this optimal, the index code would have to be changed to support the idea of partial scans. In the case of btrees it would be pretty easy using the root page to figure out what index values delineated different 1/2's, 1/3's, 1/4's etc. of the index space. I'm not sure what you'd have to do to support this for table scans as I don't know the PostgreSQL tuple storage mechanism, yet. This does not seem like too much architectural complexity or performance overhead (even for the single executor case) for a big gain for complex query performance. Being able to apply multiple CPUs to a single query is attractive, but I've not yet seen schemes for it that don't look like the extra CPU power would be chewed up in overhead :-(. Do you remember specifc overhead problems/issues? - Curtis ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
