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

INSERT SELECT randomly blocks in Copy to temp table

    XMLWordPrintable

    Details

      Description

      Hello,

      we have a INSERT SELECT statement that started to block randomly about a week ago. Normally the statement executes in less than 5 seconds, but when it blocks, it does not return in more than 3 hours (longer we did not wait).

      The statement blocks randomly, that means sometimes it runs fine and then a minute later it may block.

      The statement looks as follows:

      insert into findispyear(fyperi,fytyp,fywert,fyMenge,fyupdt,fylgrx,fymat,fywareh)
      select f1peri as Peri,'22. + WE aus Vorperioden' as Art,round(sum(atbwmg*atbwwt),4) as WEWert,
      sum(atbwmg) as fyMenge,
      date_format(current_date, '%Y%m%d')as fyupdt,
      f1lgrx as LgRx,f1mat as fymat,atzo01 as fywareh
      from werh01
      join werd01 on wezo01=wercid
      join atad01 on weatid=atpkey
      join w_findispmon01 on f1whse=atzo01 and f1zo05=atzo05
      where weendperi=f1peri
      and atbwdt <(f1peri*100)+1
      group by art,Peri,f1lgrx,f1mat,atzo01

      Some tests showed that it is most likely that the table atad01 is involved in this issue: The atad01 gets quite a lot of inserts. As long as these inserts are running (or even some inserts are pending when the statement above starts), then it is more likely for the statement to block. When we stop the inserts into atad01, then no blocking could be observed.

      Therefore here the table definition of atad01 and an example of the insert statements we are using there:

      CREATE TABLE `atad01` (
      `ATBWID` INT(11) NULL DEFAULT NULL,
      `ATZO01` INT(11) NULL DEFAULT NULL,
      `ATZO02` INT(11) NULL DEFAULT NULL,
      `ATZO03` INT(11) NULL DEFAULT NULL,
      `ATZO04` INT(11) NULL DEFAULT NULL,
      `ATZO05` INT(11) NULL DEFAULT NULL,
      `ATZO06` INT(11) NULL DEFAULT NULL,
      `ATZO07` INT(11) NULL DEFAULT NULL,
      `ATZO08` INT(11) NULL DEFAULT NULL,
      `ATZO09` INT(11) NULL DEFAULT NULL,
      `ATZO10` INT(11) NULL DEFAULT NULL,
      `ATBART` VARCHAR(10) NULL DEFAULT NULL,
      `ATBLGN` VARCHAR(64) NULL DEFAULT NULL,
      `ATBPOS` INT(11) NULL DEFAULT NULL,
      `ATCHGN` VARCHAR(10) NULL DEFAULT NULL,
      `ATKDLI` INT(11) NULL DEFAULT NULL,
      `ATBNCH` VARCHAR(10) NULL DEFAULT NULL,
      `ATPBVB` INT(11) NULL DEFAULT NULL,
      `ATBWMG` INT(11) NULL DEFAULT NULL,
      `ATBWWT` DECIMAL(13,4) NULL DEFAULT NULL,
      `ATBWDT` INT(11) NULL DEFAULT NULL,
      `ATBWZT` INT(11) NULL DEFAULT NULL,
      `ATCMT1` VARCHAR(77) NULL DEFAULT NULL,
      `ATUPDT` INT(11) NULL DEFAULT NULL,
      `ATUPZT` INT(11) NULL DEFAULT NULL,
      `ATUPUS` VARCHAR(50) NULL DEFAULT NULL,
      `ATUPBS` VARCHAR(50) NULL DEFAULT NULL,
      `ATPKEY` INT(11) NOT NULL AUTO_INCREMENT,
      `ATSRCE` VARCHAR(10) NULL DEFAULT NULL,
      `ATBUID` INT(11) NULL DEFAULT NULL,
      `ATSTOCKEVALUATIONTYPE` VARCHAR(25) NULL DEFAULT NULL,
      `ATIGNORELOCK` BIT(1) NULL DEFAULT NULL,
      `ATPBVBLGR` INT(11) NULL DEFAULT NULL,
      `ATCMTUSER` VARCHAR(1024) NULL DEFAULT NULL,
      `ATCMTERROR` VARCHAR(1024) NULL DEFAULT NULL,
      `ATWTVB` DECIMAL(13,4) NULL DEFAULT NULL,
      `ATCONSHIST` VARCHAR(10) NULL DEFAULT NULL,
      `ATZOTECHID` INT(11) NULL DEFAULT NULL,
      PRIMARY KEY (`ATPKEY`),
      INDEX `Art_ARTicle` (`ATBART`, `ATZO05`),
      INDEX `iATBWID_atad01` (`ATBWID`),
      INDEX `iDocid` (`ATBLGN`, `ATZO03`, `ATZO04`),
      INDEX `iLens` (`ATZO03`, `ATZO04`),
      INDEX `iWDBWID` (`ATZO08`),
      INDEX `iAS_KEY` (`ATBLGN`, `ATBPOS`),
      INDEX `izo06` (`ATZO06`),
      INDEX `iLocation` (`ATZO01`, `ATZO02`),
      INDEX `iArticle` (`ATZO05`),
      INDEX `atbwdt` (`ATBWDT`),
      INDEX `iATZO07_atad01` (`ATZO07`),
      INDEX `iATZOTECHID_atad01` (`ATZOTECHID`)
      )
      COLLATE='utf8_general_ci'
      ENGINE=InnoDB
      ROW_FORMAT=COMPACT
      AUTO_INCREMENT=17721126

      INSERT INTO `atad01` (`ATBWID`, `ATZO01`, `ATZO02`, `ATZO03`, `ATZO04`, `ATZO05`, `ATZO06`, `ATZO07`, `ATZO08`, `ATZO09`, `ATZO10`, `ATBART`, `ATBLGN`, `ATBPOS`, `ATCHGN`, `ATKDLI`, `ATBNCH`, `ATPBVB`, `ATBWMG`, `ATBWWT`, `ATBWDT`, `ATBWZT`, `ATCMT1`, `ATUPDT`, `ATUPZT`, `ATUPUS`, `ATUPBS`, `ATSRCE`, `ATBUID`, `ATSTOCKEVALUATIONTYPE`, `ATIGNORELOCK`, `ATPBVBLGR`, `ATCMTUSER`, `ATCMTERROR`, `ATWTVB`, `ATCONSHIST`, `ATZOTECHID`) VALUES (9, 1, NULL, NULL, NULL, 46432, 24, NULL, NULL, 529, NULL, 'STD', 'PICK529', 1, NULL, 0, 'LENS', 12, -1, 0.8100, 20120619, 110337, NULL, 20120619, 110337, 'user', 'OV-VM', 'BULK', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

      At the moment we are still in the stage where we try to understand, what is blocking. So at the moment we would be glad for hints, what to check, when the statement hangs, to get more details about what is going on.

      As so often: We were not able to reproduce this behavior on our test server, it happens on the live server only.

      As mentioned above, the problem started last week. Before that, the statement never blocked before, and the table structures, database version, statements running on those tables did not change for many weeks. Just the size of atad01 is constantly growing (currently around 7GB and 16 million lines.

      We double checked the free disk space for table space and temp files, both show many GB free space.

      After stopping such a statement, it shows up in the slow query log. Here is an example of such an entry, where the statement took more than 300 seconds before it was stopped, but the lock time is close to 0.

      1. Time: 190910 11:30:38
      2. User@Host: OWStudio[OWStudio] @ [192.168.184.6]
      3. Thread_id: 3381816 Schema: optovision QC_hit: No
      4. Query_time: 308.844872 Lock_time: 0.000713 Rows_sent: 0 Rows_examined: 0
      5. Rows_affected: 0
        SET timestamp=1568107838;
      6. WE-PeriodenAbgrenzung
        insert into findispyear(fyperi,fytyp,fywert,fyMenge,fyupdt,fylgrx,fymat,fywareh)
        select f1peri as Peri,'22. + WE aus Vorperioden' as Art,round(sum(atbwmg*atbwwt),4) as WEWert,
        sum(atbwmg) as fyMenge,
        date_format(current_date, '%Y%m%d')as fyupdt,
        f1lgrx as LgRx,f1mat as fymat,atzo01 as fywareh
        from werh01
        join werd01 on wezo01=wercid
        join atad01 on weatid=atpkey
        join w_findispmon01 on f1whse=atzo01 and f1zo05=atzo05
        where weendperi=f1peri
        and atbwdt <(f1peri*100)+1
        group by art,Peri,f1lgrx,f1mat,atzo01

      Any suggestions, how to continue?

      Best regards,

      Thomas Mischke

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            thomas.mischke Thomas Mischke
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated: