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

Creating CONNECT PIVOT table on large base table crashes server

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.10
    • 10.0.11
    • None
    • Mac OS X, CentOS 6.5

    Description

      I have a table with a couple million rows in it:

      CREATE TABLE `big_table` (
      		 
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      		 
        `c1` char(32) DEFAULT NULL,
      		 
        `c2` char(32) DEFAULT NULL,
      		 
        `c3` char(32) DEFAULT NULL,
      		 
        PRIMARY KEY (`id`)
      		 
      ) ENGINE=InnoDB;

      Each row of the table has different values in the character columns, but all three character columns are the same for each row:

      mysql 10.0.10-MariaDB (root) [test]> select * from big_table limit 10;
      		 
      +---------+----------------------------------+----------------------------------+----------------------------------+
      		 
      | id      | c1                               | c2                               | c3                               |
      		 
      +---------+----------------------------------+----------------------------------+----------------------------------+
      		 
      | 1960001 | a64f35b091f92301e81abd6c881efa4c | a64f35b091f92301e81abd6c881efa4c | a64f35b091f92301e81abd6c881efa4c |
      		 
      | 1960002 | 3cddf70e775e566a270b3a80ebeb635f | 3cddf70e775e566a270b3a80ebeb635f | 3cddf70e775e566a270b3a80ebeb635f |
      		 
      | 1960003 | f3f75c6448ae94cc20c78bcff17b0d92 | f3f75c6448ae94cc20c78bcff17b0d92 | f3f75c6448ae94cc20c78bcff17b0d92 |
      		 
      | 1960004 | f0f39711ef82cb8f99443b59ad12f940 | f0f39711ef82cb8f99443b59ad12f940 | f0f39711ef82cb8f99443b59ad12f940 |

      I tried creating a really simple pivot table:

      create table big_table_pivot engine=connect table_type=pivot tabname=big_table;

      Shortly after executing that create table statement, mysqld crashes:

       #0  0x00007ffff6a907fe in __longjmp () from /lib64/libc.so.6
      		 
       #1  0x4830f0d22fbc1ec0 in ?? ()

      It crashes even after removing quite a lot of rows. Now with <200,000 rows in the table, the CREATE TABLE statement appears to be hanging, rather than crashing.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bertrandop Olivier Bertrand added a comment -

          Well, PIVOT is probably not meant to be used on very big tables. It requires a lot of storage to find the distinct PIVOT column names and, if there are many, can produce tables with too many columns.
          However, a bug caused exhausted memory to crash the server instead of raising an error condition with a proper message.
          This was fixed and it should not crash anymore.
          By the way, it is not hanging on big tables but just takes a very long time.

          bertrandop Olivier Bertrand added a comment - Well, PIVOT is probably not meant to be used on very big tables. It requires a lot of storage to find the distinct PIVOT column names and, if there are many, can produce tables with too many columns. However, a bug caused exhausted memory to crash the server instead of raising an error condition with a proper message. This was fixed and it should not crash anymore. By the way, it is not hanging on big tables but just takes a very long time.
          kolbe Kolbe Kegel (Inactive) added a comment -

          I thought it was maybe some problem with memory usage, but it didn't seem like mysqld memory usage really went up that much.

          I had success using my own hand-rolled "pivot" approach for this large table where I did GROUP BY and GROUP_CONCAT(IF(pivotCol=1,factCol,null)) as value_1, repeating that pattern for each value of the pivot column. I was hoping I could use the pivot feature in connect to do that heavy lifting for me. I'm still sort of figuring out how pivot in connect works, so maybe I can get it to do what I want if I keep poking at it enough

          kolbe Kolbe Kegel (Inactive) added a comment - I thought it was maybe some problem with memory usage, but it didn't seem like mysqld memory usage really went up that much. I had success using my own hand-rolled "pivot" approach for this large table where I did GROUP BY and GROUP_CONCAT(IF(pivotCol=1,factCol,null)) as value_1, repeating that pattern for each value of the pivot column. I was hoping I could use the pivot feature in connect to do that heavy lifting for me. I'm still sort of figuring out how pivot in connect works, so maybe I can get it to do what I want if I keep poking at it enough
          bertrandop Olivier Bertrand added a comment -

          Ooops, I did not quite understand what you did...
          However, it's normal that mysqld memory usage did not went up because CONNECT has its own memory allocation system (in fact sub-allocation) that is limited by the size of the area in which it works. What I am working on is to add a system variable allowing to change the size of this work area. For instance increasing it from 64M (the default) to 256M enabled me to create a pivot table on a 10 million rows table. It is created but still fails to be used, more work to do...

          bertrandop Olivier Bertrand added a comment - Ooops, I did not quite understand what you did... However, it's normal that mysqld memory usage did not went up because CONNECT has its own memory allocation system (in fact sub-allocation) that is limited by the size of the area in which it works. What I am working on is to add a system variable allowing to change the size of this work area. For instance increasing it from 64M (the default) to 256M enabled me to create a pivot table on a 10 million rows table. It is created but still fails to be used, more work to do...

          People

            bertrandop Olivier Bertrand
            kolbe Kolbe Kegel (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.