친애하는 DELETE. 니콜라이 사모크발로프(Postgres.ai)

언젠가는 불필요한 데이터를 자동으로 제거하는 것이 DBMS의 중요한 작업 중 하나가 될 것입니다[1]. 그동안 우리는 불필요한 데이터를 삭제하거나 저렴한 스토리지 시스템으로 옮기는 일을 스스로 처리해야 합니다. 수백만 개의 행을 삭제하기로 결정했다고 가정해 보겠습니다. 특히 조건이 알려져 있고 적합한 색인이 있는 경우 매우 간단한 작업입니다. "DELETE FROM table1 WHERE col1 = :value" - 무엇이 더 간단할 수 있습니까?

비디오 :

• 저는 첫 해부터, 즉 2007년부터 Highload 프로그램 위원회에 있었습니다.

• 저는 2005년부터 Postgres와 함께했습니다. 많은 프로젝트에서 사용했습니다.

• RuPostges와도 2007년부터 그룹화되었습니다.

• 우리는 Meetup에서 2100명 이상의 참가자로 성장했습니다. 오랫동안 샌프란시스코에 추월당한 뉴욕에 이어 세계 XNUMX위다.

• 저는 몇 년 동안 캘리포니아에서 살았습니다. 나는 대기업을 포함하여 미국 회사와 더 많이 거래합니다. 그들은 Postgres의 활성 사용자입니다. 그리고 모든 종류의 흥미로운 것들이 있습니다.

https://postgres.ai/ 내 회사입니다. 우리는 개발 속도 저하를 제거하는 작업을 자동화하는 사업을 하고 있습니다.

무언가를 하고 있다면 때때로 Postgres 주변에 어떤 종류의 플러그가 있습니다. 관리자가 테스트 스탠드를 설정하거나 DBA가 응답할 때까지 기다려야 한다고 가정해 보겠습니다. 또한 개발, 테스트 및 관리 프로세스에서 이러한 병목 현상을 발견하고 자동화 및 새로운 접근 방식을 통해 이를 제거하려고 합니다.

https://www.seagate.com/files/www-content/our-story/trends/files/idc-seagate-dataage-whitepaper.pdf

저는 최근에 로스앤젤레스의 VLDB에 있었습니다. 이것은 데이터베이스에 관한 가장 큰 회의입니다. 그리고 앞으로는 DBMS가 데이터를 저장할 뿐만 아니라 자동으로 삭제할 것이라는 보고도 있었다. 이것은 새로운 주제입니다.

제타바이트 세계에는 점점 더 많은 데이터가 있습니다. 즉, 1페타바이트입니다. 그리고 현재 전 세계에 000제타바이트 이상의 데이터가 저장되어 있는 것으로 이미 추정됩니다. 그리고 점점 더 많이 있습니다.

https://vldb2019.github.io/files/VLDB19-keynote-2-slides.pdf

그리고 그것으로 무엇을해야합니까? 당연히 제거해야 합니다. 다음은 이 흥미로운 보고서에 대한 링크입니다. 그러나 지금까지 이것은 DBMS에서 구현되지 않았습니다.

돈을 세는 사람은 두 가지를 원합니다. 그들은 우리가 삭제하기를 원하므로 기술적으로 우리는 그것을 할 수 있어야 합니다.

다음에 말할 것은 많은 실제 상황을 포함하는 추상적인 상황입니다. 즉, 저와 주변 데이터베이스에 여러 번, 수년 동안 실제로 일어난 일을 편집한 것입니다. 갈퀴는 어디에나 있고 모든 사람이 항상 갈퀴를 밟습니다.

성장하고 있는 기반 또는 여러 기반이 있다고 가정해 봅시다. 그리고 일부 기록은 분명히 쓰레기입니다. 예를 들어 사용자가 그곳에서 작업을 시작했지만 완료하지 않았습니다. 그리고 얼마 후 우리는 이 미완성품을 더 이상 저장할 수 없다는 것을 알게 됩니다. 즉, 공간 절약, 성능 향상 등을 위해 일부 쓰레기를 청소하고 싶습니다.

일반적으로 작업은 특정 항목, 일부 테이블의 특정 행 삭제를 자동화하는 것입니다.

그리고 오늘 우리가 이야기 할 요청, 즉 쓰레기 제거에 관한 요청이 있습니다.

숙련된 개발자에게 작업을 요청했습니다. 그는이 요청을 받아 직접 확인했습니다. 모든 것이 작동합니다. 스테이징 테스트 - 모든 것이 정상입니다. 롤아웃 - 모든 것이 작동합니다. 하루에 한 번 실행합니다. 모든 것이 정상입니다.

데이터베이스는 점점 커집니다. Daily DELETE가 조금 더 느리게 작동하기 시작합니다.

그런 다음 이제 마케팅 회사가 있고 트래픽이 몇 배 더 커질 것이라는 것을 이해하므로 불필요한 것을 일시적으로 중단하기로 결정합니다. 그리고 돌아가는 것을 잊으십시오.

몇 달 후 그들은 기억했습니다. 그리고 그 개발자가 그만 두거나 다른 일로 바쁘면 다른 사람에게 반환하도록 지시했습니다.

그는 개발과 스테이징을 확인했습니다. 모든 것이 정상입니다. 당연히 쌓인 것을 정리해야 합니다. 그는 모든 것이 작동하는지 확인했습니다.

다음에 어떻게 됩니까? 그런 다음 모든 것이 우리를 위해 무너집니다. 어느 순간 모든 것이 무너지도록 떨어진다. 모두가 충격을 받고 있으며 아무도 무슨 일이 일어나고 있는지 이해하지 못합니다. 그리고 문제가 이 DELETE에 있다는 것이 밝혀졌습니다.

문제가 발생했나요? 다음은 잘못되었을 수 있는 항목의 목록입니다. 이 중 가장 중요한 것은 무엇입니까?

• 예를 들어 검토가 없었습니다. 즉, DBA 전문가가 검토하지 않았습니다. 그는 숙련된 눈으로 즉시 문제를 발견할 것이며, 게다가 수백만 라인이 축적된 prod에 접근할 수 있습니다.

