4 ms·

If you take one thing away from this it's do not use AWS timestream, and if you are using it, get rid of it quick. It's unfit for purpose. That first graph tel

by eloff 6y ago

If you take one thing away from this it's do not use AWS timestream, and if you are using it, get rid of it quick. It's unfit for purpose. That first graph tells you everything you need to know. Time to insert a billion events: TimescaleDB: 5 min AWS Timestream: ~2 weeks! I think the team at AWS that built it should be reassigned and contractors or an A team brought in to try and salvage it. Because they've built themselves a very expensive lemon and they're trying to sell it to the world as a Cadillac. The fact that they did not detect this themselves before or since releasing it upon the world demonstrates to me that they're incompetent and have to be replaced.

SmokeyHamster 6y ago
Yeah, differences like that are why I ditched MySQL years ago in favor of PostgreSQL. I was using MySQL at the time, and did a performance comparison of how long it took each database to dump and load a snapshot of a 100GB database. MySQL took 3 days. PostgreSQL took 30 minutes. I posted on a MySQL forum asking why the drastic difference, and they tried to hand-wave it away by saying I had a lot of indexes. Well yeah, databases have indexes. I shouldn't expect my MySQL database transfers to be quick if it has an index? Features shouldn't cripple the application for routine tasks.
- eloff 6y ago
  That's surprising to me since Postgres does less efficient writes compared to MySQL in order to optimize for read queries[1] - a sensible tradeoff since most of the time reads are more common than writes in OLTP workloads. TimescaleDB essentially solves this problem for the specific case of time-series inserts. [In Postgres] if we have a table with a dozen indexes defined on it, an update to a field that is only covered by a single index must be propagated into all 12 indexes to reflect the ctid for the new row.[1] [1] https://eng.uber.com/postgres-to-mysql-migration/ https://eng.uber.com/postgres-to-mysql-migration/
  - aforwardslash 6y ago
    The parent post didn't have enough detail, but I'd assume the dump would be using COPY for inserts. AFAIK when using COPY, the indexes are only built at the end of the command, not on each insert.
    - djk447 6y ago
      Indexes are built during the inserts for copies and for normal inserts, in general copies are faster because many people use single row inserts that need to instantiate executor nodes and other bits for every row. I think there used to be some other differences, but in general we've seen that multi-row inserts are around as fast as copies and I'm honestly not sure which one our benchmarking tool uses. (I'm at Timescale, btw). Aside from that, the MySQL vs Postgres comment above actually has to do with updates not with inserts and the main difference here is that PG stores rows in the heap whereas MySQL has primary keys that actually organize tables (and also things around copy-on-write MVCC model). So, in some ways, PG should actually be faster for raw inserts, because you don't have to deal with as many page splits and other bits for the organization of the heap, things are just written there (and indexes are secondary). We limit the overhead of building indexes by making sure our chunks are "right-sized" so that you don't end up swapping too much as you're doing inserts into recent data.
    - djk447 6y ago
      (Just noticed this was about dump/restore, during restores you can in fact write the data and then build the index in a separate operation and that can be faster, sorry for missing context)
      - aforwardslash 6y ago
        Still, you managed to add valuable knowledge on your reply I wasn't aware of, regarding the overall topic. thank you for that :)
solidasparagus 6y ago
> I think the team at AWS that built it should be reassigned and contractors or an A team brought in to try and salvage it That's a knee-jerk assessment. You don't know the first thing about Timestream's development and we are only a few years into the development process of what I believe is a novel architecture for a timeseries database. Give it another couple of years and then we can take a look at how Timestream is doing. One of Amazon's advantages is the ability to plan and execute on a longer time scale than their competitors. They can take approaches that take longer to bear fruit but are better in the long term. Timestream is a scalable timeseries database with separated compute and storage built for extremely high volumes (or that's my guess given the architecture offloads cold data to magnetic storage). Whereas Timescale seems to be timeseries functionality added to Postgres - which means they are probably going to hit scaling (perf/cost) issues once they need to work at petabyte scale. And my guess would be that their coupling to Postgres is going to make it painful to build for that scale. They also claim to offer separated compute and storage, but based on the pricing model it seems to be that you can change your CPU+Storage configuration, not an ephemeral design where you only pay for compute when it's needed - very different from Timestream's ephemeral pricing model. This is a pure guess, but I would imagine that Timescale being built on top of Postgres is going to make a truly serverless SaaS (i.e. pay for only what you use) very difficult to build. Time will tell, but this is sort of if MySQL benchmarked writes against Snowflake 5 years ago and then thinking that everyone at Snowflake should be fired.
- eloff 6y ago
  Yeah, it's harsh. But we're talking 6000x worse ingest performance for a database whose whole raison d'etre is based on ingesting high volumes of data - otherwise just use an RDBMS. That's like making a car with square wheels. The only logical thing for management to do to a team who delivered that is disband them - because something is horribly wrong. Now to be completely fair they do get good ingest performance if you open thousands of connections to send the data over. So they can probably fix it. I still wouldn't touch it with a stick though, that's not the only issue they have at present.
ryanbooz 6y ago
(Post author from Timescale) For the record, in reviewing HN conversation tonight I saw this and realized it was an incorrect quote of the article. Totally honest mistake I'm sure, but I wanted to set the record straight. We spent a little over a week working at Timestream benchmarking. Trying different approaches to batching metrics for ingest, threading differently, running multiple EC2 instance, etc. to improve performance. Once we felt like we were getting the best we could, we started our final ingest which we let run for nearly 40 hours (~2 days). The other value is absolutely correct, however. We were able to ingest 1 billion metrics into Timescale in 5 minutes.
- crb002 6y ago
  How was the test set up? Big difference between slamming localhost and ingesting edge inserts from IoT devices all over.
  - ryanbooz 6y ago
    It's all detailed in the post (granted... it IS a detailed, long read ;-) TL;DR; - TimescaleDB was tested with a cloud setup in Digital Ocean, separate client and server. We actually ran a second, unpublished, test into Timescale Forge from the same client(s) that we tested Timestream with. We did this second TimescaleDB test just to see if something was wrong with our EC2 instances or setup in other way. So, completely separate service offering with no VPC or anything - just a raw PSQL connection from client to server. The Timescale Forge instance was 1/4 the specs of the DO server from the published results (again, the intent wasn't to replicate the TimescaleDB tests all over again), and it still easily achieved 1.2 million/sec ingest from the same client computer where Timestream only achieved ~525 metrics/sec.

by eloff 6y ago

SmokeyHamster 6y ago

Yeah, differences like that are why I ditched MySQL years ago in favor of PostgreSQL. I was using MySQL at the time, and did a performance comparison of how long it took each database to dump and load a snapshot of a 100GB database. MySQL took 3 days. PostgreSQL took 30 minutes. I posted on a MySQL forum asking why the drastic difference, and they tried to hand-wave it away by saying I had a lot of indexes. Well yeah, databases have indexes. I shouldn't expect my MySQL database transfers to be quick if it has an index? Features shouldn't cripple the application for routine tasks.

eloff 6y ago
That's surprising to me since Postgres does less efficient writes compared to MySQL in order to optimize for read queries[1] - a sensible tradeoff since most of the time reads are more common than writes in OLTP workloads. TimescaleDB essentially solves this problem for the specific case of time-series inserts. [In Postgres] if we have a table with a dozen indexes defined on it, an update to a field that is only covered by a single index must be propagated into all 12 indexes to reflect the ctid for the new row.[1] [1] https://eng.uber.com/postgres-to-mysql-migration/ https://eng.uber.com/postgres-to-mysql-migration/
- aforwardslash 6y ago
  The parent post didn't have enough detail, but I'd assume the dump would be using COPY for inserts. AFAIK when using COPY, the indexes are only built at the end of the command, not on each insert.
  - djk447 6y ago
    Indexes are built during the inserts for copies and for normal inserts, in general copies are faster because many people use single row inserts that need to instantiate executor nodes and other bits for every row. I think there used to be some other differences, but in general we've seen that multi-row inserts are around as fast as copies and I'm honestly not sure which one our benchmarking tool uses. (I'm at Timescale, btw). Aside from that, the MySQL vs Postgres comment above actually has to do with updates not with inserts and the main difference here is that PG stores rows in the heap whereas MySQL has primary keys that actually organize tables (and also things around copy-on-write MVCC model). So, in some ways, PG should actually be faster for raw inserts, because you don't have to deal with as many page splits and other bits for the organization of the heap, things are just written there (and indexes are secondary). We limit the overhead of building indexes by making sure our chunks are "right-sized" so that you don't end up swapping too much as you're doing inserts into recent data.
  - djk447 6y ago
    (Just noticed this was about dump/restore, during restores you can in fact write the data and then build the index in a separate operation and that can be faster, sorry for missing context)
    - aforwardslash 6y ago
      Still, you managed to add valuable knowledge on your reply I wasn't aware of, regarding the overall topic. thank you for that :)

solidasparagus 6y ago

> I think the team at AWS that built it should be reassigned and contractors or an A team brought in to try and salvage it That's a knee-jerk assessment. You don't know the first thing about Timestream's development and we are only a few years into the development process of what I believe is a novel architecture for a timeseries database. Give it another couple of years and then we can take a look at how Timestream is doing. One of Amazon's advantages is the ability to plan and execute on a longer time scale than their competitors. They can take approaches that take longer to bear fruit but are better in the long term. Timestream is a scalable timeseries database with separated compute and storage built for extremely high volumes (or that's my guess given the architecture offloads cold data to magnetic storage). Whereas Timescale seems to be timeseries functionality added to Postgres - which means they are probably going to hit scaling (perf/cost) issues once they need to work at petabyte scale. And my guess would be that their coupling to Postgres is going to make it painful to build for that scale. They also claim to offer separated compute and storage, but based on the pricing model it seems to be that you can change your CPU+Storage configuration, not an ephemeral design where you only pay for compute when it's needed - very different from Timestream's ephemeral pricing model. This is a pure guess, but I would imagine that Timescale being built on top of Postgres is going to make a truly serverless SaaS (i.e. pay for only what you use) very difficult to build. Time will tell, but this is sort of if MySQL benchmarked writes against Snowflake 5 years ago and then thinking that everyone at Snowflake should be fired.

eloff 6y ago
Yeah, it's harsh. But we're talking 6000x worse ingest performance for a database whose whole raison d'etre is based on ingesting high volumes of data - otherwise just use an RDBMS. That's like making a car with square wheels. The only logical thing for management to do to a team who delivered that is disband them - because something is horribly wrong. Now to be completely fair they do get good ingest performance if you open thousands of connections to send the data over. So they can probably fix it. I still wouldn't touch it with a stick though, that's not the only issue they have at present.

ryanbooz 6y ago

(Post author from Timescale) For the record, in reviewing HN conversation tonight I saw this and realized it was an incorrect quote of the article. Totally honest mistake I'm sure, but I wanted to set the record straight. We spent a little over a week working at Timestream benchmarking. Trying different approaches to batching metrics for ingest, threading differently, running multiple EC2 instance, etc. to improve performance. Once we felt like we were getting the best we could, we started our final ingest which we let run for nearly 40 hours (~2 days). The other value is absolutely correct, however. We were able to ingest 1 billion metrics into Timescale in 5 minutes.

crb002 6y ago
How was the test set up? Big difference between slamming localhost and ingesting edge inserts from IoT devices all over.
- ryanbooz 6y ago
  It's all detailed in the post (granted... it IS a detailed, long read ;-) TL;DR; - TimescaleDB was tested with a cloud setup in Digital Ocean, separate client and server. We actually ran a second, unpublished, test into Timescale Forge from the same client(s) that we tested Timestream with. We did this second TimescaleDB test just to see if something was wrong with our EC2 instances or setup in other way. So, completely separate service offering with no VPC or anything - just a raw PSQL connection from client to server. The Timescale Forge instance was 1/4 the specs of the DO server from the published results (again, the intent wasn't to replicate the TimescaleDB tests all over again), and it still easily achieved 1.2 million/sec ingest from the same client computer where Timestream only achieved ~525 metrics/sec.