Docs
tidb
FAQ

FAQ

Q1: How many regions does TiDB currently cover?

TiDB currently supports regions such as North China 1, Shanghai 2, and Guangdong. Users who need to use it in other regions please contact technical support or account manager to enable it.

Q2: Every hour, the business needs to create some intermediate tables, perform calculations, and then delete them. Does the frequent creation and deletion of tables have a big impact on TiDB’s performance?

The impact can be ignored when the data volume is small. In the case of large data volume, the deletion operation takes a long time, and it has a certain impact on GC. If the amount of data that needs to be GC is large, it will affect the write performance. It is recommended to use partitioned tables for intermediate tables to avoid performance impact in this scenario.

Q3: What is the time zone in the slow log?

admin show slow follows the time zone of the host where the service is located, and it can’t be set. It is recommended to use the select statement to query. Select will apply the time zone information you set.

select * from information_schema.slow_query;

admin show slow log top 4;

Q4: For a large table insert into t2 select * from t1; it fails and reports an error ERROR 2013 (HY000): Lost connection to MySQL server during query

This situation is basically Transaction too large

There are restrictions on TiDB transactions:

A single transaction contains no more than 100,000 SQL statements. Each key-value pair does not exceed 6MB, and the total size of the key-value pair does not exceed 100MB.

Q5: Does TiDB support select for update?

It is supported, and it is basically consistent with MySQL when pessimistic locks are used. When optimistic locks are used, TiDB, a distributed database, does not lock data as soon as the select for update transaction starts, but checks for conflicts when other transactions commit. If there is a conflict, a rollback will be performed.

Q6: The transaction is too large (insert… select), or select..for update, as well as network problems, the execution of the transaction will return errors in TiDB, is there any way to distinguish them?

Can be judged according to TiDB’s error codes

Q7: Can TIDB support session-level pessimistic lock?

Execute set @@tidb_txn_mode = ‘pessimistic’;, so that all explicit transactions (i.e., non-autocommit transactions) performed by this session will enter the pessimistic transaction mode.

Q8: The target end of TiDB binlog does not support udb-mysql5.6.41 version under the following conditions

The default index key prefix of udb-mysql5.6.41 is limited to 767 bytes. The key of TiDB’s table design is too long, and an error will be reported during full synchronization.

If you have to use udb-mysql5.6 version, you need to do the following:

  1. Enable the system variable innodb_large_prefix at the target end

    1). The system variable innodb_large_prefix is ON

    2). The system variable innodb_file_format is Barracuda

If the user’s permission is not enough, first adjust your super permissions:

mysql>update mysql.user set super_priv = 'Y' where user = 'root';

mysql>flush privileges;

mysql>set global innodb_large_prefix=on;

mysql>set global innodb_file_format=Barracuda;
  1. The source end needs to modify the table attributes:
mysql> ALTER TABLE TEST ROW_FORMAT=DYNAMIC;

The target end supports: udb-mysql 5.7

Q9: In TiDB, is the effect of setting the table or field to utf8 and setting it to utf8mb4 the same?

For version 2.1.3 and subsequent versions, the default character set is changed from uft8 to utf8mb4. The effect is the same, but to ensure that the backup data and binlog output data can be compatible with other databases, it needs to be explicitly specified as utf8mb4

Q10: Will adding a joint index to TiDB lock the table?

First of all, TiDB does not have a mechanism for locking tables internally: https://pingcap.com/docs-cn/dev/reference/sql/statements/flush-tables/#mysql-%E5%85%BC%E5%AE%B9%E6%80%A7

Secondly, in TiDB, ADD INDEX is an online operation and does not block data reading and writing in the table. https://pingcap.com/docs-cn/dev/reference/sql/statements/add-index/

However, if you are creating an index and the data you want to read and write happens to be the same part of the data, this will be affected, because creating an index requires filling data, which will also involve read and write operations.

Read from historical versions will not affect the reading speed.

Q11: Default time zone of TiDB

After the current instance is created, the default time zone is UTC. If users need CST time, they need to manually set the time zone to +8

SET time_zone = '+8:00';   SET GLOBAL time_zone ='+8:00';

Reconnecting to mysql will take effect

Q12: View whether the process of creating an index in TiDB has ended

You can view the progress of the current job through the “admin show ddl;” statement

Q13: TiDB maximum connection number

The default is 2048, which can be modified through the console

Q14: SQL execution time suddenly becomes longer

Before executing the SQL statement, TiDB will calculate the execution plan through statistical information and choose whether to scan the whole table or get data from the index. If a table has a very large amount of data, the error of TiDB’s selection algorithm is relatively large. As soon as it chooses to scan the whole table, it will seriously affect the performance of the cluster. It is recommended to forcibly use the index

use index(index_name):https://book.tidb.io/session4/chapter6/sql-optimization-cases.html#%E6%A1%88%E4%BE%8B5-sql-%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92%E4%B8%8D%E5%87%86

Q15: How to find the table name through tableID

There is a corresponding relationship in INFORMATION_SCHEMA.TABLES.

Q16: How to view the current version

select tidb_version();

Q17: How to view slow queries

In version 4.0 and later, query the information_schema.cluster_slow_query table, for example:

select query_time, query from information_schema.cluster_slow_query 
  where is_internal = false and time > '2021-05-24 05:00:00' 
  order by query_time desc limit 10;

Before the 4.0 version, you can view the slow query statements of the current connection point through the information_schema.slow_query table. If you want to query slow queries of all nodes, please contact technical support.

Q18: The monitoring graph sees that there are slow queries, but it is impossible to query them in the information_schema.cluster_slow_query table?

You need to use an account with full permissions, it is recommended to use a root account

Q19: How to deal with Error 1298: Unknown or incorrect time zone: ‘UTC’ when creating a synchronization task or synchronizing to MySQL with TiCDC?

This is because the downstream MySQL has not loaded the time zone. You can load the time zone through the mysql_tzinfo_to_sql command, and then you can normally create tasks or synchronize tasks.

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p

Q20: TiDB database error ERROR 1105 (HY000): Out Of Memory Quota how to deal with it?

There are currently two ways to deal with it

  1. Customers set the session variable SET tidb_mem_quota_query = 8 << 30; before executing the oom statement;

  2. Contact technical support and let the backend colleagues modify the tidb configuration file to make it permanently effective.

Performance Data