Add support for Oceanbase in MySQL TPROC-C

[KingKongLittle]

Describe the bug
Transaction Counter For Oceanbase4.2.3.0 ( for mysql TPROC-C ) cannot be displayed

To Reproduce

Expected behavior
When I Test Oceanbase4.2.3.0 use Mysql TPROC-C options,The Transaction Counter can't displayed,
It show the proc read_more is return no value.
After I replace :
set sqc "show global status where Variable_name = 'Com_commit' or Variable_name = 'Com_rollback'"
with
set sqc "select REPLACE( REPLACE(name,'trans commit count','Com_commit'),'trans rollback count','Com_rollback') Variable_name,sum(value) from oceanbase.gv$sysstat where name in ('trans rollback count','trans commit count') group by name "

then Transaction Counter is OK.

Screenshots

HammerDB Version (please complete the following information):

• Version: 4.4

Operating System (please complete the following information):

• Client OS: Centos 6.5

Database Server (please complete the following information):

• Database name: OceanBase
• Database Release Version :4.2.3.0

Database Client (please complete the following information):

• Database client name: jdbc 8.0.33

Additional context

[sm-shaw]

At the moment we don't have support for Oceanbase with MySQL TPROC-C, However the good news is that Pull Request #670 adds support for Oceanbase for MySQL TPROC-H and as this adds support for querying Oceanbase queries (as opposed to transactions) for the transaction counter and this will be available in v4.11.

To add support for Oceanbase TPROC-C we could not just change the statement as that would then break it for regular MySQL.

Therefore, it needs something similar to the TPROC-H support i.e. an Oceanbase compatible option/checkbox and the additional connection options added if this is selected such as <mysql_oceanbase_port>2881</mysql_oceanbase_port> and <mysql_ob_tenant_name>tpch</mysql_ob_tenant_name>.

When this is selected, it would then use the statement you show or something similar, however, does it also need other options such as the tenant name adding as the TPROC-H workload does? It would also need the statement changing in the driver script for when it reports the final TPM value.

From your screenshots it looks as if the schema built and the workload ran OK, but it would also need to clarify options such as whether the stored procedures run OK or whether the client SQL option is necessary and stored procedures get greyed out.

So, the core team don't have access to Oceanbase and would therefore not add this feature. (however as with #670 we can check if it breaks existing functionality) Nevertheless, #670 provides an excellent template for exactly what you want to do, where #670 adds support for TPROC-H and you want to do this for TPROC-C.

Therefore, the best approach is for you to develop this feature and make a pull request. Perhaps also @yaqi-zhao and @xtangxtang who did #670 may be willing to assist you to add Oceanbase support for TPROC-C also and make a pull request to do so.

[sm-shaw]

And if you add the change by making the PR, you can find that the transaction counter is improved in the current version:

and if you run the web service to query the stored job data it would also then add the transaction count to the job data as shown here (Click on Jobs in the tree view, start web service and browse)

[KingKongLittle]

It look like the schema built and the workload and stored procedures run OK .

[sm-shaw]

What would be useful would be to run the Schema Check introduced at v4.10. you can see the GUI "Check" option in my screenshot above or you can run the CLI command "schemacheck" - If this runs OK and you can run with stored procedures then that would verify that the build ran OK. Then it would be a case of adding the Oceanbase compatible checkbox, the additional connection options such as port and tenant name and then the transaction counter query in both the transaction counter and the driver scripts (in multiple places).
Also currently we just have the tcl example CLI scripts for Oceanbase TPROC-H and therefore it would also need these completing fully for both Tcl and Python for Oceanbase for both TPROC-C and TPROC-H.

[sm-shaw]

A blog post on how to verify the schema is here https://www.hammerdb.com/blog/uncategorized/hammerdb-v4-10-new-features-schema-and-consistency-checks/

[KingKongLittle]

Error in Virtual User 1: Error: mysqlexec/db server: MySQL compatible temporary table not supported

[KingKongLittle]

about temporary table, It was supported in the previous Oceanbase 3.x version, but in the new Oceanbase 4.x version it has been restructured, and the support will be available around September.

[KingKongLittle]

This is a TPROC-C test result :

[KingKongLittle]

It seems the test was successful.

[sm-shaw]

yes that looks successful, so it looks like support for TPROC-C for oceanbase is entirely possible and the performance looks good.
So it looks like we need the following in a PR from someone who can test on Oceanbase and then we can verify that it still all works against regualr MySQL.

• Oceanbase compatible checkbox for TPROC-C driver
• the additional connection options such as port and tenant name
• the transaction counter query as an option in both the transaction counter
• the transaction counter query in the driver scripts (in multiple places).
• temporary table support for the checkschema command (or support in oceanbase so the current one runs correctly)

[KingKongLittle]

