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

CONVERT_IF_BIGGER_TO_BLOB makes temp. table use Aria, which hits the disk

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • None
    • None

    Description

      In 5.3 derived_merge was passing the CONVERT_IF_BIGGER_TO_BLOB and would force the optimizer not to produce on disk temporary table . It stopped working in 5.5

      I have documented the issue here :
      http://varokism.blogspot.fr/2011/12/back-on-disk-temporary-tables.html

      At that time no test case have been produce and merging may have break this improvement.

      Attachments

        Issue Links

          Activity

            I have explored why MySQL/MariaDB has CONVERT_IF_BIGGER_TO_BLOB constant.

            The reason is: temporary tables use "Fixed" row format. This means that a temporary table with this definition

            CREATE TABLE tmp (col1 varchar(3000) CHARACTER SET utf8);

            will use 3000* max_char_len(utf8) = 3000* 3= 9000 bytes for each row. Dynamic row format (and blobs will use dynamic row format) is much more economical.

            One may ask a question: why can't we use a heap temporary table with a dynamic row format? The answer for this is not yet clear for me. I'll investigate.

            psergei Sergei Petrunia added a comment - I have explored why MySQL/MariaDB has CONVERT_IF_BIGGER_TO_BLOB constant. The reason is: temporary tables use "Fixed" row format. This means that a temporary table with this definition CREATE TABLE tmp (col1 varchar(3000) CHARACTER SET utf8); will use 3000* max_char_len(utf8) = 3000* 3= 9000 bytes for each row. Dynamic row format (and blobs will use dynamic row format) is much more economical. One may ask a question: why can't we use a heap temporary table with a dynamic row format? The answer for this is not yet clear for me. I'll investigate.

            It turns out, MariaDB doesn't support row_format=dynamic for heap tables.

            Percona supports row_format=dynamic as of 5.5. However, their create_tmp_table() code is not aware of that, so Percona Server will still create an on-disk table for the provided example.

            psergei Sergei Petrunia added a comment - It turns out, MariaDB doesn't support row_format=dynamic for heap tables. Percona supports row_format=dynamic as of 5.5. However, their create_tmp_table() code is not aware of that, so Percona Server will still create an on-disk table for the provided example.

            In my opinion, this cannot be fixed in MariaDB 5.5. It is quite risky to make changes in create_tmp_table() code. We could consider this as a feature for MariaDB 10.0.

            psergei Sergei Petrunia added a comment - In my opinion, this cannot be fixed in MariaDB 5.5. It is quite risky to make changes in create_tmp_table() code. We could consider this as a feature for MariaDB 10.0.

            Hi Sergei,

            Should we convert it into a task and target for 10.1?

            elenst Elena Stepanova added a comment - Hi Sergei, Should we convert it into a task and target for 10.1?

            Personally, I would like to see this implemented, so I think converting it into a task is a good idea.

            ccalender Chris Calender (Inactive) added a comment - Personally, I would like to see this implemented, so I think converting it into a task is a good idea.

            People

              Unassigned Unassigned
              stephane@skysql.com VAROQUI Stephane
              Votes:
              2 Vote for this issue
              Watchers:
              7 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.