[HACKERS] RFC: Very large scale postgres support
Recently I was tasked with creating a "distribution system" for postgres nodes here at work. This would allow us to simply bring up a new box, push postgres to it, and have a new database. At the same time, we have started to approach the limits of what we can do with postgres on one machine. Our platform presently is the HP DL380. It is a reasonably fast machine, but in order to eke more performance out of postgres, we are going to have to upgrade the hardware substantially. So the subject came up, wouldn't it be nice if, with replication and proxies, we could create postgres clusters? When we need more throughput, to just put a new box in the cluster, dist a psotgres instance to it, and tell the proxy about it. This is a very attractive idea for us, from a scalability standpoint. It means that we don't have to buy $300,000 servers when we max out our 2- or 4- cpu machines (in the past, I would have suggested a Sun V880 for this database, but we are using Linux on x86). We are left with one last option, and that is re-engineering our application to distribute load across several instances of postgres which are operating without any real knowledge of eachother. I worry, though, that as our needs increase further, these application redesigns will become asymptotic. I find myself wondering what other people are doing with postgres that this doesn't seem to have come up. When one searches for postgres clustering on google, they will find lots of HA products. However, nobody seems to be attempting to create very high throughput clusters. I feel that it would be a very good thing if some thinking on this subject was done. In the future, people will hopefully begin using postgres for more intense applications. We are looking at perhaps many tens of billions of transactions per day within the next year or two. To simply buy a "bigger box" each time we outgrow the one we're on is not effective nor efficient. I simply don't believe we're the only ones pushing postgres this hard. I understand there are many applications out there trying to achieve replication. Some of them seem fairly promising. However, it seems to me that if we want to see a true clustered database environment, there would have to be actual native support in the postmaster (inter postmaster communication if you will) for replication and cross-instance locking. This is obviously a complicated problem, and probably not very many of us are doing anything near as large-scale as this. However, I am sure most of us can see the benefit of being able to provide support for these sorts of applications. I've just submitted this RFC in the hopes that we can discuss both the best way to support very large scale databases, as well as how to handle them presently. Thanks again for your time. alex -- [EMAIL PROTECTED] Alex J. Avriette, Solaris Systems Masseur "I ... remain against the death penalty because I feel that eternal boredom with no hope of parole is a much worse punishment than just ending it all mercifully with that quiet needle." - Rachel Mills, NC Libertarian Gubernatorial Candidate ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] RFC: Very large scale postgres support
Alex J. Avriette wrote: I feel that it would be a very good thing if some thinking on this subject was done. In the future, people will hopefully begin using postgres for more intense applications. We are looking at perhaps many tens of billions of transactions per day within the next year or two. tens of billions =10e10 per day? This is probably a typo, because this would mean > 100,000 requests per second? Do you want to feed a monitor with pixel data right from the database, using individual queries for each pixel? Or record each irc user's keyclick in the world concurrently online in a single database? Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] RFC: Very large scale postgres support
On Sun, Feb 08, 2004 at 08:07:14PM +0100, Andreas Pflug wrote: > >I feel that it would be a very good thing if some thinking on this > >subject was done. In the future, people will hopefully begin using > >postgres for more intense applications. We are looking at perhaps many > >tens of billions of transactions per day within the next year or two. > > > > > > tens of billions =10e10 per day? This is probably a typo, because this > would mean > 100,000 requests per second? Do you want to feed a monitor That's what I said, and what I meant. Ten billion transactions equates to 115,740 transactions per second. > with pixel data right from the database, using individual queries for > each pixel? Or record each irc user's keyclick in the world concurrently > online in a single database? Just because you don't think there is a valid use for that sort of traffic doesn't mean there isn't one. Imagine, if you will, a hundred thousand agents making four to five requests a second. Now, imagine these requests are all going to the same database. I'll leave the rest of this exercise up to you. The fact is, there are situations in which such extreme traffic is warranted. My concern is that I am not able to use postgres in such situations because it cannot scale to that level. I feel that it would be possible to reach that level with support in the postmaster for replication. With software load balancing (eg rotors or similar) and updates between postmasters, it would be (it seems to me) possible to drastically increase the available capacity of a database installation through the addition of more nodes. This has the added benefit of allowing us to distribute network resources. Alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator The Emperor Wears No Clothes. http://www.drugsense.org/wodclock.htm ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] RFC: Very large scale postgres support
> The fact is, there are situations in which such extreme traffic is > warranted. My concern is that I am not able to use postgres in such > situations because it cannot scale to that level. I feel that it would > be possible to reach that level with support in the postmaster for > replication. Replication won't help if those are all mostly write transactions. If a small percentage, even 1% would be challenging, is INSERTS, UPDATES or DELETES, master / slave replication might get you somewhere. Otherwise you're going to need to partition the data up into smaller, easily managed sizes -- that of course requires an ability to horizontally partition the data. Anyway, if you want a sane answer we need more information about the data (is it partitionable?), schema type, queries producing the load (simple or complex), acceptable data delays (does a new insert need to be immediately visible?), etc. Dealing with a hundred thousand queries/second isn't just challenging to PostgreSQL, you will be hard pressed to find the hardware that will push that much data around even with the overhead of the database itself. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] RFC: Very large scale postgres support
On Sun, Feb 08, 2004 at 08:01:38PM -0500, Rod Taylor wrote: > Replication won't help if those are all mostly write transactions. If a > small percentage, even 1% would be challenging, is INSERTS, UPDATES or > DELETES, master / slave replication might get you somewhere. There is no way on earth we could be doing writes at that rate. I think that's a given. > Otherwise you're going to need to partition the data up into smaller, > easily managed sizes -- that of course requires an ability to > horizontally partition the data. Obviously, this is the route we have taken. > Anyway, if you want a sane answer we need more information about the > data (is it partitionable?), schema type, queries producing the load > (simple or complex), acceptable data delays (does a new insert need to > be immediately visible?), etc. We've considered a lot of this. Like I said, I think a lot of our need for distributing the database can be helped along with native replication. Am I hearing that nobody believes scalability is a concern? I think many of us would like to see features that would allow large scale installations to be more practical. I also think most of us would agree that the current "graft-on" replication methods are sub-ideal. alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator The Emperor Wears No Clothes. http://www.drugsense.org/wodclock.htm ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] RFC: Very large scale postgres support
On Sun, 2004-02-08 at 21:01, Alex J. Avriette wrote: > On Sun, Feb 08, 2004 at 08:01:38PM -0500, Rod Taylor wrote: > > > Replication won't help if those are all mostly write transactions. If a > > small percentage, even 1% would be challenging, is INSERTS, UPDATES or > > DELETES, master / slave replication might get you somewhere. > > There is no way on earth we could be doing writes at that rate. I think > that's a given. Sure you can, if you can horizontally partition the data so clients A are on machine A, clients B are on machine B, ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] RFC: Very large scale postgres support
> replication. Am I hearing that nobody believes scalability is a > concern? I think many of us would like to see features that would > allow large scale installations to be more practical. I also think most > of us would agree that the current "graft-on" replication methods are > sub-ideal. You really haven't told us which approach to scaling you require. Many are available and PostgreSQL is really only good at a few of them. Anyway, current replication is a PITA mostly due to it's inability to easily start from an empty live database and catch up. You might want to throw a developer at helping SLONY along if you're feel master/slave is the right direction, since presumably you will have several slaves. ---(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] RFC: Very large scale postgres support
On Sun, Feb 08, 2004 at 09:20:07PM -0500, Rod Taylor wrote: > On Sun, 2004-02-08 at 21:01, Alex J. Avriette wrote: > > On Sun, Feb 08, 2004 at 08:01:38PM -0500, Rod Taylor wrote: > > > > > Replication won't help if those are all mostly write transactions. If a > > > small percentage, even 1% would be challenging, is INSERTS, UPDATES or > > > DELETES, master / slave replication might get you somewhere. > > > > There is no way on earth we could be doing writes at that rate. I think > > that's a given. > > Sure you can, if you can horizontally partition the data so clients A > are on machine A, clients B are on machine B, ... I think you were assuming inserts here. The problem actually comes from updates here. The problem is, if I update here, how long before the rest of my "cloud" of postgres nodes understand that record is updated? With an insert, the transaction and propagation are fairly clear. With an update, the overall cost is higher, and the cost per-node is higher. Alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator "You cannot invade the mainland United States. There would be a rifle behind each blade of grass." - Admiral Isoroku Yamamoto ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] RFC: Very large scale postgres support
Alex, > I find myself wondering what other people are doing with postgres that > this doesn't seem to have come up. When one searches for postgres > clustering on google, they will find lots of HA products. However, > nobody seems to be attempting to create very high throughput clusters. Have you checked out "Clusgres" from Linux Labs? One of my clients will be testing this application soon. While we haven't run into transaction throughput limits, a couple of my client's seem to be running in x86's limits on very large queries, particularly on vendor hardware. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] RFC: Very large scale postgres support
> That's what I said, and what I meant. Ten billion transactions equates > to 115,740 transactions per second. Have you tried to look at the scientific comunity? CERN has setups that produce such large amounts of data - try searching google for http://www.google.com/search?q=cern+event+database+postgresql or even http://www.google.com/search?q=cern+event+database You might find some inspiration there! Bye, Chris. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] RFC: Very large scale postgres support
On Mon, 2004-02-09 at 01:54, Alex J. Avriette wrote: > On Sun, Feb 08, 2004 at 09:20:07PM -0500, Rod Taylor wrote: > > On Sun, 2004-02-08 at 21:01, Alex J. Avriette wrote: > > > On Sun, Feb 08, 2004 at 08:01:38PM -0500, Rod Taylor wrote: > > > > > > > Replication won't help if those are all mostly write transactions. If a > > > > small percentage, even 1% would be challenging, is INSERTS, UPDATES or > > > > DELETES, master / slave replication might get you somewhere. > > > > > > There is no way on earth we could be doing writes at that rate. I think > > > that's a given. > > > > Sure you can, if you can horizontally partition the data so clients A > > are on machine A, clients B are on machine B, ... > > I think you were assuming inserts here. The problem actually comes from > updates here. The problem is, if I update here, how long before the No, with the above I'm assuming that you have several completely independent systems. Look, if you're really doing that many select style queries, I presume it is because you simply have too much data or it changes too frequently to present statically as otherwise you wouldn't be using the database for those queries. To me, this implies either every one of your mirrors is going to need some kind of enterprise storage solution, OR you segregate the clients onto different databases so there is not any cross talk or data propagation at all. Anyway, you've yet to tell us anything of substance to even allow guessing at what your solution should be. Simple master/slave mirroring is only one of many options. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] RFC: Very large scale postgres support
Alex, I agree that this is something that is worth spending time on. This resembles the Oracle RAC (Real Application Cluster). While other people may feel that the amount of data is unreasonable I have a similar problem that will only be solved using such a solution. In regards to how your database is designed? Who cares? This is an RFC for a general discussion on how to design this level of functionality into Postgres. Ultimately any solution would work without regard to the insert, updates, or deletes being executed. Alex, I think as a first step we should start coming up with a feature list of what would be necessary to support this level of functionality. From that point we could then identify efforts that are currently ongoing on Postgres development that we could help out on as well as those items that would need to be handled directly. I am very interested in going forth with this discussion and believe that I would be able to have the company I work for put forward resources (i.e. people or money) on developing the solution if we can come up with a workable plan. Josh, thanks for the heads up on Clusgres, I will take a look and see how that fits. Thanks, Keith -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alex J. Avriette Sent: Saturday, February 07, 2004 12:29 PM To: [EMAIL PROTECTED] Subject: [HACKERS] RFC: Very large scale postgres support Recently I was tasked with creating a "distribution system" for postgres nodes here at work. This would allow us to simply bring up a new box, push postgres to it, and have a new database. At the same time, we have started to approach the limits of what we can do with postgres on one machine. Our platform presently is the HP DL380. It is a reasonably fast machine, but in order to eke more performance out of postgres, we are going to have to upgrade the hardware substantially. So the subject came up, wouldn't it be nice if, with replication and proxies, we could create postgres clusters? When we need more throughput, to just put a new box in the cluster, dist a psotgres instance to it, and tell the proxy about it. This is a very attractive idea for us, from a scalability standpoint. It means that we don't have to buy $300,000 servers when we max out our 2- or 4- cpu machines (in the past, I would have suggested a Sun V880 for this database, but we are using Linux on x86). We are left with one last option, and that is re-engineering our application to distribute load across several instances of postgres which are operating without any real knowledge of eachother. I worry, though, that as our needs increase further, these application redesigns will become asymptotic. I find myself wondering what other people are doing with postgres that this doesn't seem to have come up. When one searches for postgres clustering on google, they will find lots of HA products. However, nobody seems to be attempting to create very high throughput clusters. I feel that it would be a very good thing if some thinking on this subject was done. In the future, people will hopefully begin using postgres for more intense applications. We are looking at perhaps many tens of billions of transactions per day within the next year or two. To simply buy a "bigger box" each time we outgrow the one we're on is not effective nor efficient. I simply don't believe we're the only ones pushing postgres this hard. I understand there are many applications out there trying to achieve replication. Some of them seem fairly promising. However, it seems to me that if we want to see a true clustered database environment, there would have to be actual native support in the postmaster (inter postmaster communication if you will) for replication and cross-instance locking. This is obviously a complicated problem, and probably not very many of us are doing anything near as large-scale as this. However, I am sure most of us can see the benefit of being able to provide support for these sorts of applications. I've just submitted this RFC in the hopes that we can discuss both the best way to support very large scale databases, as well as how to handle them presently. Thanks again for your time. alex -- [EMAIL PROTECTED] Alex J. Avriette, Solaris Systems Masseur "I ... remain against the death penalty because I feel that eternal boredom with no hope of parole is a much worse punishment than just ending it all mercifully with that quiet needle." - Rachel Mills, NC Libertarian Gubernatorial Candidate ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] RFC: Very large scale postgres support
Keith Bottner wrote: Alex, I agree that this is something that is worth spending time on. This resembles the Oracle RAC (Real Application Cluster). While other people may feel that the amount of data is unreasonable I have a similar problem that will only be solved using such a solution. In regards to how your database is designed? Who cares? This is an RFC for a general discussion on how to design this level of functionality into Postgres. IMHO a general discussion isn't too helpful, you might be discussing stuff that's never needed for PostgreSQL. Different database systems give different solutions to the same problem, as you might see from e.g. table partition discussions, which where initiated by Oracle-originating people. There still might be weaknesses in pgsql, but to identify them, *real* issues need to be discussed. This is necessary to avoid major hardware/software dbms efforts that might well be replaced by organizational/app level tools. Regards, Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] RFC: Very large scale postgres support
I always enjoy how everyone wants to talk about using different solutions prior to understanding the complete problem. I would say that a *real* issue is any perceived issue whether a current solution exists or not. If current solutions are applicable and would work then great we have all gained; however, if in fact it is necessary to add the additional functionality to Postgres then so be it. But none of this can be decided until the complete problem and hence the requirements are understood. My impression of the Postgres project has always been that of a high end database system that is endeavoring to become a component of critical enterprise systems. If this is not true or as a group we are going to keep placing these scalability issues aside then this will never be achieved and those of us who want Postgres to play a more important role for our corporate systems will have no choice but to go somewhere else. I understand your position Andreas and respect your opinion; maybe what I have identified as requirements is what you are specifying as *real* issues. I hope so, because I to would like to avoid unnecessary dbms efforts. But from what I understand of Alex's problem and more specifically mine, adding another layer at the organizational/app level will not provide the level of functionality that is required. Regards, Keith -Original Message- From: Andreas Pflug [mailto:[EMAIL PROTECTED] Sent: Monday, February 09, 2004 9:54 AM To: Keith Bottner Cc: 'Alex J. Avriette'; [EMAIL PROTECTED] Subject: Re: [HACKERS] RFC: Very large scale postgres support Keith Bottner wrote: >Alex, > >I agree that this is something that is worth spending time on. This >resembles the Oracle RAC (Real Application Cluster). While other people >may feel that the amount of data is unreasonable I have a similar >problem that will only be solved using such a solution. > >In regards to how your database is designed? Who cares? This is an RFC >for a general discussion on how to design this level of functionality >into Postgres. > IMHO a general discussion isn't too helpful, you might be discussing stuff that's never needed for PostgreSQL. Different database systems give different solutions to the same problem, as you might see from e.g. table partition discussions, which where initiated by Oracle-originating people. There still might be weaknesses in pgsql, but to identify them, *real* issues need to be discussed. This is necessary to avoid major hardware/software dbms efforts that might well be replaced by organizational/app level tools. Regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] RFC: Very large scale postgres support
Keith Bottner wrote: I understand your position Andreas and respect your opinion; maybe what I have identified as requirements is what you are specifying as *real* issues. I hope so, because I to would like to avoid unnecessary dbms efforts. You got me very right. I didn't mean to declare high volume databasing as minor issue, I meant real world requirements. Regards, Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] RFC: Very large scale postgres support
Alex, In our quest to see if we can get better performance out of PostgreSQL by throwing more HW at it, I would have recommended a V880 also. I'm curious to find out why you would have: "(in the past, I would have suggested a Sun V880 for this database, but we are using Linux on x86)" too. Cheers, Eddy [EMAIL PROTECTED] ("Keith Bottner") wrote in message news:<[EMAIL PROTECTED]>... > Alex, > > I agree that this is something that is worth spending time on. This > resembles the Oracle RAC (Real Application Cluster). While other people may > feel that the amount of data is unreasonable I have a similar problem that > will only be solved using such a solution. > > In regards to how your database is designed? Who cares? This is an RFC for a > general discussion on how to design this level of functionality into > Postgres. Ultimately any solution would work without regard to the insert, > updates, or deletes being executed. Alex, I think as a first step we should > start coming up with a feature list of what would be necessary to support > this level of functionality. From that point we could then identify efforts > that are currently ongoing on Postgres development that we could help out on > as well as those items that would need to be handled directly. > > I am very interested in going forth with this discussion and believe that I > would be able to have the company I work for put forward resources (i.e. > people or money) on developing the solution if we can come up with a > workable plan. > > Josh, thanks for the heads up on Clusgres, I will take a look and see how > that fits. > > Thanks, > > Keith > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Alex J. Avriette > Sent: Saturday, February 07, 2004 12:29 PM > To: [EMAIL PROTECTED] > Subject: [HACKERS] RFC: Very large scale postgres support > > > Recently I was tasked with creating a "distribution system" for postgres > nodes here at work. This would allow us to simply bring up a new box, push > postgres to it, and have a new database. > > At the same time, we have started to approach the limits of what we can do > with postgres on one machine. Our platform presently is the HP DL380. It is > a reasonably fast machine, but in order to eke more performance out of > postgres, we are going to have to upgrade the hardware substantially. > > So the subject came up, wouldn't it be nice if, with replication and > proxies, we could create postgres clusters? When we need more throughput, to > just put a new box in the cluster, dist a psotgres instance to it, and tell > the proxy about it. This is a very attractive idea for us, from a > scalability standpoint. It means that we don't have to buy $300,000 servers > when we max out our 2- or 4- cpu machines (in the past, I would have > suggested a Sun V880 for this database, but we are using Linux on x86). > > We are left with one last option, and that is re-engineering our application > to distribute load across several instances of postgres which are operating > without any real knowledge of eachother. I worry, though, that as our needs > increase further, these application redesigns will become asymptotic. > > I find myself wondering what other people are doing with postgres that this > doesn't seem to have come up. When one searches for postgres clustering on > google, they will find lots of HA products. However, nobody seems to be > attempting to create very high throughput clusters. > > I feel that it would be a very good thing if some thinking on this subject > was done. In the future, people will hopefully begin using postgres for more > intense applications. We are looking at perhaps many tens of billions of > transactions per day within the next year or two. To simply buy a "bigger > box" each time we outgrow the one we're on is not effective nor efficient. I > simply don't believe we're the only ones pushing postgres this hard. > > I understand there are many applications out there trying to achieve > replication. Some of them seem fairly promising. However, it seems to me > that if we want to see a true clustered database environment, there would > have to be actual native support in the postmaster (inter postmaster > communication if you will) for replication and cross-instance locking. > > This is obviously a complicated problem, and probably not very many of us > are doing anything near as large-scale as this. However, I am sure most of > us can see the benefit of being able to provide support for these sorts of > applications. > > I've just submitted this RFC in the hopes that we can discuss both the best &g