我有下表：
CREATE TABLE mydb.customer_data (
field1 int,
field2 int,
field3 int,
field4 text,
field5 text,
data_time timestamp,
PRIMARY KEY ((field1, field2, field3, field4, field5), data_time)
) WITH CLUSTERING ORDER BY (utilisation_time ASC)
AND bloom_filter_fp_chance = 0.01
AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
AND comment = ''
AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
AND crc_check_chance = 1.0
AND dclocal_read_repair_chance = 0.1
AND default_time_to_live = 0
AND gc_grace_seconds = 864000
AND max_index_interval = 2048
AND memtable_flush_period_in_ms = 0
AND min_index_interval = 128
AND read_repair_chance = 0.0
AND speculative_retry = '99PERCENTILE';我有脚本收集每日数据如下：
SELECT * FROM mydb.customer_data WHERE field1 = 21 AND
data_time >= '2020-03-26 16:00:00' AND
data_time <= '2020-03-27 15:59:00' ALLOW FILTERING几乎所有的日子都可以毫无问题地查询，除了某一天，我相信这一天的数据比另一天大。
客户端（java）收到以下错误消息：
Cassandra failure during read query at consistency LOCAL_ONE (1 responses were required but only 0 replica responded, 1 failed)在system.log中，我在客户端应用程序执行查询时捕获此日志：
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,029 MessagingService.java:1236 - READ messages were dropped in last 5000 ms: 1 internal and 0 cross node. Mean internal dropped latency: 5960 ms and Mean cross-node dropped latency: 0 ms
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,029 StatusLogger.java:47 - Pool Name Active Pending Completed Blocked All Time Blocked
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,033 StatusLogger.java:51 - MutationStage 0 0 2273404011 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,033 StatusLogger.java:51 - ViewMutationStage 0 0 0 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,033 StatusLogger.java:51 - ReadStage 0 0 55205177 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,033 StatusLogger.java:51 - RequestResponseStage 0 0 3551921449 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,033 StatusLogger.java:51 - ReadRepairStage 0 0 1016339 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,034 StatusLogger.java:51 - CounterMutationStage 0 0 0 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,034 StatusLogger.java:51 - MiscStage 0 0 0 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,034 StatusLogger.java:51 - CompactionExecutor 0 0 19952206 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,034 StatusLogger.java:51 - MemtableReclaimMemory 0 0 22014 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,034 StatusLogger.java:51 - PendingRangeCalculator 0 0 8 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,035 StatusLogger.java:51 - GossipStage 0 0 75343472 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,035 StatusLogger.java:51 - SecondaryIndexManagement 0 0 0 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,035 StatusLogger.java:51 - HintsDispatcher 0 0 681 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,035 StatusLogger.java:51 - MigrationStage 0 0 108 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,035 StatusLogger.java:51 - MemtablePostFlush 0 0 22759 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,035 StatusLogger.java:51 - PerDiskMemtableFlushWriter_0 0 0 22006 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,036 StatusLogger.java:51 - ValidationExecutor 0 0 392 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,036 StatusLogger.java:51 - Sampler 0 0 0 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,036 StatusLogger.java:51 - MemtableFlushWriter 0 0 22014 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,036 StatusLogger.java:51 - InternalResponseStage 0 0 421680 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,036 StatusLogger.java:51 - AntiEntropyStage 0 0 1104 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,036 StatusLogger.java:51 - CacheCleanupExecutor 0 0 0 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,037 StatusLogger.java:51 - Native-Transport-Requests 0 0 1619401305 0 19514
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,037 StatusLogger.java:61 - CompactionManager 0 0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,037 StatusLogger.java:73 - MessagingService n/a 0/0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,037 StatusLogger.java:83 - Cache Type Size Capacity KeysToSave
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,037 StatusLogger.java:85 - KeyCache 99614696 99614720 all
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,037 StatusLogger.java:91 - RowCache 0 0 all
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,037 StatusLogger.java:98 - Table Memtable ops,data
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,037 StatusLogger.java:101 - system_distributed.parent_repair_history 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,037 StatusLogger.java:101 - system_distributed.repair_history 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,037 StatusLogger.java:101 - system_distributed.view_build_status 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,037 StatusLogger.java:101 - system.compaction_history 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,037 StatusLogger.java:101 - system.schema_aggregates 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.schema_triggers 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.size_estimates 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.paxos 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.views_builds_in_progress 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.batches 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.schema_keyspaces 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.sstable_activity 39,2699
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.batchlog 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.schema_columns 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.hints 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.IndexInfo 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.schema_columnfamilies 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.schema_functions 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.built_views 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,038 StatusLogger.java:101 - system.peer_events 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - system.range_xfers 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - system.peers 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - system.transferred_ranges 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - system.schema_usertypes 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - system.local 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - system.available_ranges 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - system.prepared_statements 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - mydb.customer_data 30776,2714968
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - mydb.table1 296603,12546709
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - mydb.table2 26825,4419610
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - mydb.table3 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - mydb.table4 3,236
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - mydb.table5 63,4990
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - mydb.table6 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - mydb.table7 3,363
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,039 StatusLogger.java:101 - mydb.table8 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - mydb.table9 4,108
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - mydb.table10 2,70
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - mydb.table11 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - mydb.table12 35,1583
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - mydb.table13 7920,790982
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - mydb.table14 123,7473
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - mydb.table15 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - mydb.table16 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - mydb.table17 18638,3336186
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - mydb.table18 9254,1443624
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - mydb.table19 3,69
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - system_schema.columns 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - system_schema.types 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,040 StatusLogger.java:101 - system_schema.indexes 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,041 StatusLogger.java:101 - system_schema.keyspaces 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,041 StatusLogger.java:101 - system_schema.dropped_columns 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,041 StatusLogger.java:101 - system_schema.aggregates 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,041 StatusLogger.java:101 - system_schema.triggers 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,041 StatusLogger.java:101 - system_schema.tables 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,041 StatusLogger.java:101 - system_schema.views 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,041 StatusLogger.java:101 - system_schema.functions 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,041 StatusLogger.java:101 - system_auth.roles 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,041 StatusLogger.java:101 - system_auth.role_members 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,041 StatusLogger.java:101 - system_auth.resource_role_permissons_index 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,041 StatusLogger.java:101 - system_auth.role_permissions 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,041 StatusLogger.java:101 - system_traces.sessions 0,0
INFO [ScheduledTasks:1] 2020-04-03 05:07:59,041 StatusLogger.java:101 - system_traces.events 0,0
DEBUG [ScheduledTasks:1] 2020-04-03 05:07:59,041 MonitoringTask.java:152 - 1 operations timed out in the last 5017 msecs:
<SELECT * FROM mydb.customer_data WHERE field1 = 21 AND data_time >= 2020-03-26 16:00Z AND data_time <= 2020-03-27 15:59Z LIMIT 5000>, total time 5004 msec, timeout 5000 msec这是 nodetool tablestats 表的 mydb.customer_data 在其中一个节点中：
ubuntu@ip-172-31-44-227:/usr/local/cassandra/conf$ /usr/local/cassandra/bin/nodetool tablestats prospacedb.quarter_utilisation
Total number of tables: 56
----------------
Keyspace : mydb
Read Count: 5282126
Read Latency: 2.48710102428454 ms
Write Count: 2164892217
Write Latency: 0.016614636464832373 ms
Pending Flushes: 0
Table: customer_data
SSTable count: 15
Space used (live): 2455268488
Space used (total): 2455268488
Space used by snapshots (total): 3241990632
Off heap memory used (total): 1019317
SSTable Compression Ratio: 0.3615507660944151
Number of partitions (estimate): 14732
Memtable cell count: 86273
Memtable data size: 7612904
Memtable off heap memory used: 0
Memtable switch count: 1664
Local read count: 592956
Local read latency: NaN ms
Local write count: 278088230
Local write latency: NaN ms
Pending flushes: 0
Percent repaired: 76.49
Bloom filter false positives: 0
Bloom filter false ratio: 0.00000
Bloom filter space used: 145840
Bloom filter off heap memory used: 145720
Index summary off heap memory used: 47685
Compression metadata off heap memory used: 825912
Compacted partition minimum bytes: 87
Compacted partition maximum bytes: 1131752
Compacted partition mean bytes: 63353
Average live cells per slice (last five minutes): NaN
Maximum live cells per slice (last five minutes): 0
Average tombstones per slice (last five minutes): NaN
Maximum tombstones per slice (last five minutes): 0
Dropped Mutations: 0造成这个问题的潜在根本原因是什么？有什么快速的解决方案可以让查询运行吗？
1条答案
按热度按时间sd2nnvve1#
这个查询失败的主要原因是它不正确—只有当您有完整的分区密钥时，cassandra才能快速工作，然后您可以在该分区内执行范围查询。在您的例子中，分区键由5列组成，但在查询中只提供一列，cassandra需要对整个数据进行扫描，以找到相应的行所在的位置。我真的很想知道它以前是否有用。。。
为了解决您的问题，您需要更改表结构，使分区/主键与您的查询相匹配—cassandra的所有数据建模都从应该执行的查询开始。我建议在datastax学院学习ds220数据建模课程。