Beta 7 - Secondary hash index - performance issue

[zmeidav]

When querying for a row by a hash, the performance is quite jumpy - event after initial query compilation.
I think it depends on what shard it hits, but the performance can be around 40 ms to 500ms.
My cluster is two leaves of 16 cores each and 60GB each of ram.
using SSD with 15K iops.

What should actually be expected?

The table is small with 400M entries.

Few examples ( shard key is senor_id ) hash key is pid:

memsql> select * from process_relations limit 2 offset 100;
+----------------------------------+--------------------------+--------------------------+--------------------------+--------------------------+----------------------------+
| sensor_id                         | pid              | parent_pid       | internal_id             | event_id                 | generated_time             |
+----------------------------------+--------------------------+--------------------------+--------------------------+--------------------------+----------------------------+
| 0202bd29933f95946d25ab406371f5b2 | AdUvieStLcwAAA2IAAAAAA== | AdUt9BOL6UQAAAOMAAAAAA== | AdUvieStLcwAAA2IAAAAAA== | AAABa6pEY4/Egd6IAA48BA== | 2019-06-30 21:22:17.297000 |
| 0202bd29933f95946d25ab406371f5b2 | AdUvieTCm1UAAAT4AAAAAA== | AdUt9BOL6UQAAAOMAAAAAA== | AdUvieTCm1UAAAT4AAAAAA== | AAABa6pEZAzEgd6IAA48dA== | 2019-06-30 21:22:17.422000 |
+----------------------------------+--------------------------+--------------------------+--------------------------+--------------------------+----------------------------+
2 rows in set (0.63 sec)

memsql> select sensor_id, pid, parent_pid from process_relations where sensor_id='0202bd29933f95946d25ab406371f5b2' and pid='AdUt9BOL6UQAAAOMAAAAAA==' limit 1;
+----------------------------------+--------------------------+--------------------------+
| sensor_id                         | pid              | parent_pid       |
+----------------------------------+--------------------------+--------------------------+
| 0202bd29933f95946d25ab406371f5b2 | AdUt9BOL6UQAAAOMAAAAAA== | AdUt9BM9W9sAAALEAAAAAA== |
+----------------------------------+--------------------------+--------------------------+
1 row in set (0.77 sec)

memsql> select sensor_id, pid, parent_pid from process_relations where sensor_id='0202bd29933f95946d25ab406371f5b2' and pid='AdUt9BM9W9sAAALEAAAAAA==' limit 1;
+----------------------------------+--------------------------+--------------------------+
| sensor_id                         | pid              | parent_pid       |
+----------------------------------+--------------------------+--------------------------+
| 0202bd29933f95946d25ab406371f5b2 | AdUt9BM9W9sAAALEAAAAAA== | AdUt9BMljhgAAAJ0AAAAAA== |
+----------------------------------+--------------------------+--------------------------+
1 row in set (0.18 sec)

memsql> select sensor_id, pid, parent_pid from process_relations where sensor_id='0202bd29933f95946d25ab406371f5b2' and pid='AdUt9BMljhgAAAJ0AAAAAA==' limit 1;
+----------------------------------+--------------------------+--------------------+
| sensor_id                         | pid              | parent_pid |
+----------------------------------+--------------------------+--------------------+
| 0202bd29933f95946d25ab406371f5b2 | AdUt9BMljhgAAAJ0AAAAAA== |                    |
+----------------------------------+--------------------------+--------------------+
1 row in set (0.22 sec)

memsql> select sensor_id, pid, parent_pid from process_relations where sensor_id='0202bd29933f95946d25ab406371f5b2' and pid='AdUt9BMljhgAAAJ0AAAAAA==' limit 1;^C
memsql> select * from process_relations limit 2 offset 10000;
+----------------------------------+--------------------------+--------------------------+--------------------------+--------------------------+----------------------------+
| sensor_id                         | pid              | parent_pid       | internal_id             | event_id                 | generated_time             |
+----------------------------------+--------------------------+--------------------------+--------------------------+--------------------------+----------------------------+
| 006c3e15917ca1640caa9ab634661872 | AdUvkPzmhtwAAAWUAAAAAA== | AdUvfLERPeMAAAOMAAAAAA== | AdUvkPzmhtwAAAWUAAAAAA== | AAABa6py4k151APBAADG6w== | 2019-06-30 22:13:04.357000 |
| 006c3e15917ca1640caa9ab634661872 | AdUvkSceoHAAAAwMAAAAAA== | AdUvfLERPeMAAAOMAAAAAA== | AdUvkSceoHAAAAwMAAAAAA== | AAABa6pz9wN51APBAADH4w== | 2019-06-30 22:14:15.195000 |
+----------------------------------+--------------------------+--------------------------+--------------------------+--------------------------+----------------------------+
2 rows in set (0.05 sec)

memsql> select sensor_id, pid, parent_pid from process_relations where sensor_id='006c3e15917ca1640caa9ab634661872' and pid='AdUvfLERPeMAAAOMAAAAAA==' limit 1;
+----------------------------------+--------------------------+--------------------------+
| sensor_id                         | pid              | parent_pid       |
+----------------------------------+--------------------------+--------------------------+
| 006c3e15917ca1640caa9ab634661872 | AdUvfLERPeMAAAOMAAAAAA== | AdUvfLDOuCkAAAMIAAAAAA== |
+----------------------------------+--------------------------+--------------------------+
1 row in set (0.33 sec)

memsql> select sensor_id, pid, parent_pid from process_relations where sensor_id='006c3e15917ca1640caa9ab634661872' and pid='AdUvfLDOuCkAAAMIAAAAAA==' limit 1;
+----------------------------------+--------------------------+--------------------------+
| sensor_id                         | pid              | parent_pid       |
+----------------------------------+--------------------------+--------------------------+
| 006c3e15917ca1640caa9ab634661872 | AdUvfLDOuCkAAAMIAAAAAA== | AdUvfLCn4awAAAJsAAAAAA== |
+----------------------------------+--------------------------+--------------------------+
1 row in set (0.37 sec)

memsql> select sensor_id, pid, parent_pid from process_relations where sensor_id='006c3e15917ca1640caa9ab634661872' and pid='AdUvfLCn4awAAAJsAAAAAA==' limit 1;
+----------------------------------+--------------------------+--------------------+
| sensor_id                         | pid              | parent_pid |
+----------------------------------+--------------------------+--------------------+
| 006c3e15917ca1640caa9ab634661872 | AdUvfLCn4awAAAJsAAAAAA== |                    |
+----------------------------------+--------------------------+--------------------+
1 row in set (0.27 sec)

memsql> select * from process_relations limit 2 offset 1000;
+----------------------------------+--------------------------+--------------------------+--------------------------+--------------------------+----------------------------+
| sensor_id                         | pid              | parent_pid       | internal_id             | event_id                 | generated_time             |
+----------------------------------+--------------------------+--------------------------+--------------------------+--------------------------+----------------------------+
| 0826174f333cd820985b6cabef6a4f7c | AdUviZ4/A00AAB1IAAAAAA== | AdUve/FNgSwAAAMsAAAAAA== | AdUviZ4/A00AAB1IAAAAAA== | AAABa6pCleecD98UAACT6Q== | 2019-06-30 21:20:19.044000 |
| 0826174f333cd820985b6cabef6a4f7c | AdUviZN5CcwAAAyEAAAAAA== | AdUve/GNUUgAAAPAAAAAAA== | AdUviZN5CcwAAAyEAAAAAA== | AAABa6pCT1ecD98UAACSkg== | 2019-06-30 21:20:00.980000 |
+----------------------------------+--------------------------+--------------------------+--------------------------+--------------------------+----------------------------+
2 rows in set (0.02 sec)

memsql> select sensor_id, pid, parent_pid from process_relations where sensor_id='0826174f333cd820985b6cabef6a4f7c' and pid='AdUve/FNgSwAAAMsAAAAAA==' limit 1;
+----------------------------------+--------------------------+--------------------------+
| sensor_id                         | pid              | parent_pid       |
+----------------------------------+--------------------------+--------------------------+
| 0826174f333cd820985b6cabef6a4f7c | AdUve/FNgSwAAAMsAAAAAA== | AdUve/Ent1cAAAKUAAAAAA== |
+----------------------------------+--------------------------+--------------------------+
1 row in set (0.13 sec)

memsql> select sensor_id, pid, parent_pid from process_relations where sensor_id='0826174f333cd820985b6cabef6a4f7c' and pid='AdUve/Ent1cAAAKUAAAAAA==' limit 1;
+----------------------------------+--------------------------+--------------------+
| sensor_id                         | pid              | parent_pid |
+----------------------------------+--------------------------+--------------------+
| 0826174f333cd820985b6cabef6a4f7c | AdUve/Ent1cAAAKUAAAAAA== |                    |
+----------------------------------+--------------------------+--------------------+
1 row in set (0.09 sec)

[haoran]

Hello,

Thanks for your post.
(1) In the log you posted, you may have noticed that if you run the same query twice, the second run is significantly faster. This is expected. The first query run will put relevant disk data into the Linux page cache, so the second query run (which would seek for the identical data on disk) will be a page cache hit. It will be faster as it would not need to actually read from disk. Linux page cache also have some read-ahead heuristic, so it could also possibly be a reason that your later query is faster than your earlier query even if the searched key is not identical.
(2) There is a background merger thread which attempts to optimize the secondary index on-disk data structure in background. So queries that are executed immediately after your data is ingested will be slower than queries that are executed a while later (after merger has optimized the on-disk structure). You can verify the status of the data structure using the following command (it will be helpful if you could attach the output):

