我的查询磁盘查找过多时遇到问题。
对于这样的查询:
SELECT sessionid
FROM sessionlink
WHERE linktype = 'host'
AND link = 'webserver1'
AND timestamp > minTimeuuid('2016-07-02 09:00:00')
AND timestamp < maxTimeuuid('2016-07-02 10:00:00');
在这样的桌子上:
CREATE TABLE logs.sessionlink (
linktype text,
link text,
"timestamp" timeuuid,
sessionid text,
PRIMARY KEY ((linktype, link), "timestamp", sessionid)
);
我正在打 120 个 SS 表。为什么查询在磁盘上的这么多 SS 表中查找,而不仅仅是在给定日期内包含数据的一次?我手动检查了带有sstablemetadata
的 SS 表,发现只有一个 SS 表包含查询的日期(查看最小和最大时间戳)。
数据插入时:
INSERT INTO sessionlink(sessionid, linktype, link, timestamp)
VALUES (?, ?, ?, now()) USING TTL ?;
并使用日期分层压缩来压缩表。
在阅读了有关日期分层压缩(https://labs.spotify.com/2014/12/18/date-tiered-compaction/)和其他资源的Spotify博客后,我的印象是,它将允许快速读取时间序列数据,因为SS表是按时间排序的,并且会最大限度地减少磁盘读取的数量。但是,这不是我在集群上看到的。
下面是查询的跟踪(删除了一些键缓存命中和查找行):
activity | timestamp | source | source_elapsed
-------------------------------------------------------------------------------------------------+----------------------------+---------------+----------------
Execute CQL3 query | 2016-07-08 12:06:17.333000 | 192.168.4.184 | 0
Parsing "The query from above" [SharedPool-Worker-6] | 2016-07-08 12:06:17.335000 | 192.168.4.184 | 72
READ message received from /192.168.4.184 [MessagingService-Incoming-/192.168.4.184] | 2016-07-08 12:06:17.335000 | 192.168.4.186 | 23
Preparing statement [SharedPool-Worker-6] | 2016-07-08 12:06:17.336000 | 192.168.4.184 | 236
Executing single-partition query on sessionlink [SharedPool-Worker-1] | 2016-07-08 12:06:17.336000 | 192.168.4.186 | 80
reading data from /192.168.4.186 [SharedPool-Worker-6] | 2016-07-08 12:06:17.337000 | 192.168.4.184 | 1224
Acquiring sstable references [SharedPool-Worker-1] | 2016-07-08 12:06:17.337000 | 192.168.4.186 | 85
Sending READ message to /192.168.4.186 [MessagingService-Outgoing-/192.168.4.186] | 2016-07-08 12:06:17.338000 | 192.168.4.184 | 1304
Merging memtable tombstones [SharedPool-Worker-1] | 2016-07-08 12:06:17.338000 | 192.168.4.186 | 371
Key cache hit for sstable 1168 [SharedPool-Worker-1] | 2016-07-08 12:06:17.339000 | 192.168.4.186 | 1003
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.339000 | 192.168.4.186 | 1007
Key cache hit for sstable 1167 [SharedPool-Worker-1] | 2016-07-08 12:06:17.339000 | 192.168.4.186 | 1029
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.343000 | 192.168.4.186 | 1031
Key cache hit for sstable 1278 [SharedPool-Worker-1] | 2016-07-08 12:06:17.343000 | 192.168.4.186 | 1965
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.343000 | 192.168.4.186 | 1968
Key cache hit for sstable 1277 [SharedPool-Worker-1] | 2016-07-08 12:06:17.344000 | 192.168.4.186 | 1982
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.344000 | 192.168.4.186 | 1984
Key cache hit for sstable 1276 [SharedPool-Worker-1] | 2016-07-08 12:06:17.344000 | 192.168.4.186 | 1996
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.345000 | 192.168.4.186 | 1999
Key cache hit for sstable 1275 [SharedPool-Worker-1] | 2016-07-08 12:06:17.345000 | 192.168.4.186 | 2011
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.345000 | 192.168.4.186 | 2013
Key cache hit for sstable 1274 [SharedPool-Worker-1] | 2016-07-08 12:06:17.346000 | 192.168.4.186 | 2026
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.346000 | 192.168.4.186 | 2027
Key cache hit for sstable 1273 [SharedPool-Worker-1] | 2016-07-08 12:06:17.346000 | 192.168.4.186 | 2040
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.346000 | 192.168.4.186 | 2041
Key cache hit for sstable 1272 [SharedPool-Worker-1] | 2016-07-08 12:06:17.347000 | 192.168.4.186 | 2053
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.347000 | 192.168.4.186 | 2078
Key cache hit for sstable 1271 [SharedPool-Worker-1] | 2016-07-08 12:06:17.347000 | 192.168.4.186 | 2090
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.348000 | 192.168.4.186 | 2092
... removed some rows to make it fit ...
REQUEST_RESPONSE message received from /192.168.4.186 [MessagingService-Incoming-/192.168.4.186] | 2016-07-08 12:06:17.402000 | 192.168.4.184 | 68884
Processing response from /192.168.4.186 [SharedPool-Worker-2] | 2016-07-08 12:06:17.403000 | 192.168.4.184 | 68953
Key cache hit for sstable 1188 [SharedPool-Worker-1] | 2016-07-08 12:06:17.403000 | 192.168.4.186 | 17630
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.403000 | 192.168.4.186 | 17632
Key cache hit for sstable 1187 [SharedPool-Worker-1] | 2016-07-08 12:06:17.403000 | 192.168.4.186 | 17647
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.404000 | 192.168.4.186 | 17649
Key cache hit for sstable 1186 [SharedPool-Worker-1] | 2016-07-08 12:06:17.404000 | 192.168.4.186 | 17665
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.404000 | 192.168.4.186 | 17666
Key cache hit for sstable 1185 [SharedPool-Worker-1] | 2016-07-08 12:06:17.404000 | 192.168.4.186 | 17884
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.405000 | 192.168.4.186 | 17886
Key cache hit for sstable 1184 [SharedPool-Worker-1] | 2016-07-08 12:06:17.405000 | 192.168.4.186 | 18098
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.405000 | 192.168.4.186 | 18100
Key cache hit for sstable 1183 [SharedPool-Worker-1] | 2016-07-08 12:06:17.405000 | 192.168.4.186 | 18314
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.406000 | 192.168.4.186 | 18317
Key cache hit for sstable 1182 [SharedPool-Worker-1] | 2016-07-08 12:06:17.406000 | 192.168.4.186 | 18537
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.406000 | 192.168.4.186 | 18540
Key cache hit for sstable 1181 [SharedPool-Worker-1] | 2016-07-08 12:06:17.406000 | 192.168.4.186 | 18754
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.407000 | 192.168.4.186 | 18756
Key cache hit for sstable 1180 [SharedPool-Worker-1] | 2016-07-08 12:06:17.407000 | 192.168.4.186 | 18971
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.407000 | 192.168.4.186 | 18974
Key cache hit for sstable 1179 [SharedPool-Worker-1] | 2016-07-08 12:06:17.408000 | 192.168.4.186 | 19212
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.408000 | 192.168.4.186 | 19215
Key cache hit for sstable 1178 [SharedPool-Worker-1] | 2016-07-08 12:06:17.408000 | 192.168.4.186 | 19434
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.408000 | 192.168.4.186 | 19437
Key cache hit for sstable 1177 [SharedPool-Worker-1] | 2016-07-08 12:06:17.409000 | 192.168.4.186 | 19656
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.409000 | 192.168.4.186 | 19658
Key cache hit for sstable 1176 [SharedPool-Worker-1] | 2016-07-08 12:06:17.409000 | 192.168.4.186 | 19873
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.409001 | 192.168.4.186 | 19875
Key cache hit for sstable 1175 [SharedPool-Worker-1] | 2016-07-08 12:06:17.410000 | 192.168.4.186 | 20093
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.410000 | 192.168.4.186 | 20096
Key cache hit for sstable 1174 [SharedPool-Worker-1] | 2016-07-08 12:06:17.410000 | 192.168.4.186 | 20309
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.410000 | 192.168.4.186 | 20311
Key cache hit for sstable 1173 [SharedPool-Worker-1] | 2016-07-08 12:06:17.410000 | 192.168.4.186 | 20537
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.411000 | 192.168.4.186 | 20539
Key cache hit for sstable 1172 [SharedPool-Worker-1] | 2016-07-08 12:06:17.411000 | 192.168.4.186 | 20762
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.411000 | 192.168.4.186 | 20765
Key cache hit for sstable 1171 [SharedPool-Worker-1] | 2016-07-08 12:06:17.412000 | 192.168.4.186 | 20985
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.412000 | 192.168.4.186 | 20987
Key cache hit for sstable 1170 [SharedPool-Worker-1] | 2016-07-08 12:06:17.412000 | 192.168.4.186 | 21212
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.412000 | 192.168.4.186 | 21214
Key cache hit for sstable 1169 [SharedPool-Worker-1] | 2016-07-08 12:06:17.413000 | 192.168.4.186 | 21230
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.413000 | 192.168.4.186 | 21232
Key cache hit for sstable 1166 [SharedPool-Worker-1] | 2016-07-08 12:06:17.413000 | 192.168.4.186 | 21497
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.414000 | 192.168.4.186 | 21501
Key cache hit for sstable 1165 [SharedPool-Worker-1] | 2016-07-08 12:06:17.414000 | 192.168.4.186 | 21928
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.414000 | 192.168.4.186 | 21930
Key cache hit for sstable 1164 [SharedPool-Worker-1] | 2016-07-08 12:06:17.414001 | 192.168.4.186 | 22476
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.415000 | 192.168.4.186 | 22479
Key cache hit for sstable 1163 [SharedPool-Worker-1] | 2016-07-08 12:06:17.415000 | 192.168.4.186 | 22494
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.415000 | 192.168.4.186 | 22496
Key cache hit for sstable 1162 [SharedPool-Worker-1] | 2016-07-08 12:06:17.415000 | 192.168.4.186 | 23103
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.415000 | 192.168.4.186 | 23107
Key cache hit for sstable 1161 [SharedPool-Worker-1] | 2016-07-08 12:06:17.416000 | 192.168.4.186 | 23124
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.416000 | 192.168.4.186 | 23126
Key cache hit for sstable 1160 [SharedPool-Worker-1] | 2016-07-08 12:06:17.416000 | 192.168.4.186 | 23753
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.417000 | 192.168.4.186 | 23757
Key cache hit for sstable 1159 [SharedPool-Worker-1] | 2016-07-08 12:06:17.417000 | 192.168.4.186 | 23771
Seeking to partition indexed section in data file [SharedPool-Worker-1] | 2016-07-08 12:06:17.417000 | 192.168.4.186 | 23773
Request complete | 2016-07-08 12:06:17.402317 | 192.168.4.184 | 69317
https://academy.datastax.com/resources/getting-started-time-series-data-modeling
这是在 Cassandra 中使用日期的一个很好的例子。我认为问题在于您如何对表格进行建模。如果您将时间戳移动到分区键中,Cassandra 将更好地了解要搜索哪些表。如果您只是按(linktype,link)对数据进行排序,则需要搜索数据存储的每个区域。
简而言之,我建议使用主键((链接类型,链接,"时间戳"),sessionid)重新创建表。