Performance Optimization

After a TDengine cluster has been running for a long enough time, because of data insertion, table deletion and deletion of expired data, there may be fragments in data files and query performance may be impacted. To resolve the problem of fragments, since version 2.1.3.0 a new SQL command COMPACT can be used to defragment data files.

  1. COMPACT VNODES IN (vg_id1, vg_id2, ...)

COMPACT can be used to defragment one or more vgroups. The defragmentation work will be scheduled in the task queue for execution by TDengine. SHOW VGROUPS command can be used to get the vgroup ids to be used in COMPACT command. There is a column compacting in the output of SHOW GROUPS to indicate the compaction status of the vgroup: 2 means the vgroup is waiting in task queue for compaction, 1 means compaction is in progress, and 0 means the vgroup has not been scheduled for compaction.

Please note that a lot of disk I/O is required for defragementation operations. During defragmentation the performance may be impacted significantly for data insertion and query. Data insertion may even be blocked for very short periods, in extreme cases.

Optimize Storage Parameters

The data in different use cases may have different characteristics, such as the days to keep, number of replicas, collection interval, record size, number of collection points, compression or not, etc. To achieve best efficiency in storage, the parameters in the table below can be used. All of them can either be configured in taos.cfg, as default parameters, or can be set in the command create database. For detailed definition of these parameters please refer to Configuration Parameters.

#ParameterUnitDefinitionValue RangeDefault Value
1daysDayThe time range of the data stored in a single data file1-365010
2keepDayThe number of days the data is kept in the database1-365003650
3cacheMBThe size of each memory block1-12816
4blocksNoneThe number of memory blocks used by each vnode3-100006
5quorumNoneThe number of required confirmations in case of multiple replicas1-21
6minRowsNoneThe minimum number of rows in a data file10-1000100
7maxRowsNoneThe maximum number of rows in a data file200-100004096
8compNoneWhether to compress the data0:uncompressed; 1: One Phase compression; 2: Two Phase compression2
9walLevelNonewal sync level (named as “wal” in create database )1:wal enabled without fsync; 2:wal enabled with fsync1
10fsyncmsThe time to wait for invoking fsync when walLevel is set to 2; 0 means no wait0-3000
11replicanoneThe number of replications1-31
12precisionnoneTime precisionms: millisecond; us: microsecond;ns: nanosecondms
13updatenoneWhether to allow updating data0: not allowed; 1: a whole row must be updated; 2: a portion of columns in a row can be updated0
14cacheLastnoneWhether the latest data of a table is cached in memory0: not cached; 1: the last row is cached; 2: the latest non-NULL value of each column is cached0

Even for a specific use case, there may be multiple kinds of data with different characteristics. In this case it’s best to put data with the same characteristics in the same database. There may be multiple databases in a system and each database can be configured with different storage parameters to achieve the best performance. The above parameters can be used when creating a database to override the default setting in the configuration file.

  1. CREATE DATABASE demo DAYS 10 CACHE 32 BLOCKS 8 REPLICA 3 UPDATE 1;

The above SQL statement creates a database named demo, in which each data file stores 10 days of data, the size of each memory block is 32 MB and 8 blocks are allocated to each vnode, there are 3 replicas and update operations are allowed. All other parameters not specified in the command, will default to the values in the configuration file taos.cfg.

Once a database is created, only some parameters can be changed and be effective immediately while others are can’t.

ParameterAlterableValue RangeSyntax
name
create time
ntables
vgroups
replicaYES1-3ALTER DATABASE <dbname> REPLICA n
quorumYES1-2ALTER DATABASE <dbname> QUORUM n
days
keepYESdays-365000ALTER DATABASE <dbname> KEEP n
cache
blocksYES3-1000ALTER DATABASE <dbname> BLOCKS n
minrows
maxrows
wal
fsync
compYES0-2ALTER DATABASE <dbname> COMP n
precision
status
update
cachelastYES0 | 1 | 2 | 3ALTER DATABASE <dbname> CACHELAST n

Explanation: Prior to version 2.1.3.0, taosd server process needs to be restarted for these parameters to take in effect if they are changed using ALTER DATABASE.

When trying to join a new dnode into a running TDengine cluster, all the parameters related to the cluster in the new dnode configuration must be consistent with the cluster, otherwise it can’t join the cluster. The parameters that are checked when joining a dnode are listed below. For detailed definition of these parameters please refer to Configuration Parameters.

  • numOfMnodes
  • mnodeEqualVnodeNum
  • offlineThreshold
  • statusInterval
  • maxTablesPerVnode
  • maxVgroupsPerDb
  • arbitrator
  • timezone
  • balance
  • flowctrl
  • slaveQuery
  • adjustMaster

For the convenience of debugging, the log setting of a dnode can be changed temporarily. The temporary change will be lost once the server is restarted.

  1. ALTER DNODE <dnode_id> <config>
  • dnode_id: from output of “SHOW DNODES”
  • config: the parameter to be changed, as below
    • resetlog: close the old log file and create the new one
    • debugFlag: 131 (INFO/ERROR/WARNING), 135 (DEBUG), 143 (TRACE)

For example:

  1. alter dnode 1 debugFlag 135;