storage: Performance degradation caused by kv tombstones

[a-robinson]

If a workload deletes or updates rows frequently and also scans over those rows, the tombstones left behind by our MVCC cause the scans to get slower and slower over time. This is surprising to users and can render workloads that are perfectly reasonable on a new database impractically slow on a database that's been under load for 24 hours.

This was hit by a user whose workload involved two tables scans in a three-way join in #17075, and has received some previous discussion on our forum. Consider this a tracking issue for the user-visible problem.

Jira issue: CRDB-6045

[tbg]

#16252 as well and I remember there was another instance (but can't find it now). Suffice to say it's a relatively frequent problem.

[petermattis]

An idea mentioned by @tschottdorf is to move historical data to a separate column family. Concretely, we could have a background process that periodically moved old versions and deletion tombstones to a separate column family. This could be done independently on each replica as the logical data would remain unchanged. Each replica would maintain a timestamp that would control whether the separate column family needs to be accessed or not.

[bdarnell]

I'm tagging this issue for 1.3 because it keeps coming up and I want to make sure it gets into the next prioritization cycle. (If we could squeeze one more thing into 1.2, this would be a candidate)

I'd also like to propose index-level zone configs as a potential solution for this. Normally, the solution to slow reads is to add an index, but that doesn't work here because the index itself is getting filled up with garbage. An index with lower GC retention would work. We'd just need to ensure that the query planner takes this into account and won't use an index that may have been GC'd at the query's timestamp.

[petermattis]

There might be other optimizations possible here. Index-level zone configs could possibly fall out of the partitioning work scheduled for 1.2.

Cc @danhhz and @benesch

[benesch]

As part of the partitioning work, I expect we'll update our usage of zone configs internally to be based on byte ranges instead of database/table IDs. Targeting indexes falls out naturally from that work. I imagine the hard part is actually the user interface; the echo 'SOME YAML JUNK' | ./cockroach zone -f - CLI gets clunkier and clunkier with every knob we add.

I heard @BramGruneir might have a zone config redesign in the pipeline?

[petermattis]

./config zone set -f - myTable@myIndex seems natural.

@BramGruneir is interested in providing SQL statements to set&get zone configs. I'd imagine this would be some sort of ALTER TABLE syntax.

[benesch]

What if your index is partitioned, though? Then you might end up with

$ ./config zone set -f - db.tbl@idx1.partition1

and there'd be four potential zone configs that could take effect (db, tbl, idx1, partition1). We'll need to decide if they inherit or not. If they don't inherit it's simpler to understand what's happening—the first zone config that exists is used verbatim—but without inheritance updating a table-wide setting (say, replication factor) requires updating every partition zone config for every index. That could mean manually running several dozen cockroach zone commands that install zone configs that only differ by a few bytes.

Anyway, my point is that if partitioning doesn't motivate us to redesign the zone config interface, then partitioning + index zone configs should definitely motivate us.

[petermattis]

but without inheritance updating a table-wide setting (say, replication factor) requires updating every partition zone config for every index. That could mean manually running several dozen cockroach zone commands that install zone configs that only differ by a few bytes.

cockroach zone set could be extended to set a specific field (e.g. the replication factor) on all zones matching a pattern. But I see your point that some rethinking is likely in order here: we'll either need to extend the tools for manipulating zones or extend the zone config semantics.

[petermattis]

@spencerkimball This was the issue I mentioned earlier.

[vivekmenezes]

It looks like SELECT COUNT(*) can take 1400ms even on a an empty table even after lowering the TTL and waiting several hours after the TTL

[spencerkimball]

Could be scanning over thousands of empty ranges since we don't merge.

…

On Thu, Dec 7, 2017 at 4:56 PM vivekmenezes ***@***.***> wrote: It looks like SELECT COUNT(*) can take 1400ms even on a an empty table even after lowering the TTL and waiting several hours after the TTL — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#17229 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AF3MTQW3OtKH-I4LFWW1KI3fgdHM1AaOks5s-F8GgaJpZM4Oj9K4> .

[bdarnell]

In the report of 1400ms for a empty table (or nearly empty: the stats I have show 106KB of live data), there were 25 ranges (with all the live data in the last one). 17 of them were empty; the other 8 were awaiting GC with 32MB of non-live data.

So I think in this case, the problem is not a bunch of empty ranges, it's that the GC queue appears to have fallen behind and hasn't deleted all this data that is past its TTL. We've made some improvements here recently (this test was using version 1.1.3; at least some of the GC queue improvements will be in 1.1.4).

In the longer term, though, the ever-growing number of ranges due to the lack of merging is the bigger problem for this workload. Insertions to this table (with a unique_id primary key) are producing splits that are five minutes apart.