1 00:00:00,000 --> 00:00:12,540 [Music] Welcome to course 101  TiDB Technical Essentials. 2 00:00:12,540 --> 00:00:18,000 This is module two, TiDB server. My name is  Jeff and I'm your instructor for this module. 3 00:00:18,660 --> 00:00:25,620 When you complete this module, you will be able  to describe the TiDB server architecture, describe   4 00:00:25,620 --> 00:00:33,240 the role of TiDB server, describe the TiDB server  process, and describe the TiDB server cache. 5 00:00:34,440 --> 00:00:40,680 In this module, we will begin with the internal  thread pool and cache of TiDB server and introduce   6 00:00:40,680 --> 00:00:46,200 the key functions and hierarchy of the TiDB  server. First let's get to know the core thread   7 00:00:46,200 --> 00:00:52,560 pool and cache of TiDB server. Let's take a  look at the main functions of TiDB server. 8 00:00:53,640 --> 00:00:59,580 Let's summarize the core functions of TiDB server  one more time. TiDB server is responsible for   9 00:00:59,580 --> 00:01:07,080 the following. One, handling client connections.  Users are authenticated by username and password. 10 00:01:07,080 --> 00:01:14,640 Database privileges are primarily enforced by the  protocol layer. Two, parsing and compiling SQL   11 00:01:14,640 --> 00:01:21,120 statements. The TiDB server parses and compiles  SQL statements during the compilation process. 12 00:01:21,120 --> 00:01:27,480 It optimizes and generates a distributed  execution plan. These tasks are performed by   13 00:01:27,480 --> 00:01:34,980 the parse and compile modules. Three, converting  relational data to key value pairs. Since data   14 00:01:34,980 --> 00:01:40,920 is stored on TiKV in the form of key value pairs.  There's a big difference between relational data,   15 00:01:40,920 --> 00:01:49,620 such as table index row and a key value structure.  In order to execute SQL, TiDB must convert   16 00:01:49,620 --> 00:01:57,660 relational data to key value pairs so that it can  be stored in TiKV. Four, SQL statements execution.  17 00:01:58,620 --> 00:02:06,120 Using the compiled execution plan, SQL statements  are executed on TiKV in batches. The data is read   18 00:02:06,120 --> 00:02:13,260 by the executor module, the KV module and the  distSQL module. And the transaction related   19 00:02:13,260 --> 00:02:19,560 operations are completed by the transaction module  and the KV module. KV meaning key value module.  20 00:02:20,340 --> 00:02:29,040 Five, online DDL execution. Data definition  language (DDL) operations do not lock tables. 21 00:02:29,880 --> 00:02:36,120 The work is performed by the schema load  module, worker module, and the start job module. 22 00:02:36,840 --> 00:02:43,500 Six, garbage collection. The garbage collection  background module is responsible for recovering   23 00:02:43,500 --> 00:02:49,500 old versions of data that have expired. We  will cover this in detail in the TiKV module.   24 00:02:49,500 --> 00:02:55,500 And finally, cached table. Remember  this is a new feature in TiDB 6.0.   25 00:02:56,400 --> 00:03:02,520 This function is suitable for hot spot sub-tables  that are frequently accessed and rarely modified. 26 00:03:02,520 --> 00:03:08,700 That is the data of the entire table  is loaded in the TiDB server memory.   27 00:03:09,240 --> 00:03:15,600 Since the query doesn't have to read from TiKV, it  is significantly faster than having to go to disk. 28 00:03:16,740 --> 00:03:22,560 Parsing and compiling are important functions  in TiDB server. Let's introduce them separately.   29 00:03:22,560 --> 00:03:27,600 First the parse module. Parse  uses lexer in this case Lex   30 00:03:27,600 --> 00:03:31,140 to parse SQL statements into tokens one by one. 31 00:03:32,040 --> 00:03:39,600 Next it uses Yacc, yet another compiler compiler,  to generate a tree structure that is an abstract 32 00:03:39,600 --> 00:03:46,440 syntax tree. This is usually referred to as  AST. It can then be identified and compiled as   33 00:03:46,440 --> 00:03:52,440 shown in the diagram. Next let's look at parse and  compile SQL statements. This is the compile phase. 34 00:03:53,160 --> 00:03:58,620 After the SQL statement is parsed, the  AST syntax tree is generated and passed   35 00:03:58,620 --> 00:04:02,700 to the compile module. The compile  module performs the following tasks.   36 00:04:03,420 --> 00:04:08,520 First, it performs validation on the query  and checks the legality and name bindings.   37 00:04:09,300 --> 00:04:15,360 Next is logic optimization. This process  makes logical transformations based on the   38 00:04:15,360 --> 00:04:20,760 equivalent exchange rules of relational algebra.  It consists of a series of optimization rules. 39 00:04:21,360 --> 00:04:28,200 The logical optimization rules of TiDB include  column pruning, maximum and minimum elimination, 40 00:04:28,980 --> 00:04:36,600 projection elimination, predicate push down,  sub query decorrelation, outer join elimination, 41 00:04:36,600 --> 00:04:43,380 aggregation elimination, and more.  Once logical optimization completes,   42 00:04:43,380 --> 00:04:49,500 physical optimization begins based on logical  optimization results. Physical optimization   43 00:04:49,500 --> 00:04:55,620 considers the distribution of data and decides  how to select physical operators. Part of the   44 00:04:55,620 --> 00:05:02,640 physical operation process involves selecting the  optimal execution path. This process depends on   45 00:05:02,640 --> 00:05:07,980 catalog statistics. The more accurate the  statistics, the better the execution plan.   46 00:05:09,000 --> 00:05:16,620 TiDB optimizes queries using techniques, such as  data read method, join method, table connection   47 00:05:16,620 --> 00:05:24,960 order and ordering. Finally, TiDB server creates  an implementation planned. It's worth noting that   48 00:05:24,960 --> 00:05:29,280 physical optimization requires querying the  metadata of the tables that participate in   49 00:05:29,280 --> 00:05:35,700 the query. Metadata is used for legality checking  and statistics. The metadata and statistics of the   50 00:05:35,700 --> 00:05:41,880 table are stored in the TiDB server cache. Another very important job the TiDB server   51 00:05:41,880 --> 00:05:48,840 performs is the conversion of relational data to  key value pairs. Remember TiDB server works with   52 00:05:48,840 --> 00:05:54,960 relational data and TiKV works with key value  pairs. Let's use an animation to explain the   53 00:05:54,960 --> 00:06:02,280 mutual transformation of relational data to key  value pairs. The diagram shows a relational table   54 00:06:02,280 --> 00:06:09,060 with a numeric column as the primary key. We mean  numbers. The remaining columns, name, birthday,   55 00:06:09,060 --> 00:06:17,400 cell phone, and score make up the value part. The  new column along with the dot dot dot signify the   56 00:06:17,400 --> 00:06:26,040 separation of key and value. Unfortunately our  primary key can only uniquely identify a single   57 00:06:26,040 --> 00:06:32,940 row in a single table. Our database will have many  tables. We need a way to identify a single row in   58 00:06:32,940 --> 00:06:40,740 a specific table. So let's add a column that will  uniquely identify a table. Our new column is table   59 00:06:40,740 --> 00:06:47,340 No which really means table number and that will  handle this. When we combine table No. and No.,   60 00:06:47,340 --> 00:06:54,240 think of it as row ID. Using this approach the  row of data has a database level global key.   61 00:06:55,080 --> 00:06:59,460 I know what you're thinking, what about  tables that don't have a primary key? 62 00:07:00,420 --> 00:07:07,620 Well, TiDB adds a hidden column row ID to all  relational tables, if the table has an integer  63 00:07:07,620 --> 00:07:12,840 primary key. Then, the value of the primary  key will be used as the row ID. Otherwise,   64 00:07:12,840 --> 00:07:19,980 the generated row ID will act as the primary  key. Table ID is unique in the whole cluster,   65 00:07:19,980 --> 00:07:26,700 index ID, row ID is unique in the table.  And these IDs are of type integer 64.   66 00:07:27,240 --> 00:07:32,460 Now that we have relational data in key  value format. The following question arises,   67 00:07:33,000 --> 00:07:38,940 how are these key values organized?  Our diagram shows us that combining   68 00:07:38,940 --> 00:07:43,620 a table ID with a row ID allows us to  uniquely identify the rows in our table. 69 00:07:44,460 --> 00:07:51,060 We don't show it here, but imagine that there are  multiple tables stored in the TiKV key value pair   70 00:07:51,060 --> 00:07:58,680 structure. The combination of table ID and row  ID will enable TiDB to find any specific row.  71 00:08:00,300 --> 00:08:05,700 We call a set of key values in the database a region. A region is also the logical unit   72 00:08:05,700 --> 00:08:11,640 in which we read data. We will see on the next  diagram that the data in a region is replicated. 73 00:08:11,640 --> 00:08:18,000 Obviously, storing all of our data in a single  region doesn't make a lot of sense. As a result,   74 00:08:18,000 --> 00:08:27,180 when a region reaches a certain size limit it  splits. By default, one region in TiDB is 96 MB 75 00:08:27,180 --> 00:08:33,360 maximum. That is when 96 megabytes is reached  the region will be split into two regions. 76 00:08:33,960 --> 00:08:39,360 this avoids the problem that the region is too  long. It can also prevent a region from becoming  77 00:08:39,360 --> 00:08:45,300 hot. Hot regions have lots of activity. This can  cause performance problems because the activity   78 00:08:45,300 --> 00:08:53,160 isn't spread across TiKV nodes. The data in  a region is replicated. By default, there are   79 00:08:53,160 --> 00:08:59,820 three replicas and the replicas are distributed to  multiple TiKV storage nodes. This ensures that our   80 00:08:59,820 --> 00:09:05,640 data is naturally dispersed and distributed.  And like we said, spreading our tables data   81 00:09:05,640 --> 00:09:12,000 across multiple TiKV nodes increases parallelism,  and therefore performance of our TiDB database. 82 00:09:14,400 --> 00:09:20,040 The region distribution information is stored  in the PD. And each time the SQL statement is   83 00:09:20,040 --> 00:09:24,600 executed, the location of the region, where  the data can be found is read from the PD.  84 00:09:25,200 --> 00:09:30,840 Our diagram shows that the data stored in the  region is replicated across multiple TiKV nodes.  85 00:09:31,680 --> 00:09:37,800 The diagram skips the replicas too. Think  of each region as really a replica of a   86 00:09:37,800 --> 00:09:41,700 single region's data. We will cover  this in detail in a later module.   87 00:09:43,500 --> 00:09:49,680 The thread pool modules related to SQL read and  write are shown on this diagram. We will introduce   88 00:09:49,680 --> 00:09:55,320 the SQL reads and writes process here, but we will  cover them in detail in module five, TiDB database   89 00:09:55,320 --> 00:10:02,100 SQL execution flow. So let's take a look at the  relevant modules. The executor module is shown   90 00:10:02,100 --> 00:10:07,680 towards the top middle portion of the diagram.  Execution begins according to the tree execution   91 00:10:07,680 --> 00:10:13,800 plan, generated by compile. Each layer calls  the methods of the next layer to obtain results. 92 00:10:14,880 --> 00:10:22,860 Next the distSQL module is below the executor. In  order to avoid complex logic, coupling with the   93 00:10:22,860 --> 00:10:30,360 SQL layer, TiDB extracts a unified distributed  query layer interface, the distSQL module. 94 00:10:31,380 --> 00:10:36,540 The distSQL module is a layer of abstraction  between TiDB server and the coprocessor.  95 00:10:37,080 --> 00:10:42,900 The coprocessor can handle aggregation,  filtering, and other tasks. Since it lives   96 00:10:42,900 --> 00:10:48,660 in the TiKV component, it increases performance by  minimizing the volume of data being passed between   97 00:10:48,660 --> 00:10:56,220 it and the TiDB server. DistSQL encapsulates the  lower coprocessor request and provides a simple   98 00:10:56,220 --> 00:11:03,300 select method for the upper layer. It performs  a single table calculation task. The top level   99 00:11:03,300 --> 00:11:10,260 SQL statement may be complex operators such as  join and subquery involving many tables, while   100 00:11:10,260 --> 00:11:17,220 DistSQL only involves data from a single table.  A distSQL request involves multiple regions,   101 00:11:17,220 --> 00:11:22,260 and we want to execute a coprocessor  request once for each region involved.   102 00:11:23,400 --> 00:11:31,320 Next the TiKV client module is shown below  DistSQL. The TiKV client module, and in this case,   103 00:11:31,320 --> 00:11:36,600 it's going to be TiDB server, but it doesn't  have to be, is primarily responsible for sending   104 00:11:36,600 --> 00:11:44,280 read write requests to the TiKV module. The first type is raw KV request which is   105 00:11:44,280 --> 00:11:50,040 a simple request to obtain a single row of data,  such as an equivalent query based on a primary   106 00:11:50,040 --> 00:11:58,380 index or a unique index. The second type is  coprocessor request, which requires that the   107 00:11:58,380 --> 00:12:03,960 computation also be pushed down to TiKV's  coprocessor framework, as we said before,   108 00:12:04,620 --> 00:12:10,260 things like filtering, aggregation,  or column projection and other things.  109 00:12:11,100 --> 00:12:18,060 Next, the transaction module is shown towards the  top left side of our diagram. As its name implies   110 00:12:18,060 --> 00:12:25,140 the transaction module handles transactions.  This includes two-stage commit, pessimistic lock,   111 00:12:25,140 --> 00:12:33,660 optimistic lock, management, etc. Next, we have  the KV module. It's towards the middle left of our   112 00:12:33,660 --> 00:12:41,820 diagram. The KV module handles transaction related  requests KV. For example KV get and batch get. 113 00:12:42,900 --> 00:12:48,840 Finally, we have the PD client module. You can  find it in the upper right hand side of the   114 00:12:48,840 --> 00:12:55,740 TiDB server. It is a module in the TiDB server  that handles communication with the PD cluster. 115 00:12:57,000 --> 00:13:03,840 TiDB database supports online data definition  language commands. To save time, we call this DDL.  116 00:13:04,500 --> 00:13:09,840 Online DDL, means that DDL operations,  such as create table, create indexes,   117 00:13:10,440 --> 00:13:15,480 do not lock tables. And data  manipulation language statements,   118 00:13:15,480 --> 00:13:21,480 or DML, are not blocked. Let's take a look at  the various modules required to execute DDL.   119 00:13:22,800 --> 00:13:30,780 First, the entire TiDB database only allows one  TiDB server to perform DDL operations at a time. 120 00:13:31,320 --> 00:13:36,720 In order for a TiDB server to execute  DDL, it must be assigned the owner role. 121 00:13:37,680 --> 00:13:44,940 Although every TiDB server has a worker module,  that can handle DDL operations, it can only do so,  122 00:13:44,940 --> 00:13:51,900 if assigned the owner role. If you have more than  one TiDB server in your TiDB database, then these   123 00:13:51,900 --> 00:13:58,320 TiDB servers will take turns assuming the owner  role. The chosen TiDB server will have the owner   124 00:13:58,320 --> 00:14:03,360 roll for a fixed time, and this is by-election.  This time is called the term of office.  125 00:14:04,200 --> 00:14:07,980 When a TiDB server has the owner  role and is in the term of office,   126 00:14:07,980 --> 00:14:10,440 then it handles DDL operations. 127 00:14:11,400 --> 00:14:17,460 The current owner role information of the database  is stored in the PD. Remember the PD is the brains   128 00:14:17,460 --> 00:14:23,820 of the database, and the workers module can check  whether the TiDB server in which it is located is   129 00:14:23,820 --> 00:14:30,360 currently the owner. When the term of an owner  expires, PD will initiate the selection of a new   130 00:14:30,360 --> 00:14:37,140 owner. Secondly, in addition to the worker  module, there's also a schema load module.   131 00:14:37,140 --> 00:14:42,840 This module is used to load the latest schema  information after a TiDB server assumes the owner   132 00:14:42,840 --> 00:14:48,600 role. This ensures that the new owner role TiDB  server has the most up-to-date schema information.  133 00:14:49,860 --> 00:14:56,940 Finally, the start job module is responsible  for receiving DDL requests and assigning IDs   134 00:14:56,940 --> 00:15:03,180 to DDL operations. Each DDL operation,  assigned an ID, is called a job.   135 00:15:03,900 --> 00:15:12,780 The start job will persist the job to the queue  in TiKV. TiDB's transaction implementation uses   136 00:15:12,780 --> 00:15:19,620 a multi-version concurrency control, or  MVCC mechanism, when newly written data   137 00:15:19,620 --> 00:15:26,100 overwrites the old data. The old data is not  replaced, it is kept at the same time as the   138 00:15:26,100 --> 00:15:30,960 newly written data and the versions are  distinguished by a more recent timestamp,   139 00:15:30,960 --> 00:15:36,480 in other words the version of the data with the  most recent timestamp is the one that is used. 140 00:15:37,260 --> 00:15:43,560 The garbage collector's task is to clean up all  data that is no longer needed. The principles and   141 00:15:43,560 --> 00:15:50,040 implementation of MVCC and garbage collection  processing will be covered in the TiKV module. 142 00:15:51,600 --> 00:15:57,120 One TiDB instance in the TiDB cluster will  be elected as the garbage collection leader. 143 00:15:57,120 --> 00:16:03,300 The garbage collection leader controls garbage  collection for the entire TiKV cluster. 144 00:16:04,500 --> 00:16:11,400 Garbage collection is triggered periodically.  At each garbage collection point, TiDB server   145 00:16:11,400 --> 00:16:17,040 first calculates a timestamp called save  point. By default, garbage collection is   146 00:16:17,040 --> 00:16:22,380 triggered every 10 minutes. That is the default  garbage collection lifetime is 10 minutes,   147 00:16:22,380 --> 00:16:28,080 and the safe point is calculated as the  current time minus garbage collection lifetime. 148 00:16:29,640 --> 00:16:35,940 Next let's look at TiDB server cache. The  TiDB server cache is greedy and uses all of   149 00:16:35,940 --> 00:16:40,320 the server's available memory by default.  First let's look at where memory is used.  150 00:16:41,580 --> 00:16:46,860 When complex SQL queries such as hash joins  and queries with many sub queries are used,   151 00:16:46,860 --> 00:16:53,340 in the execution plan, TiDB server will use  a lot of cache, when these operations cannot   152 00:16:53,340 --> 00:16:59,220 be performed entirely by co-processors that  is operations pushed down to the TiKV node,   153 00:16:59,220 --> 00:17:06,660 the data must be sorted in TiDB's server cache  before it can be returned to the user. Update   154 00:17:06,660 --> 00:17:12,660 and delete processing can involve a huge amount  of data and therefore will consume a lot of cache.   155 00:17:13,920 --> 00:17:18,660 Threads are cached in TiDB server, and  if there are many active threads that   156 00:17:18,660 --> 00:17:25,680 will also use a lot of the server cache. The table metadata, information schema,   157 00:17:25,680 --> 00:17:33,840 user authentication, authorization data, and  statistics are also cached. So what are the   158 00:17:33,840 --> 00:17:40,680 parameters that we can use to effectively manage  the cache? The first one is tidb_mem_quota_query.  159 00:17:42,120 --> 00:17:48,000 This parameter controls the default cache usage  of each query. There are additional parameters   160 00:17:48,000 --> 00:17:53,700 that can be used to alter the TiDB server cache.  You can find these in the TiDB documentation.   161 00:17:54,360 --> 00:18:00,120 Look in the configure memory  usage section. Next, oom_action. 162 00:18:01,140 --> 00:18:08,520 If you set oom_action to cancel, memory usage of  a SQL statement that exceeds a certain threshold,   163 00:18:08,520 --> 00:18:13,500 will immediately interrupt the execution of the  SQL statement and return an error to the client.   164 00:18:13,500 --> 00:18:20,040 The error message will specify in detail, each  physical execution that takes up too much memory.   165 00:18:20,880 --> 00:18:28,920 In TiDB version 6.0, PingCAP introduced the cached  table. This is also called the TiDB hotspot table   166 00:18:28,920 --> 00:18:35,640 cache. The entire table's data is loaded into  TiDB server memory. When we query the cache table,   167 00:18:35,640 --> 00:18:41,760 we read directly from memory. This improves  performance. You're probably asking yourself, 168 00:18:41,760 --> 00:18:43,560 How do I cache a table?   169 00:18:44,220 --> 00:18:49,620 It's easy. let's assume you have a table  named user. This table is read often 170 00:18:49,620 --> 00:18:56,040 and rarely updated. We can empower this table to  use cache by issuing an alter table command. The   171 00:18:56,040 --> 00:19:03,180 command is alter table users cache semicolon.  The TiDB hotspot table cache, the amount of   172 00:19:03,180 --> 00:19:09,240 data in the table is very small. Currently TiDB  has a limit of 64 megabytes per cache table. 173 00:19:09,900 --> 00:19:15,660 The alter table cash statement will fail if the  table contains more than 64 megabytes of data. 174 00:19:15,660 --> 00:19:19,200 It's important to note that at  least a lease is added to the   175 00:19:19,200 --> 00:19:23,580 cache table and that the lease duration  is determined by the system parameter.   176 00:19:24,480 --> 00:19:34,320 The tidb_table_cache_lease definition defaults  to 3, and can range from 1 to 10 seconds. In the   177 00:19:34,320 --> 00:19:41,220 case of a graph the timing starts when the table  begins loading from KV's cache to the TiDB server.  178 00:19:43,200 --> 00:19:48,600 During the lease time we can read the table  in the cache on the TiDB server but write   179 00:19:48,600 --> 00:19:54,420 operations are blocked. In other words, the table  cannot be modified while the lease is active. 180 00:19:55,980 --> 00:20:02,100 When the lease expires the data in the cache  also expires. At this time all read operations   181 00:20:02,100 --> 00:20:09,480 will retrieve data from the TiKV node and write  operations can be performed. When the data is   182 00:20:09,480 --> 00:20:15,060 updated the lease starts to be re-timed and  the write operations continue to be blocked. 183 00:20:15,600 --> 00:20:19,680 At this time the data is reloaded  into the TiDB server cache,   184 00:20:19,680 --> 00:20:24,060 and the read operation can retrieve  data from the TiDB server cache.   185 00:20:24,780 --> 00:20:28,320 This is a great time to review the  hot table cache use constraints. 186 00:20:29,220 --> 00:20:33,300 Currently, TiDB has a limit of  64 megabytes per cache table. 187 00:20:34,380 --> 00:20:37,680 Here are some typical use  cases for TiDB cache tables.   188 00:20:38,520 --> 00:20:43,740 Configuration tables for applications. These  are typically read every time the application   189 00:20:43,740 --> 00:20:49,140 starts. Perfect use of a cache table.  A table that stores exchange rates and   190 00:20:49,140 --> 00:20:54,600 financial scenarios. This table is not updated  in real time and is only updated once a day.   191 00:20:55,500 --> 00:21:00,540 Branch bank or branch information  forms which rarely adds new entries. 192 00:21:01,440 --> 00:21:08,040 Anything that has a lookup table is also a  candidate for this. Direct DDL operations on cache   193 00:21:08,040 --> 00:21:14,340 tables are not supported and the cache needs to be  shut down first before you can do DDL operations.  194 00:21:15,480 --> 00:21:24,360 For tables that load slowly or are rarely  modified, TiDB table cache lease can be extended   195 00:21:24,360 --> 00:21:30,420 appropriately to maintain stable read performance.  We have covered a lot of material during this   196 00:21:30,420 --> 00:21:35,100 module. I feel confident that we have met all  of the objectives defined at the beginning.   197 00:21:36,720 --> 00:21:43,440 First, we can describe the overall architecture of  TiDB server. We can describe the main functions of   198 00:21:43,440 --> 00:21:50,400 TiDB server and finally we can describe TiDB  server modules and cached table features. 199 00:21:52,980 --> 00:21:57,720 Thank you for taking the time to view this  module, we hope you enjoyed it and learned a lot.