Science and technology

How to save lots of time with TiDB

Last November, I wrote about key differences between MySQL and TiDB, an open source-compatible, cloud-based database engine, from the angle of scaling each options within the cloud. In this follow-up article, I will dive deeper into the methods TiDB streamlines and simplifies administration.

If you come from a MySQL background, you could be used to doing plenty of guide duties which are both not required or a lot easier with TiDB.

The inspiration for TiDB got here from the founders managing sharded MySQL at scale at a few of China’s largest web corporations. Since necessities for working a big system at scale are a key concern, I will have a look at some typical MySQL database administrator (DBA) duties and the way they translate to TiDB.

In TiDB’s architecture:

  • SQL processing is separated from information storage. The SQL processing (TiDB) and storage (TiKV) elements independently scale horizontally.
  • PD (Placement Driver) acts because the cluster supervisor and shops metadata.
  • All elements natively present excessive availability, with PD and TiKV utilizing the Raft consensus algorithm.
  • You can entry your information by way of both MySQL (TiDB) or Spark (TiSpark) protocols.

Adding/fixing replication slaves

tl;dr: It does not occur in the identical means as in MySQL.

Replication and redundancy of information are mechanically managed by TiKV. You additionally need not fear about creating preliminary backups to seed replicas, as each the provisioning and replication are dealt with for you.

Replication can also be quorum-based utilizing the Raft consensus algorithm, so you do not have to fret in regards to the inconsistency issues surrounding failures that you simply do with asynchronous replication (the default in MySQL and what many customers are utilizing).

TiDB does help its personal binary log, so it may be used for asynchronous replication between clusters.

Optimizing gradual queries

tl;dr: Still occurs in TiDB

There isn’t any possible way out of optimizing gradual queries which were launched by growth groups.

As a mitigating issue although, if it is advisable add respiratory room to your database’s capability when you work on optimization, the TiDB’s structure lets you horizontally scale.

Upgrades and upkeep

tl;dr: Still required, however typically simpler

Because the TiDB server is stateless, you’ll be able to roll by an improve and deploy new TiDB servers. Then you’ll be able to take away the older TiDB servers from the load balancer pool, shutting down them as soon as connections have drained.

Upgrading PD can also be fairly easy since solely the PD chief actively solutions requests at a time. You can carry out a rolling improve and improve PD’s non-leader friends one after the other, after which change the chief earlier than upgrading the ultimate PD server.

For TiKV, the improve is marginally extra complicated. If you need to take away a node, I like to recommend first setting it to be a follower on every of the areas the place it’s at the moment a pacesetter. After that, you’ll be able to carry down the node with out impacting your utility. If the downtime is temporary, TiKV will recuperate with its regional friends from the Raft log. In an extended downtime, it might want to re-copy information. This can all be managed for you, although, for those who select to deploy utilizing Ansible or Kubernetes.

Manual sharding

tl;dr: Not required

Manual sharding is especially a ache on the a part of the applying builders, however as a DBA, you might need to become involved if the sharding is naive or has issues comparable to hotspots (many workloads do) that require re-balancing.

In TiDB, re-sharding or re-balancing occurs mechanically within the background. The PD server observes when information areas (TiKV’s time period for chunks of information in key-value kind) get too small, too massive, or too continuously accessed.

You can even explicitly configure PD to retailer areas on sure TiKV servers. This works rather well when mixed with MySQL partitioning.

Capacity planning

tl;dr: Much simpler

Capacity planning on a MySQL database is usually a little bit onerous as a result of it is advisable plan your bodily infrastructure necessities two to 3 years from now. As information grows (and the working set adjustments), this is usually a troublesome activity. I would not say it utterly goes away within the cloud both, since altering a grasp server’s is all the time onerous.

TiDB splits information into roughly 100MiB chunks that it distributes amongst TiKV servers. Because this increment is way smaller than a full server, it is a lot simpler to maneuver round and redistribute information. It’s additionally potential so as to add new servers in smaller increments, which is simpler on planning.


tl;dr: Much simpler

This is said to capability planning and sharding. When we speak about scaling, many individuals take into consideration very massive methods, however that isn’t completely how I consider the issue:

  • Scaling is having the ability to begin with one thing very small, with out having to make enormous investments upfront on the prospect it may change into very massive.
  • Scaling can also be a folks downside. If a system requires an excessive amount of inner data to function, it may possibly change into onerous to develop as an engineering group. The barrier to entry for brand spanking new hires can change into very excessive.

Thus, by offering computerized sharding, TiDB can scale a lot simpler.

Schema adjustments (DDL)

tl;dr: Mostly higher

The information definition language (DDL) supported in TiDB is all on-line, which implies it does not block different reads or writes to the system. It additionally does not block the replication stream.

That’s the excellent news, however there are a few limitations to concentrate on:

  • TiDB doesn’t at the moment help all DDL operations, comparable to altering the first key or some “change data type” operations.
  • TiDB doesn’t at the moment mean you can chain a number of DDL adjustments in the identical command, e.g., ALTER TABLE t1 ADD INDEX (x), ADD INDEX (y). You might want to break these queries up into particular person DDL queries.

This is an space that we’re seeking to enhance in TiDB 3.0.

Creating one-off information dumps for the reporting group

tl;dr: May not be required

DBAs detest guide duties that create one-off exports of information to be consumed by one other group, maybe in an analytics instrument or information warehouse.

This is usually required when the forms of queries which are be executed on the dataset are analytical. TiDB has hybrid transactional/analytical processing (HTAP) capabilities, so in lots of instances, these queries ought to work high quality. If your analytics group is utilizing Spark, you can even use the TiSpark connector to permit them to attach on to TiKV.

This is one other space we’re bettering with TiFlash, a column retailer accelerator. We are additionally engaged on a plugin system to help exterior authentication. This will make it simpler to handle entry by the reporting group.


In this publish, I checked out some widespread MySQL DBA duties and the way they translate to TiDB. If you wish to be taught extra, try our TiDB Academy course designed for MySQL DBAs (it is free!).

Most Popular features the latest multimedia technologies, from live video streaming to audio packages to searchable archives of news features and background information. The site is updated continuously throughout the day.

Copyright © 2017 Breaking Express, Green Media Corporation

To Top