9 ms·

Is there really no simpler way to solve this problem? "Moving a message needs to move all of the message's comments, and all of the comments' files, and all of

• russell 16y ago
  It can be if your data structure is a simple tree. But usually it's a complicated, maybe inconsistent, multi-rooted graph. It got that way because of incremental implementation. As the article illustrated, you then have to tease out the pieces. I once inherited a large enterprise system that had been designed from the ground up to allow that kind of flexibility. The problem was that there were so many levels of abstraction and indirection in the database and the ORM model that performance was abysmal.
• weaksauce 16y ago
  Might be trickier to do this if the foreign key is across a shard or if the db record for the comment wasn't structured like this. One situation could be because they store the threaded comments with the thread key in each record and also the parent comment key. This would be useful to pull down all the comments associated with a thread in one db query but also allow you to have the nested comments.
  • smiler 16y ago
    37signals don't use sharding. They use one database server with enough RAM to load the db into memory
    • jules 16y ago
      One database server for the whole of basecamp? Edit: I found this: > With that in mind, we went looking for an option to host the Basecamp database, which is becoming a monster. As of this writing, the database is 325GB and handles several thousand queries per second at peak times. 325GB RAM?! But now they have multiple servers. Read more: http://37signals.com/svn/posts/2479-nuts-bolts-database-servers http://37signals.com/svn/posts/2479-nuts-bolts-database-serv...
      • die_sekte 16y ago
        HP sells servers with up to 64 cores and 2 TB of memory. Considering the car that DHH just bought himself, they should be able to buy/lease several of them.
        • jules 16y ago
          Are these x86 servers? Or do you mean a single chip with 64 cores?
          • die_sekte 16y ago
            This thing: http://h10010.www1.hp.com/wwpc/us/en/sm/WF04a/15351-15351-3328412-241644-4222584 http://h10010.www1.hp.com/wwpc/us/en/sm/WF04a/15351-15351-33...
          • whatusername 16y ago
            http://www-03.ibm.com/systems/x/hardware/enterprise/x3850x5/features.html http://www-03.ibm.com/systems/x/hardware/enterprise/x3850x5/...
      • aaronblohowiak 16y ago
        You can get servers that can house 512GB or 1TB of RAM from HP/Sun/Dell. Scaling out your app servers and scaling up your DB is common in systems that don't need to be truly "web scale".
    • die_sekte 16y ago
• SMrF 16y ago
  From the response; "We can't use database transactions because performing a big move would slow Basecamp down for everyone. So we have to log the process of each step of the move, and make it so any failure in the move can be rolled back gracefully. That means a move is actually a series of copies and deletions instead of just changing a field for each moved item"
  • xentronium 16y ago
    What's the point of having ACID when you don't use its advantages, one might wonder.
    • sstephenson 16y ago
      So your solution is to reimplement Basecamp on top of another database?
      • deleted 16y ago
        [deleted]
      • smydhacker 16y ago
        THE SOLUTION IS MUNGDB
      • antidaily 16y ago
        Duh
      • prodigal_erik 16y ago
        From a couple of web searches it sounds like they're on MySQL. Targeting a database that has working transactions has to be less work than implementing transactions yourself from scratch, which it seems everyone stuck on MySQL eventually has to do (that's what they're describing, and I know we've done similar things).
        • dabeeeenster 16y ago
          Depends what MySQL table types they are using. I think MyISAM has no transactional ability.
• toddheasley 16y ago
  I also agree with the sentiment that nothing is ever as simple as it seems. But, yeah, isn't this what relational databases are for?
• grandpa 16y ago
  That might - might - solve the first 4 points, but certainly nothing after that.
• amix 16y ago
  The strategy you usually take with scaling a relational database is sharding, partitioning and de-normalization. And given that Basecamp has millions of users I don't imagine their database structure to be pretty or normalized. This said, if they have their database sharded by project_id then it would not be that big an issue, but it can be that their database structure is very complex or messed up...
  • jemfinch 16y ago
    > And given that Basecamp has millions of users I don't imagine their database structure to be pretty or normalized. Millions of users is not really that many. It's certainly within the realm of what can be reasonably vertically scaled.
    • amix 16y ago
      You don't want to scale vertically once you hit millions of users and most other web companies like Google, Facebook, Yahoo and Microsoft have proven that horizontally scaling without big iron is the way to go. Currently the only way to scale a relational database such as MySQL or Postgre is by sharding and partitioning - these things ruin most good relational properties that your database structure may have.
      • jemfinch 16y ago
        > You don't want to scale vertically once you hit millions of users Hundreds of the top websites in the world (the majority, I would hazard, though without the data to support it) have scaled vertically to millions and tens of millions of entities just fine. Far more than have scaled horizontally. It works. It's been done. And it doesn't give up the sort of transactional niceties that make problems like this easier. > most other web companies like Google, Facebook, Yahoo and Microsoft You're confusing "the very biggest web companies" with "most other web companies." Most other web companies continue to use commodity products, and Google/Facebook/Yahoo!/MS certainly would (and do) insofar as it's possible at that scale. Expending resources now to be as horizontally scalable as Google is wasteful premature optimization. Notably, Yahoo! runs the largest PostgreSQL installation in the world, and Google and Facebook both continue to use MySQL. > horizontally scaling without big iron is the way to go. You can get 32-core machines with 128GB of ram from Dell (a mildly tweaked R910) for $30k these days. Is that big iron? How does its price compare with the amount of developer salary and benefits you'll have to spend to grok a non-relational data store, migrate your data to it, and reimplement the ACID features of a relational store in the code for your app? How many developer-days will you spend maintaining that code and how many developer-nights will you spend triaging a crashed site because of the complexity and likely bugginess of that reimplementation? How many users' feature requests will you have to reject as "too difficult to implement" because you feel the need to scale to Google/Facebook levels despite having only a few million users now and predicted growth which shows you'll never in a million years catch up to them? > Currently the only way to scale a relational database such as MySQL or Postgre is by sharding and partitioning It will be years before the vast majority of startups exhaust reasonable, cost-effective options for vertical scaling. The recent fervor for non-relational, horizontally scalable data stores is simply the new way of scratching the intellectually masturbatory premature optimization itch that programmers have had since ENIAC. For what it's worth, I'm not the only crank who thinks this; Dennis Forbes has argued it much more eloquently and compellingly on his blog, e.g. http://blog.yafla.com/Getting_Real_about_NoSQL_and_the_SQL_Performance_Lie/ http://blog.yafla.com/Getting_Real_about_NoSQL_and_the_SQL_P... .