show columnar merge status on process_relations

[zmeidav]

Hey,

It still a lot of an overhead for SSD to take that much time, which should respond in a matter of milliseconds.
The table has 480M rows, takes 1.01GB of memory use and only 36GB on disk.

memsql> show columnar merge status on process_relations; ±---------------------------------------±------±-----±---------±----------+
| Merger | State | Plan | Progress | Partition |
±---------------------------------------±------±-----±---------±----------+
| (Current groups) | NULL | 15,1 | NULL | 16 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 16 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 16 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 16 |
| (Current groups) | NULL | 15,1 | NULL | 14 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 14 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 14 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 14 |
| (Current groups) | NULL | 13,1 | NULL | 1 |
| (Current Index Groups on event_id) | NULL | 13,1 | NULL | 1 |
| (Current Index Groups on pid) | NULL | 13,1 | NULL | 1 |
| (Current Index Groups on internal_id) | NULL | 13,1 | NULL | 1 |
| (Current groups) | NULL | 15,1 | NULL | 2 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 2 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 2 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 2 |
| (Current groups) | NULL | 15,1 | NULL | 3 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 3 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 3 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 3 |
| (Current groups) | NULL | 14,1 | NULL | 4 |
| (Current Index Groups on event_id) | NULL | 14,1 | NULL | 4 |
| (Current Index Groups on pid) | NULL | 14,1 | NULL | 4 |
| (Current Index Groups on internal_id) | NULL | 14,1 | NULL | 4 |
| (Current groups) | NULL | 14,1 | NULL | 5 |
| (Current Index Groups on event_id) | NULL | 14,1 | NULL | 5 |
| (Current Index Groups on pid) | NULL | 14,1 | NULL | 5 |
| (Current Index Groups on internal_id) | NULL | 14,1 | NULL | 5 |
| (Current groups) | NULL | 13,1 | NULL | 6 |
| (Current Index Groups on event_id) | NULL | 13,1 | NULL | 6 |
| (Current Index Groups on pid) | NULL | 13,1 | NULL | 6 |
| (Current Index Groups on internal_id) | NULL | 13,1 | NULL | 6 |
| (Current groups) | NULL | 15,1 | NULL | 7 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 7 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 7 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 7 |
| (Current groups) | NULL | 14,1 | NULL | 8 |
| (Current Index Groups on event_id) | NULL | 14,1 | NULL | 8 |
| (Current Index Groups on pid) | NULL | 14,1 | NULL | 8 |
| (Current Index Groups on internal_id) | NULL | 14,1 | NULL | 8 |
| (Current groups) | NULL | 14,1 | NULL | 9 |
| (Current Index Groups on event_id) | NULL | 14,1 | NULL | 9 |
| (Current Index Groups on pid) | NULL | 14,1 | NULL | 9 |
| (Current Index Groups on internal_id) | NULL | 14,1 | NULL | 9 |
| (Current groups) | NULL | 14,1 | NULL | 12 |
| (Current Index Groups on event_id) | NULL | 14,1 | NULL | 12 |
| (Current Index Groups on pid) | NULL | 14,1 | NULL | 12 |
| (Current Index Groups on internal_id) | NULL | 14,1 | NULL | 12 |
| (Current groups) | NULL | 16,1 | NULL | 11 |
| (Current Index Groups on event_id) | NULL | 16,1 | NULL | 11 |
| (Current Index Groups on pid) | NULL | 16,1 | NULL | 11 |
| (Current Index Groups on internal_id) | NULL | 16,1 | NULL | 11 |
| (Current groups) | NULL | 17,2 | NULL | 31 |
| (Current Index Groups on event_id) | NULL | 16,3 | NULL | 31 |
| (Current Index Groups on pid) | NULL | 16,3 | NULL | 31 |
| (Current Index Groups on internal_id) | NULL | 16,3 | NULL | 31 |
| (Current groups) | NULL | 14,1 | NULL | 13 |
| (Current Index Groups on event_id) | NULL | 14,1 | NULL | 13 |
| (Current Index Groups on pid) | NULL | 14,1 | NULL | 13 |
| (Current Index Groups on internal_id) | NULL | 14,1 | NULL | 13 |
| (Current groups) | NULL | 15,1 | NULL | 10 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 10 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 10 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 10 |
| (Current groups) | NULL | 15,1 | NULL | 15 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 15 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 15 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 15 |
| (Current groups) | NULL | 15,1 | NULL | 0 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 0 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 0 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 0 |
| (Current groups) | NULL | 14,1 | NULL | 17 |
| (Current Index Groups on event_id) | NULL | 14,1 | NULL | 17 |
| (Current Index Groups on pid) | NULL | 14,1 | NULL | 17 |
| (Current Index Groups on internal_id) | NULL | 14,1 | NULL | 17 |
| (Current groups) | NULL | 15,1 | NULL | 18 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 18 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 18 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 18 |
| (Current groups) | NULL | 14,1 | NULL | 19 |
| (Current Index Groups on event_id) | NULL | 14,1 | NULL | 19 |
| (Current Index Groups on pid) | NULL | 14,1 | NULL | 19 |
| (Current Index Groups on internal_id) | NULL | 14,1 | NULL | 19 |
| (Current groups) | NULL | 15,1 | NULL | 20 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 20 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 20 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 20 |
| (Current groups) | NULL | 15,1 | NULL | 21 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 21 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 21 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 21 |
| (Current groups) | NULL | 14,1 | NULL | 22 |
| (Current Index Groups on event_id) | NULL | 14,1 | NULL | 22 |
| (Current Index Groups on pid) | NULL | 14,1 | NULL | 22 |
| (Current Index Groups on internal_id) | NULL | 14,1 | NULL | 22 |
| (Current groups) | NULL | 16,1 | NULL | 23 |
| (Current Index Groups on event_id) | NULL | 16,1 | NULL | 23 |
| (Current Index Groups on pid) | NULL | 16,1 | NULL | 23 |
| (Current Index Groups on internal_id) | NULL | 16,1 | NULL | 23 |
| (Current groups) | NULL | 15,1 | NULL | 24 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 24 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 24 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 24 |
| (Current groups) | NULL | 15,1 | NULL | 25 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 25 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 25 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 25 |
| (Current groups) | NULL | 15,1 | NULL | 26 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 26 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 26 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 26 |
| (Current groups) | NULL | 14,1 | NULL | 27 |
| (Current Index Groups on event_id) | NULL | 14,1 | NULL | 27 |
| (Current Index Groups on pid) | NULL | 14,1 | NULL | 27 |
| (Current Index Groups on internal_id) | NULL | 14,1 | NULL | 27 |
| (Current groups) | NULL | 15,1 | NULL | 28 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 28 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 28 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 28 |
| (Current groups) | NULL | 14,1 | NULL | 29 |
| (Current Index Groups on event_id) | NULL | 14,1 | NULL | 29 |
| (Current Index Groups on pid) | NULL | 14,1 | NULL | 29 |
| (Current Index Groups on internal_id) | NULL | 14,1 | NULL | 29 |
| (Current groups) | NULL | 15,1 | NULL | 30 |
| (Current Index Groups on event_id) | NULL | 15,1 | NULL | 30 |
| (Current Index Groups on pid) | NULL | 15,1 | NULL | 30 |
| (Current Index Groups on internal_id) | NULL | 15,1 | NULL | 30 |
±---------------------------------------±------±-----±---------±----------+
128 rows in set (0.04 sec)

[haoran]

Thanks for your reply. After seeing the merge status output, I agree with you normally it should not be that slow. Can you attach the output of

select DATABASE_NAME, PARTITION, COLUMN_NAME, SEGMENT_ID, COLUMN_ID, ENCODING, UNCOMPRESSED_SIZE, COMPRESSED_SIZE from information_schema.columnar_segments where table_name = 'process_relations';

so we can investigate further. If it is exceeding the max length of one forum post it’s fine to redact some.

[zmeidav]