• 뭔가 잘못 확인했을 수도 있습니다.

• 하드웨어가 구식이어서 이 기반을 업그레이드해야 할 수도 있습니다.

• 또는 데이터베이스 자체에 문제가 있어 Postgres에서 MySQL로 이동해야 합니다.

• 아니면 작동에 문제가 있는 것일 수도 있습니다.

• 작업 조직에 실수가 있어서 누군가를 해고하고 최고의 사람을 고용해야 합니까?

DBA 확인이 없었습니다. DBA가 있다면 그는 이러한 수백만 줄을 보고 실험 없이도 "그들은 그렇게 하지 않습니다."라고 말할 것입니다. 이 코드가 GitLab, GitHub에 있고 코드 검토 프로세스가 있고 DBA의 승인 없이 이 작업이 프로덕션에서 수행되는 그런 일이 없다고 가정하면 DBA는 분명히 다음과 같이 말할 것입니다. .”

그리고 그는 디스크 IO에 문제가 있고 모든 프로세스가 미쳐 가고 잠금이있을 수 있으며 몇 분 동안 autovacuum을 차단할 것이므로 좋지 않다고 말할 것입니다.

http://bit.ly/nancy-hl2018-2

두 번째 실수 - 그들은 잘못된 장소를 확인했습니다. 우리는 prod에 많은 정크 데이터가 축적된다는 사실을 나중에 보았지만 개발자는 이 데이터베이스에 데이터를 축적하지 않았고 스테이징 중에 아무도 이러한 정크를 생성하지 않았습니다. 그에 따라 1개의 대사가 순식간에 풀렸다.

우리는 테스트가 약하다는 것을 이해합니다. 즉, 구축된 프로세스가 문제를 포착하지 못합니다. 적절한 DB 실험이 수행되지 않았습니다.

이상적인 실험은 바람직하게는 동일한 장비에서 수행됩니다. 항상 동일한 장비에서 이 작업을 수행할 수 있는 것은 아니지만 전체 크기의 데이터베이스 복사본이라는 것이 매우 중요합니다. 이것이 제가 몇 년 동안 설교해 온 것입니다. 그리고 XNUMX년 전에 제가 이것에 대해 이야기한 적이 있습니다. YouTube에서 모두 볼 수 있습니다.

우리 장비가 불량일까요? 보시면 대기 시간이 뛰었습니다. 활용도가 100%인 것을 확인했습니다. 물론 이것이 최신 NVMe 드라이브라면 훨씬 쉬울 것입니다. 그리고 아마도 우리는 그것에서 눕지 않을 것입니다.

클라우드가 있는 경우 업그레이드가 쉽게 수행됩니다. 새 하드웨어에서 새 복제본을 생성했습니다. 전환. 그리고 모든 것이 좋습니다. 아주 쉽습니다.

어떻게든 더 작은 디스크를 만질 수 있습니까? 그리고 여기에서는 DBA의 도움을 받아 체크포인트 튜닝이라는 특정 주제에 대해 알아봅니다. 우리는 체크포인트 튜닝을 하지 않은 것으로 나타났습니다.

체크포인트란? 모든 DBMS에 있습니다. 메모리에 변경되는 데이터가 있는 경우 디스크에 즉시 기록되지 않습니다. 데이터가 변경되었다는 정보는 먼저 미리 쓰기 로그에 기록됩니다. 그리고 어느 시점에서 DBMS는 실제 페이지를 디스크에 덤프할 때라고 결정하여 장애가 발생하면 REDO를 덜 수행할 수 있습니다. 장난감 같아요. 우리가 죽으면 마지막 체크 포인트에서 게임을 시작합니다. 그리고 모든 DBMS는 이를 구현합니다.

Postgres의 설정이 뒤쳐져 있습니다. 10-15년 된 데이터 및 트랜잭션 볼륨을 위해 설계되었습니다. 체크포인트도 예외는 아닙니다.

다음은 Postgres 점검 보고서, 즉 자동 상태 점검의 정보입니다. 그리고 여기 몇 테라바이트의 데이터베이스가 있습니다. 그리고 거의 90%의 경우에 체크포인트를 강제하는 것을 잘 볼 수 있습니다.

무슨 뜻이에요? 거기에는 두 가지 설정이 있습니다. 체크포인트는 예를 들어 10분 후에 제한 시간에 도달할 수 있습니다. 또는 꽤 많은 데이터가 채워졌을 때 올 수도 있습니다.

그리고 기본적으로 max_wal_saze는 1GB로 설정됩니다. 실제로 이것은 Postgres에서 300-400MB 이후에 실제로 발생합니다. 너무 많은 데이터를 변경했고 체크포인트가 발생했습니다.

그리고 아무도 그것을 조정하지 않고 서비스가 성장하고 회사가 많은 돈을 벌고 많은 트랜잭션이 발생하면 체크 포인트는 30 분에 한 번, 때로는 XNUMX 초마다, 때로는 겹치기도합니다. 이것은 매우 나쁩니다.

그리고 우리는 그것이 덜 자주 오는지 확인해야 합니다. 즉, max_wal_size를 높일 수 있습니다. 그리고 덜 자주 올 것입니다.

그러나 우리는 더 정확하게 수행하는 방법, 즉 특정 데이터를 명확하게 기반으로 설정 선택에 대한 결정을 내리는 방법에 대한 전체 방법론을 개발했습니다.

따라서 데이터베이스에 대한 두 가지 일련의 실험을 수행하고 있습니다.

첫 번째 시리즈 - max_wal_size를 변경합니다. 그리고 우리는 대대적인 수술을 하고 있습니다. 먼저 기본 설정인 1GB에서 수행합니다. 그리고 우리는 수백만 줄의 대규모 DELETE를 수행합니다.

우리가 얼마나 힘든지 알 수 있습니다. 디스크 IO가 매우 나쁘다는 것을 알 수 있습니다. 생성한 WAL의 수를 확인합니다. 이는 매우 중요하기 때문입니다. 체크포인트가 몇 번 발생했는지 봅시다. 그리고 우리는 그것이 좋지 않다는 것을 압니다.

