We Chose a Distributed SQL Database to Complement MySQL | Hacker News Reader

We Chose a Distributed SQL Database to Complement MySQL

pingcap.com

63 pointsCaitin_Chen16 comments

If anyone from the company's reading this: I'd take a guess that it's wanting "...to ensure that TiFlash processes the latest (not just fresh) data" is a major problem. If you can tolerate even a few minutes-old data then I really think you could use some dumb kind of replication to another machine which would be much simpler.

Also a billion rows of data isn't really so much, depending on what you're storing.

(I'm trying to get 250 million rows (of 1 character) into a db to see how fast it counts when in memory. Still inserting)

Edit: TSQL, counting 268 million rows all mem resident, 7-year old CPU, forcing it to use 1 core is 12 secs, allowing it to parallelise over 2 cores (all I have) is 8 secs.

This is for comparison to "I did a simple test with five TiKV nodes, one TiFlash node, and one table with 250 million rows of data. I tried to count the table records. When I queried by index in TiKV, it took me more than 40 seconds to get the result. But when I queried in a single TiFlash node, it took only 10 seconds."

I used single bytes per row but I know from prior testing that fat rows and thin rows count about the same speed.

The TiFlash replication is sub-second and happens automatically. We are going to be publishing a more extensive paper on TiFlash, but the point is that the operational burden is equivalent to a read replica but you get the column-based storage speed of a data warehouse.

I agree the data size doesn't seem extraordinarily big from some of the description. Later in the article it states a size of 2.4 TB.

It is also true that in-memory databases should be faster. TiDB is designed for data that is too large to keep in memory.

Note that having 5 TiKV nodes isn't going to make this single count query any faster than having 1 TiKV node. TiDB is designed for high availability, so a default TiKV setup has 3 nodes each with a copy of the data. TiKV can be scaled out horizontally to provide more compute power to handle a larger workload but it is not likely to make a single query on an idle machine any faster.

The link to the paper (on VLDB 2020) is now available: http://www.vldb.org/pvldb/vol13/p3072-huang.pdf

Well, its a lot easier to use TiFlash than replicating your OLTP data to a read-optimized database (whatever it is).

1. Add TiFlash nodes to your TiDB cluster (you might need to config some ports and ip address just like other databases and then start the nodes)

2. ALTER TABLE your_table set TiFlash replica 1;

That's it. You won't need all data in memory and replicating details are taken care of silently (schema change, fault-tolerance, load-balance, consistency and etc).

I believe heterogeneous replicating even with minutes latency tolerance will not be as easy as above :)

PS: the good part of keep TP/AP consistent is that user can use them in a single application without any surprise: it's just a different forms of a single logical data.

PS2: I'm a dev of PingCAP.

TiDB sounds cool except it's not 8.0 compatible. We use the hell out of CTEs and window functions.

It actually has all the window functions: https://docs.pingcap.com/tidb/stable/window-functions

For CTEs, stay tuned for the next release (TiDB 5.0) or perhaps track this github issue: https://github.com/pingcap/tidb/issues/17472

Vitess might have being a decent option considering they are already MySQL shop

I might be wrong here, but can someone comment on the process required to add additional nodes on Vitess?

Every time I start reading about Vitess, I fall into a hole about manual resharding, hosting multiple vitess instances per host (I think 250GB of storage each?), and that cross-shard reads may be inconsistent.

I am taking a quick look at the documentation of TiDB, and they have automatic data re-balancing, 2PC Raft-based-transactions, global snapshot isolation, and actually some nice dashboards to analyze cluster health.

Vitess just does not seem nice from an operations perspective.

Yes, if you want to add a node you have to manually reshard. It’s quite the process. I’d add each node is a MySQL database and you can make each shard as large you want.

The data on different vitess shards is disjoint, so I don’t know how much you need to worry about consistency for cross shard reads. And vitess doesn’t really support cross shard transactions either.

My understanding you will have to reshard. It's always a set of tradeoffs for the most part automatic in this context will mean network and other types of overhead.

Hi, I work at PingCAP and can try to explain the difference between the two options. TiFlash (fast column-based storage) was important in this story and an equivalent doesn't exist for Vitess.

Vitess is a MySQL sharding system. In this article they stated that it was too difficult to query across shards in their existing setup. Switching to Vitess from their existing sharding system (assuming they were using something else) might have helped them for some of their queries. However, Vitess doesn't support cross shard transactions for production workloads. TiDB doesn't have these sharding limitations.

Thanx for the detailed resp. Just to clarify TiDB uses 2pc too for cross region transactions from what I remember?

Yes, it is a form of 2PC that happens transparently during normal transactions. It is designed to perform well for production workloads whereas Vitess now has 2PC but warns against using it and requires configuration changes to enable it.

2PC = two-phase commit

https://en.m.wikipedia.org/wiki/Two-phase_commit_protocol

For anyone like me whose neural net had a cache miss due to lack of caffeine activation function ;)

What is PingCAP's story when it comes to data protection and privacy, particularly with its managed product?

We just had a SOC 2 (type 1) examination for Security, Availability and Confidentiality. [1] We believe independent audits for compliance are more meaningful than any words of assurance.

At an architectural level tidb cloud (managed TiDB) currently dedicates infrastructure for each tenant although we are working on developing lower cost options with less isolation.

[1] https://pingcap.com/blog/pingcap-successfully-completes-soc-...

Reply on news.ycombinator.com