±--------------±----------±-------------------±-----------±----------±------------------------±------------------±----------------+
| DATABASE_NAME | PARTITION | COLUMN_NAME | SEGMENT_ID | COLUMN_ID | ENCODING | UNCOMPRESSED_SIZE | COMPRESSED_SIZE |
±--------------±----------±-------------------±-----------±----------±------------------------±------------------±----------------+
| ziv_test_db | 10 | sensor_id | 58180 | 551199 | SeekableStringRunLength | 36864000 | 13882 |
| ziv_test_db | 10 | pid | 58180 | 551209 | SeekableStringRunLength | 27976696 | 27887402 |
| ziv_test_db | 10 | parent_pid | 58180 | 551213 | StringDictionary | 28644904 | 5014359 |
| ziv_test_db | 10 | internal_id | 58180 | 551215 | StringDictionary | 27976696 | 20580432 |
| ziv_test_db | 10 | event_id | 58180 | 551217 | SeekableStringRunLength | 28672000 | 25592489 |
| ziv_test_db | 10 | generated_time | 58180 | 551218 | SeekableIntegerDelta | 8192000 | 4894894 |
| ziv_test_db | 10 | sensor_id | 58182 | 551221 | SeekableStringRunLength | 36864000 | 12794 |
| ziv_test_db | 10 | pid | 58182 | 551223 | SeekableStringRunLength | 27861136 | 27768986 |
| ziv_test_db | 10 | parent_pid | 58182 | 551224 | StringDictionary | 28661392 | 4115987 |
| ziv_test_db | 10 | internal_id | 58182 | 551226 | SeekableStringRunLength | 27861136 | 24597543 |
| ziv_test_db | 10 | event_id | 58182 | 551228 | SeekableStringRunLength | 28672000 | 25598414 |
| ziv_test_db | 10 | generated_time | 58182 | 551229 | SeekableIntegerDelta | 8192000 | 4837294 |
| ziv_test_db | 10 | sensor_id | 58183 | 551232 | SeekableStringRunLength | 36864000 | 13202 |
| ziv_test_db | 10 | pid | 58183 | 551234 | SeekableStringRunLength | 28086040 | 28004301 |
| ziv_test_db | 10 | parent_pid | 58183 | 551235 | StringDictionary | 28654264 | 4085774 |
| ziv_test_db | 10 | internal_id | 58183 | 551248 | StringDictionary | 28086040 | 22228192 |
| ziv_test_db | 10 | event_id | 58183 | 551250 | SeekableStringRunLength | 28672000 | 25598489 |
| ziv_test_db | 10 | generated_time | 58183 | 551251 | SeekableIntegerDelta | 8192000 | 4853550 |
| ziv_test_db | 10 | sensor_id | 58185 | 551254 | SeekableStringRunLength | 36864000 | 13202 |
| ziv_test_db | 10 | pid | 58185 | 551256 | SeekableStringRunLength | 27858136 | 27735746 |
| ziv_test_db | 10 | parent_pid | 58185 | 551257 | StringDictionary | 28651288 | 4665708 |
| ziv_test_db | 10 | internal_id | 58185 | 551259 | StringDictionary | 27858136 | 20738085 |
| ziv_test_db | 10 | event_id | 58185 | 551261 | SeekableStringRunLength | 28672000 | 25596014 |
| ziv_test_db | 10 | generated_time | 58185 | 551262 | SeekableIntegerDelta | 8192000 | 4841134 |
| ziv_test_db | 10 | sensor_id | 58186 | 551265 | SeekableStringRunLength | 36864000 | 12896 |
| ziv_test_db | 10 | pid | 58186 | 551278 | SeekableStringRunLength | 28240216 | 28201673 |
| ziv_test_db | 10 | parent_pid | 58186 | 551279 | StringDictionary | 28656856 | 5935727 |
| ziv_test_db | 10 | internal_id | 58186 | 551281 | StringDictionary | 28240216 | 19054149 |
| ziv_test_db | 10 | event_id | 58186 | 551283 | SeekableStringRunLength | 28672000 | 25595314 |
| ziv_test_db | 10 | generated_time | 58186 | 551284 | SeekableIntegerDelta | 8192000 | 4859182 |
| ziv_test_db | 10 | sensor_id | 58188 | 551287 | SeekableStringRunLength | 36864000 | 12318 |
| ziv_test_db | 10 | pid | 58188 | 551289 | SeekableStringRunLength | 27897304 | 27787717 |
| ziv_test_db | 10 | parent_pid | 58188 | 551290 | StringDictionary | 28662112 | 3566652 |
| ziv_test_db | 10 | internal_id | 58188 | 551292 | StringDictionary | 27897304 | 13254394 |
| ziv_test_db | 10 | event_id | 58188 | 551294 | SeekableStringRunLength | 28672000 | 25595989 |
| ziv_test_db | 10 | generated_time | 58188 | 551295 | SeekableIntegerDelta | 8192000 | 4779822 |
| ziv_test_db | 10 | sensor_id | 58189 | 551298 | SeekableStringRunLength | 36864000 | 12284 |
| ziv_test_db | 10 | pid | 58189 | 551300 | SeekableStringRunLength | 27865336 | 27770044 |
| ziv_test_db | 10 | parent_pid | 58189 | 551309 | StringDictionary | 28663672 | 5053860 |
| ziv_test_db | 10 | internal_id | 58189 | 551314 | StringDictionary | 27865336 | 18928653 |
| ziv_test_db | 10 | event_id | 58189 | 551316 | SeekableStringRunLength | 28672000 | 25598839 |
| ziv_test_db | 10 | generated_time | 58189 | 551317 | SeekableIntegerDelta | 8192000 | 4831918 |
| ziv_test_db | 10 | sensor_id | 58191 | 551320 | SeekableStringRunLength | 36864000 | 12522 |
| ziv_test_db | 10 | pid | 58191 | 551322 | SeekableStringRunLength | 27680752 | 27535264 |
| ziv_test_db | 10 | parent_pid | 58191 | 551323 | StringDictionary | 28662016 | 5239350 |
| ziv_test_db | 10 | internal_id | 58191 | 551325 | StringDictionary | 27680752 | 17848302 |
| ziv_test_db | 10 | event_id | 58191 | 551327 | SeekableStringRunLength | 28672000 | 25597164 |
| ziv_test_db | 10 | generated_time | 58191 | 551328 | SeekableIntegerDelta | 8192000 | 4793518 |
| ziv_test_db | 10 | sensor_id | 58192 | 551331 | SeekableStringRunLength | 36864000 | 13644 |
| ziv_test_db | 10 | pid | 58192 | 551333 | SeekableStringRunLength | 27947296 | 27857203 |
| ziv_test_db | 10 | parent_pid | 58192 | 551334 | StringDictionary | 28646512 | 4829193 |
| ziv_test_db | 10 | internal_id | 58192 | 551336 | StringDictionary | 27947296 | 20474160 |
| ziv_test_db | 10 | event_id | 58192 | 551338 | SeekableStringRunLength | 28672000 | 25596939 |
| ziv_test_db | 10 | generated_time | 58192 | 551339 | SeekableIntegerDelta | 8192000 | 4850350 |
| ziv_test_db | 10 | sensor_id | 58193 | 551342 | SeekableStringRunLength | 36864000 | 14222 |
| ziv_test_db | 10 | pid | 58193 | 551344 | SeekableStringRunLength | 27416320 | 27210271 |
| ziv_test_db | 10 | parent_pid | 58193 | 551345 | StringDictionary | 28644568 | 5491260 |
| ziv_test_db | 10 | internal_id | 58193 | 551347 | StringDictionary | 27416320 | 19913019 |
| ziv_test_db | 10 | event_id | 58193 | 551360 | SeekableStringRunLength | 28672000 | 25588889 |
| ziv_test_db | 10 | generated_time | 58193 | 551361 | SeekableIntegerDelta | 8192000 | 4871342 |
| ziv_test_db | 10 | sensor_id | 58195 | 551364 | SeekableStringRunLength | 36864000 | 12386 |
| ziv_test_db | 10 | pid | 58195 | 551366 | SeekableStringRunLength | 27649144 | 27517580 |
| ziv_test_db | 10 | parent_pid | 58195 | 551367 | StringDictionary | 28662520 | 4389578 |
| ziv_test_db | 10 | internal_id | 58195 | 551369 | StringDictionary | 27649144 | 15706345 |
| ziv_test_db | 10 | event_id | 58195 | 551371 | SeekableStringRunLength | 28672000 | 25598264 |
| ziv_test_db | 10 | generated_time | 58195 | 551372 | SeekableIntegerDelta | 8192000 | 4799022 |
| ziv_test_db | 10 | sensor_id | 58196 | 551375 | SeekableStringRunLength | 36864000 | 12216 |
| ziv_test_db | 10 | pid | 58196 | 551377 | SeekableStringRunLength | 27717232 | 27576396 |
| ziv_test_db | 10 | parent_pid | 58196 | 551378 | StringDictionary | 28663168 | 5958623 |
| ziv_test_db | 10 | internal_id | 58196 | 551380 | StringDictionary | 27717232 | 16461940 |
| ziv_test_db | 10 | event_id | 58196 | 551382 | SeekableStringRunLength | 28672000 | 25601064 |
| ziv_test_db | 10 | generated_time | 58196 | 551383 | SeekableIntegerDelta | 8192000 | 4755118 |
| ziv_test_db | 10 | sensor_id | 58197 | 551386 | SeekableStringRunLength | 36864000 | 13100 |
| ziv_test_db | 10 | pid | 58197 | 551388 | SeekableStringRunLength | 28169704 | 28108099 |
| ziv_test_db | 10 | parent_pid | 58197 | 551389 | StringDictionary | 28649080 | 4257197 |
| ziv_test_db | 10 | internal_id | 58197 | 551402 | StringDictionary | 28169704 | 20409144 |
| ziv_test_db | 10 | event_id | 58197 | 551404 | SeekableStringRunLength | 28672000 | 25598514 |
| ziv_test_db | 10 | generated_time | 58197 | 551405 | SeekableIntegerDelta | 8192000 | 4853550 |
| ziv_test_db | 10 | sensor_id | 58199 | 551408 | SeekableStringRunLength | 36864000 | 12930 |
| ziv_test_db | 10 | pid | 58199 | 551410 | SeekableStringRunLength | 27600712 | 27450251 |
| ziv_test_db | 10 | parent_pid | 58199 | 551411 | StringDictionary | 28656880 | 5878163 |
| ziv_test_db | 10 | internal_id | 58199 | 551413 | StringDictionary | 27600712 | 18940020 |
| ziv_test_db | 10 | event_id | 58199 | 551415 | SeekableStringRunLength | 28672000 | 25597314 |
| ziv_test_db | 10 | generated_time | 58199 | 551416 | SeekableIntegerDelta | 8192000 | 4858414 |
| ziv_test_db | 10 | sensor_id | 58200 | 551419 | SeekableStringRunLength | 27043956 | 9348 |
| ziv_test_db | 10 | pid | 58200 | 551421 | SeekableStringRunLength | 20588172 | 20518542 |
| ziv_test_db | 10 | parent_pid | 58200 | 551422 | StringDictionary | 21025644 | 3097148 |
| ziv_test_db | 10 | internal_id | 58200 | 551424 | SeekableStringRunLength | 20588172 | 18053077 |
| ziv_test_db | 10 | event_id | 58200 | 551426 | SeekableStringRunLength | 21034188 | 18777763 |
| ziv_test_db | 10 | generated_time | 58200 | 551427 | SeekableIntegerDelta | 6009768 | 3563492 |
| ziv_test_db | 10 | sensor_id | 69606 | 676103 | SeekableStringRunLength | 36864000 | 25612 |
| ziv_test_db | 10 | pid | 69606 | 676105 | SeekableStringRunLength | 28672000 | 25588914 |
| ziv_test_db | 10 | parent_pid | 69606 | 676106 | StringDictionary | 28616440 | 6140441 |
| ziv_test_db | 10 | internal_id | 69606 | 676108 | StringDictionary | 28672000 | 17918904 |
| ziv_test_db | 10 | event_id | 69606 | 676110 | SeekableStringRunLength | 28672000 | 25588914 |
| ziv_test_db | 10 | generated_time | 69606 | 676111 | Integer | 8192000 | 4992012 |
| ziv_test_db | 10 | sensor_id | 69607 | 676114 | SeekableStringRunLength | 6633828 | 4725 |
| ziv_test_db | 10 | pid | 69607 | 676116 | SeekableStringRunLength | 5159644 | 4605734 |
| ziv_test_db | 10 | parent_pid | 69607 | 676117 | StringDictionary | 5149540 | 1053841 |
| ziv_test_db | 10 | internal_id | 69607 | 676119 | StringDictionary | 5159644 | 3268332 |
| ziv_test_db | 10 | event_id | 69607 | 676121 | SeekableStringRunLength | 5159644 | 4605734 |
| ziv_test_db | 10 | generated_time | 69607 | 676122 | Integer | 1474184 | 875532 |
| ziv_test_db | 28 | sensor_id | 58633 | 548145 | SeekableStringRunLength | 36864000 | 13270 |
| ziv_test_db | 28 | pid | 58633 | 548147 | SeekableStringRunLength | 28031248 | 27937715 |
| ziv_test_db | 28 | parent_pid | 58633 | 548148 | StringDictionary | 28650592 | 4296725 |
| ziv_test_db | 28 | internal_id | 58633 | 548150 | StringDictionary | 28031248 | 22199380 |
| ziv_test_db | 28 | event_id | 58633 | 548152 | SeekableStringRunLength | 28672000 | 25594989 |
| ziv_test_db | 28 | generated_time | 58633 | 548153 | SeekableIntegerDelta | 8192000 | 4855854 |
| ziv_test_db | 28 | sensor_id | 58634 | 548156 | SeekableStringRunLength | 36864000 | 12760 |
| ziv_test_db | 28 | pid | 58634 | 548158 | SeekableStringRunLength | 27509536 | 27329894 |
| ziv_test_db | 28 | parent_pid | 58634 | 548159 | StringDictionary | 28659880 | 6742730 |
| ziv_test_db | 28 | internal_id | 58634 | 548172 | StringDictionary | 27509536 | 17672640 |
| ziv_test_db | 28 | event_id | 58634 | 548174 | SeekableStringRunLength | 28672000 | 25597214 |
| ziv_test_db | 28 | generated_time | 58634 | 548175 | SeekableIntegerDelta | 8192000 | 4781614 |
| ziv_test_db | 28 | sensor_id | 58636 | 548178 | SeekableStringRunLength | 36864000 | 12964 |
| ziv_test_db | 28 | pid | 58636 | 548180 | SeekableStringRunLength | 27787192 | 27674846 |
| ziv_test_db | 28 | parent_pid | 58636 | 548181 | StringDictionary | 28649464 | 5679801 |
| ziv_test_db | 28 | internal_id | 58636 | 548183 | StringDictionary | 27787192 | 19676661 |
| ziv_test_db | 28 | event_id | 58636 | 548185 | SeekableStringRunLength | 28672000 | 25596064 |
| ziv_test_db | 28 | generated_time | 58636 | 548186 | SeekableIntegerDelta | 8192000 | 4842926 |
| ziv_test_db | 28 | sensor_id | 58637 | 548189 | SeekableStringRunLength | 36864000 | 12658 |
| ziv_test_db | 28 | pid | 58637 | 548191 | SeekableStringRunLength | 27502000 | 27320727 |
| ziv_test_db | 28 | parent_pid | 58637 | 548192 | StringDictionary | 28661896 | 5064687 |
| ziv_test_db | 28 | internal_id | 58637 | 548194 | StringDictionary | 27502000 | 20096403 |
| ziv_test_db | 28 | event_id | 58637 | 548196 | SeekableStringRunLength | 28672000 | 25588539 |
| ziv_test_db | 28 | generated_time | 58637 | 548197 | SeekableIntegerDelta | 8192000 | 4783022 |
| ziv_test_db | 28 | sensor_id | 58638 | 548200 | SeekableStringRunLength | 36864000 | 13032 |
| ziv_test_db | 28 | pid | 58638 | 548213 | SeekableStringRunLength | 27958648 | 27853886 |
| ziv_test_db | 28 | parent_pid | 58638 | 548214 | StringDictionary | 28655272 | 6517820 |
| ziv_test_db | 28 | internal_id | 58638 | 548216 | StringDictionary | 27958648 | 19026933 |
| ziv_test_db | 28 | event_id | 58638 | 548218 | SeekableStringRunLength | 28672000 | 25597639 |
| ziv_test_db | 28 | generated_time | 58638 | 548219 | SeekableIntegerDelta | 8192000 | 4840238 |
| ziv_test_db | 28 | sensor_id | 58640 | 548222 | SeekableStringRunLength | 36864000 | 13440 |
| ziv_test_db | 28 | pid | 58640 | 548224 | SeekableStringRunLength | 28165000 | 28119265 |
| ziv_test_db | 28 | parent_pid | 58640 | 548225 | StringDictionary | 28657072 | 5166450 |
| ziv_test_db | 28 | internal_id | 58640 | 548227 | StringDictionary | 28165000 | 20962239 |
| ziv_test_db | 28 | event_id | 58640 | 548229 | SeekableStringRunLength | 28672000 | 25598214 |
| ziv_test_db | 28 | generated_time | 58640 | 548230 | SeekableIntegerDelta | 8192000 | 4885038 |
| ziv_test_db | 28 | sensor_id | 58641 | 548233 | SeekableStringRunLength | 36864000 | 12794 |
| ziv_test_db | 28 | pid | 58641 | 548235 | SeekableStringRunLength | 27558184 | 27383912 |
| ziv_test_db | 28 | parent_pid | 58641 | 548244 | StringDictionary | 28654456 | 4584222 |
| ziv_test_db | 28 | internal_id | 58641 | 548249 | StringDictionary | 27558184 | 20680251 |
| ziv_test_db | 28 | event_id | 58641 | 548251 | SeekableStringRunLength | 28672000 | 25589189 |
| ziv_test_db | 28 | generated_time | 58641 | 548252 | SeekableIntegerDelta | 8192000 | 4833582 |
| ziv_test_db | 28 | sensor_id | 58643 | 548255 | SeekableStringRunLength | 36864000 | 13236 |
| ziv_test_db | 28 | pid | 58643 | 548257 | SeekableStringRunLength | 28020976 | 27939085 |
| ziv_test_db | 28 | parent_pid | 58643 | 548258 | StringDictionary | 28654936 | 5516262 |
| ziv_test_db | 28 | internal_id | 58643 | 548260 | StringDictionary | 28020976 | 19896414 |
| ziv_test_db | 28 | event_id | 58643 | 548262 | SeekableStringRunLength | 28672000 | 25595439 |
| ziv_test_db | 28 | generated_time | 58643 | 548263 | SeekableIntegerDelta | 8192000 | 4884910 |
| ziv_test_db | 28 | sensor_id | 58644 | 548266 | SeekableStringRunLength | 36864000 | 12352 |
| ziv_test_db | 28 | pid | 58644 | 548268 | SeekableStringRunLength | 27923320 | 27805940 |
| ziv_test_db | 28 | parent_pid | 58644 | 548269 | StringDictionary | 28663120 | 6776129 |
| ziv_test_db | 28 | internal_id | 58644 | 548271 | StringDictionary | 27923320 | 17745135 |
| ziv_test_db | 28 | event_id | 58644 | 548273 | SeekableStringRunLength | 28672000 | 25587664 |
| ziv_test_db | 28 | generated_time | 58644 | 548274 | SeekableIntegerDelta | 8192000 | 4829486 |
| ziv_test_db | 28 | sensor_id | 58645 | 548277 | SeekableStringRunLength | 36864000 | 13372 |
| ziv_test_db | 28 | pid | 58645 | 548279 | SeekableStringRunLength | 27895456 | 27786959 |
| ziv_test_db | 28 | parent_pid | 58645 | 548280 | StringDictionary | 28651672 | 7165307 |
| ziv_test_db | 28 | internal_id | 58645 | 548282 | StringDictionary | 27895456 | 17221038 |
| ziv_test_db | 28 | event_id | 58645 | 548284 | SeekableStringRunLength | 28672000 | 25598114 |
| ziv_test_db | 28 | generated_time | 58645 | 548285 | SeekableIntegerDelta | 8192000 | 4824750 |
| ziv_test_db | 28 | sensor_id | 58646 | 548288 | SeekableStringRunLength | 36864000 | 12930 |
| ziv_test_db | 28 | pid | 58646 | 548290 | SeekableStringRunLength | 27471328 | 27298515 |
| ziv_test_db | 28 | parent_pid | 58646 | 548291 | StringDictionary | 28653448 | 5278041 |
| ziv_test_db | 28 | internal_id | 58646 | 548304 | StringDictionary | 27471328 | 20669316 |
| ziv_test_db | 28 | event_id | 58646 | 548306 | SeekableStringRunLength | 28672000 | 25596814 |
| ziv_test_db | 28 | generated_time | 58646 | 548307 | SeekableIntegerDelta | 8192000 | 4831406 |
| ziv_test_db | 28 | sensor_id | 58648 | 548310 | SeekableStringRunLength | 36864000 | 13032 |
| ziv_test_db | 28 | pid | 58648 | 548312 | SeekableStringRunLength | 27213808 | 26997532 |
| ziv_test_db | 28 | parent_pid | 58648 | 548313 | StringDictionary | 28653760 | 5336604 |

