INCREMENTAL PARSING QUERIES -
Steps With dsi_compile_enable (and incremental parsing, not set to on) -- According to RS Tuning Guide ( By Jeff Tallman & Chris Brown, Page 249)
a. DSI uses DSI SQT cache to parse the transactions
b. Parsed results stored in PRS cache (defined with sqt_max_prs_size). This uses a lot of SQT cache and PRS cache
c. The compilation database (CDB) is created from scanning the parsed commands
d. DSI EXEC uses the pre-parsed commands to reduce duplicate parsing and reduce overall parsing time
Steps With dsi_compile_enable (and incremental parsing, set to on) -- According to RS Tuning Guide (By Jeff Tallman & Chris Brown, Page 249)
a. DSI-S reads transaction as before
b. DSI-S parses only beginning of record for table & operation
c. Uses this for transaction profiling
d. Reduces SQT cache usage
e. DSIEXEC reads commands from SQT cache
f. DSIEXEC completes the parsing of values
SAP Sysadmin Guide says - Because the SQT cache stores only the first part of the command, there is less memory consumption, leaving more memory available for effective use of the SQM cache and SQM command cache, inbound and outbound direct command replication, and metadata reduction.
a. To clarify, is the SQT used for storing parsed results or not ? Are the parsed results stored exclusively in PRS cache or distributed between SQT & PRS cache ?
b. When it says "Because the SQT cache stores only the first part of the command" - is is referring to SQT storing only part of the parsed result set or does it refer to SQT storing only part of the actual command ( that is needed for basic/minimum parsing required by DSI) ? If referring to "part of parsed result set" it would mean SQT indeed stores the parsed results. If referring to "part of the actual command", then DSIEXEC will need to do too many physical reads to read the full command if Page cache is small and DSI is behind.
c. Again, can somebody please explain as to how memory consumption reduces with "incremental parsing" ? With incremental parsing, DSIEXEC still needs to parse the commands. So again, the commands will be picked from DSI SQT and results stored in PRS cache. How does this increase efficiency or reduce cache usage ? As i understand, We are still parsing at least once (by DSI EXEC instead of DSI). Is it only helpful with Multiple and Parallel DSI ?
d. Also SQM Cache (which i believe is page cache), Command Cache and DSI/SQT Cache are 3 different silo's of memory. Reducing usage in one of them does not help the other cache (so why the wording - "leaving more memory available for effective use of the SQM cache and SQM command cache"). This is from a version where smart memory control did not even exist.
e. Does HVAR use command cache or not ? If yes, what is the purpose of PRS cache ( aren't both supposed to contain the command structure/ parsed data) ?
LARGE TRANSACTION QUERIES -
From (Replication Whitepaper by Jeff Tallman, Page 127)
The most common problems with the SQT are associated with 1) large transactions; and 2) slow SQT readers (i.e. DIST or DSI). The first deals with the classic 10,000 row delete. If the SQT attempted to cache all of the statements for such a delete in its memory, it would quickly be exhausted. Consequently, when a large transaction is encountered, the SQT simply discards all of its statements and merely keeps the transaction structure in the appropriate list. However, this means that in order for the transaction to be passed to the SQT reader, the SQT must go back to the beginning of the transaction and physically rescan the disk. In addition to the slow down of simply doing the physical i/o, it effectively pauses the scanning where the SQT had gotten to until that transaction is fully read back off disk and sent to the DIST, etc. It also impacts Replication Agent performance as this likely will involve a large number of read requests to refetch all of the same blocks – adding to the workload of the SQM that is busy trying to write.
a. To confirm my understanding - Large transactions ( which either fills SQT cache or are beyond limits defined by dsi_large_xact_size are removed from SQT cache)- leading to group closures according to grouping rule of closing a group is next/current transaction is on disk.
b. So the penalty is 1. "group closure" 2. "Reread from disk i.e. physical IO" iii. "Re-read from disk causes interference with SQM again". Any other penalties involved with large transaction ?
OTHER QUERIES -
a. We have used STATEMENT cache on WS sites with impressive performance (vs no statement cache at Replicate Sites). Should we remove STATEMENT cache with DSI_COMPILE_ENABLE and dsi_bulk_copy set to on ?
b. Given that executor command cache and async parser are not compatible. Is there something that you would prefer over the other ?
c. Due to KBA 2276200, we are setting all features of async_parser except "ascii_pack_ibq" to on (by setting individually,
exec_prs_num_threads, cmd_direct_eplicate,dist_cmd_direct_replicate to on). Does it seem like an issue to have binary packing to on but exec_prs_num_threads and cmd_direct_replicate to on ?
regards,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.