7 ms·

A Bunch of Money on AWS and Some Benchmark Results

38 points by nikita 7y ago

tyingq 7y ago
Might be advisable to note that you don't support foreign keys if you're going to show how much better your performance is versus a database that does.
- scarface74 7y ago
  It’s an OLAP database. Are you really that concerned about FK constraints? The only other OLAP database that I’ve used is Amazon’s Redshift and FKs are for “informational purposes”. Or is it considered a transactional database or an analytics database?
  - AdamProut 7y ago
    MemSQL can run both types of workloads. That is one the main things we're trying to demonstrate by running one of the most popular operational database benchmarks (TPC-C) as well as the two most popular analytics benchmarks (TPC-DS, TPC-H). Our strength is somewhere in the middle[1] [1] https://www.memsql.com/blog/the-need-for-operational-analytics/ https://www.memsql.com/blog/the-need-for-operational-analyti...
    - nikita 7y ago
      Another way to think about MemSQL is that it's an operational database. This means that it can support high velocity transactions and power apps. The fact it can run decision support (OLAP) benchmarks is an important differentiation. Since those benchmarks are SQL benchmarks you can think of it as simple as "it's just great in SQL". Or you can think about it as "if my app has reporting as part of the workloads MemSQL will handle it out of the box". Finally there are many use cases that fall in the category of "Operational Analytics": time series, IoT, real-time dashboarding, moving from batch to streaming, predictive maintenance. All of those require underlying infrastructure to handle transactions and complex SQL in one system.
  - deleted 7y ago
    [deleted]
- AdamProut 7y ago
  (MemSQL CTO here) You're right, MemSQL doesn't support foreign keys as of yet, but none of these benchmarks require foreign key support. Two of them (TPC-H and TPC-DS) are a set of complex SELECT queries where foreign keys are not relevant at all. TPC-C is a write heavy benchmark, but the specification doesn't require foreign keys to be maintained (the data model does indicate the foreign key relationships though)[1]. These are unofficial benchmark results (not independently verified by TPC), so our interpretation of the specs may not be 100% correct, but I think we got it right as far as foreign keys are concerned. [1] http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-c_v5.11.0.pdf http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-c_...
  - deleted 7y ago
    [deleted]
  - tyingq 7y ago
    Just seems like you would want that noted if you're going to compare with CockroachDB and say things like "Our results show that we can do both transaction processing and data warehousing well".
