{"id":1226,"date":"2023-11-19T23:52:03","date_gmt":"2023-11-19T23:52:03","guid":{"rendered":"https:\/\/craftcookcode.com\/?p=1226"},"modified":"2024-05-06T05:18:32","modified_gmt":"2024-05-06T05:18:32","slug":"qlik-replicate-the-million-row-challenge-with","status":"publish","type":"post","link":"https:\/\/craftcookcode.com\/?p=1226","title":{"rendered":"Qlik Replicate – The million row challenge with"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

It seems every new project; the project teams come with new and innovative ways to make my life \u201cinteresting\u201d while managing Qlik Replicate.<\/p>\n

The latest idea they have is to pretty much request that one of our major Qlik Replicate tasks that is sucking data our of a DB2 database and delivering data to AWS S3 be duplicated and deliver pretty much the same data to GCS.<\/p>\n

When I got wind of this proposal it was already too far into the solution design to propose another solution; like copying the data from GCS to AWS S3.\u00a0 So, it was left up to me to go and tell the owners of the DB2 system that there is another massive feed will be built of their database \u2013 basically doing the same thing.<\/p>\n

With some persuasion I could get internal funding to research and implement Log Streaming to consolidate our reads from the DB2 system.\u00a0 Hardware wise this required adding more memory and disk space to the QR servers to handle the log streaming.\u00a0<\/p>\n

As for the Log stream component of the solution, I started looking into the settings.\u00a0 Since we are drawing a lot of data from DB2 \u2013 I am concerned about disk space and what performance impact the compression setting has on the task.<\/p>\n

To test the impact; I set up the following test scenario:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t

The \"Million row\" challenge<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

To test \u2013 I created a very wide table in a test MS-SQL (a database I had access to); which contained integers and varchars.<\/p>\n

With a python script, a million rows were inserted into the table in batches of 10,000.\u00a0 In the end the million rows equated to about 1.3Gb on the database.<\/p>\n

I did ten test runs, increasing the compression in the log stream endpoint with each individual run.<\/p>\n

\u00a0<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

Using Qlik Replicate\u2019s analytics I could then get the metrics for the Log Stream\u2019s task; and a task that is reading from the log stream and outputting the data to a NULL endpoint.<\/p>\n

I also grabbed the file sizes that the log stream created on the server’s drive.<\/p>\n

Here are the results:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

<\/p>\n

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\u00a0<\/th>\nLog Stream File Size (Mb)<\/th>\nLS – CPU %<\/th>\nLS – Memory (MB)<\/th>\nLS – Source latency (sec)<\/th>\nLS – Target latency (sec)<\/th>\nTarget – CPU %<\/th>\nTarget – Memory (MB)<\/th>\nTarget – Source latency (sec)<\/th>\nTarget – Target latency (sec)<\/th>\n<\/tr>\n<\/thead>\n
No compression<\/td>\n1,974<\/td>\n7<\/td>\n134<\/td>\n1<\/td>\n3<\/td>\n4<\/td>\n126<\/td>\n3<\/td>\n4<\/td>\n<\/tr>\n
Level 2<\/td>\n718<\/td>\n15<\/td>\n169<\/td>\n2<\/td>\n4<\/td>\n4<\/td>\n105<\/td>\n3<\/td>\n3<\/td>\n<\/tr>\n
Level 3<\/td>\n690<\/td>\n14<\/td>\n156<\/td>\n0<\/td>\n3<\/td>\n4<\/td>\n118<\/td>\n2<\/td>\n2<\/td>\n<\/tr>\n
Level 4<\/td>\n683<\/td>\n16<\/td>\n155<\/td>\n1<\/td>\n2<\/td>\n6<\/td>\n127<\/td>\n2<\/td>\n2<\/td>\n<\/tr>\n
Level 5<\/td>\n675<\/td>\n21<\/td>\n281<\/td>\n1<\/td>\n5<\/td>\n6<\/td>\n156<\/td>\n3<\/td>\n3<\/td>\n<\/tr>\n
Level 6<\/td>\n665<\/td>\n24<\/td>\n224<\/td>\n1<\/td>\n3<\/td>\n5<\/td>\n176<\/td>\n4<\/td>\n4<\/td>\n<\/tr>\n
Level 7<\/td>\n663<\/td>\n23<\/td>\n182<\/td>\n1<\/td>\n3<\/td>\n4<\/td>\n160<\/td>\n4<\/td>\n4<\/td>\n<\/tr>\n
Level 8<\/td>\n657<\/td>\n38<\/td>\n394<\/td>\n1<\/td>\n9<\/td>\n4<\/td>\n181<\/td>\n9<\/td>\n9<\/td>\n<\/tr>\n
Level 9<\/td>\n657<\/td>\n46<\/td>\n345<\/td>\n1<\/td>\n10<\/td>\n6<\/td>\n189<\/td>\n12<\/td>\n12<\/td>\n<\/tr>\n
\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n

<\/p>\n

<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Some notes on the results<\/h4>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
    \n
  • The run was done on a shared server; so other processes on the server running at the same time could impact the CPU and memory metrics<\/li>\n
  • Analytics only takes a snapshot of the metrics every 5min and the million rows are inserted into the database in less than <10min so we can only get one snapshot of the metrics for the run.\u00a0 Depending where the run was up to; this could impact the metrics<\/li>\n<\/ul>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t\t
    \n\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t

    Conclusions<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t\t
    \n\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t\t\t

    Like most things in IT; there is no magical setting that will cover all scenarios.\u00a0 Everything is a compromise, and the settings will need to be adjusted to meet one\u2019s particular source endpoint, nature of the data being replicated, and the latency can be tolerated to disk space usage.<\/p>\n

    Here is what I learnt.<\/p>\n

      \n
    • If you require absolute minimum latency as possible \u2013 log steaming is not your answer for a couple of reasons.\u00a0\n
        \n
      1. There is an inherited one second delay writing batches from the source system to the log stream.<\/li>\n
      2. All the data is written to disk and then read by the target QR task.\u00a0 This means that the changes do not reside in memory and need to be read from disk.<\/li>\n<\/ol>\n<\/li>\n
      3. Level 2 significantly reduces disk space usage with little compromise to speed and resource usage.<\/li>\n
      4. Level 4 is the best compromise of disk space, speed, and resource usage.\u00a0 Anything above level 4 starts to strain the speed and resource usage with little improvement to disk space.<\/li>\n<\/ul>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
        \n\t\t\t\t\t\t
        \n\t\t\t\t\t
        \n\t\t\t
        \n\t\t\t\t\t\t
        \n\t\t\t\t
        \n\t\t\t\t\t

        Implementing Log streaming plan<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
        \n\t\t\t\t\t\t
        \n\t\t\t\t\t
        \n\t\t\t
        \n\t\t\t\t\t\t
        \n\t\t\t\t
        \n\t\t\t\t\t\t\t\t\t

        For my scenario \u2013 I think Level 2 is the way to go and will stem my testing out from there.\u00a0<\/p>\n

        My implementation plan is to implement the log streaming task into production and let it run by itself for a couple of weeks.<\/p>\n

        During that time, I will monitor how the log stream task behaves with large batches and how much data it writes to disk as we cannot generate that amount data in our performance testing environments.<\/p>\n

        Once the log stream task is settled; then I will start migrating the existing Qlik Replicate tasks to read from the log stream task stream instead of directly from the database.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"

        It seems every new project; the project teams come with new and innovative ways to make my life \u201cinteresting\u201d while managing Qlik Replicate. The latest idea...<\/p>\n","protected":false},"author":1,"featured_media":1076,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[16],"tags":[],"class_list":["post-1226","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-qlik-replicate"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1226","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1226"}],"version-history":[{"count":4,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1226\/revisions"}],"predecessor-version":[{"id":1233,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1226\/revisions\/1233"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/media\/1076"}],"wp:attachment":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1226"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1226"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1226"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}