6 ms·

For all the noise about replication, the reality is that buying a server with a) lots of RAM b) dual power supplies, each going to a separate UPS or power feed

For all the noise about replication, the reality is that buying a server with a) lots of RAM b) dual power supplies, each going to a separate UPS or power feed c) dual or quad CPUs will get you 95% of the way there. If you really care, get an external storage solution and buy two identical servers; then you can switch the storage over to the working machine if the first one fails. So many people I know have sites that do not require fancy-pants replication - if they just shelled out the $20K for a big 128GB RAM system and learned how to do decent backups they would save on hosting charges and have a site that is just as reliable.

tobi 18y ago
Maybe for some use cases but most people on this site are concerned about scaling web sites where scaling up (which is what you suggest) isn't feasible because of the obvious cap to the approach and the fact that you can't even add your own hardware to cloud/virtual hosting which is quickly becoming the norm. In general, every problem that can be solved in software should be solved in software. Postgres can be the second coming of jesus but it's still utterly infeasible for high traffic web applications because of the replication issue. Even the current hacks that add replication will not work because they cannot deal with schema changes without downtime. At Shopify we add an average of 12 columns and 2 tables to the database every month and we had a grand total of 58 minutes of downtime in 2008.
- aditya 18y ago
  Tobi, that's good to know... I'm assuming you're running MySQL then?
  - tobi 18y ago
    We actually launched Shopify beta on Postgres. One of the last things we setup before launch (literally 2 days before) was replication. This was foolish by us but there was a lot on our plates back in the day. We spent the entire weekend setting up every different replication solution for postgres and in the end I just had to make a judgement call and went to mysql. Luckily we used rails which is pretty agnostic and we have a huge unit test coverage so we could quickly get it ported. We converted the data and launched on MySQL and have been very happy with it ever since. In fact this was a good decision for other reasons as well: the MySQL query cache saved us from having to seriously look at caching for a crucial 3-4 months which we could spend on more important things. On a personal note, i like Postgres much better if it weren't for those issues.
    - xzilla 18y ago
      Wow! For less than $5000 and a days work, you could have hired any number of postgres consultants to setup PITR warm standby for you (or Slony if you really thought you needed the read slaves). Instead, you migrated your entire architecture over to another database system. Doesn't sound like a good idea to me, but I have to give you points for chutzpah!
      - tobi 18y ago
        First of all, at the time 5k was completely unrealistic. I've worked for nearly 20 months without salary on Shopify before launching it and all my last savings went to pay (very low) salaries to my friends and colleagues who agreed to work on the project because they were passionate about it. But even now, Shopify being a multi million dollar company, I still don't think this would have been well invested money. We had Slony setup and PITR is of no use here either. The point is that you cannot upgrade the schema and data in a migration without downtime in a Postgres setup. Mysql simply replicates alter table statements to it's clients and everything stays in sync. Besides, changing architecture took - as i said - a few hours which ends up even at crazy hourly rates to be no more than 1k so even by direct comparison we saved money. I know that you mean well with your suggestion but it's the same thing i've been hearing from a lot of Postgres supporters, they always argue that we did something wrong.
        xzilla 18y ago
        Yikes... didn't mean to make you so defensive! Let me retort and clarify (hopefully with understanding that this is not meant as criticism) :-) First, I know people doing rails+slony; I think the most common way is by piping the sql to a file and then to slonik, but there are other methods too. Granted, it's not awesome. For a new rails/postgres shop, I'd look at pgpool (provided you really needed replication, which is dubious for a lot of people) Second, this wasn't really about postgres/mysql. If you had told me you went from MySQL->Oracle 2 days before launch, I'd have concerns. Heck, even going from Postgres 8.2 -> 8.3, I'd want to make sure you had good test plans (which it sounds like you did). As a general rule, swapping out your database infrastructure is not something I recommend people take lightly. Yes, rails shops have already decided that the application code is more important than the data, so it's a lot easier to do, but given subtle differences in SQL implementations, people still get bitten by it. Please note, I never said you did anything wrong. You did something risky. Most people don't succeed with risky (which is why it doesn't sound like a good idea to me), but since you did I have to give you credit (again) for making a ballsy call and pulling it off. But I think even you realize that you probably would do things different if you had to do it over again.
- moe 18y ago
  but it's still utterly infeasible for high traffic web applications because of the replication issue Sorry, but what a nonsense. High traffic sites scale by caching, sharding and non-relational databases, in that order. Replication can, at best, be an intermediate kludge for read-heavy websites that haven't learned about caching (wink) or insist on abusing their RDBMS as a fulltext search engine. It was also commonly used as an incremental backup solution until filesystem snapshots became commonplace.
  - aditya 18y ago
    I think having multiple read slaves (and even write masters) is a perfectly acceptable way of distributing load, and in turn, scaling. To completely ignore that sounds foolish. EDIT: xzilla is right, I wasn't really recommending blindly adding write masters since that wouldn't work, but just that read slaves are not a bad idea. :)
    - xzilla 18y ago
      Anyone who thinks you can scale writes by adding more masters is plain ignorant on the subject. The only way to scale writes is with better hardware or federation of application/data. The first of those is easy, the latter is hard.
    - moe 18y ago
      Well, I have never seen a read-slave setup in a webapp for the purpose of scalability that would've made a lot of sense. It's a sometimes a stopgap measure when money is cheaper than time, but will come back to haunt you later. When your reads are starving in a webapp then you should look into fragment caching and content-generation but not much further. If that doesn't solve your problem then you're just very likely doing something fundamentally wrong and better go ask someone smarter.
  - tobi 18y ago
    Replication has nothing to do with scalability in most cases. Replication is how you reduce downtime! It's the same reason why Raid5 is more then useless in a web server - if a disk dies it has to reboot and has to reconstruct data for hours. You cannot have downtime in a web application. This is of course slightly domain specific. We host several thousand e-commerce stores that are the livelihood of our clients. If we are down then no one can earn money. Shopify being down is a lot worse then twitter being down. In fact we may even see legal action if our downtime is too bad. The reason why you need replication is because you need 3+ 100% accurate and up to the second copies of your database which can take over at a moments notice. One single server, no matter how beefy it is, can never accomplish this. Someone is going to trip over it's power cords eventually ( and if it has two then someone is going to trip over the power cord of the network switch it's connected to ).
- Andys 18y ago
  When I was using pgpool2, it dealt with schema changes seamlessly - you connect to the proxy and issue your table updates, and it all just works.
RobGR 18y ago
I think if you compare the cost of getting a dual power supply server, to the cost of getting two servers, you will conclude that buying two servers gives you more reliability per dollar. I have come to the same conclusion even with regards to RAID. After using linux software RAID and occansionally the expensive 3Ware cards for years, I have decided that the cost and complecation of the RAID system inself also needs to be accounted for. Sometimes there is downtime directly attributable to the RAID scheme. What I would like to be able to do, is set up two linux servers, such that any change to the file system on one would be writen to the other. This can be done with inotify and rsync, but I suspect that it would be hard to configure rsync to both have a low enough load on a heavily used database machine and also "keep up" enough that the slave system has a consistent snapshot. It would be nice if there were some block-level way of doing this, so that I could have the slave system just be running the OS without the DB server started, and on the master every change that happened to the database data area would be quickly synced on a block level with the slave. This would remove the database software from the replicaton issue, replication would be handled on a filesystem or directory level, and the database would just have to be configured to sync to disk within a reasonable amount of time.
- wmf 18y ago
  I think if you compare the cost of getting a dual power supply server, to the cost of getting two servers, you will conclude that buying two servers gives you more reliability per dollar. Really you should compare the TCO, including the sysadmin time to configure replication vs. the time to configure failover. What I would like to be able to do, is set up two linux servers, such that any change to the file system on one would be writen to the other. This can be done with inotify and rsync, but I suspect that it would be hard to configure rsync to both have a low enough load on a heavily used database machine and also "keep up" enough that the slave system has a consistent snapshot. This is called a dual-ported array plus GFS2 plus Heartbeat.
  - RobGR 18y ago
    I have just spent some time reading up on GFS2. It sounds interesting. But it also seems aimed at "SAN" style storage, which is expensive. The acronyms that gaius posted above were also interesting to google, but I don't want to buy storage from a place like EMC or NetApp, I have some drives from Fry's that should be more than sufficient. Here is what I want to do: I will take two consumer grade computers less than a couple years old, and install an extra gigabit ethernet card in each one. I will connect the extra crads with a cross over cable. On the "master" machine, I will run Postgres or MySQL, with the data directory on a separate partition if the replication scheme is at the file system level. On the "slave" machine, I willnot have the database server started up. Slow option with a lot of overhead: when the data dir changes, inotify kicks off an rsync that copies the datadir from the master to the slave. Better option: inotify kicks of something that doesn't have to scan whole data file on each side to find the changed data and move it. On a failure of the master, either I would manually move a cable to make the system start using the slave, or IPs could be shuffled remotely, or one of the failover systems could do it automatically. For my particular application, it would be fine if I had to ssh in and change a DNS entry to make our web servers start using the slave database, and because I would not want the database server on the slave to start up and start writing to a disk that might also be receiving syncs from the master, I would prefer it be manual for now. Aside from an inotify / rsync solution that seems limited and kludgy, does anyone have any tips on how to go about this ?
russss 18y ago
You've got the wrong end of the stick here a bit. Postgres has had a "warm standby" system since version 8.1 which you can use to keep a second server in recovery mode waiting to go live. So as far as HA goes, that's pretty good - we use it at Last.fm and it works well for us. What's lacking is a decent way of setting up read-only slaves like you can with MySQL. That was what was promised with 8.4 and that's what's annoying. At Last.fm we have a large, frequently-changing DB schema which is impractical to replicate in its entirety using the existing Postgres replication mechanisms (Slony, Londiste, etc). The annoying thing is that it appears they backed these changes out of 8.4 because of issues with the synchronous part of the replication patch (which we don't care about). Oh well.
- aditya 18y ago
  So, what do you guys do then? I imagine Last.fm is not running on one gigantic postgres db, are you using db partitioning or something similar?
  - russss 18y ago
    Most of our hardcore data is in non-relational sources (including Hadoop). Our global DB is Postgres - that holds everything which we haven't had a reason to move off it - it's a pair of Sun boxes with 128GB of RAM using Postgres warm standby (in this case the hardware is a lot cheaper than trying to shard it). And we also have a ton of other MySQL and Postgres databases running other services.