When i use V4.11 an error occurred "Error in Virtual User 1: Error: invalid command name "maria::sel""

But the SQL statement with "SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$db'"
And "SELECT COUNT(DISTINCT TABLE_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$db'"
All return the correct result :

[sm-shaw]

Yes that is fixed here a56b984 that will go into the 4.11 release that is still undergoing testing before release.

[KingKongLittle]

It is OK.

For good performance ，can use SQL statement in OceanBase :

ALTER TABLE customer PARTITION BY KEY(c_w_id) PARTITIONS 12;
ALTER TABLE district PARTITION BY KEY(d_w_id) PARTITIONS 12;
ALTER TABLE history PARTITION BY KEY(h_w_id) PARTITIONS 12;
ALTER TABLE new_order PARTITION BY KEY(no_w_id) PARTITIONS 12;
ALTER TABLE order_line PARTITION BY KEY(ol_w_id) PARTITIONS 12;
ALTER TABLE orders PARTITION BY KEY(o_w_id) PARTITIONS 12;
ALTER TABLE stock PARTITION BY KEY(s_w_id) PARTITIONS 12;
ALTER TABLE warehouse PARTITION BY KEY(w_id) PARTITIONS 12;
CREATE TABLEGROUP hammerdb_group SHARDING = 'PARTITION';
ALTER TABLE customer SET TABLEGROUP hammerdb_group ;
ALTER TABLE district SET TABLEGROUP hammerdb_group ;
ALTER TABLE history SET TABLEGROUP hammerdb_group ;
ALTER TABLE new_order SET TABLEGROUP hammerdb_group ;
ALTER TABLE order_line SET TABLEGROUP hammerdb_group ;
ALTER TABLE orders SET TABLEGROUP hammerdb_group ;
ALTER TABLE stock SET TABLEGROUP hammerdb_group ;
ALTER TABLE warehouse SET TABLEGROUP hammerdb_group ;

call dbms_stats.gather_schema_stats('tpcc',degree=>96);

[sm-shaw]

There is already a partitioning option for MySQL, however with an oceanbase checkbox you can add any specific partitioning for Oceanbase there.
In the screenshots it looks like you are missing the fonts the required packages are detailed here https://www.hammerdb.com/docs/ch01s01.html#d0e109

[KingKongLittle]

ALTER TABLE item DUPLICATE_SCOPE= 'cluster';
can Improve test scores

[KingKongLittle]

Alicoud Node specifications : 14cpu70g

[sm-shaw]

Lets leave this issue open as "help required" as someone with Oceanbase access may wish to add support as it looks straightforward, for example @yaqi-zhao and @xtangxtang can advise if there is interest in doing this?

As as a summary, the key enhancement is:

• Add support for oceanbase TPROC-C workload as an option to the existing MySQL TPROC-C using existing TPROC-H Add Support for oceanbase tpch #670 as a template

• Oceanbase compatible checkbox for TPROC-C driver (example below, options only become active when the Oceanbase Database compatible checkbox is selected (Note that the options dialogs have changed to 2 tabs to reduce the size since the original Oceanbase TPROC-H PR)

• the additional connection options such as port and tenant name. Example below for TPROC-H so something similar needs adding for TPROC-C. The mysql_oceanbase_port supports both but additional options needed under tpcc. Note that changes to mariadb.xml are only read the first time that HammerDB is started and then stored in SQLite so for development removing the SQLite file is necessary to reinitialize with new options.

<?xml version="1.0" encoding="utf-8"?>
<mysql>
..
        <mysql_oceanbase_port>2881</mysql_oceanbase_port>
    </connection>
..
    <tpch>
        <schema>
            <mysql_ob_tenant_name>tpch</mysql_ob_tenant_name>
..
            <mysql_tpch_obcompat>false</mysql_tpch_obcompat>
            <mysql_ob_partition_num>1</mysql_ob_partition_num>

When set up correctly print dict will show the new settings:

hammerdb>print dict
Dictionary Settings for MySQL
connection {
...
 mysql_oceanbase_port     = 2881
}
tpch       {
...
 mysql_ob_tenant_name      = tpch
..
 mysql_tpch_obcompat       = true
 mysql_ob_partition_num    = 1
...
}

Once the configuration is done then the additional task are:

• the transaction counter query as an option when mysql_tpcc_obccompat is selected.
• the transaction counter query in the driver scripts (in multiple places).
• temporary table support for the checkschema command (or support in oceanbase so the current one runs correctly)
• Potential enhancements as described in this issue for oceanbase only
• Verify that the original MySQL workload runs without errors when mysql_tpcc_obcompat is not selected.

[sm-shaw]

Changed title of Issue to be clear for where help is wanted

[yourchanges]

Alicoud Node specifications : 14cpu70g

@KingKongLittle
Could you share the tpcc result in this env? thank you very much!