docs/en/14-reference/03-taos-sql/02-database.md
CREATE DATABASE [IF NOT EXISTS] db_name [database_options]
database_options:
database_option ...
database_option: {
VGROUPS value
| PRECISION {'ms' | 'us' | 'ns'}
| REPLICA value
| BUFFER value
| PAGES value
| PAGESIZE value
| CACHEMODEL {'none' | 'last_row' | 'last_value' | 'both'}
| CACHESIZE value
| COMP {0 | 1 | 2}
| DURATION value
| MAXROWS value
| MINROWS value
| KEEP value
| KEEP_TIME_OFFSET value
| STT_TRIGGER value
| SINGLE_STABLE {0 | 1}
| TABLE_PREFIX value
| TABLE_SUFFIX value
| DNODES value
| TSDB_PAGESIZE value
| WAL_LEVEL {1 | 2}
| WAL_FSYNC_PERIOD value
| WAL_RETENTION_PERIOD value
| WAL_RETENTION_SIZE value
| SS_KEEPLOCAL value
| SS_CHUNKPAGES value
| SS_COMPACT value
| COMPACT_INTERVAL value
| COMPACT_TIME_RANGE value
| COMPACT_TIME_OFFSET value
}
:::note
The following parameters are available in TDengine Enterprise only.
:::
COMPACT_INTERVAL: Interval at which to trigger automatic database compaction. The default value is 0, which disables automatic database compaction. To enable automatic database compaction, specify a value between 10m and KEEP2. The time unit of the value can be minutes (m), hours (h), or days (d), and the default unit is days.
Note that time slices start from 1970-01-01T00:00:00Z.
Automatic database compaction is not triggered when an existing compaction task is already running on the database.
COMPACT_TIME_RANGE: Time range for automatic compact tasks. The default value is 0, 0, which indicates the range from -KEEP2 to -DURATION. You can specify a custom time range starting at or after -KEEP2 and ending at or before -DURATION. The time unit of the values in this range can be minutes (m), hours (h), or days (d), and the default unit is days.
For example, -300, -200 would compact data between 300 and 200 days in the past each time automatic compaction is triggered. If the duration parameter of the database is the default 10 days, -300, -5 would return an error because the second value (5 days in the past) is more recent than the value of -DURATION (10 days in the past).
Note that these values are negative numbers, indicating that the time range to be compacted is in the past.
COMPACT_TIME_OFFSET: Time offset relative to local time at which to trigger automatic database compaction. The default value is 0. You can enter an offset between 0 and 23 to trigger compaction after the specified number of hours.
For example, if COMPACT_INTERVAL is 1d and COMPACT_TIME_OFFSET is 0, automatic compact is triggered at 00:00 every day. If COMPACT_TIME_OFFSET is 2, automatic compact is triggered at 02:00 every day.
create database if not exists db vgroups 10 buffer 10
The above example creates a database named db with 10 vgroups, where each vnode is allocated 10MB of write buffer.
USE db_name;
Use/switch database.
DROP DATABASE [IF EXISTS] db_name
Deletes the database. All tables contained in the Database will be deleted, and all vgroups of that database will also be destroyed, so use with caution!
ALTER DATABASE db_name [alter_database_options]
alter_database_options:
alter_database_option ...
alter_database_option: {
CACHEMODEL {'none' | 'last_row' | 'last_value' | 'both'}
| CACHESIZE value
| BUFFER value
| PAGES value
| REPLICA value
| STT_TRIGGER value
| WAL_LEVEL value
| WAL_FSYNC_PERIOD value
| KEEP value
| WAL_RETENTION_PERIOD value
| WAL_RETENTION_SIZE value
| MINROWS value
}
The command to modify database parameters is simple, but the difficulty lies in determining whether a modification is needed and how to modify it. This section describes how to judge whether the cachesize is sufficient.
You can view the specific values of these cachesize through select * from information_schema.ins_databases;.
You can view cacheload through show <db_name>.vgroups;
If cacheload is very close to cachesize, then cachesize may be too small. If cacheload is significantly less than cachesize, then cachesize is sufficient. You can decide whether to modify cachesize based on this principle. The specific modification value can be determined based on the available system memory, whether to double it or increase it several times.
:::note Other parameters are not supported for modification in version 3.0.0.0
:::
SHOW DATABASES;
SHOW CREATE DATABASE db_name \G;
Commonly used for database migration. For an existing database, it returns its creation statement; executing this statement in another cluster will result in a Database with the exact same settings.
SELECT * FROM INFORMATION_SCHEMA.INS_DATABASES WHERE NAME='db_name' \G;
Lists the configuration parameters of the specified database, displaying one parameter per line.
TRIM DATABASE db_name;
Deletes expired data and reorganizes data according to the multi-level storage configuration.
TRIM DATABASE db_name WAL;
Delete expired WAL logs. Using trim wal ignores the vgroup keep_version restriction.
FLUSH DATABASE db_name;
Flushes data in memory to disk. Executing this command before shutting down a node can avoid data replay after restart, speeding up the startup process.
REDISTRIBUTE VGROUP vgroup_no DNODE dnode_id1 [DNODE dnode_id2] [DNODE dnode_id3]
Adjusts the distribution of vnodes in a vgroup according to the given list of dnodes. Since the maximum number of replicas is 3, a maximum of 3 dnodes can be entered.
BALANCE VGROUP
Automatically adjusts the distribution of vnodes in all vgroups of the cluster, equivalent to performing data load balancing at the vnode level for the cluster.
SHOW db_name.ALIVE;
Query the availability status of the database db_name, with return values of 0 (unavailable), 1 (fully available), or 2 (partially available, indicating that some VNODEs in the database are available while others are not).
select * from INFORMATION_SCHEMA.INS_DISK_USAGE where db_name = 'db_name'
View the disk usage of each module in the DB.
SHOW db_name.disk_info;
View the compression ratio and disk usage of the database db_name
This command is essentially equivalent to select sum(data1 + data2 + data3)/sum(raw_data), sum(data1 + data2 + data3) from information_schema.ins_disk_usage where db_name="dbname"