3408 rows in set (20.21 sec)

[haoran]

Can you try to run

select sensor_id, pid from process_relations where sensor_id=‘0202bd29933f95946d25ab406371f5b2’ and pid=‘AdUt9BOL6UQAAAOMAAAAAA==’ limit 1;

for a few times and see if it is significantly faster? (the query differs from yours that it did not select parent_pid column)

If you are interested in more technical details, here is my current guess: secondary hash index gives you what rows passed the filter quickly; however, to actually complete the query, we still need to extract the data of all requested columns from the compressed columnar blob files. We use subsegment access to quickly support extracting one single row from a segment of 1M rows. From your log, it turns out that your “parent_pid” column are mostly compressed using StringDictionary encoding and it has a rather large dictionary (i.e. distinct values in this segment). Subsegment access on StringDictionary encoding requires reading the whole dictionary, which is a few megabytes per segment in your specific case. This is likely where most of the time is spent.

I can provide more suggestions on how to prevent this issue (i.e. allowing you to select parent_pid column without regressing the perf) if we confirmed that this is indeed the root cause.

[zmeidav]

While no data ingestion is going on: ( in comparison the other query took 0.240 seconds twice as much )
memsql> select sensor_id, pid from process_relations where sensor_id=‘003f38ef75229a7c97174b2b0b814926’ and pid=‘AdUtIJhBVwAAAAJgAAAAAA==’ limit 1; — first time the query was run
±---------------------------------±-------------------------+
| sensor_id | pid |
±---------------------------------±-------------------------+
| 003f38ef75229a7c97174b2b0b814926 | AdUtIJhBVwAAAAJgAAAAAA== |
±---------------------------------±-------------------------+
1 row in set (0.64 sec)

memsql> select sensor_id, pid from process_relations where sensor_id=‘003f38ef75229a7c97174b2b0b814926’ and pid=‘AdUtIJjjNIAAAAMUAAAAAA==’ limit 1;
±---------------------------------±-------------------------+
| sensor_id | pid |
±---------------------------------±-------------------------+
| 003f38ef75229a7c97174b2b0b814926 | AdUtIJjjNIAAAAMUAAAAAA== |
±---------------------------------±-------------------------+
1 row in set (0.14 sec)

I checked now but there is a bit of high ingestion rate into the DB (not this table though) - so it simulates the same conditions as the original post.
memsql> select * from process_relations limit 2 offset 100000;
±---------------------------------±-------------------------±-------------------------±-------------------------±-------------------------±---------------------------+
| sensor_id | pid | parent_pid | internal_id | event_id | generated_time |
±---------------------------------±-------------------------±-------------------------±-------------------------±-------------------------±---------------------------+
| 90ee3fd1094efebc23a0efa394503031 | AdUw6HaP6sYAACNAAAAAAA== | AdUve9fSAtwAAAOQAAAAAA== | AdUw6HaP6sYAACNAAAAAAA== | AAABa7M94h5k3lyLAAxncw== | 2019-07-02 15:11:45.849000 |
| 9174dbb97c1ab47be93560a966b332ca | AdUw6F7XACgAABBoAAAAAA== | AdUwR1QLfbIAAALwAAAAAA== | AdUw6F7XACgAABBoAAAAAA== | AAABa7M9RrUgPv5oAAY4Lg== | 2019-07-02 15:11:06.051000 |
±---------------------------------±-------------------------±-------------------------±-------------------------±-------------------------±---------------------------+
2 rows in set (0.16 sec)

memsql> select sensor_id, pid from process_relations where sensor_id=‘90ee3fd1094efebc23a0efa394503031’ and pid=‘AdUve9fSAtwAAAOQAAAAAA==’ limit 1;
±---------------------------------±-------------------------+
| sensor_id | pid |
±---------------------------------±-------------------------+
| 90ee3fd1094efebc23a0efa394503031 | AdUve9fSAtwAAAOQAAAAAA== |
±---------------------------------±-------------------------+
1 row in set (0.73 sec)

memsql> select sensor_id, pid from process_relations where sensor_id=‘9174dbb97c1ab47be93560a966b332ca’ and pid=‘AdUwR1QLfbIAAALwAAAAAA==’ limit 1;
±---------------------------------±-------------------------+
| sensor_id | pid |
±---------------------------------±-------------------------+
| 9174dbb97c1ab47be93560a966b332ca | AdUwR1QLfbIAAALwAAAAAA== |
±---------------------------------±-------------------------+
1 row in set (0.26 sec)

[haoran]

I would suggest the following diagnostic steps:
(1) Attach here the output of

explain select sensor_id, pid from process_relations where sensor_id=‘9174dbb97c1ab47be93560a966b332ca’ and pid=‘AdUwR1QLfbIAAALwAAAAAA==’ limit 1

This allows us to confirm that the execution plan is using secondary hash index seek (it should, but just to be 100% sure).
(2) Run

select count(*) from process_relations where pid=‘AdUve9fSAtwAAAOQAAAAAA==’

This returns the number of rows that passed the secondary index filter. The query has to scan all the rows in order to give you the result. So if there are a lot of rows, this is likely the root cause.
(3) Run

select count(*) from process_relations where sensor_id=‘90ee3fd1094efebc23a0efa394503031’ and pid=‘AdUve9fSAtwAAAOQAAAAAA==’

for similar reason as (3).

I would also suggest isolating the problem. Try to run the query while the server does not have other workloads (ingesting etc) running to see if there are still large fluctuations. If no, then we know that it is not a issue with secondary hash index itself.

[zmeidav]

(1)
memsql> explain select sensor_id, pid from process_relations where sensor_id=‘9174dbb97c1ab47be93560a966b332ca’ and pid=‘AdUwR1QLfbIAAALwAAAAAA==’ limit 1
-> ;
±----------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
±----------------------------------------------------------------------------------------------------------------------------------------------+
| Gather partitions:single alias:remote_0 |
| Project [process_relations.sensor_id, process_relations.pid] |
| Top limit:1 |
| Filter [process_relations.sensor_id = ‘9174dbb97c1ab47be93560a966b332ca’ AND process_relations.pid = ‘AdUwR1QLfbIAAALwAAAAAA==’] |
| ColumnStoreScan ziv_test_db.process_relations, KEY sensor_id (sensor_id, pid, internal_id, generated_time) USING CLUSTERED COLUMNSTORE |
±----------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.08 sec)

(2)

memsql> select count() from process_relations where pid=‘AdUve9fSAtwAAAOQAAAAAA==’;
±---------+
| count() |
±---------+
| 1 |
±---------+
1 row in set (17.59 sec)

(3)
memsql> select count() from process_relations where sensor_id=‘90ee3fd1094efebc23a0efa394503031’ and pid=‘AdUve9fSAtwAAAOQAAAAAA==’;
±---------+
| count() |
±---------+
| 1 |
±---------+
1 row in set (0.31 sec)

Idle server will follow a bit later.

[zmeidav]

Hey,

After checking the explain plan and seeing that it doesnt use the index, i created another table, which is exactly the same one, with HASH key indexes at all:
memsql> select sensor_id, pid from process_relations_no_hashes where sensor_id=‘9174dbb97c1ab47be93560a966b332ca’ and pid=‘AdUwR1QLfbIAAALwAAAAAA==’^Cimit 1;
memsql> select sensor_id, pid, parent_pid from process_relations_no_hashes where sensor_id=‘01e70fd8a4ed209ad7b5c3255dd1aae1’ and pid=‘AdUusrz8hJIAAAMMAAAAAA==’ limit 1;
±---------------------------------±-------------------------±-------------------------+
| sensor_id | pid | parent_pid |
±---------------------------------±-------------------------±-------------------------+
| 01e70fd8a4ed209ad7b5c3255dd1aae1 | AdUusrz8hJIAAAMMAAAAAA== | AdUusrzUK4gAAAJwAAAAAA== |
±---------------------------------±-------------------------±-------------------------+
1 row in set (0.15 sec)

memsql> select sensor_id, pid, parent_pid from process_relations_no_hashes where sensor_id=‘01e70fd8a4ed209ad7b5c3255dd1aae1’ and pid=‘AdUusrzUK4gAAAJwAAAAAA==’ limit 1;
±---------------------------------±-------------------------±-------------------+
| sensor_id | pid | parent_pid |
±---------------------------------±-------------------------±-------------------+
| 01e70fd8a4ed209ad7b5c3255dd1aae1 | AdUusrzUK4gAAAJwAAAAAA== | |
±---------------------------------±-------------------------±-------------------+
1 row in set (0.01 sec)

Looks like it got the same performance.

Could it be that the secondary hash index is not used at all??

[zhou]

