Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-41499

With PQ, Performance degradation observed for range predicates returning fewer rows

    XMLWordPrintable

Details

    • Related to performance

    Description

      Issue:

      -------------
      Table has 10L rows with column "a" ranging from 1 to 10L
      select * from query where a<1000 ;
      With PQ (100) , performance degradation seen when query returns fewer rows.

      Note:

      Perf improvement seen with a>1000 condition

      Query:

      analyze select * from t2 where a<1000;
      

      serial: 0.001 (1ms)
      parallel(100) : 0.009 (9ms)
      perf degradation: 800% (9 times slower)

      For analyze output refer:
      serial : analyze-json.txt
      Parallel : analyze-json-PQ100.txt

      Note:

      parallel(2,4,8) : 2ms – analyze-json-PQ2.txt
      parallel(16) : 3ms – analyze-json-PQ16.txt
      parallel(32) : 4ms – analyze-json-PQ32.txt
      parallel(64) : 6ms – analyze-json-PQ64.txt
      parallel(100) : 9ms --analyze-json-PQ100.txt

      Version details:

      ----------------------
      Branch: bb-13.2-MDEV-40012-refactored-input3
      Commit:1648b516083ccdbddeb123dc6cf2c2f62d7b0328
      Version:13.1.0

      How to repro:

      create table t2( a int);
      create unique index i1 on t2(a);
      insert into t2 select seq from seq_1_to_1000000;
       
      //Run query with 0 and 100 threads and note the difference
      set parallel_worker_threads=0;
      analyze select * from t2 where a<1000;
      

      Attachments

        1. analyze-json-PQ64.txt
          77 kB
        2. analyze-json-PQ32.txt
          9 kB
        3. analyze-json-PQ2.txt
          5 kB
        4. analyze-json-PQ16.txt
          7 kB
        5. analyze-json-PQ100.txt
          145 kB
        6. analyze-json.txt
          44 kB

        Issue Links

          Activity

            People

              Johnston Rex Johnston
              mariadb-pavithrapandith Pavithra Pandith
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.