Toad World® Forums

scaling up TPC-E on MySQL


#1

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)

Thanks!

Veronica.


#2

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.


#3

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

mysql>

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!

Veronica.