다음으로 max_wal_size를 늘립니다. 반복합니다. 증가하고 반복합니다. 그리고 여러 번. 원칙적으로 10, 1, 2, 4기가바이트인 8포인트가 좋습니다. 그리고 특정 시스템의 동작을 살펴봅니다. 여기서 장비는 온 프로덕션과 같아야 합니다. 동일한 디스크, 동일한 양의 메모리 및 동일한 Postgres 설정이 있어야 합니다.

그리고 이런 식으로 우리는 시스템을 교환할 것이고, 불량 대량 DELETE의 경우 DBMS가 어떻게 작동하는지, 어떻게 체크포인트를 지정하는지 알 수 있습니다.

러시아어로 검문소는 검문소입니다.

예: 색인별로 수백만 개의 행을 DELETE, 행은 페이지 전체에 "분산"됩니다.

다음은 예입니다. 이것은 약간의 기반입니다. 그리고 max_wal_size에 대해 1GB의 기본 설정을 사용하면 디스크가 기록을 위해 선반으로 이동한다는 것이 매우 분명합니다. 이 사진은 매우 아픈 환자의 전형적인 증상입니다. 즉, 그는 정말 기분이 나빴습니다. 그리고 하나의 단일 작업이 있었고 수백만 줄의 DELETE가 있었습니다.

이러한 작업이 prod에서 허용되면 하나의 DELETE가 선반에서 우리를 죽이는 것이 분명하기 때문에 우리는 그냥 누울 것입니다.

또한 16GB의 경우 이미 치아가 사라진 것이 분명합니다. 즉, 우리는 천장을 두드리고 있지만 그렇게 나쁘지는 않습니다. 거기에는 약간의 자유가 있었다. 오른쪽은 기록입니다. 그리고 작업 수 - 두 번째 그래프. 그리고 16GB일 때 우리는 이미 조금 더 쉽게 호흡하고 있음이 분명합니다.

그리고 64GB가 완전히 좋아진 것을 볼 수 있습니다. 이미 치아가 발음되고 다른 작업에서 살아남고 디스크로 무언가를 할 수있는 더 많은 기회가 있습니다.

왜 그럴까요?

조금 더 자세히 알아보겠지만 이 주제인 체크포인트 튜닝 방법은 전체 보고서가 될 수 있으므로 많이 로드하지는 않겠지만 어떤 어려움이 있는지 조금 간략하게 설명하겠습니다.

체크 포인트가 너무 자주 발생하고 라인을 순차적으로 업데이트하지 않고 인덱스로 찾으면 전체 테이블을 삭제하지 않기 때문에 처음에는 첫 번째 페이지를 터치 한 다음 천 번째 페이지를 터치했을 수 있습니다. 그리고 처음으로 돌아왔습니다. 그리고 첫 번째 페이지를 방문하는 사이에 체크포인트가 이미 디스크에 저장했다면 두 번째로 더럽혀졌기 때문에 다시 저장합니다.

그리고 우리는 체크포인트가 그것을 여러 번 저장하도록 강제할 것입니다. 그에게 중복 작업이 어떻게 있습니까?

그러나 그것이 전부는 아닙니다. 페이지는 Postgres에서 8KB이고 Linux에서 4KB입니다. 그리고 full_page_writes 설정이 있습니다. 기본적으로 활성화되어 있습니다. 끄면 충돌이 발생하면 페이지의 절반 만 저장 될 위험이 있기 때문에 이것은 맞습니다.

포워드 로그의 WAL에 기록하는 동작은 체크포인트가 있고 페이지를 처음으로 변경할 때 페이지 전체, 즉 8KB 모두가 포워드 로그에 들어가는 것과 같습니다. 무게가 100바이트인 줄. 그리고 우리는 전체 페이지를 적어야 합니다.

후속 변경에서는 특정 튜플만 있을 것이지만 처음으로 모든 것을 기록합니다.

따라서 체크포인트가 다시 발생하면 모든 것을 처음부터 다시 시작하고 전체 페이지를 푸시해야 합니다. 빈번한 체크포인트를 사용하면 동일한 페이지를 통과할 때 full_page_writes = on이 가능한 것보다 많을 것입니다. 즉, 더 많은 WAL을 생성합니다. 더 많은 정보가 복제본, 아카이브, 디스크로 전송됩니다.

따라서 두 가지 중복이 있습니다.

max_wal_size를 늘리면 체크포인트와 wal 작성자 모두에게 더 쉬워집니다. 그리고 그것은 훌륭합니다.

테라바이트를 넣고 그것과 함께 살자. 나쁜 점은 무엇입니까? 체크 포인트가 오래 전이었고 이미 많은 것이 바뀌었기 때문에 실패하면 몇 시간 동안 올라갈 것이기 때문에 이것은 나쁩니다. 그리고 우리는 이 모든 REDO를 수행해야 합니다. 그래서 우리는 두 번째 일련의 실험을 합니다.

우리는 작업을 수행하고 체크포인트가 완료되려고 할 때를 확인하고 의도적으로 kill -9 Postgres를 수행합니다.

그런 다음 다시 시작하고 이 장비에서 얼마나 오래 상승할지, 즉 이 나쁜 상황에서 얼마나 REDO할 것인지 확인합니다.

두 번 상황이 나쁘다는 점에 유의하겠습니다. 첫째, 체크포인트가 끝나기 직전에 추락했기 때문에 잃을 것이 많습니다. 둘째, 우리는 대대적인 작전을 펼쳤습니다. 그리고 체크포인트가 시간 초과된 경우 마지막 체크포인트 이후 WAL이 더 적게 생성될 가능성이 높습니다. 즉, 이중 패자입니다.

다양한 max_wal_size 크기에 대해 이러한 상황을 측정하고 max_wal_size가 64GB인 경우 이중 최악의 경우 10분 동안 올라갈 것임을 이해합니다. 그리고 그것이 우리에게 적합한지 아닌지를 생각합니다. 이것은 비즈니스 질문입니다. 우리는 이 사진을 비즈니스 의사결정 책임자에게 보여주고 “문제가 생겼을 때 기껏해야 얼마나 오래 누워있을 수 있습니까? 최악의 상황에서 3~5분 누워있을 수 있을까? 그리고 당신은 결정을 내립니다.

