다중 테이블 MariaDB 데이터베이스 파티셔닝 솔루션

다중 테이블 MariaDB 데이터베이스 파티셔닝 솔루션

일정 기간 동안 데이터를 아카이브/삭제하기 위해 일부 데이터베이스 테이블에서 파티셔닝 문제를 가장 잘 해결하는 방법에 대한 지침을 찾고 있습니다.그 주된 이유는 데이터베이스 크기의 몇 가지 문제를 해결하기 위해서입니다.

시간이 지남에 따라 증가하는 원격 측정 데이터와 유사한 데이터를 생각할 수 있지만, 데이터베이스에 입력된 데이터는 충돌하는 데이터의 최초 10~15분 이외에는 변경되지 않습니다.이 경우 응용 프로그램에서 최신 기록(최대 15분)을 업데이트해야 합니다.

현재 데이터베이스 크기는 500GB에 육박하고 있으며 3개 도시의 3x 노드 Galera 클러스터에 걸쳐 NVMe 스토리지에 배치되어 있습니다.백업은 점점 더 커지고 있으며 노드 간에 SST가 필요한 경우 이 작업을 완료하는 데 몇 시간이 걸릴 수 있습니다. 이는 이상적인 작업과는 다릅니다.

이 문제에 대처하는 방법은 아카이브를 사용하는 것입니다.이 때, 이력 데이터를, 속도가 느린 다른 서버에 오프보드(연 1회 정도)하는 것으로, 1회 백업하면 12개월 동안은 변경되지 않습니다.이력 데이터는 거의 액세스 할 수 없습니다.또, 「최근」데이터에 크게 의존하고 있는 실가동 서버가 아니고, 특정의 날짜보다 오래된 경우, 어카이브 서버의 조회가 애플리케이션에 의해서 처리됩니다.

고객 한 명당 3개의 테이블이 있으며, 그들은 일종의 가계의 형태로 서로를 참조합니다.테이블에는 외부 키는 없지만 서로 참조가 유지되며 JOIN 쿼리에서 사용됩니다.예: 요약 테이블은 계층의 맨 위에 있으며 "이벤트"당 하나의 레코드를 보유합니다.이 아래는 상세 테이블이며 요약 이벤트 아래에 1-10개의 상세 레코드가 있을 수 있습니다.아래는 상세 레코드당0 ~ 10개의 레코드를 포함할 수 있는 숫자 테이블입니다.

아래 표 데이터 생성

