High-performance parameter configuration for OceanBase Sysbench

This topic is designed to help you achieve high performance in Sysbench tests by explaining how to install and deploy OceanBase Database, create a business tenant, and use Sysbench effectively.

Configure deployment parameters

This section provides an overview of performance-related parameter configurations involved in the installation and deployment of OceanBase Database, along with the relevant configuration file details.

Installation and deployment procedure

• Community Edition:

  Deploy OceanBase Database Community Edition using obd: Deploy OceanBase Database on CLI in a production environment

  • Enterprise Edition:

    • Deployment process

Installation and deployment parameters

Apart from the quick installation process, it is usually necessary to manually modify the .yaml file for configuration. OceanBase Database offers many configurable parameters, but this topic focuses on performance-related parameters. For more details, see the deployment documentation mentioned earlier.

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

The specific parameters include the following:

• memory_limit

  • Description: the memory limit for an OBServer node, which takes precedence over the memory_limit_percentage parameter.
  • Default value: 0
  • Recommended configuration: 80%–90% of 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

  • Description: the percentage of system memory allocated to an OBServer node. this configuration only takes effect when memory_limit is set to 0.
  • Default value: 80
  • Recommended configuration: 80–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

  • Description: the memory allocated to the system tenant, which is included in memory_limit. If not configured, it will be adaptively set.
  • Default value: Enterprise Edition (30 GB). Community Edition (0 MB)
  • 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

  • Description: the total number of CPUs available for an OBServer node.
  • Default value: 0, meaning the system will automatically detect the number of CPUs.
  • Recommended configuration: the total number of system CPUs, which can be checked using lscpu.
  • Dynamic modification: alter system set cpu_count=24;
  • Query: show parameters where name = 'cpu_count';
  • Related documentation: cpu_count

• net_thread_count

  • Description: the number of network threads.
  • Default value: 0 (the number of network I/O threads is calculated as max(6, CPU_NUM/8)).
  • Recommended configuration: 1/8 of the total CPU count. If transactions are relatively short and network overhead is higher, it can be set to 20% of the total CPU count.
  • This configuration can only be statically set during the deployment of OBServer nodes and cannot be dynamically modified.
  • Query: show parameters where name = 'net_thread_count';
  • Related documentation: net_thread_count

• sql_net_thread_count

  • Description: the number of IO threads for the cluster in MySQL mode.
  • Default value: 0, which means it is the same as the value of the net_thread_count parameter.
  • Related documentation: sql_net_thread_count

• datafile_size

  • Description: the total size of the data files, which takes precedence over the datafile_disk_percentage parameter.
  • Default value: 0
  • Recommended configuration: set to 60% of the disk space when sharing the disk with logs, or 90% when using 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

  • Description: the percentage of the total disk space occupied by data files on the disk.
  • Default value: 0
  • Recommended configuration: set to 60% when sharing the disk with logs, or 90% when using 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

  • Description: the total size of redo log files, which takes precedence over the log_disk_percentage parameter.
  • Default value: 0
  • Recommended configuration: set to 30% when sharing the disk with data, or 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

  • Description: the percentage of the total disk space occupied by redo log files on the disk.
  • Default value: 0
  • Recommended configuration: set to 30% of the disk space when sharing the disk with data, or 90% when using a dedicated disk.
  • Dynamic modification: alter system set log_disk_percentage=30;
  • Query: show parameters where name = 'log_disk_percentage';

  Related documentation: log_disk_percentage

Configuration file

In this example, the specifications of the server are 32 CPU cores and 128 GB of memory. The deployment mode is "3 OBServer nodes + 1 ODP", and other components such as obagent are not considered. To ensure performance, each OBServer node and ODP are deployed on different servers. The following example shows the deployment configuration file of OceanBase Database and ODP:

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

Configure parameters for a business tenant

If obd is used to automatically deploy the OceanBase Database, only the sys tenant is created after the deployment. You need to create a dedicated tenant for performance testing to make full use of the available resources of the OBServer node.

Create a tenant

OceanBase Database allows you to create only user tenants. System tenants are automatically created when a cluster is created. The process of creating a user tenant involves creating resource specifications, a resource pool, and the tenant. Therefore, the correct order is: resource specifications -> resource pool -> tenant.

For more information, see Create a tenant.

In this example, the server has 32 CPU cores and 128 GB of memory. The command to create a tenant is as follows:

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 no tenant type is specified when creating a tenant, it defaults to a MySQL tenant. It will only be an Oracle tenant if Oracle mode is explicitly specified.

Run the following command in the sys tenant:

SELECT * FROM DBA_OB_TENANTS;

The COMPATIBILITY_MODE field indicates the mode of the tenant.

Key configurations of a tenant

The following performance-related configurations are available for a tenant:

