Add pgbench test

[dpxcc]

What feature are you requesting?

Add pgbench test

Why are you requesting this feature?

stress testing

What is your proposed implementation for this feature?

No response

[superctj]

@dpxcc Thank you for open-sourcing this great project! I am also interested in testing pg_mooncake with pgbench. Any update on this feature request? Is there anything in mooncake that needs to be changed or adapted for running pgbench?

[dpxcc]

Hi @superctj, thanks!
This issue is to track moving from a manual script to using pgbench for stress testing pg_mooncake
We’ve actually been using pgbench internally, but haven’t invested enough time to properly integrate it into the repo yet. Contributions are welcome!

[superctj]

Happy to help. Can you clarify the part on “proper integration into the repo?” I imagine there will be a script to automate the database initialization and workload running (with flags like number of clients configurable). Feel free to let me know if you have any other needs

[dpxcc]

Thanks! Yes, that’s right. All existing tests use pg_regress via pgrx, and we don’t have any test scripts for pg_mooncake yet, so we’ll need to add them. We’ve been manually running TPCC and CH benchmarks with pgbench, and it would be great to automate those. We just haven’t had the time to set this up yet

[superctj]

Thank you for the clarification. It looks like pgrx is from the Rust side, which I am not familiar with. I tried to run pgbench within the mooncake Docker image but the simple table name swapping trick does not work. Here's my attempt to run the default TPC-B like benchmark in pgbench (I understand the mooncake columnstore tables are not suitable for running the default workload in pgbench while this is simply to run something quick with mooncake and pgbench):

1. Created a database and populated data using pgbench (e.g., a regular PG table "pgbench_accounts" is created)
2. Created mooncake columnstore tables (e.g., "pgbench_accounts_mc") which correspond to regular PG tables created in the first step. At this point, I can query either "pgbench_accounts" or its columnar mirror "pgbench_accounts_mc" without issues)
3. Swapped their names (e.g., "pgbench_accounts" --> "pgbench_accounts_heap" and "pgbench_accounts_mc" --> pgbench_accounts)
4. Running pgbench threw a moonlink error like "Table ... not found at ... replication manager"

It seems table renaming breaks the mooncake catalog.

My second attempt:

1. Created a database and populated data using pgbench (e.g., a regular PG table "pgbench_accounts" is created)
2. Renamed PG tables (e.g., "pgbench_accounts" --> "pgbench_accounts_heap"
3. Created mooncake columnstore tables from renamed PG tables (e.g., "pgbench_accounts" mirrors "pgbench_accounts_heap")
4. Running pgbench threw errors like "not implemented: mooncake_relation_vacuum" and "not implemented: GetStorageInfo"

Neither attempt works. I was wondering if you could share a proper procedure of testing mooncake's HTAP performance with pgbench. As table name swapping does not work, the only way I can think of is to use a custom script with pgbench, which manually specifies the workload.

[superctj]

I realized from docs that mooncake v0.2 does not support direct writes to columnstore tables yet. In that case, in the custom script of pgbench, I think I need to put update/insert queries on PG tables and select queries on the columnstore tables. Is this a fair way to benchmark mooncake with pgbench?

It would also be great to get your insights into why table renaming breaks the mooncake catalog.

[dpxcc]

Thanks for looking into it!

Correct, mooncake table is read-only, writes should be done on the source Postgres table

Note that moonlink supports batched and streaming INSERT/UPDATE/DELETE, so pg_mooncake is not just a data warehouse but more of HTAP
We have run TPCC and the hybrid CH benchmark using pgbench on pg_mooncake before

For development, it is strongly recommended to use our devcontainer: https://github.com/Mooncake-Labs/pg_mooncake/blob/main/.devcontainer/devcontainer.json
One thing to note is that the default dev build is debug, and we should use release build for benchmarking

[superctj]

Thank you Cheng for the instruction! I'm using the release build for benchmarking.

Below is my custom pgbench script for running a TPC-B like workload. All updates/inserts are run against PG tables and the SELECT queries are run against mooncake columnstore tables. Does this look good to you?

-- 1. Set random variables for the transaction
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)

BEGIN;

-- 2. Update Account balance
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

-- 3. Select the new balance
SELECT abalance FROM pgbench_accounts_mc WHERE aid = :aid;

-- 4. Update Teller balance
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

-- 5. Update Branch balance
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

-- 6. Insert History record
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

END;

I plan to try the CH benchmark (as columnstore tables are not suitable for point queries in the TPC-B like workload). If I can make it work, I will be happy to contribute the script that automates the test process. If you have any suggestion/guideline for code contribution, feel free to let me know.

[dpxcc]

-- 2. Update Account balance
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

-- 3. Select the new balance
SELECT abalance FROM pgbench_accounts_mc WHERE aid = :aid;

Due to limitations in Postgres logical replication, the mooncake table can only see the last committed changes, not uncommitted ones. For example, when Step 2 writes to a Postgres table, querying the Mooncake table in Step 3 within the same transaction will not reflect those changes. You must commit Step 2 before the updates appear in the Mooncake table.

To clarify, the main goal of adding a pgbench test in pg_mooncake is to stress test moonlink to identify potential correctness and performance bugs there. The ideal workload will run three components concurrently:

1. One or two connections performing batch writes
2. TPCC–like concurrent streaming writes
3. TPCH–like analytical queries

(1) and (2) write to the Postgres table, while (3) reads from the mooncake mirror.

You can find more details on the internal workings of pg_mooncake and moonlink at: https://docs.google.com/presentation/d/1Mc19QagKENMUSOgeY6bb1TAxlpsos1QBfU_KmEm9AQ4/edit?usp=sharing

[superctj]

Thank you for the clarification and for sharing the slides! I enjoyed watching your CMU talk and appreciate the detailed insights you provided.