Database Application Parameters

MySQL

The overall performance of MySQL database applications is highly dependent on the I/O characteristics of the storage subsystem. In actual tests, SSD and NVMe drives demonstrate clear advantages over HDDs in transaction processing, latency control, and concurrent throughput. The two types of storage media have a large gap in performance, mainly due to the natural advantages of SSD and NVMe drives in random read/write performance and IOPS.

Therefore, it is recommended to use SSD or NVMe drives to maximize the performance potential of MySQL and achieve more stable latency. In scenarios where HDDs are still used, balance capacity and performance according to actual service load characteristics, and consider the potential impact of I/O bottlenecks on overall application performance.

• Service parameters

  Tune MySQL performance based on the read and write characteristics of specific application scenarios. Using the recommended parameters and tuning analysis provided in Table 1, you can adjust parameter values based on your service load characteristics to optimize overall system performance and resource utilization.

  Table 1 MySQL service parameters

  Recommended Parameter			Tuning Analysis
  Parameter Name	Parameter Description	Value Range	Test Case	Importance Ratio (%)	Optimal Value (vs. Baseline)		Worst Value (vs. Baseline)
  sync_binlog	Binary log (Binlog) flush frequency on transaction commit	{0, 1}	oltp_read_write	59.7%	1	(+8.9%)	0
  innodb_flush_method	Method of writing InnoDB data and logs	{fsync, O_DSYNC, O_DIRECT,O_DIRECT_NO_FSYNC}		13.1%	O_DIRECT_NO_FSYNC		O_DIRECT
  binlog_cache_size	Size of the memory cache for writing a single transaction to the binlog	[1, 256]	oltp_write_only	32.4%	254	(+17.2%)	115
  innodb_write_io_threads	Number of background I/O threads for InnoDB write operations	[1, 64]		10.1%	18		58
  performance_schema	Built-in real-time performance monitoring system of MySQL	{ON, OFF}	oltp_read_only	64.7%	OFF	(+8.9%)	ON
  innodb_adaptive_hash_index	InnoDB adaptive hash index	{ON, OFF}		15.7%	ON		OFF

  

  "Importance Ratio" comes from the built-in parameter importance analysis function of the Kunpeng AutoTuner. Based on the historical tuning data, this function quantitatively evaluates the impact of each parameter on performance metrics for specified test cases, and displays the impact in percentages. You can identify performance-sensitive parameters based on the percentages and make accurate tuning decisions to optimize performance.

• System parameters

  Table 2 MySQL system parameters

  Recommended Parameter		Tuning Analysis
  Parameter Name	Value Range	Test Case	Importance Ratio (%)	Default Value	Optimal Value (vs. Baseline)	Worst Value (vs. Baseline)
  scheduler	{mq-deadline, kyber, bfq, none}	oltp_read_write	57.6%	mq-deadline	none (+3.1%)	bfq (-89.6%)
  max_sectors_kb	[68, 128]	oltp_write_only	10.8%	512	86 (+1.0%)	-
  kernel.sched_cluster	{0, 1}	oltp_read_only	10.8%	0	1 (+0.5%)	-

openGauss

• Service parameters

  Set enable_codegen to off in the openGauss startup settings and assign a small value to random_page_cost to improve application performance.

  Table 3 openGauss service parameters

  Recommended Parameter			Tuning Analysis
  Parameter Name	Parameter Description	Value Range	Importance Ratio (%)	Optimal Value (vs. Baseline)	Worst Value (vs. Baseline)
  enable_codegen	SQL code execution optimization	{on, off}	28.7%	off (+2.3%)	on (-24.3%)
  random_page_cost	Estimated cost for the optimizer to fetch an out-of-sequence disk page	[0.0, 100.0]	22.7%	2.0 (+2.3%)	96.0 (-24.3%)

• System parameters

  Table 4 openGauss system parameters

  Recommended Parameter		Tuning Analysis
  Parameter Name	Value Range	Importance Ratio (%)	Default Value	Optimal Value (vs. Baseline)	Worst Value (vs. Baseline)
  scheduler	{mq-deadline, kyber, bfq, none}	34.0%	mq-deadline	bfq (+21.2%)	none (-4.8%)

Redis

• Service parameters

  Sensitive service parameters are generally the same across different scenarios, but their impact on performance varies. Table 5 provides only the parameter tuning reference for MSET. In the production environment, adjust parameters according to application load and service characteristics to achieve optimal performance.

  Table 5 Redis service parameters

  Recommended Parameter			Tuning Analysis
  Parameter Name	Parameter Description	Value Range	Test Case	Importance Ratio (%)	Optimal Value (vs. Baseline)
  set-max-intset-entries	Maximum number of elements stored in the intset code of a set object	[32, 4096]	Write with MSET	25.5%	3787	(+27.9%)
  maxmemory	Maximum memory available for a Redis instance	[1, 1048576]		23.3%	214068
  hash-max-ziplist-value	Maximum number of bytes that a compressed list can store for a single field value of the hash type	[32, 4096]		13.7%	1791
  maxclients	Maximum number of client connections allowed by Redis	[1, 65536]		13.3%	18907
  zset-max-ziplist-entries	Maximum number of elements that a compressed list can store for an ordered set	[32, 4096]		11.1%	3691
  hash-max-ziplist-entries	Maximum number of hash fields that can be stored in a compressed list	[32, 4096]		10.3%	159

• System parameters

  To optimize Redis memory management and scheduling, it is recommended to set transparent_hugepage_mode to never and kernel.sched_cluster to 1.

  Table 6 Redis system parameters

  Recommended Parameter		Tuning Analysis
  Parameter Name	Value Range	Importance Ratio (%)	Default Value	Optimal Value (vs. Baseline)
  transparent_hugepage_mode	{madvise, never, always}	75.0%	never	always (+6.8%)
  kernel.sched_cluster	{0, 1}	21.2%	0	1 (+1.2%)

RocksDB

• Service parameters

  To improve the write throughput, it is recommended to set write_buffer_size and batch_size to larger values when starting RocksDB.

  Table 7 RocksDB service parameters

  Recommended Parameter			Tuning Analysis
  Parameter Name	Parameter Description	Value Range	Importance Ratio (%)	Optimal Value (vs. Baseline)		Worst Value (vs. Baseline)
  write_buffer_size	Maximum size of a single MemTable.	[2, 268435456]	39.4%	185884824	(+65.3%)	3764887	(-68.8%)
  batch_size	Number of key-value pairs in a batch write operation	[1, 256]	23.3%	200		10
  block_size	Size of data blocks in an SST file	[2, 262144]	10.5%	10		121480
  target_file_size_base	Level 1 SST file size	[2, 268435456]	9.1%	129960935		83333272

• System parameters

  To optimize memory management and caching for higher performance, it is recommended to set vm.dirty_background_ratio, vm.max_map_count, and vm.vfs_cache_pressure to larger values.

  Table 8 RocksDB system parameters

  Recommended Parameter		Tuning Analysis
  Parameter Name	Value Range	Test Case	Importance Ratio (%)	Default Value	Optimal Value (vs. Baseline)	Worst Value (vs. Baseline)
  vm.dirty_background_ratio	[0, 100]	fillseq, fillrandom	35.3%	10	25 (+1.9%)	0 (-17.2%)
  vm.max_map_count	[65530, 10000000]	readrandomwriterandom	9.7%	65530	6876145 (+2.2%)	-
  vm.vfs_cache_pressure	[0, 500]	multireadrandom	6.9%	100	445 (+1.0%)	9 (-5.5%)

Vastbase G100