1 00:00:07,980 --> 00:00:13,980 Now I'm going to look at the cluster of  TiDB servers also known as the SQL layer. 2 00:00:14,640 --> 00:00:17,640 Thanks to this layer TiDB  understands SQL statements. 3 00:00:18,420 --> 00:00:23,100 One of the most important features of SQL  layer is managing the mapping between the   4 00:00:23,100 --> 00:00:28,680 table row in relational database domain  and the key value pair in KV domain. 5 00:00:29,460 --> 00:00:34,260 Here is how the mapping works. TiDB  assigns a table ID to the table.   6 00:00:34,920 --> 00:00:38,820 The table ID is an integer that  is unique across the cluster. 7 00:00:40,320 --> 00:00:46,800 TiDB also assigns a row ID to each row. The row ID  is also an integer being unique within the table. 8 00:00:47,400 --> 00:00:52,380 In the slide, on the left side, there  are three rows with four columns. 9 00:00:52,380 --> 00:00:57,180 The first column is the primary key  of the table. The KV domain is on the   10 00:00:57,180 --> 00:01:03,960 right side. The key is shown in red. The  value is enclosed in the square brackets. 11 00:01:05,160 --> 00:01:09,960 Additionally, for the row ID, TiDB  server has made a small optimization,   12 00:01:10,620 --> 00:01:15,480 which is, if the primary key column  for your table is integer data type,   13 00:01:15,480 --> 00:01:23,040 then SQL layer will use the primary key as the row  ID directly to compose the key for the KV record. 14 00:01:24,360 --> 00:01:30,420 This is exactly the case shown on a slide. On the  other hand, if the primary key column is a non   15 00:01:30,420 --> 00:01:37,140 integer data type, the SQL layer will generate  and encode a row ID for the key in KV record. 16 00:01:38,340 --> 00:01:43,140 We will cover more details related to the  row ID in the clustered index section,   17 00:01:43,140 --> 00:01:48,120 in another course named TiDB Specific  features and transaction control. 18 00:01:49,920 --> 00:01:54,600 The row ID can be used effectively  in a unique get or point get query.   19 00:01:55,500 --> 00:02:01,200 The table prefix can be used in full  table scan. From TiKV map point of view,   20 00:02:01,200 --> 00:02:06,660 the full table scan in relational domain  is an actually a render scan in KV demand. 21 00:02:08,340 --> 00:02:12,420 Now let's do a deep dive into a  single TiKV server to see what   22 00:02:12,420 --> 00:02:16,740 modules the TiDB server has on it  and what are the related functions. 23 00:02:17,880 --> 00:02:23,400 Suppose the client issues an SQL statement  over the network. The front end handles the   24 00:02:23,400 --> 00:02:29,640 request is the protocol layer. The protocol layer  actually makes the TiDB server a network server. 25 00:02:30,420 --> 00:02:34,620 Then you reach the session context  stage after you have been authenticated. 26 00:02:35,340 --> 00:02:39,720 After you get a session context, the  request enters the passing stage. 27 00:02:40,860 --> 00:02:44,940 Parser is going to treat your SQL  statement text as a logical tree structure,   28 00:02:44,940 --> 00:02:49,860 and then analyze your intention, such as  what database objects you would like to   29 00:02:49,860 --> 00:02:55,020 access and what operations you would  like to do on these database objects. 30 00:02:56,220 --> 00:03:02,460 After that it will be sent to a logical optimizer  which is a rule-based optimizer. The logical   31 00:03:02,460 --> 00:03:07,500 optimizer may rewrite your SQL statement into  a good shape following a series of rules.   32 00:03:08,160 --> 00:03:11,880 This can help the following step to  generate a better execution plan. 33 00:03:13,440 --> 00:03:15,960 The output is the logical excursion plan. 34 00:03:17,340 --> 00:03:22,440 After that it is sent to the physical  optimizer which is a cost based optimizer.   35 00:03:23,040 --> 00:03:26,640 The physical optimizer is aware of  some other important information,   36 00:03:26,640 --> 00:03:32,280 such as the statistics of the objects your  SQL statement needs directly or indirectly. 37 00:03:33,840 --> 00:03:37,080 For example it might include  how many rows the table has,   38 00:03:37,080 --> 00:03:42,060 what is the average length of each row,  and the information about the indexes. 39 00:03:43,080 --> 00:03:48,000 Statistics like this can help the physical  optimization to get a better exclusion plan,   40 00:03:48,000 --> 00:03:50,940 which the logical optimization cannot. 41 00:03:52,380 --> 00:03:55,560 Now we have the physical execution plan it can   42 00:03:55,560 --> 00:04:00,000 be passed into the local and the  distributed executor to execute. 43 00:04:01,320 --> 00:04:03,600 Another thing, we should keep in mind,   44 00:04:03,600 --> 00:04:07,740 is that a client can only connect to one  TiDB server instance at the same time,   45 00:04:08,280 --> 00:04:13,740 which means the relationship between client  session and TiDB server instance is one to one. 46 00:04:14,880 --> 00:04:18,900 The local execution means that  the task is executed on the TiDB   47 00:04:18,900 --> 00:04:21,780 server instance where the session is connected to. 48 00:04:23,100 --> 00:04:26,220 Distributed execution means the distributed SQL   49 00:04:26,220 --> 00:04:29,880 instructions will be sent to  TiKV servers for executions. 50 00:04:31,260 --> 00:04:36,900 We already know that there can be a lot of  TiKV servers in the backend. So multiple   51 00:04:36,900 --> 00:04:41,880 TiKV servers may receive the distributed SQL  instructions and leads parallel execution. 52 00:04:44,100 --> 00:04:48,240 We also have a course available called  TiDB Performance Tuning which covers the   53 00:04:48,240 --> 00:04:53,160 topics including SQL tuning, query  optimizer and the distributed SQL. 54 00:04:54,480 --> 00:04:57,840 If you are interested in the  performance tuning topics on TiDB,   55 00:04:57,840 --> 00:05:00,180 I highly recommend your enrol this course. 56 00:05:02,160 --> 00:05:07,020 One question you might have is that since the  client session can only connect to one TiDB server   57 00:05:07,020 --> 00:05:13,200 at a time, what if this TiDB node is down? In  this case, what should the client application do? 58 00:05:14,100 --> 00:05:18,600 In fact the answer is very simple,  since the TiDB server is stateless,   59 00:05:18,600 --> 00:05:22,620 meaning no business data  persisted on TiDB server node,   60 00:05:23,460 --> 00:05:29,280 when it goes down you can just reconnect and  recreate your session to a healthy TiDB server. 61 00:05:30,300 --> 00:05:32,880 The client session should be able to handle any   62 00:05:32,880 --> 00:05:36,960 unfinished transaction before  and after the reconnecting. 63 00:05:37,620 --> 00:05:40,260 We do have a course talking  able transaction handling. 64 00:05:42,120 --> 00:05:47,580 Next I'm going to show you an example.  I will kill my TiDB server instance in   65 00:05:47,580 --> 00:05:50,340 the playground sandbox, and see what happens. 66 00:05:51,240 --> 00:05:54,120 Two simple client programs  are running at the same time. 67 00:05:54,840 --> 00:05:59,940 One programs keeps inserting new  planets into planets table and the   68 00:05:59,940 --> 00:06:04,740 other one is querying on the same  table. Both application implements   69 00:06:04,740 --> 00:06:10,740 the basic reconnect and a simple transaction  recommit mechanism in case of connection loss. 70 00:06:12,300 --> 00:06:15,060 Let's check the status of  my playground cluster first. 71 00:06:16,200 --> 00:06:22,380 Now we have the PID of each instance, my  target is the TiDB server instance right now. 72 00:06:23,520 --> 00:06:25,920 Let's spin up our little client programs.   73 00:06:26,940 --> 00:06:30,540 The first one is the planet's  creator with random planet names.   74 00:06:34,140 --> 00:06:36,480 Then, let's query against the planet table. 75 00:06:41,400 --> 00:06:47,820 You can see the new planets are being  discovered. Now two tiny programs are running.   76 00:06:48,660 --> 00:06:52,800 Please note both programs are aware  of three TiDB server instances,   77 00:06:53,340 --> 00:06:57,720 they are designed to connect to any TiDB  service in case of the connection failure.   78 00:07:01,680 --> 00:07:05,100 They are written in Python  and very straightforward.   79 00:07:06,000 --> 00:07:07,740 You can go through the code if you like 80 00:07:10,680 --> 00:07:12,660 Let me kill one TiDB instance. 81 00:07:25,620 --> 00:07:30,420 Both programs encountered exceptions  and they did not give up easily.   82 00:07:34,980 --> 00:07:40,860 Within a short time, they can reconnect to  another TiDB server and continue their tasks. 83 00:07:48,600 --> 00:07:50,940 Let me kill one more TiDB instance. 84 00:07:59,760 --> 00:08:02,220 As you can see the failover happens again. 85 00:08:09,660 --> 00:08:14,700 Let me kill the last TiDB instance.  There will be no TiDB server after   86 00:08:14,700 --> 00:08:18,420 this kill operation. As I only  have three TiDB server instance. 87 00:08:23,940 --> 00:08:27,780 You can see now my programs  have no TiDB server to talk to. 88 00:08:29,460 --> 00:08:36,300 My business is literally paused. What  can I do now? Do I lost any data? No,   89 00:08:36,300 --> 00:08:42,660 because the TiDB server is stateless. What I  should do is add TiDB servers back to the cluster   90 00:08:42,660 --> 00:08:51,180 to fit my business requirements. For my dummy  business here, actually one TiDB server is enough. 91 00:08:53,460 --> 00:08:56,100 So I am going to add one TiDB server instance. 92 00:08:56,640 --> 00:08:59,820 This operation is actually  a scaling out operation. 93 00:09:01,200 --> 00:09:06,480 I can do that with very intuitive  command line. Let me run it now. 94 00:09:30,000 --> 00:09:34,260 Everything is back to normal. I  lost three old TiDB instances,   95 00:09:34,260 --> 00:09:37,320 and then one new TiDB instance joined the cluster. 96 00:09:38,100 --> 00:09:40,740 No data is lost in this simple demo. 97 00:09:41,820 --> 00:09:46,020 I added TiDB instance back  manually. In production,   98 00:09:46,020 --> 00:09:51,300 you don't have to do this manually. You have  multiple options to do it in an automatic way. 99 00:09:52,080 --> 00:09:55,440 If you are in TiDB Cloud, it  will be even more convenient.