OceanBase vs MySQL/TiDB: Performance Evaluation, Deployment Guide, and Migration Practices

Background

The goal is to evaluate OceanBase against MySQL and TiDB in terms of performance, storage compression, and to migrate an internal project from MySQL to OceanBase.

Single‑Node Test

Preparation

File download and extraction

wget https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/download-center/opensource/oceanbase-all-in-one/7/x86_64/oceanbase-all-in-one-4.1.0.0-100120230323143519.el7.x86_64.tar.gz?Expires=1681878350&OSSAccessKeyId=LTAI5tGVLeRRycCRGerZJMNC&Signature=4E8%2FW77U1MAqq1ttNvuljadkTq0%3D
mv oceanbase-all-in-one-4.1.0.0-100120230323143519.el7.x86_64.tar.gz?Expires=1681878350 oceanbase-all-in-one-4.1.0.0-100120230323143519.el7.x86_64.tar.gz
tar -zxf oceanbase-all-in-one-4.1.0.0-100120230323143519.el7.x86_64.tar.gz -C /usr/local

Install related packages

/usr/local/oceanbase-all-in-one/bin/install.sh
# ...
Install Finished
Setup Environment: source ~/.oceanbase-all-in-one/bin/env.sh
Start Web Service: obd web
Quick Start: obd demo
More Details: obd -h

Modify configuration file

Components include oceanbase-ce, obproxy-ce, obagent, grafana, Prometheus.

cp /usr/local/oceanbase-all-in-one/obd/usr/obd/example/all-components.yaml ./
vi all-components.yaml

memory_limit 64G (tenant + system memory)

system_memory 30G

datafile_size 1500G (pre‑allocated on start)

log_disk_size (pre‑allocated)

# all-components.yaml
oceanbase-ce:
  servers:
    - name: server1
      ip: 127.0.0.1
  global:
    devname: lo
    memory_limit: 64G # The maximum running memory for an observer
    system_memory: 30G
    datafile_size: 1500G # Size of the data file.
    log_disk_size: 800G # The size of disk space used by the clog files.
    ...
server1:
    ...
    zone: zone1
obproxy-ce:
  depends:
    - oceanbase-ce
  servers:
    - 127.0.0.1
  global:
    ...
obagent:
  depends:
    - oceanbase-ce
  servers:
    - name: server1
      ip: 127.0.0.1
  global:
    home_path: /data/obagent1
ocp-express:
  depends:
    - oceanbase-ce
    - obproxy-ce
    - obagent
  servers:
    - 127.0.0.1
  global:
    ...

Deployment and Startup

obd cluster deploy obtest -c all-components.yaml
obd cluster start obtest
obclient -h127.0.0.1 -P4000 -uroot -Doceanbase -A
obclient -h127.0.0.1 -P2883 -uroot -Doceanbase -A

The command output shows observer, obproxy, obagent, and ocp‑express services all ACTIVE.

Create MySQL User (Tenant)

create resource unit ut1 max_cpu 32, memory_size '16G';
create resource pool p1 unit 'ut1', unit_num 1;
create tenant mysql resource_pool_list=('p1') set ob_tcp_invited_nodes='%';

Important configuration differences among OceanBase, TiDB, and MySQL are summarized in a table (memory, storage, flush settings, concurrency, test modes, test duration, etc.).

Performance Results

QPS: OceanBase achieves roughly one‑third of MySQL latency, with minimum average latency around 3 ms; storage compression reduces physical size to about 3/5 of MySQL.

Images (QPS, latency, compression) are referenced in the original article.

Binlog Compatibility

Data can be synchronized to downstream Kafka via OMS.

OceanBase supports MySQL binlog protocol for Kafka ingestion.

Full‑text binlog export is not yet supported in the community edition.

Cluster Test

Cluster deployment follows the same OBD workflow; hardware resources for control and data nodes are listed (e.g., 16C 64G, 32C 50G, etc.).

Deployment Preparation

Online deployment

yum install -y yum-utils
yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo
yum install -y ob-deploy
source /etc/profile.d/obd.sh

Offline deployment

tar -xzf oceanbase-all-in-one-*.tar.gz
cd oceanbase-all-in-one/bin/
./install.sh
source ~/.oceanbase-all-in-one/bin/env.sh

White‑Screen Deployment

obd web -p 80

Provides a visual UI with health checks.

OCP Management Platform (Nginx Forwarding)

cat oceanbase-ocp.conf
server {
    listen       80;
    server_name  ob-ocp-gz01.db.dmall.com;
    location / {
        proxy_pass http://10.xxxx.209:8180;
        index  index.html index.htm index.jsp;
    }
}

Tenant Creation Issues

Creating a tenant via CLI may hit timeout errors when memory_size is too large; creating through OCP UI succeeds.

Partitioned Table Example

CREATE TABLE `snapshot_innodb_status_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `stat_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  ...
  PRIMARY KEY (`id`,stat_time),
  KEY `idx_port` (`port`),
  KEY `idx_ip` (`ip`),
  KEY `idx_stat_time` (`stat_time`)
) PARTITION BY RANGE (year(stat_time)*100+month(stat_time)) (
  PARTITION p0 VALUES LESS THAN (202201),
  PARTITION p1 VALUES LESS THAN (202203),
  ...
  PARTITION p12 VALUES LESS THAN MAXVALUE
);

Notes: Community edition 4.1 does not support utf8mb4_unicode_ci or utf16_unicode_ci; primary/unique keys must include partition columns; partition key must be DATETIME, not TIMESTAMP.

DRC Synchronization MySQL → OceanBase

DRC tool and official OMS migration tool (OAT) are used. Compression ratio of ~1/6 (660 GB MySQL → 110 GB OceanBase) is observed.

Key Configuration

memory_limit : Maximum memory for an observer (recommended 58 GB for a 64 GB node).

system_memory : Memory reserved for system tenant (ID 500).

rootservice_memory_limit : Root service memory limit.

ob_query_timeout : Max query execution time in microseconds.

Common Commands

Manual Statistics Collection

CALL dbms_stats.gather_table_stats('test','sbtest1', granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE 128');
ALTER SYSTEM SET ENABLE_SQL_EXTENSION = TRUE;
ANALYZE TABLE sbtest1 COMPUTE STATISTICS FOR ALL COLUMNS SIZE 128;

Tenant Resource Creation

CREATE RESOURCE UNIT uc1 MAX_CPU 5, MIN_CPU 4, MEMORY_SIZE '36G', MAX_IOPS 128000, MIN_IOPS 128000, LOG_DISK_SIZE '2T';
CREATE RESOURCE POOL rp1 UNIT 'uc1', UNIT_NUM 2, ZONE_LIST ('zone1','zone2');
CREATE TENANT tt resource_pool_list=('rp1');

Viewing Configurations

SELECT t1.name resource_pool_name, t2.`name` unit_config_name, t2.max_cpu, t2.min_cpu,
       t2.memory_size/1024/1024/1024 memory_size_GB, t3.unit_id, t3.zone,
       CONCAT(t3.svr_ip,':',t3.svr_port) observer, t4.tenant_id, t4.tenant_name
FROM __all_resource_pool t1
JOIN __all_unit_config t2 ON (t1.unit_config_id=t2.unit_config_id)
JOIN __all_unit t3 ON (t1.resource_pool_id = t3.resource_pool_id)
LEFT JOIN __all_tenant t4 ON (t1.tenant_id=t4.tenant_id)
ORDER BY t1.resource_pool_id, t2.unit_config_id, t3.unit_id;

Limitations

Community edition does not support utf8mb4_general_ci or utf8mb4_unicode_ci.

User creation only supports plain‑text passwords.

Disk or memory quota exceedance results in errors; resolution requires expanding resource units or adjusting configuration (e.g., datafile_size, memstore_limit_percentage).

Summary

OceanBase shows superior QPS, latency, and storage compression compared with MySQL in the tested scenarios.

High‑TPS workloads may need tuning of tenant memory and dump parameters.

Resource isolation (IOPS, memory, CPU) per tenant makes OceanBase suitable for multi‑tenant environments.

OBD white‑screen deployment and OCP management platform simplify operations.

Application changes include partitioned tables for large datasets and tenant‑qualified usernames.

References

[1] OMS: https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001697223

[2] Partition table guide: https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000357627

[3] OAT: https://www.oceanbase.com/docs/enterprise-oat-doc-cn-10000000002026444

[4] Manual statistics collection: https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001699693

[5] Resource pool management: https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001702463

[6] Compatibility: https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001687857

[7] Tenant memory full handling: https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001702785