Thread: Scalability Design Questions
What happens when a database needs to scale beyond a single server? Is this something PostgreSQL can do? Further, does a database need to be designed differently if it's going to be used on more than one server? I know there are many issues that might effect a database's ability to scale; but specifically, will a well designed single server database be easily adapted to multiple servers, or will the design have to be adjusted? Example that might make the question more clear: from my perspective, in terms of simplicity it'd be ideal if when a database is deployed on multiple servers, the database responds as if it's simply deployed on a more powerful box. But of course it may be more complicated than that <g>. -- View this message in context: http://www.nabble.com/Scalability-Design-Questions-tf4406693.html#a12571941 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 9/8/07, novnov <novnovice@gmail.com> wrote: > > What happens when a database needs to scale beyond a single server? Is this > something PostgreSQL can do? > > Further, does a database need to be designed differently if it's going to be > used on more than one server? I know there are many issues that might effect > a database's ability to scale; but specifically, will a well designed single > server database be easily adapted to multiple servers, or will the design > have to be adjusted? There are so many ways in which an application may need to scale. What if your data is mostly static, but has 100,000 users an hour or a minute hitting it (think google, news sites). what if you've got to handle 1,000 airline reservations a minute? What if you've got to handle 500 inserts per second, but only have to aggregate the data at the end of every hour and then throw it away? The way you scale depends very much on what you're doing. While the idea of just throwing more machines in a cluster that act as one big machine seems simple, the actual implementation and software to make that happen is not, and it's why Oracle can charge as much as the national budget of a small island nation to implement a large RAC cluster. PostgreSQL can handle a lot of very large jobs, but it really doesn't have anything as complex, sophisticated (and trouble pron I might add) as oracle's RAC. Start cutting checks for $200,000 to a few developers and you might start getting somewhere on it though. :)
Yes, I realize that scaling must be a large and complicated topic. And that PostgresSQL is not as scaleable as Oracle etc. I know after reading your post that Postgres cannot match RAC. I'm still not sure if Postgres can operate a database from more than one box, at all. If it can, how? I mean is there a module for this, or is that capability native to postgres? PGCluster on PGFoundry seems to be more about replication. Searching for 'cluster' gets a lot of results but they're mostly about the cluster that postgresql creates at installation, the 'parent' of regular databases. If I have a database that records 100 trx a second, I'm pretty sure postgres would support splitting off aggregation services to another box. But what if the 'main' database has to keep scaling? Can the 'main' database be supported by multiple boxes? Scott Marlowe-2 wrote: > > On 9/8/07, novnov <novnovice@gmail.com> wrote: >> >> What happens when a database needs to scale beyond a single server? Is >> this >> something PostgreSQL can do? >> >> Further, does a database need to be designed differently if it's going to >> be >> used on more than one server? I know there are many issues that might >> effect >> a database's ability to scale; but specifically, will a well designed >> single >> server database be easily adapted to multiple servers, or will the design >> have to be adjusted? > > > There are so many ways in which an application may need to scale. > What if your data is mostly static, but has 100,000 users an hour or a > minute hitting it (think google, news sites). what if you've got to > handle 1,000 airline reservations a minute? What if you've got to > handle 500 inserts per second, but only have to aggregate the data at > the end of every hour and then throw it away? > > The way you scale depends very much on what you're doing. > > While the idea of just throwing more machines in a cluster that act as > one big machine seems simple, the actual implementation and software > to make that happen is not, and it's why Oracle can charge as much as > the national budget of a small island nation to implement a large RAC > cluster. > > PostgreSQL can handle a lot of very large jobs, but it really doesn't > have anything as complex, sophisticated (and trouble pron I might add) > as oracle's RAC. Start cutting checks for $200,000 to a few > developers and you might start getting somewhere on it though. :) > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > > -- View this message in context: http://www.nabble.com/Scalability-Design-Questions-tf4406693.html#a12572458 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 9/8/07, novnov <novnovice@gmail.com> wrote: > > Yes, I realize that scaling must be a large and complicated topic. And that > PostgresSQL is not as scaleable as Oracle etc. Depends on how we're defining scalability. :) I didn't explicitly say that RAC will outscale postgresql. It's just one approach. another approach is getting a bigger and faster server. It may well be that you can buy enough hardware for pgsql to outrun a similarly priced RAC setup. Counting how much oracle charges per machine in a RAC cluster, it's not unreasonable to think so. RAC isn't infused with magic pixie dust, there's a point at which adding machines to a RAC cluster will no longer make the cluster faster. The SAN required for a RAC cluster is not cheap. BTW, I assume you're talking about a transactional db, i.e. banking type transactions. > I know after reading your post that Postgres cannot match RAC. I'm still not > sure if Postgres can operate a database from more than one box, at all. There's been some work going on that I've seen mentioned to give PostgreSQL a RAC like capability, but I don't think we'll see it real soon. for now, postgresql does single master multuple slave asynchronous replication quite well. multi-master synchronous, which is what RAC basically does is not available. It it quite possible that as hardware and pgsql get faster that all the scaling you'll need can be provided by those two things. How many transactions per second are you planning on? Can you partition your data to multiple servers? > PGCluster on PGFoundry seems to be more about replication. I'm not all that familiar with pgcluster.
Yes the trx would be akin to banking operations. This is good: "Can you partition your data to multiple servers?" That's kind of my question! <g> I've not dealt with RAC, data partitioning, any of those topics; just haven't had exposure. But since you're asking me if the data can be partitioned to different servers, it must mean that at least under some cirucumstances, postgres can do this. If you or anyone can tell me more about that or point me to some resources, it'd be great. Thanks for the info Scott Marlowe-2 wrote: > > On 9/8/07, novnov <novnovice@gmail.com> wrote: >> >> Yes, I realize that scaling must be a large and complicated topic. And >> that >> PostgresSQL is not as scaleable as Oracle etc. > > Depends on how we're defining scalability. :) > > I didn't explicitly say that RAC will outscale postgresql. It's just > one approach. another approach is getting a bigger and faster server. > It may well be that you can buy enough hardware for pgsql to outrun a > similarly priced RAC setup. Counting how much oracle charges per > machine in a RAC cluster, it's not unreasonable to think so. > > RAC isn't infused with magic pixie dust, there's a point at which > adding machines to a RAC cluster will no longer make the cluster > faster. The SAN required for a RAC cluster is not cheap. > > BTW, I assume you're talking about a transactional db, i.e. banking > type transactions. > >> I know after reading your post that Postgres cannot match RAC. I'm still >> not >> sure if Postgres can operate a database from more than one box, at all. > > There's been some work going on that I've seen mentioned to give > PostgreSQL a RAC like capability, but I don't think we'll see it real > soon. for now, postgresql does single master multuple slave > asynchronous replication quite well. multi-master synchronous, which > is what RAC basically does is not available. > > It it quite possible that as hardware and pgsql get faster that all > the scaling you'll need can be provided by those two things. > > How many transactions per second are you planning on? Can you > partition your data to multiple servers? > >> PGCluster on PGFoundry seems to be more about replication. > > I'm not all that familiar with pgcluster. > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > > -- View this message in context: http://www.nabble.com/Scalability-Design-Questions-tf4406693.html#a12573403 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 9/8/07, novnov <novnovice@gmail.com> wrote: > > Yes the trx would be akin to banking operations. > > This is good: "Can you partition your data to multiple servers?" That's kind > of my question! <g> I've not dealt with RAC, data partitioning, any of those > topics; just haven't had exposure. But since you're asking me if the data > can be partitioned to different servers, it must mean that at least under > some cirucumstances, postgres can do this. Not sure you understood my question. I'm talking about partitioning your database into multiple unrelated parts. I.e. people with last names starting with a-m on one and n-z on the other, that kind of thing. The partitioning would be part of your app, and your app would hit the right pgsql machine. Again, it has a LOT to do with what exactly your trying to do. Solutions don't present themselves until you define the problem you're trying to solve. And the way to solve it in postgresql might be different than how you'd do it with a different database.
You're right, that's not how I understood your partitioning question. I'd not be eager to implement that kind of split in the db, though I can see that it's an example of how to spread the load around. But basically, it seems that the answer to one of my questions is that there is currently no way with postgres to spread a single database over multiple servers, ala a loadbalanced apache cluster, where requests are forwarded to different boxes. It's not going to be an issue for me soon, maybe ever, I just wanted to see what the score is. Scott Marlowe-2 wrote: > > On 9/8/07, novnov <novnovice@gmail.com> wrote: >> >> Yes the trx would be akin to banking operations. >> >> This is good: "Can you partition your data to multiple servers?" That's >> kind >> of my question! <g> I've not dealt with RAC, data partitioning, any of >> those >> topics; just haven't had exposure. But since you're asking me if the data >> can be partitioned to different servers, it must mean that at least under >> some cirucumstances, postgres can do this. > > Not sure you understood my question. I'm talking about partitioning > your database into multiple unrelated parts. I.e. people with last > names starting with a-m on one and n-z on the other, that kind of > thing. The partitioning would be part of your app, and your app would > hit the right pgsql machine. > > Again, it has a LOT to do with what exactly your trying to do. > Solutions don't present themselves until you define the problem you're > trying to solve. And the way to solve it in postgresql might be > different than how you'd do it with a different database. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > > -- View this message in context: http://www.nabble.com/Scalability-Design-Questions-tf4406693.html#a12575030 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 9/8/07, novnov <novnovice@gmail.com> wrote: > But basically, it seems that the answer to one of my questions is that there > is currently no way with postgres to spread a single database over multiple > servers, ala a loadbalanced apache cluster, where requests are forwarded to > different boxes. Actually, that's essentially the same thing. Whether it's the front end or middleware, something splits the requests apart before they're processed. The asynchronous replication to readonly slaves Scott mentioned earlier would be roughly equivalent to having several identical apache boxes that have their own local copies of files that you periodically rsync/ftp/whatever to them from a single place. Partitioning data would be roughly equivalent to having one apache box for images, one for ads, etc. From what I've seen people mention of RAC, it provides strong guarantees about server consistency -- all of them have the changes or none of them do -- but you need to go to great effort to achieve the same thing on a set of apache boxes too. I mean, you don't have each box accepting file uploads via the web and assume the others will magically see the same file at exactly the same time, right? Unless, of course, you're using them purely for CPU reasons and have a single shared storage pool. Whatever is splitting the requests may do it on a "session" level too, which makes it easier for the backend clusters. E.g. if a given user always hits a given apache box, that file upload situation isn't a problem as long as you can rsync faster than the sessions time out. Often you need to load balance this way anyway if you have a web app using an internal notion of sessions -- session data isn't replicated to other apache boxes. (If you need it to be replicated, you're already in special design territory, not just easy load balancing.) It all varies depending on the details of what you're doing. Even that seemingly straightforward question isn't specific enough :(
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 09/08/07 19:58, novnov wrote: > You're right, that's not how I understood your partitioning question. I'd not > be eager to implement that kind of split in the db, though I can see that > it's an example of how to spread the load around. > > But basically, it seems that the answer to one of my questions is that there > is currently no way with postgres to spread a single database over multiple > servers, ala a loadbalanced apache cluster, where requests are forwarded to > different boxes. > > It's not going to be an issue for me soon, maybe ever, I just wanted to see > what the score is. Scott is correct both in (a) that the money otherwise spent on RAC can buy a big heap of extra oomph, and (b) "middleware partitioning" method of scaling systems works very well. Any way you go, though, requires proper design and coding from the very beginning. And it never hurts to think outside the box: sometimes you can get the results you want by doing something that seems crazy. For example, we had a couple of tables that were specifically designed for OLTP. A specific weekly report that joined the 2 tables just would *never* finish. Instead of fighting to create some hairy- looking SQL-statement-from-Hell, I create WHERE-less views on the two tables with only the columns that were needed. Now we have a job that unloads the views, truncates then loads them into a *separate* database (so that backups & CREATE INDEX don't clash), indexes them perfectly for this query, and then runs the query against this separate database. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFG42yaS9HxQb37XmcRAg+VAJ48E7pERfJ2MpRj2j9N4u1Wh01wJQCfUwnN h1pHoRtnUOLrJkWOtg0Fs4A= =7meG -----END PGP SIGNATURE-----
OK, this has been very informative and I'd like to thank the three of you. Asynchronous replication to readonly slaves is something I will look into. I've never touched posgtres replication; and Scott mentioned that he was not familiar with PGCluster, so there must be some other replication system he's referencing, maybe Slony-I? Trevor Talbot-2 wrote: > > On 9/8/07, novnov <novnovice@gmail.com> wrote: > >> But basically, it seems that the answer to one of my questions is that >> there >> is currently no way with postgres to spread a single database over >> multiple >> servers, ala a loadbalanced apache cluster, where requests are forwarded >> to >> different boxes. > > Actually, that's essentially the same thing. Whether it's the front > end or middleware, something splits the requests apart before they're > processed. > > The asynchronous replication to readonly slaves Scott mentioned > earlier would be roughly equivalent to having several identical apache > boxes that have their own local copies of files that you periodically > rsync/ftp/whatever to them from a single place. Partitioning data > would be roughly equivalent to having one apache box for images, one > for ads, etc. > > From what I've seen people mention of RAC, it provides strong > guarantees about server consistency -- all of them have the changes or > none of them do -- but you need to go to great effort to achieve the > same thing on a set of apache boxes too. I mean, you don't have each > box accepting file uploads via the web and assume the others will > magically see the same file at exactly the same time, right? Unless, > of course, you're using them purely for CPU reasons and have a single > shared storage pool. > > Whatever is splitting the requests may do it on a "session" level too, > which makes it easier for the backend clusters. E.g. if a given user > always hits a given apache box, that file upload situation isn't a > problem as long as you can rsync faster than the sessions time out. > Often you need to load balance this way anyway if you have a web app > using an internal notion of sessions -- session data isn't replicated > to other apache boxes. (If you need it to be replicated, you're > already in special design territory, not just easy load balancing.) > > It all varies depending on the details of what you're doing. Even > that seemingly straightforward question isn't specific enough :( > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > -- View this message in context: http://www.nabble.com/Scalability-Design-Questions-tf4406693.html#a12580273 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hello
We plProxy to split our database into partitions. See Kristo's blog's about that at http://kaiv.wordpress.com/.
For replication we use Londiste in SkyTools package. SkyTools contains several more scripts that are useful when buildin large and complex systems running on large number of servers.
Asko
We plProxy to split our database into partitions. See Kristo's blog's about that at http://kaiv.wordpress.com/.
For replication we use Londiste in SkyTools package. SkyTools contains several more scripts that are useful when buildin large and complex systems running on large number of servers.
Asko
On 9/9/07, novnov <novnovice@gmail.com> wrote:
OK, this has been very informative and I'd like to thank the three of you.
Asynchronous replication to readonly slaves is something I will look into.
I've never touched posgtres replication; and Scott mentioned that he was not
familiar with PGCluster, so there must be some other replication system he's
referencing, maybe Slony-I?
Trevor Talbot-2 wrote:
>
> On 9/8/07, novnov < novnovice@gmail.com> wrote:
>
>> But basically, it seems that the answer to one of my questions is that
>> there
>> is currently no way with postgres to spread a single database over
>> multiple
>> servers, ala a loadbalanced apache cluster, where requests are forwarded
>> to
>> different boxes.
>
> Actually, that's essentially the same thing. Whether it's the front
> end or middleware, something splits the requests apart before they're
> processed.
>
> The asynchronous replication to readonly slaves Scott mentioned
> earlier would be roughly equivalent to having several identical apache
> boxes that have their own local copies of files that you periodically
> rsync/ftp/whatever to them from a single place. Partitioning data
> would be roughly equivalent to having one apache box for images, one
> for ads, etc.
>
> From what I've seen people mention of RAC, it provides strong
> guarantees about server consistency -- all of them have the changes or
> none of them do -- but you need to go to great effort to achieve the
> same thing on a set of apache boxes too. I mean, you don't have each
> box accepting file uploads via the web and assume the others will
> magically see the same file at exactly the same time, right? Unless,
> of course, you're using them purely for CPU reasons and have a single
> shared storage pool.
>
> Whatever is splitting the requests may do it on a "session" level too,
> which makes it easier for the backend clusters. E.g. if a given user
> always hits a given apache box, that file upload situation isn't a
> problem as long as you can rsync faster than the sessions time out.
> Often you need to load balance this way anyway if you have a web app
> using an internal notion of sessions -- session data isn't replicated
> to other apache boxes. (If you need it to be replicated, you're
> already in special design territory, not just easy load balancing.)
>
> It all varies depending on the details of what you're doing. Even
> that seemingly straightforward question isn't specific enough :(
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
--
View this message in context: http://www.nabble.com/Scalability-Design-Questions-tf4406693.html#a12580273
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Hi, novnov wrote: > OK, this has been very informative and I'd like to thank the three of you. > > Asynchronous replication to readonly slaves is something I will look into. > I've never touched posgtres replication; and Scott mentioned that he was not > familiar with PGCluster, so there must be some other replication system he's > referencing, maybe Slony-I? Not sure if you've found those, but just to make sure: there's the a nice chapter in the official Postgres Documentation about High Availability and Load Balancing [1]. Another starting point might be the advocacy wiki at [2]. Regards Markus [1]: Postgres Documentation, Chapter 24. High Availability and Load Balancing: http://www.postgresql.org/docs/8.2/static/high-availability.html [2]: Postgres Advocacy Wiki, Replication: http://developer.postgresql.org/index.php/Replication%2C_Clustering%2C_and_Connection_Pooling