Performance issues with rocksdb bulk insert with multiple indexes

90 views
Skip to first unread message

Justin Swanhart

unread,
Dec 25, 2018, 4:36:49 PM12/25/18
to Percona Discussion
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

unread,
Dec 25, 2018, 4:39:23 PM12/25/18
to Percona Discussion
Forgot to mention that I'm using PS 5.7.24-27 
Reply all
Reply to author
Forward
0 new messages