워크로드가 IO에 묶여 있을 때 mariadb에서 대량의 데이터를 빠르게 가져오는 방법

워크로드가 IO에 묶여 있을 때 mariadb에서 대량의 데이터를 빠르게 가져오는 방법

중요한 정보:

데이터베이스 테이블 'cdrs'가 있습니다.

+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | bigint(12)    | NO   | PRI | NULL    | auto_increment |
| server_id       | tinyint(2)    | NO   |     | 0       |                |
| cdr_id          | bigint(13)    | NO   | MUL | 0       |                |
| user_id         | int(11)       | NO   | MUL | 0       |                |
| transaction_id  | int(11)       | YES  | MUL | NULL    |                |
| sip_id          | int(11)       | NO   | MUL | 0       |                |
| call_type       | tinyint(2)    | YES  | MUL | NULL    |                |
| did_from        | char(24)      | YES  |     | NULL    |                |
| did_from_alias  | bigint(18)    | YES  | MUL | 0       |                |
| did_to          | char(24)      | YES  |     | NULL    |                |
| did_to_alias    | bigint(18)    | YES  | MUL | 0       |                |
| call_status     | char(12)      | YES  | MUL | NULL    |                |
| start_time      | int(11)       | NO   | PRI | 0       |                |
| duration        | decimal(13,3) | YES  |     | NULL    |                |
| billed_duration | decimal(13,3) | NO   |     | 0.000   |                |
| rate            | decimal(10,4) | YES  |     | NULL    |                |
| amount          | decimal(10,4) | YES  |     | NULL    |                |
| usf             | decimal(10,4) | YES  |     | NULL    |                |
| total           | decimal(10,4) | YES  | MUL | NULL    |                |
| country         | varchar(96)   | YES  |     | NULL    |                |
| country_id      | int(8)        | NO   |     | 0       |                |
| code            | varchar(8)    | NO   |     |         |                |
+-----------------+---------------+------+-----+---------+----------------+

다음 인덱스 사용

+---------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| cdrs |          0 | PRIMARY          |            1 | id             | A         |   573346816 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          0 | PRIMARY          |            2 | start_time     | A         |   573346816 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_cdr_id         |            1 | cdr_id         | A         |   573346816 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_user_id        |            1 | user_id        | A         |      158909 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_call_type      |            1 | call_type      | A         |       19887 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_transaction_id |            1 | transaction_id | A         |   143336704 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_total          |            1 | total          | A         |      163953 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_start_time     |            1 | start_time     | A         |    24928122 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_call_status    |            1 | call_status    | A         |        9877 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_sip_id         |            1 | sip_id         | A         |      353481 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_did_to         |            1 | did_to_alias   | A         |   286673408 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_did_from       |            1 | did_from_alias | A         |    57334681 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+---------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

문제:

현재 이 테이블에는 int의 최대값보다 많은 행이 있습니다(따라서 ID가 bigint인 이유).

이 테이블은 매일 GB 단위로 크기가 커집니다.기업의 예측에 따라 GB는 10 GB로, 최종적으로 수백 GB가 될 것입니다.

현재 데이터베이스는 raid 10 SSD 상에서 실행되고 있습니다.

다음과 같은 시나리오를 취합니다.

이번 달 초순인데 고객이 지난달의 기록을 조회해서 고객에게 청구할 수 있게 하려고 합니다.

이 쿼리는 Innodb 버퍼에 데이터가 존재하지 않기 때문에 직접 IO 바인딩됩니다.이 쿼리는 처음 쿼리되기 때문입니다.

예를 들어, 우리의 쿼리는

SELECT * FROM cdrs WHERE user_id='<SOME_USER_ID' AND start_time>=1654099200 AND start_time<1656691200

따라서 월초부터 월말까지 이 사용자의 모든 통화 레코드를 취득합니다.

테스트에서는 1개의 클라이언트에 대해 52,627,431개의 행이 생성되어 쿼리 실행에만 50초가 소요되었습니다.

SELECT Count(*) FROM cdrs WHERE user_id='<SOME_USER_ID>' AND start_time>=1654099200 AND start_time<1656691200;

여러 데이터베이스를 찾아봤는데 어떤 방향으로 가야 할지 잘 모르겠습니다만, 현재 1시간 분량의 레코드를 끌어내는 데만 30분이 걸리는 고객도 있습니다.

델이 시도한 솔루션

요약표 사용

하나의 제안이 이미 사용하고 있는 요약 테이블을 사용하는 것을 알고 있지만, 이 표에 포함된 실제 데이터가 고객에게 필요하기 때문에 이 경우 도움이 되지 않습니다.

