기록하고 공유하는 기술 블로그

MySQL 을 비롯한 RDB 들은 쿼리를 던지면 옵티마이저를 통해 가장 효율적인 쿼리 플랜을 생성하여 해당 방식으로 쿼리를 수행한다.

예를 들면 다음과 같은 테이블이 있다고 할 때,

create table delivery
(
    id                       int unsigned auto_increment primary key,
    seller_id                int unsigned,
    order_id                 int unsigned,
    status                   varchar(50),
    send_date_time           datetime(6),
)

datetime field 인 send_date_time를 기준으로 조회해보자. 인덱스는 걸려있다.

SELECT * FROM delivery WHERE send_date_time < $send_date_time;

보이는 것 처럼 이번에도 인덱스를 사용한다.

여기까지는 너무 당연하다.

이번에는 send_date_time를 기준으로 조회하고, id로 정렬해보자.

아마도 배송일자 기준으로 조회하고, 먼저 생성된 레코드 부터 처리하는 유스케이스에 사용될 것이다.

SELECT * FROM delivery WHERE send_date_time < $send_date_time ORDER BY id;

그런데 이렇게 조회하면 아래와 같이 인덱스를 타지 않고 PK 정렬을 먼저 하는 쿼리 플랜을 보여준다.

조회 조건으로 들어간 날짜가 현재 시간에 가까울 수록 아래와 같은 쿼리 플랜을 생성할 가능성이 높은듯 하다.

이러한 경우 인덱스를 타지 않기 때문에 성능이 크게 달라질 수 있어 유의해야 한다.

해결 방법은, 인덱스를 사용하도록 아래와 같이 직접 명시하는 방법이 있다.

SELECT *
FROM delivery
use index (delivery_send_date_time_index)
WHERE send_date_time < $send_date_time
ORDER BY id;

또한 애초에 pk를 정렬 조건으로 쓰는게 맞는지 한 번 더 고민해 봐야할 것 같다.

이번 케이스는 reg_date_time 등을 추가해서 레코드 생성 시점을 별도로 관리하는 것이 더 적합했던 것 같다.

id를 주로 sequential 하게 사용하지만, 때에 따라 uuid등으로 사용할 수도 있으니 말이다.

클라이언트에서 api를 쏘는데 갑자기 sorry, too many client already 라는 문구와 함께 api가 깨지는 현상이 발생했다.

이는 database의 connection 개수가 초과되었다는 뜻으로,
원인 파악 이후 max_connection을 늘려주든, 오용되고 있는 connection을 끊어주든 해야한다.

postgresql.conf 를 열어보면 max_connections 라는 필드가 있고, 기본 설정은 25인듯 하다.

SHOW max_connections;

위의 쿼리를 통해서도 확인이 가능하다.

max_connections는 확인했고, 이제는 클라이언트들이 연결되었는지 확인해보자.

SELECT * FROM pg_stat_activity where datname = '데이터베이스 이름';

위의 쿼리를 통해 어떤 쿼리들이 현재 작동중이며 어떤 클라이언트가 connection을 물고 있는지 확인이 가능하다.

간혹 너무 오래 걸리는 쿼리가 계속 작동 중이라 connection 풀이 고갈되는 경우가 있는데, 이럴 때는

SELECT pg_cancel_backend(pid int);

위의 쿼리를 통해 중지 시킬수 있다.

나의 경우에는 다행히 오용되고 있는 connection은 없었고, 데이터 파이프라인과 api 등 요청이 많아서 그런 것으로 확인되어, 단순히 postgresql.conf의 max_connections를 적절히 늘려주는 것으로 해결했다.

docker를 사용하는 환경에서는 여기에 있는 방법으로 해결할 수 있다.

References

https://stackoverflow.com/questions/2757549/org-postgresql-util-psqlexception-fatal-sorry-too-many-clients-already

https://stackoverflow.com/questions/47252026/how-to-increase-max-connection-in-the-official-postgresql-docker-image