OceanBase Sysbench high-performance parameter configuration

This topic explains how to configure OceanBase Database for high Sysbench performance, including deployment, tenant creation, and Sysbench settings.

OceanBase Database deployment parameter configuration

This section covers performance-related parameters and configuration files used during deployment.

Installation and deployment process

• Installation and deployment process of the Community Edition

  Use OBD to deploy: Deploy OceanBase Database in a production environment

• Installation and deployment process of the Enterprise Edition

  • Deployment of the Enterprise Edition: Deployment process

Installation and deployment parameters

Beyond the quick-start installation, you typically edit the .yaml configuration file manually. OceanBase exposes many parameters; this topic focuses on those that affect performance. See the deployment documentation for the full list.

• Query configuration items: show parameters where name = 'xxx';
• Query server key parameters: SELECT * FROM GV$OB_SERVERS;

The specific configuration items involved are as follows:

• memory_limit

  • Parameter description: The maximum memory size of an OBServer node, which takes precedence over the memory_limit_percentage parameter.
  • Default value: 0
  • Recommended configuration: 80% to 90% of the total system memory
  • Dynamic modification: alter system set memory_limit='32G';
  • Query: show parameters where name = 'memory_limit';
  • Related documentation: memory_limit

• memory_limit_percentage

  • Parameter description: The percentage of system memory occupied by an OBServer node. This parameter takes effect only when memory_limit is set to 0.
  • Default value: 80
  • Recommended configuration: 80 to 90
  • Dynamic modification: alter system set memory_limit_percentage=80;
  • Query: show parameters where name = 'memory_limit_percentage';
  • Related documentation: memory_limit_percentage

• system_memory

  • Parameter description: The memory occupied by the sys tenant, which is included in memory_limit. If this parameter is not configured, the system will automatically set it.
  • Default value: 30G for Enterprise Edition and 0M for Community Edition
  • Recommended configuration: 10% of memory_limit
  • Dynamic modification: alter system set system_memory='2G';
  • Query: show parameters where name = 'system_memory';
  • Related documentation: system_memory

• cpu_count

  • Parameter description: The total number of available CPU cores of an OBServer node.
  • Default value: 0, indicating that the system will automatically detect the number of CPU cores.
  • Recommended configuration: The total number of CPU cores, which can be viewed using lscpu.
  • Dynamic modification: alter system set cpu_count=24;
  • Query: show parameters where name = 'cpu_count';
  • Related documentation: cpu_count

• net_thread_count

  • Parameter description: The number of network threads.
  • Default value: 0 (the number of network I/O threads is max(6, CPU_NUM/8))
  • Recommended configuration: 1/8 of the total number of CPU cores. If transactions are short, the network overhead is relatively large, so you can set it to 20% of the total number of CPU cores.
  • This parameter can only be statically set when deploying an OBServer node and cannot be dynamically modified.
  • Query: show parameters where name = 'net_thread_count';
  • Related documentation: net_thread_count

• sql_net_thread_count

  • Parameter description: The number of I/O threads for the cluster in MySQL mode.
  • Default value: 0, indicating that the value is the same as the net_thread_count parameter.
  • Related documentation: sql_net_thread_count

• datafile_size

  • Parameter description: The total size of data files, which takes precedence over the datafile_disk_percentage parameter.
  • Default value: 0
  • Recommended configuration: 60% of the disk space if data files and logs share the disk, or 90% of the disk space if data files use a dedicated disk.
  • Dynamic modification: alter system set datafile_size='80G';
  • Query: show parameters where name = 'datafile_size';
  • Related documentation: datafile_size

• datafile_disk_percentage

  • Parameter description: The percentage of disk space occupied by data files.
  • Default value: 0
  • Recommended configuration: 60% of the disk space if data files and logs share the disk, or 90% of the disk space if data files use a dedicated disk.
  • Dynamic modification: alter system set datafile_disk_percentage=60;
  • Query: show parameters where name = 'datafile_disk_percentage';
  • Related documentation: datafile_disk_percentage

• log_disk_size

  • Parameter description: The total size of redo log files, which takes precedence over the log_disk_percentage parameter.

  • Default value: 0

  • Recommended configuration: 30 when sharing a disk with data files, 90 when using a dedicated disk.

  • Dynamic modification: alter system set log_disk_size='40G';

  • Query: show parameters where name = 'log_disk_size';

  • Related documentation: log_disk_size

• log_disk_percentage

  • Parameter description: The percentage of the total storage space on the disk occupied by redo log files.

  • Default value: 0

  • Recommended configuration: 30% of disk space when the disks are shared with data files, and 90% of disk space when the disks are dedicated.

  • Dynamic modification: alter system set log_disk_percentage=30;

  • Query: show parameters where name = 'log_disk_percentage';

  • Related documentation: log_disk_percentage

