Unclear documentation for "transaction is too large to complete; try splitting into pieces"


(Sean Houghton) #1

I’m running in to the transaction is too large to complete; try splitting into pieces error. I initially tried to fix the problem as suggested in the documentation by breaking a large INSERT INTO ... SELECT FROM statement into multiple statements inside a single transaction. However, this doesn’t resolve the issue and I still get the same error even if each statement is only a few hundred bytes. After reading the documentation more carefully I noticed an important detail: “manually page through the data you want to insert using separate transactions” (emphasis mine).

So it sounds like I have to break apart the transaction into multiple transactions rather than break it up into multiple statements within the transaction. This should probably be spelled out much more clearly in the documentation because this is the type of error that you would only encounter in the wild after deploying something. I only found it after running some load tests with very large input data.

This is complicated further by the claims made in the same section which the average reader would incorrectly assume applied to this problem:

“A single statement can perform at most 64MiB of combined updates”
and
“There’s only a size limit, and it applies independently to each statement”

https://www.cockroachlabs.com/docs/stable/known-limitations.html#write-and-update-limits-for-a-single-statement

I’m enjoying using CockroachDB but the size limitations are challenging to deal with.


#2

@seanhoughton Great points. Definitely something we can improve.

To help get you unstuck: are you trying to import data into your cluster? If so, you might be able to use IMPORT to get around the limits you’re running into.

Let us know what we can do to help you out. I’ve added a GitHub issue for the problem, as well.

Best,
Sean


(Rich Loveland) #3

@seanhoughton here is a small Python/SQLAlchemy code sample (that is part of a docs PR going through review now) that shows a pattern for breaking a possibly “too large” transaction into multiple smaller transactions.

Hopefully the code sample linked above will be useful to you or the next person reading this, at least until we can address the larger issue you pointed out.

For reference, the max transaction size is 256 KiB and is controlled by the kv.transaction.max_intents_bytes cluster setting, but as noted on that page

Many cluster settings are intended for tuning CockroachDB internals. Before changing these settings, we strongly encourage you to discuss your goals with Cockroach Labs; otherwise, you use them at your own risk.

Sorry for your trouble finding what you needed in the docs, we look forward to addressing that!


(Sean Houghton) #4

Thanks for the clarification. Are you saying that my problem is not the amount of data that was changed as part of the transaction (the 64MB limit) but actually the size of the transaction representation itself?

Doing the pagination outside of a transaction is subject to race conditions because rows could be added or removed while in the middle of the pagination. In the short term I’ll probably just do a select to copy the data to the client and page through the data on the client. This guarantees that my windows are always adjacent.


(Rich Loveland) #5

Are you saying that my problem is not the amount of data that was changed as part of the transaction (the 64MB limit) but actually the size of the transaction representation itself?

Interestingly, no.

I shared your question with our engineering team and got the following answer:

That error means that the transaction has encountered a conflict with another transaction that will force it to restart.
(If it did not have to restart, it would be allowed to complete even if it exceeded the limit.)
So even if the limit were increased, the transaction would have to restart, and would be likely to be stuck in an endless cycle of restarts.
In other words, the transaction has to be made smaller to reduce the chance and cost of restarts, not because it exceeds this particular limit.

I have added this info to the issue @Sean already filed to make sure it goes into the official docs.


(Andrei Matei) #6

The transaction is indeed likely too “large” for its own good.
More technically, the limitation you’re running into is about the data that has been read by the transaction, not the data being written. The transaction likely performed a bunch of disparate reads, and separately some writes, and has gotten in some sort of conflict with another transaction which might cause it to restart. To try avoiding the restart, crdb attempts to keep track of the key ranges that have been read. In the case of this transaction, we ran out of memory for keeping track of those reads (and we have also detected a conflict).
In the next version, we’ll likely get rid of this error (see https://github.com/cockroachdb/cockroach/pull/30074); the client will just receive a usual retriable error.
In the meantime, the memory that can be used for the data structure I’m telling you about is governed by the kv.transaction.max_refresh_spans_bytes cluster setting. You can increase it with set cluster setting kv.transaction.max_refresh_spans_bytes = <foo>.


(Sean Houghton) #7

Thanks for the detailed response @andrei, the github thread was interesting. I think I can safely break up the query into batches but it would be great to have it work like the rest of the retriable errors.