I have an InfluxQL query (Influxdb 1.8) & it takes a lot of time for it to finish even on c5.4xlarge with Intel Xeon Platinum 8000 with Turbo CPU clock speed of up to 3.6 GHz + EBS io2 with 16k iops available.
But when I look at htop I see only 1 core loaded 100% & the rest are idle. Aws monitoring shows that volume iops while running the query are ~8k which is far from max 16k & ram is at 20%.
Is there a way to optimize the query to spread the load on all cores?
I have other queries & they load all cores ok.
Here's the problematic query:
select count(*) from
(SELECT "pitch" AS "AAAA" FROM "AAAA"."autogen"."imu_messages"),
(SELECT "pitch" AS "BBBB" FROM "BBBB"."autogen"."imu_messages"),
(SELECT "pitch" AS "CCCC" FROM "CCCC"."autogen"."imu_messages"),
(SELECT "pitch" AS "DDDD" FROM "DDDD"."autogen"."imu_messages"),
(SELECT "pitch" AS "EEEE" FROM "EEEE"."autogen"."imu_messages"),
(SELECT "pitch" AS "FFFF" FROM "FFFF"."autogen"."imu_messages"),
(SELECT "pitch" AS "GGGG" FROM "GGGG"."autogen"."imu_messages"),
(SELECT "pitch" AS "HHHH" FROM "HHHH"."autogen"."imu_messages"),
WHERE time > now() - 60d GROUP BY time(1m) FILL(-1)
If you need any additional info let me know & I'll update the question with it.
Thanks.