• min_cpu

  • Description: the minimum number of CPU cores available to the tenant (sum of all tenants' min_cpu <= cpu_count).
  • Recommended value: cpu_count/2
  • Related document: For more information about the min_cpu parameter, see Create a tenant under Create a resource unit > Parameter explanation.

• max_cpu

  • Description: the maximum number of CPU cores available to the tenant (sum of all tenants' max_cpu <= cpu_count * resource_hard_limit/ 100).
  • Recommended value: cpu_count/2 ~ cpu_count (or set it to the same value as min_cpu)
  • Related document: For more information about the min_cpu parameter, see Create a tenant under Create a resource unit > Parameter explanation.

• cpu_quota_concurrency

  • Description: the number of threads that a CPU core can concurrently handle (max_cpu * cpu_quota_concurrency is the maximum number of business threads).
  • Recommended value: 4 (Generally, we recommend that you do not modify the default value. For CPU-intensive stress tests, you can set it to 2.)
  • Related document: For more information about the cpu_quota_concurrency parameter, see 400.tenant-level-configuration-items/1800.cpu_quota_concurrency.md.

• memory_size

  • Description: the size of memory provided by the resource unit.
  • Recommended value: 50% to 80% of the total memory (you can flexibly allocate the memory size if you create multiple resource units).

• log_disk_size

  • Description: the size of the log disk.
  • Recommended value: Set it to a value that ensures normal operation. For example, you can set it to 40 GB to 80 GB. If a large amount of data is generated, you can set a larger value.

• primary_zone

  • Description: the priority of the zone where the primary replica is located. The priorities are the same on both sides of the comma and the priority on the semicolon-left side is higher than that on the semicolon-right side. For example, zone1,zone2,zone3.

  • Recommended value:

    1. If three servers have the same specifications and the network latency is similar, you can set the primary replica to be evenly distributed to improve performance. In this case, set the value to RANDOM.
    2. If one of the servers has higher performance or if you want to facilitate statistics and monitoring, you can specify that zone. For example, you can set the value to zone1.

Modify global configurations in the sys tenant

You can connect to the sys tenant to modify cluster-level configurations, such as the log level and the sql_audit parameter, which affect performance. Here is a sample configuration command:

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;

Configure Sysbench parameters

Prepare for and configure the key parameters of the Sysbench test by using a MySQL tenant as an example.

Procedure

• 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
  

Recommended parameter configurations

The recommended parameter configurations for performance in Sysbench are as follows:

• table_size

  • Description: the number of initial data rows per table. The default value is 10,000.
  • Recommended values: 100,000, 1,000,000

• tables

  • Description: the number of tables to initialize. This parameter is not supported in MySQL mode.
  • Recommended value: 30 to 100

• threads

  • Description: the number of threads to use. This parameter is not supported in MySQL mode.
  • Recommended value: 500 to 2,000 (the more CPU cores the test machine has, the greater the value can be)

• rand-type

  The parameter specifies the random number generation mode. It can be set to uniform, gaussian, special, or pareto, with the default value being special.

  1. special: the distribution mode where keys are concentrated in a few ranges. This mode is likely to cause lock conflicts.
  2. uniform: the discrete uniform distribution mode where all values in the range have an equal probability of being chosen. This mode does not have hotspots.
  3. gaussian: the Gaussian (or normal) distribution mode where data values are concentrated around the median.
  4. pareto: the Pareto distribution mode where data values are concentrated around the minimum value. Larger values have smaller probabilities of occurrence.

  Recommended value: uniform

• mysql-ignore-errors

  • Description: the error codes to ignore during the test. Once Sysbench receives an error, it stops sending new requests until all current requests return. Then the test exits. Therefore, you can ignore some error codes if they do not affect the test results.
  • Optional value: --mysql-ignore-errors=1062,1213,6002, where 1062 indicates primary key or unique index conflict; 1213 indicates dead lock; 6002 indicates transaction rollback.

• db-ps-mode

  • Description: whether to precompile SQL statements. The value can be auto or disable, with the default value being disable.
  • Recommended value: disable (we recommend that you set this parameter to disable)
  • If you set the value to auto, run the following command to modify the tenant configuration: alter system set open_cursors=65535;

• skip_trx

  • Description: whether to skip transactions in a read-only test. The default value is OFF, which means to start transactions.
  • Recommended value: You can set the value to ON in a read-only test. Otherwise, leave it as the default value.

• auto-inc

  • Description: whether to enable auto-increment for the ID column. The default value is ON.
  • Recommended value: OFF (we recommend that you set this parameter to OFF in a write scenario)

• warmup-time

  • Description: the warmup time (in s) before the test starts, which indicates how long to run the test before statistics are collected. The default value is 0.
  • Recommended value: 10 to 30 (a longer time is allowed for a larger volume of data)

For more information about Sysbench parameters, see Sysbench parameters.