Open www-uu opened 2 years ago
👋 @JH-D ,
Thanks for the detailed step-by-step, which made it really easy to reproduce here and give some feedback. I'll provide a few observations to start with and we can go from there.
The iot
use case is the newest of the TSBS use cases and has not been maintained for feature updates at this point. There's only so much time and our benchmarks (as well as most others that other companies run with TSBS) is the cpu-only
use case. It's just easier to calculate total metrics and such because of the even numbers.
This also means that some of the queries in the iot
use case are not written efficiently for larger scale
values (the number of "things" to create values for). I think there were plans to do more with the queries, but that just hasn't happened yet.
Specifically, these queries always query ALL data in the table every time. There are no time range filters created for the queries like the cpu-only
/devops
use case. Again, from a benchmarking perspective, this is generally an unrealistic query format.
So, take it for what that is, the blog post you pointed to was to demonstrate a new simulated dataset, but it hasn't been improved in more than 2 years.
scale
size vs. blog postYour test was different in one significant way, the scale
was set to 4,000 items, whereas the blog post only had 1,000. While that might not seem like a lot, it generates ~130 million more rows (~65 million readings
table/ ~65 million diagnostics
table) .,by jumping from to 4k items. Because the queries have no filter on time
, scanning through 3x more data will just take longer. For instance, I noticed on a different size machine with a large cache that there was still an external merge
operation which will slow it down.
That's one of the big difference to recognize if you're trying to compare against that blog post.
The two queries you tried happen to be two of the worst given your significant increase in total rows, again because there are no time predicates on any of the sample queries.
The last location
query, for instance, hasn't been updated to take advantage of SkinScan
that was introduced in TimescaleDB 2.3. When I add the appropriate index and run an updated query DISTINCT
query, it returns on 4k items in a few hundred milliseconds. Without SkipScan
, the query as written (because there is no time predicate) will always return poorly (a known PostgreSQL issue). Read more: https://blog.timescale.com/blog/how-we-made-distinct-queries-up-to-8000x-faster-on-postgresql/
The other query creates 10 minute time_buckets
for every item, for all 4 days and then has to rescan the entire table to do a LEAD()
query. It's just a slow query as written without a time predicate as you add more and more tags
(which is equal to scale
).
I can't say for sure, but I ran your exact process on an EC2 instance that's similar in size (m5.4xlarge). I was not running Docker, however. My ingest rate from a separate client was significantly better than yours - and inline with the numbers we show in that blog post - about ~385,000 rows/sec. Your results for ingest were only ~85,000 rows/sec.
So while your machine is decently big, something appears to be limiting the overall performance of PostgreSQL. Have you tried verifying the resources that Docker is using or running TimecaleDB outside of Docker?
We do our best to maintain TSBS and update queries, etc. That said, we do know that iot
needs some attention, however our benchmarking standard is cpu-only
and it's the use case that all other databases run as well when they publish benchmarking results. Consider using those queries as a starting place because they attempt to run queries with various time ranges (and a query appropriately written to get the "last value") and see where that puts you.
@ryanbooz Thanks for reply.
At the beginning I read the introduction article. Then conducted an experiment according to GitHub's README.md, so such as the "scale" parameter use 4000, and the sample queries. Because I think the tutorial is basic, should finish it first.
Will try to use the cpu-only
(mainly) and devops
test case.
Will try to install TimescaleDB on ubuntu, not docker, re-run tests.
curious to hear the results of your docker vs non-docker test
Description
Follow the docs, finished the test. But the test result quite different with the docs, much more slow. For example, "Overall query rate 0.31 queries/sec". Is there something wrong?
Environment infos
Operating System: Ubuntu 20.04.2 LTS TimescaleDB version: 2.2.1 (Run from docker image: timescale/timescaledb-postgis:latest-pg12) PostgreSQL version: PostgreSQL 12.6 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit Hardware: 32CPU, 64GB Mem, 2TB Disk * 2(not SSD)
Step 1. Generate data
Step 2. Insert data
Step 3. breakdown-frequency Test
Step 4. last-loc Test
This has no log info even after waiting hours , have to cancel.