Environment

YugabyteDB all  version 

Issue

In a complex scenario involving 11 interconnected databases in a microservices architecture, a data recovery task is necessary due to a restoration error resulting in missing data.

For example: While attempting to restore data from the 26th of October, a typo resulted in one database being restored from the 26th of September instead. This created a gap in the live database from September 26th to October 26th. To rectify this, the plan is to restore the October 26th backup in a new universe and identify missing or changed data. However, limitations in query capabilities, such as YBB's 250MB data retrieval limit, and the presence of JSON columns in tables, complicate the process.
Get the following error when run vanilla select

screeningtest=# CREATE TABLE backup_latest_response AS 
screeningtest-# SELECT * FROM latest_response 
screeningtest-# WHERE created_timestamp >= '2023-09-27 00:00:00';
ERROR: [Invalid argument (yb/tablet/preparer.cc:300): Operation replicate msg size (263766865) exceeds limit of leader side single op size (254017536)]

This is hitting the rpc_max_message_size and failing.

Resolution:

To resolve this issue we have a workaround by reducing the number of rows returned in each rpc message so that the rpc_max_message_size limit is not reached.

YugabyteDB version 2.18.X and below

• This can be done by setting yb_prefetch_rows.
• Find the value of rpc_max_message_size, usually 250MB
• Find the average size of your rows. (add a margin of error (10%)
• Set yb_prefetch_rows to divide 250MB by the average size of a row (yb_prefetch_rows defaults to 1024)
• Add GFlag yb_prefetch_rows=value and rolling restart the universe

YugabyteDB version 2.19.X

• This can be done by setting yb_fetch_row_limit.
• Find the value of rpc_max_message_size, usually 250MB
• Find the average size of your rows.(add a margin of error (10%)
• Set yb_fetch_row_limit to divide 250MB by the average size of a row (yb_fetch_row_limit defaults to 1024)
• Add GFlag yb_fetch_row_limit=value or set value from ysqlsh

YugabyteDB version 2.20.X

• This can be done by setting yb_fetch_row_size.
• Find the value of rpc_max_message_size, usually 250MB
• Set yb_fetch_row_limit to divide rpc_max_message_size - rpc overhead (10MB)
• Add GFlag yb_fetch_row_limit=value and rolling restart the universe