그리고 여기에 흥미로운 점이 있습니다. 컨퍼런스에서 Patroni에 대한 몇 가지 보고서가 있습니다. 그리고 아마도 당신은 그것을 사용하고 있습니다. 이것은 Postgres의 자동 장애 조치입니다. GitLab과 Data Egret이 이에 대해 이야기했습니다.

그리고 30초 안에 오는 자동 장애 조치가 있으면 10분 동안 누워 있을 수 있지 않을까요? 이 시점에서 복제본으로 전환하면 모든 것이 잘 될 것이기 때문입니다. 이것은 논점입니다. 나는 명확한 대답을 모른다. 나는 이 주제가 응급 복구에 관한 것만은 아니라고 생각합니다.

실패 후 회복 기간이 길면 다른 많은 상황에서 불편할 것입니다. 예를 들어, 같은 실험에서 우리가 무언가를 할 때 때때로 10분을 기다려야 합니다.

자동 장애 조치가 있어도 너무 멀리 가지 않을 것입니다. 일반적으로 64, 100GB와 같은 값은 좋은 값입니다. 때로는 덜 선택할 가치가 있습니다. 일반적으로 이것은 미묘한 과학입니다.

예를 들어 max_wal_size =1, 8과 같이 반복하려면 대량 작업을 여러 번 반복해야 합니다. 네가 해냈어. 그리고 같은 기지에서 다시 하고 싶지만 이미 모든 것을 삭제했습니다. 무엇을 해야 합니까?

그런 상황에서 반복하기 위해 우리가 하는 일에 대해서는 나중에 이야기하겠습니다. 그리고 이것이 가장 정확한 접근 방식입니다.

하지만 이 경우에는 운이 좋았습니다. 여기에 "BEGIN, DELETE, ROLLBACK"이라고 표시된 대로 DELETE를 반복할 수 있습니다. 즉, 우리가 직접 취소하면 반복할 수 있습니다. 그리고 물리적으로 데이터는 같은 위치에 있을 것입니다. 부풀어 오르지도 않습니다. 이러한 DELETE를 반복할 수 있습니다.

이 DELETE with ROLLBACK은 적절하게 배포된 데이터베이스 랩이 없는 경우에도 체크포인트 튜닝에 이상적입니다.

하나의 열 "i"로 접시를 만들었습니다. Postgres에는 유틸리티 열이 있습니다. 특별히 요청하지 않는 한 보이지 않습니다. 이들은 ctid, xmid, xmax입니다.

Ctid는 물리적 주소입니다. 제로 페이지, 페이지의 첫 번째 튜플.

ROOLBACK 후에 튜플이 같은 위치에 남아 있음을 알 수 있습니다. 즉, 다시 시도하면 같은 방식으로 작동합니다. 이것이 중요한 것입니다.

Xmax는 튜플의 사망 시간입니다. 스탬프가 찍혔지만 Postgres는 트랜잭션이 롤백된 것을 알고 있으므로 0인지 또는 롤백된 트랜잭션인지는 중요하지 않습니다. 이는 DELETE를 반복하고 시스템 동작의 대량 작업을 확인할 수 있음을 나타냅니다. 가난한 사람들을 위한 데이터베이스 실험실을 만들 수 있습니다.

이것은 프로그래머에 관한 것입니다. DBA에 대해서도 그들은 항상 프로그래머를 꾸짖습니다. "왜 그렇게 길고 어려운 작업을 수행합니까?" 이것은 완전히 다른 수직 주제입니다. 예전에는 관리가 있었고 이제는 개발이있을 것입니다.

분명히, 우리는 산산조각이 나지 않았습니다. 알았습니다. 수백만 줄의 힙에 대해 이러한 DELETE를 부분으로 나누지 않는 것은 불가능합니다. 20분 동안 진행되며 모든 것이 누울 것입니다. 그러나 유감스럽게도 숙련된 개발자도 실수를 저지릅니다. 대기업에서도 마찬가지입니다.

깨는 것이 왜 중요한가요?

• 디스크가 단단한 것을 확인하면 속도를 늦추십시오. 그리고 우리가 망가지면 일시 중지를 추가하고 스로틀링 속도를 늦출 수 있습니다.

• 그리고 우리는 오랫동안 다른 사람들을 차단하지 않을 것입니다. 어떤 경우에는 중요하지 않습니다. 아무도 작업하지 않는 실제 쓰레기를 삭제하는 경우 트랜잭션이 완료될 때까지 기다리기 때문에 autovacuum 작업을 제외한 다른 사람을 차단하지 않을 가능성이 큽니다. 그러나 다른 사람이 요청할 수 있는 것을 제거하면 차단되고 일종의 연쇄 반응이 발생합니다. 웹사이트 및 모바일 애플리케이션에서는 긴 거래를 피해야 합니다.

https://postgres.ai/products/joe/

이건 재미 있네. 나는 종종 개발자들이 "어떤 팩 크기를 선택해야 합니까?"라고 묻는 것을 봅니다.

번들 크기가 클수록 트랜잭션 오버헤드, 즉 트랜잭션으로 인한 추가 오버헤드가 작아지는 것은 분명합니다. 그러나 동시에 이 트랜잭션에 대한 시간이 늘어납니다.

아주 간단한 규칙이 있습니다. 가능한 한 많이 가져가되 초당 실행 파일을 넘지 마십시오.

왜 잠깐? 설명은 매우 간단하고 모든 사람, 심지어 기술자가 아닌 사람도 이해할 수 있습니다. 우리는 반응을 봅니다. 50밀리초를 봅시다. 무언가가 바뀌면 우리의 눈이 반응할 것입니다. 적다면 더 어렵습니다. 예를 들어, 무언가가 100밀리초 후에 응답하면, 예를 들어 마우스를 클릭했는데 100밀리초 후에 응답하면 이미 약간의 지연을 느끼는 것입니다. 초는 이미 브레이크로 인식됩니다.

