MySQL Error 1206, 데이터 엔지니어의 눈으로 바라보기

https://onlinemasters.ohio.edu/blog/a-step-in-the-right-direction-data-analysis-for-decision-making/

★ 부제 : 데이터엔지니어와 데이터분석가의 경계

 

“The total number of locks exceeds the lock table size”

락의 총합이 락테이블 사이즈를 초과했다.

무슨 뜻일까?

냉큼 buffer_size를 늘이라고 해서 늘린다면 바로 사고다.

그럼 어떻게 해야 할까?

한 번 고민해보자.

 

언제 생길까?

create table Temp_Table
select * from You_Table
where id > 1000;

회원정보 테이블에서 1,000 번 이상인 사람들만

모아서 새로운 테이블로 만들고 싶을 때 쓰는 SQL문이다.

 

create table 하려고 스키마를 일일히 타이핑할 필요가 없다.

쪼끔 편하다.

 

Table을 정식으로 만들땐 쓰지 않는다.

실수할 수 있기 때문이다.

 

그냥 잠깐 데이터조작을 해보고 싶을 때 쓴다.

주로 작은 데이터셋을 만들 때 쓴다.

여러번에 걸쳐 데이터를 조작해야 할 때 쓴다.

 

그런데, 초보자는 멋모르고 전체 데이터를 Query한다.

음... 잘 몰라서 그렇다.

 

저런 에러가 왜 나는지,

어떻게 해야 하는지 설명해주기로 하자.

 

도대체 얼마를 Query할까?

select *
from information_schema.tables
where table_name = "You_Table";

MySQL 시스템 관리영역에서 "Table Size" 만 확인할 때 쓰는 명령어다.

select count(*) from You_Tables;

이것보다 훨씬 빠르게 전체 row건수를 확인할 수 있다.

count(*) 명령어는 테이블 전체를 다 뒤지기 때문이다.

당연히 시간이 더 오래걸린다.

 

음. 테이블 크기를 보자.

데이터건수가 1.18억건(TABLE_ROWS)이다.

데이터크기가 21GB(DATA_LENGTH, bytes단위)다.

인덱스크기가 20GB(INDEX_LENGTH, bytes단위)다.

즉, 데이터 파일만 41GB 정도 된다.

 

여기서 select 할 때 where 문을 써서 조건을 주지 않으면

21GB를 query해서 21GB의 파일을 생성한다.

 

왜 에러가 날까?

MySQL은 저 문장이 들어오면 일단 select 결과를 모두 메모리로 가져온다.

그 메모리가 buffer 다.

이걸 조정하는 게 buffer_size 옵션이다.

 

그런데, 가지고 올 데이터량이 많아서 buffer_size를 넘어간다면?

MySQL 은 Temp_Table이 완성되기 전에 You_Table 이 변경되면 안되니까,

You_Table 에다가 lock을 걸어버린다.

 

즉, 그동안 다른 트랜잭션이 데이터를 변경할 수 없다는 소리다.

그래서 이런 에러가 생긴다.

“The total number of locks exceeds the lock table size”

 

timeout은 왜 생길까?

음 그런데 시간이 얼마나 걸릴까?

21GB 영화파일을 C: 에서 D: 로 옮겨본다.

40분 정도 걸린다.

음, 40분 정도면 보통 OS 가 timeout 을 시켜버린다.

 

그런데 그동안 CPU 사용율이 full 이라서

다른 업무가 작동하지 않았다면?

 

지금 돌아가는 서버어플리케이션이

은행용이라면 치명적일 수 있다.

외화송금을 해야 하는데, 마감시간 10분 동안 거래가 안된거다.

피해액이 얼마나 될까?

크다.

 

사고친 개발자가 법적책임을 물을 수도 있다.

어떤가, 함부로 SQL문을 날릴 수 있을까?

 

요령껏 접근해보기

1. buffer size 늘리기

"곧 죽어도 21GB 짜리 임시테이블을 만들어야겠다."

이 경우 쓰는 방법이다.

 

buffer size 를 21GB 수준으로 늘려준다.

 

음. 하지만 큰 직장에선 일어나지 않는 일이다.

DBA가 못하게 한다.

DB는 다양한 서버 어플리케이션들과 통신한다. 

100% 확률로 다른 어플리케이션까지 장애가 파급된다.

호미로 막을 일을 가래로도 못막게 된다.

 

혼자 하는 웹사이트라도 이렇게는 하지말자.

10분 이상 사이트가 마비되어 버린다.

자주 이런 일이 생기면 90% 확률로 유저는 떠나버린다.

 

2. export 후 import 하기

"곧 죽어도 21GB 짜리 임시테이블을 만들어야겠다."

하지만, "40분은 죽어도 못 기다리겠다."

이 경우 쓸 수 있는 방법이 있다.

 

table 자체를 export 했다 새로운 테이블에 import 한다.

 

