쿼리 성능 10000배 향상 시키는 방법

쿼리 성능 10000배 향상 시키는 방법

 

시스템 속도 향상

사진 제공: Hello I'm Nik on Unsplash

시스템에서 더 많은 데이터를 얻을수록 응용 프로그램이 크롤링 속도가 느려지는 것을 알 수 있습니까? 당신은 혼자가 아닙니다. 우리는 모두 조기 최적화에 대한 경고를 들었지만 어느 시점에서 시스템 성능을 개선하는 방법을 연구하는 데 시간을 할애해야 할 것입니다.

우리는 최근 Sky Ledge에서 이러한 상황에 직면했습니다. 시계열 데이터를 검색하는 간단한 쿼리는 스테이징 환경에서 실행하는 데 30초가 걸렸습니다. 관심 있는 열에 대한 인덱스가 있는 간단한 쿼리였습니다. 요청이 30초가 아니라 1초 이내에 완료될 것으로 예상합니다.

우리는 우수한 사용자 경험을 자랑스럽게 생각합니다. 그래프가 렌더링될 때까지 30초를 기다리면 UX가 매우 열악하므로 탐색 및 데이터베이스 최적화가 필요한 시점이었습니다.

문맥

Sky Ledge는 사용자가 신속하게 제어실 경험을 생성하여 운영에 대한 통찰력을 얻을 수 있도록 도와주는 플랫폼입니다.

Sky Ledge의 기본 부분 중 하나는 자산 API로, 이를 통해 현실 세계의 개체에서 데이터를 매우 쉽게 추적하고 시각화할 수 있습니다. 자산은 실제 개체(예: 차량)를 나타내는 Sky Ledge 내의 개체입니다. 메트릭은 자산과 연결된 데이터 스트림입니다. 메트릭의 예로는 속도, 고도, 온도 등이 있습니다. 각 메트릭은 연결된 타임스탬프가 있는 자산에 대해 단일 값을 추적합니다.

작동 중인 스카이 렛지

지표 데이터베이스는 다음과 같습니다(Postgres를 실행하는 AWS RDS 사용).

create table asset_metric
(
asset_id. uuid not null,
metric_name text,
timestamp timestamptz,
value double precision
);

에 대한 인덱스도 있습니다. asset_metric 테이블:

create index on asset_metric (timestamp, asset_id, name);

문제

문제는 스테이징 환경에서 클라이언트용 데모를 만들 때 발생했습니다. 데모의 일부로 Sky Ledge 플랫폼 내 자산의 온도 데이터를 표시했습니다.

온도 데이터를 검색하기 위해 데이터베이스에 다음 쿼리를 수행합니다.

select * from asset_metric
where
asset_id = 'abc123' and metric_name = 'speed'
order by timestamp desc
limit 1000;

이 쿼리는 완료하는 데 지속적으로 30초 이상 걸렸습니다. 이것은 간단한 쿼리이며 이 테이블에 인덱스가 있다는 것을 알고 있었습니다. 원인은 즉시 밝혀지지 않았습니다.

데이터베이스에 연결

첫 번째 단계는 데이터베이스에 연결하는 것이었습니다. 우리는 보안을 매우 중요하게 생각하므로 스테이징 및 프로덕션 데이터베이스는 모두 AWS의 프라이빗 VPC에 잠겨 있으며 퍼블릭 인터넷에 액세스할 수 없습니다.

스테이징 데이터베이스에 연결하기 위해 SSH 점프 박스를 설정했습니다. 자세한 방법은 여기를 참조하십시오(프로덕션 DB에는 이 접근 방식을 권장하지 않습니다).

우리의 데이터는 어떻게 생겼습니까?

다음으로 데이터 분포가 문제에 기여하는지 여부를 조사하고 싶었습니다. 문제 없이 수행되는 스테이징에 대한 다른 데모가 있었기 때문에 속도 저하가 특정 자산으로 제한되었습니다.

7,000,000개의 행이 있습니다. asset_metrics 데이터 베이스. 작지는 않지만 우리가 보고 있는 것보다 더 나은 성능을 기대합니다. 700만 행은 106개의 서로 다른 자산에 분산되어 있습니다. 또한 710개의 자산/메트릭 쌍(속도, 온도, 고도 등)이 있습니다.

Rows: 7,050,951
Unique assets: 106
Asset / metric pairs: 710

다음으로 데이터 분포를 이해하기 위해 몇 가지 쿼리를 실행했습니다. 데이터의 84%는 스테이징 환경에서 단 10개의 자산과 연결됩니다. 데이터 세트가 매우 치우쳐 있습니다.

자산별 데이터베이스 행 분포

자산/메트릭 쌍별 데이터베이스 행 분포

다양한 자산/메트릭 쌍 테스트

단일 자산/메트릭 쌍에 대한 쿼리를 테스트하는 대신 9개의 자산/메트릭 쌍 목록을 컴파일했습니다. 이는 전반적으로 성능을 잘 나타냅니다. 이것들은:

+-------+-------------+--------+
| Asset | Metric | Rows |
+-------+-------------+--------+
| 1 | speed | 169000 |
| 2 | speed | 100000 |
| 3 | speed | 75000 |
| 4 | speed | 20000 |
| 5 | speed | 10000 |
| 6 | speed | 5000 |
| 7 | speed | 2000 |
| 8 | temperature | 700 |
| 9 | speed | 400 |
+-------+-------------+--------+

매우 자주 업데이트되는 측정항목에서 매우 드물게 업데이트되는 측정항목에 이르기까지 다양합니다. 이는 문제가 전반적으로 발생하는지 또는 특정 상황에서만 발생하는지 알려주는 데 도움이 됩니다.

쿼리 성능을 측정한 방법

점프 박스를 통해 데이터베이스에 연결할 때 네트워크 대기 시간이 왕복 쿼리 시간에 영향을 미칩니다. 대신 내가 사용한 explain analyse DB가 쿼리를 실행하는 데 걸린 시간을 측정합니다. 이를 통해 네트워크의 변동성에 대해 걱정하지 않고 데이터베이스에서 직접 성능을 측정할 수 있었습니다.

현재 쿼리

기준선을 얻기 위해 9개의 자산/메트릭 쌍 각각에 대해 쿼리를 실행했습니다.

결과는 나를 놀라게 했다. 9개의 경우 중 8개의 경우 쿼리 플래너가 역방향 인덱스 스캔을 사용했습니다. 시간이 걸려서 순차 스캔인 줄 알았습니다. 직관은 훌륭하지만 직관을 확인하는 데 도움이 됩니다. 인덱스가 사용되지 않은 이유에 대해 고민하느라 시간을 낭비하는 대신, 이제 문제가 인덱스의 성능이라는 것을 알았습니다.

순차 스캔을 사용한 유일한 시간은 온도 측정 기준이었습니다. 온도 메트릭보다 속도가 훨씬 더 빠르기 때문에 그 이유가 될 수 있습니다.

이미 인덱스 스캔을 사용하고 있으며 인덱스 스캔이 빠릅니다.™그렇다면 가장 빈번한 측정항목을 제외한 모든 측정항목의 결과가 느린 이유는 무엇입니까?

인덱스 되돌리기

첫 번째 관찰은 데이터베이스가 역방향 인덱스 스캔을 수행하고 있다는 것입니다. 역방향 스캔은 다중 열 인덱스(우리가 가지고 있는 것)에 대한 표준 인덱스 스캔보다 느릴 수 있습니다.

우리가 거의 항상 최신 데이터를 쿼리한다는 점을 감안할 때( order by timestamp desc ), 타임스탬프가 내림차순으로 인덱스를 정렬하는 것이 논리적으로 보입니다. 그래서 새로운 인덱스를 만들었습니다.

create index on asset_metric(timestamp desc, asset_id, metric_name)

쿼리를 다시 실행했습니다.

온도 메트릭에 대한 변경 사항은 없지만(여전히 순차 스캔) 다른 곳에서는 진행됩니다! 정방향 인덱스 스캔으로 변경하여 다른 느린 자산/메트릭 쌍에 대한 쿼리 성능을 상당히 개선했습니다.

...하지만 여전히 끔찍합니다. 4초는 25초보다 낫지만 여전히 우리의 필요에는 너무 느립니다. 우리는 고객을 위한 훌륭한 경험을 구축하는 데 중점을 두고 있으며 간단한 그래프가 로드될 때까지 몇 초를 기다려야 하는 것은 아닙니다.

인덱스 단순화

인덱스가 너무 복잡하여 쿼리 플래너에 문제를 일으킬 수 있습니까? 메트릭 이름과 자산 ID만 사용하여 새 인덱스를 만들어 그 가정을 테스트했습니다.

create index on asset_metric (metric_name, asset_id)

이렇게 하면 각 자산/메트릭 쌍에 대한 관련 데이터를 쉽게 찾을 수 있을 것이라고 생각했습니다.

그리고 확실히 결과는 훌륭해 보였습니다! 4초에서 10밀리초 미만으로 400배 개선되었습니다!

쿼리를 반환하는 데 여전히 11초가 소요되었던 하나의 자산/메트릭 쌍을 제외하고. 이상하지 않은지 확인하기 위해 쿼리를 여러 번 실행했지만 일관되게 10초 이상 걸렸습니다.

쿼리 플래너는 더 빈번한 자산/메트릭 쌍에 대해 원래 인덱스를 계속 사용하고 있었습니다(한 번에 하나의 변수만 테스트하기 위해 이 시점까지 반전되지 않은 인덱스를 제거했습니다).

이것은 덜 빈번한 쌍의 경우가 아닙니다. 여기서는 Bitmap Index Scan을 사용하고 원본 인덱스와 새 인덱스를 모두 결합했습니다.

성가신 엣지 케이스를 제거하고 전반적으로 우수한 성능을 얻을 수 있는 방법이 있습니까?

향상된 인덱스 - 열 순서 변경

이전 인덱스는 중요한 단서를 제공했습니다. 자산 ID와 메트릭 이름으로 자주 사용하지 않는 쌍을 검색하는 것이 유익했습니다.

다중 열 인덱스에서 열 순서가 중요합니까? 그렇습니다.

표준 예는 전화번호부입니다. 전화번호부가 먼저 성을 기준으로 정렬된 다음 이름으로 정렬되는 것과 유사합니다. 여러 열 인덱스는 첫 번째 열, 두 번째 열, 세 번째 열 등으로 정렬됩니다.

전화번호부에서 이름만으로 누군가를 찾을 수 없는 것처럼 여러 열 인덱스를 사용하여 두 번째 또는 세 번째 열로 행을 찾을 수도 없습니다.

우리는 항상 자산 ID와 메트릭 이름을 고려하여 메트릭 데이터를 찾고 있습니다. 이것이 인덱스의 첫 번째 열이어야 하는 것이 논리적으로 보입니다. 다음 인덱스를 만들었습니다.

create index on asset_metric (metric_name, asset_id, timestamp desc)

여기에서 인덱스가 다음 기준으로 정렬되도록 열 순서를 변경합니다. metric_name그리고 asset_id. 우리도 변한다 timestamp 이전의 결과에 따라 내림차순입니다. 이것이 어떻게 수행되는지 봅시다:

멋진! 전반적으로 우수한 성능. 모든 쿼리는 2밀리초 미만으로 일관되게 실행되었습니다.

최악의 경우 쿼리를 15,000배 개선했습니다! 우리가 위에서 취한 단계에 대해 혁명적인 것은 없습니다. 현실 세계에서 수행해야 하는 DB 최적화 유형의 전형적인 예입니다. 최신 데이터베이스는 엄청난 양의 데이터를 처리할 수 있습니다.

데이터베이스 작동 방식에 대한 기본적인 이해는 세상을 바꿀 수 있습니다. 700만 행에서 질식하는 대신 이제 이 사용 사례에 대해 테이블을 1억 행으로 확장할 수 있어야 합니다. 병목 현상을 찾아 수정하는 데 2시간도 채 걸리지 않았습니다. 인덱스를 간단히 수정하는 것만으로도 충분했습니다.