따라서 대량 작업을 10초 버스트로 나누면 누군가를 차단할 위험이 있습니다. 그리고 그것은 몇 초 동안 작동할 것이고 사람들은 이미 그것을 알아차릴 것입니다. 따라서 저는 XNUMX초 이상 하지 않는 것을 선호합니다. 그러나 동시에 트랜잭션 오버헤드가 눈에 띄기 때문에 매우 미세하게 분해하지 마십시오. 베이스가 더 단단해지고 다른 문제가 발생할 수 있습니다.

우리는 팩의 크기를 선택합니다. 각각의 경우에 다르게 할 수 있습니다. 자동화할 수 있습니다. 그리고 우리는 한 팩 처리의 효율성을 확신합니다. 즉, 하나의 팩을 DELETE하거나 UPDATE합니다.

그건 그렇고, 내가 말하는 모든 것은 DELETE에 관한 것이 아닙니다. 짐작하셨겠지만 이는 데이터에 대한 대량 작업입니다.

그리고 우리는 계획이 훌륭하다는 것을 압니다. 인덱스 스캔을 볼 수 있습니다. 인덱스 전용 스캔이 훨씬 좋습니다. 그리고 소량의 데이터가 관련되어 있습니다. 그리고 XNUMX초도 채 되지 않아 성취됩니다. 감독자.

그리고 여전히 성능 저하가 없는지 확인해야 합니다. 첫 번째 팩이 빠르게 작동하고 점점 더 악화됩니다. 프로세스는 많은 테스트가 필요한 것과 같습니다. 이것이 바로 데이터베이스 랩의 목적입니다.

그리고 우리는 프로덕션에서 이를 올바르게 따를 수 있도록 무언가를 준비해야 합니다. 예를 들어, 우리는 로그에 시간을 기록할 수 있고, 우리가 지금 어디에 있고 누구를 삭제했는지 기록할 수 있습니다. 그리고 이것은 우리가 나중에 무슨 일이 일어나고 있는지 이해할 수 있게 해줄 것입니다. 그리고 문제가 발생하면 신속하게 문제를 찾습니다.

요청의 효율성을 확인해야 하고 여러 번 반복해야 하는 경우 동료 봇과 같은 것이 있습니다. 그는 이미 준비되었습니다. 매일 수십 명의 개발자가 사용합니다. 그리고 그는 요청 시 거대한 테라바이트 데이터베이스를 30초 안에 제공하는 방법을 알고 있습니다. 그리고 거기에서 무언가를 삭제하고 RESET이라고 말한 다음 다시 삭제할 수 있습니다. 이런 식으로 실험해 볼 수 있습니다. 나는 이것에 대한 미래를 본다. 그리고 우리는 이미 그렇게 하고 있습니다.

https://docs.gitlab.com/ee/development/background_migrations.html

파티셔닝 전략이란 무엇입니까? 나는 팩의 개발자들이 사용하고 있는 3가지 다른 파티셔닝 전략을 봅니다.

첫 번째는 매우 간단합니다. 숫자 ID가 있습니다. 그리고 그것을 다른 간격으로 나누고 그것으로 작업합시다. 단점은 분명합니다. 첫 번째 세그먼트에는 100줄의 실제 쓰레기가 있을 수 있고, 두 번째 5줄에는 전혀 없을 수도 있고, 1줄 모두 쓰레기로 판명될 수도 있습니다. 매우 고르지 않은 작업이지만 부서지기 쉽습니다. 그들은 최대 ID를 가져 와서 박살 냈습니다. 이것은 순진한 접근 방식입니다.

두 번째 전략은 균형 잡힌 접근 방식입니다. Gitlab에서 사용됩니다. 그들은 테이블을 가져다가 스캔했습니다. 각 팩에 정확히 10개의 레코드가 있도록 ID 팩의 경계를 찾았습니다. 그리고 그것들을 대기열에 넣습니다. 그런 다음 처리합니다. 여러 스레드에서 이 작업을 수행할 수 있습니다.

그런데 첫 번째 전략에서도 여러 스레드에서 이 작업을 수행할 수 있습니다. 어렵지 않습니다.

https://medium.com/@samokhvalov/how-partial-indexes-affect-update-performance-in-postgres-d05e0052abc

그러나 더 시원하고 더 나은 접근 방식이 있습니다. 세 번째 전략입니다. 그리고 가능하면 선택하는 것이 좋습니다. 우리는 특수 인덱스를 기반으로 이를 수행합니다. 이 경우 가비지 조건 및 ID에 따른 인덱스일 가능성이 큽니다. 힙으로 가지 않도록 인덱스 전용 스캔이 되도록 ID를 포함합니다.

일반적으로 인덱스만 스캔하는 것이 인덱스 스캔보다 빠릅니다.

그리고 삭제하고 싶은 ID를 빠르게 찾습니다. BATCH_SIZE는 미리 선택합니다. 그리고 우리는 그것들을 얻을 뿐만 아니라 특별한 방법으로 그것들을 얻고 즉시 해킹합니다. 그러나 우리는 그들이 이미 잠긴 경우 잠그지 않고 계속해서 다음을 가져갈 수 있도록 잠그고 있습니다. 업데이트 건너뛰기 잠금입니다. Postgres의 이 뛰어난 기능을 사용하면 원하는 경우 여러 스레드에서 작업할 수 있습니다. 하나의 스트림에서 가능합니다. 그리고 여기에 CTE가 있습니다. 이것은 하나의 요청입니다. 그리고 이 CTE의 XNUMX층에서 실제 삭제 작업이 진행 중입니다. returning *. ID를 반환할 수 있지만 더 좋습니다. *각 줄에 데이터가 많지 않은 경우.

왜 필요한가요? 이것이 우리가 다시 보고해야 하는 것입니다. 이제 실제로 너무 많은 줄을 삭제했습니다. 그리고 이와 같이 ID 또는 created_at별로 테두리가 있습니다. 최소, 최대를 할 수 있습니다. 다른 일을 할 수 있습니다. 여기서 많은 것을 채울 수 있습니다. 그리고 모니터링에 매우 편리합니다.

