Backfill Index Throttling for YSQL #7889
Comments
|
YCQL has if (FLAGS_backfill_index_rate_rows_per_sec > 0) {
auto duration_since_last_batch = MonoDelta(now - *last_flushed_at);
auto expected_duration_ms = MonoDelta::FromMilliseconds(
index_requests->size() * 1000 / FLAGS_backfill_index_rate_rows_per_sec);
DVLOG(3) << "Duration since last batch " << duration_since_last_batch
<< " expected duration " << expected_duration_ms
<< " extra time so sleep: " << expected_duration_ms - duration_since_last_batch;
if (duration_since_last_batch < expected_duration_ms) {
SleepFor(expected_duration_ms - duration_since_last_batch);
}
}This is not in the YSQL code path. If YSQL wants throttling, either
Those are the two ideas that come to mind. |
|
Talking with Bogdan here are some examples of the cpu utilization of both the postgres and tserver processes. First picture is the cluster at steady state. The second is the cluster doing an index backfill with num_concurrent_backfills_allowed set to 2. The third is from when we ran the backfill with num_concurrent_backfills_allowed set to 1. The final screenshot is from the metrics page in YB platform of the 2 runs. Note the 2 runs were at 15:40 mark and 18:00 mark. The blip in the middle can be ignored. |
m-iancu
added a commit
that referenced
this issue
Aug 19, 2021
Summary: This is basically @nocaway 's diff with minor updates, rebased to master, and tested against the follow-up tserver changes using this infrastructure to set up pagination and throttling support for YSQL index backfill. It enhances the BACKFILL command with a new clause containing a backfill instruction (encoded as an opaque string). The backfill instruction defines how many rows to process (backfill) and which row key to start from (i.e. where the previous backfill call left off). This way the tserver can configure both the per-backfill-request work (number of rows per backfill call), as well as the overall backfill rate (throttle the issuing of backfill calls). Note that the grammar change is safe w.r.t. rolling upgrades because BACKFILL command is only used internally by the tserver to a local Postgres instance. #### Detailed changes **(1) Backfill Process Description.** - A **//requesting//** tablet-server sends out a **BACKFILL** request with an `opaque string` for backfill instruction. No code-layer needs to read this string except tablet-server - Proxy process the **BACKFILL** statement by sending a scan request together with the `opaque string`. - A **//processing//** tablet-server will need to read the `opaque string` to know when to stop scanning. Postgres / Proxy will NOT process or care about this LIMIT. - When finishing and replying to scanning request, the **//processing//** tablet-server must send back an `opaque string` whose contents should have been updated for the followup backfill instruction. - Postgres / Proxy layer will forward the result `opaque string` to the //**requesting**// tablet-server by replying to the BACKFILL request - The **//requesting//** tablet-server reads the `opaque string` to decide whether or not to continue the backfill process and how to execute it. **(2) BACKFILL Statement Syntax** ``` BACKFILL <index oid list> [ WITH <opaque instruction string> ] READ TIME <time> PARTITION <bounds> ``` **(3) BACKFILL Processing and Execution** - Postgres parses `BACKFILL` statement. - Postgres sends query to scan the primary table. - Postgres insert the fetched data to index. - When scanning and inserting Postgres passes IN/OUT parameters for the BACKFILLing operation. **(4) BACKFILL Input / Output** - IN parameters are sent from Postgres to Yugabyte to provide further information on how to execute BACKFILL statement. This is already done. - This diff add an OUT parameter to send the execution result from Yugabyte to Postgres. BACKFILL spec from tablet server will be written to this OUT param, which will be returned-value of the BACKFILL statement. ``` typedef struct PgExecOutParamValue { const char *bfoutput = NULL; // The following parameters are not yet used. // Detailing execution status in yugabyte. const char *status = NULL; int64_t status_code = 0; } YBCPgExecOutParamValue; typedef struct PgExecParameters { ..... PgExecOutParam out_param = NULL; } ``` **(5) Implementation for backfill instruction.** - The aforementioned `opaque string` is a serialized string of a protobuf for the backfill instruction. - Example ``` // Backfill instruction. message PgsqlBackfillSpecPB { // Limit for each backfill batch. "-1" means no limit. optional int64 limit = 1 [default = -1]; // Accumulated counter is used to check if the number of scanned rows has reached limit. optional int64 count = 2 [default = 0]; // The next row key to read for backfill. Defined/used similar to how it is used in paging state. // i.e. The row key (SubDocKey = [DocKey + HybridTimestamp]) of the next row to read. optional bytes next_row_key = 3; } ``` **(6) Scanning Implementation** - Tablet server Serialize(PgsqlBackfillSpecPB) into hex string and send it together with the BACKFILL statement. See file `tablet.cc` - Backfill instruction string will be sent back & forth between tablet-server and proxy-server during scanning. The read request and response will have a new entry for the `opaque string`. Only tablet-server needs to read this string. ``` message PgsqlReadRequestPB { .... // Instruction for BACKFILL operation from master/table server. // This is the serialized-string of "message PgsqlBackfillSpecPB". optional bytes backfill_spec = 31; } message PgsqlResponsePB { .... optional bytes backfill_spec = 13; } ``` - The PgsqlBackfillSpecPB string is not the same as PgsqlPagingStatePB. Paging state is cursor that is advanced every 1024 rows by default and is to be read by all code-layers while backfill instruction string is created, interpreted, and processed by only tablet-server, and it represents the state of a backfill process and not the state of scanning. - At the end of scanning, either end-of-tablet or limit-is-reached, the server will need to send appropriate PgsqlBackfillSpecPB as a serialized-hex-string for the next batch of backfill. And postgres will forward this string back to the server who requests the BACKFILL. **(7) Writing Implementation ** This remains the same. Test Plan: Tested together with https://phabricator.dev.yugabyte.com/D12425 ([#7889, #5326] YBASE: Implement chunking/throttling in Tablet::BackfillIndexForYSQL) Reviewers: neil, amitanand, jason Reviewed By: jason Subscribers: yql Differential Revision: https://phabricator.dev.yugabyte.com/D12435
amitanandaiyer
added a commit
that referenced
this issue
Aug 20, 2021
…llIndexForYSQL Summary: Depends on https://phabricator.dev.yugabyte.com/D12435 Implement chunking/throttling in Tablet::BackfillIndexForYSQL * At a high level, we loop around in Tablet::BackfillIndexForYSQL to call `BACKFILL INDEX` on postgres until the backfill is done, or we are approaching the deadline. * The loop also throttles/sleeps itself to maintain the specified rate of backfill. Notes regarding rolling-upgrades: * The diff assumes that the TServer issuing the new format BACKFILL INDEX command only issues it to a postgres process, which understands the new format. This holds true, because the postgres process that the TServer talks to is the local postgres process. * Postgres/yb-client will read from the "leader" of the tablet. If the leadership has moved, this may be a different tablet/TServer. In this case, it may read/write from/to a TServer with a different version. In this case, the backfill_spec field is ignored by the server running the older process. This will result in the "old" behavior where the BACKFILL INDEX command will run until the end of the tablet range. Perf notes: Connecting to postgres to run a BACKFILL INDEX command may have some fixed set up costs. So having a very small batch size could be inefficient. More detailed description of the changes: ------------ Docdb: # Implement support for backfill_spec. # Set it accordingly while calling BACKFILL INDEX # Parse/update backfill_spec accordingly while reading pgsql_operation for a backfill read. # Loop around BACKFILL INDEX to implement throttling/progress-in-batches Test Plan: Tests added. ybd --cxx-test pg_index_backfill-test ybd --cxx-test integration-tests_cassandra_cpp_driver-test Reviewers: mihnea, neil, jason Reviewed By: jason Subscribers: bogdan, yql, ybase Differential Revision: https://phabricator.dev.yugabyte.com/D12425
amitanandaiyer
added a commit
that referenced
this issue
Aug 31, 2021
Summary: With ysql backfill being able to perform chunking, we no longer need a high timeout value (which was previously used to backfill the whole tablet in one go). Reduce ysql_index_backfill_rpc_timeout_ms to 1 min. Add a pg-regress test for backfill. Test Plan: ybd --java-test TestPgRegressBackfillIndex Reviewers: mihnea, neil, jason Reviewed By: jason Subscribers: bogdan, yql, ybase Differential Revision: https://phabricator.dev.yugabyte.com/D12670
amitanandaiyer
added a commit
that referenced
this issue
Sep 13, 2021
Summary: Fix assert to account for lowered rpc timeout (in 2fcae00) which may result in more than one backfill RPC per tablet. Test Plan: ybd asan --cxx-test pg_index_backfill-test --gtest_filter PgIndexBackfillTest.Large -n 100 --tp 1 Reviewers: jason Reviewed By: jason Subscribers: ybase Differential Revision: https://phabricator.dev.yugabyte.com/D12951
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
No description provided.
The text was updated successfully, but these errors were encountered: