Java 개발자의 시각으로 본 PostgreSQL의 인덱스 상태

안녕.

제 이름은 Vanya이고 Java 개발자입니다. 저는 PostgreSQL을 사용하여 데이터베이스 설정, 구조 및 성능 최적화, 주말에 약간의 DBA 역할을 하는 등 많은 작업을 수행합니다.

최근에 저는 마이크로서비스의 여러 데이터베이스를 정리하고 Java 라이브러리를 작성했습니다. pg-색인-건강를 사용하면 이 작업이 더 쉬워지고 시간이 절약되며 개발자가 흔히 범하는 실수를 방지하는 데 도움이 됩니다. 오늘 이야기할 도서관이 바로 이 도서관입니다.

책임 부인

제가 작업하는 PostgreSQL의 기본 버전은 10입니다. 내가 사용하는 모든 SQL 쿼리도 버전 11에서 테스트되었습니다. 지원되는 최소 버전은 9.6입니다.

선사 시대

이 모든 일은 거의 XNUMX년 전에 나에게 이상한 상황에서 시작되었습니다. 갑자기 경쟁적인 인덱스 생성이 오류로 끝났습니다. 평소와 같이 인덱스 자체는 잘못된 상태로 데이터베이스에 남아 있었습니다. 로그 분석 결과 부족함이 나타났습니다. 임시_파일_한계. 그리고 시작합니다... 더 깊이 파고들면서 저는 데이터베이스 구성에서 수많은 문제를 발견했고, 소매를 걷어붙이고 눈을 반짝반짝 빛내며 문제를 해결하기 시작했습니다.

문제 XNUMX - 기본 구성

아마도 모든 사람들이 커피메이커에서 실행할 수 있는 Postgres에 대한 비유에 꽤 지쳤을 것입니다. 하지만... 기본 구성은 실제로 많은 질문을 제기합니다. 최소한주의를 기울일 가치가 있습니다. 유지 보수_작업_멤, 임시_파일_한계, 문_시간 초과 и lock_timeout.

우리의 경우 유지 보수_작업_멤 기본값은 64MB였으며 임시_파일_한계 대략 2GB 정도입니다. 큰 테이블에 인덱스를 생성할 메모리가 부족했습니다.

따라서 에서 pg-색인-건강 시리즈를 모아봤습니다 키, 제 생각에는 각 데이터베이스에 대해 구성해야 하는 매개변수입니다.

문제 XNUMX - 중복 인덱스

우리 데이터베이스는 SSD 드라이브에 저장되어 있으며 HA-여러 데이터 센터, 마스터 호스트 및 n-복제본 수. 디스크 공간은 우리에게 매우 귀중한 자원입니다. 성능과 CPU 소비만큼 중요합니다. 따라서 빠른 읽기를 위해서는 인덱스가 필요하고, 불필요한 인덱스는 공간을 차지하고 데이터 업데이트 속도를 늦추기 때문에 데이터베이스에서 불필요한 인덱스를 보고 싶지 않습니다.

이제 모든 것을 복원한 후 잘못된 인덱스 그리고 충분히 봤으니 올렉 바르투노프(Oleg Bartunov)의 보고서, 나는 "대단한" 숙청을 조직하기로 결정했습니다. 개발자는 데이터베이스 문서를 읽는 것을 좋아하지 않는 것으로 나타났습니다. 그들은 그것을 별로 좋아하지 않습니다. 이로 인해 기본 키에 수동으로 생성된 인덱스와 고유 열에 유사한 "수동" 인덱스라는 두 가지 일반적인 오류가 발생합니다. 사실은 필요하지 않습니다. Postgres는 모든 것을 자체적으로 수행합니다. 이러한 인덱스는 안전하게 삭제할 수 있으며 이러한 목적으로 진단이 나타났습니다. Duplicate_indexes.

문제 XNUMX - 교차 인덱스

대부분의 초보 개발자는 단일 열에 인덱스를 만듭니다. 점차적으로 이 비즈니스를 철저히 경험한 사람들은 쿼리를 최적화하고 여러 열을 포함하는 더 복잡한 인덱스를 추가하기 시작합니다. 열의 인덱스가 나타나는 방식입니다. A, A + B, A + B + C 등등. 이 인덱스 중 처음 두 개는 세 번째 인덱스의 접두사이므로 안전하게 삭제할 수 있습니다. 이는 또한 많은 디스크 공간을 절약하며 이에 대한 진단도 있습니다. 교차_인덱스.

문제 XNUMX - 인덱스가 없는 외래 키

Postgres를 사용하면 백업 인덱스를 지정하지 않고도 외래 키 제약 조건을 만들 수 있습니다. 많은 상황에서 이는 문제가 되지 않으며 심지어 나타나지 않을 수도 있습니다... 당분간은...

우리도 마찬가지였습니다. 어느 시점에서 일정에 따라 실행되고 테스트 주문 데이터베이스를 지우는 작업이 마스터 호스트에 의해 우리에게 "추가"되기 시작했습니다. CPU와 IO가 낭비되고 요청 속도가 느려지고 시간 초과되었으며 서비스는 XNUMX개였습니다. 빠른 분석 pg_stat_activity 다음과 같은 쿼리가 표시되었습니다.