인덱스에 대한 참고 사항이 하나 더 있습니다. 이 작업에 대한 특수 인덱스가 필요하다고 결정하면 힙 전용 튜플 업데이트를 망치지 않도록 해야 합니다. 즉, Postgres에는 이러한 통계가 있습니다. 이는 테이블의 pg_stat_user_tables에서 볼 수 있습니다. 핫 업데이트가 사용되고 있는지 여부를 확인할 수 있습니다.

새 색인이 단순히 차단될 수 있는 상황이 있습니다. 그리고 이미 작동 중인 다른 모든 업데이트가 있으므로 속도를 늦추십시오. 인덱스가 나타났기 때문이 아니라(각 인덱스가 업데이트 속도를 약간 늦추지만 약간) 여기서는 여전히 망칩니다. 그리고 이 테이블에 대한 특별한 최적화는 불가능합니다. 이것은 때때로 발생합니다. 이것은 소수의 사람들이 기억하는 미묘함입니다. 그리고 이 갈퀴는 밟기 쉽습니다. 때로는 다른 쪽에서 접근 방식을 찾아야 하고 여전히 이 새 인덱스 없이 수행하거나 다른 인덱스를 만들거나 다른 방법으로 두 번째 방법을 사용할 수 있습니다.

그러나 이것이 가장 최적의 전략입니다. 배치로 분할하고 하나의 요청으로 배치에서 촬영하고 조금 삭제하는 방법 등입니다.

긴 트랜잭션 https://gitlab.com/snippets/1890447

차단된 autovacuum - https://gitlab.com/snippets/1889668

차단 문제 - https://gitlab.com/snippets/1890428

실수 #5는 큰 실수입니다. Okmeter의 Nikolai가 Postgres 모니터링에 대해 이야기했습니다. 불행히도 이상적인 Postgres 모니터링은 존재하지 않습니다. 일부는 더 가깝고 일부는 더 멀다. Okmeter는 완벽에 가깝지만 많은 것이 누락되어 추가해야 합니다. 이에 대한 준비가 필요합니다.

예를 들어 데드 튜플이 가장 잘 모니터링됩니다. 테이블에 죽은 것이 많이 있으면 뭔가 잘못된 것입니다. 지금 대응하는 것이 더 낫습니다. 그렇지 않으면 저하가 발생할 수 있으며 누울 수 있습니다. 일어난다.

IO가 크면 좋지 않은 것이 분명합니다.

긴 거래도. 긴 트랜잭션은 OLTP에서 허용되지 않아야 합니다. 그리고 여기 스니펫에 대한 링크가 있어 이 스니펫을 가져오고 이미 긴 트랜잭션을 일부 추적할 수 있습니다.

긴 트랜잭션이 나쁜 이유는 무엇입니까? 모든 잠금은 마지막에만 해제되기 때문입니다. 그리고 우리는 모두를 망칩니다. 또한 모든 테이블에 대해 autovacuum을 차단합니다. 전혀 좋지 않습니다. 복제본에서 상시 대기가 활성화되어 있어도 여전히 좋지 않습니다. 일반적으로 긴 거래를 피하는 것이 더 좋은 곳은 없습니다.

vacuum되지 않은 테이블이 많은 경우 경고가 필요합니다. 여기서 그러한 상황이 가능합니다. autovacuum의 작동에 간접적으로 영향을 미칠 수 있습니다. 이것은 내가 약간 개선한 Avito의 스니펫입니다. 그리고 그것은 autovacuum으로 우리가 무엇을 가지고 있는지 볼 수 있는 흥미로운 도구로 밝혀졌습니다. 예를 들어, 일부 테이블은 그곳에서 기다리고 있으며 차례를 기다리지 않습니다. 또한 모니터링에 넣고 경고를 받아야 합니다.

그리고 블록을 발행합니다. 블록 나무의 숲. 나는 누군가에게서 무언가를 가져 와서 개선하는 것을 좋아합니다. 여기서 저는 Data Egret에서 록 트리 숲을 보여주는 멋진 재귀 CTE를 가져왔습니다. 이것은 좋은 진단 도구입니다. 그리고 이를 기반으로 모니터링을 구축할 수도 있습니다. 그러나 이것은 신중하게 이루어져야 합니다. 자신을 위해 작은 statement_timeout을 만들어야 합니다. 그리고 lock_timeout이 바람직합니다.

때로는 이러한 모든 오류가 합산되어 발생합니다.

제 생각에는 여기에서 가장 큰 실수는 조직적입니다. 기술이 당기지 않기 때문에 조직적입니다. 이것은 2번입니다 - 그들은 잘못된 장소에 체크인했습니다.

확인하기 쉬운 프로덕션 클론이 없었기 때문에 잘못된 위치를 확인했습니다. 개발자는 프로덕션에 전혀 액세스하지 못할 수 있습니다.

그리고 우리는 거기에 있지 않은지 확인했습니다. 그곳에서 확인했다면 직접 보았을 것입니다. 개발자는 DBA 없이도 같은 데이터와 같은 위치에 있는 좋은 환경에서 확인했다면 다 보았습니다. 그는 이 모든 타락을 보았을 것이고 부끄러워했을 것입니다.

autovacuum에 대해 자세히 알아보십시오. 수백만 줄의 대규모 스윕을 수행한 후에도 여전히 REPACK을 수행해야 합니다. 이는 인덱스에 특히 중요합니다. 우리가 거기에서 모든 것을 청소한 후에 그들은 기분이 나쁠 것입니다.

그리고 일상적인 청소 작업을 되돌리고 싶다면 더 자주 하지만 더 작은 규모로 청소하는 것이 좋습니다. XNUMX분에 한 번 또는 조금 더 자주 할 수 있습니다. 그리고 두 가지를 모니터링해야 합니다. 이 항목에는 오류가 없고 뒤처지지 않습니다. 제가 보여드린 트릭이 바로 해결해 줄 것입니다.

