Hey
I have a 4.0.4 cluster, with reads of partitions that are a bit on the
bigger side, taking longer than I would expect.
Reading entire partition that has ~70000 rows, total partition size of 4mb,
takes 120ms, I would expect it to take less.
This is after major compaction, so there is only one sstables. local_one
consistency level, no tombstones, and reading the entire partition in one
fetch.
Cluster is not doing much else at the time, nvme disk. I can see most of
the time is spent on getting the data from the sstable.
Is there any specific optimization one can do to speed up cases like this?
I would expect fetching 4mb to take less, I assume if this was one blob of
4mb that would be the case.
Table definition :
CREATE TABLE ks1.item_to_item (
x1 bigint,
x2 bigint,
x3 int,
x4 int,
y1 bigint,
y2 bigint,
y3 bigint,
y4 bigint,
y5 bigint,
metadata text,
m_metadata_created_at timestamp,
m_metadata_created_by bigint,
m_metadata_updated_at timestamp,
PRIMARY KEY ((x1, x2, x3, x4), y1, y2, y3, y4, y5)
) WITH CLUSTERING ORDER BY (y1 ASC, y2 ASC, y3 ASC, y4 ASC, y5 ASC)
cqlsh> select count(0) from ks1.item_to_item where x1=4 and x2=70000 and
x4=0 and x3=1;
count
-------
70000
(1 rows)
Tracing session: 6356d290-d785-11ed-aba5-ab86979f2f58
activity
| timestamp | source | source_elapsed | client
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+------------+----------------+-----------
Execute CQL3 query
| 2023-04-10 09:52:21.561000 | 172.25.0.4 | 0 |
127.0.0.1
Parsing XXXX [Native-Transport-Requests-1]
| 2023-04-10 09:52:21.561000 | 172.25.0.4 | 428 |
127.0.0.1
Preparing statement
[Native-Transport-Requests-1] | 2023-04-10 09:52:21.562000 |
172.25.0.4 | 973 | 127.0.0.1
Acquiring sstable references
[ReadStage-2] | 2023-04-10 09:52:21.563000 | 172.25.0.4 |
2255 | 127.0.0.1
Skipped 0/1 non-slice-intersecting sstables, included 0 due to tombstones
[ReadStage-2] | 2023-04-10 09:52:21.563000 | 172.25.0.4 | 2524 |
127.0.0.1
Key cache hit for sstable 9
[ReadStage-2] | 2023-04-10 09:52:21.563000 | 172.25.0.4 |
2692 | 127.0.0.1
Merged data from memtables and 1 sstables
[ReadStage-2] | 2023-04-10 09:52:21.651000 | 172.25.0.4 |
90405 | 127.0.0.1
Read 70000 live rows and 0 tombstone cells
[ReadStage-2] | 2023-04-10 09:52:21.651000 | 172.25.0.4 |
90726 | 127.0.0.1
Request
complete | 2023-04-10 09:52:21.682603 | 172.25.0.4 |
121603 | 127.0.0.1
gil
