처리 - mysql 성능 최적화




mysql으로 매우 큰 데이터 처리하기 (2)

수십억 개의 행을 수집 할 때, 저장 하기 전에 데이터를 통합, 처리, 요약하는 것이 좋습니다 (가능한 경우). 원 데이터를 다시 가져와야한다고 생각하면 원시 데이터를 파일에 보관하십시오.

그렇게하면 대부분의 질문과 우려가 사라지고 처리 속도가 빨라집니다.

긴 게시물을 위해 유감스럽게 생각합니다!

~ 30 테이블 (InnoDB 엔진)을 포함하는 데이터베이스가 있습니다. 이 두 테이블 중 "트랜잭션"과 "시프트"만 매우 큽니다 (첫 번째 행은 150 만 개의 행을 가지며 시프트는 23k 행을가집니다). 이제 모든 것이 잘 작동하고 현재 데이터베이스 크기에 문제가 없습니다.

그러나 우리는 비슷한 데이터베이스 (동일한 데이터 유형, 디자인, ..)를 가질 것이지만 "트랜잭션"테이블은 약 10 억 개의 레코드 (하루에 약 2,300 만 트랜잭션)를 가질 것입니다. 우리는 MySQL에서 이와 같은 양의 데이터를 처리해야합니까? (읽기 및 쓰기 집중적입니다). Mysql (그리고 특히 InnoDB 엔진)이 수십억 개의 레코드로 잘 수행되는지 확인하기 위해 많은 관련 게시물을 읽었지 만 여전히 몇 가지 질문이 있습니다. 내가 읽은 관련 게시물 중 일부는 다음과 같습니다.

매우 큰 테이블의 성능을 향상시키기 위해 지금까지 내가 이해 한 바는 다음과 같다.

  1. (innoDB 테이블의 경우) innodb_buffer_pool_size (예 : RAM의 최대 80 %)가 증가합니다. 또한 percona 블로그에서 다른 MySQL 성능 튜닝 설정을 발견했습니다.
  2. 테이블에 적절한 인덱스가 있음 (쿼리에서 EXPLAN 사용)
  3. 테이블 분할하기
  4. MySQL 샤딩 또는 클러스터링

여기에 내 질문 / 혼란이있다 :

  • 파티셔닝에 관해서, 나는 우리가 그것을 사용해야하는지 아닌지 의문을 품는다. 많은 사람들이 테이블이 매우 커질 때 성능을 향상시킬 것을 제안했습니다. 다른 한편으로는 쿼리 성능을 향상시키지 않고 쿼리를 더 빨리 실행할 수 없다는 많은 게시물을 읽었습니다 (예 : here , here ). 또한 MySQL 레퍼런스 매뉴얼 에서 InnoDB 외래 키와 MySQL 파티셔닝이 호환되지 않는다는 것을 읽었습니다 (우리는 외래 키를 가지고 있습니다).

  • 인덱스에 관해서는 지금은 성능이 뛰어 났지만, 이해할 수있는 한, 매우 큰 테이블의 경우 색인 생성이 더 제한적입니다 (Kevin Bedell이 대답 에서 언급했듯이). 또한 인덱스는 읽기 속도를 높이고 쓰기 속도는 느려집니다 (삽입 / 업데이트). 그래서 우리가이 큰 DB를 갖게 될 새로운 유사한 프로젝트를 위해, 먼저 모든 데이터를 삽입 /로드 한 다음 인덱스를 생성해야합니까? (인서트 속도를 높이기 위해)

  • 큰 테이블 ( "트랜잭션"테이블)에서 파티셔닝을 사용할 수 없다면 성능을 향상시키기위한 대안은 무엇입니까? ( innodb_buffer_pool_size 와 같은 MySQl 변수 설정 제외). MySQL 클러스터를 사용해야합니까? (우리는 또한 많은 조인이있다)

편집하다

