UPDATE 쿼리 실행 시 WHERE 절을 PK로 지정해야 하는 이유

대부분의 데이터베이스에서 특정 row의 데이터를 변경하기 위해 UPDATE ... SET .. WHERE ... 쿼리를 이용하여 데이터를 변경합니다.

WHERE 절에는 row가 특정될 만한 조건을 넣어야 하는데, PK(Primary Key)를 조건으로 지정하는 것이 일반적입니다.

오래된 코드에서는 개발자의 성향이나 편의 등의 사유로 WHERE 절에 PK가 아닌 컬럼을 조건으로 지정하는 경우를 종종 볼 수 있습니다. 

예를 들어 아래와 같은 데이터가 있다고 가정해 보겠습니다.

id는 PK로 지정되어 있고, 나머지 컬럼은 모두 index가 생성되어 있다고 전제하겠습니다.

id 값이 146인 row의 name을 변경하려면 아래와 같이 SQL을 작성합니다

UPDATE tbl_name SET name = 'New Product' WHERE id = 146

아래와 같이 쿼리를 작성해도 동일한 결과를 얻을 수 있습니다.

UPDATE tbl_name SET name = 'New Product' WHERE category_id = 3 AND origin = 'quia'

첫 번째 쿼리와 두 번째 쿼리 모두 변경하고자 하는 row 만 제대로 특정된다면 크게 문제는 없어 보이는데요,

그럼에도 불구하고 두 번째 쿼리보다 첫 번째 쿼리를 사용하는 것이 좋은 이유는 무엇일까요?

바로 PK와 PK가 아닌 컬럼의 인덱스가 서로 다른 방식이기 때문입니다.

MySQL 기준, 테이블에 부여할 수 있는 인덱스의 종류로는 Clustered Index와 Non-Clustered Index 가 있습니다.

Clustered Index의 특징은 아래와 같습니다.

• 테이블당 1개만 지정 가능
• Primary Key 또는 Unique Index만 지정 가능하며, Primary Key가 있는 경우 PK가, 없는 경우 Unique Index가 Clustered Index 로 지정됨
• 물리적인 정렬 방식을 이용하여 SELECT 속도가 빠름 (UPDATE/INSERT/DELETE 속도는 느림)
• 생성시 데이터 재정렬이 필요하여 큰 부하 발생

반면에 Non-Clustered Index의 특징은 아래와 같습니다.

• 테이블당 여러개 지정 가능
• 페이지 단위로 인덱스가 저장되며, 랜덤한 순서로 저장 (뒤죽박죽)
• SELECT 속도보다 UPDATE/INSERT/DELETE 속도가 빠름
• 생성시 부하가 적어 비교적 부담 없이 생성 가능

트랜잭션 구간 내에서 UPDATE 쿼리 실행시 데이터베이스는 WHERE 절을 기준으로 레코드에 lock을 걸어 다른 트랜잭션에 의해 데이터가 오염되지 않도록 방지합니다.

Clustered Index를 기준으로 WHERE 절을 지정하면 단일 row만 lock이 걸리지만, Non-Clustered Index를 기준으로 WHERE 절을 지정하면 모든 WHERE 조건을 만족하는 1개 row만 lock이 걸리는게 아니라 WHERE 절의 첫 번째 조건을 만족하는 row 중 나머지 조건이 일치하는 row가 속한 인덱스 페이지 단위로 lock이 걸리게 됩니다.

아래는 Non-Clustered Index의 구조를 설명한 그림입니다.

MobileNo = 117인 row를 변경하려고 시도한다면, MobileNo = 117인 row 1개만 lock 이 걸리는 것이 아니라, 같은 인덱스 페이지인 115 ~ 118 범위의 row가 모두 lock이 걸리게 됩니다.

즉, 아래 쿼리로 UPDATE를 시도한다면, id = 146 인 row 1개만 lock이 걸리는 것이 아닌, category_id = 3인 row 중 랜덤한 row들이 lock이 걸리게 됩니다. (인덱스내 row는 정렬이 랜덤이라 페이지별 범위가 특정되지 않습니다)

UPDATE tbl_name SET name = 'New Product' WHERE category_id = 3 AND origin = 'quia'

실제로 변경하려는 row외 다른 row가 lock이 걸리는지 실험을 해보겠습니다.

DataGrip에서 Tx: Manual 로 설정 후 아래 쿼리를 실행하여 Lock을 겁니다.

SELECT * FROM products WHERE category_id = 3 AND origin = 'quia' FOR UPDATE

이 상태에서 id = 106 인 row의 데이터를 변경하려고 시도하면 Lock wait timeout exceeded 오류와 함께 변경에 실패하게 됩니다.

실제로 서비스중인 애플리케이션에 위와 같은 쿼리가 포함되었다면, Deadlock이 발생하여 전체 트랜잭션이 롤백되었을 것입니다. Non-Clustered Index 특성상 랜덤하게 페이지가 부여되기 때문에 항상 Deadlock이 발생하는 것이 아닌 '간헐적'으로 Deadlock이 발생하게 됩니다.

만약 운영중인 서비스에서 간헐적인 Deadlock이 발생하는데 원인파악이 어렵다면 위와 같은 케이스에 해당되지 않는지 면밀히 살펴보는 것이 좋습니다.

※ 본 포스팅을 할 수 있도록 조언과 도움을 아끼지 않았던  동료 개발자분께 감사드립니다.

Develop MySQL 데이터베이스

서비스 응답속도 저하시 php-fpm 로그로 원인분석하기

php-fpm 기반의 웹 서비스에서 알 수 없는 문제로 응답속도가 저하되는 현상이 발생 할 경우가 종종 있다.

원인은 여러가지일 수 있다. 몇 가지를 꼽자면,

1. DBMS의 SQL 응답속도 저하 (Slow Query)
2. 네트워크 지연
3. 서버 리소스 점유율 (CPU, RAM 등)
4. Disk 입출력 문제

등이 있다.

하지만, 서버 리소스나 네트워크에 별 다른 문제가 없고, Slow Query 로그에도 별다른 이상징후가 없다면,
원인분석을 해야 하는데, 이 경우 Web Application에서 어느 로직에서 문제가 발생하는지 원인을 찾아야 한다.

개발환경이라면 Xdebug를 활용 수 있겠지만, 실 서비스 중인 서버에는 적용할 수 없는 방법이다.

이 때는 php-fpm의 slowlog를 찍어서 확인하는 방법이 있다.

php-fpm.conf 파일을 열어보면 아래와 같은 설정을 확인할 수 있다.

......
request_slowlog_timeout = 30s
slowlog = /var/log/php-fpm/slow.log
......

여기서 request_slowlog_timeout을 3~5s 정도로 낮추고, php-fpm 서비스를 재시작한다.

그 후 tail 명령으로 /var/log/php-fpm/slow.log 를 확인 후 응답속도가 느린 페이지에 접속을 계속 시도해보면 다음과 같은 로그가 찍힌다.

[10-Nov-2018 11:30:22]  [pool www] pid 8260
script_filename = ******************
[0x00007f1a47de79a8] fopen() ****.php:422
[0x00007f1a47de6500] +++ dump failed

이 로그에는 응답속도 저하의 원인이 되는 파일과 해당 function까지 추적해주기 때문에 원인을 금방 파악할 수 있다.

DevOps 장애조치 PHP php-fpm Linux