[MDEV-19554] data directory with page compressed tables is slow to copy Created: 2019-05-22  Updated: 2019-05-27

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.2
Fix Version/s: 10.2

Type: Bug Priority: Major
Reporter: Tomas Mozes Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 1
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


Generated at Thu Feb 08 08:52:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.