5 ms·

So basically one table for each type of node, plus one table for all edges. Neat! How big was the relationships table? How was performance?

withinboredom 4y ago
It was pretty massive. I’m certain that it is probably sharded by now. Database performance wasn’t our bottleneck, we had np-hard problems all over the place which was our primary performance issues. For most customers, they never really had issues, but our larger customers def did. Those problems were amazingly fun to solve.
- prox 4y ago
  How do you shard a thing like this? (Apart from “with great difficulty.”) :)
  - hamandcheese 4y ago
    I was wondering the same thing. But at the same time, it seems like having it all centralized in one place would make it really easy to write tooling to aid in such an endeavor. For example, suppose it was decided that sharding would be by account ID. It would probably be very achievable to have some monitoring for all newly created relationships to check that they have an account ID specified, and break that down by relationship type (and therefor attributable to a team). Dashboards and metrics would be trivial to create and track progress, which would be essential for any large scale engineering effort.
    - prox 4y ago
      Yeah it does seem you need to think ahead of what the future needs would be.
  - withinboredom 4y ago
    This would have happened after I left, so I'm just speculating based on experience. 1. You create a function in your application that maps a customer id to a database server. There is only one at first. 2. You setup replication to another server. 3. You update your application's mapping function to map about half of your ids to the new replica. Use something like md5(customer id) % 2. Using md5 or something else that has a good random distribution is important (sha-x DOES NOT have a good random distribution). 4. You application now will write to the replica, turn off replication, and delete the ids from each server that no longer map to that server. Adding a new server follows approximately the same lines, except you need to rebalance your keys which can be tricky.
    - prox 4y ago
      Thanks for that answer, it seems quite a specialist job. So many moving parts. Why would you need random distribution and not just customers 1 to 100k for example?
      - Keyframe 4y ago
        This depends on access patterns to your data.
      - withinboredom 4y ago
        > Why would you need random distribution and not just customers 1 to 100k for example? To keep the databases from having hotspots. For example, if your average customer churns after 6 months, you'll have your "old customer" database mostly just idling, sucking up $$ that you can't downgrade to a smaller machine because there are still customers on it. Meanwhile, your "new customer" database is on fire, swamped with requests. By using a random distribution, you spread the load out. If you get fancy with some tooling, you can migrate customers between databases, and have a pool of small databases for "dead customers" and a more powerful pool for "live customers" or even have geographical distribution and use the datacenter closest to them. But you really do need some fancy tooling to pull that off, but it is doable.
        4y ago
  - chrisjc 4y ago
    Does this question imply sharding would be more difficult with this sort of table design, compared with other table designs? Obviously sharding is difficult, but what would make it any more difficult with this table design? Picking the sharding keys, avoiding hotspots, rebalancing, failover, joining disparate tables on different shards, etc... these are all complicated issues with far reaching implications, but I just don't understand how this table design would complicate sharding any more than another. I'm not trying to sound condescending and suggest it's a dumb question... genuinely interested.

withinboredom 4y ago

It was pretty massive. I’m certain that it is probably sharded by now. Database performance wasn’t our bottleneck, we had np-hard problems all over the place which was our primary performance issues. For most customers, they never really had issues, but our larger customers def did. Those problems were amazingly fun to solve.

prox 4y ago
How do you shard a thing like this? (Apart from “with great difficulty.”) :)
- hamandcheese 4y ago
  I was wondering the same thing. But at the same time, it seems like having it all centralized in one place would make it really easy to write tooling to aid in such an endeavor. For example, suppose it was decided that sharding would be by account ID. It would probably be very achievable to have some monitoring for all newly created relationships to check that they have an account ID specified, and break that down by relationship type (and therefor attributable to a team). Dashboards and metrics would be trivial to create and track progress, which would be essential for any large scale engineering effort.
  - prox 4y ago
    Yeah it does seem you need to think ahead of what the future needs would be.
- withinboredom 4y ago
  This would have happened after I left, so I'm just speculating based on experience. 1. You create a function in your application that maps a customer id to a database server. There is only one at first. 2. You setup replication to another server. 3. You update your application's mapping function to map about half of your ids to the new replica. Use something like md5(customer id) % 2. Using md5 or something else that has a good random distribution is important (sha-x DOES NOT have a good random distribution). 4. You application now will write to the replica, turn off replication, and delete the ids from each server that no longer map to that server. Adding a new server follows approximately the same lines, except you need to rebalance your keys which can be tricky.
  - prox 4y ago
    Thanks for that answer, it seems quite a specialist job. So many moving parts. Why would you need random distribution and not just customers 1 to 100k for example?
    - Keyframe 4y ago
      This depends on access patterns to your data.
    - withinboredom 4y ago
      > Why would you need random distribution and not just customers 1 to 100k for example? To keep the databases from having hotspots. For example, if your average customer churns after 6 months, you'll have your "old customer" database mostly just idling, sucking up $$ that you can't downgrade to a smaller machine because there are still customers on it. Meanwhile, your "new customer" database is on fire, swamped with requests. By using a random distribution, you spread the load out. If you get fancy with some tooling, you can migrate customers between databases, and have a pool of small databases for "dead customers" and a more powerful pool for "live customers" or even have geographical distribution and use the datacenter closest to them. But you really do need some fancy tooling to pull that off, but it is doable.
      - 4y ago
- chrisjc 4y ago
  Does this question imply sharding would be more difficult with this sort of table design, compared with other table designs? Obviously sharding is difficult, but what would make it any more difficult with this table design? Picking the sharding keys, avoiding hotspots, rebalancing, failover, joining disparate tables on different shards, etc... these are all complicated issues with far reaching implications, but I just don't understand how this table design would complicate sharding any more than another. I'm not trying to sound condescending and suggest it's a dumb question... genuinely interested.