Configuration file

For a 32-core, 128 GB server (32c128g) with sufficient disk space, this example uses three OBServer nodes plus one OBProxy node. OBAgent and other components are omitted. For best performance, run each OBServer and OBProxy on a separate host. Sample deployment files:

oceanbase:
  servers:
    * name: server1
      ip: xxx.xxx.1.1
    * name: server2
      ip: xxx.xxx.1.2
    * name: server3
      ip: xxx.xxx.1.3
  server1:
    mysql_port: 2881
    rpc_port: 2882
    home_path: /data/1/user_name/observer1
    zone: zone1
  server2:
    mysql_port: 2881
    rpc_port: 2882
    home_path: /data/1/user_name/observer2
    zone: zone2
  server3:
    mysql_port: 2881
    rpc_port: 2882
    home_path: /data/1/user_name/observer3
    zone: zone3
  include: obd/observer.include.yaml
  global:
    devname: eth0
    memory_limit: '100G'
    system_memory: '2G'
    datafile_size: '80G'
    cpu_count: '32'
    net_thread_count: 6
obproxy:
  servers:
    * xxx.xxx.1.5
  depends:
    * oceanbase
  include: obd/obproxy.include.yaml
  global:
    listen_port: 2891
    prometheus_listen_port: 2892
    home_path: /data/1/user_name/obproxy

Business tenant parameter configuration

If you deploy with OBD, only the sys tenant is created. Create a dedicated tenant for performance testing to use OBServer resources fully.

Create a tenant

OceanBase supports user tenants only; the sys tenant is created automatically with the cluster. Creating a tenant involves three steps: define a resource unit, create a resource pool from that unit, then create the tenant and assign the pool. The order is resource unit → resource pool → tenant.

For more information, see Create a tenant.

Example tenant creation on a 32c128g server:

sh create_perf_tenant.sh ip port

mysql -c -h $1 -P $2 -uroot -e "drop resource unit unit_1;"
mysql -c -h $1 -P $2 -uroot -e "create resource unit unit_1 max_cpu 24, min_cpu 16, memory_size 64000000000, log_disk_size 40000000000, max_iops 10000000;"
mysql -c -h $1 -P $2 -uroot -e "create resource pool pool_2 unit = 'unit_1', unit_num = 1, zone_list = ('zone1','zone2','zone3');"
mysql -c -h $1 -P $2 -uroot -e "create tenant perf replica_num = 3,primary_zone='zone1', resource_pool_list=('pool_2') set ob_tcp_invited_nodes='%';"
mysql -c -h $1 -P $2 -uroot@perf -e "set global ob_query_timeout = 50000000000;"
mysql -c -h $1 -P $2 -uroot@perf -e "set global ob_trx_timeout = 50000000000;"
mysql -c -h $1 -P $2 -uroot@perf -e "set global ob_plan_cache_percentage = 20;"
mysql -c -h $1 -P $2 -uroot@perf -e "set global binlog_row_image='MINIMAL';"
mysql -c -h $1 -P $2 -uroot -e "alter system set enable_early_lock_release=true tenant = perf;"
mysql -c -h $1 -P $2 -uroot -e "alter system set cpu_quota_concurrency=2 tenant = perf;"

If you omit the tenant type in CREATE TENANT, the default is a MySQL tenant. Specify Oracle mode to create an Oracle tenant.

In the sys tenant, run:

SELECT * FROM DBA_OB_TENANTS;

The COMPATIBILITY_MODE column indicates the tenant mode.

Key tenant configuration

The key tenant parameters related to performance are as follows:

• min_cpu

  • Parameter description: The minimum number of available CPU cores for the tenant (sum of min_cpu of all tenants <= cpu_count).
  • Recommended value: cpu_count/2
  • Related documentation: For more information about the min_cpu parameter, see Parameter description in the Create resource unit section of Create a tenant.

• max_cpu

  • Parameter description: The maximum number of available CPU cores for the tenant (sum of max_cpu of all tenants <= cpu_count * resource_hard_limit/ 100).
  • Recommended value: cpu_count/2 ~ cpu_count (or the same as min_cpu)
  • Related documentation: For more information about the max_cpu parameter, see Parameter description in the Create resource unit section of Create a tenant.

• cpu_quota_concurrency

  • Parameter description: The number of threads that can be concurrently executed on each CPU core of the tenant (max_cpu * cpu_quota_concurrency = maximum number of business threads).
  • Recommended value: 4 (Generally, do not change the default value. For CPU-intensive stress tests, you can set this parameter to 2.)
  • Related documentation: cpu_quota_concurrency

