KlustronDB Parameter Optimization Settings
KlustronDB Parameter Optimization Settings
Note:
Unless otherwise specified, the version numbers in the text can be replaced with the version numbers of any released version. For all released versions, see: Release notes
Overview
In a KlustronDB cluster with default configuration, some important parameters are relatively conservative. However, the hardware environment for actual customers installing KlustronDB varies. To better utilize KlustronDB's performance, it is necessary to adjust these important performance parameters according to the characteristics of the specific environment and business workload to meet business performance requirements. This article aims to describe the meaning and purpose of these relevant parameters and provide configuration recommendations.
01 Key Parameters of Kluscomp instance (PG):
1.1 shared_buffers: The Kluscomp instances of KlustronDB do not store data locally. Data retrieved from Klustore instances is not stored in the buffer pool; instead, it is streamed (incrementally) from the Klustore instances on demand. Therefore, the buffer pool of the Kluscomp instances is only used to store data pages of metadata tables, with a total size of just a few megabytes, so we set this value to be small. If the total data size of several temporary tables used by the user is large, temp_buffers can be set larger.
1.2 statement_timeout: Used on the Kluscomp instance to control the execution duration of SQL statements, in milliseconds. If set to 10000, it means the statement can execute for a maximum of 10000 ms = 10 s. Under normal circumstances, it is recommended to set this value to: 6000000.
1.3 mysql_read_timeout, mysql_write_timeout: mysql_read_timeout and mysql_write_timeout: These parameters control the communication (read/write) timeout between Kluscomp instances and storage/metadata nodes. If a read exceeds mysql_read_timeout or a write exceeds mysql_write_timeout, the MySQL client library used by the Kluscomp instance will report an error and return from the read/write wait, causing the SQL statement execution to terminate early. If an insert statement sent to the Kluscomp instance will insert 1 million rows of data, or a select statement will return over a million rows from the Klustore instance, it is best to increase the values of these two variables, which default to 50 seconds; it is recommended to set them, for example, to 1200 seconds. In addition, in such cases, the mysql_max_packet_size variable should also be increased to ensure that such a large data packet can be correctly sent to the Klustore instance.
1.4 lock_timeout: The time a Kluscomp instance waits for a table lock. Concurrently executed insert, delete, update, and select statements have compatible locks on the table and do not need to wait. However, if an alter table statement is being executed, other connections on the same Kluscomp instance cannot execute DML statements on this table. They can wait at most this long, and if they still cannot obtain the lock, an error will be returned (default is 100 seconds). It is recommended to set it to, for example, 1,200,000 seconds.
1.5 log_min_duration_statement: Statements that exceed this duration will be treated as slow queries and recorded in the log file. If you are inserting tens of thousands of rows or more in each insert statement, you must increase this variable, otherwise a large amount of data will be recorded in the log file, potentially exhausting disk space on the Kluscomp instances (default is 10 seconds). It is recommended to set it, for example, to 1,200,000 seconds.
1.6 effective_cache_size: This is the maximum cache that the Kluscomp instance can use, and this number should be large enough, for example, more than 50% of the physical memory.
1.7 work_mem: The amount of memory that can be used by each Kluscomp instance during execution. When performing sorting operations, whether a large result set is split into several smaller temporary files roughly the size of work_mem depends on the size of work_mem. Obviously, splitting reduces the speed of sorting, so increasing work_mem helps to improve sorting speed. For OLTP-type workloads, a value of 8M is usually sufficient, but for OLAP workloads, setting it to 2%-4% of physical memory is also reasonable.
1.8 wal_buffers: This is the write-ahead log (WAL) buffer. The default size of the buffer is set by wal_buffers, initially 16MB. For KlustronDB, since user data is not stored locally on Kluscomp instances, only modifications to the vacuum metadata table will require writing redo logs to the WAL buffer. Therefore, this parameter does not need to be very large; the default value is sufficient. Increasing it significantly will not noticeably improve system performance.
1.9 autovacuum: autovacuum is a background process of the PostgreSQL database, which starts automatically with the database. The functions of autovacuum are as follows:
- Clean up expired dead tuples in the table
- Prevent business table expansion
- Update the table's statistics for the optimizer to use
- The autovacuum launcher uses the stats collector background process to gather statistics to determine the autovacuum candidate list
It is recommended to set this value to false.
All of the above parameters can be modified using the command alter system set <parameter_name>=<parameter_value>, but afterward, it is necessary to restart the Kluscomp instance service for the parameters to take effect. An example is as follows:
pg_ctl restart -D /kunlun/server_datadir/47001
02 Key Parameters of Klustore instance (MySQL):
2.1 innodb_buffer_pool_size: Specifies the size of the InnoDB buffer pool for this KlustronDB_storage instance. For metadata nodes, the default value is 128MB. This value may be small for production environments and generally needs to be set according to requirements. The maximum value depends on the CPU architecture. On a 32-bit platform, the maximum value is 2^32 - 1; on a 64-bit platform, the maximum value is 2^64 - 1. When the buffer pool size is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve server scalability. A large buffer pool can reduce repeated disk I/O access to the same table data. On dedicated database servers, the buffer pool size can be set to 80% of the server's physical memory.
2.2 lock_wait_timeout: The lock timeout variable at the MySQL server layer, representing the maximum time to wait for a table lock at the server layer. If a DDL statement involves an ALTER TABLE, then all transactions performing DML operations on that table will be blocked, waiting up to this time for the table lock, and if the lock is still not acquired, an error will be returned. In MySQL 8.0, common operations like adding a column or adding an index, which previously required locking the entire table, no longer need long-term full table locks and have become online DDL operations. Therefore, the default value is generally sufficient. For performance testing, whether TPCC or TPCH, it is recommended to set this value to: 1200 seconds.
2.3 innodb_lock_wait_timeout: This is the lock timeout variable for MySQL InnoDB, representing the maximum time to wait for an InnoDB row lock. If exceeded, DML statements will return an error. If you are performing a full table update and the table contains a very large amount of data, such as hundreds of GB or more, the update statement will lock a large number of rows for a long time. In this case, other transactions usually experience lock timeouts unless this parameter is increased (default is 20 seconds). In such situations, it is recommended to set it to 1200 seconds.
2.4 fullsync_timeout: For the complete explanation of this parameter, please refer to: Klustron_Storage_Cluster_Fullsync for a full understanding. It is recommended to set this parameter to: 1200000.
2.5 enable_fullsync: For the full explanation of this parameter, please refer to for complete understanding. Under normal circumstances, this parameter must be set to true. If loading a large amount of data during TPCC/TPCH tests (for example, more than 10G), in order to improve data loading speed, it is recommended to set this parameter to false.
2.6 innodb_flush_log_at_trx_commit: This is specific to the InnoDB engine and refers to the way the ib_logfile is flushed. Possible values are 0/1/2. innodb_flush_log_at_trx_commit=0 means that the log buffer is flushed to the file system (OS buffer) every second, and the file system's "flush" operation is called to flush the cache to disk. In other words, logs from the last second are stored in the log buffer, that is, in memory. If the machine crashes, there may be a loss of up to one second of committed transaction data.
innodb_flush_log_at_trx_commit=1 means that at each transaction commit, the log buffer is flushed to the file system (OS buffer) and the file system's 'flush' operation is called to write the cache to disk. In this case, the database has very high I/O requirements. Although InnoDB will merge a group of simultaneously committed transactions to perform only one flush, the I/O load caused by these flushes is still relatively high. If the underlying hardware provides poor IOPS, MySQL's concurrency will quickly be limited by hardware I/O issues. innodb_flush_log_at_trx_commit=2 means that at each transaction commit, the log buffer is flushed to the file system's page cache, but it is not immediately written to disk. If only the MySQL database crashes and the file system is fine, the corresponding transaction data is not lost. Only if the host operating system where the database resides is damaged or there is a sudden power outage, a transaction data loss of up to 1 second may occur. The advantage of this setting is that it reduces the probability of transaction data loss while not placing high I/O requirements on the underlying hardware (writing the log buffer to the file system usually only transfers data from the log buffer's memory to the file system's memory cache, without stressing the underlying I/O). If you need to load a large amount of data during TPCH testing (e.g., more than 10G), to improve speed, it is recommended to set this parameter to 2.
2.7 sync_binlog: MySQL provides a sync_binlog parameter to control when the database binlog is flushed to disk. By default, sync_binlog=0, which means MySQL does not control the binlog flushing; it is controlled by the file system's own cache flushing. At this point, performance is the best, but the risk is also the highest. Because if the system crashes, any updates to the binlog file cached in the operating system's page cache will be lost. If sync_binlog>0, it means that every sync_binlog transaction commits, MySQL will call the file system's flush operation to flush the cache. The safest is sync_binlog=1, meaning that for each transaction commit, MySQL will flush the binlog, which is the safest but has the greatest performance cost. Although MySQL can merge a batch of simultaneously committed transactions and perform the flush only once, the IO load caused by these flushes is still quite high. In this case, if the host operating system crashes or there is a sudden power outage, the system will not lose any confirmed committed transactions. For high-concurrency transaction systems, the difference in write performance between a system with sync_binlog set to 0 and to 1 can be up to 5 times or more. Depending on the needs, sync_binlog may be set not to the safest 1, but to 100 or 0. This way, sacrificing some consistency can achieve higher concurrency and performance. In performance tests, especially TPCH-type tests, it is recommended to set it to: 0.
2.8 max_binlog_size: Specifies the maximum size of a single binary log file. If this value is exceeded, a new binary log file is created with suffix 1 and recorded in the .index file. The default value has been 1073741824 (representing 1GB) since MySQL 5.0. It is recommended to use the default value of 500MB from the KlustronDB configuration template.
The above parameters can be accessed through:
set global <参数名>=<参数值>;
If it takes effect temporarily, the parameters will become invalid if the Klustore instance restarts. If permanent settings are needed, the above parameters must be written and saved in my.cnf.
Attachment: Methods to connect Klustore instances:
mysql -h <存储节点主机IP> -P <存储节点端口> -upgx -ppgx_pwd
Additionally, when conducting TPCC/TPCH test scenarios, during data loading, to improve speed and reduce unnecessary failures, it is recommended to set shards to be exempt from switching. The concept of this parameter is: setting a switch exemption means that within the exemption period, an exception in the shard's primary node will not trigger a primary-backup switch. For example, if set to 10800 seconds (3 hours), the timing starts from the point of 'setting,' and no switching will occur within 10800 seconds. This setting needs to be performed on XPanel.
监控是否发生过主备切换，通过“主备切换记录”查看：
2.9 存储节点存储引擎参数说明：
by default, the storage engine used by the KlustronDB Klustore instance is innodb. however, in some scenarios, to improve the efficiency of data loading and warehousing, for example, for the scenario of a large number of log records and warehousing, you can set the storage engine of the Klustore instance to rocksdb. you can enable the rocksdb storage plug-in by modifying the mysql configuration file of the Klustore instance. the file path is as follows:
vi /kunlun/storage_datadir/57001/data/57001.cnf
Remove the '#' in front of #default-storage-engine=rocksdb, save and exit, then restart the Klustore instance. The MySQL service will enable the rocksdb storage engine. However, for specific configurations and environments, there are also some other rocksdb-related parameters worth adjusting, as explained below.
2.9.1 rocksdb_max_open_files: RocksDB keeps all file descriptors in the table cache. If the number of file descriptors exceeds max_open_files, some files will be evicted from the table cache and their file descriptors will be closed. This means that every read must go through the table cache to find the required files. It is recommended to set max_open_files to -1 to keep all files open at all times, thus avoiding expensive table cache calls.
2.9.2 rocksdb_max_background_jobs: This variable is used to replace rocksdb_base_background_compactions, rocksdb_max_background_compactions, and rocksdb_max_background_flushes. It specifies the maximum number of background jobs. It automatically determines how many threads are allocated for flushing/compaction. Its implementation is intended to reduce the number of (confusing) options for users, and a common configuration is the number of CPU cores / 4. If there are too few threads, more pauses may occur due to limitations. If there are too many threads, the threads handling user queries may be affected. The default value for this parameter is 2.
2.9.3 rocksdb_max_background_flushes: This variable has been replaced by rocksdb_max_background_jobs.
2.9.4 rocksdb_max_background_compactions: This variable has been replaced by rocksdb_max_background_jobs.
2.9.5 rocksdb_max_total_wal_size: Specifies the maximum size of WAL (Write-Ahead Log) files, used to control the total size of Write-Ahead Logging (WAL) log files. WAL logs are used to ensure the durability and consistency of data. Generally, you do not want WAL log files to be too large, to avoid consuming a lot of disk space. At the same time, you need to ensure that WAL log files are large enough to accommodate the transaction data in your application. Smaller WAL files can reduce write latency but may increase the risk to durability. Larger WAL files can improve durability but may increase write latency. Therefore, you need to choose an appropriate size based on application requirements. The default value is 4G.
2.9.6 rocksdb_table_cache_numshardbits: This is a parameter in RocksDB used to control the table cache. It is used to specify the number of shards in the table cache in order to better manage memory resources. By default, the value of rocksdb_table_cache_numshardbits is 6. This means that RocksDB will use 64 shards. You can consider this as a starting point and then adjust it as needed. It is necessary to monitor RocksDB's performance, observe the latency of read operations and cache hit rate to ensure that the table cache settings can meet the application's performance requirements. You may need to continuously adjust rocksdb_table_cache_numshardbits to optimize performance.
2.9.7 rocksdb_default_cf_options write_buffer_size: Sets the size of a single memory table. Once a memtable exceeds this size, it is marked as immutable and a new memtable is created. Different workloads require different write buffer sizes. If your application performs a large number of write operations, a larger write buffer may help reduce write amplification and improve performance. However, if your workload is primarily read operations, a smaller write buffer may be sufficient. The default value for this parameter is 128M.
2.9.8 rocksdb_default_cf_options's target_file_size_base: along with target_file_size_multiplier - files in level 1 will have target_file_size_base bytes. Files in the next level will be larger than the previous level by target_file_size_multiplier. However, by default, target_file_size_multiplier is 1, so files in all L1..Lmax levels are equal. Increasing target_file_size_base will reduce the total number of database files, which is usually a good thing. We recommend setting target_file_size_base to max_bytes_for_level_base / 10 so that there are 10 files in level 1. The default value of this parameter is 64M.
2.9.9 rocksdb_default_cf_options's cache_index_and_filter_blocks: This parameter is used to control whether to cache the index blocks and filter blocks of SST files, which can significantly affect read performance. If your application frequently performs range queries or requires fast random reads, caching index and filter blocks is generally beneficial, as it can improve read performance. However, if your application is primarily write-heavy or has limited memory, you may need to reduce or disable this cache to free up more memory. The default value for this parameter is 1.
2.9.10 rocksdb_default_cf_options filter_policy=bloomfilter: This parameter is used to specify the Bloom Filter options for the default column family (CF). A Bloom Filter is a data structure used to quickly determine whether an element exists in a set, typically used to speed up read operations and reduce unnecessary disk reads. Usually, there are several related parameters that need to be set, such as the false positive rate of the Bloom Filter and the number of bits in the Bloom Filter. These parameters need to be adjusted according to the application's requirements. A Bloom Filter with a lower false positive rate will be more accurate but will consume more memory. You can balance accuracy and memory usage by setting an appropriate number of bits.
2.9.11 rocksdb_lock_wait_timeout: This is a parameter used to control lock wait timeout, specifying the maximum time (in seconds) that RocksDB will wait for a lock to be released. This parameter can help you avoid waiting indefinitely in cases of lock conflicts, thereby reducing potential deadlock risks. If your application has frequent write operations or a high likelihood of lock conflicts, you may need to set a shorter wait timeout to reduce lock waiting time. If your application is mainly performing read operations, you can set a longer wait timeout. The default value of this parameter is 20 seconds.
03 metashard Parameter Adjustment:
For the KlustronDB metashard, it is recommended during testing to make appropriate adjustments to a small number of key parameters, following the example below:
Log in to a cluster node:
export LD_LIBRARY_PATH=/kunlun/instance_binaries/storage/57001/Kunlun-storage-1.2.1/lib:$LD_LIBRARY_PATH
/kunlun/instance_binaries/storage/57001/Kunlun-storage-1.2.1/bin/mysql --connect-expired-password -S/nvme2/kunlun/storage_logdir/57001/mysql.sock -uroot -proot
set global innodb_buffer_pool_size=1*1024*1024*1024; -- (缺省128M)
This parameter needs to be set on all three nodes. Similarly, the above settings only temporarily change the parameter value in memory. If you need to set it permanently, you need to configure it in my.cnf, save it, and restart each node for it to take effect.