우리가 하는 일은 오픈 소스입니다. GitLab에 게시되어 있습니다. 그리고 DBA 없이도 사람들이 확인할 수 있도록 만듭니다. 우리는 데이터베이스 랩을 수행하고 있습니다. 즉, Joe가 현재 작업하고 있는 기본 구성 요소를 호출합니다. 그리고 프로덕션 사본을 얻을 수 있습니다. 이제 슬랙을 위한 Joe의 구현이 있습니다. "이러한 요청을 설명하십시오"라고 말하면 즉시 데이터베이스 복사본에 대한 결과를 얻을 수 있습니다. 거기에서 DELETE도 할 수 있으며 아무도 눈치 채지 못할 것입니다.

10테라바이트가 있다고 가정하면 데이터베이스 랩도 10테라바이트로 만듭니다. 또한 동시 10테라바이트 데이터베이스로 10명의 개발자가 동시에 작업할 수 있습니다. 누구나 원하는 것을 할 수 있습니다. 삭제, 삭제 등을 할 수 있습니다. 정말 환상적입니다. 내일 이것에 대해 이야기하겠습니다.

이를 씬 프로비저닝이라고 합니다. 이것은 미묘한 프로비저닝입니다. 이것은 개발 지연, 테스트 지연을 크게 제거하고 이와 관련하여 세상을 더 나은 곳으로 만드는 일종의 환상입니다. 즉, 대량 작업의 문제를 피할 수 있습니다.

예: 5테라바이트 데이터베이스, 30초 이내에 사본 가져오기. 그리고 그것은 크기에도 의존하지 않습니다. 즉, 몇 테라바이트인지는 중요하지 않습니다.

오늘 당신은 갈 수 있습니다 postgres.ai 도구를 파헤쳐 보세요. 등록하면 무엇이 있는지 확인할 수 있습니다. 이 봇을 설치할 수 있습니다. 그것은 무료입니다. 쓰다.

질문

실제 상황에서 매우 자주 테이블에 남아 있어야 하는 데이터가 삭제해야 하는 데이터보다 훨씬 적습니다. 즉, 이러한 상황에서 새 개체를 생성하고 거기에 필요한 데이터만 복사하고 이전 테이블을 트렁킹하는 것이 더 쉬울 때 이러한 접근 방식을 구현하는 것이 더 쉬운 경우가 많습니다. 전환하는 동안 이 순간에 프로그래밍 방식의 접근 방식이 필요하다는 것은 분명합니다. 이 접근법은 어떻습니까?

이것은 매우 좋은 접근 방식이며 매우 좋은 작업입니다. pg_repack이 하는 일과 매우 유사하며, ID를 4바이트로 만들 때 수행해야 하는 일과 매우 유사합니다. 많은 프레임워크가 몇 년 전에 이 작업을 수행했으며 플레이트만 커졌고 8바이트로 변환해야 합니다.

이 작업은 상당히 어렵습니다. 우리는 해냈다. 그리고 매우 조심해야 합니다. 잠금 장치 등이 있지만 완료되고 있습니다. 즉, 표준 접근 방식은 pg_repack을 사용하는 것입니다. 그러한 레이블을 선언합니다. 그리고 스냅샷 데이터를 업로드하기 전에 모든 변경 사항을 추적하는 하나의 플레이트도 선언합니다. 일부 변경 사항을 추적하지 못할 수도 있는 트릭이 있습니다. 미묘함이 있습니다. 그런 다음 롤링 변경으로 전환합니다. 모든 사람을 종료할 때 잠시 일시 중지되지만 일반적으로 이 작업이 완료되고 있습니다.

GitHub의 pg_repack을 보면 거기에 ID를 int 4에서 int 8로 변환하는 작업이 있을 때 pg_repack 자체를 사용하는 아이디어가 있었습니다. 이것은 또한 가능하지만 약간의 해킹이지만 이것도 작동합니다. pg_repack이 사용하는 트리거에 개입하여 "우리는 이 데이터가 필요하지 않습니다"라고 말할 수 있습니다. 즉, 필요한 것만 전송합니다. 그런 다음 그는 전환하기만 하면 됩니다.

이 접근 방식을 사용하면 데이터가 이미 인덱싱되고 아름다운 인덱스로 매우 고르게 쌓인 테이블의 두 번째 복사본을 얻을 수 있습니다.

부풀림이 없으며 좋은 접근 방식입니다. 그러나 이에 대한 자동화를 개발하려는 시도, 즉 범용 솔루션을 만들려는 시도가 있다는 것을 알고 있습니다. 이 자동화로 연결해 드릴 수 있습니다. 그것은 좋은 것입니다 파이썬으로 작성되었습니다.

나는 MySQL의 세계에서 조금 왔기 때문에 들어 왔습니다. 그리고 우리는 이 접근법을 사용합니다.

단, 90%일 경우에만 가능합니다. 5 %가 있으면 사용하기에 좋지 않습니다.

신고해 주셔서 감사합니다! 제품의 전체 복사본을 만들 리소스가 없는 경우 부하 또는 크기를 계산하는 알고리즘이나 공식이 있습니까?

좋은 질문. 지금까지 우리는 멀티 테라바이트 데이터베이스를 찾을 수 있었습니다. 예를 들어 메모리가 적고 프로세서가 적고 디스크가 정확히 같지는 않지만 하드웨어가 동일하지 않더라도 여전히 그렇게 합니다. 아무데도 없다면 생각해야합니다. 내일까지 생각하겠습니다. 당신이 왔고, 우리는 이야기 할 것입니다. 이것은 좋은 질문입니다.

신고해 주셔서 감사합니다! 당신은 이런저런 제한이 있지만 발전하고 있는 멋진 Postgres가 있다는 사실에 대해 처음 시작했습니다. 그리고 이것은 대체로 목발입니다. 이 모든 것이 Postgres 자체의 개발과 충돌하지 않습니까? 일부 DELETE 종속 항목이 나타나거나 여기에서 이상한 수단으로 더럽히려는 것을 낮은 수준으로 유지해야 하는 다른 것이 있습니까?

SQL에서 하나의 트랜잭션에서 많은 레코드를 삭제하거나 업데이트하라고 말했다면 Postgres는 어떻게 이를 거기에 배포할 수 있습니까? 우리는 물리적으로 운영에 제한이 있습니다. 우리는 여전히 오랫동안 그것을 할 것입니다. 그리고 우리는 이 시간에 잠글 것입니다.

