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

Slow deletes if number of deleted rows smaller then LIMIT

    XMLWordPrintable

Details

    Description

      Have two tables RLOG, RREC which basically Looks like this:

       
      CREATE TABLE rrec (
        RREC_TIMESTAMP_DT varchar(25) NOT NULL,
        RREC_SESSION_UID varchar(70) NOT NULL,
        ... (shorten) ...
        PRIMARY KEY (RREC_SESSION_UID),
       ...
          )
      ENGINE = INNODB
      AVG_ROW_LENGTH = 197
      CHARACTER SET latin1
      COLLATE latin1_general_cs
      ROW_FORMAT = COMPRESSED;
      

      and

      CREATE TABLE rlog (
        RLOG_ID varchar(70) NOT NULL,
        RLOG_LOGENTRY longtext DEFAULT NULL,
        PRIMARY KEY (RLOG_ID),
        UNIQUE INDEX IDX_UK_rlog (RLOG_ID),
        CONSTRAINT RLOG_RREC_FK FOREIGN KEY (RLOG_ID)
        REFERENCES rrec (RREC_SESSION_UID) ON DELETE CASCADE ON UPDATE CASCADE
      )
      ENGINE = INNODB
      AVG_ROW_LENGTH = 2343
      CHARACTER SET latin1
      COLLATE latin1_general_cs
      ROW_FORMAT = COMPRESSED;
      

      (see attachments for full table creation SQL)

      Both tables have several rows:

      RLOG: 1 MIO
      RREC: 1,3 MIO

      From time to time i want to delete some entrys from RLOG table:

       DELETE FROM RLOG WHERE RLOG_ID in (SELECT RREC_SESSION_UID FROM RREC WHERE RREC_TIMESTAMP_DT < '2016-01-01') LIMIT 1000;
      

      As long as at least 1000 rows are deleted everything is fine:

      1000 rows deleted [0,107s]
      

      but if i delete less then 1000 rows the delete Operation takes very long time:

      847 rows deleted [24,270s]
      

      and even worse if nothing is deleted it takes Long time also:

      0 rows deleted [24,090s]
      

      Profiling of such a statement shows the following:

      Step Time Percent

      updating |51,634785 |99,86|
      query end |0,033120 |0,06|
      statistics |0,014102 |0,03|
      end |0,009650 |0,02|
      Unlocking tables |0,005223 |0,01|
      freeing items |0,003979 |0,01|
      updating status |0,001728 |0,00|
      starting |0,001212 |0,00|
      init |0,001178 |0,00|
      Opening tables |0,000821 |0,00|
      preparing |0,000791 |0,00|
      closing tables |0,000760 |0,00|
      After opening tables |0,000380 |0,00|
      checking permissions |0,000288 |0,00|
      Table lock |0,000227 |0,00|
      cleaning up |0,000024 |0,00|
      System lock |0,000020 |0,00|
      optimizing |0,000013 |0,00|

      If you like I can provide you my testdata (6GB).

      Attachments

        Issue Links

          Activity

            People

              alice Alice Sherepa
              ThomasHuppertz Thomas Huppertz
              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.