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

Performance testing for packed sort keys

Details

    Description

      This tasks relates to analysing performance of the implementation of packed sort keys in MDEV-21580.

      First part of testing this would be

      • use the random-generated latin1 varchar dataset introduced in MDEV-21263 (with more rows)
      • take integers and/or dates and make sure we didn't introduce a regression [can use dbt3 queries for this]

      Attachments

        Issue Links

          is part of

          Technical task - A technical task. MDEV-21580 Allow packed sort keys in sort buffer

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-21955 Packed sort keys: Evaluate performance of dynamic-length mem-comparable data encoding

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 9 older comments
            psergei Sergei Petrunia added a comment - - edited

            Note that this again was not reproducible on CentOS machine.

            data from the two runs:

            old	new	100*new/old
            		 
            13	12	92.31
            		 
            63	50	79.37
            		 
            99	108	109.09
            		 
            200	189	94.50
            		 
            401	378	94.26
            		 
            796	759	95.35
            		 
            1593	1517	95.23
            		 
            3174	3001	94.55


            old	new	100*new/old
            		 
            13	12	92.31
            		 
            50	48	96.00
            		 
            100	93	93.00
            		 
            252	190	75.40
            		 
            405	377	93.09
            		 
            805	753	93.54
            		 
            1610	1525	94.72
            		 
            3245	3044	93.81

            here, adding new virtual functions made the queries run slightly faster?

            psergei Sergei Petrunia added a comment - - edited Note that this again was not reproducible on CentOS machine. data from the two runs: old new 100*new/old 13 12 92.31 63 50 79.37 99 108 109.09 200 189 94.50 401 378 94.26 796 759 95.35 1593 1517 95.23 3174 3001 94.55 old new 100*new/old 13 12 92.31 50 48 96.00 100 93 93.00 252 190 75.40 405 377 93.09 805 753 93.54 1610 1525 94.72 3245 3044 93.81 here, adding new virtual functions made the queries run slightly faster?
            psergei Sergei Petrunia added a comment -

            Varchar benchmark:

            https://docs.google.com/spreadsheets/d/1uXGeg7-XkHdo0-yvuK5d0d6zxiLUco_At3FmAhHXQdM/edit#gid=0

            • For short varchars, new code is slower.
            • For long varchars, it is faster
            • THere is an odd maximum at varchar(200)?
            psergei Sergei Petrunia added a comment - Varchar benchmark: https://docs.google.com/spreadsheets/d/1uXGeg7-XkHdo0-yvuK5d0d6zxiLUco_At3FmAhHXQdM/edit#gid=0 For short varchars, new code is slower. For long varchars, it is faster THere is an odd maximum at varchar(200)?
            psergei Sergei Petrunia added a comment -

            Added another tab:
            https://docs.google.com/spreadsheets/d/1uXGeg7-XkHdo0-yvuK5d0d6zxiLUco_At3FmAhHXQdM/edit#gid=1218566530
            For latin1 character set,

            • sorting data in VARCHAR(N) gets more expensive for bigger values of N.
            • with packed sort keys, there's no difference.
            psergei Sergei Petrunia added a comment - Added another tab: https://docs.google.com/spreadsheets/d/1uXGeg7-XkHdo0-yvuK5d0d6zxiLUco_At3FmAhHXQdM/edit#gid=1218566530 For latin1 character set, sorting data in VARCHAR(N) gets more expensive for bigger values of N. with packed sort keys, there's no difference.
            psergei Sergei Petrunia added a comment -

            More tabs added:

            https://docs.google.com/spreadsheets/d/1uXGeg7-XkHdo0-yvuK5d0d6zxiLUco_At3FmAhHXQdM/edit#gid=1367235574

            • utf8_general_ci: The query I've tried is faster than MySQL-8.
            • utf8_unicode_ci: the new code is faster than the old one
            psergei Sergei Petrunia added a comment - More tabs added: https://docs.google.com/spreadsheets/d/1uXGeg7-XkHdo0-yvuK5d0d6zxiLUco_At3FmAhHXQdM/edit#gid=1367235574 utf8_general_ci: The query I've tried is faster than MySQL-8. utf8_unicode_ci: the new code is faster than the old one
            serg Sergei Golubchik added a comment -

            More benchmarks https://docs.google.com/spreadsheets/d/1CqDb--d6gK_pWyecqU76wsmh0p-vVjCmB_uoknz65DY/

            serg Sergei Golubchik added a comment - More benchmarks https://docs.google.com/spreadsheets/d/1CqDb--d6gK_pWyecqU76wsmh0p-vVjCmB_uoknz65DY/

            People

              psergei Sergei Petrunia
              varun Varun Gupta (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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