delete from <table> where id in (…)

물론 이 경우에는 대상 테이블에 id별 인덱스가 있었고, 조건에 따라 삭제된 레코드는 거의 없었다. 모든 것이 제대로 작동할 것 같았지만 아쉽게도 그렇지 않았습니다.

멋진 분이 구출하러 오셨어요 설명하다 대상 테이블의 레코드를 삭제하는 것 외에도 참조 무결성 검사도 있는데 관련 테이블 중 하나에서 이 검사가 실패했다고 말했습니다. 순차 스캔 적절한 인덱스가 없기 때문입니다. 그리하여 진단이 탄생했습니다. foreign_keys_without_index.

문제 XNUMX – 인덱스의 null 값

기본적으로 Postgres는 btree 인덱스에 null 값을 포함하지만 일반적으로 필요하지 않습니다. 그래서 저는 이러한 null을 부지런히 버리려고 노력합니다(진단 indexes_with_null_values), 유형별로 Null 허용 열에 부분 인덱스 생성 where <A> is not null. 이러한 방식으로 인덱스 중 하나의 크기를 1877MB에서 16KB로 줄일 수 있었습니다. 그리고 서비스 중 하나에서는 인덱스에서 null 값을 제외하여 데이터베이스 크기가 총 16%(절대 수치로 4.3GB) 감소했습니다. 매우 간단한 수정으로 디스크 공간을 크게 절약할 수 있습니다. 🙂

문제 XNUMX – 기본 키 부족

메커니즘의 특성상 포스트그레스의 MVCC 이런 상황이 가능하다 고창증죽은 레코드 수가 많아 테이블 크기가 급격히 커지는 경우. 나는 이것이 우리를 위협하지 않을 것이라고 순진하게 믿었고, 우리 기지에는 이런 일이 일어나지 않을 것이라고 순진하게 믿었습니다. 왜냐하면 우리는 와우!!! 평범한 개발자이기 때문입니다... 내가 얼마나 멍청하고 순진했는지...

어느 날 멋진 마이그레이션이 활발히 사용되는 대규모 테이블의 모든 레코드를 가져와 업데이트했습니다. 우리는 갑자기 테이블 크기를 +100GB로 늘렸습니다. 정말 부끄러운 일이었지만 우리의 불운은 거기서 끝나지 않았습니다. 이 테이블에 대한 자동 진공 작업이 15시간 후에 종료된 후 물리적 위치가 반환되지 않는다는 것이 분명해졌습니다. 서비스를 중지하고 VACUUM FULL을 만들 수 없어서 사용하기로 결정했습니다. pg_repack. 그리고 나서 그것은 밝혀졌습니다 pg_repack 기본 키나 기타 고유성 제약 조건 없이 테이블을 처리하는 방법을 모르고 테이블에 기본 키가 없습니다. 이렇게 진단이 탄생했습니다 tables_without_primary_key.

라이브러리 버전에서는 0.1.5 늘어나는 테이블과 인덱스로부터 데이터를 수집하고 적시에 대응하는 기능이 추가되었습니다.

문제 XNUMX, XNUMX - 인덱스 부족 및 사용되지 않는 인덱스

다음 두 가지 진단은 다음과 같습니다. tables_with_missing_indexes и 미사용_인덱스 – 비교적 최근에 최종 형태로 나타났습니다. 요점은 그냥 가져와서 추가할 수 없다는 것입니다.

이미 작성한 것처럼 여러 개의 복제본이 있는 구성을 사용하며 호스트마다 읽기 로드가 근본적으로 다릅니다. 결과적으로 일부 호스트의 일부 테이블과 인덱스가 실제로 사용되지 않는 상황이 발생했으며 분석을 위해서는 클러스터의 모든 호스트에서 통계를 수집해야 합니다. 통계 재설정 이는 클러스터의 모든 호스트에도 필요하며 마스터에서만 이 작업을 수행할 수는 없습니다.

이 접근 방식을 통해 사용되지 않은 인덱스를 제거하고 거의 사용되지 않는 테이블에 누락된 인덱스를 추가하여 수십 기가바이트를 절약할 수 있었습니다.

결론으로서

물론 거의 모든 진단에 대해 구성할 수 있습니다. 제외 목록. 이렇게 하면 애플리케이션에서 신속하게 검사를 구현하여 새로운 오류가 나타나는 것을 방지하고 오래된 오류를 점진적으로 수정할 수 있습니다.

일부 진단은 데이터베이스 마이그레이션을 롤아웃한 직후 기능 테스트에서 수행할 수 있습니다. 그리고 이것은 아마도 내 도서관의 가장 강력한 기능 중 하나일 것입니다. 사용 예는 다음에서 찾을 수 있습니다. 데모.

실제 데이터베이스에서만 사용되지 않거나 누락된 인덱스와 팽창에 대한 검사를 수행하는 것이 합리적입니다. 수집된 값은 다음과 같이 기록될 수 있습니다. 클릭 하우스 또는 모니터링 시스템으로 전송됩니다.

정말 그랬으면 좋겠어 pg-색인-건강 유용하고 수요가 많을 것입니다. 발견한 문제를 보고하고 새로운 진단을 제안함으로써 라이브러리 개발에 기여할 수도 있습니다.

출처 : habr.com