[Mysql - InnoDB] 상당히 복합적인 구조의 테이블에서 엄청난 간헐적 테이블 락 현상.

kars의 이미지

몇날 몇일 골머리를 앓다가 도저히 문제를 해결할 수 없어 질문을 올립니다.

초당 2000쿼리정도가 실행이 되는 서비스를 운영 중입니다. Insert/update 작업이 상당히 많습니다. 평소엔 매우 문제 없이 대부분의 쿼리가 0.5ms ~ 1ms 안에 실행됩니다. Insert 와 Update 는 대부분 bulk insert 로 되며, 수십-수백개의 row 가 Insert or Update 됩니다. 이런 쿼리들도 대부분 문제 없이 수십ms 안에 처리가 완료됩니다.

그러나, 특정 시간마다 돌아가는 통계 작업용 테이블 대량 스캔 쿼리를 날리고 난 뒤(실행시간이 수십초정도 되는 쿼리)에는 전체적으로 시스템이 버티지 못하는 현상이 나타납니다. 이 작업 이후에는 대부분의 INSERT 쿼리가 10~30초 이상 밀리게 되고, 60초넘게 밀리는 경우도 다반사입니다. 하지만 각각의 쿼리속도가 수십초가 걸리는 것이 아닌, 무언가의 Lock 에 의해서 수십-수백개의 쿼리가 밀리다가 언젠가 시점에 갑자기 이 수십개의 쿼리가 한방에 물밀려가듯 처리가 되버립니다. 최대 쿼리는 215개까지 밀리고, 215개까지 밀리는 상황이 되면 웹서버는 자동으로 접근이 차단됩니다. (502에러) 이 현상이 대략 5~10초마다 계속적으로 무한정 반복됩니다.

이 현상은 위에 언급한 풀스캔행위를 하는 쿼리를 두세번정도 날리게 되면 그 이후부터 계속적으로 볼 수 있게 됩니다. (* 단 이 쿼리는 인덱스가 대부분 걸려 있으므로 그 엄청난 양의 테이블을 풀스캔 하는 쿼리는 아닙니다. explain 을 하면 거의 끝자락에서 filesort을 사용합니다.) 대부분의 이 쿼리는 20~30초 내외에 종료됩니다만, 위 현상이 일어나는 상태가 시작되면 이 쿼리는 한번 실행되면 쿼리 완료시까지 1000초 이상 걸리게 됩니다.

즉, 언젠가 실행되는 일련의 작업 때문에, 그리고 일련의 심한 부하 때문에, 그 이후로부터 실행되는 모든 쿼리들이 전체적으로 미치는 현상이 발생하고 있습니다. 한가지 의심되는건 테이블 파티션을 최근에 했는데, 테이블 파티션을 한 이후로 이 현상이 계속적으로 발생하고 있는 것입니다. 한 테이블에 80기가가 넘는 데이터가 쌓여있었거든요. 그래서 파티션 했더니 이런 불상사가 일어나네요.

모니터링은 mytop 으로 0.5초 단위로 하고 있습니다. 어떤 쿼리가 얼마나 걸리는지 이제 거의 외울정도입니다. 디테일한 상황은 아래와 같습니다.

문제의 테이블은 1초에 한번은 아래와 같은 벌크 인서트를 하고 있습니다. (항상 다른 값들임)

1. 쿼리 - Multiple bulk Insert with on duplicate key update
INSERT INTO `tableName` (field1, field2, field3 ..... field 25) VALUES
(Val1-1, val1-2 ... val1-25), ... (Val80-1, val80-2 ... val80-25)
on duplicate key update
val2 = values(val2), ... val25 = values(val25)

와 같은 약 80개의 벌크 Insert into 쿼리문이 대략 10초에 5~10번은 호출되고 있습니다. 서버는 (12 * 2)코어짜리 시스템이구요. (+SSD)

2. DB Engine - InnoDB
대부분이 InnoDB를 취하고 있습니다. 문제의 테이블 역시 InnoDB 입니다.

3. Table Partition
문제의 테이블은 테이블 파티셔닝을 취하고 있습니다. 총 파티션은 약 20개이며, 약 4기가짜리 파티션 테이블이 두세개 정도 있으며 나머지는 대부분 빈 테이블들입니다. 테이블 총 용량은 약 20기가.

4. Index - Unique column has multiple index
테이블 파티션으로 인해 Auto-increment 가 가능한 Primary index 가 없습니다. (테이블 파티션을 취하려면 Unique or Primary 인덱스에 테이블 파티션이 되는 기준 필드가 꼭 들어가야 합니다.) 그래서 Primary Index 는 2개의 필드로 되어 있으며, 이 2개의 필드는 1번의 Insert 시에 한번 인서트 되고난 뒤에는 변하지 않는 고유 값입니다.
이 외 자잘한 1개 필드 인덱스가 3개, 2개 필드 인덱스가 2개 할당되어 있습니다.
* 파티션하기 전, 잘 작동 할때는 Auto-increment 필드가 존재했었음