| ColumnStoreScan ziv_test_db.process_relations, KEY sensor_id (sensor_id, pid, internal_id, generated_time) USING CLUSTERED COLUMNSTORE |

According to the explain, it seems sensor_id, pid are already part of sort key.
So even without the secondary index we can eliminate most of the segments and scan only 1 or 2.
In this case the most of the time would be spent on decompress and read the value, which would be the same with and without the hash index.

If you try select sensor_id, pid, parent_pid from process_relations_no_hashes where pid=‘AdUusrzUK4gAAAJwAAAAAA==’ limit 1;
you should see noticeable difference in performance.

Btw, could you run
profile select sensor_id, pid from process_relations where sensor_id=‘9174dbb97c1ab47be93560a966b332ca’ and pid=‘AdUwR1QLfbIAAALwAAAAAA==’ limit 1;
and
"show profile json" so we can understand the effectiveness of secondary index better?

Thanks a lot.

[zmeidav]

memsql> profile select sensor_id, pid from process_relations where pid=‘AdUwR1QLfbIAAALwAAAAAA==’ limit 1;
±---------------------------------±-------------------------+
| sensor_id | pid |
±---------------------------------±-------------------------+
| 9174dbb97c1ab47be93560a966b332ca | AdUwR1QLfbIAAALwAAAAAA== |
±---------------------------------±-------------------------+
1 row in set (8.33 sec)

memsql> show profile json;
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PROFILE |
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
“profile”:[
{
“executor”:“Top”,
“keyId”:76,
“limit”:"?",
“actual_row_count”:{ “value”:1 },
“actual_total_time”:{ “value”:0 },
“inputs”:[
{
“executor”:“Gather”,
“keyId”:4294967448,
“partitions”:“all”,
“query”:“SELECT process_relations.sensor_id AS sensor_id, process_relations.pid AS pid FROM ziv_test_db_0.process_relations as process_relations WHERE (process_relations.pid = ‘AdUwR1QLfbIAAALwAAAAAA’) LIMIT 1 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)”,
“alias”:“remote_0”,
“actual_row_count”:{ “value”:1, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“actual_total_time”:{ “value”:0 },
“start_time”:{ “value”:0 },
“end_time”:{ “value”:7629 },
“inputs”:[
{
“executor”:“Project”,
“keyId”:224,
“out”:[
{
“alias”:"",
“projection”:“process_relations.sensor_id”
},
{
“alias”:"",
“projection”:“process_relations.pid”
}
],
“subselects”:[],
“actual_row_count”:{ “value”:1, “avg”:0.031250, “stddev”:0.173993, “max”:1, “maxPartition”:12 },
“actual_total_time”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“start_time”:{ “value”:7628, “avg”:7628.000000, “stddev”:0.000000, “max”:7628, “maxPartition”:12 },
“network_traffic”:{ “value”:58, “avg”:29.000000, “stddev”:29.000000, “max”:58, “maxPartition”:12 },
“network_time”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“inputs”:[
{
“executor”:“Top”,
“keyId”:228,
“limit”:"?",
“actual_row_count”:{ “value”:1, “avg”:0.031250, “stddev”:0.173993, “max”:1, “maxPartition”:12 },
“actual_total_time”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“inputs”:[
{
“executor”:“Filter”,
“keyId”:17179869563,
“condition”:[
“process_relations.pid = ?”
],
“subselects”:[],
“actual_row_count”:{ “value”:1, “avg”:0.031250, “stddev”:0.173993, “max”:1, “maxPartition”:12 },
“actual_total_time”:{ “value”:13, “avg”:9.000000, “stddev”:4.000000, “max”:13, “maxPartition”:1 },
“start_time”:{ “value”:1490, “avg”:1490.000000, “stddev”:0.000000, “max”:1490, “maxPartition”:12 },
“inputs”:[
{
“executor”:“ColumnStoreScan”,
“keyId”:17179869488,
“db”:“ziv_test_db”,
“table”:“process_relations”,
“alias”:“process_relations”,
“index”:“KEY sensor_id (sensor_id, pid, internal_id, generated_time) USING CLUSTERED COLUMNSTORE”,
“storage”:“columnar”,
“actual_row_count”:{ “value”:277662424, “avg”:8676950.750000, “stddev”:1928388.636170, “max”:17619672, “maxPartition”:21 },
“actual_total_time”:{ “value”:7342, “avg”:7233.500000, “stddev”:108.500000, “max”:7342, “maxPartition”:1 },
“start_time”:{ “value”:0, “avg”:4.500000, “stddev”:0.000000, “max”:16, “maxPartition”:30 },
“memory_usage”:{ “value”:57409536, “avg”:1794048.000000, “stddev”:392447.248532, “max”:2162688, “maxPartition”:0 },
“segments_scanned”:{ “value”:333, “avg”:10.406250, “stddev”:1.711348, “max”:18, “maxPartition”:21 },
“segments_skipped”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“segments_fully_contained”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“segments_filter_encoded_data”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“inputs”:[]
}
]
}
]
}
]
}
]
}
]
}
],
“version”:“4”,
“info”:{
“memsql_version”:“7.0.1”,
“memsql_version_hash”:“604cf2b3e99b622ffb91c0f4357e9e35a6520acc”,
“num_online_leaves”:“2”,
“num_online_aggs”:“2”,
“context_database”:“ziv_test_db”
},
“query_info”:{
“query_text”:“profile select sensor_id, pid from process_relations where pid=‘AdUwR1QLfbIAAALwAAAAAA==’ limit 1”,
“total_runtime_ms”:“8327”,
“text_profile”:“Top limit:[?] actual_rows: 1 exec_time: 0ms\nGather partitions:all alias:remote_0 actual_rows: 1 exec_time: 0ms start_time: 00:00:00.000 end_time: 00:00:07.629\nProject [process_relations.sensor_id, process_relations.pid] actual_rows: 1 exec_time: 0ms start_time: 00:00:07.628 network_traffic: 0.058000 KB network_time: 0ms\nTop limit:[?] actual_rows: 1 exec_time: 0ms\nFilter [process_relations.pid = ?] actual_rows: 1 exec_time: 13ms start_time: 00:00:01.490\nColumnStoreScan ziv_test_db.process_relations, KEY sensor_id (sensor_id, pid, internal_id, generated_time) USING CLUSTERED COLUMNSTORE actual_rows: 277,662,424 exec_time: 7,342ms start_time: [00:00:00.000, 00:00:00.016] memory_usage: 57,409.535156 KB segments_scanned: 333 segments_skipped: 0 segments_fully_contained: 0\n”
}
} |
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[zmeidav]

Thinking it may have something to do with querying columns that may be part of the columnstore, i tried querying the event_id column which is not part of the columnstore key.
I tried running this to see if there is any difference between my two tables, one with hash keys, one without. ( the explain looks the same on both of them)

memsql> select sensor_id, pid,event_id from process_relations where event_id=‘AAABa68d3r4gPv5oAAAAdA==’ limit 1;
±---------------------------------±-------------------------±-------------------------+
| sensor_id | pid | event_id |
±---------------------------------±-------------------------±-------------------------+
| 9174dbb97c1ab47be93560a966b332ca | AdUwR1QLfbIAAALwAAAAAA== | AAABa68d3r4gPv5oAAAAdA== |
±---------------------------------±-------------------------±-------------------------+
1 row in set (0.84 sec)

memsql> select sensor_id, pid,event_id from process_relations_no_hashes where event_id=‘AAABa68d3r4gPv5oAAAAdA==’ limit 1;
±---------------------------------±-------------------------±-------------------------+
| sensor_id | pid | event_id |
±---------------------------------±-------------------------±-------------------------+
| 9174dbb97c1ab47be93560a966b332ca | AdUwR1QLfbIAAALwAAAAAA== | AAABa68d3r4gPv5oAAAAdA== |
±---------------------------------±-------------------------±-------------------------+
1 row in set (1.07 sec)

memsql> explain select sensor_id, pid,event_id from process_relations where event_id=‘AAABa7k4kBoM7ngUAAWVPA==’ limit 1;
±----------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
±----------------------------------------------------------------------------------------------------------------------------------------------+
| Top limit:1 |
| Gather partitions:all alias:remote_0 |
| Project [process_relations.sensor_id, process_relations.pid, process_relations.event_id] |
| Top limit:1 |
| Filter [process_relations.event_id = ‘AAABa7k4kBoM7ngUAAWVPA==’] |
| ColumnStoreScan ziv_test_db.process_relations, KEY sensor_id (sensor_id, pid, internal_id, generated_time) USING CLUSTERED COLUMNSTORE |
±----------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.06 sec)

memsql> profile select sensor_id, pid,event_id from process_relations where event_id=‘AAABa68d3r4gPv5oAAAAdA==’ limit 1;
±---------------------------------±-------------------------±-------------------------+
| sensor_id | pid | event_id |
±---------------------------------±-------------------------±-------------------------+
| 9174dbb97c1ab47be93560a966b332ca | AdUwR1QLfbIAAALwAAAAAA== | AAABa68d3r4gPv5oAAAAdA== |
±---------------------------------±-------------------------±-------------------------+
1 row in set (4.74 sec)

