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 parsing 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 execution 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 execution 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 about 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.