인덱스는 끝났습니다.

동일한 체크포인트 조정을 자동화할 수 있다고 가정할 수 있습니다. 언젠가는 그럴 수도 있습니다. 그러나 나는 그 질문을 정말로 이해하지 못합니다.

문제는 여기저기서 진행되는 개발 벡터가 있고 여기에서 귀하의 것과 평행하게 진행되는 개발 벡터가 있습니까? 저것들. 그들은 아직 그것에 대해 생각하지 않았습니까?

지금 사용할 수 있는 원리에 대해 이야기했습니다. 다른 봇이 있습니다 낸시, 이것으로 자동 체크포인트 튜닝을 할 수 있습니다. 언젠가 Postgres에 포함될 예정입니까? 아직 논의조차 되지 않았는지 모르겠습니다. 우리는 아직 멀었습니다. 그러나 새로운 시스템을 만드는 과학자들이 있습니다. 그리고 그들은 우리를 자동 색인으로 밀어 넣었습니다. 개발이 있습니다. 예를 들어 자동 튜닝을 볼 수 있습니다. 자동으로 매개변수를 선택합니다. 그러나 그는 아직 당신을 위해 체크포인트 조정을 하지 않을 것입니다. 즉, 성능, 셸 버퍼 등을 위해 선택합니다.

그리고 체크포인트 조정을 위해 다음과 같이 할 수 있습니다. 클라우드에 수천 개의 클러스터와 다른 하드웨어, 다른 가상 머신이 있는 경우 봇을 사용할 수 있습니다. 낸시 자동화를 합니다. 그리고 max_wal_size는 목표 설정에 따라 자동으로 선택됩니다. 그러나 지금까지 이것은 불행하게도 핵심에 가깝지도 않습니다.

좋은 오후에요 장기 거래의 위험성에 대해 말씀하셨습니다. 삭제 시 autovacuum이 차단된다고 하셨습니다. 그것이 우리에게 또 어떤 해를 끼치나요? 우리는 공간을 확보하고 사용할 수 있는 것에 대해 더 많이 이야기하고 있기 때문입니다. 우리는 또 무엇을 놓치고 있습니까?

Autovacuum은 여기서 가장 큰 문제가 아닐 수 있습니다. 그리고 긴 트랜잭션이 다른 트랜잭션을 잠글 수 있다는 사실, 이 가능성은 더 위험합니다. 그녀는 만날 수도 있고 만나지 않을 수도 있습니다. 그녀가 만난다면 그것은 매우 나쁠 수 있습니다. 그리고 autovacuum으로 - 이것은 또한 문제입니다. OLTP의 긴 트랜잭션에는 잠금과 자동 진공이라는 두 가지 문제가 있습니다. 복제본에서 상시 대기 피드백을 활성화한 경우에도 마스터에서 autovacuum 잠금을 수신하고 복제본에서 도착합니다. 그러나 적어도 자물쇠는 없을 것입니다. 그리고 록이 있을 것입니다. 우리는 데이터 변경에 대해 이야기하고 있으므로 여기서 잠금은 중요한 포인트입니다. 그리고 이것이 오랫동안 지속된다면 점점 더 많은 트랜잭션이 잠깁니다. 그들은 다른 사람을 훔칠 수 있습니다. 그리고 록 트리가 나타납니다. 스 니펫에 대한 링크를 제공했습니다. 그리고 이 문제는 축적만 가능한 autovacuum의 문제보다 더 빨리 눈에 띕니다.

신고해 주셔서 감사합니다! 테스트를 잘못했다고 말하면서 보고서를 시작했습니다. 우리는 같은 방식으로 기지와 함께 동일한 장비를 가져와야한다는 생각을 계속했습니다. 개발자에게 기반을 제공했다고 가정해 보겠습니다. 그리고 그는 요청에 따랐다. 그리고 그는 괜찮은 것 같습니다. 그러나 그는 라이브를 확인하지 않지만 라이브를 위해 예를 들어 60-70%의 부하가 있습니다. 그리고 이 튜닝을 사용해도 잘 되지 않습니다.

팀에 전문가가 있고 실제 백그라운드 로드에서 발생할 일을 예측할 수 있는 DBA 전문가를 사용하는 것이 중요합니다. 깨끗한 변경 사항을 실행했을 때 그림을 볼 수 있습니다. 그러나 보다 진보된 접근 방식으로 동일한 작업을 다시 수행하지만 프로덕션으로 시뮬레이션된 로드를 사용합니다. 꽤 멋지다. 그때까지 성장해야 합니다. 어른같습니다. 우리는 우리가 가진 것을 살펴보고 충분한 자원이 있는지도 살펴보았습니다. 그건 좋은 질문이야.

이미 가비지 선택을 수행하고 있고 예를 들어 삭제된 플래그가 있는 경우

이것이 Postgres에서 autovacuum이 자동으로 수행하는 작업입니다.

오, 그는 그것을합니까?

Autovacuum은 가비지 수집기입니다.

감사합니다!

신고해 주셔서 감사합니다! 모든 쓰레기가 기본 테이블에서 옆으로 더러워지는 방식으로 파티셔닝을 사용하여 데이터베이스를 즉시 설계하는 옵션이 있습니까?

물론 있습니다.

사용하지 말아야 할 테이블을 잠근 경우 우리 자신을 보호할 수 있습니까?

물론 있습니다. 그러나 그것은 닭고기와 달걀 문제와 같습니다. 미래에 무슨 일이 일어날지 우리 모두가 안다면 당연히 모든 일을 멋지게 할 것입니다. 그러나 비즈니스는 변화하고 있으며 새로운 칼럼과 새로운 요청이 있습니다. 그런 다음 – 죄송합니다. 제거하고 싶습니다. 그러나이 이상적인 상황은 인생에서 발생하지만 항상 그런 것은 아닙니다. 그러나 전반적으로 좋은 생각입니다. 잘라내기만 하면 됩니다.

출처 : habr.com