memsql> show profile json;
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PROFILE |
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
“profile”:[
{
“executor”:“Top”,
“keyId”:76,
“limit”:"?",
“actual_row_count”:{ “value”:1 },
“actual_total_time”:{ “value”:0 },
“inputs”:[
{
“executor”:“Gather”,
“keyId”:4294967448,
“partitions”:“all”,
“query”:“SELECT process_relations.sensor_id AS sensor_id, process_relations.pid AS pid, process_relations.event_id AS event_id FROM ziv_test_db_0.process_relations as process_relations WHERE (process_relations.event_id = ‘AdUwR1QLfbIAAALwAAAAAA==’) LIMIT 1 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)”,
“alias”:“remote_0”,
“actual_row_count”:{ “value”:1, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“actual_total_time”:{ “value”:0 },
“start_time”:{ “value”:14 },
“end_time”:{ “value”:3914 },
“inputs”:[
{
“executor”:“Project”,
“keyId”:224,
“out”:[
{
“alias”:"",
“projection”:“process_relations.sensor_id”
},
{
“alias”:"",
“projection”:“process_relations.pid”
},
{
“alias”:"",
“projection”:“process_relations.event_id”
}
],
“subselects”:[],
“actual_row_count”:{ “value”:1, “avg”:0.031250, “stddev”:0.173993, “max”:1, “maxPartition”:12 },
“actual_total_time”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“start_time”:{ “value”:3913, “avg”:3913.000000, “stddev”:0.000000, “max”:3913, “maxPartition”:12 },
“network_traffic”:{ “value”:83, “avg”:27.666667, “stddev”:39.126575, “max”:83, “maxPartition”:12 },
“network_time”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“inputs”:[
{
“executor”:“Top”,
“keyId”:228,
“limit”:"?",
“actual_row_count”:{ “value”:1, “avg”:0.031250, “stddev”:0.173993, “max”:1, “maxPartition”:12 },
“actual_total_time”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“inputs”:[
{
“executor”:“Filter”,
“keyId”:17179869563,
“condition”:[
“process_relations.event_id = ?”
],
“subselects”:[],
“actual_row_count”:{ “value”:1, “avg”:0.031250, “stddev”:0.173993, “max”:1, “maxPartition”:12 },
“actual_total_time”:{ “value”:19, “avg”:17.500000, “stddev”:1.500000, “max”:19, “maxPartition”:0 },
“start_time”:{ “value”:1019, “avg”:1019.000000, “stddev”:0.000000, “max”:1019, “maxPartition”:12 },
“inputs”:[
{
“executor”:“ColumnStoreScan”,
“keyId”:17179869488,
“db”:“ziv_test_db”,
“table”:“process_relations”,
“alias”:“process_relations”,
“index”:“KEY sensor_id (sensor_id, pid, internal_id, generated_time) USING CLUSTERED COLUMNSTORE”,
“storage”:“columnar”,
“actual_row_count”:{ “value”:222533241, “avg”:6954163.781250, “stddev”:2878764.315001, “max”:17215653, “maxPartition”:30 },
“actual_total_time”:{ “value”:4413, “avg”:4169.000000, “stddev”:244.000000, “max”:4413, “maxPartition”:0 },
“start_time”:{ “value”:7, “avg”:16.281250, “stddev”:0.000000, “max”:31, “maxPartition”:28 },
“memory_usage”:{ “value”:53215232, “avg”:1662976.000000, “stddev”:136833.376981, “max”:2424832, “maxPartition”:31 },
“segments_scanned”:{ “value”:277, “avg”:8.656250, “stddev”:2.444501, “max”:17, “maxPartition”:30 },
“segments_skipped”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“segments_fully_contained”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“segments_filter_encoded_data”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“inputs”:[]
}
]
}
]
}
]
}
]
}
]
}
],
“version”:“4”,
“info”:{
“memsql_version”:“7.0.1”,
“memsql_version_hash”:“604cf2b3e99b622ffb91c0f4357e9e35a6520acc”,
“num_online_leaves”:“2”,
“num_online_aggs”:“2”,
“context_database”:“ziv_test_db”
},
“query_info”:{
“query_text”:“profile select sensor_id, pid,event_id from process_relations where event_id=‘AAABa68d3r4gPv5oAAAAdA==’ limit 1”,
“total_runtime_ms”:“4738”,
“text_profile”:“Top limit:[?] actual_rows: 1 exec_time: 0ms\nGather partitions:all alias:remote_0 actual_rows: 1 exec_time: 0ms start_time: 00:00:00.014 end_time: 00:00:03.914\nProject [process_relations.sensor_id, process_relations.pid, process_relations.event_id] actual_rows: 1 exec_time: 0ms start_time: 00:00:03.913 network_traffic: 0.083000 KB network_time: 0ms\nTop limit:[?] actual_rows: 1 exec_time: 0ms\nFilter [process_relations.event_id = ?] actual_rows: 1 exec_time: 19ms start_time: 00:00:01.019\nColumnStoreScan ziv_test_db.process_relations, KEY sensor_id (sensor_id, pid, internal_id, generated_time) USING CLUSTERED COLUMNSTORE actual_rows: 222,533,241 exec_time: 4,413ms start_time: [00:00:00.007, 00:00:00.031] memory_usage: 53,215.230469 KB segments_scanned: 277 segments_skipped: 0 segments_fully_contained: 0\n”
}
} |

1 row in set (0.00 sec)

[zmeidav]

Actually the profile of the two queries on the no_hashes table and the original table looks completely different in where the exec time was mostly spent.
profile select sensor_id, pid,event_id from process_relations where event_id=‘AAABa68d3r4gPv5oAAAAdA==’ limit 1
memsql> show profile json;
| PROFILE |
| {
“profile”:[
{
“executor”:“Top”,
“keyId”:76,
“limit”:"?",
“actual_row_count”:{ “value”:1 },
“actual_total_time”:{ “value”:0 },
“inputs”:[
{
“executor”:“Gather”,
“keyId”:4294967448,
“partitions”:“all”,
“query”:“SELECT process_relations.sensor_id AS sensor_id, process_relations.pid AS pid, process_relations.event_id AS event_id FROM ziv_test_db_0.process_relations as process_relations WHERE (process_relations.event_id = ‘AdUwR1QLfbIAAALwAAAAAA==’) LIMIT 1 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)”,
“alias”:“remote_0”,
“actual_row_count”:{ “value”:1, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“actual_total_time”:{ “value”:0 },
“start_time”:{ “value”:0 },
“end_time”:{ “value”:328 },
“inputs”:[
{
“executor”:“Project”,
“keyId”:224,
“out”:[
{
“alias”:"",
“projection”:“process_relations.sensor_id”
},
{
“alias”:"",
“projection”:“process_relations.pid”
},
{
“alias”:"",
“projection”:“process_relations.event_id”
}
],
“subselects”:[],
“actual_row_count”:{ “value”:1, “avg”:0.031250, “stddev”:0.173993, “max”:1, “maxPartition”:12 },
“actual_total_time”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“start_time”:{ “value”:327, “avg”:327.000000, “stddev”:0.000000, “max”:327, “maxPartition”:12 },
“network_traffic”:{ “value”:83, “avg”:83.000000, “stddev”:0.000000, “max”:83, “maxPartition”:12 },
“network_time”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“inputs”:[
{
“executor”:“Top”,
“keyId”:228,
“limit”:"?",
“actual_row_count”:{ “value”:1, “avg”:0.031250, “stddev”:0.173993, “max”:1, “maxPartition”:12 },
“actual_total_time”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“inputs”:[
{
“executor”:“Filter”,
“keyId”:17179869563,
“condition”:[
“process_relations.event_id = ?”
],
“subselects”:[],
“actual_row_count”:{ “value”:1, “avg”:0.031250, “stddev”:0.173993, “max”:1, “maxPartition”:12 },
“actual_total_time”:{ “value”:16, “avg”:12.500000, “stddev”:3.500000, “max”:16, “maxPartition”:0 },
“start_time”:{ “value”:288, “avg”:288.000000, “stddev”:0.000000, “max”:288, “maxPartition”:12 },
“inputs”:[
{
“executor”:“ColumnStoreScan”,
“keyId”:17179869488,
“db”:“ziv_test_db”,
“table”:“process_relations”,
“alias”:“process_relations”,
“index”:“KEY sensor_id (sensor_id, pid, internal_id, generated_time) USING CLUSTERED COLUMNSTORE”,
“storage”:“columnar”,
“actual_row_count”:{ “value”:116477952, “avg”:3639936.000000, “stddev”:4684083.773010, “max”:12288000, “maxPartition”:5 },
“actual_total_time”:{ “value”:898, “avg”:889.500000, “stddev”:8.500000, “max”:898, “maxPartition”:1 },
“start_time”:{ “value”:0, “avg”:11.666667, “stddev”:0.000000, “max”:20, “maxPartition”:30 },
“memory_usage”:{ “value”:49676288, “avg”:1552384.000000, “stddev”:189967.869957, “max”:2031616, “maxPartition”:31 },
“segments_scanned”:{ “value”:166, “avg”:5.187500, “stddev”:4.772562, “max”:14, “maxPartition”:5 },
“segments_skipped”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“segments_fully_contained”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“segments_filter_encoded_data”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“inputs”:[]
}
]
}
]
}
]
}
]
}
]
}
],
“version”:“4”,
“info”:{
“memsql_version”:“7.0.1”,
“memsql_version_hash”:“604cf2b3e99b622ffb91c0f4357e9e35a6520acc”,
“num_online_leaves”:“2”,
“num_online_aggs”:“2”,
“context_database”:“ziv_test_db”
},
“query_info”:{
“query_text”:“profile select sensor_id, pid,event_id from process_relations where event_id=‘AAABa68d3r4gPv5oAAAAdA==’ limit 1”,
“total_runtime_ms”:“921”,
“text_profile”:“Top limit:[?] actual_rows: 1 exec_time: 0ms\nGather partitions:all alias:remote_0 actual_rows: 1 exec_time: 0ms start_time: 00:00:00.000 end_time: 00:00:00.328\nProject [process_relations.sensor_id, process_relations.pid, process_relations.event_id] actual_rows: 1 exec_time: 0ms start_time: 00:00:00.327 network_traffic: 0.083000 KB network_time: 0ms\nTop limit:[?] actual_rows: 1 exec_time: 0ms\nFilter [process_relations.event_id = ?] actual_rows: 1 exec_time: 16ms start_time: 00:00:00.288\nColumnStoreScan ziv_test_db.process_relations, KEY sensor_id (sensor_id, pid, internal_id, generated_time) USING CLUSTERED COLUMNSTORE actual_rows: 116,477,952 exec_time: 898ms start_time: [00:00:00.000, 00:00:00.020] memory_usage: 49,676.289062 KB segments_scanned: 166 segments_skipped: 0 segments_fully_contained: 0\n [actual_rows: 116,477,952 | max:12,288,000 at partition_5, average: 3,639,936.000000, std dev: 4,684,083.773010]\n”
}
} |

