Performance issues with rocksdb bulk insert with multiple indexes

[Justin Swanhart]

I am loading the following table with LOAD DATA INFILE in multiple threads:

CREATE TABLE IF NOT EXISTS lineorder

(

    LO_OrderKey bigint not null,

    LO_LineNumber tinyint not null,

    LO_CustKey int not null,

    LO_PartKey int not null,

    LO_SuppKey int not null,

    LO_OrderDateKey int not null,

    LO_OrderPriority varchar(15),

    LO_ShipPriority char(1),

    LO_Quantity tinyint,

    LO_ExtendedPrice decimal,

    LO_OrdTotalPrice decimal,

    LO_Discount decimal,

    LO_Revenue decimal,

    LO_SupplyCost decimal,

    LO_Tax tinyint,

    LO_CommitDateKey int not null,

    LO_ShipMode varchar(10),

    key(LO_CustKey),

    key(LO_PartKey),

    key(LO_SuppKey),

    key(LO_OrderDateKey)

)

PARTITION BY HASH (LO_OrderDateKey) PARTITIONS 32;

With the four indexes, loading from 32 threads yields only 5000 handler_write per second.  

If I remove all of the indexes except LO_OrderDateKey (so there is only one index on the table) I get 150000 handler_write per second.  This is a dramatic performance difference.  

I  am wondering if there is something I can tune to get better performance when there are multiple indexes on the table?  I haven't tuned RocksDB engine at all, I'm using just the default settings.

To get the data to load, use http://github.com/greenlion/ssb-dbgen

./dbgen -T l -s 160 -C 32 (32 is the number of cores, it will generate one file per thread)

Then run 32 LOAD DATA INFILE simultaneously against the table.  The delimiter is '|'.

Then monitor how quickly the handler_write status counter is incremented, with mext or whatever.

--Justin Swanhart

[Justin Swanhart]

Forgot to mention that I'm using PS 5.7.24-27