• memory_size

  • Parameter description: The size of memory provided by the resource unit.
  • Recommended value: 50%~80% of the total memory (You need to flexibly allocate memory when you create multiple resource units.)

• log_disk_size

  • Parameter description: The size of the log disk.
  • Recommended value: Set a value that is sufficient for normal operation, such as 40~80 GB. If the data volume is large, you can set a larger value.

• primary_zone

  • Parameter description: The priority of the zone in which the leader replicas are allocated. The priorities on both sides of the comma are the same. The priority on the left side of the semicolon is higher than that on the right side. For example, zone1,zone2,zone3.

  • Recommended value:

    1. If three server nodes have the same specifications and similar network latency, you can set primary_zone to RANDOM to evenly distribute leaders and improve performance.
    2. If one server node has better performance or you want to facilitate data statistics and observation, you can specify that zone, such as zone1.

System tenant global configuration

Connect to the sys tenant to change cluster-level settings, such as log level or disabling sql_audit for better benchmark performance. Common commands:

ALTER SYSTEM SET enable_sql_audit=false;
select sleep(5);
ALTER SYSTEM SET enable_perf_event=false;
ALTER SYSTEM SET syslog_level='ERROR';
alter SYSTEM SET enable_record_trace_log=false;

Sysbench parameter configuration

This section covers Sysbench preparation and key parameters for MySQL tenants.

Sysbench test process

• Prepare data

  cleanup:
  sysbench oltp_read_write.lua --mysql-host=x.x.x.x --mysql-port=xxxx --mysql-db=test --mysql-user=test@xxx --mysql-password=xxx --table_size=1000000 --tables=30 --rand-type=uniform --threads=1000  --report-interval=1 --time=600 cleanup
  prepare:
  sysbench oltp_read_write.lua --mysql-host=x.x.x.x --mysql-port=xxxx --mysql-db=test --mysql-user=test@xxx --mysql-password=test --table_size=1000000 --tables=30 --rand-type=uniform --threads=1000  --report-interval=1 --time=600 prepare
  

• Run the test

  sysbench oltp_read_write.lua --mysql-host=x.x.x.x --mysql-port=xxxx --mysql-db=test --mysql-user=test@xxx --mysql-password=xxx --table_size=1000000 --tables=30 --rand-type=uniform --threads=1000  --report-interval=1 --time=600 --db-ps-mode=disable run
  

Key performance parameters

The recommended Sysbench parameters for performance testing are as follows:

• table_size

  • Parameter description: The number of data rows initialized in each table. Default value: 10000.
  • Recommended value: 100000, 1000000

• tables

  • Parameter description: The number of tables to be initialized.
  • Recommended value: 30 ~ 100

• threads

  • Parameter description: The number of test threads.
  • Recommended value: 500 ~ 2000. The more CPU cores the test server has, the more test threads you can specify.

• rand-type

  Parameter description: The random distribution mode. Valid values: uniform, gaussian, special, and pareto. Default value: special.

  1. special: Keys are concentrated in a specific range. This mode is likely to cause lock conflicts.
  2. uniform: Values are uniformly distributed in the range. No hotspots exist.
  3. gaussian: Values are concentrated around the median. This mode is also called the normal distribution.
  4. pareto: Values are concentrated around the minimum value. The larger the value, the smaller the probability that it appears.

  Recommended value: uniform

• mysql-ignore-errors

  • Parameter description: The error codes to be ignored during the test. If an error occurs, Sysbench stops sending new requests until the current request is completed and then exits the test. Therefore, you can ignore the error codes that are expected.
  • Optional value: --mysql-ignore-errors=1062,1213,6002, where 1062 indicates a primary key or unique index conflict, 1213 indicates a deadlock, and 6002 indicates a transaction rollback.

• db-ps-mode

  • Parameter description: Whether SQL statements need to be precompiled. Valid values: auto and disable. Default value: disable.
  • Recommended value: disable (we recommend that you set this parameter to disable).
  • If you set this parameter to auto, run the following command to modify the tenant configuration: alter system set open_cursors=65535;

• skip_trx

  • Parameter description: Whether to skip transactions. In read-only tests, you can choose whether to skip transactions. Default value: OFF, which indicates that transactions are enabled.
  • Recommended value: ON for read-only tests. For other scenarios, use the default value.

• auto-inc

  • Parameter description: Whether to enable auto-increment for the ID column. Default value: ON.
  • Recommended value: OFF (we recommend that you disable auto-increment for write operations).

• warmup-time

  • Parameter description: The warm-up time in seconds. The test starts formal statistics after this period elapses. Default value: 0.
  • Recommended value: 10~30. If the data volume is large, you can set this parameter to a larger value.