CREATE TABLE `summary_X` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start_utc` datetime DEFAULT NULL,
  `end_utc` datetime DEFAULT NULL,
  `total_duration` smallint(6) DEFAULT NULL,
  `legs` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `start_utc` (`start_utc`)
) ENGINE=InnoDB

CREATE TABLE `details_X` (
  `xid` bigint(20) NOT NULL AUTO_INCREMENT,
  `id` int(11) NOT NULL,
  `duration` smallint(6) DEFAULT NULL,
  `start_utc` timestamp NULL DEFAULT NULL,
  `end_utc` timestamp NULL DEFAULT NULL,
  `event` varchar(2) DEFAULT NULL,
  `event_time` smallint(6) DEFAULT NULL,
  `event_a` varchar(7) DEFAULT NULL,
  `event_b` varchar(7) DEFAULT NULL,
  `ani` varchar(20) DEFAULT NULL,
  `dnis` varchar(10) DEFAULT NULL,
  `first_time` varchar(30) DEFAULT NULL,
  `final_time` varchar(30) DEFAULT NULL,
  `digits_count` int(2) DEFAULT 0,
  `sys_a` varchar(3) DEFAULT NULL,
  `sys_b` varchar(3) DEFAULT NULL,
  `log_id_a` varchar(12) DEFAULT NULL,
  `seq_a` varchar(1) DEFAULT NULL,
  `log_id_b` varchar(12) DEFAULT NULL,
  `seq_b` varchar(1) DEFAULT NULL,
  `assoc_log_id_a` varchar(12) DEFAULT NULL,
  `assoc_log_id_b` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`xid`),
  KEY `start_utc` (`start_utc`),
  KEY `end_utc` (`end_utc`),
  KEY `event_a` (`event_a`),
  KEY `event_b` (`event_b`),
  KEY `id` (`id`),
  KEY `final_digits` (`final_digits`),
  KEY `log_id_a` (`log_id_a`),
  KEY `log_id_b` (`log_id_b`)
) ENGINE=InnoDB

CREATE TABLE `digits_X` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `leg_id` bigint(20) DEFAULT NULL,
  `sequence` int(2) NOT NULL,
  `digits` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `digits` (`digits`),
  KEY `leg_id` (`leg_id`)
) ENGINE=InnoDB

처음에는 연도에 파티션을 분할하는 것이 매우 간단하다고 생각했지만, 숫자 테이블에 날짜 열이 없기 때문에 이 레코드는 매핑된 상세 레코드에서 분리되어 아카이브 서버의 JOIN에서 더 이상 일치하지 않을 수 있습니다.또, 요약에 관해서도 같은 문제가 발생할 가능성이 있어, 「상세」레코드상의 타임스탬프가 몇년에 걸치는 일이 있습니다.예: 요약 이벤트는 2021-12-31 23:55:00에 시작합니다.첫 번째 상세 레코드는 동일한 타임스탬프이며, 그 다음 동일한 이벤트 아래의 다음 상세 레코드는 2022-01-01 00:11:00가 될 수 있습니다.2021 파티션이 다른 서버에 아카이브된 경우 2022 세부 정보는 연결이 끊기고 2021 요약 이벤트에 더 이상 가입하지 않습니다.

한 가지 방법은 파티션을 전혀 분할하지 않고 SELECT/INSERT/DELETE를 실행하는 것입니다.이것은 데이터 볼륨에서는 실용적이지 않습니다.테이블에 따라서는 연간 3000~40M 행이 있기 때문에 자원을 많이 소비합니다.또한 400명 이상의 고객이 각각 자신의 테이블 세트를 가지고 있습니다.

또 하나 생각한 것은 3개의 테이블에 「년」컬럼을 추가하는 것입니다만, 모든 관련 레코드는 같은 파티션/서버에 격납할 수 있도록, 「년」컬럼을 포함한 것입니다만, 이것은 공간 낭비라고 생각되기 때문에, 보다 좋은 방법이 있을 것입니다.

어떤 생각이나 안내라도 해주시면 감사하겠습니다.

파티셔닝을 추가하려면 테이블 전체를 복사해야 합니다.여기에는 다운타임과 디스크 공간이 필요합니다.그걸 감수할 수 있다면...

PARTITION BY RANGE(...)예를 들어, 이 표현은 다음과 같은 것들이 있다.TO_DAYS(...) (가능성이 있다)TO_SECONDS(...)그런 다음 cron 작업을 설정하여 정기적으로 새 파티션을 추가합니다(자동화된 파티션은 없습니다).그리고 가장 오래된 파티션을 분리합니다.자세한 내용은 파티션을 참조하십시오(TO_DAYS에는 '년' 열이 필요하지 않습니다).

파티셔닝은 테이블 아래에 여러 하위 테이블로 구현됩니다."수송 가능한 테이블 영역"을 사용하면 큰 테이블에서 파티션을 분리하여 자체 테이블로 만들 수 있습니다.이 시점에서 다른 서버로 자유롭게 이동할 수 있습니다.

당신과 같은 상황에서는 다음과 같은 것을 생각할 수 있습니다.

• 보관을 위해 미가공 데이터를 파일에 씁니다(약 1일 1회).
• 단시간만 존속하는 테이블에 삽입합니다.이는 어떤 방법으로든 자주 삭제됩니다.
• "정규화" 테이블 업데이트
• 데이터를 요약 표로 "요약"합니다.여기서 각 행 집합은 1시간(또는 의미가 있는 것)에 걸쳐 있습니다.
• 요약 테이블에서 "보고서"를 작성합니다.

각 파티션에는 추가 5.5가 필요합니다.MB(평균)이므로 파티션을 많이 만들지 마십시오.아니면 각각 15분 분량의 데이터가 포함된 2개만 필요합니까?

그 동안 스키마를 주의 깊게 살펴보도록 하겠습니다. 수 있다INT는 (4바이트)로 됩니다.SMALLINT(2바이트).더 많은 것을 정상화할 수 있습니다.

digits_count int(2) -- 4바이트는 4바이트입니다.INT »(2)MySQL 8은 MySQL 8입니다.(MariaDB) 1바이트만 될 것 요.TINYINT UNSIGNED(범위: 0~255)

이것은 로그 정보이므로 Daylight Savings wrt에 주의해 주십시오.DATETIME(1년에 1시간이 없어지고 1시간이 반복됩니다.)이 문제는 에 의해 발생하지 않습니다.TIMESTAMP. 각각 5바이트가 소요됩니다(단, 소수점을 포함하지 않는 한).

(조회를 보지 않고서는 불필요한 인덱스에 대해 조언할 수 없습니다.) SHOW TABLE STATUS모든 인덱스가 사용하고 있는 공간의 크기를 나타냅니다.

테이블 3개가 비슷한 크기인가요?

"고정" - 2개 이상의 파티션이 필요합니다.하나는 꽉 차 있고(0~100% 가득 차 있음) 오래된 파티션(100% 가득 차 있음)

"연간 3000만~4000만 행" x 400명의 고객.그러면 초당 최대 500개의 행을 삽입할 수 있습니까?한 번에 한 줄씩 삽입됩니까?고속 섭취

삭제 및 선택이 삽입보다 많습니까?그리고/또는 1열 이상입니까? (자세한 내용은 고객님이 가지고 있거나 가지고 있을 것 같은 다른 문제에 대한 도움을 요청하고 있습니다.)삭제 및 파티셔닝이 없는 경우에도 사용 가능한 공간이 생성되고 다시 사용되기 때문에 디스크 증가 속도가 느려집니다.("Rince and repeating")")

파티션을 분할하지 않은 경우 "대규모 삭제"를 참조하십시오. 단...테이블에서 데이터를 삭제해도 디스크 설치 공간이 줄어들지 않습니다.다만, 각 「고객」이 데이터의 400분의 1을 소유하고 있는 경우, (물론) 각 고객을 개별적으로 취급하는 경우, 디스크에는 문제가 없을 가능성이 있습니다.

생각할 시간을 많이 줬어제 질문에 대답하세요. 더 조언이 있을지도 몰라요.

언급URL : https://stackoverflow.com/questions/73524165/multi-table-mariadb-database-partitioning-solution