HammerDB 4.7 TPROC-H SF 1000 receiving an "Out of range value for column 'L_ORDERKEY' at row 1" error"

[sm-shaw]

Discussed in #550

^{Originally posted by anrahman4 May 9, 2023}
I am currently running HammerDB 4.7 on Ubuntu 22.04.2 Linux with kernel 5.15.0-71-generic. I have installed MySQL 8.0 mysql Ver 8.0.33-0ubuntu0.22.04.1 as well as libmysqlclient21 through the use of the apt package manager. This is a single node configuration.

I ran a build schema workload overnight for a TPROC-H database with a scale factor of 1000 with 64 virtual users. The platform is dual socket and has a total of 128 physical CPU cores, so I assume 64 shouldn't be too high of a number to execute in parallel. I have also done this in the past for other databases and MySQL on other server platforms that had CPU configurations that could support it and had no problems.

When I come back to it, it shows the following error: "Out of range value for column 'L_ORDERKEY' at row 1" error" for 42 of the virtual users. These virtual users seem to be stuck at the "Loading ORDERS and LINEITEM..." steps. The other 22 virtual users don't have any problem and seem to have completed their row insertion with "Loading TPCH TABLES COMPLETE".

I then tried a fresh re-install of everything, but came across the same issue a second time. I understand MySQL supposedly does not have support for data analytics as seen in the documentation here: https://www.hammerdb.com/docs/ch12s02.html#d0e4329

I assumed this statement meant that there is no special configuration that would optimize the database for a data analytics focused query-set, and not that it was simply not possible at all. I would assume you should still be able to create the table just fine and run the workload, even if it doesn't perform potentially as well as other databases that do have configuration options to optimize data analytics workloads.

It is not a storage capacity issue, as the database is storing its data on a mounted XFS file system on a drive that has 3.84 TB of capacity. Running the 'df -h' command as well as 'sudo nvme list' shows that I am using 1.2 TB only, and still have 2.4 TB total of remaining space.

I was wondering if I could get some pointers as to where this is going wrong. Thank you very much.

[sm-shaw]

As per discussion. Out of range value for column 'L_ORDERKEY' is likely to be due to using INT for the L_ORDEKEY in the lineitem table.

set sql(8) "CREATE TABLE LINEITEM (
L_SHIPDATE DATE NULL,
L_ORDERKEY INT NOT NULL,
...

so if this exceeds the values here https://dev.mysql.com/doc/refman/8.0/en/integer-types.html then that would result in this error and it needs to be changed to BIGINT.

We have not tested on 1TB before due to the single-threaded nature of MySQL queries. However now we are seeing analytic storage engines it is definitely worth updating to ensure larger schemas are supported.

This is straightforward to change. The line in the source is here:

https://github.com/TPC-Council/HammerDB/blob/6a8d32d25e3353f66d8b4cbc47c209bb897d0ae2/src/mysql/mysqlolap.tcl#LL188C5-L188C5

In your existing HammerDB installation, you can edit the file /src/mysql/mysqlolap.tcl and change it to L_ORDERKEY BIGINT NOT NULL for testing. INT is used elsewhere as well, so it may be the case that this change is needed in other places as well. Once it is changed when you next start HammerDB it will pick up the change automatically, so you don't need to build from source.

If this all works well and the schema builds successfully, why not submit a pull request yourself to add the change and become a contributor?

What you would need to do is to fork the HammerDB repository, then create a branch with your changes committed and then submit a pull request back to https://github.com/TPC-Council/HammerDB/ - accepting the pull request would include the change.

For a step-by-step guide up to doing the pull request, there is a series of blog posts here:

https://www.hammerdb.com/blog/uncategorized/how-to-add-your-database-to-hammerdb-pt1-opening-an-issue/
https://www.hammerdb.com/blog/uncategorized/how-to-add-you-database-to-hammerdb-pt2-fork-clone-and-binary-downloads/
https://www.hammerdb.com/blog/uncategorized/how-to-add-your-database-to-hammerdb-pt3-adding-to-and-modifying-the-source/
https://www.hammerdb.com/blog/uncategorized/how-to-add-your-database-to-hammerdb-pt4-commit-changes-and-pull-request/

These are not identical, as it is for adding a database and also now we can build from source https://www.hammerdb.com/blog/uncategorized/how-to-build-hammerdb-from-source/. However, they include all the key steps and any questions feel free to ask here or in the original discussion topic. #550