테이블을 분할하다

테이블을 월별로 분할했지만 결과는 여전히 느리다

mariadb에서 칼럼스토어 엔진을 사용하려고 했습니다.이것에 의해, 일부의 분야에서 퍼포먼스가 큰폭으로 향상했습니다만, 특별히 고객에게 CDR를 제공하는 것은 아닙니다.

레디스는 번개처럼 빠르지만 테이블은 1.1을 차지하기 때문에 레디스를 사용할 수 있으면 좋겠습니다.현재 T개의 데이터가 있으며 앞으로도 계속 증가할 것입니다.

솔루션에 대한 열망과 개방성

프라이머리 키

WHERE user_id='<SOME_USER_ID>'
  AND start_time>=1654099200
  AND start_time< 1656691200;

에 의해 처리되는 것이 최선이다.

PRIMARY KEY(user_id, start_time) -- in this order.

user가 정확히 한 "user"를 의 행을 수 start_time,그리고나서

PRIMARY KEY(user_id, start_time, id) -- in this order.
INDEX(id)

그 이외의 경우는, 을 삭제하는 것을 검토해 주세요.id.

PK의 시작은 다음과 같이 시작됨user_id는 특정 사용자의 모든 행을 클러스터화하여 (및 유사한) 쿼리를 고속화합니다.

PK의 두 번째 부분은start_time그것에 큰 도움이 되다BETWEEN.

파티션 - "잘못된" 방법들이 많이 있습니다.PARTITIONing자세한 내용을 알려주시기 바랍니다.일반적으로 파티셔닝은 속도 향상을 제공하지 않습니다.

사용하는 주된 이유PARTITIONing"오래된" 데이터를 삭제할 필요가 있는 경우DROP PARTITION보다 엄청나게 빠르다DELETEing수백만의 행.

데이터 크기

여기서의 코멘트의 대부분은, 테이블의 디스크 용량에 관한 것입니다.

데이터를 적절히 「클러스터화」함으로써, 테이블이 RAM보다 훨씬 큰 경우, I/O가 삭감됩니다.의 설정은 무엇입니까?innodb_buffer_pool_size메모리 용량이 얼마나 됩니까?테이블 삭제를 시작하기 전에 테이블에는 몇 GB가 있습니까?

클러스터링을 통해 10배의 속도 향상을 실현할 수 있습니다.

CHAR - 고정 길이 데이터 유형이며, 데이터가 지정된 길이에 '항상'인 경우에만 사용해야 합니다.그렇지 않으면 공간을 낭비하게 됩니다.

INDEXes - 많은 열에 대해 색인을 맹목적으로 추가하지 않습니다.필요한 것만 가지세요."니즈"는 실행할 쿼리에서 나옵니다.필요에 따라서, 「복합」인덱스를 사용하는 것을 검토해 주세요.

대부분인 것 같다SELECTs포함되다WHERE user_id = ...만약 그것이 맞다면, 나는 많은 인덱스가 '복합'이 될 것이라고 예상한다.user_id.

국가 - 4바이트 INT 및 멀티바이트 문자열은 포함하지 않습니다.단순히CHAR(2)표준 "country_code" 및 필요한 경우 문자열에 매핑할 조회 테이블을 지정합니다.

Midnight - 일별 또는 월별 보고서를 작성할 때 자정에 걸쳐 유출되는 콜은 어떻게 해야 합니까?

요약 테이블 - 요약 테이블이 데이터에 매우 적합해야 합니다.보고서에 필요한 데이터를 설명해 주세요.요약표 재설계를 도와드리겠습니다.

요약이 해당 달의 모든 콜에 대한 모든 세부사항인 경우, 이는 "요약"이 아닙니다. 클러스터된 PK를 통해 10배 향상됩니다.

"summary"가 일일 소계를 의미한다면 자세한 내용을 살펴보겠습니다.

숫자 유형 - INT는 4바이트, BIGINT는 8바이트입니다.의 "13"bigint(13)의미가 없습니다.값이 13자리까지로 제한되어 있는 경우는,DECIMAL(13,0)6시 정각「 」도 참조해 .MEDIUMINT ★★★★★★★★★★★★★★★★★」SMALLINTDECIMAL(13,3)소요됩니다.7바이트가 소요됩니다.

Engine -- InnoDB를 사용합니다.

언급URL : https://stackoverflow.com/questions/73058165/how-to-pull-a-large-amount-of-data-from-mariadb-in-a-fast-manner-when-workload-i