table export 가 create ... select 보다는 확실히 빠르다.

select 자체는 엄청 빠르기 때문이다.

write 에 걸리는 시간과 연동시킬 필요가 없다.

 

export time과 import time을 분리시켜 

서비스가 다운되지 않게 하는거다.

 

단점으로는 "리눅스 터미널" 작업을 해야 한다.

고수에게야 껌이겠지만 초보자에겐 두려울 수 있다.

 

3. 데이터 처리건수 나누어 잡기

40분을 기다릴 순 없고,

21GB테이블은 만들어야겠는데,

실제 작업시간은 좀 길어도 상관없다.

 

이 경우라면 차선책이 있다.

create ... select ... 할 때 구간을 나누어 작업한다.

 

종종 쓰는 방법이긴한데  좀 귀찮다.

그래도 어쩔 수 없어 자주 쓰는 방법이다.

그나마 작업부담이 적은 편이다.

 

4. 다른 처리방법 고민하기

Disk에 GB단위의 바이너리 파일이 생기는 건 또다른 골치거리다.

읽기 성능을 위해 어차피 index 를 만들어야 하고,

join 성능이 잘 나오지 않으면 또다시 SQL문을 튜닝해야 한다.

 

임시테이블 (Temp Table)을 만드는 건

여러번에 걸쳐 데이터를 가공해야만 하는 경우에 한해서 하는 게 좋다.

즉 다단계 변환처리 때문에 어쩔 수 없이

중간 데이터를 만들어야만 할 때 선택하는 게 좋다.

 

가능하면 insert, update 없이 select 만으로 작업한다.

규모가 문제라면 기간별로 쪼개어 작업한다.

 

데이터작업의 기본은

최대한 write 작업없이 read 만으로 해보는거다.

데이터를 가공한다는 건, 어떤 식으로든 버림현상이 일어난다는 뜻이다.

재활용될수록 정확도는 떨어진다.

중간가공 데이터는 원본으로 사용하지 않는게 좋다.

 

5. 통계적으로 접근해보기

데이터 전체를 다루고자 할 때 엄두가 나지 않을 수 있다.

데이터 건수가 너무 많기 때문이다.

Terra Bytes 단위의 데이터라면, RDBMS 로는 다루기 힘들어진다.

 

구간구간 잘라서 하는 것도 힘들다.

적당한 구간을 나누어도 작업구간이 100 개를 넘어가면

집중력이 떨어져 제대로 된 통찰력을 얻기 어렵다.

 

이 경우 모집단을 대표할 수 있는,

또는 추정할 수 있는 어떤 집단을 좁혀서 선택해본다.

 

필요한 모델을 정하고,

필요한 걸 샘플링해서 select 한다.

그리곤 Output을 중심으로 추론한다.

 

통계적 접근법은 누락과 요약이라는 한계가 있다.

그래서 반드시 함께 볼 필요가 있다.

 

그래도, 시사점을 찾아내기에는 꽤 강력한 도구이다.

회귀분석, 상관분석을 왜 쓰는지 공부해보자.

중요한 건 그 방법이 아니라,

어떤 때에 그걸 써야 하는지 알아내는거다.

 

6. 그냥 딱보고 알기

그 업무를 10년 정도 해오던 사람이라면

데이터를 몇 개만 Query 해도 금방 무언가를 찾아낸다.

 

능력이 출중해서 그럴까?

아니다.

아주 오랫동안 고민하던 거라서 그렇다.

집에 갈 때마다 생각하던거라 그렇다.

 

이미 머릿 속에서 시뮬레이션을 다 돌려본거다.

그러니 딱 보면 아는거다.

알고 있는 내용을 확인하는 거지,

새로 깨달은 게 아니다.

 

음, 그렇다면 어떻게 미리 고민했던걸까?

평소에 그렇게 살기 때문이다.

원래 그런 사람이기 때문이다.

 

한번 딱보고 모르는 사실을 우루루쾅쾅 알게 되는 건

도사가 깨닫는 수준의 일이다.

그런 건 존재하지 않는다.

 

어떻게 해야 할까?

초보자라면 저걸 다 해볼 필요가 있다.

해야할 일에 적어두고 언젠가는 다 해보도록 하자.

좋은 핑계거리이자 좋은 목표가 된다.

 

클라우드 하나 열어놓고

궁금한 함수나 명령문을 실행해보자.

생각보다 알게되는 지식들이 많다.

 

중수라면 포기할 건 포기하자.

아마 내일 아침까지 뭔가를 하려고

이 글을 찾아왔을테니...

 

이미 고수라면 이런 거 읽고 있지 않겠지만.

혹시 심심해서라도 읽고 있다면 부탁이 있다. 

 

초보자들이 좌충우돌할 수 있게 지켜봐주자.

특히 당신이 그렇게 커왔다면,

후배들도 그렇게 클 수 있게 배려해주자.

 

끝.