• dasil003 16y ago
  You're falling into the same trap all programmers do when they perpetually underestimate the time to accomplish any task. The main difficulty in estimating time is that you don't know what you don't know, so you have to actually get into the nitty gritty of implementing it, and if you're smart enough you'll hopefully catch all the requirements before you actually launch it. I'm going to take Sam's word on this that there is not a simpler way, because he's the one working on it, and I trust the competence of the 37s dev team. That's not to say that some armchair analysis here on HN might not have valuable insight, but without actually seeing the code, and in light of the full list of points Sam mentioned, I doubt very much that there is a much simpler solution of any form.
  • jemfinch 16y ago
    > I trust the competence of the 37s dev team. I don't, in large part because this shouldn't be a complicated problem to solve.
    • dasil003 16y ago
      Yes, in your armchair analysis that takes into account none of the issues that they've dealt with in getting where they are today, any past architectural decisions that make this particular feature difficult to implement indicate incompetence. Your confidence probably serves you well (let me guess, early 20s?), but in this case you literally don't know what you're talking about.
      • latortuga 16y ago
        Are we really taking cheap shots about age now? This is not how an argument is won and not how we do things around here. Your point stands on its own without ageism.
        • dasil003 16y ago
          Fair point, but this type of hubris is far more common (and more forgivable) in youth. Is it ageist to recognize this fact?
• vog 16y ago
  Also, even if there was a good reason for that strange database scheme, they could have simply specified "ON UPDATE CASCADE" on their foreign references, and the database would have taken care of the nasty details. That's what databases are good at. (Or did their ORM prevent them from doing so?) So this is more an example of "How to make simple tasks difficult" rather than "Even simple tasks have their pitfalls".
• ams6110 16y ago
  Maybe we're seeing a downside of the YAGNI, shoot from the hip, do the simplest thing design philosophy.
• Vitaly 16y ago
  agree. most of the described changes seem to follow from a bad db design. lets see: - Moving a message needs to move all of the message's comments, and all of the comments' files, and all of the comments' files' versions. Not really. comment should only by 'tied' to message, i.e. there should be comments.messages_id command, so moving massage somewhere else shouldn't require moving comments (unless you fucked up the db schema to begin with) - Moving any file needs to move its thumbnail, too, if it's an image. No need to move the file to begin with, see above. - Moving a milestone needs to move any associated to-do list and messages. And of course those to-do lists can have to-do items with comments, and attached files, and multiple versions of those files. This is probably the one that indeed needs special treatment even in reasonable designed db. There are ways to skip this if you anticipate the need at the very beginning but it will require some convoluted db hacks to allow O(1) simple move (i.e. make everything including project just an item and just have parent_id in each item, then have messages to be children of the milestone. but this will have other problems, performance and complexity etc. so I wouldn't go this way) - Moving a to-do list whose to-do items have associated time tracking entries needs to move those time entries to the destination project too. Not really. time tracking entries should not have project_id in it. just todo_id - Moving a message or file needs to re-create its category in the destination project if it doesn't already exist. Indeed. - If a moved file is backed up on S3 we need to rename it there. If it's not, we need to make sure it doesn't get backed up with its old filename. why the file path on S3 has project id in it. attachments.id would suffice to uniquely identify stuff. its not like you need to invent multilevel directories on S3 like you do on a file system. - When someone posts a message to the wrong project, then moves it to the right place, we need to make sure that everyone who received an email notification from the original message can still reply to the message via email. If the email link just has the message_id and not the project_id it solves itself. You might need to play with some ACL though. - Similarly we need to make sure that when you follow a URL in an email notification for a moved message, comment or files, you are redirected to its new location. Dont' include project_id in the url to begin with. Use 'flat' routes. - Since moving one milestone could potentially result in hundreds of database operations, we need to perform the move asynchronously. This means storing information about the move, pushing it into a queue, and processing it with a pool of background workers. Not really. Even moving a milestone should only require 2 transactions. move the milestone, and move all milestone's children. Thats might be a non-trivial UPDATE sql operation but its not hundreds of queries. - We also have to build a new UI for displaying the progress of a move. It needs to poll the Basecamp servers periodically in the background to check to see if the move is done yet, and take you to the right place afterwards. Probably right. - We can't use database transactions because performing a big move would slow Basecamp down for everyone. So we have to log the process of each step of the move, and make it so any failure in the move can be rolled back gracefully. That means a move is actually a series of copies and deletions instead of just changing a field for each moved item. If you eliminate most of the complications above then you can use transactions.