Cassandra 查询失败(墓碑)

Question

所以这让我发疯。我尝试查询 Cassandra 中的一张表，结果显示查询失败。我尝试深入探究其背后的原因，发现这是因为墓碑。我将 GC_GRACE_SECONDS 更改为零并使用 nodetool 触发了压缩，当我再次查询时它工作正常。然而，在随后的调用中，由于相同的原因，查询再次失败。我正在使用 cassandra-nodejs 驱动程序。这是我的数据模型。

CREATE TABLE my_table (
    firstname text,
    lastname text,
    email text,
    mobile text,
    date timeuuid,
    value float,
    PRIMARY KEY (firstname, lastname, email, mobile)
) WITH CLUSTERING ORDER BY (lastname ASC, email ASC, mobile ASC);

这是我想要对该数据模型执行的查询。

SELECT firstname, email, toDate(date) as date, mobile, value FROM my_table  WHERE date >= minTimeuuid('2017-03-25 00:00:00+0000') AND date <= minTimeuuid('2017-03-28 23:59:59+0000') ALLOW FILTERING;

结果将有大约 40k 行。 this显示如果我们删除某些内容，它将被标记为逻辑删除，并在为给定表设置 GC_GRACE_SECONDS 之后被删除。如果我理解正确的话。

1. 当我从不删除表的任何行时，为什么会出现墓碑问题？
2. 当且仅当我们删除一行时，一行才会被标记为墓碑，这是真的吗？
3. 清理墓碑然后查询同样的作品有时有时不行，为什么会这样？
4. 增加tombstone_failure_threshold值是个好主意吗？ (单节点集群应用)

我使用的是 cassandra 3.5，cqlsh 版本为 5.0.1。查询在终端上运行良好，但当我们使用外部客户端(使用 cassandra 的 nodejs 驱动程序的 Express 应用程序)执行时会出现错误。我有一个单节点集群应用程序。

编辑1

这是我在字段中插入空值的日志(我只插入了名称和时间戳)；

  activity                                                                                        | timestamp                  | source        | source_elapsed
-------------------------------------------------------------------------------------------------+----------------------------+---------------+----------------
                                                                              Execute CQL3 query | 2017-03-29 10:28:27.342000 | 172.31.34.179 |              0
                   Parsing select * FROM testtomb WHERE name = 'Dhaval45'; [SharedPool-Worker-2] | 2017-03-29 10:28:27.342000 | 172.31.34.179 |             64
                                                       Preparing statement [SharedPool-Worker-2] | 2017-03-29 10:28:27.342000 | 172.31.34.179 |            101
                              Executing single-partition query on testtomb [SharedPool-Worker-3] | 2017-03-29 10:28:27.342000 | 172.31.34.179 |            210
                                              Acquiring sstable references [SharedPool-Worker-3] | 2017-03-29 10:28:27.342000 | 172.31.34.179 |            223
 Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-3] | 2017-03-29 10:28:27.342000 | 172.31.34.179 |            243
                                 Merged data from memtables and 0 sstables [SharedPool-Worker-3] | 2017-03-29 10:28:27.342000 | 172.31.34.179 |            288
                                         Read 2 live and 0 tombstone cells [SharedPool-Worker-3] | 2017-03-29 10:28:27.342001 | 172.31.34.179 |            310
                                 Merged data from memtables and 0 sstables [SharedPool-Worker-3] | 2017-03-29 10:28:27.342001 | 172.31.34.179 |            323
                                                                                Request complete | 2017-03-29 10:28:27.342385 | 172.31.34.179 |            385

这是我查询已执行删除查询的字段时的日志。最初，用户Dhaval15的名字是“aaaa”，然后是单元格aaa。然后再次对同一用户执行选择查询给了我这个日志。

       activity                                                                                        | timestamp                  | source        | source_elapsed
-------------------------------------------------------------------------------------------------+----------------------------+---------------+----------------
                                                                              Execute CQL3 query | 2017-03-29 10:35:18.581000 | 172.31.34.179 |              0
                   Parsing select * FROM testtomb WHERE name = 'Dhaval15'; [SharedPool-Worker-1] | 2017-03-29 10:35:18.581000 | 172.31.34.179 |             65
                                                       Preparing statement [SharedPool-Worker-1] | 2017-03-29 10:35:18.581000 | 172.31.34.179 |            113
                              Executing single-partition query on testtomb [SharedPool-Worker-3] | 2017-03-29 10:35:18.581000 | 172.31.34.179 |            223
                                              Acquiring sstable references [SharedPool-Worker-3] | 2017-03-29 10:35:18.581000 | 172.31.34.179 |            235
 Skipped 0/0 non-slice-intersecting sstables, included 0 due to tombstones [SharedPool-Worker-3] | 2017-03-29 10:35:18.581000 | 172.31.34.179 |            256
                                 Merged data from memtables and 0 sstables [SharedPool-Worker-3] | 2017-03-29 10:35:18.581001 | 172.31.34.179 |            305
                                         Read 1 live and 1 tombstone cells [SharedPool-Worker-3] | 2017-03-29 10:35:18.581001 | 172.31.34.179 |            338
                                 Merged data from memtables and 0 sstables [SharedPool-Worker-3] | 2017-03-29 10:35:18.581001 | 172.31.34.179 |            351
                                                                                Request complete | 2017-03-29 10:35:18.581430 | 172.31.34.179 |            430

Answer 1

在 Cassandra 中，即使您不执行删除查询，当您插入空值时也会创建墓碑。

墓碑会消耗空间。当您执行选择查询时，cassandra 需要按逻辑删除来过滤数据。如果生成巨大的墓碑，您的选择查询性能将会降低。

由于墓碑巨大且ALLOW FILTERING，您的查询失败。不要在生产中使用ALLOW FILTERING。这是非常昂贵的。当您在不指定分区键的情况下执行查询时，Cassandra 需要扫描所有节点的所有行。

将您的数据模型更改为如下所示:

CREATE TABLE my_table (
    year int,
    month int,
    date timeuuid,
    email text,
    firstname text,
    lastname text,
    mobile text,
    value float,
    PRIMARY KEY ((year, month), date)
);

您可以在此处指定日期中的年和月摘录。
现在您可以通过指定分区键进行查询:

SELECT * FROM my_table WHERE year = 2017 AND month = 03 AND date >= minTimeuuid('2017-03-25 00:00:00+0000') AND date <= minTimeuuid('2017-03-28 23:59:59+0000') ;

这将非常有效地返回结果并且不会失败。

如果您需要查询名字和姓氏，请为其创建索引

CREATE INDEX index_firstname ON my_table (firstname) ;
CREATE INDEX index_lastname ON my_table (lastname) ;

然后您可以使用名字或姓氏进行查询

SELECT * FROM my_table WHERE firstname = 'ashraful' ;
SELECT * FROM my_table WHERE lastname  = 'islam' ;

由于高基数问题，我没有在电子邮件和电话上创建索引。相反，创建物化 View 或另一个表以通过电话或电子邮件进行查询

CREATE MATERIALIZED VIEW mview_mobile AS
    SELECT *
    FROM my_table
    WHERE mobile IS NOT NULL AND year IS NOT NULL AND month IS NOT NULL AND date IS NOT NULL
    PRIMARY KEY (mobile, year, month, date);


CREATE MATERIALIZED VIEW mview_email AS
        SELECT *
        FROM my_table
        WHERE email IS NOT NULL AND year IS NOT NULL AND month IS NOT NULL AND date IS NOT NULL
        PRIMARY KEY (email, year, month, date);

现在您可以通过电话或邮件查询

SELECT * FROM mview_mobile WHERE mobile = '018..';
SELECT * FROM mview_email WHERE email = 'ashraful@...';

有关 Cassandra 墓碑的更多信息:http://thelastpickle.com/blog/2016/07/27/about-deletes-and-tombstones.html