22 ms·

Point #1, there is a world of difference between a reporting database and a transactional database. If you need a reporting database, ship logs and set up a re

by btilly 6y ago

Point #1, there is a world of difference between a reporting database and a transactional database. If you need a reporting database, ship logs and set up a reporting database separate from your transactional one. That solves most of the problem. Point #2, the fact that you've hit performance problems does not mean that you need to distribute. Every real system that I've seen has had order of magnitude performance improvements left when it first looked like it was topping out. Add a caching layer, have better indexes, more targeted queries, appropriate use of window functions, etc. Give those a try before biting the bullet on distributed data. Point #3, here is a good rule of thumb for distributed systems. Whatever scale you hit on a single machine, you can probably gain 1-2 orders of magnitude of performance by switching to a faster language and carefully optimizing. If you switch to distributed, you'll LOSE at least an order of magnitude performance due to the overhead of RPCs, but are able to scale indefinitely afterwards. If you're distributing for reliability, great. But if you're distributing for performance and you have less than 20 machines in your system, either your problem is embarrassingly parallel or you likely aren't achieving a net win. I've seen a lot of people prematurely distribute, run into performance challenges, solve them, then pat themselves on the back for being smart enough to have distributed their code. While failing to recognize that they were addressing a self-inflicted injury.

ajsharp 6y ago
My thoughts exactly on point #1. Nothing in a hot path should take multiple seconds.
- barrkel 6y ago
  The hot path may be a SET inside an UPDATE which uses a join which needs to touch millions of rows. You can break that query apart and run it in application logic: do some fetches, do application-side joins, do lots of little updates. Or you can write a single piece of SQL. The former is a whole lot more code and will run slower but individually each item will be fast. The latter is a lot less code and runs faster overall, but the single individual SQL statement will be slow. No simple rules. It depends on the application. (Yes, there are middle ways. Break up the giant UPDATE using some kind of batching strategy. Long-running update-heavy transactions aren't healthy, particularly for Postgres.)
  - btilly 6y ago
    Every system has ways that it can fall down hard. Here is a fun one for Postgres. Modify your query to be using a stored procedure that creates/drops temporary tables. Watch your database fall over from needing to VACUUM system tables. (This was not a hypothetical disaster. It was the result of trying to use a third-party ETL tool that had been designed for Oracle and didn't understand how temporary tables differ on Postgres.)
    - mulmen 6y ago
      VACUUM system tables? Is that a thing? At $dayjob I use Redshift quite a bit and as far as I know there's never a VACUUM operation on any system tables but maybe I'm not looking closely. Are system tables even "real" tables?
      - Tostino 6y ago
        Yes, that is a thing in Postgres. They are real tables and use the same underlying storage management as the rest of the database.
      - btilly 6y ago
        Yes, system tables are just tables. And every time to create/drop a temporary table you've inserted/deleted a bunch of data from https://www.postgresql.org/docs/12/catalog-pg-attribute.html https://www.postgresql.org/docs/12/catalog-pg-attribute.html. If you do so faster than it can vacuum, you're in for a world of hurt.
        mulmen 6y ago
        Huh. For a long time there was no autovacuum in Redshift and AFAIK this was never a problem for us. I wrote our VACUUM script and it would never pick up system tables. Maybe Redshift does something differently.
      - barrkel 6y ago
        AFAIK most of the Postgres bits of Redshift have been rewritten except for the SQL parser and client protocol.
barrkel 6y ago
What if you ingest 10s of millions of rows a day, and need to conditional updates based on those 10s of millions of rows? Either you're going to do 10s of millions of fetches and a whole lot of code, or you can push some of the work to the database and perform updates that involve joins. Those queries may take minutes to complete; but the SQL will be pretty short. Big queries that fetch millions of rows isn't solely the preserve of reporting. Some applications - business SaaS in particular - has a different user:row-count distribution to consumer businesses; instead of a handful of rows for every consumer, with each consumer doing something simple, a business SaaS may have tens of users who each individually manipulate millions of rows every day.
- btilly 6y ago
  What if you ingest 10s of millions of rows a day, and need to conditional updates based on those 10s of millions of rows? Know your problem, then set things up appropriately. I was addressing someone whose problem looked like a reporting query about a consumer application that was affecting the responsiveness of their consumer application. For that case separate the transactional and reporting database and tune each appropriately. That means, for example, different numbers of connections, different memory per connection, different amounts of temporary table space, and so on. The problem that you're describing is much like the one I'm currently facing, ingesting time series from a busy factory floor, then doing various kinds of analytics. For that case, you can simply use one database, optimize reasonably, tune appropriately for the workload, and set appropriate expectations on responsiveness. In all these cases you don't need a distributed system. Not unless your requirements are a lot harsher than what has been described so far.
- mulmen 6y ago
  My relational experience is largely in the analytics world. Can you give me a (simplified/obfuscated) example of this type of workload? Why do the rows need to be updated together or so frequently? My imagination is failing me but this sounds like an interesting problem.
  - barrkel 6y ago
    Most recently I was chief architect at London-based fintech SaaS company that does reconciliations. Recs, as they're known, are in practice a big diff, or join (in the relational sense), between two sets of tabular data. The clients are financial organizations, and the records are things like trades, positions, transactions of various kinds. Larger organizations perform millions of these a day, and for regulatory and risk reasons need to have controls in place such that they are accounted for in different systems and with different counterparties (trade repositories, brokers and the like). So the system looks like this: it takes in lots of records for one side, lots of records for the other side, and compares the two. This produces 4 classes of output: fully matched data; partially matched data (some fields in the "join", like ISIN, are matches, but others are different - e.g. the price or quantity or fee is incorrect); and unmatched data on the left and right sides. All this data needs to be written into a database and fetchable and queryable and sortable on demand by end users, by any field in the data, or by metadata associated with the records. This in itself is a challenge, since you can't index everything and you especially can't index across joins. But there's more. Unmatched data may get matched by subsequently submitted data, and partially matched data may be updated by refreshed submitted data. Workflow metadata needs to be associated with outstanding items (unmatched, partially matched), and this workflow information has further details, like labels, comments, assigned users and groups, which in turn may be updated manually or automatically, with configurable rule-based automatic workflow classification and assignment. Millions of records a day added, across lots of customers, adding up to billions of rows a day (multiple rows per record, naturally). Maybe 5% of inserted records will get meaningfully updated later, but 5% of a big number is still pretty big. Ideally recs would have 100% match rate and not need any workflow metadata because there would be no problems to assign to anyone, nor label or comment on. But match quality (often a function of customer maturity and sophistication) is variable, and more importantly, input batch skew is very common. That is, maybe an org gets a batch every evening from its counterparty, but submits an up-to-the-minute snapshot of its own data. If the skew is bad, every rec might have 30% unmatched data which will mostly be fully matched after the next batch comes in the next day. This overhang (called 'carry over' internally) needs to be managed; if it gets too stale (timestamps, expiry), if it doesn't in fact get matched off, if it gets refreshed by new data, etc. Oh, and you can't stick everything denormalized into a document database and build nice fat indexes, because the bigger customers are hyper-cautious and don't want data intermingling, and running separate redundant services for every customer is too expensive. It is an interesting problem - or at least it was, but it's not my problem any more!
mulmen 6y ago
#2, so much #2. And also #3. One of my earliest and still one of my favorite career achievements was optimizing a stored procedure in Oracle. Our application called the procedure and depending on the result dropped a character from the input and called the procedure again. This was the highest load on our database. We were negotiating a new license to scale up the Oracle database. Estimates were in the six figure range. I simply reproduced that modification/retry logic inside the procedure. The application always got the desired result on the first call over the network. No application side code change needed at all. IIRC my optimization reduced load by ~an order of magnitude. We no longer needed the additional Oracle capacity. Saved a multiple of my salary in an afternoon, just looking for a hot spot. I think my boss bought me a sandwich. It was a good day.

by btilly 6y ago

ajsharp 6y ago

My thoughts exactly on point #1. Nothing in a hot path should take multiple seconds.

barrkel 6y ago
The hot path may be a SET inside an UPDATE which uses a join which needs to touch millions of rows. You can break that query apart and run it in application logic: do some fetches, do application-side joins, do lots of little updates. Or you can write a single piece of SQL. The former is a whole lot more code and will run slower but individually each item will be fast. The latter is a lot less code and runs faster overall, but the single individual SQL statement will be slow. No simple rules. It depends on the application. (Yes, there are middle ways. Break up the giant UPDATE using some kind of batching strategy. Long-running update-heavy transactions aren't healthy, particularly for Postgres.)
- btilly 6y ago
  Every system has ways that it can fall down hard. Here is a fun one for Postgres. Modify your query to be using a stored procedure that creates/drops temporary tables. Watch your database fall over from needing to VACUUM system tables. (This was not a hypothetical disaster. It was the result of trying to use a third-party ETL tool that had been designed for Oracle and didn't understand how temporary tables differ on Postgres.)
  - mulmen 6y ago
    VACUUM system tables? Is that a thing? At $dayjob I use Redshift quite a bit and as far as I know there's never a VACUUM operation on any system tables but maybe I'm not looking closely. Are system tables even "real" tables?
    - Tostino 6y ago
      Yes, that is a thing in Postgres. They are real tables and use the same underlying storage management as the rest of the database.
    - btilly 6y ago
      Yes, system tables are just tables. And every time to create/drop a temporary table you've inserted/deleted a bunch of data from https://www.postgresql.org/docs/12/catalog-pg-attribute.html https://www.postgresql.org/docs/12/catalog-pg-attribute.html. If you do so faster than it can vacuum, you're in for a world of hurt.
      - mulmen 6y ago
        Huh. For a long time there was no autovacuum in Redshift and AFAIK this was never a problem for us. I wrote our VACUUM script and it would never pick up system tables. Maybe Redshift does something differently.
    - barrkel 6y ago
      AFAIK most of the Postgres bits of Redshift have been rewritten except for the SQL parser and client protocol.

barrkel 6y ago

What if you ingest 10s of millions of rows a day, and need to conditional updates based on those 10s of millions of rows? Either you're going to do 10s of millions of fetches and a whole lot of code, or you can push some of the work to the database and perform updates that involve joins. Those queries may take minutes to complete; but the SQL will be pretty short. Big queries that fetch millions of rows isn't solely the preserve of reporting. Some applications - business SaaS in particular - has a different user:row-count distribution to consumer businesses; instead of a handful of rows for every consumer, with each consumer doing something simple, a business SaaS may have tens of users who each individually manipulate millions of rows every day.

btilly 6y ago
What if you ingest 10s of millions of rows a day, and need to conditional updates based on those 10s of millions of rows? Know your problem, then set things up appropriately. I was addressing someone whose problem looked like a reporting query about a consumer application that was affecting the responsiveness of their consumer application. For that case separate the transactional and reporting database and tune each appropriately. That means, for example, different numbers of connections, different memory per connection, different amounts of temporary table space, and so on. The problem that you're describing is much like the one I'm currently facing, ingesting time series from a busy factory floor, then doing various kinds of analytics. For that case, you can simply use one database, optimize reasonably, tune appropriately for the workload, and set appropriate expectations on responsiveness. In all these cases you don't need a distributed system. Not unless your requirements are a lot harsher than what has been described so far.
mulmen 6y ago
My relational experience is largely in the analytics world. Can you give me a (simplified/obfuscated) example of this type of workload? Why do the rows need to be updated together or so frequently? My imagination is failing me but this sounds like an interesting problem.
- barrkel 6y ago
  Most recently I was chief architect at London-based fintech SaaS company that does reconciliations. Recs, as they're known, are in practice a big diff, or join (in the relational sense), between two sets of tabular data. The clients are financial organizations, and the records are things like trades, positions, transactions of various kinds. Larger organizations perform millions of these a day, and for regulatory and risk reasons need to have controls in place such that they are accounted for in different systems and with different counterparties (trade repositories, brokers and the like). So the system looks like this: it takes in lots of records for one side, lots of records for the other side, and compares the two. This produces 4 classes of output: fully matched data; partially matched data (some fields in the "join", like ISIN, are matches, but others are different - e.g. the price or quantity or fee is incorrect); and unmatched data on the left and right sides. All this data needs to be written into a database and fetchable and queryable and sortable on demand by end users, by any field in the data, or by metadata associated with the records. This in itself is a challenge, since you can't index everything and you especially can't index across joins. But there's more. Unmatched data may get matched by subsequently submitted data, and partially matched data may be updated by refreshed submitted data. Workflow metadata needs to be associated with outstanding items (unmatched, partially matched), and this workflow information has further details, like labels, comments, assigned users and groups, which in turn may be updated manually or automatically, with configurable rule-based automatic workflow classification and assignment. Millions of records a day added, across lots of customers, adding up to billions of rows a day (multiple rows per record, naturally). Maybe 5% of inserted records will get meaningfully updated later, but 5% of a big number is still pretty big. Ideally recs would have 100% match rate and not need any workflow metadata because there would be no problems to assign to anyone, nor label or comment on. But match quality (often a function of customer maturity and sophistication) is variable, and more importantly, input batch skew is very common. That is, maybe an org gets a batch every evening from its counterparty, but submits an up-to-the-minute snapshot of its own data. If the skew is bad, every rec might have 30% unmatched data which will mostly be fully matched after the next batch comes in the next day. This overhang (called 'carry over' internally) needs to be managed; if it gets too stale (timestamps, expiry), if it doesn't in fact get matched off, if it gets refreshed by new data, etc. Oh, and you can't stick everything denormalized into a document database and build nice fat indexes, because the bigger customers are hyper-cautious and don't want data intermingling, and running separate redundant services for every customer is too expensive. It is an interesting problem - or at least it was, but it's not my problem any more!

mulmen 6y ago

#2, so much #2. And also #3. One of my earliest and still one of my favorite career achievements was optimizing a stored procedure in Oracle. Our application called the procedure and depending on the result dropped a character from the input and called the procedure again. This was the highest load on our database. We were negotiating a new license to scale up the Oracle database. Estimates were in the six figure range. I simply reproduced that modification/retry logic inside the procedure. The application always got the desired result on the first call over the network. No application side code change needed at all. IIRC my optimization reduced load by ~an order of magnitude. We no longer needed the additional Oracle capacity. Saved a multiple of my salary in an afternoon, just looking for a hot spot. I think my boss bought me a sandwich. It was a good day.