- xzilla 18y ago
  Just to clarify, hot standby wasn't backed out, because the changes were never "in". There was a working patch set against the 8.4 tree (which you could still get today) for hot standby, and the testing/review of that code ran into enough corner cases that the reviewers thought it was best to push it. While many of the same people are involved in the synchronous replication piece, the issues in that code didn't really play a part (it had pretty much been decided at least month ago that synchronous replication wouldn't make it)

tobi 18y ago

Maybe for some use cases but most people on this site are concerned about scaling web sites where scaling up (which is what you suggest) isn't feasible because of the obvious cap to the approach and the fact that you can't even add your own hardware to cloud/virtual hosting which is quickly becoming the norm. In general, every problem that can be solved in software should be solved in software. Postgres can be the second coming of jesus but it's still utterly infeasible for high traffic web applications because of the replication issue. Even the current hacks that add replication will not work because they cannot deal with schema changes without downtime. At Shopify we add an average of 12 columns and 2 tables to the database every month and we had a grand total of 58 minutes of downtime in 2008.

aditya 18y ago
Tobi, that's good to know... I'm assuming you're running MySQL then?
- tobi 18y ago
  We actually launched Shopify beta on Postgres. One of the last things we setup before launch (literally 2 days before) was replication. This was foolish by us but there was a lot on our plates back in the day. We spent the entire weekend setting up every different replication solution for postgres and in the end I just had to make a judgement call and went to mysql. Luckily we used rails which is pretty agnostic and we have a huge unit test coverage so we could quickly get it ported. We converted the data and launched on MySQL and have been very happy with it ever since. In fact this was a good decision for other reasons as well: the MySQL query cache saved us from having to seriously look at caching for a crucial 3-4 months which we could spend on more important things. On a personal note, i like Postgres much better if it weren't for those issues.
  - xzilla 18y ago
    Wow! For less than $5000 and a days work, you could have hired any number of postgres consultants to setup PITR warm standby for you (or Slony if you really thought you needed the read slaves). Instead, you migrated your entire architecture over to another database system. Doesn't sound like a good idea to me, but I have to give you points for chutzpah!
    - tobi 18y ago
      First of all, at the time 5k was completely unrealistic. I've worked for nearly 20 months without salary on Shopify before launching it and all my last savings went to pay (very low) salaries to my friends and colleagues who agreed to work on the project because they were passionate about it. But even now, Shopify being a multi million dollar company, I still don't think this would have been well invested money. We had Slony setup and PITR is of no use here either. The point is that you cannot upgrade the schema and data in a migration without downtime in a Postgres setup. Mysql simply replicates alter table statements to it's clients and everything stays in sync. Besides, changing architecture took - as i said - a few hours which ends up even at crazy hourly rates to be no more than 1k so even by direct comparison we saved money. I know that you mean well with your suggestion but it's the same thing i've been hearing from a lot of Postgres supporters, they always argue that we did something wrong.
      - xzilla 18y ago
        Yikes... didn't mean to make you so defensive! Let me retort and clarify (hopefully with understanding that this is not meant as criticism) :-) First, I know people doing rails+slony; I think the most common way is by piping the sql to a file and then to slonik, but there are other methods too. Granted, it's not awesome. For a new rails/postgres shop, I'd look at pgpool (provided you really needed replication, which is dubious for a lot of people) Second, this wasn't really about postgres/mysql. If you had told me you went from MySQL->Oracle 2 days before launch, I'd have concerns. Heck, even going from Postgres 8.2 -> 8.3, I'd want to make sure you had good test plans (which it sounds like you did). As a general rule, swapping out your database infrastructure is not something I recommend people take lightly. Yes, rails shops have already decided that the application code is more important than the data, so it's a lot easier to do, but given subtle differences in SQL implementations, people still get bitten by it. Please note, I never said you did anything wrong. You did something risky. Most people don't succeed with risky (which is why it doesn't sound like a good idea to me), but since you did I have to give you credit (again) for making a ballsy call and pulling it off. But I think even you realize that you probably would do things different if you had to do it over again.
moe 18y ago
but it's still utterly infeasible for high traffic web applications because of the replication issue Sorry, but what a nonsense. High traffic sites scale by caching, sharding and non-relational databases, in that order. Replication can, at best, be an intermediate kludge for read-heavy websites that haven't learned about caching (wink) or insist on abusing their RDBMS as a fulltext search engine. It was also commonly used as an incremental backup solution until filesystem snapshots became commonplace.
- aditya 18y ago
  I think having multiple read slaves (and even write masters) is a perfectly acceptable way of distributing load, and in turn, scaling. To completely ignore that sounds foolish. EDIT: xzilla is right, I wasn't really recommending blindly adding write masters since that wouldn't work, but just that read slaves are not a bad idea. :)
  - xzilla 18y ago
    Anyone who thinks you can scale writes by adding more masters is plain ignorant on the subject. The only way to scale writes is with better hardware or federation of application/data. The first of those is easy, the latter is hard.
  - moe 18y ago
    Well, I have never seen a read-slave setup in a webapp for the purpose of scalability that would've made a lot of sense. It's a sometimes a stopgap measure when money is cheaper than time, but will come back to haunt you later. When your reads are starving in a webapp then you should look into fragment caching and content-generation but not much further. If that doesn't solve your problem then you're just very likely doing something fundamentally wrong and better go ask someone smarter.
- tobi 18y ago
  Replication has nothing to do with scalability in most cases. Replication is how you reduce downtime! It's the same reason why Raid5 is more then useless in a web server - if a disk dies it has to reboot and has to reconstruct data for hours. You cannot have downtime in a web application. This is of course slightly domain specific. We host several thousand e-commerce stores that are the livelihood of our clients. If we are down then no one can earn money. Shopify being down is a lot worse then twitter being down. In fact we may even see legal action if our downtime is too bad. The reason why you need replication is because you need 3+ 100% accurate and up to the second copies of your database which can take over at a moments notice. One single server, no matter how beefy it is, can never accomplish this. Someone is going to trip over it's power cords eventually ( and if it has two then someone is going to trip over the power cord of the network switch it's connected to ).
Andys 18y ago
When I was using pgpool2, it dealt with schema changes seamlessly - you connect to the proxy and issue your table updates, and it all just works.

RobGR 18y ago

I think if you compare the cost of getting a dual power supply server, to the cost of getting two servers, you will conclude that buying two servers gives you more reliability per dollar. I have come to the same conclusion even with regards to RAID. After using linux software RAID and occansionally the expensive 3Ware cards for years, I have decided that the cost and complecation of the RAID system inself also needs to be accounted for. Sometimes there is downtime directly attributable to the RAID scheme. What I would like to be able to do, is set up two linux servers, such that any change to the file system on one would be writen to the other. This can be done with inotify and rsync, but I suspect that it would be hard to configure rsync to both have a low enough load on a heavily used database machine and also "keep up" enough that the slave system has a consistent snapshot. It would be nice if there were some block-level way of doing this, so that I could have the slave system just be running the OS without the DB server started, and on the master every change that happened to the database data area would be quickly synced on a block level with the slave. This would remove the database software from the replicaton issue, replication would be handled on a filesystem or directory level, and the database would just have to be configured to sync to disk within a reasonable amount of time.

wmf 18y ago
I think if you compare the cost of getting a dual power supply server, to the cost of getting two servers, you will conclude that buying two servers gives you more reliability per dollar. Really you should compare the TCO, including the sysadmin time to configure replication vs. the time to configure failover. What I would like to be able to do, is set up two linux servers, such that any change to the file system on one would be writen to the other. This can be done with inotify and rsync, but I suspect that it would be hard to configure rsync to both have a low enough load on a heavily used database machine and also "keep up" enough that the slave system has a consistent snapshot. This is called a dual-ported array plus GFS2 plus Heartbeat.
- RobGR 18y ago
  I have just spent some time reading up on GFS2. It sounds interesting. But it also seems aimed at "SAN" style storage, which is expensive. The acronyms that gaius posted above were also interesting to google, but I don't want to buy storage from a place like EMC or NetApp, I have some drives from Fry's that should be more than sufficient. Here is what I want to do: I will take two consumer grade computers less than a couple years old, and install an extra gigabit ethernet card in each one. I will connect the extra crads with a cross over cable. On the "master" machine, I will run Postgres or MySQL, with the data directory on a separate partition if the replication scheme is at the file system level. On the "slave" machine, I willnot have the database server started up. Slow option with a lot of overhead: when the data dir changes, inotify kicks off an rsync that copies the datadir from the master to the slave. Better option: inotify kicks of something that doesn't have to scan whole data file on each side to find the changed data and move it. On a failure of the master, either I would manually move a cable to make the system start using the slave, or IPs could be shuffled remotely, or one of the failover systems could do it automatically. For my particular application, it would be fine if I had to ssh in and change a DNS entry to make our web servers start using the slave database, and because I would not want the database server on the slave to start up and start writing to a disk that might also be receiving syncs from the master, I would prefer it be manual for now. Aside from an inotify / rsync solution that seems limited and kludgy, does anyone have any tips on how to go about this ?

russss 18y ago

You've got the wrong end of the stick here a bit. Postgres has had a "warm standby" system since version 8.1 which you can use to keep a second server in recovery mode waiting to go live. So as far as HA goes, that's pretty good - we use it at Last.fm and it works well for us. What's lacking is a decent way of setting up read-only slaves like you can with MySQL. That was what was promised with 8.4 and that's what's annoying. At Last.fm we have a large, frequently-changing DB schema which is impractical to replicate in its entirety using the existing Postgres replication mechanisms (Slony, Londiste, etc). The annoying thing is that it appears they backed these changes out of 8.4 because of issues with the synchronous part of the replication patch (which we don't care about). Oh well.

aditya 18y ago
So, what do you guys do then? I imagine Last.fm is not running on one gigantic postgres db, are you using db partitioning or something similar?
- russss 18y ago
  Most of our hardcore data is in non-relational sources (including Hadoop). Our global DB is Postgres - that holds everything which we haven't had a reason to move off it - it's a pair of Sun boxes with 128GB of RAM using Postgres warm standby (in this case the hardware is a lot cheaper than trying to shard it). And we also have a ton of other MySQL and Postgres databases running other services.
xzilla 18y ago
Just to clarify, hot standby wasn't backed out, because the changes were never "in". There was a working patch set against the 8.4 tree (which you could still get today) for hot standby, and the testing/review of that code ran into enough corner cases that the reviewers thought it was best to push it. While many of the same people are involved in the synchronous replication piece, the issues in that code didn't really play a part (it had pretty much been decided at least month ago that synchronous replication wouldn't make it)