database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Q&A 9906 게시물 읽기
No. 9906
같은 쿼리가 특정 시점에 갑자기 느려지는 현상에 대한 도움이 필요합니다.
작성자
배우미(iloveuu)
작성일
2017-11-01 00:10ⓒ
2017-11-01 00:25ⓜ
조회수
2,816

 안녕하세요. 현재 postgresql 9.6을 서비스에 사용하기 위해 준비 중입니다.

그런데 한가지 문제점이 있는데 검색 쿼리를 실행할 때 잘 돌다가 한번씩 응답이 굉장히

느려지는 경우가 발생합니다. 플랜을 확인해보면 인덱스를 잘 타고 있는 상황이고

실제로 쿼리 속도가 늦지도 않습니다. 그런데 페이징을 누르거나 검색을 누르거나 하면서

쿼리를 실행하다가 특정 시점이 됐을때 갑자기 반응 속도가 확 떨어집니다. 이 때

vmstat를 확인해보면 interrupt와 context switching이 굉장히 늘어나 있고

8 core중 1개의 cpu가 100%를 표시합니다. iowait나 스와핑은 전혀 일어나지

않고 실제 디스크에 쓰기 작업도 전혀 일어나지 않고 있습니다. 이상하게 운영체제 상에

전혀 수상한 발자국을 남기지 않으면서 쿼리 성능만 확 떨어지는 현상입니다. 그리고

그 쿼리 이후부터는 성능이 갈수록 계속 떨어져 세션을 끊어야만 하는 상황이 됩니다.

shared_buffers: 8G
work_mem : 16MB
temp_buffers : 8MB

와 같이 설정되어 있고 나머지는 디폴트 옵션입니다. 쿼리가 처음부터 꾸준히 늦으면

쿼리 탓이라고 생각할텐데 잘 돌다가 갑자기 무슨 조건인지도 모를 때에 성능이 확

줄어드는 현상에 대해서 짚이시는 곳이 있으신 분은 제발 도와주시길 부탁드립니다.

참고로 모든 쿼리가 늦어지는게 아니고 해당 쿼리만 그런 문제가 발생하고 있습니다.

대상 데이터는 현재 11만건 정도이고 DML은 전혀 이루어지지 않고 읽기만 하는

상황입니다.

 

쓰다보니 한가지 더 짚이는게 있네요. 이게 psql이나 pgadmin에서는 전혀 성능저하가

없습니다. 문제가 되는 어플리케이션의 쿼리를 그대로 psql이나 pgadmin에서는 아무리

날려도 성능저하가 없습니다. 현재 spring, jpa(hibernate), hikari CP로 구현되어

있는데 jdbc로 연결된 상태에서만 발생하는 현상입니다. 혹시 커넥션 풀의 문제일까

싶어서 커넥션 풀없이 PreparedStatement로 바인딩 파라미터로 쿼리를 직접 날리는

테스트 코드를 작서앻서 1.5초마다 쿼리를 날리도록 했는데 이 역시 어느 순간에 응답이

오지 않는 상태가 되었습니다. 쿼리가 전송은 됐는데 디비로부터 응답이 없고 pgadmin이나

psql은 바로 응답을 주고 무슨 영문인지 모르겠습니다. 물론 pgadmin과 psql도 리모트로

접속해서 날려보았습니다.

이 글에 대한 댓글이 총 10건 있습니다.

그 알 수 없는 조건이 혹 DB 서버 기준으로 여러 동시에 쿼리를 실행하는 세션 수가 많아지면서 발생한 것이라면, 동시 쿼리 처리 능력의 한계로 보면 될 것 같습니다.

 

그 외 나머지 상황은 그 때 그 상황을 종합적으로 판단하지 않으면 답을 찾아내기 힘든 부분입니다. 간헐적 네트워크 장애일 수도 있고, OS 차원의 디스크 읽기 쓰기의 잠깐 멈춤일 수도 있고, 온갖 경우가 있어 뭐라고 하기 힘든 상황이네요.

 

jdbc가 의심이 되면 jdbc를 바꿔서 확인해 보시고요. 일반적으로 jdbc 커넥션의 속성 가운데, prepareThreshold , preparedStatementCacheQueries 이런 속성들이 0이 아니여서 jdbc 차원에 캐시와 서버 차원의 prepare를 쓰겠끔 되어있어 이 부분의 문제일 수도 있습니다.

 

https://jdbc.postgresql.org/documentation/head/connect.html

김상기(ioseph)님이 2017-11-01 11:20에 작성한 댓글입니다.
이 댓글은 2017-11-01 11:20에 마지막으로 수정되었습니다.

 답변 감사드립니다.

이유를 확실히 알았습니다. 댓글보기 전에 이미 jdbc 버전도 바꿔서 해봤고 여러 시도를 해봤는데

콘솔이나 pgadmin에서는 빠른데 어플리케이션에서 느린 이유는 바인딩 변수 때문이었습니다.

콘솔에서는 상수값으로 넣었기 때문에 빠르게 나왔는데 psql 커맨드에서 직접 prepare을 만들고

바인딩 변수를 주었더니 성능이 확 떨어지고 말았습니다. 스택오버플로우에서 이러한 사례가 있어서

