Resolve transaction hash slowdown

[jnels124]

Problem

After executing the transactions to populate the increased entity load, it appears we have trouble with the transaction hash table. The database contains 1404024871 entries in transaction_hash. Once 10k testing started, the parser performance dropped drastically and this table appears to be the cause.

2025-08-26T19:44:37.534Z  INFO pool-10-thread-1 o.h.m.i.p.r.e.r.RedisPublisher Finished notifying 1011 messages in 11.47 ms
2025-08-26T19:44:37.534Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 992 rows to contract_log table in 11.45 ms
2025-08-26T19:44:37.590Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 14081 rows to crypto_transfer table in 56.11 ms
2025-08-26T19:44:37.644Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 31 rows to entity_temp table in 53.56 ms
2025-08-26T19:44:37.645Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 1 rows to record_file table in 865.6 μs
2025-08-26T19:44:37.655Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 1011 rows to topic_message table in 10.35 ms
2025-08-26T19:44:37.677Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 1 rows to topic_message_lookup_temp table in 21.71 ms
2025-08-26T19:44:40.967Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 3010 rows to transaction_hash table in 3.290 s
2025-08-26T19:44:40.998Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 2 rows to token_account_temp table in 30.77 ms
2025-08-26T19:44:41.034Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 4021 rows to transaction table in 35.71 ms
2025-08-26T19:44:41.046Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 1984 rows to token_transfer table in 12.39 ms
2025-08-26T19:44:41.046Z  INFO pool-11-thread-2 o.h.m.i.p.r.e.s.SqlEntityListener Completed batch inserts in 3.524 s
2025-08-26T19:44:41.046Z  INFO pool-11-thread-2 o.h.m.i.p.r.RecordFileParser Successfully processed 4021 items from 2025-08-26T19_17_56.010954000Z.rcd.gz in 3.557 s
2025-08-26T19:44:41.189Z  INFO pool-10-thread-1 o.h.m.i.p.r.e.r.RedisPublisher Finished notifying 954 messages in 11.97 ms
2025-08-26T19:44:41.189Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 1028 rows to contract_log table in 11.71 ms
2025-08-26T19:44:41.245Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 14034 rows to crypto_transfer table in 55.70 ms
2025-08-26T19:44:41.298Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 31 rows to entity_temp table in 53.08 ms
2025-08-26T19:44:41.299Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 1 rows to record_file table in 1.038 ms
2025-08-26T19:44:41.311Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 954 rows to topic_message table in 11.70 ms
2025-08-26T19:44:41.457Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 1 rows to topic_message_lookup_temp table in 146.0 ms
2025-08-26T19:44:45.025Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 3050 rows to transaction_hash table in 3.568 s
2025-08-26T19:44:45.056Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 2 rows to token_account_temp table in 30.93 ms
2025-08-26T19:44:45.127Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 4004 rows to transaction table in 70.54 ms
2025-08-26T19:44:45.144Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 2056 rows to token_transfer table in 17.06 ms
2025-08-26T19:44:45.144Z  INFO pool-11-thread-2 o.h.m.i.p.r.e.s.SqlEntityListener Completed batch inserts in 3.966 s
2025-08-26T19:44:45.144Z  INFO pool-11-thread-2 o.h.m.i.p.r.RecordFileParser Successfully processed 4004 items from 2025-08-26T19_17_58.016208260Z.rcd.gz in 3.995 s
2025-08-26T19:44:45.307Z  INFO pool-10-thread-1 o.h.m.i.p.r.e.r.RedisPublisher Finished notifying 992 messages in 13.41 ms
2025-08-26T19:44:45.307Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 998 rows to contract_log table in 13.38 ms
2025-08-26T19:44:45.365Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 13882 rows to crypto_transfer table in 57.87 ms
2025-08-26T19:44:45.427Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 31 rows to entity_temp table in 61.08 ms
2025-08-26T19:44:45.428Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 1 rows to record_file table in 1.165 ms
2025-08-26T19:44:45.441Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 992 rows to topic_message table in 13.47 ms
2025-08-26T19:44:45.463Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 1 rows to topic_message_lookup_temp table in 21.94 ms
2025-08-26T19:44:48.881Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 2976 rows to transaction_hash table in 3.417 s
2025-08-26T19:44:48.914Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 2 rows to token_account_temp table in 33.23 ms
2025-08-26T19:44:48.948Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 3968 rows to transaction table in 34.04 ms
2025-08-26T19:44:48.961Z  INFO pool-11-thread-2 o.h.m.i.p.b.BatchInserter Copied 1996 rows to token_transfer table in 12.97 ms
2025-08-26T19:44:48.961Z  INFO pool-11-thread-2 o.h.m.i.p.r.e.s.SqlEntityListener Completed batch inserts in 3.667 s
2025-08-26T19:44:48.962Z  INFO pool-11-thread-2 o.h.m.i.p.r.RecordFileParser Successfully processed 3968 items from 2025-08-26T19_18_00.007686263Z.rcd.gz in 3.701 s

Solution

Investigate and implement a solution to ensure inserts to this table are able to happen in a reasonable amount of time.

Alternatives

No response

[jnels124]

Note there was also a slow down for the rest endpoint as well:
| transactionsHash | /transactions/0xd0bbb54d8902998058eb3771c86db5e9ddf10ac84db06bb11d023fa8402bd48547cdedc3d08490972610a0b3fbab23ac | 1500 | 100.00 | 2095.00/s | 2095.00/s | 692.45ms | No | |