Parameter Tuning

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 参数调优

| username: TiDBer_uI8QIp1t

Do I need to tune parameters like max_allowed_packet, max-connection, innodb_buffer_max_size, etc., after installing TiDB, similar to MySQL?

| username: TiDBer_jYQINSnf | Original post link

There’s no need to adjust max-connection; TiDB allows a large number of connections.
The innodb setting is useless.
max-allowed_packet is somewhat useful and can be adjusted.

| username: zhanggame1 | Original post link

  1. The default GC time is 10 minutes. Consider extending it to allow data recovery within the GC time.

  2. Modify the log retention period; otherwise, too many logs might fill up the hard drive.

  3. The tidb_ddl_reorg_worker_cnt parameter can be increased to speed up index creation.

  4. TiDB’s transaction isolation level is Repeatable Read by default. You can choose to use Read Committed.

  5. Set the time period for statistical analysis.

    You can schedule this statistical analysis to run during database idle times:

    SET GLOBAL tidb_auto_analyze_start_time='23:00 +0800';
    SET GLOBAL tidb_auto_analyze_end_time='00:00 +0800';
    
  6. Adjust TiDB memory parameters.
    If you have enough memory, you can increase the default 1G tidb_mem_quota_query.

  7. Modify the MySQL version number displayed by TiDB to address vulnerability scanning issues.

  8. Set the session connection timeout for TiDB.

  9. Check the permissions and size settings of the temporary space.

image

| username: TiDBer_uI8QIp1t | Original post link

Are there any other variables that need to be adjusted?

| username: Kongdom | Original post link

:yum: This needs to be considered in conjunction with your own hardware situation. If the time is too long, it may affect query performance, right?

| username: zhanggame1 | Original post link

Depending on the importance of the data, if the data is extremely important, then efficiency might not be the top priority.

| username: Kongdom | Original post link

Indeed, it is necessary to configure it according to your own business needs.

| username: Miracle | Original post link

I think each environment is different, so there shouldn’t be any fixed parameter adjustments. You can start by using the default settings for a period of time and then adjust the corresponding parameters based on the issues that arise.

| username: forever | Original post link

By default, running for a period of time, it would be even better if there was something like Oracle’s AWR to analyze which parameters need to be adjusted.

| username: TiDBer_jYQINSnf | Original post link

Basically, you don’t need to change anything. Running with the default parameters works just fine.

| username: 路在何chu | Original post link

First run it with the default settings, and then make adjustments if performance issues arise. The parameters are automatically set based on the machine configuration.

| username: DBAER | Original post link

Adjusting the GC time, recovery from misoperation is relatively quick.
Adjust tidb_mem_quota_query.

| username: 小于同学 | Original post link

If there are no issues, there’s no need. Adjust when needed.

| username: dba远航 | Original post link

This needs to be adjusted according to the actual situation.

| username: residentevil | Original post link

Through multi-dimensional stress testing of the TIDB cluster, it was found that the parameter storage.block-cache.capacity must be adjusted, otherwise TIKV will frequently encounter OOM (Out of Memory) issues.

| username: TiDBer_uI8QIp1t | Original post link

For example, with three nodes, each with 64G, what should the adjustment be?

| username: TiDBer_rvITcue9 | Original post link

If there are no performance issues, there’s no need to adjust.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.