[YSQL] Use session flag to set transactional writes for index inserts #11266
Assignees
Labels
area/ysql
Yugabyte SQL (YSQL)
Comments
emhna
added a commit
that referenced
this issue
Feb 8, 2022
…ndex inserts Summary: Today, only index backfill uses the non transactional write for index inserts. In order to allow non transactional writes for regular writes to index tables, we will use the `yb_disable_transactional_writes` session flag which was initially created to disable transactional writes on regular tables for inserts to the main table: https://phabricator.dev.yugabyte.com/D13703 Note using this session flag could lead to state inconsistency as follows: Prior to using this session variable, a distributed transaction write lock was used to write to both main table and index table. But by disabling this distributed transaction write, the writes to the main table and index become independent writes as they are written directly to RocksDB. So when there’s a connection abort or crash in the middle of the write, there could be a state inconsistency between the main table and index table, like some rows may appear in one but not the other. User would have to truncate and restart in such scenarios. Despite such pitfall, we are extending this flag onto index tables to speed up the bulk loading of data such as when using the COPY command. Also, note that this change explicitly covers the write use case for the insert writes like COPY command and no other write use cases like delete or update. Test Plan: On a test of 1 GB file, when the `yb_disable_transactional_writes` flag was set to false, the COPY command on indexed table took around 8 hours. On the same condition, when the `yb_disable_transactional_writes` flag was set to true, the COPY took around 4 hours. When the difference was calculated in seconds, there was 91% growth in speed when the session flag was set to true. This performance improvement aligns with the latency improvement seen on regular tables tested in the `D13703` diff. Modified existing unit test. Reviewers: jason, smishra Reviewed By: jason, smishra Subscribers: yql Differential Revision: https://phabricator.dev.yugabyte.com/D15159
jayant07-yb
pushed a commit
to jayant07-yb/yugabyte-db
that referenced
this issue
Mar 8, 2022
…es for index inserts Summary: Today, only index backfill uses the non transactional write for index inserts. In order to allow non transactional writes for regular writes to index tables, we will use the `yb_disable_transactional_writes` session flag which was initially created to disable transactional writes on regular tables for inserts to the main table: https://phabricator.dev.yugabyte.com/D13703 Note using this session flag could lead to state inconsistency as follows: Prior to using this session variable, a distributed transaction write lock was used to write to both main table and index table. But by disabling this distributed transaction write, the writes to the main table and index become independent writes as they are written directly to RocksDB. So when there’s a connection abort or crash in the middle of the write, there could be a state inconsistency between the main table and index table, like some rows may appear in one but not the other. User would have to truncate and restart in such scenarios. Despite such pitfall, we are extending this flag onto index tables to speed up the bulk loading of data such as when using the COPY command. Also, note that this change explicitly covers the write use case for the insert writes like COPY command and no other write use cases like delete or update. Test Plan: On a test of 1 GB file, when the `yb_disable_transactional_writes` flag was set to false, the COPY command on indexed table took around 8 hours. On the same condition, when the `yb_disable_transactional_writes` flag was set to true, the COPY took around 4 hours. When the difference was calculated in seconds, there was 91% growth in speed when the session flag was set to true. This performance improvement aligns with the latency improvement seen on regular tables tested in the `D13703` diff. Modified existing unit test. Reviewers: jason, smishra Reviewed By: jason, smishra Subscribers: yql Differential Revision: https://phabricator.dev.yugabyte.com/D15159
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Description
Today,
backfill_write_timeis used to determine whether the distributed transactional write should be used which is only set during index backfills.In order to disable the distributed transactional writes for regular inserts to index, we will use the
yb_disable_transactional_writessession flag.Using this flag will help improve the latency performance of bulk data loading for index tables such as when COPY command is used which goes into the insert write path (not delete or update).
The text was updated successfully, but these errors were encountered: