This topic describes how to assess the performance differences between an Oracle database and an OceanBase database.
Use any client that can connect to the Oracle database (such as sqlplus) to collect the load file (which is specified by the
--source-fileparameter in the subsequent file analysis commands) from the Oracle database and save it to local.Analyze the files.
Run
start.shby referring to the following sample commands. For more information about the parameters in the script, see Product form. In Microsoft Windows, replacesh bin/start.shwithstart.bat.sh bin/start.sh \ # The task name. --name test \ # A fixed value that indicates a database replay. --mode REPLAY \ # A fixed value that indicates the analysis phase of replay. --replay-phase COLLECT \ # The source file. --source-file "/root/oma/test" \ # The number of parallel threads, which affects the replay performance. We recommend that you set the value to be as large as possible if the server performance permits. --parallel-count 20 \ # The schema name corresponding to the UID. The schema name must be in uppercase. Multiple schema names must be separated with commas (,). Note that only one hyphen (-) is added to the beginning of this parameter. -DuseUidToSchemaMap=103:UWBPS,20:UBXYZ \ # The number of parallel threads during parsing. Specify this parameter based on the server performance. Default value: 10. --parse-thread-count 200 \ # The extended configuration file, which is a standard Java configuration file. You can leave this parameter unspecified if no special configuration is required. # In the configuration file, specify one configuration item in each line and separate multiple configuration items with equal signs (=). --extend-configure "/your/path/file/name.conf" \ # The date format of OceanBase Database. You can use this parameter to convert the date format. # By default, if this parameter is not specified, the date format is DD-MON-RR. You can run the show variables like '%nls%format%' command to view the specific NLS configuration. --nls-format "YYYY-MM-DD" # The following parameters are required only when you want to tune SQL statements. # Indicates to enable tuning. --tuning-mode \ # The IP address of the source Oracle database. --source-db-host xxx.xxx.xxx.xxx \ # The port number of the source Oracle database. --source-db-port 1521 \ # The username of the source Oracle database. --source-db-user user \ # The password of the source Oracle database. --source-db-password password \ # The service name of the source Oracle database. --source-db-service-name orcl11g.us.oracle.comThe extended configuration file specified by the
--extend-configureparameter is a standard Java configuration file. The extended configuration file must contain theignore_sqls=XXX,AAA,XXXXparameter to correctly parse out the SQL ID. Multiple IDs must be separated with commas (,). The IDs are not parsed and are no longer displayed during the replay. Example:ignore_sqls=0046uu0mqdjr1,5rsm4y10jd4p2After the analysis is completed, the analysis results are generated, including the overall chart of queries per minute (QPM). The step size is 60s. Statistical information of the specified schema is displayed, including QPM and the total number of queries.
Task name: [ test-20210530_233520 ] SCHEMA : [ schema_name ] : -----================ [ schema_name ] Traffic histogram [ Step size: 60 seconds ]===============------- 4352.0┤ ─ ─ ─ ─ ─ ─ ─ ─ ─ █ ─ ─ ─ ─ ─ ─ ─ 4134.4┤ ─ ─ ─ ─ ─ ─ ─ ─ ─ █ ─ ─ ─ ─ ─ █ ─ 3916.8┤ ─ ─ ─ ─ ─ ─ ─ ─ ─ █ █ ─ ─ ─ ─ █ ─ 3699.2┤ ─ ─ ─ ─ ─ ─ ─ ─ ─ █ █ ─ ─ ─ ─ █ ─ 3481.6┤ ─ ─ ─ ─ ─ ─ ─ ─ ─ █ █ ─ ─ ─ █ █ ─ 3264.0┤ ─ ─ ─ ─ ─ ─ ─ ─ █ █ █ ─ ─ ─ █ █ ─ 3046.4┤ ─ ─ ─ ─ ─ ─ ─ █ █ █ █ █ █ ─ █ █ ─ 2828.8┤ ─ ─ ─ ─ █ ─ ─ █ █ █ █ █ █ █ █ █ ─ 2611.2┤ ─ ─ ─ ─ █ ─ ─ █ █ █ █ █ █ █ █ █ ─ 2393.6┤ █ ─ ─ ─ █ ─ █ █ █ █ █ █ █ █ █ █ ─ 2176.0┤ █ ─ ─ ─ █ █ █ █ █ █ █ █ █ █ █ █ █ 1958.4┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 1740.8┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 1523.2┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 1305.6┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 1088.0┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 870.4┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 652.8┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 435.2┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 217.6┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ Start time: 2021-03-09 09:59:09 End time: 2021-03-09 10:10:15 Time spent (seconds): 666 Requests: 50774 ******************************************************************************************After the analysis is completed, a folder named after the task name is generated in the
./dump/directory. Example:test-20210530_233520. The folder stores replayable files in JSON format.Replay the files.
Run
start.shby referring to the following sample commands. For more information about the parameters in the script, see Product form. In Microsoft Windows, replacesh bin/start.shwithstart.bat.bin/start.sh \ # The task name. --name send1 \ # A fixed value that indicates a database replay. --mode REPLAY \ # A fixed value that indicates the sending phase of replay. --replay-phase SEND \ # The replay mode. Valid values: READ, WRITE, READ_WRITE, PL, and ALL. Default value: READ. --replay-mode ALL \ # The path of files to be replayed. --source-file "./dump/test" \ # Specifies to replay in order. --replay-process-name "sort" \ # Specifies the period after which the replay will be restarted, in the unit of seconds. Default value: 0, which specifies to immediately start the replay. --delay-start-time 5 \ # The replay scale. The default value of this parameter is 1, which indicates that the replay will run at the original speed. --replay-scale 5 \ # The sampling ratio, which ranges from 0 to 1. The default value is 1, which indicates full sampling. --replay-sample 1 \ # The maximum number of threads, which is 400 by default and cannot exceed 2,000. --max-parallel 400 \ # Due to the cold start during a replay, a warm-up time must be specified. --warm-up 30 \ # The IP address of the destination database. You can specify only one destination database for replay. --target-db-host xxx.xxx.xxx.1 \ # The port number of the destination database. --target-db-port 2883 \ # The tenant name and cluster name, which must be separated with a number sign (#). The tenant name precedes the cluster name. # At present, multi-user replay is supported only within the same tenant. --target-db-tenant-cluster "<tenant_name>#<cluster_name>" \ # The login username and password, which must be separated with a colon (:). Multiple usernames and passwords must be separated by commas (,). Please note that there is only one hyphen (-) before DuserAndPassword, and the first username must be ROOT, SYS, or a superuser with high privileges, which is used to obtain the SQL AUDIT information of OceanBase Database. If the first user account cannot access the Schema to be replayed, you need to specify another username and password with access to the corresponding Schema. -DuserAndPassword="root:******,username:******" \ # The custom configuration for the JDBC connection. You can specify the JDBC parameters based on the actual situation, such as useUnicode:true,characterEncoding:utf8. This parameter is optional. -DjdbcConnectionConfigure=useUnicode:true,characterEncoding:utf8 \ # If you set this parameter to XX, the system will forcibly exit the main process when the main process has not sent any data for XX minutes. --monitor-processor-time 10 \ # The interval for scanning the SQL_AUDIT view, in seconds. Default value: 60. Set this parameter based on your requirements. --sql-audit-interval 300 \ # The mode of OceanBase Database. --ob-mode MYSQL \ # The Schema of OceanBase Database that needs to be replayed. --target-db-schemas "AAA,BBB" \ # The type of the destination database. --target-db-type OBMYSQL # Specifies to replay transactions. If this parameter is absent, transactions are not replayed. --with-replay-transactionAfter this command is executed, the real-time replay progress is displayed.
Destination SCHEMA :::SCHEMA : [ schema_name ] : -----================ [ schema_name ] Traffic histogram [ Step size: 12 seconds ]===============------- 4412.0┤ █ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ 4191.4┤ █ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ 3970.8┤ █ ─ ─ ─ ─ ─ ─ ─ █ █ ─ ─ ─ ─ █ ─ 3750.2┤ █ ─ ─ ─ ─ ─ ─ ─ █ █ ─ ─ ─ ─ █ ─ 3529.6┤ █ ─ ─ ─ ─ ─ ─ ─ █ █ ─ ─ ─ ─ █ ─ 3309.0┤ █ ─ ─ ─ ─ ─ ─ ─ █ █ █ ─ ─ █ █ ─ 3088.4┤ █ ─ ─ ─ ─ ─ ─ █ █ █ █ █ ─ █ █ █ 2867.8┤ █ ─ ─ █ ─ ─ █ █ █ █ █ █ █ █ █ █ 2647.2┤ █ ─ ─ █ ─ ─ █ █ █ █ █ █ █ █ █ █ 2426.6┤ █ ─ ─ █ █ ─ █ █ █ █ █ █ █ █ █ █ 2206.0┤ █ ─ ─ █ █ █ █ █ █ █ █ █ █ █ █ █ 1985.4┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 1764.8┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 1544.2┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 1323.6┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 1103.0┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 882.4┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 661.8┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 441.2┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ 220.6┤ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ █ Start time: 2021-05-31 00:14:04 End time: 2021-05-31 00:17:15 Time spent (seconds): 190 Requests: 50774 ******************************************************************************************View the replay report.
- After the replay is completed, a folder containing CSV files is generated in the
reportdirectory. You can view the traffic replay comparison between the source and target databases by using the CSV report files in the folder. The SQL statements in the CSV files are aggregated based on the SQL IDs.
The folder name is in the format of "Time and date+string", and the CSV file name is in the format of "Schema name.csv". If multiple schemas exist, multiple corresponding CSV files are generated.
- After the replay is completed, a folder containing CSV files is generated in the