[YSQL] Batch FK trigger checks #2951
Comments
24 tasks
|
While executing the newer TPCC code with all the batching added to the NewOrder transaction we noticed that the latency for the transaction was unusually high {500ms}. The latency for this operation is about 170 ms.
Do note that the reads in step 3 are not parallel, rather they happen one after the other: Batching the reads and performing them in parallel will drop about 120 ms from the entire operation. |
d-uspenskiy
added a commit
that referenced
this issue
Oct 11, 2020
Summary: Vanilla postgres processes triggers one by one. Each foreign key trigger reads individual row from the referenced table. By knowing whole list of foreign key that should be checked it is possible to read multiple rows at a time and save it in local cache to reduce number of read RPC. Solution: - In the `AfterTriggerSaveEvent` function YB calculates `ybctid` of referenced row and save it in foreign key intent cache in `PgSession` - When postgres process each trigger YB checks `ybctid` of referenced row in foreign key cache in `PgSession` - In case no entry found YB initiates read operation of requested `ybctid` and other `ybctids` belonging to same table from `PgSession` foreign key intent cache **Note:** `ybctid` of row in referenced table is built from reference table tuple. In case these tables has at least one argument with different type (`int` vs `int8` for example) `ybctid` will not be built and trigger will be processed as usual without using the cache. Speedup estimation, `release` build, `RF=1` cluster ``` CREATE TABLE parent(k INT PRIMARY KEY); CREATE TABLE child(k INT PRIMARY KEY, v INT REFERENCES parent(k)); INSERT INTO parent SELECT s FROM generate_series(1, 100000) AS s; \timing INSERT INTO child SELECT s, s FROM generate_series(1, 100000) AS s; ``` Before the fix ``` Time: 37335.535 ms (00:37.336) ``` After the fix ``` Time: 2656.684 ms (00:02.657) ``` **Additional changes:** To reduce multiple copying of `ybctid` from PgGate layer to postgres layer and back the `YBCPgYBTupleIdDescriptor` class is introduced. It encapsulates all the data required to build `ybctid` on PgGate side. Using this class instead of `ybctid` in functions: ``` YBCPgForeignKeyReferenceCacheDelete YBCForeignKeyReferenceExists YBCForeignKeyReferenceIntent ``` Allows to build `ybctid` string in PgGate layer and move it to destination function in `PgSession` object without any copying. Test Plan: New test cases are introduced ``` ./yb_build.sh --java-test org.yb.pgsql.TestPgForeignKey ./yb_build.sh --java-test org.yb.pgsql.TestPgForeignKeyBatching ``` **Note:** New test case`org.yb.pgsql.TestPgForeignKey#testHighConcurrency` fails due to inconsistent read described it the following issue #5954 Workaround with a force flushing of buffered operations is implemented and enabled by default. The following command can be used to run test without workaround ``` export FLAGS_TEST_disable_fk_check_force_flush=1; ./yb_build.sh --java-test org.yb.pgsql.TestPgForeignKey#testHighConcurrency ``` Reviewers: alex, sudheer, mihnea Reviewed By: sudheer Subscribers: zyu, yql Differential Revision: https://phabricator.dev.yugabyte.com/D9337
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
If a table being written to has a FK reference to another table, then the FK check is done as an after row trigger. After we add batching for updates or deletes in #2934 , then we should also batch FK checks for those rows.
For example, if we update 100 rows, then there will be 100
SELECT 1 from base_table WHERE id=x FOR KEY SHAREqueries sent to docDB. We should batch these queries.The text was updated successfully, but these errors were encountered: