Toad World® Forums

scaling up TPC-E on MySQL


I have no problem creating a scale factor 1 (1,000 customers) TPC-E database.

But if I try a scale factor 10 (10,000 customers), then the 5 largest tables (E_TRADE_HISTORY, E_SETTLEMENT,E_TRADE,E_HOLDING_HISTORY, and E_CASH_TRANSACTION) will fail with error "ODBC Error (S1000,4B6) - [MySQL][ODBC 5.3(a) Driver][mysqld-5.7.8-rc]The total number of locks exceeds the lock table size "

All of the respective BAK tables are built fine. (so… building and loading the E_TRADE_BAK etc. tables was no problem).

My questions:

  1. Any best practices regarding loading big tables from BAK with Benchmark Factory?

  2. In particular, where are the LOAD statements? (I was considering customizing them for these tables)




Hi Veronica,

Are you able to adjust the INNODB_Buffer_Pool_size parameter to a higher size? This may help with the locking issues.

As for the BAK files, this is just a mechanism that BMF uses to avoid having to completely reload the tables for a benchmark after each test is run. It uses a create table as select to create the tables.


Hi Kevin,

Thanks for your reply!

Yes, increasing the innodb_buffer_pool_size resolves the issue for a 10,000-customer database, but it does not scale: we should not expect to have enough buffer memory for the largest tables at scale.

Yes, I understand what BMF does. Tried to reproduce it from MySQL shell, but still hit the locking issue even if I only load a subset of the rows. No matter how small that subset is:

mysql> insert into E_TRADE select * from E_TRADE_BAK where T_ID < 100;

ERROR 1206 (HY000): The total number of locks exceeds the lock table size


So, the constraint is the size of the BAK table.

I noticed none of the TPC-E (or TPC-C) tables, in BMF, are created with PRIMARY KEYs. Is there a reason for that?

Thanks again!