profile select sensor_id, pid,event_id from process_relations_no_hashes where event_id=‘AAABa68d3r4gPv5oAAAAdA==’ limit 1;
memsql> show profile json;
| PROFILE |
| {
“profile”:[
{
“executor”:“Top”,
“keyId”:76,
“limit”:"?",
“actual_row_count”:{ “value”:1 },
“actual_total_time”:{ “value”:0 },
“inputs”:[
{
“executor”:“Gather”,
“keyId”:4294967448,
“partitions”:“all”,
“query”:“SELECT process_relations_no_hashes.sensor_id AS sensor_id, process_relations_no_hashes.pid AS pid, process_relations_no_hashes.event_id AS event_id FROM ziv_test_db_0.process_relations_no_hashes as process_relations_no_hashes WHERE (process_relations_no_hashes.event_id = ‘AAABa68d3r4gPv5oAAAAdA==’) LIMIT 1 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)”,
“alias”:“remote_0”,
“actual_row_count”:{ “value”:1, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“actual_total_time”:{ “value”:0 },
“start_time”:{ “value”:0 },
“end_time”:{ “value”:1117 },
“inputs”:[
{
“executor”:“Project”,
“keyId”:224,
“out”:[
{
“alias”:"",
“projection”:“process_relations_no_hashes.sensor_id”
},
{
“alias”:"",
“projection”:“process_relations_no_hashes.pid”
},
{
“alias”:"",
“projection”:“process_relations_no_hashes.event_id”
}
],
“subselects”:[],
“actual_row_count”:{ “value”:1, “avg”:0.031250, “stddev”:0.173993, “max”:1, “maxPartition”:12 },
“actual_total_time”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“start_time”:{ “value”:1117, “avg”:1117.000000, “stddev”:0.000000, “max”:1117, “maxPartition”:12 },
“network_traffic”:{ “value”:83, “avg”:83.000000, “stddev”:0.000000, “max”:83, “maxPartition”:12 },
“network_time”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“inputs”:[
{
“executor”:“Top”,
“keyId”:228,
“limit”:"?",
“actual_row_count”:{ “value”:1, “avg”:0.031250, “stddev”:0.173993, “max”:1, “maxPartition”:12 },
“actual_total_time”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“inputs”:[
{
“executor”:“Filter”,
“keyId”:4294967675,
“condition”:[
“process_relations_no_hashes.event_id = ?”
],
“subselects”:[],
“actual_row_count”:{ “value”:1, “avg”:0.031250, “stddev”:0.173993, “max”:1, “maxPartition”:12 },
“actual_total_time”:{ “value”:1099, “avg”:1096.000000, “stddev”:3.000000, “max”:1099, “maxPartition”:0 },
“start_time”:{ “value”:3, “avg”:3.000000, “stddev”:0.000000, “max”:3, “maxPartition”:12 },
“inputs”:[
{
“executor”:“ColumnStoreScan”,
“keyId”:4294967600,
“db”:“ziv_test_db”,
“table”:“process_relations_no_hashes”,
“alias”:“process_relations_no_hashes”,
“index”:“KEY sensor_id (sensor_id, pid, internal_id, generated_time) USING CLUSTERED COLUMNSTORE”,
“storage”:“columnar”,
“actual_row_count”:{ “value”:271212544, “avg”:8475392.000000, “stddev”:1733883.728918, “max”:12087296, “maxPartition”:7 },
“actual_total_time”:{ “value”:29, “avg”:26.000000, “stddev”:3.000000, “max”:29, “maxPartition”:1 },
“start_time”:{ “value”:0, “avg”:19.437500, “stddev”:0.000000, “max”:72, “maxPartition”:31 },
“memory_usage”:{ “value”:34865152, “avg”:1089536.000000, “stddev”:76409.889203, “max”:1310720, “maxPartition”:2 },
“segments_scanned”:{ “value”:315, “avg”:9.843750, “stddev”:1.679088, “max”:13, “maxPartition”:7 },
“segments_skipped”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“segments_fully_contained”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“segments_filter_encoded_data”:{ “value”:66214, “avg”:2069.187500, “stddev”:423.311457, “max”:2951, “maxPartition”:7 },
“inputs”:[]
}
]
}
]
}
]
}
]
}
]
}
],
“version”:“4”,
“info”:{
“memsql_version”:“7.0.1”,
“memsql_version_hash”:“604cf2b3e99b622ffb91c0f4357e9e35a6520acc”,
“num_online_leaves”:“2”,
“num_online_aggs”:“2”,
“context_database”:“ziv_test_db”
},
“query_info”:{
“query_text”:“profile select sensor_id, pid,event_id from process_relations_no_hashes where event_id=‘AAABa68d3r4gPv5oAAAAdA==’ limit 1”,
“total_runtime_ms”:“1127”,
“text_profile”:“Top limit:[?] actual_rows: 1 exec_time: 0ms\nGather partitions:all alias:remote_0 actual_rows: 1 exec_time: 0ms start_time: 00:00:00.000 end_time: 00:00:01.117\nProject [process_relations_no_hashes.sensor_id, process_relations_no_hashes.pid, process_relations_no_hashes.event_id] actual_rows: 1 exec_time: 0ms start_time: 00:00:01.117 network_traffic: 0.083000 KB network_time: 0ms\nTop limit:[?] actual_rows: 1 exec_time: 0ms\nFilter [process_relations_no_hashes.event_id = ?] actual_rows: 1 exec_time: 1,099ms start_time: 00:00:00.003\nColumnStoreScan ziv_test_db.process_relations_no_hashes, KEY sensor_id (sensor_id, pid, internal_id, generated_time) USING CLUSTERED COLUMNSTORE actual_rows: 271,212,544 exec_time: 29ms start_time: [00:00:00.000, 00:00:00.072] memory_usage: 34,865.152344 KB segments_scanned: 315 segments_skipped: 0 segments_fully_contained: 0\n”
}
} |
1 row in set (0.00 sec)

[haoran]

memsql> profile select sensor_id, pid from process_relations where pid=‘AdUwR1QLfbIAAALwAAAAAA==’ limit 1;
±---------------------------------±-------------------------+
| sensor_id | pid |
±---------------------------------±-------------------------+
| 9174dbb97c1ab47be93560a966b332ca | AdUwR1QLfbIAAALwAAAAAA== |
±---------------------------------±-------------------------+
1 row in set (8.33 sec)

This query is really suspicious, it seems very likely a bug on our side. From the runtime, apparently it is not using secondary hash index at all, despite that from your earlier show columnar merge status output there is indeed a secondary index on column pid. This could also be confirmed from your profile output:

“segments_scanned”:{ “value”:333, “avg”:10.406250, “stddev”:1.711348, “max”:18, “maxPartition”:21 },
“segments_skipped”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“segments_fully_contained”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },

None of the segments are eliminated by secondary index (the “segments_skipped” value).

I tried to repro it on my machine, but I cannot repro it. Can you attach the exact table schema by running the following command, so it could be more possible for us to repro it:

show create table process_relations

Thanks again for your help!

[zmeidav]

@haoran

show create table process_relations

create table process_relations(sensor_id VARCHAR(32), pid VARCHAR(32), parent_pid VARCHAR(32),
internal_id VARCHAR(32), event_id VARCHAR(32), generated_time DATETIME(6), KEY(event_id) USING HASH, KEY(pid) USING HASH, key(internal_id) USING HASH,
KEY(sensor_id, pid, internal_id, generated_time) USING CLUSTERED COLUMNSTORE, SHARD KEY(sensor_id));

CREATE TABLE process_relations (
sensor_id varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
pid varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
parent_pid varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
internal_id varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
event_id varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
generated_time datetime(6) DEFAULT NULL,
KEY event_id (event_id) USING HASH,
KEY pid (pid) USING HASH,
KEY internal_id (internal_id) USING HASH,
KEY sensor_id (sensor_id,pid,internal_id,generated_time) /*!90619 USING CLUSTERED COLUMNSTORE /,
/!90618 SHARD / KEY sensor_id_2 (sensor_id)
) /!90621 AUTOSTATS_ENABLED=TRUE / /!90623 SQL_MODE=‘STRICT_ALL_TABLES’ */

[haoran]

We have discovered the issue that caused this performance problem. It affects all string data types, but does not affect integer or binary data types. We will make sure that it will be fixed in 7.0.
The easiest workaround at this time is to make the column type varbinary instead of varchar.

Note that ALTER TABLE is not supported on a table with columnstore secondary hash index in our beta1 version (although it will be supported in the GA version) . So you will have to create another table and dump the data.