Yugabyte-db: Mulltiple indexes performance

[ducquyen2509]

The context:

I benchmark with 30mil records using YCSB without index and the result is 16k ops/s for insert statement.

I increase the record to 50mil with indexed 5 field and the result drop down dramatically to 2k ops/s.

I don’t know it’s a bug or Yugabytedb doesn’t support with multiple indexes.
I'm looking forward for your reply soon.

[ntphi]

Oh, I have the same issue! @yugabyte-ci
I hope this issue would be resolved ASAP.

[amitanandaiyer]

@ducquyen2509 @ntphi When you are writing to a table with _k_ indexes, essentially each client-side write will internally result in 1+_k_ to 1 + 2*_k_ writes.

• 1 for the insert into the main table.
• _k_ to update the _k_ indexes, and
• possibly _k_ more to delete the old index values from the _k_ indexes.

So, it is somewhat expected that the overall number of client ops will be lower depending on how many indexes you have. Given that you are seeing about 16k without indexes, I'd expect you to see about 2-3k with 5 indexes.

[ntphi]

Oke, thank you for your answer.
But in my case, I am required to maintain performance so are there any other approach?

Additionally, when I create an index, will YB create a new table for that index or use the same table for all indexes?

[ddorian]

@ntphi A new virtual table will be created to store the index.
Another reason is that keeping the index consistent requires distributed transactions compared to insert transactions into a table without indexes (assuming the writes hit 1 tablet).

We have Colocated Tables that put all tables/indexes into 1 tablet thus lowering overhead of distributed transactions. Can you try it ?

[ntphi]

@ddorian,
Ye, I tried, but there are no difference.

[z0mb1ek]

@amitanandaiyer can performance with indexes increase by adding more nodes?

[rkarthik007]

@amitanandaiyer can performance with indexes increase by adding more nodes?

Yes, that's correct, the performance scales linearly with number of nodes - so you can get better throughput with more nodes.

[rkarthik007]

Also, for folks interested in why these two paths have dramatically difference throughput profiles, here is the relevant section in our docs/architecture section: transactions overview - fast path versus distributed transactions.