4 ms·

> To do this, we need to write database queries that aggregate over the entirety of our data. We don’t want to run these queries against our production SQL data

by Roybot 6y ago

> To do this, we need to write database queries that aggregate over the entirety of our data. We don’t want to run these queries against our production SQL database, because they could put an enormous amount of load on it. We don’t want a huge query issued by an internal analyst to be able to bring our production database to a grinding halt What kind of query would you have to write to bring down a production db? What makes a solution like hive much better - I guess its optimized for this?

sradman 6y ago
> What kind of query would you have to write to bring down a production db? Scans and Sorts, seen in a query plan, are relatively expensive to run in a production row store. OLAP queries (GROUP BY with aggregate functions like COUNT, SUM, and AVG) do large^/full table scans by definition. They take seconds to run while your goal in a Cloud OLTP system is thousands of requests per second. An automatic sort issued per query in an OLTP system is pathological and represents a vector for a DoS attack. > What makes a solution like hive much better - I guess its optimized for this? Column stores use compressed bitmap indexes that are optimized for scans over a small number of columns. Hive is SQL over Hadoop, and is inherently slow but it does offload the processing from your Production OLTP server. Hive supports the RCFile format which is partially column oriented. The ORC file format is fully column oriented, replaces RCFile format, but requires Presto (or equivalent). Hive is brownfield for existing Hadoop clusters but it has no place in a discussion about greenfield architecture other than discussing historical systems. If you have a need for GROUP BY style analytics, a true column store like Presto, Impala, or RedShift is a necessity. ^EDIT: based on zbentley's comment
- zbentley 6y ago
  > OLAP queries (GROUP BY with aggregate functions like COUNT, SUM, and AVG) do full table scans by definition Isn't it only a full table scan if your query isn't otherwise filtered? Those functions have to read every row of "something", but that something might not always be a whole table.
  - sradman 6y ago
    Very true, I've edited my comment. Some GROUP BY queries are inexpensive and are fine in an OLTP system if the WHERE clause restriction limits the result set size, most however, are meant to scan a large number of rows.
    - Roybot 6y ago
      Thank you - this was insightful.
- jordic 6y ago
  We started evaluating clickhouse, after saying that sentry it's using it

by Roybot 6y ago

sradman 6y ago

> What kind of query would you have to write to bring down a production db? Scans and Sorts, seen in a query plan, are relatively expensive to run in a production row store. OLAP queries (GROUP BY with aggregate functions like COUNT, SUM, and AVG) do large^/full table scans by definition. They take seconds to run while your goal in a Cloud OLTP system is thousands of requests per second. An automatic sort issued per query in an OLTP system is pathological and represents a vector for a DoS attack. > What makes a solution like hive much better - I guess its optimized for this? Column stores use compressed bitmap indexes that are optimized for scans over a small number of columns. Hive is SQL over Hadoop, and is inherently slow but it does offload the processing from your Production OLTP server. Hive supports the RCFile format which is partially column oriented. The ORC file format is fully column oriented, replaces RCFile format, but requires Presto (or equivalent). Hive is brownfield for existing Hadoop clusters but it has no place in a discussion about greenfield architecture other than discussing historical systems. If you have a need for GROUP BY style analytics, a true column store like Presto, Impala, or RedShift is a necessity. ^EDIT: based on zbentley's comment

zbentley 6y ago
> OLAP queries (GROUP BY with aggregate functions like COUNT, SUM, and AVG) do full table scans by definition Isn't it only a full table scan if your query isn't otherwise filtered? Those functions have to read every row of "something", but that something might not always be a whole table.
- sradman 6y ago
  Very true, I've edited my comment. Some GROUP BY queries are inexpensive and are fine in an OLTP system if the WHERE clause restriction limits the result set size, most however, are meant to scan a large number of rows.
  - Roybot 6y ago
    Thank you - this was insightful.
jordic 6y ago
We started evaluating clickhouse, after saying that sentry it's using it