TPC-C-compatible templates for `dbgen`
======================================
This folder provides template files and a Python script to produce SQL dump compatible with
the [TPC-C] v5.11.0 benchmark. The table names are compatible with [BenchmarkSQL].
| Output format | SQL dump split into 256 MiB files | 8 large CSV files |
| Total size per warehouse | 80 MiB | 70 MiB |
| Speed (-j 8, W = 30) | 30s | 50s |
| Speed (-j 8, W = 50) | 40s | 80s |
## Usage
1. Download or clone this repository
2. Install Python 3.7 (or above).
3. Download or build `dbgen`.
Pre-compiled binaries can be downloaded from <https://github.com/kennytm/dbgen/releases>.
Decompress the `*.tar.xz` from the assets of the latest release to get the `dbgen` executable.
We recommend placing it into the folder `./target/release/`.
You can also build `dbgen` from source with Rust 1.40 (or above). After installing Rust, run
`cargo build --release`.
4. Execute the generator script. Suppose we want to create a 30-warehouse dump in the `tpcc-out/`
folder:
```sh
python3 res/tpcc/gen.py -o tpcc-out -w 30
```
<details><summary>The SQL dump is split into multiple files in subdirectories of
<code>tpcc-out</code>. They are lexicographically sorted by the proper import order.</summary>
```
tpcc-out/
0_config/
tpcc-schema-create.sql
tpcc.bmsql_config-schema.sql
tpcc.bmsql_config.1.sql
1_item/
tpcc.bmsql_item-schema.sql
tpcc.bmsql_item.1.sql
2_warehouse/
tpcc.bmsql_warehouse-schema.sql
tpcc.bmsql_warehouse.1.sql
3_stock/
tpcc.bmsql_stock-schema.sql
tpcc.bmsql_stock.001.sql
tpcc.bmsql_stock.002.sql
…
4_district/
tpcc.bmsql_district-schema.sql
tpcc.bmsql_district.1.sql
5_customer/
tpcc.bmsql_customer-schema.sql
tpcc.bmsql_customer.01.sql
tpcc.bmsql_customer.02.sql
…
6_history/
tpcc.bmsql_history-schema.sql
tpcc.bmsql_history.01.sql
tpcc.bmsql_history.12.sql
…
7_order/
tpcc.bmsql_oorder-schema.sql
tpcc.bmsql_oorder.001.sql
tpcc.bmsql_oorder.002.sql
…
tpcc.bmsql_order_line-schema.sql
tpcc.bmsql_order_line.001.sql
tpcc.bmsql_order_line.002.sql
…
8_new_order/
tpcc.bmsql_new_order-schema.sql
tpcc.bmsql_new_order.1.sql
tpcc.bmsql_new_order.2.sql
…
```
</details>
5. Load the SQL dump into the database. Typically you can simply pipe the files into the database
client, e.g.
* **SQLite3**
```sh
export LANG=C
rm -f tpcc.db
for f in tpcc-out/*/*.*.sql; do
echo "$f"
sqlite3 tpcc.db < "$f" || break
done
```
* **PostgreSQL via `psql`**
Make sure you have CREATE privilege in the chosen database to create the `tpcc` schema.
```sh
export LANG=C # make sure '-' is sorted before '.'
export PGOPTIONS=--search_path=tpcc
psql postgres -c 'drop schema if exists tpcc cascade;'
for f in tpcc-out/*/*.sql; do
echo "$f"
psql postgres -q -1 -v ON_ERROR_STOP=1 -f "$f" || break
done
```
* **MySQL via `mysql`**
Make sure you have CREATE privilege to create the `tpcc` database.
```sh
export LANG=C
mysql -u root 'drop schema if exists tpcc; create schema tpcc;'
for f in tpcc-out/*/*.sql; do
echo "$f"
mysql -u root < "$f" || break
done
```
* **MySQL via [myloader]**
`myloader` restores an SQL dump into MySQL in parallel. It automatically manages the import
order but expects SQL files in a flat directory, so we first need to flatten it. Then we can
ingest the entire directory in one go.
```sh
# Transform the output directory into mydumper structure.
mv tpcc-out/*/* tpcc-out/
touch tpcc-out/metadata
# Disable foreign key checks, since the files are imported in no particular order.
mysql -u root -e 'set @@global.foreign_key_checks = 0;'
# Now import the entire directory.
myloader -u root -B tpcc -d tpcc-out/
# Re-enable foreign key checks.
mysql -u root -e 'set @@global.foreign_key_checks = 1;'
```
* **[TiDB] via [TiDB Lightning]**
The output structure is directly compatible with TiDB Lightning and can be used directly.
Note that, before v4.0, TiDB does not support the SERIAL alias. You may need to manually
replace its use in `6_history/tpcc.bmsql_history-schema.sql` as
BIGINT UNSIGNED AUTO_INCREMENT first.
```sh
sed -i'' 's/serial/bigint unsigned auto_increment/' tpcc-out/6_history/tpcc.bmsql_history-schema.sql
# ^ Not needed for TiDB Lightning 4.0 or above
tidb-lightning -d tpcc-out/ --tidb-host 127.0.0.1
```
[TPC-C]: http://www.tpc.org/tpcc/
[BenchmarkSQL]: https://sourceforge.net/projects/benchmarksql/
[myloader]: https://github.com/maxbube/mydumper
[TiDB]: https://pingcap.com/docs/
[TiDB Lightning]: https://pingcap.com/docs/stable/reference/tools/tidb-lightning/overview/