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

Row deletes from Aria table very slow

Details

    Description

      DELETE LOW_PRIORITY QUICK FROM tablename WHERE col_name="XX" took 40 minutes to delete 16M adjacent rows from the front of a fully-indexed, ordered table with no other activity interfering with the operation. That seems very slow! (Many other operations, e.g. INSERT INTO WHERE (SELECT...), seem very slow also, so slow I keep checking SMART to see whether the disc is on the verge of collapse (smartctl claims it's not).

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          danblack Daniel Black added a comment -

          What mariadb version? Are sure it's indexed? What does EXPLAIN DELETE .. WHERE.. Show? What's the table structure? (SHOW CREATE TABLE tblname)

          danblack Daniel Black added a comment - What mariadb version? Are sure it's indexed? What does EXPLAIN DELETE .. WHERE.. Show? What's the table structure? (SHOW CREATE TABLE tblname)
          danblack Daniel Black added a comment -

          Are you using so much memory mysqld is swapping?

          danblack Daniel Black added a comment - Are you using so much memory mysqld is swapping?
          MMacD M MacDonald added a comment - - edited

          v10.1.22.

          It's definitely indexed, because I wondered the same thing (even though I'd created it myself!) and did a DESCRIBE several times, half-expecting to see that I'd hallucinated the indexing.

          I didn't run EXPLAIN because the delete was so straightforward: DELETE FROM raw_tags WHERE ParType='pt'.

          The table structure is quite simple:

          raw_tags | CREATE TABLE `raw_tags` (
          `ParentType` char(2) NOT NULL,
          `ParentID` bigint(20) NOT NULL,
          `K` varchar(64) NOT NULL DEFAULT '',
          `V` varchar(1024) NOT NULL DEFAULT '',
          KEY `ParentType` (`ParentType`,`ParentID`),
          KEY `K` (`K`),
          KEY `V` (`V`),
          KEY `ParentID` (`ParentID`)
          ) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1

          I said that the records were at the front of the table, but that was me being foggy: they comprised the largest block of records, and were in "the middle": after a block about a third that size and before a very much smaller (1.5M) block. They were all in that one big block, no scattering at all. So it should have been: mark the FBA and LBA for the block to be overwritten, do the overwrite, prune the Btree, decrement the FBAs for the moved block, and adjust the table length. Easy-peasy.

          No, it wasn't swapping. It never used more than 1 of the 8 cores and there was plenty uncommitted memory available. It could have loaded both files into memory (6.5GB, 5GB) and done the work there, but doesn't seem to have loaded either one.

          MMacD M MacDonald added a comment - - edited v10.1.22. It's definitely indexed, because I wondered the same thing (even though I'd created it myself!) and did a DESCRIBE several times, half-expecting to see that I'd hallucinated the indexing. I didn't run EXPLAIN because the delete was so straightforward: DELETE FROM raw_tags WHERE ParType='pt'. The table structure is quite simple: raw_tags | CREATE TABLE `raw_tags` ( `ParentType` char(2) NOT NULL, `ParentID` bigint(20) NOT NULL, `K` varchar(64) NOT NULL DEFAULT '', `V` varchar(1024) NOT NULL DEFAULT '', KEY `ParentType` (`ParentType`,`ParentID`), KEY `K` (`K`), KEY `V` (`V`), KEY `ParentID` (`ParentID`) ) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 I said that the records were at the front of the table, but that was me being foggy: they comprised the largest block of records, and were in "the middle": after a block about a third that size and before a very much smaller (1.5M) block. They were all in that one big block, no scattering at all. So it should have been: mark the FBA and LBA for the block to be overwritten, do the overwrite, prune the Btree, decrement the FBAs for the moved block, and adjust the table length. Easy-peasy. No, it wasn't swapping. It never used more than 1 of the 8 cores and there was plenty uncommitted memory available. It could have loaded both files into memory (6.5GB, 5GB) and done the work there, but doesn't seem to have loaded either one.

          People

            Unassigned Unassigned
            MMacD M MacDonald
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.