PostgreSQL 튜닝을 위한 산업적 접근 방식: 데이터베이스 실험.” 니콜라이 사모크발로프

Nikolai Samokhvalov의 보고서 "PostgreSQL 튜닝에 대한 산업적 접근 방식: 데이터베이스 실험"의 내용을 읽어 보시기 바랍니다.

Shared_buffers = 25% – 많습니까, 적습니까? 아니면 그냥 맞나요? 다소 오래된 권장 사항이 귀하의 특별한 경우에 적합한지 어떻게 알 수 있나요?

이제 "성인처럼" postgresql.conf 매개변수를 선택하는 문제에 접근할 때입니다. 맹목적인 "자동 튜너"의 도움이나 기사 및 블로그의 오래된 조언이 아니라 다음을 기반으로 합니다.

1. 데이터베이스에 대한 엄격하게 검증된 실험은 자동으로 대량으로 수행되며 가능한 한 "전투"에 가까운 조건에서 수행됩니다.
2. DBMS와 OS의 기능에 대한 깊은 이해.

낸시 CLI 사용(https://gitlab.com/postgres.ai/nancy), 다양한 상황, 다양한 프로젝트에서 특정 예(악명 높은 shared_buffers)를 살펴보고 인프라, 데이터베이스 및 로드에 대한 최적의 설정을 선택하는 방법을 알아내려고 노력할 것입니다.

데이터베이스를 이용한 실험에 대해 이야기하겠습니다. XNUMX개월 조금 넘게 지속된 이야기입니다.

나에 대해 조금. 14년 넘게 Postgres를 사용해 본 경험이 있습니다. 다수의 소셜 네트워킹 회사가 설립되었습니다. Postgres는 모든 곳에서 사용되었으며 현재도 사용되고 있습니다.

또한 세계 2위인 Meetup의 RuPostgres 그룹도 있습니다. 서서히 2명에 가까워지고 있습니다. RuPostgres.org.

그리고 Highload를 비롯한 다양한 컨퍼런스의 PC에서는 처음부터 데이터베이스, 특히 Postgres를 담당하고 있습니다.

그리고 지난 몇 년 동안 저는 여기에서 11개 시간대에 대한 Postgres 컨설팅 업무를 다시 시작했습니다.

그리고 몇 년 전 이 작업을 수행했을 때 아마도 2010년부터 Postgres를 사용한 수동 작업이 잠시 중단되었습니다. DBA의 업무 루틴은 거의 변하지 않았고, 여전히 육체 노동을 얼마나 많이 사용해야 하는지 놀랐습니다. 그리고 저는 즉시 여기에 뭔가 문제가 있다고 생각했습니다. 모든 것을 더 많이 자동화해야 합니다.

그리고 모두 원격이었기 때문에 대부분의 클라이언트가 클라우드에 있었습니다. 그리고 분명히 많은 것이 이미 자동화되었습니다. 이에 대해서는 나중에 자세히 설명합니다. 즉, 이 모든 것은 많은 수의 데이터베이스를 관리할 수 있도록 거의 모든 DBA 작업을 자동화하는 일종의 플랫폼과 같은 여러 도구가 있어야 한다는 아이디어로 이어졌습니다.

이 보고서에는 다음이 포함되지 않습니다.

• "만능 해결책" 및 다음과 같은 설명 - 8GB 또는 25% shared_buffers를 설정하면 괜찮을 것입니다. shared_buffers에 관한 내용은 많지 않습니다.
• 하드코어 "내부".

무슨 일이 일어날까요?

• 우리가 적용하고 개발하는 최적화 원칙이 있을 것입니다. 그 과정에서 온갖 종류의 아이디어가 나올 것이며 우리가 대부분 오픈 소스에서 만드는 다양한 도구, 즉 우리는 오픈 소스에서 기초를 만듭니다. 또한 티켓이 있으며 모든 커뮤니케이션은 사실상 오픈 소스입니다. 현재 진행 중인 작업, 다음 릴리스에 포함될 작업 등을 확인할 수 있습니다.
• 또한 소규모 스타트업부터 대기업에 이르기까지 여러 회사에서 이러한 원칙과 도구를 사용하는 경험이 있을 것입니다.

이 모든 것이 어떻게 발전하고 있습니까?

첫째, 인스턴스 생성, 백업 배포 등을 보장하는 것 외에도 DBA의 주요 임무는 병목 현상을 찾아 성능을 최적화하는 것입니다.

이제 이렇게 설정되었습니다. 모니터링을 보면 뭔가 보이지만 세부 사항이 누락되어 있습니다. 우리는 대개 손으로 더 주의 깊게 파기 시작하고 어떤 식으로든 그것을 어떻게 해야 할지 이해합니다.

그리고 두 가지 접근 방식이 있습니다. Pg_stat_statements는 느린 쿼리를 식별하기 위한 기본 솔루션입니다. 그리고 pgBadger를 사용하여 Postgres 로그를 분석합니다.

각 접근 방식에는 심각한 단점이 있습니다. 첫 번째 접근 방식에서는 모든 매개변수를 삭제했습니다. 그리고 열이 "?"와 같은 그룹 SELECT * FROM 테이블을 보면 또는 Postgres 10부터 "$"입니다. 이것이 인덱스 스캔인지 시퀀스 스캔인지 알 수 없습니다. 이는 매개변수에 따라 크게 달라집니다. 거기에 좀처럼 접하지 않는 값을 대입하면 인덱스 스캔이 된다. 거기에 테이블의 90%를 차지하는 값을 대체하면 Postgres가 통계를 알고 있기 때문에 seq 스캔이 분명해집니다. 일부 작업이 진행 중이지만 이는 pg_stat_statements의 큰 단점입니다.

로그 분석의 가장 큰 단점은 원칙적으로 "log_min_duration_statement = 0"을 감당할 수 없다는 점이다. 그리고 우리는 이것에 대해서도 이야기할 것입니다. 따라서 전체 그림을 볼 수 없습니다. 그리고 매우 빠른 일부 쿼리는 엄청난 양의 리소스를 소비할 수 있지만 임계값 미만이기 때문에 표시되지 않습니다.

DBA는 발견한 문제를 어떻게 해결합니까?

예를 들어, 몇 가지 문제를 발견했습니다. 일반적으로 무엇을 합니까? 개발자라면 크기가 같지 않은 일부 인스턴스에서 작업을 수행하게 될 것입니다. DBA라면 스테이징이 있습니다. 그리고 하나만 있을 수 있습니다. 그리고 그는 XNUMX개월 뒤처졌습니다. 그리고 당신은 생산에 들어갈 것이라고 생각합니다. 경험이 풍부한 DBA라도 프로덕션에서 복제본을 체크인할 수 있습니다. 그리고 임시 색인을 생성하여 도움이 되는지 확인하고 이를 삭제한 후 개발자에게 제공하여 마이그레이션 파일에 넣을 수 있도록 합니다. 이것이 지금 일어나고 있는 말도 안되는 일입니다. 그리고 이것이 문제입니다.

• 구성을 조정합니다.
• 인덱스 세트를 최적화합니다.
• SQL 쿼리 자체를 변경합니다(가장 어려운 방법입니다).
• 용량을 추가합니다(대부분의 경우 가장 쉬운 방법).

이런 일들이 많이 일어나고 있습니다. Postgres에는 많은 핸들이 있습니다. 알아야 할 것이 많습니다. Postgres에는 많은 인덱스가 있습니다. 이 컨퍼런스의 주최자에게도 감사드립니다. 그리고 이 모든 것을 알아야 하며, 이것이 비DBA가 DBA가 흑마술을 연습하는 것처럼 느끼게 만드는 이유입니다. 즉, 이 모든 것을 정상적으로 이해하려면 10년 동안 공부해야 합니다.

그리고 나는 이 흑마법에 맞서 싸우는 전사입니다. 나는 기술이 있고이 모든 것에 직관이 없도록 모든 것을하고 싶습니다.

생활 예

나는 내 프로젝트를 포함하여 적어도 두 개의 프로젝트에서 이것을 관찰했습니다. 또 다른 블로그 게시물에서는 default_statistict_target 값이 1이면 좋다고 말합니다. 좋아요, 프로덕션 환경에서 시도해 보겠습니다.

그리고 XNUMX년 후 우리의 도구를 사용하여 오늘 우리가 이야기하고 있는 데이터베이스에 대한 실험을 통해 과거의 것과 현재의 것을 비교할 수 있습니다.

이를 위해서는 실험을 만들어야 합니다. 그것은 네 부분으로 구성됩니다.

• 첫 번째는 환경입니다. 하드웨어가 필요해요. 그리고 어떤 회사에 와서 계약을 하면 생산할 때와 똑같은 하드웨어를 주라고 하더군요. 각 마스터마다 이와 같은 하드웨어가 하나 이상 필요합니다. 이것은 Amazon이나 Google의 인스턴스 가상 머신이거나 정확히 동일한 하드웨어가 필요합니다. 즉, 환경을 재현하고 싶습니다. 그리고 환경 개념에는 Postgres의 주요 버전이 포함됩니다.
• 두 번째 부분은 우리 연구의 대상입니다. 이것은 데이터베이스입니다. 여러 가지 방법으로 만들 수 있습니다. 방법을 보여드리겠습니다.
• 세 번째 부분은 부하입니다. 이것은 가장 어려운 순간입니다.
• 그리고 네 번째 부분은 우리가 확인하는 것, 즉 무엇을 무엇과 비교할 것인지입니다. 구성에서 하나 이상의 매개변수를 변경하거나 색인 등을 생성할 수 있다고 가정해 보겠습니다.

우리는 실험을 시작하고 있습니다. 여기 pg_stat_statements가 있습니다. 왼쪽은 무슨 일이 일어났는지 입니다. 오른쪽 - 무슨 일이 있었나요?

왼쪽에서는 default_statistics_target = 100, 오른쪽에서는 =1. 이것이 도움이 되었음을 알 수 있습니다. 전반적으로 모든 것이 000% 향상되었습니다.

그러나 아래로 스크롤하면 pgBadger 또는 pg_stat_statements의 요청 그룹이 있을 것입니다. 두 가지 옵션이 있습니다. 일부 요청이 88% 감소한 것을 볼 수 있습니다. 그리고 여기에 엔지니어링 접근 방식이 있습니다. 왜 가라앉았는지 궁금하기 때문에 더 안쪽을 파헤쳐 볼 수 있습니다. 통계에 무슨 일이 일어났는지 이해해야 합니다. 통계에 버킷이 많을수록 결과가 더 나빠지는 이유

또는 파헤칠 수 없지만 "ALTER TABLE ... ALTER COLUMN"을 수행하고 이 열의 통계에 100개의 버킷을 반환합니다. 그런 다음 또 다른 실험을 통해 이 패치가 도움이 되었는지 확인할 수 있습니다. 모두. 이는 직관보다는 데이터를 기반으로 큰 그림을 보고 결정을 내리는 데 도움이 되는 엔지니어링 접근 방식입니다.

다른 지역의 몇 가지 예. 수년 동안 테스트에는 CI 테스트가 있었습니다. 그리고 자동화된 테스트 없이는 제대로 된 프로젝트가 없을 것입니다.

다른 산업 분야: 항공, 자동차 산업에서는 공기역학을 테스트할 때 실험을 할 기회도 있습니다. 우리는 그림에서 직접 우주로 무언가를 발사하지 않을 것이며, 자동차를 즉시 트랙으로 가져가지 않을 것입니다. 예를 들어 풍동이 있습니다.

우리는 다른 산업을 관찰하여 결론을 도출할 수 있습니다.

첫째, 우리는 특별한 환경을 가지고 있습니다. 생산에 가깝지만 가깝지는 않습니다. 주요 특징은 저렴하고 반복 가능하며 최대한 자동화되어야 한다는 것입니다. 그리고 상세한 분석을 수행하기 위한 특별한 도구도 있어야 합니다.

아마도 비행기를 이륙하고 비행할 때 풍동에서보다 날개 표면의 모든 밀리미터를 연구할 기회가 더 적을 것입니다. 더 많은 진단 도구가 있습니다. 우리는 비행기에 실을 수 없는 무거운 물건을 더 많이 운반할 여유가 있습니다. 포스트그레스와 동일합니다. 경우에 따라 실험 중에 전체 쿼리 로깅을 활성화할 수도 있습니다. 그리고 우리는 프로덕션 환경에서 이런 일을 하고 싶지 않습니다. auto_explain을 사용하여 이를 활성화할 계획도 있습니다.

제가 말했듯이 높은 수준의 자동화는 버튼을 누르고 반복하는 것을 의미합니다. 이렇게 해야 많은 실험이 가능하고 스트리밍이 가능합니다.

Nancy CLI - "데이터베이스 연구소"의 기초

그래서 우리는 이 일을 했습니다. 즉, 저는 거의 XNUMX년 전인 XNUMX월에 이러한 아이디어에 대해 이야기했습니다. 그리고 우리는 이미 오픈 소스에 Nancy CLI라는 것을 가지고 있습니다. 이것이 데이터베이스 연구실을 구축하기 위한 기반이다.

낸시 — Gitlab의 오픈 소스에 있습니다. 당신은 그것을 말할 수 있습니다, 당신은 그것을 시도할 수 있습니다. 슬라이드에 링크를 걸어두었습니다. 그것을 클릭하면 거기에 있을 것입니다. 도움 모든면에서.

물론 아직 개발 중인 부분도 많습니다. 거기에는 많은 아이디어가 있습니다. 하지만 이것은 우리가 거의 매일 사용하는 것입니다. 그리고 아이디어가 생기면 40개의 라인을 삭제하면 모든 것이 IO로 귀결되는 이유는 무엇입니까? 그런 다음 실험을 수행하고 더 자세히 살펴보고 무슨 일이 일어나고 있는지 이해한 다음 즉석에서 수정하려고 시도할 수 있습니다. 즉, 우리는 실험을 하고 있습니다. 예를 들어, 우리는 무언가를 조정하고 결국 어떤 일이 일어나는지 확인합니다. 그리고 우리는 프로덕션 환경에서는 이런 일을 하지 않습니다. 이것이 아이디어의 본질입니다.

이것은 어디에서 작동할 수 있나요? 이는 로컬에서 작동할 수 있습니다. 즉, 어디서나 수행할 수 있으며 MacBook에서도 실행할 수 있습니다. 도커가 필요해요. 가자. 그게 다야. 하드웨어나 가상 머신 등 어느 곳에서든 실행할 수 있습니다.

또한 EC2 인스턴스의 Amazon에서 원격으로 실행할 수 있는 기회도 있습니다. 그리고 이것은 매우 멋진 기회입니다. 예를 들어, 어제 우리는 i500 인스턴스에 대해 가장 어린 인스턴스부터 시작하여 i3-3-xlarge까지 16개 이상의 실험을 수행했습니다. 그리고 500번의 실험에 64달러의 비용이 들었습니다. 각각 15분 동안 지속되었습니다. 즉, 스팟이 사용된다는 사실로 인해 매우 저렴합니다. Amazon의 초당 청구액 인 70 % 할인입니다. 당신은 많은 일을 할 수 있습니다. 실제 연구를 할 수 있습니다.

그리고 Postgres의 세 가지 주요 버전이 지원됩니다. 일부 오래된 버전과 새로운 12번째 버전을 완성하는 것은 그리 어렵지 않습니다.

우리는 세 가지 방법으로 객체를 정의할 수 있습니다. 이것:

• 덤프/SQL 파일.
• 주요 방법은 PGDATA 디렉토리를 복제하는 것입니다. 원칙적으로 백업 서버에서 가져옵니다. 일반 바이너리 백업이 있는 경우 거기에서 클론을 만들 수 있습니다. 클라우드가 있다면 Amazon 및 Google과 같은 클라우드 오피스가 이를 대신해 드립니다. 이는 실제 프로덕션을 복제하는 가장 중요한 방법입니다. 이것이 우리가 펼치는 방식입니다.
• 그리고 마지막 방법은 Postgres에서 어떤 것이 어떻게 작동하는지 이해하려는 연구에 적합합니다. pgbench 입니다. pgbench를 사용하여 생성할 수 있습니다. 그것은 단지 하나의 "db-pgbench" 옵션일 뿐입니다. 당신은 그에게 규모를 말해줍니다. 그리고 명시된 대로 모든 것이 클라우드에서 생성됩니다.

그리고 다음을 로드하세요.

• 하나의 SQL 스레드에서 로드를 실행할 수 있습니다. 이것이 가장 원시적인 방법이다.
• 그리고 우리는 로드를 에뮬레이트할 수 있습니다. 그리고 우리는 우선 다음과 같은 방법으로 이를 에뮬레이트할 수 있습니다. 모든 로그를 수집해야 합니다. 그리고 그것은 고통스럽습니다. 그 이유를 보여드리겠습니다. 그리고 Nancy에 내장된 pgreplay를 사용하여 플레이합니다.
• 아니면 다른 옵션. 우리는 어느 정도의 노력을 들여 작업을 수행합니다. 전투 시스템의 현재 부하를 분석하여 가장 높은 요청 그룹을 추출합니다. 그리고 pgbench를 사용하면 실험실에서 이 부하를 에뮬레이션할 수 있습니다.

• 일종의 SQL을 수행해야 합니다. 즉, 일종의 마이그레이션을 확인하고 거기에 인덱스를 생성하고 거기에서 ANALAZE를 실행해야 합니다. 그리고 우리는 진공이 있기 전과 진공이 끝난 후에 무슨 일이 일어났는지 살펴봅니다. 일반적으로 모든 SQL.
• 구성에서 하나 이상의 매개변수를 변경합니다. 예를 들어 테라바이트 데이터베이스에 대해 Amazon에서 100개의 값을 확인하라고 말할 수 있습니다. 그리고 몇 시간 안에 결과가 나올 것입니다. 일반적으로 테라바이트 데이터베이스를 배포하는 데 몇 시간이 걸립니다. 하지만 개발 중인 패치가 있습니다. 일련의 패치가 가능합니다. 즉, 동일한 서버에서 동일한 pgdata를 일관되게 사용하고 확인할 수 있습니다. Postgres가 다시 시작되고 캐시가 재설정됩니다. 그리고 당신은 부하를 운전할 수 있습니다.

• pg 스냅샷부터 시작하여 다양한 파일과 함께 디렉토리가 도착합니다.스탯***. 그리고 가장 흥미로운 것은 pg_stat_statements, pg_stat_kcacke입니다. 이는 요청을 분석하는 두 가지 확장입니다. 그리고 pg_stat_bgwriter에는 pgwriter 통계뿐만 아니라 체크포인트와 백엔드 자체가 더티 버퍼를 대체하는 방법도 포함되어 있습니다. 그리고 보는 것은 모두 흥미 롭습니다. 예를 들어, shared_buffers를 설정할 때 모든 사람이 얼마나 교체되었는지 확인하는 것은 매우 흥미롭습니다.
• Postgres 로그도 도착합니다. 두 개의 로그 – 준비 로그와 로드 재생 로그.
• 비교적 새로운 기능은 FlameGraphs입니다.
• 또한 로드 재생을 위해 pgreplay 또는 pgbench 옵션을 사용한 경우 해당 출력은 기본 출력이 됩니다. 그리고 대기 시간과 TPS가 표시됩니다. 그들이 그것을 어떻게 보았는지 이해하는 것이 가능할 것입니다.
• 시스템 정보.
• 기본 CPU 및 IO 검사. 이는 Amazon의 EC2 인스턴스에 더 많은 것입니다. 스레드에서 100개의 동일한 인스턴스를 시작하고 그곳에서 100개의 다른 실행을 실행하려는 경우 10번의 실험을 하게 됩니다. 그리고 이미 누군가에 의해 억압받고 있는 결함 있는 사례를 접하지 않도록 해야 합니다. 다른 사람들은 이 하드웨어를 사용하고 있으며 리소스가 거의 남아 있지 않습니다. 그러한 결과는 폐기하는 것이 좋습니다. 그리고 Alexey Kopytov의 sysbench의 도움으로 우리는 다른 것과 비교할 수 있는 몇 가지 간단한 검사를 수행합니다. 즉, CPU 작동 방식과 IO 작동 방식을 이해할 수 있습니다.

여러 회사의 사례를 보면 기술적인 어려움은 무엇입니까?

로그를 사용하여 실제 로드를 반복한다고 가정해 보겠습니다. 오픈 소스 pgreplay에 작성하면 좋은 아이디어입니다. 우리는 그것을 사용합니다. 하지만 제대로 작동하려면 매개변수와 타이밍을 사용하여 전체 쿼리 로깅을 활성화해야 합니다.

기간과 타임스탬프에는 몇 가지 문제가 있습니다. 우리는 이 부엌 전체를 비울 거예요. 가장 중요한 질문은 당신이 그것을 감당할 수 있는지 여부입니다.

https://gist.github.com/NikolayS/08d9b7b4845371d03e195a8d8df43408

문제는 사용하지 못할 수도 있다는 것입니다. 우선, 로그에 어떤 스트림이 기록될 것인지 이해해야 합니다. pg_stat_statements가 있는 경우 이 쿼리(링크는 슬라이드에 있음)를 사용하여 초당 기록되는 바이트 수를 대략적으로 이해할 수 있습니다.

요청의 길이를 살펴봅니다. 매개변수가 없다는 사실을 무시하고 있지만 요청 길이와 초당 실행 횟수를 알고 있습니다. 이 방법으로 대략 초당 바이트 수를 추정할 수 있습니다. 우리는 두 배의 실수를 할 수도 있지만, 이런 식으로 순서를 확실히 이해하게 될 것입니다.

이 요청이 초당 802번 실행되는 것을 볼 수 있습니다. 그리고 초당 bytes_per - 300 kB/s가 플러스 또는 마이너스로 기록되는 것을 볼 수 있습니다. 그리고 원칙적으로 우리는 그러한 흐름을 감당할 수 있습니다.

하지만! 사실은 다양한 로깅 시스템이 있다는 것입니다. 그리고 사람들의 기본값은 대개 "syslog"입니다.

그리고 syslog가 있으면 다음과 같은 그림이 나타날 수 있습니다. pgbench를 사용하여 쿼리 로깅을 활성화하고 무슨 일이 일어나는지 살펴보겠습니다.

로깅하지 않음 - 왼쪽 열입니다. 우리는 161TPS를 얻었습니다. syslog를 사용하면 Amazon의 Ubuntu 000에서 16.04TPS를 얻을 수 있습니다. 그리고 두 가지 다른 로깅 방법으로 변경하면 상황이 훨씬 좋아집니다. 즉, 우리는 하락할 것으로 예상했지만 같은 정도는 아니었습니다.

그리고 Journald도 참여하여 쉽게 검색할 수 있도록 로그를 바이너리 형식으로 변환하는 CentOS 7에서는 TPS가 44배나 떨어집니다.

그리고 이것이 사람들이 살아가는 이유입니다. 그리고 종종 회사, 특히 대규모 회사에서는 이를 변경하기가 매우 어렵습니다. syslog에서 벗어날 수 있다면, syslog에서 벗어나시기 바랍니다.

• IOPS 및 쓰기 흐름을 평가합니다.
• 로깅 시스템을 확인하세요.
• 예상 부하가 지나치게 큰 경우 샘플링을 고려하십시오.

pg_stat_statements가 있습니다. 내가 말했듯이 그것은 거기에 있어야합니다. 그리고 각 요청 그룹을 파일에서 특별한 방식으로 취하고 설명할 수 있습니다. 그런 다음 pgbench의 매우 편리한 기능을 사용할 수 있습니다. 이는 "-f" 옵션을 사용하여 여러 파일을 삽입하는 기능입니다.

"-f"를 많이 이해합니다. 그리고 마지막에 "@"을 사용하여 각 파일이 어떤 공유를 가져야 하는지 알 수 있습니다. 즉, 10%의 경우에 이런 일이 발생하고 20%의 경우에 이런 일이 발생한다고 말할 수 있습니다. 그리고 이를 통해 우리가 생산 현장에서 보는 것과 더 가까워질 것입니다.

우리가 생산에 가지고 있는 것을 어떻게 이해할 것인가? 어떤 몫과 어떻게? 이것은 약간의 제쳐두고 있습니다. 제품이 하나 더 있어요 포스트그레스 점검. 또한 오픈 소스의 기반입니다. 그리고 우리는 현재 이를 적극적으로 개발하고 있습니다.

그는 약간 다른 이유로 태어났습니다. 모니터링만으로는 충분하지 않기 때문입니다. 즉, 와서 기반을 살펴보고 존재하는 문제를 살펴보십시오. 그리고 원칙적으로 health_check를 수행합니다. 숙련된 DBA라면 health_check를 수행하세요. 인덱스 사용 등을 살펴보았습니다. OKmeter가 있다면 좋습니다. 이것은 Postgres에 대한 멋진 모니터링입니다. OKmeter.io – 설치해 주세요. 모든 것이 잘 완료되었습니다. 유료입니다.

하나도 없으면 일반적으로 많지 않습니다. 모니터링에는 일반적으로 CPU, IO, 예약이 있고 그게 전부입니다. 그리고 우리는 더 많은 것이 필요합니다. autovacuum이 어떻게 작동하는지, 체크포인트가 어떻게 작동하는지 확인해야 합니다. io에서는 체크포인트를 bgwriter 및 백엔드 등에서 분리해야 합니다.

문제는 대기업에 도움을 줄 때 뭔가를 빨리 구현하지 못한다는 점이다. 그들은 OKmeter를 빨리 구입할 수 없습니다. 아마도 그들은 XNUMX개월 안에 그것을 살 것입니다. 일부 패키지는 신속하게 배송할 수 없습니다.

그리고 우리는 아무것도 설치할 필요가 없는 특별한 도구가 필요하다는 아이디어를 내놓았습니다. 즉, 프로덕션 환경에서는 아무것도 설치할 필요가 없습니다. 노트북에 설치하거나 실행할 관찰 서버에 설치하세요. 그리고 운영 체제, 파일 시스템, Postgres 자체 등 많은 것을 분석하여 프로덕션에서 직접 실행할 수 있고 아무 것도 실패하지 않는 간단한 쿼리를 만듭니다.

우리는 이것을 Postgres-checkup이라고 불렀습니다. 의학적으로는 정기적인 건강검진입니다. 자동차를 주제로 한다면 유지보수와 비슷합니다. 브랜드에 따라 자동차 정비는 XNUMX개월 또는 XNUMX년에 한 번씩 실시됩니다. 기지 유지보수 업무를 담당하시나요? 즉, 정기적으로 심층 조사를 하시나요? 반드시 이루어져야 합니다. 백업을 한 다음 점검을 수행하면 그다지 중요하지 않습니다.

그리고 우리에게는 그러한 도구가 있습니다. 불과 XNUMX개월 정도 전부터 본격적으로 등장하기 시작했습니다. 그는 아직 어리지만 거기에는 많은 것이 있습니다.

가장 "영향력 있는" 쿼리 그룹 수집 - Postgres-checkup에서 K003 보고

그리고 보고서 K가 있습니다. 지금까지 세 개의 보고서가 있습니다. 그리고 그런 보고서 K003이 있습니다. total_time별로 정렬된 pg_stat_statements의 상단이 있습니다.

요청 그룹을 total_time별로 정렬하면 맨 위에 시스템을 가장 많이 로드하는 그룹, 즉 더 많은 리소스를 소비하는 그룹이 표시됩니다. 쿼리 그룹 이름을 지정하는 이유는 무엇입니까? 매개 변수를 버렸기 때문입니다. 이는 더 이상 요청이 아니라 요청 그룹입니다. 즉, 추상화됩니다.

그리고 위에서 아래로 최적화하면 리소스가 줄어들고 업그레이드가 필요한 순간이 지연됩니다. 이것은 돈을 절약하는 아주 좋은 방법입니다.

어쩌면 이것은 사용자를 돌보는 좋은 방법이 아닐 수도 있습니다. 드물지만 사람이 15초를 기다리는 매우 짜증나는 경우를 볼 수 있기 때문입니다. 전체적으로는 너무 드물어서 볼 수 없지만 우리는 자원을 다루고 있습니다.

이 테이블에서는 무슨 일이 일어났나요? 우리는 두 장의 스냅샷을 찍었습니다. Postgres_checkup은 총 시간, 호출, 행, shared_blks_read 등 각 측정항목에 대한 델타를 제공합니다. 그게 전부입니다. 델타가 계산되었습니다. pg_stat_statements의 가장 큰 문제는 언제 재설정되었는지 기억하지 못한다는 것입니다. pg_stat_database가 기억하면 pg_stat_statements는 기억하지 않습니다. 1이라는 숫자가 있다는 것을 알 수 있지만 우리는 어디서 세었는지 모릅니다.

그리고 여기에는 두 개의 스냅샷이 있습니다. 이 경우 델타는 56초라는 것을 알고 있습니다. 매우 짧은 간격입니다. total_time으로 정렬됩니다. 그런 다음 구별할 수 있습니다. 즉, 모든 측정항목을 기간별로 나눕니다. 각 지표를 기간으로 나누면 초당 호출 수가 표시됩니다.

다음으로 초당 total_time은 제가 가장 좋아하는 측정항목입니다. 이는 초당 초 단위로 측정됩니다. 즉, 시스템이 초당 이 요청 그룹을 실행하는 데 걸린 시간(초)입니다. 거기에 초당 XNUMX초 이상이 보인다면 코어를 XNUMX개 이상 주어야 한다는 의미입니다. 이것은 매우 좋은 지표입니다. 예를 들어 이 친구에게는 최소한 XNUMX개의 코어가 필요하다는 것을 이해할 수 있습니다.

이것이 우리의 노하우입니다. 어디서도 이런 것을 본 적이 없습니다. 참고하세요 - 이것은 매우 간단한 것입니다 - 초당 100초입니다. 때로는 CPU가 XNUMX%일 때 초당 XNUMX분, 즉 이 요청에만 XNUMX분을 소비한 것입니다.

다음으로 초당 행을 확인합니다. 우리는 그것이 반환한 초당 행 수를 알고 있습니다.

그리고 흥미로운 것도 있습니다. shared_buffers 자체에서 초당 읽는 shared_buffers 수입니다. 조회수는 이미 있었고 운영 체제 캐시나 디스크에서 행을 가져왔습니다. 첫 번째 옵션은 빠르며 두 번째 옵션은 상황에 따라 빠르거나 빠르지 않을 수 있습니다.

그리고 두 번째 차별화 방법은 이 그룹의 요청 수를 나누는 것입니다. 두 번째 열에는 항상 쿼리당 하나의 쿼리가 구분됩니다. 그리고 흥미로운 점은 이 요청에 몇 밀리초가 걸렸는지입니다. 우리는 이 쿼리가 평균적으로 어떻게 작동하는지 알고 있습니다. 각 요청에는 101밀리초가 필요했습니다. 이것이 우리가 이해해야 할 전통적인 측정 기준입니다.

각 쿼리는 평균적으로 몇 개의 행을 반환했습니까? 우리는 이 그룹이 8개를 반환하는 것을 봅니다. 평균적으로 캐시에서 가져와 읽은 양입니다. 모든 것이 훌륭하게 캐시된 것을 볼 수 있습니다. 첫 번째 그룹의 견고한 히트작.

그리고 각 줄의 네 번째 하위 문자열은 전체의 몇 퍼센트인지를 나타냅니다. 전화가 왔습니다. 1이라고 가정하면 이 그룹이 어떤 기여를 하는지 이해할 수 있습니다. 이 경우 첫 번째 그룹의 기여도가 000% 미만임을 알 수 있습니다. 즉, 너무 느려서 전체 그림에서 볼 수 없습니다. 두 번째 그룹은 통화 요금이 000%입니다. 즉, 전체 통화의 0,01%가 두 번째 그룹입니다.

Total_time도 흥미롭습니다. 우리는 첫 번째 요청 그룹에 총 작업 시간의 14%를 소비했습니다. 두 번째 - 11% 등

자세히 설명하지는 않겠지만 미묘한 부분이 있습니다. 비교할 때 스냅샷이 부동할 수 있기 때문에 상단에 오류가 표시됩니다. 즉, 일부 요청이 누락되어 두 번째 요청에 더 이상 존재할 수 없고 일부 새로운 요청이 나타날 수 있기 때문입니다. 그리고 거기에서 오류를 계산합니다. 0이 보이면 좋은 것입니다. 오류가 없습니다. 오류율이 최대 20%이면 괜찮습니다.

그런 다음 주제로 돌아갑니다. 우리는 워크로드를 제작해야 합니다. 우리는 그것을 위에서 아래로 가져와 80% 또는 90%에 도달할 때까지 진행합니다. 일반적으로 이것은 10-20 그룹입니다. 그리고 우리는 pgbench용 파일을 만듭니다. 우리는 거기에서 무작위를 사용합니다. 불행하게도 이것이 효과가 없을 때도 있습니다. 그리고 Postgres 12에서는 이 접근 방식을 사용할 수 있는 기회가 더 많아질 것입니다.

그런 다음 이런 방식으로 total_time의 80-90%를 얻습니다. “@” 다음에는 무엇을 넣어야 하나요? 우리는 전화를 보고 얼마나 많은 관심이 있는지 살펴보고 여기에 많은 관심이 있다는 것을 이해합니다. 이 비율을 통해 각 파일의 균형을 맞추는 방법을 이해할 수 있습니다. 그 후 우리는 pgbench를 사용하고 작업에 들어갑니다.

K001과 K002도 있습니다.

K001은 75개의 하위 문자열이 있는 하나의 큰 문자열입니다. 이것이 우리 전체 부하의 특징입니다. 두 번째 열과 두 번째 하위 행을 참조하세요. 초당 약 10초, 즉 코어가 XNUMX개 있으면 좋을 것입니다. 용량의 약 XNUMX%가 사용됩니다. 그리고 그것은 이렇게 작동할 것입니다. 코어가 XNUMX개라면 일반적으로 차분할 것입니다. 이런 식으로 우리는 자원을 평가할 수 있습니다.

K002는 내가 쿼리 클래스(예: SELECT, INSERT, UPDATE, DELETE)라고 부르는 것입니다. 그리고 잠금이기 때문에 별도로 SELECT FOR UPDATE를 수행합니다.

그리고 여기에서 SELECT는 일반 독자(모든 호출의 82%이지만 동시에 total_time에서는 74%)라는 결론을 내릴 수 있습니다. 즉, 많이 호출되지만 리소스를 덜 소비합니다.

그리고 우리는 "올바른 shared_buffers를 어떻게 선택할 수 있습니까?"라는 질문으로 돌아갑니다. 나는 대부분의 벤치마크가 아이디어를 기반으로 한다는 것을 관찰했습니다. 처리량이 얼마나 될지, 즉 처리량이 얼마나 될지 살펴보겠습니다. 일반적으로 TPS 또는 QPS로 측정됩니다.

그리고 우리는 튜닝 매개변수를 사용하여 자동차에서 초당 가능한 많은 트랜잭션을 짜내려고 노력합니다. 선택의 경우 정확히 초당 311입니다.

그러나 누구도 전속력으로 출근하고 집으로 돌아가는 사람은 없습니다. 이것은 어리석은 일입니다. 데이터베이스와 동일합니다. 우리는 최고 속도로 운전할 필요가 없고, 누구도 그렇게 하지 않습니다. CPU가 100%인 프로덕션에는 아무도 살지 않습니다. 누군가가 살 수도 있지만 이것은 좋지 않습니다.

아이디어는 우리가 일반적으로 용량의 20%, 바람직하게는 50% 이하로 운전한다는 것입니다. 그리고 무엇보다도 사용자를 위한 응답 시간을 최적화하려고 노력합니다. 즉, 조건부로 20% 속도에서 최소 지연 시간이 있도록 손잡이를 돌려야 합니다. 이것은 우리가 실험에서도 사용하려고 하는 아이디어입니다.

마지막으로 권장사항은 다음과 같습니다.

• 데이터베이스 연구실을 꼭 해보세요.
• 가능하다면 잠시 동안 펼쳐질 수 있도록 요청에 따라 수행하십시오. 플레이하고 버리십시오. 구름이 있다면 이는 말할 필요도 없습니다. 즉, 서 있는 것이 많습니다.
• 호기심을 가지세요. 그리고 뭔가 잘못된 경우 실험을 통해 그것이 어떻게 작동하는지 확인하십시오. 낸시는 기지가 어떻게 작동하는지 확인하도록 스스로 훈련하는 데 사용될 수 있습니다.
• 그리고 최소 응답 시간을 목표로 하세요.
• 그리고 Postgres 소스를 두려워하지 마십시오. 소스 작업을 할 때는 영어를 알아야 합니다. 거기에는 많은 의견이 있으며 모든 것이 거기에 설명되어 있습니다.
• 그리고 정기적으로, 적어도 XNUMX개월에 한 번씩, 수동으로 또는 Postgres 점검을 통해 데이터베이스 상태를 점검하십시오.

질문

정말 감사합니다! 매우 흥미로운 것입니다.

두 조각.

네, 두 조각이에요. 나만 잘 이해가 안 됐어요. Nancy와 제가 작업할 때 하나의 매개변수만 조정할 수 있습니까, 아니면 전체 그룹을 조정할 수 있습니까?

델타 구성 매개변수가 있습니다. 한 번에 원하는만큼 거기로 돌릴 수 있습니다. 하지만 많은 것을 바꾸면 잘못된 결론을 내릴 수 있다는 점을 이해해야 합니다.

예. 내가 왜 물어봤나요? 매개변수가 하나만 있으면 실험을 진행하기 어렵기 때문입니다. 그것을 조이고 어떻게 작동하는지보십시오. 나는 그를 쫓아냈다. 그런 다음 다음 작업을 시작합니다.

동시에 조일 수도 있지만 물론 상황에 따라 다릅니다. 하지만 하나의 아이디어를 테스트해 보는 것이 더 좋습니다. 어제 우리는 좋은 생각을 했습니다. 우리는 매우 가까운 상황에 처해 있었습니다. 두 가지 구성이 있었습니다. 그리고 우리는 왜 큰 차이가 있는지 이해할 수 없었습니다. 그리고 차이점이 무엇인지 일관되게 이해하고 찾으려면 이분법을 사용해야한다는 아이디어가 생겼습니다. 즉시 매개변수의 절반을 동일하게 만든 다음 XNUMX/XNUMX 등으로 만들 수 있습니다. 모든 것이 유연합니다.

그리고 질문이 하나 더 있습니다. 이 프로젝트는 젊고 발전 중입니다. 문서는 이미 준비되어 있습니다. 자세한 설명이 있습니까?

특별히 매개변수 설명에 대한 링크를 만들었습니다. 저기있어. 하지만 아직은 없는 것이 많습니다. 나는 같은 생각을 가진 사람들을 찾고 있습니다. 그리고 공연할 때 찾아요. 이것은 매우 멋지다. 누군가 이미 나와 함께 일하고 있고 누군가가 그곳에서 무언가를 도왔고 뭔가를했습니다. 그리고 이 주제에 관심이 있다면 무엇이 빠졌는지 피드백을 주세요.

실험실을 구축하고 나면 아마도 피드백이 있을 것입니다. 어디 보자. 감사합니다!

안녕하세요! 신고해 주셔서 감사합니다! 아마존 지원이 있다는 걸 봤습니다. GSP를 지원할 계획이 있나요?

좋은 질문. 우리는 그것을 시작했습니다. 그리고 지금은 돈을 절약하고 싶어서 동결했습니다. 즉, localhost에서 실행을 사용하는 지원이 있습니다. 인스턴스를 직접 생성하고 로컬에서 작업할 수 있습니다. 그건 그렇고, 그것이 우리가 하는 일입니다. 저는 Getlab, GSP에서 이 작업을 수행합니다. 하지만 Google에는 저렴한 자리가 없기 때문에 아직 그러한 조정을 수행하는 것이 의미가 없습니다. 있다 ??? 경우가 있지만 제한이 있습니다. 첫째, 그들은 항상 70% 할인만 하고 거기 가격으로는 놀 수 없습니다. 현장에서는 쫓겨날 가능성을 줄이기 위해 가격을 5~10% 인상합니다. 즉, 자리를 절약하지만 언제든지 그 자리를 빼앗길 수 있습니다. 남들보다 조금 더 높게 입찰하면 나중에 죽게 됩니다. Google은 완전히 다른 세부 사항을 가지고 있습니다. 그리고 또 다른 매우 나쁜 제한이 있습니다. 그들은 24시간 동안만 살아갑니다. 그리고 때로는 5일 동안 실험을 진행하고 싶을 때도 있습니다. 그러나 이 작업은 부분적으로 수행할 수 있으며 때로는 부분이 몇 달 동안 지속됩니다.

안녕하세요! 신고해 주셔서 감사합니다! 검진을 언급하셨는데요. stat_statements 오류는 어떻게 계산하나요?

아주 좋은 질문입니다. 아주 자세하게 보여드리고 말씀드릴 수 있습니다. 간단히 말해서, 요청 그룹 세트가 어떻게 떠다니는지 살펴봅니다. 즉, 얼마나 많은 수가 사라졌고 얼마나 많은 새로운 그룹이 나타났는지 살펴봅니다. 그런 다음 total_time과 호출이라는 두 가지 측정항목을 살펴보므로 두 가지 오류가 있습니다. 그리고 부동그룹의 기여도를 살펴보겠습니다. 두 개의 하위 그룹이 있습니다: 떠난 사람과 도착한 사람. 전체 그림에 대한 그들의 기여가 무엇인지 살펴 보겠습니다.

스냅샷 사이에 XNUMX~XNUMX번은 그 곳으로 돌아갈까 두렵지 않으신가요?

즉, 다시 등록했습니까?

예를 들어, 이 요청은 이미 한 번 선점된 후 다시 선점되었으며, 다시 와서 다시 선점되었습니다. 그리고 여기서 뭔가를 계산했는데 그 모든 것이 어디에 있습니까?

좋은 질문입니다. 좀 더 살펴보겠습니다.

나도 비슷한 일을 했다. 물론 더 간단했습니다. 혼자 했어요. 하지만 stat_statements를 재설정하고, 스냅샷 시점에 특정 부분 미만이 있다는 것을 알아내야 했고, 이는 여전히 stat_statements가 얼마나 축적될 수 있는지에 도달하지 못했습니다. 그리고 내 이해는 아마도 아무것도 대체되지 않았다는 것입니다.

예, 예.

그러나 나는 그것을 안정적으로 수행하는 다른 방법을 이해하지 못합니다.

안타깝게도 거기에서 쿼리 텍스트를 사용하는지, 아니면 pg_stat_statements와 함께 queryid를 사용하고 그것에 집중하는지 정확히 기억이 나지 않습니다. queryid에 초점을 맞추면 이론적으로는 비슷한 항목을 비교하게 됩니다.

아니요, 그는 스냅샷 사이에 여러 번 강제로 나갔다가 다시 올 수 있습니다.

같은 아이디로?

네.

우리는 이것을 연구할 것입니다. 좋은 질문. 우리는 그것을 연구해야 합니다. 하지만 현재로서는 우리가 보는 것은 0으로 쓰여 있습니다...

물론 이것은 드문 경우지만 stat_statemetns가 그곳으로 옮겨질 수 있다는 사실을 알고 충격을 받았습니다.

Pg_stat_statements에는 많은 것들이 있을 수 있습니다. track_utility = on이면 세트도 추적된다는 사실을 발견했습니다.

네, 물론입니다.

그리고 임의의 Java hibernate가 있는 경우 해시 테이블이 거기에 위치하기 시작합니다. 그리고 매우 로드된 응용 프로그램을 끄자마자 50-100개의 그룹이 생성됩니다. 그리고 모든 것이 다소 안정적입니다. 이에 대처하는 한 가지 방법은 pg_stat_statements.max를 늘리는 것입니다.

예, 하지만 얼마인지 알아야 합니다. 그리고 어떻게든 우리는 그를 지켜봐야 합니다. 그게 내가하는 일이야. 즉, pg_stat_statements.max가 있습니다. 그리고 스냅샷 당시에는 70%에 도달하지 못한 것으로 나타났습니다. 좋아요, 그럼 우리는 아무것도 잃지 않았습니다. 재설정합시다. 그리고 우리는 다시 저장합니다. 다음 스냅샷이 70개 미만이라면 다시는 아무 것도 잃지 않았을 가능성이 높습니다.

예. 현재 기본값은 5이며 많은 사람들에게 이 정도면 충분합니다.

보통 그렇습니다.

비디오 :

추신 저를 대신하여 Postgres에 기밀 데이터가 포함되어 있고 해당 데이터가 테스트 환경에 포함될 수 없는 경우 다음을 사용할 수 있다는 점을 덧붙이겠습니다. PostgreSQL 익명처리기. 계획은 대략 다음과 같습니다.

출처 : habr.com