Unexplained higher CPU when using "union all"

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hi Folks,
We are using MyRocks as key value store. To make it easy to port HBase use cases, our PK has rowkey, column family name, column name and version.

When implementing getMulti(), which retrieves keys from multiple shards (each shard is a separate MyRocks table) on the same host, we used union all, where each select inside union all retrieves keys from one shard.

The above seems like a basic use case of “union all”, but we were really surprised that if we just use N get() calls (where each get() call gets only one key), CPU usage is noticeably lower rather if we use getMulti() calls (where each getMulti() call retrieves N keys).

Wondering if any known major inefficiencies in “union all” implementation?

Thanks,
Vamsi

1 Like
2

Hi @Vamsi_P,
MySQL can’t use more than 1 thread per query. So SELECT … UNION ALL … SELECT … UNION ALL… etc etc will only use 1 thread. However, if you ran N concurrent connections each executing a single-simple SELECT, the throughput will be much higher and CPU overall will be more efficiently utilized.