Re: Table spaces again [was Re: [HACKERS] Threaded Sorting]
Jim Buttafuoco wrote: Is this NOT what I have been after for many months now. I dropped the tablespace/location idea before 7.2 because that didn't seem to be any interest. Please see my past email's for the SQL commands and on disk directory layout I have proposed. I have a working 7.2 system with tablespaces/locations (what ever you want to call them, I like locations because tablespace are an Oracle thing). I would like to get this code ported into 7.4. Jim =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: how would you handle table spaces? The plan that's been discussed simply defines a tablespace as being a directory somewhere; physical storage of individual tables would remain basically the same, one or more files under the containing directory. The point of this being, of course, that the DBA could create the tablespace directories on different partitions or volumes in order to provide the behavior he wants. In my mind this would be primarily a cleaner, more flexible reimplementation of the existing database location feature. wow :) can we have the patch? i'd like to try it with my 7.2.2 :). is it stable? how did you implement it precisely? is it as you have proposed it? Hans -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at http://www.postgresql.at, cluster.postgresql.at http://cluster.postgresql.at, www.cybertec.at http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Threaded Sorting
Bingo = great :). The I/O problem seems to be solved :). A table space concept would be top of the histlist :). The symlink version is not very comfortable and I think it would be a real hack. Also: If we had a clean table space concept it would be real advantage. In the first place it would be enough to define a directory (alter tablespace, changing sizes etc. could be a lot of work). How could CREATE TABLESPACE look like? Personally I like the Oracle Syntax. Is it already time to work on the parser for CREATE/ALTER/DROP TABLESPACE? Hans Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Bingo! Want to increase sorting performance, give it more I/O bandwidth, and it will take 1/100th of the time to do threading. Added to TODO: * Allow sorting to use multiple work directories Yeah, I like that. Actually it should apply to all temp files not only sorting. A crude hack would be to allow there to be multiple pg_temp_NNN/ subdirectories (read symlinks) in a database, and then the code would automatically switch among these. Probably a cleaner idea would be to somehow integrate this with tablespace management --- if you could mark some tablespaces as intended for temp stuff, the system could round-robin among those as it creates temp files and/or temp tables. regards, tom lane -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at http://www.postgresql.at, cluster.postgresql.at http://cluster.postgresql.at, www.cybertec.at http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Threaded Sorting
Greg Copeland wrote: I wouldn't hold your breath for any form of threading. Since PostgreSQL is process based, you might consider having a pool of sort processes which address this but I doubt you'll get anywhere talking about threads here. Greg I came across the problem yesterday. We thought about SMP and did some tests on huge tables. The postmaster was running full speed to get the stuff sorted - even on an IDE system. I asked my friends who are doing a lot of work with Oracle on huge SMP machines. I was told that Oracle has a mechanism which can run efficient sorts on SMP machines. It seems to speed up sorting a lot. If we could reduce the time needed to build up an index by 25% it would be a wonderful thing. Just think of a scenario: 1 thread: 24 hours many threads: 18 hours We could gain 6 hours which is a LOT. We have many people running PostgreSQL on systems having wonderful IO systems - in this case IO is not the bottleneck anymore. I had a brief look at the code used for sorting. It is very well documented so maybe it is worth thinking about a parallel algorithm. When talking about threads: A pool of processes for sorting? Maybe this could be useful but I doubt if it the best solution to avoid overhead. Somewhere in the TODO it says that there will be experiments with a threaded backend. This make me think that threads are not a big no no. Hans -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at http://www.postgresql.at, cluster.postgresql.at http://cluster.postgresql.at, www.cybertec.at http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Threaded Sorting
Threads are not the best solutions when it comes to portability. A prefer a process model as well. My concern was that a process model might be a bit too slow for that but if we had processes in memory this would be wonderful thing. Using it for small amounts of data is pretty useless - I totally agree but when it comes to huge amounts of data it can be useful. It is a mechanism for huge installations with a lot of data. Hans That was a fork IIRC. Threading is not used in baseline PostgreSQL nor is there any such plans that I'm aware of. People from time to time ask about threads for this or that and are always told what I'm telling you. The use of threads leads to portability issues not to mention PostgreSQL is entirely built around the process model. Tom is right to dismiss the notion of adding additional CPUs to something that is already I/O bound, however, the concept it self should not be dismissed. Applying multiple CPUs to a sort operation is well accepted and understood technology. At this point, perhaps Tom or one of the other core developers having insight in this area would be willing to address how readily such a mechanism could could be put in place. Also, don't be so fast to dismiss what the process model can do. There is not reason to believe that having a process pool would not be able to perform wonderful things if implemented properly. Basically, the notion would be that the backend processing the query would solicit assistance from the sort pool if one or more processes were available. At that point, several methods could be employed to divide the work. Some form of threshold would also have to be created to prevent the pool from being used when a single backend is capable of addressing the need. Basically the idea is, you only have the pool assist with large tuple counts and then, only when resources are available and resource are available from within the pool. By doing this, you avoid additional overhead for small sort efforts and gain when it matters the most. Regards, Greg -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at http://www.postgresql.at, cluster.postgresql.at http://cluster.postgresql.at, www.cybertec.at http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Threaded Sorting
Threads are bad - I know ... I like the idea of a pool of processes instead of threads - from my point of view this would be useful. I am planning to run some tests (GEQO, AIX, sorts) as soon as I have time to do so (still too much work ahead before :( ...). If I had time I'd love to do something for the PostgreSQL community :(. As far as sorting is concerned: It would be fine if it was possible to define an alternative location for temporary sort files using SET. If you had multiple disks this would help in the case of concurrent sorts because this way people could insert and index many tables at once without having to access just one storage system. This would be an easy way out of the IO limitation ... - at least for some problems. Hans Bruce Momjian wrote: We haven't thought about it yet because there are too many buggy thread implementations. We are probably just now getting to a point where we can consider it. However, lots of databases have moved to threads for all sorts of things and ended up with a royal mess of code. Threads can only improve things in a few areas of the backend so it would be nice if we could limit the exposure to threads to those areas; sorting could certainly be one of them, but frankly, I think disk I/O is our limiting factore there. I would be interested to see some tests that showed otherwise. -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at http://www.postgresql.at, cluster.postgresql.at http://cluster.postgresql.at, www.cybertec.at http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Table spaces again [was Re: [HACKERS] Threaded Sorting]
On 4 Oct 2002 at 21:13, Hans-Jürgen Schönig wrote: Bingo = great :). The I/O problem seems to be solved :). A table space concept would be top of the histlist :). The symlink version is not very comfortable and I think it would be a real hack. Also: If we had a clean table space concept it would be real advantage. In the first place it would be enough to define a directory (alter tablespace, changing sizes etc. could be a lot of work). How could CREATE TABLESPACE look like? Personally I like the Oracle Syntax. Well. I (hopefully) understand need to get table spaces. But I absolutely hate it the way oracle does it.. I am repeating all the points I posted before. There was no follow up. I hope I get some on this. 1) It tries to be a volume assuming OS handles volumes inefficiently. Same mentality as handling all disk I/O by in it self. May be worth when oracle did it but is it worth now? 2) It allows joining multiple volumes for performance reason. If you want to join multiple volume for performance, let RAID handle it. Is it job of RDBMS? 3) It puts multiple objets together. Why? I never fully understood having a opeque file sitting on drive v/s neatly laid directory structure. I would always prefer the directory structure. Can anybody please tell me in detail.(Not just a pointing towards TODO items) 1) What a table space supposed to offer? 2) What a directory structure does not offer that table space does? 3) How do they compare for advantages/disadvantages.. Oracle familiarity is out. That's not even close to being good merit IMO. If postgresql moves to oracle way of doing things, .. well, I won't be as much hapy as I am now.. Thanks for your patience.. Bye Shridhar -- Newton's Little-Known Seventh Law: A bird in the hand is safer than one overhead. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Table spaces again [was Re: [HACKERS] Threaded Sorting]
Can anybody please tell me in detail.(Not just a pointing towards TODO items) 1) What a table space supposed to offer? They allow you to define a maximum amount of storage for a certain set of data. They help you to define the location of data. They help you to define how much data can be used by which ressource. 2) What a directory structure does not offer that table space does? You need to the command line in order to manage quotas - you might not want that. Quotas are handled differently on ever platform (if available). With tablespaces you can assign 30mb to use a, 120mb to user b etc. ... Table spaces are a nice abstraction layer to the file system. 3) How do they compare for advantages/disadvantages.. Oracle familiarity is out. That's not even close to being good merit IMO. If postgresql moves to oracle way of doing things, .. well, I won't be as much hapy as I am now.. Thanks for your patience.. how would you handle table spaces? just propose it to the hackers' list ... we should definitely discuss that ... a bad implementation of table spaces would be painful ... Hans -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at http://www.postgresql.at, cluster.postgresql.at http://cluster.postgresql.at, www.cybertec.at http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Table spaces again [was Re: [HACKERS] Threaded Sorting]
2) What a directory structure does not offer that table space does? You need to the command line in order to manage quotas - you might not want that. Mount a directory on a partition. If the data exceeds on that partition, there would be disk error. Like tablespace getting overflown. I have seen both the scenarios in action.. Of course it can be done somehow. However, with tablespaces it is more db-like and you need not be familiar with the operating system itself. Just think of a company having several different operating systems (suns, linux, bsd, ...). what do you think could be done in this case? my answer would be an abstraction layer called table spaces ... Quotas are handled differently on ever platform (if available). Yeah. But that's sysadmins responsibility not DBA's. Maybe many people ARE the sysadmins of their PostgreSQL box ... When developing a database with an open mind people should try to see a problem from more than just one perspective. Why should anybody just forget about sysdbas??? With tablespaces you can assign 30mb to use a, 120mb to user b etc. ... Table spaces are a nice abstraction layer to the file system. Hmm.. And how does that fit in database metaphor? What practical use is that? I can't imagine as I am a developer and not a DBA. One of our customers did some minor hosting projects with PostgreSQL. That's what he wanted to have because it is a practical issue. a. you don't want to have more than one instance per machine. b. you want to assign a certain amount of space to a certain user without using quotas. just think of administration tools - tablespaces are as simple as a select. per directory is a first step - a good step and a good idea but tablespaces are a useful invention. just think of hosting companies, hybrid environments, etc ... tablespaces or not a devil and sysdbas may be developers ... Hans -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at http://www.postgresql.at, cluster.postgresql.at http://cluster.postgresql.at, www.cybertec.at http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Table spaces again [was Re: [HACKERS] Threaded Sorting]
On 7 Oct 2002 at 16:49, Hans-Jürgen Schönig wrote: Mount a directory on a partition. If the data exceeds on that partition, there would be disk error. Like tablespace getting overflown. I have seen both the scenarios in action.. Of course it can be done somehow. However, with tablespaces it is more db-like and you need not be familiar with the operating system itself. Just think of a company having several different operating systems (suns, linux, bsd, ...). what do you think could be done in this case? my answer would be an abstraction layer called table spaces ... OK. Point noted. Suspended till next point. Quotas are handled differently on ever platform (if available). Yeah. But that's sysadmins responsibility not DBA's. Maybe many people ARE the sysadmins of their PostgreSQL box ... When developing a database with an open mind people should try to see a problem from more than just one perspective. Why should anybody just forget about sysdbas??? If DBA is sysadmin, does it make a difference if he learnes about mount/ln or table spaces. Yes it does. Table spaces are limited to databases but mount/ln is useful for any general purpose sysadmin work. That answers the last point as well, I guess.. With tablespaces you can assign 30mb to use a, 120mb to user b etc. ... Table spaces are a nice abstraction layer to the file system. Hmm.. And how does that fit in database metaphor? What practical use is that? I can't imagine as I am a developer and not a DBA. One of our customers did some minor hosting projects with PostgreSQL. That's what he wanted to have because it is a practical issue. a. you don't want to have more than one instance per machine. b. you want to assign a certain amount of space to a certain user without using quotas. just think of administration tools - tablespaces are as simple as a select. Agreed. Perfect point and I didn't thought of it. But it can be done in directory structure as well. Of course it's quite a deviation from what one thinks as plain old directory structure. But if this is one point where table spaces win, let's borrow that. There is lot of baggage in table spaces that can be left out.. Besides AFAIU, tablespaces implements quota using data files which are pre- allocated. Pre-claiming space/resource is the evil of everything likes of oracle do and runs in exact opposite direction of postgresql philosophy. If postgresql has to implement quotas on object, it should do without preclaiming space. Besides if postgresql offers quota on per object basis in directory/object scheme, I am sure that's far more granular than tablespaces. Choice is good.. per directory is a first step - a good step and a good idea but tablespaces are a useful invention. just think of hosting companies, hybrid environments, etc ... tablespaces or not a devil and sysdbas may be developers ... It's not about devil. It's about revaluating need once again. Especially at the level of tablespace concept in itself. Bye Shridhar -- Oblivion together does not frighten me, beloved.-- Thalassa (in Anne Mulhall's body), Return to Tomorrow, stardate 4770.3. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Table spaces again [was Re: [HACKERS] Threaded Sorting]
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: how would you handle table spaces? The plan that's been discussed simply defines a tablespace as being a directory somewhere; physical storage of individual tables would remain basically the same, one or more files under the containing directory. The point of this being, of course, that the DBA could create the tablespace directories on different partitions or volumes in order to provide the behavior he wants. In my mind this would be primarily a cleaner, more flexible reimplementation of the existing database location feature. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Table spaces again [was Re: [HACKERS] Threaded Sorting]
Quotas are handled differently on ever platform (if available). Yeah. But that's sysadmins responsibility not DBA's. Maybe many people ARE the sysadmins of their PostgreSQL box ... When developing a database with an open mind people should try to see a problem from more than just one perspective. Why should anybody just forget about sysdbas??? If DBA is sysadmin, does it make a difference if he learnes about mount/ln or table spaces. Yes it does. Table spaces are limited to databases but mount/ln is useful for any general purpose sysadmin work. That answers the last point as well, I guess.. I agree but still, think of hybrid systems .. Agreed. Perfect point and I didn't thought of it. But it can be done in directory structure as well. Of course it's quite a deviation from what one thinks as plain old directory structure. But if this is one point where table spaces win, let's borrow that. There is lot of baggage in table spaces that can be left out.. Besides AFAIU, tablespaces implements quota using data files which are pre- allocated. Pre-claiming space/resource is the evil of everything likes of oracle do and runs in exact opposite direction of postgresql philosophy. If postgresql has to implement quotas on object, it should do without preclaiming space. Besides if postgresql offers quota on per object basis in directory/object scheme, I am sure that's far more granular than tablespaces. Choice is good.. I didn't think of pre allocation - this is pretty much like Oracle would do it. I was thinking of having a maximum size or something like that. Overhead such as EXTENDS and things like that don't seem too useful for me - that's what a filesystem can be used for. I agree with Tom: If a tablespace was a directory it would be pretty simple and pretty useful. If people could define a maximum size it would be more than perfect. All I think is necessary is: - having data in different, user defined, locations - having the chance to define a maximum size for that tablespace. Suggestion: CREATE TABLESPACE: Create a directory with a certain size (optional) - nothing special here. ALTER TABLESPACE: resize table space. resizing is possible if the amount of data in the tablespace new size of tablespace DROP TABLESPACE: remove table space. the question in this case is - what about the objects in the tablespace? objects can not always be deleted (just think of inheritance and parent tables) It's not about devil. It's about revaluating need once again. Especially at the level of tablespace concept in itself. That's why people should discuss it and think about it :). People want a good implementation or no implementation :). This is Open Source - it is designed to be discussed :). Hans -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at http://www.postgresql.at, cluster.postgresql.at http://cluster.postgresql.at, www.cybertec.at http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Table spaces again [was Re: [HACKERS] Threaded Sorting]
Is this NOT what I have been after for many months now. I dropped the tablespace/location idea before 7.2 because that didn't seem to be any interest. Please see my past email's for the SQL commands and on disk directory layout I have proposed. I have a working 7.2 system with tablespaces/locations (what ever you want to call them, I like locations because tablespace are an Oracle thing). I would like to get this code ported into 7.4. Jim =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: how would you handle table spaces? The plan that's been discussed simply defines a tablespace as being a directory somewhere; physical storage of individual tables would remain basically the same, one or more files under the containing directory. The point of this being, of course, that the DBA could create the tablespace directories on different partitions or volumes in order to provide the behavior he wants. In my mind this would be primarily a cleaner, more flexible reimplementation of the existing database location feature. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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: 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
Re: [HACKERS] Threaded Sorting
tom lane writes: The notion of a sort process pool seems possibly attractive. I'm unconvinced that it's going to be a win though because of the cost of shoving data across address-space boundaries. 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. This would allow the potential for passing partial plans to multiple executors so a given query might be split up into three or four pieces and then executed in parallel with the results passed through a shared memory area owned by each executor process. This would allow for potential optimization of sorts without threads or incurring the overhead problems you mentioned for a separate sorter process. The optimizer could do things like split a scan into 3 or 4 pieces before sending it off for execution and then join the pieces back together. It could also make complex queries much faster if there are idling CPUs if the optimizer was updated to take advantage of this. If we are going to split things apart, then this should be done at a natural communication boundary right? The code has this logical split right now anyway so the change would be more natural. OTOH, there are much bigger fish to fry at the moment, I suspect. - Curtis ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Threaded Sorting
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. 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 :-(. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Threaded Sorting
Did anybody think about threaded sorting so far? Assume an SMP machine. In the case of building an index or in the case of sorting a lot of data there is just one backend working. Therefore just one CPU is used. What about starting a thread for every temporary file being created? This way CREATE INDEX could use many CPUs. Maybe this is worth thinking about because it will speed up huge databases and enterprise level computing. Best regards, Hans-Jürgen Schönig -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at http://www.postgresql.at, cluster.postgresql.at http://cluster.postgresql.at, www.cybertec.at http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Threaded Sorting
On 4 Oct 2002 at 9:46, Hans-Jürgen Schönig wrote: Did anybody think about threaded sorting so far? Assume an SMP machine. In the case of building an index or in the case of sorting a lot of data there is just one backend working. Therefore just one CPU is used. What about starting a thread for every temporary file being created? This way CREATE INDEX could use many CPUs. Maybe this is worth thinking about because it will speed up huge databases and enterprise level computing. I have a better plan. I have a thread architecture ready which acts as generic thread templates. Even the function pointers in the thread can be altered on the fly. I suggest we use some such architecture for threading. It can be used in any module without hardcoding things. Like say in sorting we assign exclusive jobs/data ranges to threads then there would be minimum locking and one thread could merge the results.. Something like that. All it takes to change entry functions to accept one more parameter that indicates range of values to act upon. In non-threaded version, it's not there because the function acts on entire data set. Further more, with this model threading support can be turned off easily. In non-threaded model, a wrapper function can call the entry point in series with necessary arguments. So postgresql does not have to deal with not-so-good- enough thread implementations. Keeping tradition to conservative defaults we can set default threads to off.. The code is in C++ but it's hardly couple of pages. I can convert it to C and post it if required.. Let me know.. Bye Shridhar -- Parkinson's Fourth Law: The number of people in any working group tends to increaseregardless of the amount of work to be done. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Threaded Sorting
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: Did anybody think about threaded sorting so far? Assume an SMP machine. In the case of building an index or in the case of sorting a lot of data there is just one backend working. Therefore just one CPU is used. What about starting a thread for every temporary file being created? This way CREATE INDEX could use many CPUs. In my experience, once you have enough data to force a temp file to be used, the sort algorithm is I/O bound anyway. Throwing more CPUs at it won't help much. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Threaded Sorting
I wouldn't hold your breath for any form of threading. Since PostgreSQL is process based, you might consider having a pool of sort processes which address this but I doubt you'll get anywhere talking about threads here. Greg On Fri, 2002-10-04 at 02:46, Hans-Jürgen Schönig wrote: Did anybody think about threaded sorting so far? Assume an SMP machine. In the case of building an index or in the case of sorting a lot of data there is just one backend working. Therefore just one CPU is used. What about starting a thread for every temporary file being created? This way CREATE INDEX could use many CPUs. Maybe this is worth thinking about because it will speed up huge databases and enterprise level computing. Best regards, Hans-Jürgen Schönig -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at http://www.postgresql.at, cluster.postgresql.at http://cluster.postgresql.at, www.cybertec.at http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Threaded Sorting
On Fri, 2002-10-04 at 09:40, Hans-Jürgen Schönig wrote: I had a brief look at the code used for sorting. It is very well documented so maybe it is worth thinking about a parallel algorithm. When talking about threads: A pool of processes for sorting? Maybe this could be useful but I doubt if it the best solution to avoid overhead. Somewhere in the TODO it says that there will be experiments with a threaded backend. This make me think that threads are not a big no no. Hans That was a fork IIRC. Threading is not used in baseline PostgreSQL nor is there any such plans that I'm aware of. People from time to time ask about threads for this or that and are always told what I'm telling you. The use of threads leads to portability issues not to mention PostgreSQL is entirely built around the process model. Tom is right to dismiss the notion of adding additional CPUs to something that is already I/O bound, however, the concept it self should not be dismissed. Applying multiple CPUs to a sort operation is well accepted and understood technology. At this point, perhaps Tom or one of the other core developers having insight in this area would be willing to address how readily such a mechanism could could be put in place. Also, don't be so fast to dismiss what the process model can do. There is not reason to believe that having a process pool would not be able to perform wonderful things if implemented properly. Basically, the notion would be that the backend processing the query would solicit assistance from the sort pool if one or more processes were available. At that point, several methods could be employed to divide the work. Some form of threshold would also have to be created to prevent the pool from being used when a single backend is capable of addressing the need. Basically the idea is, you only have the pool assist with large tuple counts and then, only when resources are available and resource are available from within the pool. By doing this, you avoid additional overhead for small sort efforts and gain when it matters the most. Regards, Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Threaded Sorting
On Fri, 2002-10-04 at 10:37, Hans-Jürgen Schönig wrote: My concern was that a process model might be a bit too slow for that but if we had processes in memory this would be wonderful thing. Yes, that's the point of having a pool. The idea is not only do you avoid process creation and destruction which is notoriously expensive on many platforms, they would sit idle until signaled to begin working on it's assigned sort operation. Ideally, these would be configurable options which would include items such as, pool size (maximum number of processes in the pool), max concurrency level (maximum number of process from the pool which can contribute to a single backend) and tuple count threshold (threshold which triggers solicition for assistance from the sort pool). Using it for small amounts of data is pretty useless - I totally agree but when it comes to huge amounts of data it can be useful. It is a mechanism for huge installations with a lot of data. Hans Agreed. Thus the importance of being able to specify some type of meaningful threshold. Any of the core developers wanna chime in here on this concept? Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Threaded Sorting
On Fri, 2002-10-04 at 12:26, Bruce Momjian wrote: Added to TODO: * Allow sorting to use multiple work directories Why wouldn't that fall under the table space effort??? Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Threaded Sorting
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Bingo! Want to increase sorting performance, give it more I/O bandwidth, and it will take 1/100th of the time to do threading. Added to TODO: * Allow sorting to use multiple work directories Yeah, I like that. Actually it should apply to all temp files not only sorting. A crude hack would be to allow there to be multiple pg_temp_NNN/ subdirectories (read symlinks) in a database, and then the code would automatically switch among these. TODO updated: * Allow sorting/temp files to use multiple work directories Tom, what temp files do we use that aren't for sorting; I forgot. Probably a cleaner idea would be to somehow integrate this with tablespace management --- if you could mark some tablespaces as intended for temp stuff, the system could round-robin among those as it creates temp files and/or temp tables. Yes, tablespaces would be the place for this. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Threaded Sorting
Greg Copeland wrote: -- Start of PGP signed section. On Fri, 2002-10-04 at 12:26, Bruce Momjian wrote: Added to TODO: * Allow sorting to use multiple work directories Why wouldn't that fall under the table space effort??? Yes, but we make it a separate item so we are sure that is implemented as part of tablespaces. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Threaded Sorting
I see. I just always assumed that it would be done as part of table space effort as it's such a defacto feature. I am curious as to why no one has commented on the other rather obvious performance enhancement which was brought up in this thread. Allowing for parallel sorting seems rather obvious and is a common enhancement yet seems to of been completely dismissed as people seem to be fixated on I/O. Go figure. Greg On Fri, 2002-10-04 at 14:02, Bruce Momjian wrote: Greg Copeland wrote: -- Start of PGP signed section. On Fri, 2002-10-04 at 12:26, Bruce Momjian wrote: Added to TODO: * Allow sorting to use multiple work directories Why wouldn't that fall under the table space effort??? Yes, but we make it a separate item so we are sure that is implemented as part of tablespaces. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Threaded Sorting
Greg Copeland wrote: -- Start of PGP signed section. I see. I just always assumed that it would be done as part of table space effort as it's such a defacto feature. I am curious as to why no one has commented on the other rather obvious performance enhancement which was brought up in this thread. Allowing for parallel sorting seems rather obvious and is a common enhancement yet seems to of been completely dismissed as people seem to be fixated on I/O. Go figure. We think we are fixated on I/O because we think that is where the delay is. Is there a reason we shouldn't think that? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Threaded Sorting
Well, that's why I was soliciting developer input as to exactly what goes on with sorts. From what I seem to be hearing, all sorts result in temp files being created and/or used. If that's the case then yes, I can understand the fixation. Of course that opens the door for it being a horrible implementation. If that's not the case, then parallel sorts still seem like a rather obvious route to look into. Greg On Fri, 2002-10-04 at 14:15, Bruce Momjian wrote: Greg Copeland wrote: -- Start of PGP signed section. I see. I just always assumed that it would be done as part of table space effort as it's such a defacto feature. I am curious as to why no one has commented on the other rather obvious performance enhancement which was brought up in this thread. Allowing for parallel sorting seems rather obvious and is a common enhancement yet seems to of been completely dismissed as people seem to be fixated on I/O. Go figure. We think we are fixated on I/O because we think that is where the delay is. Is there a reason we shouldn't think that? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Threaded Sorting
Bruce Momjian [EMAIL PROTECTED] writes: Tom, what temp files do we use that aren't for sorting; I forgot. MATERIALIZE plan nodes are the only thing I can think of offhand that uses a straight temp file. But ISTM that if this makes sense for our internal temp files, it makes sense for user-created temp tables as well. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Threaded Sorting
Greg Copeland wrote: -- Start of PGP signed section. Well, that's why I was soliciting developer input as to exactly what goes on with sorts. From what I seem to be hearing, all sorts result in temp files being created and/or used. If that's the case then yes, I can understand the fixation. Of course that opens the door for it being a horrible implementation. If that's not the case, then parallel sorts still seem like a rather obvious route to look into. We use tape sorts, ala Knuth, meaning we sort in memory as much as possible, but when there is more data than fits in memory, rather than swapping, we write to temp files then merge the temp files (aka tapes). -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Threaded Sorting
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom, what temp files do we use that aren't for sorting; I forgot. MATERIALIZE plan nodes are the only thing I can think of offhand that uses a straight temp file. But ISTM that if this makes sense for our internal temp files, it makes sense for user-created temp tables as well. Yes, I was thinking that, but of course, those are real tables, rather than just files. Not sure how clean it will be to mix those in the same directory. We haven't in the past. Is it a good idea? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Threaded Sorting
On Fri, 2002-10-04 at 14:31, Bruce Momjian wrote: We use tape sorts, ala Knuth, meaning we sort in memory as much as possible, but when there is more data than fits in memory, rather than swapping, we write to temp files then merge the temp files (aka tapes). Right, which is what I originally assumed. On lower end systems, that works great. Once you allow that people may actually have high-end systems with multiple CPUs and lots of memory, wouldn't it be nice to allow for huge improvements on large sorts? Basically, you have two ends of the spectrum. One, where you don't have enough memory and become I/O bound. The other is where you have enough memory but are CPU bound; where potentially you have extra CPUs to spare. Seems to me they are not mutually exclusive. Unless I've missed something, the ideal case is to never use tapes for sorting. Which is saying, you're trying to optimize an already less an ideal situation (which is of course good). I'm trying to discuss making it a near ideal use of available resources. I can understand why addressing the seemingly more common I/O bound case would receive priority, however, I'm at a loss as to why the other would be completely ignored. Seems to me, implementing both would even work in a complimentary fashion on the low-end cases and yield more breathing room for the high-end cases. What am I missing for the other case to be completely ignored? Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Threaded Sorting
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: ... But ISTM that if this makes sense for our internal temp files, it makes sense for user-created temp tables as well. Yes, I was thinking that, but of course, those are real tables, rather than just files. Not sure how clean it will be to mix those in the same directory. We haven't in the past. Is it a good idea? Sure we have --- up till recently, pg_temp files just lived in the database directory. I think it was you that added the pg_temp subdirectory, and the reason you did it was to let people symlink the temp files to someplace else. But that's just a zeroth-order approximation to providing a tablespace facility for these things. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Threaded Sorting
Hans-Jürgen Schönig wrote: Did anybody think about threaded sorting so far? Assume an SMP machine. In the case of building an index or in the case of sorting a lot of data there is just one backend working. Therefore just one CPU is used. What about starting a thread for every temporary file being created? This way CREATE INDEX could use many CPUs. Maybe this is worth thinking about because it will speed up huge databases and enterprise level computing. We haven't thought about it yet because there are too many buggy thread implementations. We are probably just now getting to a point where we can consider it. However, lots of databases have moved to threads for all sorts of things and ended up with a royal mess of code. Threads can only improve things in a few areas of the backend so it would be nice if we could limit the exposure to threads to those areas; sorting could certainly be one of them, but frankly, I think disk I/O is our limiting factore there. I would be interested to see some tests that showed otherwise. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Threaded Sorting
On Fri, 4 Oct 2002, Bruce Momjian wrote: Hans-Jürgen Schönig wrote: Did anybody think about threaded sorting so far? Assume an SMP machine. In the case of building an index or in the case of sorting a lot of data there is just one backend working. Therefore just one CPU is used. What about starting a thread for every temporary file being created? This way CREATE INDEX could use many CPUs. Maybe this is worth thinking about because it will speed up huge databases and enterprise level computing. We haven't thought about it yet because there are too many buggy thread implementations. We are probably just now getting to a point where we can consider it. However, lots of databases have moved to threads for all sorts of things and ended up with a royal mess of code. Threads can only improve things in a few areas of the backend so it would be nice if we could limit the exposure to threads to those areas; sorting could certainly be one of them, but frankly, I think disk I/O is our limiting factore there. I would be interested to see some tests that showed otherwise. Wouldn't the type of disk subsystem really make a big difference here? With a couple of U160 cards and a dozen 15krpm hard drives, I would imagine I/O would no longer be as much of an issue as a single drive system would be. It seems like sometimes we consider these issues more from the one or two SCSI drives perspective insted of the big box o drives perspective. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Threaded Sorting
Hans-Jürgen Schönig wrote: Threads are bad - I know ... I like the idea of a pool of processes instead of threads - from my point of view this would be useful. I am planning to run some tests (GEQO, AIX, sorts) as soon as I have time to do so (still too much work ahead before :( ...). If I had time I'd love to do something for the PostgreSQL community :(. As far as sorting is concerned: It would be fine if it was possible to define an alternative location for temporary sort files using SET. If you had multiple disks this would help in the case of concurrent sorts because this way people could insert and index many tables at once without having to access just one storage system. This would be an easy way out of the IO limitation ... - at least for some problems. Bingo! Want to increase sorting performance, give it more I/O bandwidth, and it will take 1/100th of the time to do threading. Ingres had a nice feature where you could specify sort directories and it would cycle through those directories while it did the tape sort. Added to TODO: * Allow sorting to use multiple work directories -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Threaded Sorting
scott.marlowe wrote: We haven't thought about it yet because there are too many buggy thread implementations. We are probably just now getting to a point where we can consider it. However, lots of databases have moved to threads for all sorts of things and ended up with a royal mess of code. Threads can only improve things in a few areas of the backend so it would be nice if we could limit the exposure to threads to those areas; sorting could certainly be one of them, but frankly, I think disk I/O is our limiting factore there. I would be interested to see some tests that showed otherwise. Wouldn't the type of disk subsystem really make a big difference here? With a couple of U160 cards and a dozen 15krpm hard drives, I would imagine I/O would no longer be as much of an issue as a single drive system would be. It seems like sometimes we consider these issues more from the one or two SCSI drives perspective insted of the big box o drives perspective. Yes, it is mostly for non-RAID drives, but also, sometimes single drives can be faster. When you have a drive array, it isn't as easy to hit each drive and keep it running sequentially. Of course, I don't have any hard numbers on that. ;-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Threaded Sorting
Bruce Momjian wrote: scott.marlowe wrote: snip It seems like sometimes we consider these issues more from the one or two SCSI drives perspective insted of the big box o drives perspective. Yes, it is mostly for non-RAID drives, but also, sometimes single drives can be faster. When you have a drive array, it isn't as easy to hit each drive and keep it running sequentially. Of course, I don't have any hard numbers on that. ;-) Arrghh... please remember that big bunch of drives != all in one array. It's common to have a bunch of drives and allocate different ones for different tasks appropriately, whether in array sets, individually, mirrored, etc. 100% totally feasible to have a separate 15k SCSI drive or two just purely for doing sorts if it would assist in throughput. :-) Regards and best wishes, Justin Clift -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Threaded Sorting
Bruce Momjian [EMAIL PROTECTED] writes: Bingo! Want to increase sorting performance, give it more I/O bandwidth, and it will take 1/100th of the time to do threading. Added to TODO: * Allow sorting to use multiple work directories Yeah, I like that. Actually it should apply to all temp files not only sorting. A crude hack would be to allow there to be multiple pg_temp_NNN/ subdirectories (read symlinks) in a database, and then the code would automatically switch among these. Probably a cleaner idea would be to somehow integrate this with tablespace management --- if you could mark some tablespaces as intended for temp stuff, the system could round-robin among those as it creates temp files and/or temp tables. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Threaded Sorting
Greg Copeland [EMAIL PROTECTED] writes: ... I can understand why addressing the seemingly more common I/O bound case would receive priority, however, I'm at a loss as to why the other would be completely ignored. Bruce already explained that we avoid threads because of portability and robustness considerations. The notion of a sort process pool seems possibly attractive. I'm unconvinced that it's going to be a win though because of the cost of shoving data across address-space boundaries. Another issue is that the sort comparison function can be anything, including user-defined code that does database accesses or other interesting stuff. This would mean that the sort auxiliary process would have to adopt the database user identity of the originating process, and quite possibly absorb a whole lot of other context information before it could correctly/safely execute the comparison function. That pushes the overhead up a lot more. (The need to allow arbitrary operations in the comparison function would put a pretty substantial crimp on a thread-based approach, too, even if we were willing to ignore the portability issue.) Still, if you want to try it out, feel free ... this is an open-source project, and if you can't convince other people that an idea is worth implementing, that doesn't mean you can't implement it yourself and prove 'em wrong. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Threaded Sorting
On Fri, 2002-10-04 at 15:07, Tom Lane wrote: the sort comparison function can be anything, including user-defined code that does database accesses or other interesting stuff. This This is something that I'd not considered. would mean that the sort auxiliary process would have to adopt the database user identity of the originating process, and quite possibly absorb a whole lot of other context information before it could correctly/safely execute the comparison function. That pushes the overhead up a lot more. Significantly! Agreed. Still, if you want to try it out, feel free ... this is an open-source project, and if you can't convince other people that an idea is worth implementing, that doesn't mean you can't implement it yourself and prove 'em wrong. No Tom, my issue wasn't if I could or could not convince someone but rather that something has been put on the table requesting additional feedback on it's feasibility but had been completely ignored. Fact is, I knew I didn't know enough about the implementation details to even attempt to convince anyone of anything. I simply wanted to explore the idea or rather the feasibility of the idea. In theory, it's a great idea. In practice, I had no idea, thus my desire to seek additional input. As such, it seems a practical implementation may prove difficult. I now understand. Thank you for taking the take to respond in a manner that satisfies my curiosity. That's all I was looking for. :) Best Regards, Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Threaded Sorting
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: ... But ISTM that if this makes sense for our internal temp files, it makes sense for user-created temp tables as well. Yes, I was thinking that, but of course, those are real tables, rather than just files. Not sure how clean it will be to mix those in the same directory. We haven't in the past. Is it a good idea? Sure we have --- up till recently, pg_temp files just lived in the database directory. I think it was you that added the pg_temp subdirectory, and the reason you did it was to let people symlink the temp files to someplace else. But that's just a zeroth-order approximation to providing a tablespace facility for these things. OK, TODO updated: * Allow sorting, temp files, temp tables to use multiple work directories FYI, I originally created that directory so a postmaster startup could clear that dir. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
