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

data directory with page compressed tables is slow to copy

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2
    • 10.2
    • None
    • None
    • 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

            marko Marko Mäkelä
            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.