Re: [DOCS] [Pgcluster-general] PostgreSQL Documentation of
Markus Schiltknecht wrote: > Hello Bruce, > > Bruce Momjian wrote: > > OK, but how does explaining the terms help our users? > > As we even have on sort-of-a solution for shared disk clusters (the > Shared Disk Failover part), we should explain this term (as you already > do there). > > Clarifying that all other solutions are for shared nothing clusters > makes sense, IMO. We don't necessarily need to go into shared memory and > the confusion which shared everything introduced. OTOH, where else to > enlighten people about that if not in such a documentation? > > To answer your question: by explaining these terms, they are > demystified. The users will understand the experts better and have some > fundamental terms which they can base their discussion on. Of course > it's questionable how far to go, and we are debating just that now, I think. > > But I have no doubt in the OSS tradition of good documentation. Long > live the saying 'RTFM'! :-) I figured that shared-disk/memory only really makes sense for multi-master clustering, so I mentioned it in that paragraph: Multi-Master Clustering In clustering, each server can accept write requests, and modified data is transmitted from the original server to every other server before each transaction commits. Heavy write activity can cause excessive locking, leading to poor performance. In fact, write performance is often worse than that of a single ->server. Read requests can be sent to any server. Some ->implementations use cluster-wide shared memory or shared disk ->to reduce the communication overhead. Clustering is best for mostly read workloads, though its big advantage is that any server can accept write requests — there is no need to partition workloads between master and slave servers, and because the data changes are sent from one server to another, there is no problem with non-deterministic functions like random(). Is that enought? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [DOCS] [Pgcluster-general] PostgreSQL Documentation of
Markus Schiltknecht wrote: > Hi, > > Bruce Momjian wrote: > > Oh, I am not aware of pgcluster-II. Did you mean pgpool-II? I think > > so. > > No, I really mean PGCluster-II. Didn't you attend A. Mitani's speech > about PGCluster? Check his slides: > > http://conference.postgresql.org/download/TFCKUpload/62.pdf I just saw it. It does seem more like Oracle RAC than any other method. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [DOCS] "Clustering"
Markus Schiltknecht wrote: > Hi, > > I wrote: > > That reminds me of the requirement of having Multi Master Replication in > > place to do Parallel Query Execution. Sync Multi Master Replication > > being better, while Async Multi Master Replication obviously leads to > > inconsistent responses, when queried in parallel. That may be acceptable > > in certain situations. > > Oops, during rereading "Clustering For Parallel Query Execution", I've > noticed that you mention Data Partitioning as one possible way to do > Parallel Query Execution. Thus Multi Master Replication obviously is not > a requirement, but just another way to allow for Parallel Query > Execution. Mentioning that as well would probably be good. Uh, multi-master replication allows for load balancing, but it doesn't help a single query to run any faster. Think of having only one query running on the cluster. Parallel execution allows a single query to use more than one computer, right? > IMHO, the advice to use multiple, independent databases to do parallel > query execution sounds a little meager. Of course it's also parallel > query execution, but it's not what most people suspect to find under > that section, I would guess. Uh, this confuses me. What is missing? You split tables across multiple servers. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[DOCS] Replication Docs
Hello Bruce, I was trying to put together all comments to specific sections, thus the new thread. Hope that helps. *** Synchronous Multi-Master Replication *** Bruce Momjian wrote: > OK, new title is "Synchonous Multi-Master Replication", and the next > heading is "Asynchronous Multi-Master Replication". Good, I really like that one. :-) >> Why not simply call in "Multi Master Replication"? That implies >> clustering, doesn't it? > > Well, not really because of the async multi-master that is the next > item. Yes, it's fine that way. I was just unsure if you want to have sync and async in one paragraph or not. The proposal "Multi Master Replication" would only fit if we'd describe both in one paragraph. I like to describe both in more detail, as you did now. >> BTW, I'm slowly beginning to accept that you don't want to mix >> "Statement-Based Replication Middleware" with "Multi Master >> Replication". ;-) > > OK, are they mixed now? No, they're not. They're split, which I think is what you want. I've been uncomfortable with was that split into "Statement-Based Replication Middleware" and "Synchronous Multi-Master Replication". I've been arguing that the first describes one possible implementation of the second, while other implementations are not described (2PC, SHMEM, Postgres-R, etc...) I was trying to say that I'm beginning to accept that split, because especially pgpool really seems to put a lot of those burdens to the user. I've been trying to use some humor, but that mainly seems to confuse people. My english might not be good enough for humor, yet. However, where do you now fit Sequoia in? It uses "statement-based replication", but AFAIK it is much more clever than pgpool and handles non-deterministic functions. And the Sequoia people probably won't get excited about not calling them "Multi-Master Replication". Bruce Momjian wrote: > I just saw it [the slides about PGCluster-II]. It does seem more like > Oracle RAC than any other method. Yes. I think it's not production ready, yet, so there's no point in mentioning it in the documentation. Bruce Momjian wrote: > I figured that shared-disk/memory only really makes sense for > multi-master clustering, so I mentioned it in that paragraph: > > ... > > Is that enought? I'd say so, yes. We are not going into more details for other aspects so that's fine. You might not even mention shared-memory. I don't know of any implementation in the database world. Except perhaps using OpenMosix and running PostgreSQL on top of it. Maybe just leave it in there, it won't hurt. Bruce Momjian wrote: > One problem I have is that we we have shared disk failover, but no > other shared case with a PostgreSQL implementation, and people don't > want to mention Oracle RAC, so why do we mention it if we have no > implementations even in the works. Most probably you're already aware that with PGCluster-II we have such an implementation in the works. *** Asynchronous Multi-Master Replication *** >> Again, IMHO, "Parallel Query Execution" says everything. The word >> 'Clustering' does not help, because it's not defined nor commonly >> used in any helpful way (probably besides marketing). > > OK, new title is Multi-Server Parallel Query Execution. If I have > just "Parallel Query Execution", it could be multi-process parallel > query execution. Yes, the new title is good. In the text below, you are mainly describing what I call 'disconnected operation' (somebody have a better, more common term for that?). But the main advantage of async replication is having no delay before commit. Thus giving better performance for writing transactions. In case of async, multi master replication, conflicts can arise, which have to be resolved. I think your example does not make it clear that this applies to async, multi master replication in general. And that those can sometimes be resolved automatically. *** Multi-Master Parallel Query Execution *** Bruce Momjian wrote: > Uh, multi-master replication allows for load balancing, but it doesn't > help a single query to run any faster. Think of having only one query > running on the cluster. Parallel execution allows a single query to > use more than one computer, right? Right. > Uh, this confuses me. What is missing? You split tables across > multiple servers. In "Multi-Master Parallel Query Execution" you write: "One possible way this could work is for the data to be split among servers". So the example you give involves Data Partitioning. I wanted to point out that another way to do Parallel Query Execution is using Multi-Master Replication to have equal replicas and then query them in parallel. I don't think there is any solution for that, yet. Except, perhaps PGPool-II can do it? *** Introduction Text on the top *** Bruce Momjian wrote: > OK, updated to add "little" delay, and removed "small" from async > case: > > load-balanced servers will return consistent
Re: [DOCS] [Sequoia] PostgreSQL Documentation of High Availability and Load
Hello Emmanuel, Emmanuel Cecchet wrote: Even here I think that there is a common misconception between performance and scalability. Most people think that by having multiple nodes their query will run faster which is obviously wrong if your original workload does not saturate a single node. Sure. Do you think that should be made clearer? Yes, I think so because this is a very common belief that we experience with new users. Okay, I have forwarded that to Bruce, who's editing the documentation (and is a native English speaker). I'm not sure how we can cover this, as we are very general in our description. You might want to recheck the paragraph, which is now called "Synchronous Multi-Master Replication": http://momjian.us/main/writings/pgsql/sgml/high-availability.html I'm particularly unsure, where Sequoia would fit in. There is still the split between "Statement-Based Replication Middleware" and "Synchonous Multi-Master Replication". Does Sequoia offer any form of async replication? The replication mechanisms are even adding overhead (usually perceived as increased latency) to the query execution. It is ONLY when the workload increases that you can see throughput going up (ideally somewhat close to the workload increase) and query latency remaining stable. Unless you really have a parallel query execution (that is only efficient for big queries anyway), you will never see a performance improvement on a single query execution since this is always the same database engine that executes the query in the end. I don't quite agree with that statement, but probably I'm just misreading it. If you have enough concurrent transactions you can spread among the nodes, you'll certainly note an improvement. After all, it's a huge difference, if your single node is processing only ten or hundreds of concurrent transactions. Yes, but that already means that your single node was somewhat already a bottleneck. My point was that for low workloads (note that low is relative here since many users have dual-cpu machines with decent RAM and disks, and it takes quite a number of concurrent transactions to get to the peak point), you will not see any improvement and even you'll see a slight degradation especially from a latency perspective. Below the peak point of a single machine, you will get the same performance (from a client point of view) but the load on the various machine resources will decreased by the number of machines in the cluster (at best). For example, if I have a workload of 50 requests/second that generates 50% cpu load on 1 node, I will still get my 50 req/s with 2 machines but the cpu load will only be 25% on each node. Now the contention can be elsewhere (disk, locks, ...) and exhibit other scalability characteristics but it usually conforms to the model I described. Agreed. Of course, the amount of concurrent transactions limits how far a replication solution can scale. Having more nodes than concurrent transactions does not make sense. (Of course with the exception of parallel query execution.) Yes but don't underestimate the capability of a single node to execute transactions in parallel as well. Oftentimes sending 2 concurrent transactions to a single node or to 2 different nodes does not make any difference (obviously it depends on the nature of the transaction). Okay, I just have to believe that. Up until now I'm mostly basing on theoretical estimates, rather than hard facts. :-) You seem to have made some real benchmarks. Did you publish them? To summarize, clustering solutions provide performance scalability (stable latency, throughput increasing almost linearly with load) but not performance improvement on individual query execution time. Yes, for writing transactions, no for read-only ones (queries?). Or why do you have to add overhead to read-only queries? In a middleware approach you have to proxy the read results as well so you will add some latency there. When replication is integrated in the database you can prevent this extra hop but still the replication logic adds some overhead to any query (that seems inevitable if you want to ensure consistency). Ah, okay, yes, the extra hop through the proxy has to be added even to reading queries. To make it work and production ready as soon as possible. ;-) I'm currently working on initialization and recovery. Good luck, this is the hardest part ! You'll soon figure out that replication was really the easy part ! Thanks. May I ask what you use for automatic testing and benchmarking? I'm currently stuck testing 90% of the time. Starting up the GCS, two Databases and attaching the debugger to every process which could possibly go havoc really takes a F***ING lot of time! Regards Markus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [DOCS] Replication Docs
Markus Schiltknecht wrote: > Hello Bruce, > > I was trying to put together all comments to specific sections, thus the > new thread. Hope that helps. > > *** Synchronous Multi-Master Replication *** > > Bruce Momjian wrote: > > OK, new title is "Synchonous Multi-Master Replication", and the next > > heading is "Asynchronous Multi-Master Replication". > > Good, I really like that one. :-) Great (until we change it again) ;-) > >> Why not simply call in "Multi Master Replication"? That implies > >> clustering, doesn't it? > > > > Well, not really because of the async multi-master that is the next > > item. > > Yes, it's fine that way. I was just unsure if you want to have sync and > async in one paragraph or not. The proposal "Multi Master Replication" > would only fit if we'd describe both in one paragraph. I like to > describe both in more detail, as you did now. OK, it is two separate entries now: http://momjian.us/main/writings/pgsql/sgml/high-availability.html > >> BTW, I'm slowly beginning to accept that you don't want to mix > >> "Statement-Based Replication Middleware" with "Multi Master > >> Replication". ;-) > > > > OK, are they mixed now? > > No, they're not. They're split, which I think is what you want. I've > been uncomfortable with was that split into "Statement-Based Replication > Middleware" and "Synchronous Multi-Master Replication". I've been > arguing that the first describes one possible implementation of the > second, while other implementations are not described (2PC, SHMEM, > Postgres-R, etc...) > > I was trying to say that I'm beginning to accept that split, because > especially pgpool really seems to put a lot of those burdens to the > user. I've been trying to use some humor, but that mainly seems to > confuse people. My english might not be good enough for humor, yet. > > However, where do you now fit Sequoia in? It uses "statement-based > replication", but AFAIK it is much more clever than pgpool and handles > non-deterministic functions. And the Sequoia people probably won't get > excited about not calling them "Multi-Master Replication". Uh, good point. The title is now "Statement-Based Replication Middleware". That doesn't say multi-master, but it doesn't say master/slave either. The Sequoia PDF you sent me is very detailed: http://www.continuent.org/uploads/sequoia/Resources/2006-08-15Cecchet_ApacheConAsia2006.pdf I think we are back to the issue of classification. We have traditional master/slave as slony, and multi-master as perhaps pgcluster, and lots in between. I am thinking pgpool and sequoia fit in there. I have added Sequoia to the Statement-Based Replication Middleware section. > Bruce Momjian wrote: > > I just saw it [the slides about PGCluster-II]. It does seem more like > > Oracle RAC than any other method. > > Yes. I think it's not production ready, yet, so there's no point in > mentioning it in the documentation. OK. > Bruce Momjian wrote: > > I figured that shared-disk/memory only really makes sense for > > multi-master clustering, so I mentioned it in that paragraph: > > > > ... > > > > Is that enought? > > I'd say so, yes. We are not going into more details for other aspects so > that's fine. OK. > You might not even mention shared-memory. I don't know of any > implementation in the database world. Except perhaps using OpenMosix and > running PostgreSQL on top of it. Maybe just leave it in there, it won't > hurt. OK, I will only mention shared disk now. > Bruce Momjian wrote: > > One problem I have is that we we have shared disk failover, but no > > other shared case with a PostgreSQL implementation, and people don't > > want to mention Oracle RAC, so why do we mention it if we have no > > implementations even in the works. > > Most probably you're already aware that with PGCluster-II we have such > an implementation in the works. I do now. :-) I think we are OK with the additional sentence about shared disk in the Synchonous Multi-Master Replication section, right? > *** Asynchronous Multi-Master Replication *** > > >> Again, IMHO, "Parallel Query Execution" says everything. The word > >> 'Clustering' does not help, because it's not defined nor commonly > >> used in any helpful way (probably besides marketing). > > > > OK, new title is Multi-Server Parallel Query Execution. If I have > > just "Parallel Query Execution", it could be multi-process parallel > > query execution. > > Yes, the new title is good. > > In the text below, you are mainly describing what I call 'disconnected > operation' (somebody have a better, more common term for that?). But the > main advantage of async replication is having no delay before commit. > Thus giving better performance for writing transactions. > > In case of async, multi master replication, conflicts can arise, which > have to be resolved. I think your example does not make it clear that > this applies to async,
Re: [DOCS] Replication Docs
Hi, Bruce Momjian wrote: OK, it is two separate entries now: http://momjian.us/main/writings/pgsql/sgml/high-availability.html Yes, that's fine with me. Uh, good point. The title is now "Statement-Based Replication Middleware". That doesn't say multi-master, but it doesn't say master/slave either. The Sequoia PDF you sent me is very detailed: http://www.continuent.org/uploads/sequoia/Resources/2006-08-15Cecchet_ApacheConAsia2006.pdf I think we are back to the issue of classification. We have traditional master/slave as slony, and multi-master as perhaps pgcluster, and lots in between. I am thinking pgpool and sequoia fit in there. I have added Sequoia to the Statement-Based Replication Middleware section. I'll look into that shortly, but I think Emmanuel can better categorize sequoia, I've CCed him. I'd certainly categorize it as Multi Master Replication (like pgpool, only that it's a poor implementation). Most probably you're already aware that with PGCluster-II we have such an implementation in the works. I do now. :-) I think we are OK with the additional sentence about shared disk in the Synchonous Multi-Master Replication section, right? Yes. OK, good point, section updated: Asynchronous Multi-Master Replication For servers that are not regularly connected, like laptops or remote servers, keeping data consistent among servers is a challenge. Using asynchronous multi-master replication, each server works independently, and periodically communicates with the other servers to identify conflicting transactions. The conflicts can be resolved by users or conflict resolution rules. rules. Good, that sounds better for me. There's only a typo at the very end: "..conflict resolution rules. rules." Uh, if the data isn't partitioned, what value is there to hitting multiple servers, for single query? I am confused. Right, makes only sense for complex queries, i.e. when having multiple seq scans and/or joins. The executor would have to be super clever for such things to happen. Just forget about my comment. But now, the "little delays" certainly is in the wrong place. Such delays occur before commit, not before returning results. Uh, I don't think the little appears to talk about the results but only the propogation. Maybe revert it back to "..no propagation delay". Or completely leave away the "no propagation delay". OK, how is this new text? This guarantees that a failover will not lose any data and that all load-balanced servers will return consistent results no matter which server is queried. I like that wording better, yes. Regards Markus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] [Sequoia] PostgreSQL Documentation of High Availability
Markus Schiltknecht wrote: > Hello Emmanuel, > > Emmanuel Cecchet wrote: > >>> Even here I think that there is a common misconception between > >>> performance and scalability. Most people think that by having > >>> multiple nodes their query will run faster which is obviously wrong > >>> if your original workload does not saturate a single node. > >> > >> Sure. Do you think that should be made clearer? > > Yes, I think so because this is a very common belief that we experience > > with new users. > > Okay, I have forwarded that to Bruce, who's editing the documentation > (and is a native English speaker). I'm not sure how we can cover this, > as we are very general in our description. OK, updated: Multi-Server Parallel Query Execution Many of the above solutions allow multiple servers to handle multiple queries, but none allow a single query to use multiple servers to complete faster. This solution allows multiple servers to work concurrently on a single query. This is usually accomplished by splitting the data among servers and having each server execute its part of the query and return results to a central server where they are combined and returned to the user. Pgpool-II has this capability. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [DOCS] Replication Docs
Markus Schiltknecht wrote: > Hi, > > Bruce Momjian wrote: > > OK, it is two separate entries now: > > > > http://momjian.us/main/writings/pgsql/sgml/high-availability.html > > Yes, that's fine with me. Good. > > Uh, good point. The title is now "Statement-Based Replication > > Middleware". That doesn't say multi-master, but it doesn't say > > master/slave either. The Sequoia PDF you sent me is very detailed: > > > > > > http://www.continuent.org/uploads/sequoia/Resources/2006-08-15Cecchet_ApacheConAsia2006.pdf > > > > I think we are back to the issue of classification. We have traditional > > master/slave as slony, and multi-master as perhaps pgcluster, and lots > > in between. I am thinking pgpool and sequoia fit in there. I have > > added Sequoia to the Statement-Based Replication Middleware section. > > I'll look into that shortly, but I think Emmanuel can better categorize > sequoia, I've CCed him. I'd certainly categorize it as Multi Master > Replication (like pgpool, only that it's a poor implementation). OK, let's see what they say. Right now, middleware is a separate section. > Good, that sounds better for me. > > There's only a typo at the very end: > > "..conflict resolution rules. rules." OK, fixed, thanks. > > Uh, if the data isn't partitioned, what value is there to hitting > > multiple servers, for single query? I am confused. > > Right, makes only sense for complex queries, i.e. when having multiple > seq scans and/or joins. The executor would have to be super clever for > such things to happen. Just forget about my comment. Oh, I see, splitting I/O load even with multiple copies --- interesting, but seems too far out for this documentation, as you suggested above. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] bytea vs standard_conforming_strings
Tom Lane wrote: > The discussion of bytea in section 8.4, > http://developer.postgresql.org/pgdocs/postgres/datatype-binary.html > is obsolete because it assumes that standard_conforming_strings is > always OFF. It could be very much simpler and shorter if > standard_conforming_strings were always ON, but that's not reality > either. Anyone have an idea on how to rewrite it in a way that > isn't awkward, incomprehensible, or both? I added two "(assuming standard_conforming_strings is off)" clauses in the bytea docs. Patch attached, sorry for the new wrapping. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/datatype.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v retrieving revision 1.180 diff -c -c -r1.180 datatype.sgml *** doc/src/sgml/datatype.sgml 23 Oct 2006 18:10:30 - 1.180 --- doc/src/sgml/datatype.sgml 23 Nov 2006 04:21:46 - *** *** 1097, ! When entering bytea values, octets of certain values ! must be escaped (but all octet values ! can be escaped) when used as part of a string ! literal in an SQL statement. In general, to ! escape an octet, it is converted into the three-digit octal number ! equivalent of its decimal octet value, and preceded by two ! backslashes. shows the ! characters that must be escaped, and gives the alternate escape ! sequences where applicable. --- 1097,1113 ! When entering bytea values, octets of certain ! values must be escaped (but all octet ! values can be escaped) when used as part ! of a string literal in an SQL statement. In ! general, to escape an octet, it is converted into the three-digit ! octal number equivalent of its decimal octet value, and preceded ! by two backslashes (or one backslash if ! standard_conforming_strings is off). ! shows the characters ! that must be escaped, and gives the alternate escape sequences ! where applicable. *** *** 1168,1191 ! The reason that you have to write so many backslashes, as shown in ! , is that an input string ! written as a string literal must pass through two parse phases in ! the PostgreSQL server. The first ! backslash of each pair is interpreted as an escape character by ! the string-literal parser and is therefore consumed, leaving the ! second backslash of the pair. The remaining backslash is then ! recognized by the bytea input function as starting ! either a three digit octal value or escaping another backslash. ! For example, a string literal passed to the server as ! '\\001' becomes \001 after ! passing through the string-literal parser. The ! \001 is then sent to the bytea ! input function, where it is converted to a single octet with a ! decimal value of 1. Note that the apostrophe character is not ! treated specially by bytea, so it follows the normal ! rules for string literals. (See also .) --- 1170,1194 ! The reason that you have to write so many backslashes, as shown ! in , is that an input ! string written as a string literal must pass through two parse ! phases in the PostgreSQL server. ! The first backslash of each pair is interpreted as an escape ! character by the string-literal parser (assuming ! standard_conforming_strings is off) ! and is therefore consumed, leaving the second backslash of the ! pair. The remaining backslash is then recognized by the ! bytea input function as starting either a three ! digit octal value or escaping another backslash. For example, ! a string literal passed to the server as '\\001' ! becomes \001 after passing through the ! string-literal parser. The \001 is then sent ! to the bytea input function, where it is converted ! to a single octet with a decimal value of 1. Note that the ! apostrophe character is not treated specially by bytea, ! so it follows the normal rules for string literals. (See also ! .) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
