Depending on the customer's business scenarios, it is recommended to appropriately configure the following parameters after purchasing instances.
Parameter Configuration Overview
|
| UTF8 | Yes | Server default character set | Select based on business requirements. |
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | No | SQL syntax and data validation rules | Configure based on business scenarios and development practices. |
| 1 | No | Slow query threshold time | Adjust based on customer business scenarios and performance sensitivity. |
| ON | No | DDL online replication mode switch | Keep enabled by default. |
| 64MB | No | Single-row record size limitation | Keep the default configuration. |
| 99% | No | Storage space read-only mode trigger threshold | Keep the default configuration. |
| 95% | No | Read-only mode exit threshold | Keep the default configuration. |
| ON | No | Flashback query feature switch | It is recommended to enable. |
Detailed Parameter Description
character_set_server
Default value: UTF8
Whether restart is required: Yes
Purpose: Used to configure the default character set of the TDSQL Boundless server. TDSQL Boundless provides multiple commonly used character sets, including LATIN1, UTF8, UTF8MB4, GBK, GB18030, and so on.
|
LATIN1 | English characters, each character occupies one byte. | English-only application. |
UTF8 | International encoding contains characters needed by all countries worldwide. One character occupies three bytes. | Multilingual support with strong versatility. |
UTF8MB4 | UTF8 superset, fully backward compatible, one character occupies four bytes. | Supports special characters such as emojis. |
GBK | Double-byte character set, meaning that regardless of Chinese or English characters, all are represented using two bytes. | Simplified Chinese environment. |
GB18030 | The latest national standard Chinese character set, which is a superset of GBK, includes all characters of GBK and additionally supports more Chinese characters and Chinese minority scripts. | Comprehensive Chinese character support. |
Recommendation: After purchasing an instance, select an appropriate character set based on the data formats required by your business. Ensure consistent character set settings between client and server to prevent garbled text issues and unnecessary restarts caused by incorrect configurations.
sql_mode
Default value: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Whether it needs to be restarted: No
Note:
TDSQL Boundless has a different default sql_mode than MySQL 8.0, adopting a more lenient configuration. The default parameter value for MySQL 8.0 is ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION. TDSQL Boundless does not enable date validation (NO_ZERO_DATE etc.) or strict GROUP BY checks, offering better compatibility but more relaxed data validation. If stricter data validation is required, manually adjust the sql_mode.
Function: TDSQL Boundless can run in different SQL modes. The SQL mode defines the SQL syntax that should be supported, data validation, and so on. The default parameter value for TDSQL Boundless is STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION. where:
STRICT_TRANS_TABLES enables strict mode for transactional tables and is used to control how invalid or missing data values are handled. If invalid values are inserted, or out-of-range values, strings that are too long, missing NOT NULL columns, and so on, in strict mode for transactional tables, an error is reported and execution is rejected. The modes NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO define the meaning of invalid data, and STRICT_TRANS_TABLES defines how to handle invalid data when encountered.
NO_ZERO_IN_DATE controls whether months and days in dates can contain zero, and this is affected by whether strict mode is enabled.
NO_ZERO_DATE disallows inserting zero dates into the database, and this behavior is affected by whether strict mode is enabled.
ERROR_FOR_DIVISION_BY_ZERO In strict mode, if data is divided by zero during INSERT or UPDATE operations, an error is generated instead of a warning. In non-strict mode, NULL is returned when division by zero occurs.
ONLY_FULL_GROUP_BY indicates that in GROUP BY aggregation operations, columns in the SELECT, HAVING, or ORDER BY clauses must either appear in the GROUP BY clause or be functional dependencies of the GROUP BY columns.
Note:
Adjustments to the sql_mode parameter take effect globally. However, note that for the ONLY_FULL_GROUP_BY configuration within its parameter values, if an old connection is persistent, the change remains session-level. Therefore, after the ONLY_FULL_GROUP_BY configuration is modified, it takes effect for new connections, but may not apply to existing ones until they are disconnected and reconnected.
NO_AUTO_CREATE_USER forbids GRANT from creating users with empty passwords.
NO_ENGINE_SUBSTITUTION throws an error if the storage engine used is disabled or not compiled.
Recommendation: Since different SQL modes support different SQL syntaxes, it is recommended to configure appropriately based on your business scenarios and development practices.
long_query_time
Default value: 1
Whether it needs to be restarted: No
Purpose: Used to specify the threshold time for slow queries, with a default value of 1s. When a query execution time is 1s or more, the execution details of that query are recorded in the slow log to facilitate subsequent analysis of slow queries.
Recommendation: As customer business scenarios and performance sensitivity vary, it is recommended to set appropriate values based on specific business contexts to facilitate subsequent performance analysis.
tdsql_use_online_copy_ddl
Default value: ON
Whether it needs to be restarted: No
Purpose: Controls whether to enable the DDL online replication mode for TDSQL Boundless.
ON (default): After the DDL online replication mode is enabled, DDL operations do not lock tables and do not block business read and write operations.
OFF: Disable DDL online replication mode, which will fall back to MySQL's native Copy DDL. This means that during the execution of non-Inplace or non-Instant DDL, write requests to the table will be blocked.
Recommendation: Keep the default configuration, enabled by default. If errors occur during operations such as adding/dropping primary keys, adding a column with an expression as a default value (ADD COLUMN DEFAULT (a+a)), or altering tables with triggers, you can temporarily disable Online DDL mode by executing set tdsql_use_online_copy_ddl = 0, then retry.
Note:
After temporarily disabling it, the setting must be immediately restored to ON to prevent subsequent DDL operations from unexpectedly blocking business operations.
The temporary disabling operation should be performed within the same transaction or connection session to ensure the consistency of the settings.
tdstore_txn_max_entry_size
Default value: 67108864
Whether it needs to be restarted: No
Purpose: Used to control the size of a single-row record, with a default value of 64MB. When the size of a single-row record exceeds 64MB, the statement execution fails.
Recommendation: Keep the default configuration. If the single-row record size exceeds 64MB, contact Tencent Cloud technical support to evaluate and adjust.
tdstore_enter_readonly_threshold
Default value: 99%
Whether it needs to be restarted: No
Purpose: Provides a storage space protection mechanism. When a node's storage space utilization exceeds this threshold (default: 99%), the node will automatically enter read-only mode. In this state, the node can still query data normally, but only data cleanup operations (such as DROP/TRUNCATE) are allowed; other write operations (such as INSERT/UPDATE/DELETE) will be rejected. To restore writable mode, you can perform manual operations or wait until the space utilization falls below tdstore_leave_readonly_threshold, after which the node will automatically exit read-only mode.
Recommendation: Keep the default configuration.
tdstore_leave_readonly_threshold
Default value: 95%
Whether it needs to be restarted: No
Purpose: Sets the threshold for nodes to automatically exit read-only mode. When the storage space utilization falls below this threshold (default: 95%), the node will automatically exit read-only mode.
Recommendation: Keep the default configuration.
tdsql_enable_stale_read
Default value: ON
Whether it needs to be restarted: No
Purpose: Controls the flashback query feature switch.
Recommendation: Recommend enabling the flashback query feature by default to facilitate quickly querying historical versions of data and performing recovery in scenarios of accidental updates.