지금 찾아보는 중인데 혹시 이에 대한 경험이 있으시면 나눠주시길 부탁드립니다.

(참고로 개발 중인 상황에서 성능이 떨어진 문제이기 때문에 세션 수의 문제는 아니었습니다.)

배우미(iloveuu)님이 2017-11-01 12:15에 작성한 댓글입니다.
이 댓글은 2017-11-01 12:16에 마지막으로 수정되었습니다.

 조회하는 부분이 pg 사용자 함수로 만드셨나요? 아니면 자바 소스에 쿼리?

지현명(gwise)님이 2017-11-01 12:29에 작성한 댓글입니다.

 JPA를 사용하고 있는데 orm.xml에 네이티브 쿼리로 작성하였습니다. 순수 sql입니다.

아래는 바인딩 변수 사용 시 뜬 실행계획입니다.

product_term을 풀스캔해서 왕창 버리는 비효율은 있긴 합니다만 전체적으로 느린 성능을 보여주지는 않습니다.

이해가 안되는것은 빠르게 나오다가 한순간에 먹통이 되는 현상입니다.

배우미(iloveuu)님이 2017-11-01 12:32에 작성한 댓글입니다.
이 댓글은 2017-11-01 12:38에 마지막으로 수정되었습니다.

pg사용자 함수로 조회부분 바꿔서 테스트 해보시구요.

아니면  pg_stat_statements 추가 하셔서 점검 해 보셔도 될듯

조회 목록에 실행횟수(calls)하고 실행 시간 등등 정보 많으니 쿼리 재사용 하는지 보세요.

jpa라....db에게는 pg함수 아니면 그냥 ad-hoc쿼리일뿐입니다.  

-----------------------------------------------------

CREATE EXTENSION pg_stat_statements;  

 

postgresql.conf 설정화일을 아래와 같이 수정후 PostgreSQL 을 restart 합니다.

맨앞에 #을 지워야 적용됨.

.....

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000

pg_stat_statements.track = all

 

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

지현명(gwise)님이 2017-11-01 12:39에 작성한 댓글입니다.
이 댓글은 2017-11-01 12:51에 마지막으로 수정되었습니다.

https://explain.depesz.com/

 

PostgreSQL실행 계획 공유는 위에 사이트로 해주세요~

지현명(gwise)님이 2017-11-01 12:48에 작성한 댓글입니다.

의심했던 부분이 맞을 것 같네요.

커넥션 데이터 소스 만드는 부분에 connection url 부분에

윗 두 설정값을 0으로 해서 jdbc 차원의 캐시와 서버 prepare를 쓰지 않는다고 해서 지정해 보세요.

jdbc:postgresq://host/db?prepareThreshold=0&preparedStatementCacheQueries=0

 

김상기(ioseph)님이 2017-11-01 12:51에 작성한 댓글입니다.

 김상기님의 생각이 맞았습니다.

설정바꾸고 나니 성능저하가 사라졌습니다. 사람 하나 구하셨습니다. 감사합니다~

관심가져 주신 지현명님도 감사드려요~

배우미(iloveuu)님이 2017-11-01 13:01에 작성한 댓글입니다.

http://pgday.postgresql.kr/

이번주 토요일 PostgreSQL 사용자 그룹에서 PG행사가 있습니다. 

오셔서 질문해 주세요~ 김상기님이 답변 잘해 주십니다.

 

 --------------

pg_stat_statements 익스텐션 추가하셔서 orm jpa가 던지는 ad-hoc쿼리 살펴보세요

어떤짓(?)하는지 orm은 끝까지 감시해야 합니다.

지현명(gwise)님이 2017-11-01 13:02에 작성한 댓글입니다.
이 댓글은 2017-11-01 13:09에 마지막으로 수정되었습니다.

말씀해주신 익스텐션을 설치해서 확인해보겠습니다.

그런데 orm에서 날아간 쿼리는 이미 알고 있습니다. 로깅을 찍어보면 실제 생성되서 날아간

쿼리는 확인이 가능하거든요. 그리고 위에서 말씀드린 것처럼 jpql이나 그래프 탐색을 통해

만든 쿼리가 아니고 제가 직접 만든 네이티브 쿼리였습니다. 그렇기 때문에 jpa와는 전혀

무관한 문제였습니다. 관심가져주셔서 감사드립니다.

배우미(iloveuu)님이 2017-11-01 13:20에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
9910PostgreSQL 버전 관련 질문드립니다. [2]
이창기
2017-11-13
1945
9908테이블 용량 확인 쿼리 [3]
postgres
2017-11-10
2565
9907윈도우 postgre 설치시 오류 질문드립니다 ㅠ [4]
디비
2017-11-09
2732
9906같은 쿼리가 특정 시점에 갑자기 느려지는 현상에 대한 도움이 필요합니다. [10]
배우미
2017-11-01
2816
9905함수 여러개를 하나의 트랜잭션에서 실행 안되나요? [2]
지현명
2017-10-31
1658
9904데이터베이스 기출문제 도와주실분 찾습니다. [4]
김성기
2017-10-24
1746
9903테이블에 데이타 넣기 [6]
이미나
2017-10-17
1900
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2019 DSN, All rights reserved.
작업시간: 0.074초, 이곳 서비스는
	PostgreSQL v11.5로 자료를 관리합니다