5. innodb_autoinc_lock_mode = 0
이 옵션은 Insert into 로 실제로 삽입된 경우가 아닌, Replace into, Insert into ~ on duplicate key update 등의 쿼리로 인해 삽입이 아닌 업데이트가 될 경우에는 해당 테이블의 Auto-increment 값을 증가시키지 않는 옵션입니다. 이 옵션이 기본값으로 돌아갈 경우 auto-increment 가 무한정 증가하게 됩니다. 이 부분은 테이블 파티션을 하기 훨씬 전에 적용한 부분이며, 문제가 되는 테이블에서는 Auto-increment 를 쓰고 있지 않고 있어서 의심은 되지 않으나 민감한 부분이기는 합니다.

문제 발생시 다른 이상 현상
- 이렇게 엄청난 쿼리 밀림 현상이 발생할 땐, 디스크(SSD) 읽기가 0입니다. 단 1kb 도 읽지 않습니다. 반면에 쓰기 속도는 초당 30~40MB 정도 나옵니다.

문제 발생 시점
- 정확하지는 않지만 테이블 파티션 작업을 한 이후부터 발생했습니다. 그 전엔 꽤나 멀쩡했음.

시도해본 방법
- 쿼리캐시 옵션을 ON DEMAND 로 변경해봤습니다. 첨엔 효과가 있는 듯 해서 손을 놓고 있었으나, 약 이틀 후에 다시 현상이 발생합니다. 하지만 ALWAYS ON 을 했을 때 보다는 좀 더 잘 버티는 듯 합니다. 기분 탓일지도 모르겠습니다. 어쨌건가 현재는 ON DEMAND로 운용중입니다.
- Mysql 5.5.26 버전을 쓰고 있었다가 오늘 MariaDB 5.5.60 으로 업데이트를 했습니다. (디비 용량이 너무 커서 mysql_upgrade 는 돌리지 못하였음) 좀 다른가 싶더니만, 시간이 흐르고 보니 별반 차이가 없습니다.
- Mysql 을 리붓하면 말끔히 해결됩니다. 물론 대형 쿼리가 공격하기 전까지만 유지됩니다. Mysql 을 Stop 하는데 걸리는 시간은 약 2~3분정도 걸립니다.

이 현상이 단순 부하때문이라면 서버 업그레이드가 답이겠으나, 평소에는 잘 처리하다가 특정 시점부터 문제가 발생하는 것과, 테이블 파티션 작업을 한 뒤에서야 문제가 발생하는 것을 보면 서버 업그레이드가 답은 아닐 것 같습니다.

감사합니다.

snowall의 이미지

SSD라서 그런건 아닐까요?

피할 수 있을때 즐겨라! http://melotopia.net/b

다콘의 이미지

문제가 생겼을 때 mysql에서 show engine innodb status\G; 결과와
vmstat이나 dstat으로 시스템 모니터링 한 결과가 있으면 도움이 될 듯 합니다.
profiling도 한번 해보시면 도움이 될 수 있습니다.
http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

그리고 테이블이 빈번하게 변한다면 쿼리 캐시는 끄는게 낫습니다.
mysql 쿼리 캐시가 상당히 빈약하고 제약 조건도 많아서 현 상태에서는 끄는게 더 좋아 보입니다.

댓글 달기

Filtered HTML

  • 텍스트에 BBCode 태그를 사용할 수 있습니다. URL은 자동으로 링크 됩니다.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>
  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.

BBCode

  • 텍스트에 BBCode 태그를 사용할 수 있습니다. URL은 자동으로 링크 됩니다.
  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param>
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.

Textile

  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • You can use Textile markup to format text.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>

Markdown

  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • Quick Tips:
    • Two or more spaces at a line's end = Line break
    • Double returns = Paragraph
    • *Single asterisks* or _single underscores_ = Emphasis
    • **Double** or __double__ = Strong
    • This is [a link](http://the.link.example.com "The optional title text")
    For complete details on the Markdown syntax, see the Markdown documentation and Markdown Extra documentation for tables, footnotes, and more.
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>

Plain text

  • HTML 태그를 사용할 수 없습니다.
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.
  • 줄과 단락은 자동으로 분리됩니다.
댓글 첨부 파일
이 댓글에 이미지나 파일을 업로드 합니다.
파일 크기는 8 MB보다 작아야 합니다.
허용할 파일 형식: txt pdf doc xls gif jpg jpeg mp3 png rar zip.
CAPTCHA
이것은 자동으로 스팸을 올리는 것을 막기 위해서 제공됩니다.