이것은 "transaction"이라는 이름의 가장 큰 테이블에 대한 show create table 문입니다.

  CREATE TABLE `transaction` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `terminal_transaction_id` int(11) NOT NULL,
 `fuel_terminal_id` int(11) NOT NULL,
 `fuel_terminal_serial` int(11) NOT NULL,
 `xboard_id` int(11) NOT NULL,
 `gas_station_id` int(11) NOT NULL,
 `operator_id` text NOT NULL,
 `shift_id` int(11) NOT NULL,
 `xboard_total_counter` int(11) NOT NULL,
 `fuel_type` int(11) NOT NULL,
 `start_fuel_time` int(11) NOT NULL,
 `end_fuel_time` int(11) DEFAULT NULL,
 `preset_amount` int(11) NOT NULL,
 `actual_amount` int(11) DEFAULT NULL,
 `fuel_cost` int(11) DEFAULT NULL,
 `payment_cost` int(11) DEFAULT NULL,
 `purchase_type` int(11) NOT NULL,
 `payment_ref_id` text,
 `unit_fuel_price` int(11) NOT NULL,
 `fuel_status_id` int(11) DEFAULT NULL,
 `fuel_mode_id` int(11) NOT NULL,
 `payment_result` int(11) NOT NULL,
 `card_pan` text,
 `state` int(11) DEFAULT NULL,
 `totalizer` int(11) NOT NULL DEFAULT '0',
 `shift_start_time` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `terminal_transaction_id` (`terminal_transaction_id`,`fuel_terminal_id`,`start_fuel_time`) USING BTREE,
 KEY `start_fuel_time_idx` (`start_fuel_time`),
 KEY `fuel_terminal_idx` (`fuel_terminal_id`),
 KEY `xboard_idx` (`xboard_id`),
 KEY `gas_station_id` (`gas_station_id`) USING BTREE,
 KEY `purchase_type` (`purchase_type`) USING BTREE,
 KEY `shift_start_time` (`shift_start_time`) USING BTREE,
 KEY `fuel_type` (`fuel_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1665335 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

시간 내 줘서 고마워,


  • MySQL이 수십억 개의 행에 대해 합리적으로 쿼리를 수행 할 수 있습니까? - MySQL은 수십억 개의 행을 처리 할 수 ​​있습니다. "합리적"은 쿼리에 따라 다릅니다. 그것들을 보자.

  • InnoDB (MySQL 5.5.8)는 수십억 행에 적합한 선택입니까? - 5.7은 약간의 개선이 있었지만 5.5 세는 거의 6 살 이었음에도 불구하고 더 이상 지원되지 않습니다.

  • 수십억 개의 행을위한 최고의 데이터 저장소 - 'Engine'을 의미한다면 InnoDB.

  • 성능이 떨어지기 전에 MySQL 데이터베이스가 얼마나 커질 수 있습니까? 다시 말해 쿼리에 따라 다릅니다. 나는 여러분에게 붕괴 될 1K 행의 테이블을 보여줄 수 있습니다. 나는 험담하는 10 억 개의 테이블을 가지고 일했습니다.

  • MySQL이 대형 테이블에서 느려질 수있는 이유는 무엇입니까? - 범위 스캔은 느린 부분 인 I / O로 이어집니다.

  • MySQL은 약 3 억 개의 레코드를 저장할 테이블을 처리 할 수 ​​있습니까? - 다시, 네. 한도는 약 1 조 행 정도입니다.

  • (innoDB 테이블의 경우) innodb_buffer_pool_size (예 : RAM의 최대 80 %)가 증가합니다. 또한, 여기 percona 블로그에서 다른 MySQL 성능 튜닝 설정을 발견했습니다 - 예

  • 테이블에 적절한 인덱스가있는 경우 (쿼리에서 EXPLAN 사용) - 잘 보도록하겠습니다. 이 중요한 영역에서 많은 실수가있을 수 있습니다.

  • 테이블 파티셔닝 - "파티셔닝은 만병 통치약이 아닙니다!" 내 블로그 에 그걸 들려 준다.

  • MySQL Sharding - 현재 DIY입니다.

  • MySQL 클러스터링 - 현재 가장 좋은 대답은 일부 Galera 기반 옵션 (PXC, MariaDB 10, DIY with Oracle)입니다.

  • 파티셔닝은 FOREIGN KEY 또는 "글로벌" UNIQUE 지원하지 않습니다.

  • 당신이 말하는 규모의 UUID는 시스템 속도를 늦추지 않고 실제로 죽일 것입니다. 유형 1 UUID 가 문제를 해결할 수 있습니다.

  • 삽입 및 색인 생성 속도 - 단일 답변을 제공 할 변형이 너무 많습니다. 잠정적 인 CREATE TABLE 과 데이터를 입력하는 방법을 살펴 보겠습니다.

  • 많은 조인 - "표준화하지만 지나치게 표준화하지 않습니다." 특히 datetime이나 float 또는 다른 "연속"값을 정규화하지 마십시오.

  • 요약 테이블 작성

  • 하루에 2,300 만 트랜잭션 - 2.3M 인서트 (30 / 초)이면 성능상의 문제가별로 없습니다. 더 복잡한 경우 RAID, SSD, 일괄 처리 등이 필요할 수 있습니다.

  • 많은 양의 데이터를 다루십시오 - 대부분의 활동이 "최근"행을 사용하는 경우 buffer_pool은 활동을 적절하게 '캐싱'하여 I / O를 피합니다. 활동이 "임의"이면 MySQL (또는 다른 사람)이 I / O 문제를 갖습니다.





partitioning