I don't believe TiDB is a time series DB, it's an OLTP and OLAP database and not indexed by time by default unless that's part of the table schema. I would also assume that TiKV by default is using size/leveled based compaction as it uses RocksDB, not time window compaction.
TiDB is an open source NewSQL database that speaks the MySQL protocol. You can scale it horizontally by adding nodes.
It is a relational DB (not time series). To describe a couple of differentiators from its peers:
- It aims to optimize both OLTP and OLAP workloads (aka HTAP)
- It uses a component-based architecture (the TiDB server is stateless and speaks the MySQL protocol. TiKV is the distributed storage layer. Thus, you can scale either independently. You can also connect to tikv directly from Spark).
Would you mind comparing TiDB to other HTAP databases like SAP HANA, MemSQL, HyPer? I'm more interested in the architecture, trade-offs, best/worst use cases. How would you compare the analytical bit with regard to analytical databases like ClickHouse, SQL Server tabular model, MapD?
- We suggest you use TiDB for "adhoc OLAP", and Spark for more complicated cases. While parallel, the data is still stored in a row-format (more on that next year!), so an OLAP-only solution may still have performance advantages. TiDB also supports hash joins/aggregation/sort merge joins etc. So compared to MySQL for example, you should see quite a performance improvement.
Thanks for the info. As far as I know other HTAPs often use row storage and column storage together, moving data from row to column over time. It seems like lack of such structure could be a drawback for TiDB in comparison to others.
Do you guys plan to add support for the new MySQL X-Protocol? Seems like it would be well suited for this type of architecture, especially when using the document store type APIs.
On https://www.pingcap.com/docs/sql/mysql-compatibility/ it mentions "FOREIGN KEY constraints" under unsupported features. Is that right? Isn't that a rather big problem for an OLTP DB? Or am I missing something?
Yes, that is correct. I hope to see FOREIGN KEY constraints added in the future.
In the interim though, when comparing TiDB to (application) sharded systems, it is important to clarify that FOREIGN KEYS will only be available locally to a single server. So it is a limitation that some of the large deployments we encounter are already familiar with.
Greg from the TiDB team here. I do share your sentiment, and at the moment you can probably best track or progress on this issue here: https://github.com/pingcap/tidb/issues/8484
The explanation is just that TiDB is being developed with tight feedback from our customers that have many TB of data. The feedback from that scale of users is overwhelmingly that they do not want to take the performance hit of foreign keys. It is worth mentioning though that you can declare foreign keys and that on master we do properly check DDL statements (but there is no DML enforcement).
I am trying to figure out a design that will satisfy users with large and small data alike and even let users use foreign keys for documentation purposes when they are not enforced for performance reasons. It would be great to have more community input on this.
Anyone here who is running TiDB in production that can speak to upsides and downsides? I find I only trust a database after a couple years of real usage, so it would be great to hear from someone who is currently using it.
This post inpsired a lot of great discussion about the Spanner family of distributed databases. Someone asked about TiDB there, but there was no follow up. Can someone answer that question?
The main question is this: how large is the dataset created in Sysbench? 16 tables with 10,000,000 pieces of data per table is interesting, but how big are those individual pieces? Morgan said "the expected data volume for TiDB is larger than memory," so does this benchmark actually test the underlying storage, or is it all in-memory?
Thanks for any help! Would like to try replicating this if possible in our lab.
Hi Rafuino, send me an email at [firstname]@pingcap.com and I'll connect you with the benchmark author.
Just to expand a little bit on this comment:
> The expected data volume for TiDB is larger than memory.
There is both total data volume, and working set (workload dependent). This point was in regards to some databases use data structures which require the total data to be memory resident.
The working set is a longer discussion - it could just be a few percent of the total volume, and some working sets have a "long tail" of cache misses vs. short tail. And it can vary for a B+tree vs. an LSM tree too. RocksDB uses compression which helps a lot.
It is not clear in this benchmark if the working set fits entirely in memory. Thanks for bringing it to my attention - I will follow up and make sure we do a better job of communicating what is being tested.
21 comments
[ 4.4 ms ] story [ 36.7 ms ] threadIt is a relational DB (not time series). To describe a couple of differentiators from its peers:
- It aims to optimize both OLTP and OLAP workloads (aka HTAP)
- It uses a component-based architecture (the TiDB server is stateless and speaks the MySQL protocol. TiKV is the distributed storage layer. Thus, you can scale either independently. You can also connect to tikv directly from Spark).
- TiDB is Open Source (Apache 2.0 license). Several others that you mention here are commercial offerings.
- The expected data volume for TiDB is larger than memory. I believe MemSQL, for example, is memory-only.
- The architecture of TiDB is inspired by Google Spanner.
- We try to be transparent on less-suited cases. See large+small transactions, single-threaded workloads from: https://www.pingcap.com/docs/sql/mysql-compatibility/
In regards to the analytical piece:
- We suggest you use TiDB for "adhoc OLAP", and Spark for more complicated cases. While parallel, the data is still stored in a row-format (more on that next year!), so an OLAP-only solution may still have performance advantages. TiDB also supports hash joins/aggregation/sort merge joins etc. So compared to MySQL for example, you should see quite a performance improvement.
Hope this helps!
The community has added a Redis protocol on top of TiKV with Titan: https://medium.com/@shafreeck/titan-a-distributed-redis-prot...
I expect to see more, including native language drivers directly to TiKV.
In the interim though, when comparing TiDB to (application) sharded systems, it is important to clarify that FOREIGN KEYS will only be available locally to a single server. So it is a limitation that some of the large deployments we encounter are already familiar with.
The explanation is just that TiDB is being developed with tight feedback from our customers that have many TB of data. The feedback from that scale of users is overwhelmingly that they do not want to take the performance hit of foreign keys. It is worth mentioning though that you can declare foreign keys and that on master we do properly check DDL statements (but there is no DML enforcement).
I am trying to figure out a design that will satisfy users with large and small data alike and even let users use foreign keys for documentation purposes when they are not enforced for performance reasons. It would be great to have more community input on this.
Thanks!
However, I want to mention mitigating circumstances which have helped TiDB stabilize:
- We use RocksDB as the underlying storage library, which was developed by Facebook, and has been battle-tested by many others.
- The architecture itself has been inspired (and proven) by Google Spanner
- Correctness has been proven with TLA+ https://github.com/pingcap/tla-plus
- We have 300+ customers storing 15 PiB+
Specifically full case studies: https://www.pingcap.com/success-stories/tidb-in-iqiyi/ https://www.pingcap.com/success-stories/tidb-in-zhuanzhuan/ https://www.pingcap.com/success-stories/tidb-in-eleme/ https://www.pingcap.com/success-stories/tidb-in-mobike/ https://www.pingcap.com/success-stories/tidb-in-yuanfudao/
http://dbmsmusings.blogspot.com/2018/09/newsql-database-syst...
For a quick answer, I believe Daniel Abadi's followup comment addresses it:
> This post did not discuss a central oracle. It discusses the difference between partitioned vs global consensus.
In TiDB the pd leader is a central oracle.
https://github.com/pingcap/docs/blob/master/benchmark/sysben...
The main question is this: how large is the dataset created in Sysbench? 16 tables with 10,000,000 pieces of data per table is interesting, but how big are those individual pieces? Morgan said "the expected data volume for TiDB is larger than memory," so does this benchmark actually test the underlying storage, or is it all in-memory?
Thanks for any help! Would like to try replicating this if possible in our lab.
Just to expand a little bit on this comment:
> The expected data volume for TiDB is larger than memory.
There is both total data volume, and working set (workload dependent). This point was in regards to some databases use data structures which require the total data to be memory resident.
The working set is a longer discussion - it could just be a few percent of the total volume, and some working sets have a "long tail" of cache misses vs. short tail. And it can vary for a B+tree vs. an LSM tree too. RocksDB uses compression which helps a lot.
It is not clear in this benchmark if the working set fits entirely in memory. Thanks for bringing it to my attention - I will follow up and make sure we do a better job of communicating what is being tested.