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

JSON Histograms: improve histogram collection

Details

    Description

      The histograms as defined in MDEV-21130 follow the approach used by SINGLE_PREC_HB/DOUBLE_PREC_HB histograms and put the bucket bound exactly after
      a certain fraction of rows.

      1. Popular values should have their own buckets

      For example, consider a table with these values (each character is one row):

        aaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbccdefghijkilmnopq
      		 
        |     |     |     |     |     |     |     |     |
      		 
        0     1     2     3     4     5     6     7     8

      A histogram with 8 buckets will have these buckets:

      1. a-a
      		 
      2. a-a
      		 
      3. a-a
      		 
      3. a-a
      		 
      5. a-b
      		 
      6. b-f
      		 
      7. f-i
      		 
      8. i-q

      The estimation function will be able to see that 'a' is a popular value, it will determine its frequency with bucket_size precision.

      However, a better approach would be to put all 'a' values in one bucket:

        aaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbccdefghijkilmnopq
      		 
        |                           |  |  |  |  |  |  | |
      		 
        0                           1  2  3  4  5  6  7 8

      This will give a more precise number for table 'a'.
      It will also provide more buckets for other values.

      Other databases

      • MySQL does something like this.
      • PostgreSQL collects MCVs (Most Common Values) and removes them from consideration before building the histogram.

      2. Store the number of distinct values in each bucket

      Again, following MySQL here: store number of distinct values in each bucket.

      For strings, store only prefix

      Store only a 40-character prefix.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26709 JSON histogram may contain bucketS than histogram_size allows

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26710 Histogram field in mysql.column_stats is too short, JSON histograms get corrupt

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26711 JSON histograms become invalid due to not properly quoted values

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26718 Query with: Cross join, Non-indexed column comparison, Column with histogram: speed varies 20x depending on histogram type

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26724 Endless loop in json_escape_to_string upon collecting JSON histograms with empty string in a column

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26751 Deficiencies in MTR coverage for JSON histograms

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26801 Valgrind/MSAN errors in Column_statistics_collected::finish, main.statistics_json fails

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26885 Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE_PREC (#2)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26886 Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE_PREC (#3)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26892 JSON histograms become invalid with a specific (corrupt) value in table

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26901 Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE_PREC (#4)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26911 Unexpected ER_DUP_KEY, ASAN errors, double free detected in tcache with JSON_HB histogram

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27203 Valgrind / MSAN errors in Histogram_json_hb::parse_bucket, main.statistics_json fails

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27229 Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE_PREC (#5)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27230 Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE_PREC (#6)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27243 Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE_PREC (#7) upon TIME comparison

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27492 DOUBLE_PREC_HB histogram has poor estimates for BIT columns

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27497 DOUBLE_PREC_HB histogram produces wrong estimates for COMPRESSED columns

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30097 Assertion `low == (int)buckets.size()-1 || field->key_cmp((uchar*)buckets[low+1].start_value.data(), lookup_val)> 0' failed in Histogram_json_hb::find_bucket

          • Major - Major loss of function.
          • Confirmed
          is part of

          Task - A task that needs to be done. MDEV-21130 Histograms: use JSON as on-disk format

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-27373 Q1 2022 release merge

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27472 ANALYZE: r_filtered=100 may look confusing when r_rows=0

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27495 Less precise filtered estimation with JSON histogram (#9) or wrong value of r_filtered

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26764 JSON_HB Histograms: handle BINARY and unassigned characters

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26849 JSON Histograms: point selectivity estimates are off for non-existent values

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-27062 Make histogram_type=JSON_HB the new default

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia created issue -
            psergei Sergei Petrunia made changes -
            Field Original Value New Value
            psergei Sergei Petrunia made changes -
            Description The histograms as defined in MDEV-21130 follow the approach used by SINGLE_PREC_HB/DOUBLE_PREC_HB histograms and put the bucket bound exactly after
            a certain fraction of rows.

            For example, consider a table with these values (each character is one row):

            {code}
              aaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbccdefghijkilmnopq
              | | | | | | | | |
              0 1 2 3 4 5 6 7 8
            {code}

            A histogram with 8 buckets will have these buckets:

            {code}
            1. a-a
            2. a-a
            3. a-a
            3. a-a
            5. a-b
            6. b-f
            7. f-i
            8. i-q
            {code}

            The estimation function will be able to see that 'a' is a popular value, it will determine its frequency with bucket_size precision.

            However, a better approach would be to put all 'a' values in one bucket:

            {code}
              aaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbccdefghijkilmnopq
              | | | | | | | | |
              0 1 2 3 4 5 6 7 8
            {code}

            This will give a more precise number for table 'a'.
            It will also provide more buckets for other values.

            MySQL does something like this.             		The histograms as defined in MDEV-21130 follow the approach used by SINGLE_PREC_HB/DOUBLE_PREC_HB histograms and put the bucket bound exactly after
            a certain fraction of rows.

            For example, consider a table with these values (each character is one row):

            {code}
              aaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbccdefghijkilmnopq
              | | | | | | | | |
              0 1 2 3 4 5 6 7 8
            {code}

            A histogram with 8 buckets will have these buckets:

            {code}
            1. a-a
            2. a-a
            3. a-a
            3. a-a
            5. a-b
            6. b-f
            7. f-i
            8. i-q
            {code}

            The estimation function will be able to see that 'a' is a popular value, it will determine its frequency with bucket_size precision.

            However, a better approach would be to put all 'a' values in one bucket:

            {code}
              aaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbccdefghijkilmnopq
              | | | | | | | | |
              0 1 2 3 4 5 6 7 8
            {code}

            This will give a more precise number for table 'a'.
            It will also provide more buckets for other values.

            h2. Other databases
            * MySQL does something like this.
            * PostgreSQL collects MCVs (Most Common Values) and removes them from consideration before building the histogram.
            psergei Sergei Petrunia made changes -
            Summary JSON Histograms: popular values should have their own buckets JSON Histograms: improve histogram collection
            psergei Sergei Petrunia made changes -
            Description The histograms as defined in MDEV-21130 follow the approach used by SINGLE_PREC_HB/DOUBLE_PREC_HB histograms and put the bucket bound exactly after
            a certain fraction of rows.

            For example, consider a table with these values (each character is one row):

            {code}
              aaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbccdefghijkilmnopq
              | | | | | | | | |
              0 1 2 3 4 5 6 7 8
            {code}

            A histogram with 8 buckets will have these buckets:

            {code}
            1. a-a
            2. a-a
            3. a-a
            3. a-a
            5. a-b
            6. b-f
            7. f-i
            8. i-q
            {code}

            The estimation function will be able to see that 'a' is a popular value, it will determine its frequency with bucket_size precision.

            However, a better approach would be to put all 'a' values in one bucket:

            {code}
              aaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbccdefghijkilmnopq
              | | | | | | | | |
              0 1 2 3 4 5 6 7 8
            {code}

            This will give a more precise number for table 'a'.
            It will also provide more buckets for other values.

            h2. Other databases
            * MySQL does something like this.
            * PostgreSQL collects MCVs (Most Common Values) and removes them from consideration before building the histogram.             		The histograms as defined in MDEV-21130 follow the approach used by SINGLE_PREC_HB/DOUBLE_PREC_HB histograms and put the bucket bound exactly after
            a certain fraction of rows.

            h2. Popular values should have their own buckets

            For example, consider a table with these values (each character is one row):

            {code}
              aaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbccdefghijkilmnopq
              | | | | | | | | |
              0 1 2 3 4 5 6 7 8
            {code}

            A histogram with 8 buckets will have these buckets:

            {code}
            1. a-a
            2. a-a
            3. a-a
            3. a-a
            5. a-b
            6. b-f
            7. f-i
            8. i-q
            {code}

            The estimation function will be able to see that 'a' is a popular value, it will determine its frequency with bucket_size precision.

            However, a better approach would be to put all 'a' values in one bucket:

            {code}
              aaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbccdefghijkilmnopq
              | | | | | | | | |
              0 1 2 3 4 5 6 7 8
            {code}

            This will give a more precise number for table 'a'.
            It will also provide more buckets for other values.

            h3. Other databases
            * MySQL does something like this.
            * PostgreSQL collects MCVs (Most Common Values) and removes them from consideration before building the histogram.
            psergei Sergei Petrunia made changes -
            Description The histograms as defined in MDEV-21130 follow the approach used by SINGLE_PREC_HB/DOUBLE_PREC_HB histograms and put the bucket bound exactly after
            a certain fraction of rows.

            h2. Popular values should have their own buckets

            For example, consider a table with these values (each character is one row):

            {code}
              aaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbccdefghijkilmnopq
              | | | | | | | | |
              0 1 2 3 4 5 6 7 8
            {code}

            A histogram with 8 buckets will have these buckets:

            {code}
            1. a-a
            2. a-a
            3. a-a
            3. a-a
            5. a-b
            6. b-f
            7. f-i
            8. i-q
            {code}

            The estimation function will be able to see that 'a' is a popular value, it will determine its frequency with bucket_size precision.

            However, a better approach would be to put all 'a' values in one bucket:

            {code}
              aaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbccdefghijkilmnopq
              | | | | | | | | |
              0 1 2 3 4 5 6 7 8
            {code}

            This will give a more precise number for table 'a'.
            It will also provide more buckets for other values.

            h3. Other databases
            * MySQL does something like this.
            * PostgreSQL collects MCVs (Most Common Values) and removes them from consideration before building the histogram.
            		The histograms as defined in MDEV-21130 follow the approach used by SINGLE_PREC_HB/DOUBLE_PREC_HB histograms and put the bucket bound exactly after
            a certain fraction of rows.

            h2. 1. Popular values should have their own buckets

            For example, consider a table with these values (each character is one row):

            {code}
              aaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbccdefghijkilmnopq
              | | | | | | | | |
              0 1 2 3 4 5 6 7 8
            {code}

            A histogram with 8 buckets will have these buckets:

            {code}
            1. a-a
            2. a-a
            3. a-a
            3. a-a
            5. a-b
            6. b-f
            7. f-i
            8. i-q
            {code}

            The estimation function will be able to see that 'a' is a popular value, it will determine its frequency with bucket_size precision.

            However, a better approach would be to put all 'a' values in one bucket:

            {code}
              aaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbccdefghijkilmnopq
              | | | | | | | | |
              0 1 2 3 4 5 6 7 8
            {code}

            This will give a more precise number for table 'a'.
            It will also provide more buckets for other values.

            h3. Other databases
            * MySQL does something like this.
            * PostgreSQL collects MCVs (Most Common Values) and removes them from consideration before building the histogram.

            h2. 2. Store the number of distinct values in each bucket

            Again, following MySQL here: store number of distinct values in each bucket.

            h2. For strings, store only prefix

            Store only a 40-character prefix.
            psergei Sergei Petrunia made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            psergei Sergei Petrunia made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            psergei Sergei Petrunia made changes -
            Labels eits
            serg Sergei Golubchik made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            psergei Sergei Petrunia added a comment -

            Note for those who were asking "why it is not done like in MySQL-8" : MySQL 8 has some properties that I doubt we will want to have: https://bugs.mysql.com/bug.php?id=104789

            psergei Sergei Petrunia added a comment - Note for those who were asking "why it is not done like in MySQL-8" : MySQL 8 has some properties that I doubt we will want to have: https://bugs.mysql.com/bug.php?id=104789
            psergei Sergei Petrunia added a comment -

            Suggestion #1:

            "Let each value that's larger than one bucket be in its own bucket"

            However, this may cause us to generate close to 2*N buckets (where N is the
            intended number of buckets). The idea is that popular values are separated
            by a small amount of unpopular values.

            0123456789 0123456789 0123456789 0123456789  --  numbers, just for reference
            		 
             AAAAAAAAb bCCCCCCCCd dEEEEEEEEf fGGGGGGG..  --  data (one character is one row)

            • suppose bucket_size=5
            • capital letters are popular values (each takes >5 rows)
            • small letters are non-popular values

            Make each "popular" value take a bit more than bucket_size rows. The table can
            have slightly less than N such "popular" values (let it be N1)

            Between each two consequent popular values, put a few rows with unpopular
            values. Then, each popular value gets a bucket, and each gap gets a bucket,
            which gives 2*N1 buckets.

            psergei Sergei Petrunia added a comment - Suggestion #1: "Let each value that's larger than one bucket be in its own bucket" However, this may cause us to generate close to 2*N buckets (where N is the intended number of buckets). The idea is that popular values are separated by a small amount of unpopular values. 0123456789 0123456789 0123456789 0123456789 -- numbers, just for reference AAAAAAAAb bCCCCCCCCd dEEEEEEEEf fGGGGGGG.. -- data (one character is one row) suppose bucket_size=5 capital letters are popular values (each takes >5 rows) small letters are non-popular values Make each "popular" value take a bit more than bucket_size rows. The table can have slightly less than N such "popular" values (let it be N1) Between each two consequent popular values, put a few rows with unpopular values. Then, each popular value gets a bucket, and each gap gets a bucket, which gives 2*N1 buckets.
            psergei Sergei Petrunia added a comment -

            Suggestion #2:
            Walk through the values and only put the value into a separate bucket if it covers
            the current position + the whole next bucket:

            Here, D is not put into a separate bucket:

             |===============|===============|
            		 
              aaabbccDDDDDDDDDDDDDDDDDDDDDDDee

            Here F is put into a separate bucket:

             |===============|===============|
            		 
              aaabbccFFFFFFFFFFFFFFFFFFFFFFFFFFF

            For values that take between 1 and 2 buckets, getting their own bucket becomes
            is a matter of luck.

            A popular value that is put into a larger bucket "saves" buckets by using fewer buckets.
            If this happens, the generated histogram will have fewer buckets.

            (The alternative is to make the next buckets have fewer rows but we are not
            doing that in this MDEV )

            psergei Sergei Petrunia added a comment - Suggestion #2: Walk through the values and only put the value into a separate bucket if it covers the current position + the whole next bucket: Here, D is not put into a separate bucket: |===============|===============| aaabbccDDDDDDDDDDDDDDDDDDDDDDDee Here F is put into a separate bucket: |===============|===============| aaabbccFFFFFFFFFFFFFFFFFFFFFFFFFFF For values that take between 1 and 2 buckets, getting their own bucket becomes is a matter of luck. A popular value that is put into a larger bucket "saves" buckets by using fewer buckets. If this happens, the generated histogram will have fewer buckets. (The alternative is to make the next buckets have fewer rows but we are not doing that in this MDEV )
            psergei Sergei Petrunia added a comment -

            https://github.com/MariaDB/server/tree/bb-10.7-mdev26519

            psergei Sergei Petrunia added a comment - https://github.com/MariaDB/server/tree/bb-10.7-mdev26519
            psergei Sergei Petrunia added a comment -

            The patch implements Suggestion #2 described above.

            psergei Sergei Petrunia added a comment - The patch implements Suggestion #2 described above.
            greenman Ian Gilfillan added a comment -

            For anyone wanting to preview the feature, see https://mariadb.org/10-7-preview-feature-json-histograms/

            greenman Ian Gilfillan added a comment - For anyone wanting to preview the feature, see https://mariadb.org/10-7-preview-feature-json-histograms/
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            psergei Sergei Petrunia made changes -
            elenst Elena Stepanova made changes -
            psergei Sergei Petrunia made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.8 [ 26121 ]
            Fix Version/s 10.7 [ 24805 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            psergei Sergei Petrunia made changes -
            julien.fritsch Julien Fritsch made changes -
            julien.fritsch Julien Fritsch made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 124750 ] MariaDB v4 [ 131851 ]
            serg Sergei Golubchik made changes -
            Status In Progress [ 3 ] In Testing [ 10301 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Petrunia [ psergey ] Elena Stepanova [ elenst ]
            elenst Elena Stepanova added a comment -

            The 10.8 branch to test is preview-10.8-MDEV-26519-json-histograms

            elenst Elena Stepanova added a comment - The 10.8 branch to test is preview-10.8- MDEV-26519 -json-histograms
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova added a comment - - edited

            Here are the acceptance criteria I am planning to apply to this preview (minor modifications are possible):

            1) At least 100x5min test runs for estimation precision checks should pass
            Note: This criterion is made-up to replace the absent requirements. Constants below were chosen in an arbitrary fashion and can be adjusted if there is a demand and interested parties agree.
            Note: "Special case" or "corner case" exceptions will be accepted, but only if they are officially documented in a form understandable to end users.
            The test is performed on a non-debug build as follows:

            • two servers with mildly randomized identical configurations are run using the same binaries from the preview branch, test server running with JSON_HB and the baseline with DOUBLE_PREC_HB, histogram_size default;
            • the servers execute randomized single-table ANALYZE FORMAT=JSON SELECT statements on randomized table structures and data, with table size mostly ranging from 20 to 5000 rows;
            • only queries with rows value >= 20 are looked at;
            • only queries for which on the baseline server the precision of filtered estimation is within 5% are looked at;
            • an error is returned if the precision of filtered estimation on the test server is more than 20% off.

            2) At least 100x5min test runs for histogram health checks and basic server stability should show no feature-specific problems or regressions comparing to vanilla 10.8.
            The stress test is performed on a debug ASAN build with heavily randomized configurations as follows:

            • the standard generic regression test combinations (random DML+DDL) are used, with the addition of histogram_type=JSON_HB and frequent ANALYZE TABLE .. PERSISTENT FOR ALL;
            • periodically mysql.column_stats.histogram for existing JSON histograms is checked so that
              • JSON_VALID is true,
              • actual histogram size is less or equal than histogram_size value,
              • the total size of all buckets sums up to 1.

            3) MTR test runs (subject to general skip lists) on an MSAN/ASAN/UBSAN debug builds, big+nm and big+ps, with --mysqld=--histogram-type=JSON_HB, may only return legacy failures and understandable mismatch/test failures caused by the non-default option.

            4) MTR test runs (subject to general skip lists) on an MSAN/ASAN/UBSAN debug builds, big+nm and big+ps, may only return legacy failures.

            Legend: as above

            Test Status Updated Last tested commit Notes
            Estimation precision 2022-01-16 348d3788 A few failures similar to those already ruled out as unrelated issues
            Health/stability 2022-01-16 348d3788 18% tests failed on unrelated reasons
            ASAN/MSAN/UBSAN big/nm+ps MTR 2022-01-16 746fc9e5 a few unrelated sporadic failures
            ASAN/MSAN/UBSAN big/nm+ps MTR with JSON_HB 2022-01-16 746fc9e5 a few unrelated sporadic failures
            elenst Elena Stepanova added a comment - - edited Here are the acceptance criteria I am planning to apply to this preview (minor modifications are possible): 1) At least 100x5min test runs for estimation precision checks should pass Note: This criterion is made-up to replace the absent requirements. Constants below were chosen in an arbitrary fashion and can be adjusted if there is a demand and interested parties agree. Note: "Special case" or "corner case" exceptions will be accepted, but only if they are officially documented in a form understandable to end users. The test is performed on a non-debug build as follows: two servers with mildly randomized identical configurations are run using the same binaries from the preview branch, test server running with JSON_HB and the baseline with DOUBLE_PREC_HB, histogram_size default; the servers execute randomized single-table ANALYZE FORMAT=JSON SELECT statements on randomized table structures and data, with table size mostly ranging from 20 to 5000 rows; only queries with rows value >= 20 are looked at; only queries for which on the baseline server the precision of filtered estimation is within 5% are looked at; an error is returned if the precision of filtered estimation on the test server is more than 20% off. 2) At least 100x5min test runs for histogram health checks and basic server stability should show no feature-specific problems or regressions comparing to vanilla 10.8. The stress test is performed on a debug ASAN build with heavily randomized configurations as follows: the standard generic regression test combinations (random DML+DDL) are used, with the addition of histogram_type=JSON_HB and frequent ANALYZE TABLE .. PERSISTENT FOR ALL; periodically mysql.column_stats.histogram for existing JSON histograms is checked so that JSON_VALID is true, actual histogram size is less or equal than histogram_size value, the total size of all buckets sums up to 1. 3) MTR test runs (subject to general skip lists) on an MSAN/ASAN/UBSAN debug builds, big+nm and big+ps, with --mysqld=--histogram-type=JSON_HB , may only return legacy failures and understandable mismatch/test failures caused by the non-default option. 4) MTR test runs (subject to general skip lists) on an MSAN/ASAN/UBSAN debug builds, big+nm and big+ps, may only return legacy failures. Legend: as above Test Status Updated Last tested commit Notes Estimation precision 2022-01-16 348d3788 A few failures similar to those already ruled out as unrelated issues Health/stability 2022-01-16 348d3788 18% tests failed on unrelated reasons ASAN/MSAN/UBSAN big/nm+ps MTR 2022-01-16 746fc9e5 a few unrelated sporadic failures ASAN/MSAN/UBSAN big/nm+ps MTR with JSON_HB 2022-01-16 746fc9e5 a few unrelated sporadic failures
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova added a comment -

            I think preview-10.8-MDEV-26519-json-histograms as of commit 746fc9e5 can be pushed into 10.8 main and released with 10.8.1.

            The difference between 746fc9e5 and 348d3788 is in test files only, so tests performed on 348d3788 do not need to be re-run.

            Please note that testing of the feature was limited to its technical functionality (histogram precision) and absence of functional/stability regression. Due to the specifics of the feature, it doesn't reflect end user experience – users don't use histograms directly and their precision as such doesn't concern them, what's important is the final performance of query execution, and it wasn't a part of the feature testing as it was ruled out at early stages as irrelevant to the task scope. It is reasonable for the feature itself (MDEV-21130/MDEV-26519), but for enabling it by default (MDEV-27062) I would recommend some comparative performance testing, at least with widely-recognized workloads, possibly with the table structures tuned to extend histogram usage.

            elenst Elena Stepanova added a comment - I think preview-10.8- MDEV-26519 -json-histograms as of commit 746fc9e5 can be pushed into 10.8 main and released with 10.8.1. The difference between 746fc9e5 and 348d3788 is in test files only, so tests performed on 348d3788 do not need to be re-run. Please note that testing of the feature was limited to its technical functionality (histogram precision) and absence of functional/stability regression. Due to the specifics of the feature, it doesn't reflect end user experience – users don't use histograms directly and their precision as such doesn't concern them, what's important is the final performance of query execution, and it wasn't a part of the feature testing as it was ruled out at early stages as irrelevant to the task scope. It is reasonable for the feature itself ( MDEV-21130 / MDEV-26519 ), but for enabling it by default ( MDEV-27062 ) I would recommend some comparative performance testing, at least with widely-recognized workloads, possibly with the table structures tuned to extend histogram usage.
            elenst Elena Stepanova made changes -
            Assignee Elena Stepanova [ elenst ] Sergei Golubchik [ serg ]
            Status In Testing [ 10301 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Sergei Petrunia [ psergey ]
            serg Sergei Golubchik made changes -
            Priority Critical [ 2 ] Blocker [ 1 ]
            psergei Sergei Petrunia added a comment - - edited

            Pushed into 10.8. JSON_HB histograms are NOT enabled by default.

            psergei Sergei Petrunia added a comment - - edited Pushed into 10.8. JSON_HB histograms are NOT enabled by default.
            psergei Sergei Petrunia made changes -
            Fix Version/s 10.8.1 [ 26815 ]
            Fix Version/s 10.8 [ 26121 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            elenst Elena Stepanova made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Labels eits Preview_10.8 eits
            ralf.gebhardt Ralf Gebhardt made changes -
            Labels Preview_10.8 eits Preview_10.7 Preview_10.8 eits

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.