peterwwillis 7y ago
Similar to the phrase "Lies, damned lies, and Benchmarks", should be the phrase "Lies, damned lies, and AWS costs"
- wmf 7y ago
  Because?
  - peterwwillis 7y ago
    Because all a benchmark tells you is the result of one use case. It's not an estimate of what your use will result in. It's basically a sales pitch. And sadly, upper management falls for it all the time, often without an evaluation period or even spitballing it for a week with the teams that would be using it. Tons of factors will change the result for a particular use case, regardless of how "normalized" the test tries to be (especially considering most are designed for high-load high-performance high-scale scenarios, which isn't the average real-world use case; most people just have unoptimized queries, or a shit legacy stack, or they expect to eventually have a big use case and were told to find either the fastest or cheapest solution, which may not even be in AWS to begin with, and so on...). The AWS cost is just another benchmark, so it's just as much a lie.
    - diminoten 7y ago
      The whole point of benchmarks is that their "use case" correlates roughly with every use case related to the technology being benchmarked. Is the correlation 1:1? No. Is it still relevant when making a decision about what technology to use? Absolutely.
      - dragonwriter 7y ago
        > The whole point of benchmarks is that their "use case" correlates roughly with every use case related to the technology being benchmarked. That's the idea of benchmarks, but it's not actually true of real benchmarks, because invariably some factors which can improve a particular benchmarks beaean inverse relation to performance for some other use cases of the technology.
georgewfraser 7y ago
As someone who's spent a lot of time working with TPC-DS [1] and talking to people about it [2], I see a couple areas that could be improved in this benchmark: 1. Total run time is not an appropriate way to summarize the performance across queries, because some queries take 100x longer than others. The appropriate way to summarize this kind of data is to use the geomean [3]. 2. The official TPC-DS queries make heavy use of grouping sets, which are a rarely-used SQL feature. I think TPC-DS is better if you rewrite the queries to eliminate grouping sets. 3. You used the exact same queries to "warm up" the data warehouse, and to test the performance. Some data warehouses (notably Redshift) aggressively cache intermediate compilation results, so they are much faster the second time they see a query or even a fragment of a query. To model a real user submitting queries interactively, you should use warmup queries that are similar to but not the same as the ones you use to measure performance. 4. You can solve the "vendor benchmarking their own product" problem by submitting a PR to our repo [4], which currently tests Redshift, Snowflake, BigQuery, Azure SQL DW, and Presto. We'd be happy to review it and endorse the timing if it meets our standards for fairness! [1] https://fivetran.com/blog/warehouse-benchmark https://fivetran.com/blog/warehouse-benchmark [2] https://www.youtube.com/watch?v=XpaN-PqSczM https://www.youtube.com/watch?v=XpaN-PqSczM [3] https://en.wikipedia.org/wiki/Geometric_mean https://en.wikipedia.org/wiki/Geometric_mean [4] https://github.com/fivetran/benchmark https://github.com/fivetran/benchmark
- sroussey 7y ago
  Curious to see TiDB in there
  - AdamProut 7y ago
    You need columnstore storage and a reasonably mature query optimizer to get good results on TPC-DS. TiDB is lacking both right now.
    - deleted 7y ago
      [deleted]
    - dongxu 7y ago
arjunnarayan 7y ago
This benchmark is pretty ridiculous for the following reasons: 1. Their database is run in asynchronous durability mode. 2. They specifically do the one thing that TPC-C says you shouldn't do, which is get really high throughput on a small dataset. TPC-C enforces that you scale your data-stored with the query throughput. CockroachDB maxes out at ~12.8tpmC/warehouse because its waiting at the legal maximum throughput, as opposed to running up the numbers in a way that's against the rules (and spirit) of the benchmark. 3. They make all the TPC-DS mistakes that georgewfraser points out elsewhere in this thread. 4. They run in read committed mode (they don't support anything higher), CockroachDB runs in serializable mode. I ended up ranting about this on Twitter, so rather than reproducing everything here, I'm going to link to my rant there. Apologies for the cross-posting across fora: https://twitter.com/narayanarjun/status/1128393193941274624 https://twitter.com/narayanarjun/status/1128393193941274624
- dongxu 7y ago
  I agree, running TPC-C on asynchronous durability mode is not reasonable.
- AdamProut 7y ago
  (MemSQL CTO here) 1. MemSQL is running with synchronous replication in all these benchmarks. All data is stored on a 2nd machine before any transaction is acknowledged as committed. You’re right this is not as strong as running with both synchronous writes to disk and over the network. MemSQL supports this as well and results in about a 40 to 50% performance hit depending on the disk speed. Very few of our customers run in this configuration so we didn’t include it (the edge case of multiple machines losing power is not worth the performance hit for them). 2. Can you point me to what you’re describing in the TPC-C specification? I have never heard of what you’re claiming. TPC-C has maximum allowed latency requirements for the 5 transaction types it runs and also requirements around the mix of those transactions in the workload. The goal of the benchmark is still to run as many "New Order" transaction per minute while maintaining the latency requirements of the other unmeasured transactions running in the background (this is what tpmC stands for). We used the Percona TPC-C driver for MySQL to handle this (with a few small bug fixes). 3. The main thing we wanted to show is that our performance on TPC-DS is similar (better at some scale factors, slower on others) to data warehouses that specialize in running these types of queries. We likely should have provided more details (per query break downs and what not). 4. We used the Percona MySQL TPC-C driver with some changes to make the initial data loading faster. That driver uses the “FOR UPDATE” clause in MySQL instead of running in serializable isolation level. I know you did a lot of work on CockroachDB. The point of the blog post was not to attack cockroach (I personally didn’t want to mention it at all), but to show how MemSQL is different. We are one of the few distributed SQL databases with competitive results on all 3 major TPC benchmarks.
diminoten 7y ago
Regardless of the minor technical nitpicks here, I respect the hell out of the person/people who wrote this article, if only because I know how insanely hard it is to express a technical thought in great detail while maintaining a certain amount of levity to try and keep people interested.
faaaaagggggot 7y ago
Dude, you are such a fucking faggot.
mc110 7y ago
The great thing about AWS and other cloud platforms is that you can put the data in e.g. S3, then use the tool of choice for your workloads. I'm assuming that MemSQL works fine with that sort of configuration, rather than requiring you to lock your data up in some proprietary format. Also, unlike the bad old days of on-premise platforms, you can try things out to see how they work. You could even do that with a public dataset first, to see how it works (see https://registry.opendata.aws/ https://registry.opendata.aws/ for a list of these). For example, there is an Amazon Customer Review dataset of over 160 million customer reviews - you could use that and try MemSQL for various use cases, then look at alternatives. Disclaimer - clearly as a Kognitio employee I'd suggest you looked at us for analytics use cases, and you can see an example of sentiment analysis as scale using the Amazon Customer Review data set at https://kognitio.com/blog/sentiment-analysis-amazon-reviews-pt1/ https://kognitio.com/blog/sentiment-analysis-amazon-reviews-.... Also, a couple of articles on LinkedIn at https://www.linkedin.com/pulse/100-shades-grey-other-amazon-review-discoveries-mark-chopping/ https://www.linkedin.com/pulse/100-shades-grey-other-amazon-... for another piece of work on that same data, and https://www.linkedin.com/pulse/media-brexit-story-so-far-may-corbyn-less-shambles-than-mark-chopping/ https://www.linkedin.com/pulse/media-brexit-story-so-far-may... for a view on Global Media coverage of Brexit over time.

38 points by nikita 7y ago

Might be advisable to note that you don't support foreign keys if you're going to show how much better your performance is versus a database that does.

scarface74 7y ago
It’s an OLAP database. Are you really that concerned about FK constraints? The only other OLAP database that I’ve used is Amazon’s Redshift and FKs are for “informational purposes”. Or is it considered a transactional database or an analytics database?
- AdamProut 7y ago
  MemSQL can run both types of workloads. That is one the main things we're trying to demonstrate by running one of the most popular operational database benchmarks (TPC-C) as well as the two most popular analytics benchmarks (TPC-DS, TPC-H). Our strength is somewhere in the middle[1] [1] https://www.memsql.com/blog/the-need-for-operational-analytics/ https://www.memsql.com/blog/the-need-for-operational-analyti...
  - nikita 7y ago
    Another way to think about MemSQL is that it's an operational database. This means that it can support high velocity transactions and power apps. The fact it can run decision support (OLAP) benchmarks is an important differentiation. Since those benchmarks are SQL benchmarks you can think of it as simple as "it's just great in SQL". Or you can think about it as "if my app has reporting as part of the workloads MemSQL will handle it out of the box". Finally there are many use cases that fall in the category of "Operational Analytics": time series, IoT, real-time dashboarding, moving from batch to streaming, predictive maintenance. All of those require underlying infrastructure to handle transactions and complex SQL in one system.
- deleted 7y ago
  [deleted]
AdamProut 7y ago
(MemSQL CTO here) You're right, MemSQL doesn't support foreign keys as of yet, but none of these benchmarks require foreign key support. Two of them (TPC-H and TPC-DS) are a set of complex SELECT queries where foreign keys are not relevant at all. TPC-C is a write heavy benchmark, but the specification doesn't require foreign keys to be maintained (the data model does indicate the foreign key relationships though)[1]. These are unofficial benchmark results (not independently verified by TPC), so our interpretation of the specs may not be 100% correct, but I think we got it right as far as foreign keys are concerned. [1] http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-c_v5.11.0.pdf http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-c_...
- deleted 7y ago
  [deleted]
- tyingq 7y ago
  Just seems like you would want that noted if you're going to compare with CockroachDB and say things like "Our results show that we can do both transaction processing and data warehousing well".

peterwwillis 7y ago

Similar to the phrase "Lies, damned lies, and Benchmarks", should be the phrase "Lies, damned lies, and AWS costs"

wmf 7y ago
Because?
- peterwwillis 7y ago
  Because all a benchmark tells you is the result of one use case. It's not an estimate of what your use will result in. It's basically a sales pitch. And sadly, upper management falls for it all the time, often without an evaluation period or even spitballing it for a week with the teams that would be using it. Tons of factors will change the result for a particular use case, regardless of how "normalized" the test tries to be (especially considering most are designed for high-load high-performance high-scale scenarios, which isn't the average real-world use case; most people just have unoptimized queries, or a shit legacy stack, or they expect to eventually have a big use case and were told to find either the fastest or cheapest solution, which may not even be in AWS to begin with, and so on...). The AWS cost is just another benchmark, so it's just as much a lie.
  - diminoten 7y ago
    The whole point of benchmarks is that their "use case" correlates roughly with every use case related to the technology being benchmarked. Is the correlation 1:1? No. Is it still relevant when making a decision about what technology to use? Absolutely.
    - dragonwriter 7y ago
      > The whole point of benchmarks is that their "use case" correlates roughly with every use case related to the technology being benchmarked. That's the idea of benchmarks, but it's not actually true of real benchmarks, because invariably some factors which can improve a particular benchmarks beaean inverse relation to performance for some other use cases of the technology.

georgewfraser 7y ago

As someone who's spent a lot of time working with TPC-DS [1] and talking to people about it [2], I see a couple areas that could be improved in this benchmark: 1. Total run time is not an appropriate way to summarize the performance across queries, because some queries take 100x longer than others. The appropriate way to summarize this kind of data is to use the geomean [3]. 2. The official TPC-DS queries make heavy use of grouping sets, which are a rarely-used SQL feature. I think TPC-DS is better if you rewrite the queries to eliminate grouping sets. 3. You used the exact same queries to "warm up" the data warehouse, and to test the performance. Some data warehouses (notably Redshift) aggressively cache intermediate compilation results, so they are much faster the second time they see a query or even a fragment of a query. To model a real user submitting queries interactively, you should use warmup queries that are similar to but not the same as the ones you use to measure performance. 4. You can solve the "vendor benchmarking their own product" problem by submitting a PR to our repo [4], which currently tests Redshift, Snowflake, BigQuery, Azure SQL DW, and Presto. We'd be happy to review it and endorse the timing if it meets our standards for fairness! [1] https://fivetran.com/blog/warehouse-benchmark https://fivetran.com/blog/warehouse-benchmark [2] https://www.youtube.com/watch?v=XpaN-PqSczM https://www.youtube.com/watch?v=XpaN-PqSczM [3] https://en.wikipedia.org/wiki/Geometric_mean https://en.wikipedia.org/wiki/Geometric_mean [4] https://github.com/fivetran/benchmark https://github.com/fivetran/benchmark

sroussey 7y ago
Curious to see TiDB in there
- AdamProut 7y ago
  You need columnstore storage and a reasonably mature query optimizer to get good results on TPC-DS. TiDB is lacking both right now.
  - deleted 7y ago
    [deleted]
  - dongxu 7y ago

arjunnarayan 7y ago

This benchmark is pretty ridiculous for the following reasons: 1. Their database is run in asynchronous durability mode. 2. They specifically do the one thing that TPC-C says you shouldn't do, which is get really high throughput on a small dataset. TPC-C enforces that you scale your data-stored with the query throughput. CockroachDB maxes out at ~12.8tpmC/warehouse because its waiting at the legal maximum throughput, as opposed to running up the numbers in a way that's against the rules (and spirit) of the benchmark. 3. They make all the TPC-DS mistakes that georgewfraser points out elsewhere in this thread. 4. They run in read committed mode (they don't support anything higher), CockroachDB runs in serializable mode. I ended up ranting about this on Twitter, so rather than reproducing everything here, I'm going to link to my rant there. Apologies for the cross-posting across fora: https://twitter.com/narayanarjun/status/1128393193941274624 https://twitter.com/narayanarjun/status/1128393193941274624

dongxu 7y ago
I agree, running TPC-C on asynchronous durability mode is not reasonable.
AdamProut 7y ago
(MemSQL CTO here) 1. MemSQL is running with synchronous replication in all these benchmarks. All data is stored on a 2nd machine before any transaction is acknowledged as committed. You’re right this is not as strong as running with both synchronous writes to disk and over the network. MemSQL supports this as well and results in about a 40 to 50% performance hit depending on the disk speed. Very few of our customers run in this configuration so we didn’t include it (the edge case of multiple machines losing power is not worth the performance hit for them). 2. Can you point me to what you’re describing in the TPC-C specification? I have never heard of what you’re claiming. TPC-C has maximum allowed latency requirements for the 5 transaction types it runs and also requirements around the mix of those transactions in the workload. The goal of the benchmark is still to run as many "New Order" transaction per minute while maintaining the latency requirements of the other unmeasured transactions running in the background (this is what tpmC stands for). We used the Percona TPC-C driver for MySQL to handle this (with a few small bug fixes). 3. The main thing we wanted to show is that our performance on TPC-DS is similar (better at some scale factors, slower on others) to data warehouses that specialize in running these types of queries. We likely should have provided more details (per query break downs and what not). 4. We used the Percona MySQL TPC-C driver with some changes to make the initial data loading faster. That driver uses the “FOR UPDATE” clause in MySQL instead of running in serializable isolation level. I know you did a lot of work on CockroachDB. The point of the blog post was not to attack cockroach (I personally didn’t want to mention it at all), but to show how MemSQL is different. We are one of the few distributed SQL databases with competitive results on all 3 major TPC benchmarks.

diminoten 7y ago

Regardless of the minor technical nitpicks here, I respect the hell out of the person/people who wrote this article, if only because I know how insanely hard it is to express a technical thought in great detail while maintaining a certain amount of levity to try and keep people interested.

faaaaagggggot 7y ago

Dude, you are such a fucking faggot.

mc110 7y ago

The great thing about AWS and other cloud platforms is that you can put the data in e.g. S3, then use the tool of choice for your workloads. I'm assuming that MemSQL works fine with that sort of configuration, rather than requiring you to lock your data up in some proprietary format. Also, unlike the bad old days of on-premise platforms, you can try things out to see how they work. You could even do that with a public dataset first, to see how it works (see https://registry.opendata.aws/ https://registry.opendata.aws/ for a list of these). For example, there is an Amazon Customer Review dataset of over 160 million customer reviews - you could use that and try MemSQL for various use cases, then look at alternatives. Disclaimer - clearly as a Kognitio employee I'd suggest you looked at us for analytics use cases, and you can see an example of sentiment analysis as scale using the Amazon Customer Review data set at https://kognitio.com/blog/sentiment-analysis-amazon-reviews-pt1/ https://kognitio.com/blog/sentiment-analysis-amazon-reviews-.... Also, a couple of articles on LinkedIn at https://www.linkedin.com/pulse/100-shades-grey-other-amazon-review-discoveries-mark-chopping/ https://www.linkedin.com/pulse/100-shades-grey-other-amazon-... for another piece of work on that same data, and https://www.linkedin.com/pulse/media-brexit-story-so-far-may-corbyn-less-shambles-than-mark-chopping/ https://www.linkedin.com/pulse/media-brexit-story-so-far-may... for a view on Global Media coverage of Brexit over time.