EN: |+| Now I'm going to look at the cluster of TiDB servers also known as the SQL layer. EN: |+| Thanks to this layer TiDB understands SQL statements. EN: |+| One of the most important features of SQL layer is managing the mapping between the |+| table row in relational database domain and the key value pair in KV domain. EN: |+| Here is how the mapping works. EN: TiDB assigns a table ID to the table. EN: |+| The table ID is an integer that is unique across the cluster. EN: |+| TiDB also assigns a row ID to each row. EN: The row ID is also an integer being unique within the table. EN: |+| In the slide, on the left side, there are three rows with four columns. EN: |+| The first column is the primary key of the table. EN: The KV domain is on the |+| right side. EN: The key is shown in red. EN: The value is enclosed in the square brackets. EN: |+| Additionally, for the row ID, TiDB server has made a small optimization, |+| which is, if the primary key column for your table is integer data type, |+| then SQL layer will use the primary key as the row ID directly to compose the key for the KV record. EN: |+| This is exactly the case shown on a slide. EN: On the other hand, if the primary key column is a non |+| integer data type, the SQL layer will generate and encode a row ID for the key in KV record. EN: |+| We will cover more details related to the row ID in the clustered index section, |+| in another course named TiDB Specific features and transaction control. EN: |+| The row ID can be used effectively in a unique get or point get query. EN: |+| The table prefix can be used in full table scan. EN: From TiKV map point of view, |+| the full table scan in relational domain is an actually a render scan in KV demand. EN: |+| Now let's do a deep dive into a single TiKV server to see what |+| modules the TiDB server has on it and what are the related functions. EN: |+| Suppose the client issues an SQL statement over the network. EN: The front end handles the |+| request is the protocol layer. EN: The protocol layer actually makes the TiDB server a network server. EN: |+| Then you reach the session context stage after you have been authenticated. EN: |+| After you get a session context, the request enters the passing stage. EN: |+| Parser is going to treat your SQL statement text as a logical tree structure, |+| and then analyze your intention, such as what database objects you would like to |+| access and what operations you would like to do on these database objects. EN: |+| After that it will be sent to a logical optimizer which is a rule-based optimizer. EN: The logical |+| optimizer may rewrite your SQL statement into a good shape following a series of rules. EN: |+| This can help the following step to generate a better execution plan. EN: |+| The output is the logical excursion plan. EN: |+| After that it is sent to the physical optimizer which is a cost based optimizer. EN: |+| The physical optimizer is aware of some other important information, |+| such as the statistics of the objects your SQL statement needs directly or indirectly. EN: |+| For example it might include how many rows the table has, |+| what is the average length of each row, and the information about the indexes. EN: |+| Statistics like this can help the physical optimization to get a better exclusion plan, |+| which the logical optimization cannot. EN: |+| Now we have the physical execution plan it can |+| be passed into the local and the distributed executor to execute. EN: |+| Another thing, we should keep in mind, |+| is that a client can only connect to one TiDB server instance at the same time, |+| which means the relationship between client session and TiDB server instance is one to one. EN: |+| The local execution means that the task is executed on the TiDB |+| server instance where the session is connected to. EN: |+| Distributed execution means the distributed SQL |+| instructions will be sent to TiKV servers for executions. EN: |+| We already know that there can be a lot of TiKV servers in the backend. EN: So multiple |+| TiKV servers may receive the distributed SQL instructions and leads parallel execution. EN: |+| We also have a course available called TiDB Performance Tuning which covers the |+| topics including SQL tuning, query optimizer and the distributed SQL. EN: |+| If you are interested in the performance tuning topics on TiDB, |+| I highly recommend your enrol this course. EN: |+| One question you might have is that since the client session can only connect to one TiDB server |+| at a time, what if this TiDB node is down? EN: In this case, what should the client application do? EN: |+| In fact the answer is very simple, since the TiDB server is stateless, |+| meaning no business data persisted on TiDB server node, |+| when it goes down you can just reconnect and recreate your session to a healthy TiDB server. EN: |+| The client session should be able to handle any |+| unfinished transaction before and after the reconnecting. EN: |+| We do have a course talking able transaction handling. EN: |+| Next I'm going to show you an example. EN: I will kill my TiDB server instance in |+| the playground sandbox, and see what happens. EN: |+| Two simple client programs are running at the same time. EN: |+| One programs keeps inserting new planets into planets table and the |+| other one is querying on the same table. EN: Both application implements |+| the basic reconnect and a simple transaction recommit mechanism in case of connection loss. EN: |+| Let's check the status of my playground cluster first. EN: |+| Now we have the PID of each instance, my target is the TiDB server instance right now. EN: |+| Let's spin up our little client programs. EN: |+| The first one is the planet's creator with random planet names. EN: |+| Then, let's query against the planet table. EN: |+| You can see the new planets are being discovered. EN: Now two tiny programs are running. EN: |+| Please note both programs are aware of three TiDB server instances, |+| they are designed to connect to any TiDB service in case of the connection failure. EN: |+| They are written in Python and very straightforward. EN: |+| You can go through the code if you like |+| Let me kill one TiDB instance. EN: |+| Both programs encountered exceptions and they did not give up easily. EN: |+| Within a short time, they can reconnect to another TiDB server and continue their tasks. EN: |+| Let me kill one more TiDB instance. EN: |+| As you can see the failover happens again. |+| Let me kill the last TiDB instance. EN: There will be no TiDB server after |+| this kill operation. EN: As I only have three TiDB server instance. EN: |+| You can see now my programs have no TiDB server to talk to. EN: |+| My business is literally paused. EN: What can I do now? EN: Do I lost any data? EN: No, |+| because the TiDB server is stateless. EN: What I should do is add TiDB servers back to the cluster |+| to fit my business requirements. EN: For my dummy business here, actually one TiDB server is enough. EN: |+| So I am going to add one TiDB server instance. EN: |+| This operation is actually a scaling out operation. EN: |+| I can do that with very intuitive command line. EN: Let me run it now. EN: |+| Everything is back to normal. EN: I lost three old TiDB instances, |+| and then one new TiDB instance joined the cluster. EN: |+| No data is lost in this simple demo. EN: |+| I added TiDB instance back manually. EN: In production, |+| you don't have to do this manually. EN: You have multiple options to do it in an automatic way. EN: |+| If you are in TiDB Cloud, it will be even more convenient.