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

data directory with page compressed tables is slow to copy

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2
    • Fix Version/s: 10.2
    • Component/s: None
    • Labels:
      None
    • Environment:
      Linux Gentoo, MariaDB 10.2.23, ext4 filesystem, 10k/15k rotating enterprise disks

      Description

      I'm trying to migrate my mariadb data directory with several tables with page compression from 10k to 15k disks (both ext4 filesystem). I've tried stopping the mariadb process, then do a regular cp, but it's very slow. The data directory is just 100GB large, it starts very well, comes to 60GB, then it's become very slow, copying just a few hunder KB per second, taking forever to complete. I also tried using mariabackup to copy from one disk to another, but it came to 78GB, 4 cores running on 100% (mariabackup --parallel=4) but very slow progress.

      Found this: https://www.percona.com/blog/2017/11/20/innodb-page-compression/

      Is there anything that can be done to have some reasonable speeds?

      The tables are partitioned page compressed tables like:
      CREATE TABLE `cqrs_event` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `event_id` char(36) COLLATE utf8_unicode_ci NOT NULL,
      `event_date` datetime NOT NULL,
      `event_date_u` int(10) unsigned NOT NULL,
      `aggregate_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `aggregate_id` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
      `sequence_number` int(10) unsigned DEFAULT NULL,
      `payload_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `payload` longtext COLLATE utf8_unicode_ci NOT NULL,
      `metadata` longtext COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (`id`,`event_date`),
      KEY `ix_cqrs_event_aggregate` (`aggregate_type`,`aggregate_id`,`sequence_number`),
      KEY `ix_cqrs_event_dates` (`event_date`,`event_date_u`),
      KEY `ix_cqrs_event_id` (`event_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci `PAGE_COMPRESSED`=1
      PARTITION BY RANGE (to_days(`event_date`))
      (PARTITION `to_20190127` VALUES LESS THAN (737451) ENGINE = InnoDB,
      PARTITION `to_20190203` VALUES LESS THAN (737458) ENGINE = InnoDB,
      PARTITION `to_20190210` VALUES LESS THAN (737465) ENGINE = InnoDB,
      ...

      Thanks

        Attachments

          Activity

            People

            Assignee:
            marko Marko Mäkelä
            Reporter:
            hydrapolic Tomas Mozes
            Votes:
            1 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.