DELETE FROM ... [returning nothing] crashes a node

[AnyCPU]

A cluster has 3 nodes running on Cockroachdb 1.0.5 (Linux, 64bit)
A db has about 195 millions of records.
Scheme is

CREATE TABLE stats (
	numa STRING(32) NULL,
	bytes INT NULL,
	amount DECIMAL NULL,
	ts TIMESTAMP NULL,
	da INT NULL,
	INDEX idx_stats_ts (ts ASC) STORING (numa, bytes, amount, da),
	FAMILY "primary" (numa, bytes, amount, ts, da, rowid)
)

The db has data of an one month.
I want to delete data starting from second day of month, so
I run either delete from stats where ts > '2017-01-01 23:59:59'::timestamp; or delete from stats where ts > '2017-01-01 23:59:59'::timestamp returning nothing;.
I use the cockroach sql --insecure --host = ... command to run my query.
After some minutes an used node dies.
A db driver returns - bad connection.
And there are a lot of messages like that context canceled while in command queue: ResolveIntent... in the log.

All nodes have free space.

Do I have to delete data by chunks?

Thanks.

[AnyCPU]

I cannot even remove a half a day.

[AnyCPU]

I have tried to remove data for an one hour but I got
pq: kv/txn_coord_sender.go:428: transaction is too large to commit: 478692 intents

[AnyCPU]

It is possible to remove data by ten-minutes chunks :-(

[tbg]

Hey @AnyCPU, you're running into the fact that as of the time of writing, support for large writing transactions is not ideal in CockroachDB. You've pretty much experienced all the problems: operations never succeeding, nodes running into memory troubles (though that one's somewhat unexpected), or failing with the "too large to commit" error. While deleting in small chunks is currently the best option, rest assured that we're working on improving this (see the issue above). We have workarounds in place for the case in which you want to drop/truncate the whole table (which we can do more efficiently by essentially swapping out the table with a new one), but that's clearly not going to help you.

Tracking issue: #15849

[AnyCPU]

Thank you @tschottdorf

[tbg]

Despite many improvements, this is still an issue in master at the time of writing, though we now have a test that reproduces this reliably: #22876

[tbg]

@spencerkimball let's move the discussion from #22876 here. I agree that the easiest way to diagnose this is to get heap profiles.

@petermattis if I were to introduce such a facility into roachtest, how do you think I should do it? I think it would be reasonable to write a little standalone program (or bash script for starters) that does little except periodically store heap dumps to artifacts on all relevant nodes (so that we can just run it in tests that want it, like a load gen). That way, it won't get in the way of perf tests. To get fancy, we could let the program assert on the heap dump, though I'd like to punt on that.

[petermattis]

For heap profiles, you can already do roachprod start -e COCKROACH_MEMPROF_INTERVAL=1m .... Did you mean something different by "heap dumps"?

[petermattis]

Also, when diagnosing, it is easiest to create a cluster using roachprod create and then run the test using roachtest run -c <existing-cluster>.

[tbg]

For heap profiles, you can already do roachprod start -e COCKROACH_MEMPROF_INTERVAL=1m .... Did you mean something different by "heap dumps"?

No, that's what I mean. Forgot about the env var, thanks!

[tbg]

With #22991, this seems to be making steady progress:

Remains to be seen whether it manages to commit.

[tbg]

Ok, as expected something did go wrong. I think after approximately 10 minutes we run into the timestamp cache and catch a retry and stagnate from then on:

/debug/requests shows little of use, the sql trace is huge since this line is extremely chatty and, well, because we're deleting ten million rows.

I think what you'd want here is that the refresh machinery realizes that nothing has changed and so the restart can be hidden. But something is clearly going wrong but it's not exactly clear to me what.

@spencerkimball, would you mind taking a look? This is really easy to run, just

roachprod create -n 9 spencer-test
roachtest run -c spencer-test drop

[tbg]

Reopening as there are still problems with such deletions. They should either fail gracefully or succeed; not hang indefinitely.

[tbg]

Reopening to verify fix.

[tbg]

Ah, already done: #23258 (comment)