데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

MySQL Q&A 30197 게시물 읽기

No. 30197

자꾸 lock timeout에 걸리는데요..
작성자	최진규(cjg1012)	작성일	2012-02-02 01:33ⓒ 2012-02-02 01:36ⓜ	조회수	11,007

도무지 왜그런지 모르겠네요..

UPDATE MT_GUESTBOOK SET READ_YN='Y' WHERE MY_EMAIL = 'XXX@naver.com' AND SEQ >= 510656;

이런 단순한 쿼리입니다.

MT_GUESTBOOK 테이블은 innod db 이구요 건수는 431272건 있습니다.

primary key는 seq구요 my_email은 index로 잡혀 있습니다.

사용자가 좀 많긴 한데요..그런다고 저 쿼리 하나 실행하는데 3~20초가 걸리는데요..그러면서 lock이 걸려요..

도무지 뭐가 문제인지..ㅜㅜ

등호문제 일까하고

UPDATE MT_GUESTBOOK SET READ_YN='Y' WHERE MY_EMAIL = 'XXX@naver.com';

이렇게 해봐도 lock이 걸리구요 처리 시간이 3초 이상 걸리네요..

select는 빠릅니다..업데이트가 문제인데..

혹시나 mysql설정도 올립니다.

[mysqld]

init_connect=SET collation_connection = utf8_general_ci

init_connect=SET NAMES utf8

collation-server=utf8_general_ci

character-set-server=utf8

table_open_cache = 2048

max_connections=4096

max_connect_errors=2048

query_cache_type = 1

query_cache_size = 64M

query_cache_limit = 2M

wait_timeout=10

max_allowed_packet=100M

slow-query-log = 1

slow_query_log_file = /home/mysql/var/slow-query.log

log-error = /home/mysql/var/error-logs

long_query_time=3

#skip-innodb

skip-name-resolve

lower_case_table_names=1

tmpdir = /home/tmp

## cafe24 edit

table_cache=2000

back_log = 1024

sync_binlog = 1

binlog_cache_size = 1M

max_heap_table_size = 1024M

ft_min_word_len = 4

thread_stack = 192K

transaction_isolation = REPEATABLE-READ

tmp_table_size = 512M

back_log = 512

read_buffer_size = 2M

read_rnd_buffer_size = 8M

join_buffer_size = 8M

sort_buffer_size = 8M

thread_cache_size = 8

thread_concurrency = 64

# *** INNODB Specific options ***

innodb_file_per_table

innodb_log_file_size = 256M

innodb_additional_mem_pool_size = 24M

innodb_buffer_pool_size = 1800M

innodb_data_file_path=ibdata1:10M:autoextend:max:10000M

innodb_data_home_dir = /home/mysql/var

innodb_file_io_threads = 8

innodb_thread_concurrency = 36

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 16M

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 5 --> 이부분은 30에서 혹시나 하고 줄여봤습니다.

#innodb_flush_method = O_DIRECT

#replication

log-bin = mysql-bin

server-id = 1

binlog-do-db = comalong

binlog-ignore-db = mysql

binlog-ignore-db = information_schema

[mysql]

default-character-set=utf8

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

SHOW INDEX FROM MT_GUESTBOOK;

의 결과와
5.6 버전을 사용하고 계신다면

EXPLAIN UPDATE ~~~

의 결과를, 그 외의 버전이라면

EXPLAIN SELECT READ_YN FROM MT_GUESTBOOK WHERE MY_EMAIL = 'XXX@naver.com' AND SEQ >= 510656;

의 결과를 보여주세요.

우욱님이 2012-02-02 10:37에 작성한 댓글입니다. Edit Ｘ

Table,Non_unique,Key_name,Seq_in_index,Column_name,Collation,Cardinality,Sub_part,Packed,Null,Index_type,Comment

mt_guestbook,0,PRIMARY,1,seq,A,458698,null,null,,BTREE,

mt_guestbook,1,idx_from_email,1,from_email,A,57337,null,null,,BTREE,

mt_guestbook,1,idx_my_email,1,my_email,A,32764,null,null,,BTREE,

mt_guestbook,1,idx_1,1,seq,A,458698,null,null,,BTREE,

mt_guestbook,1,idx_1,2,my_email,A,458698,null,null,,BTREE,

SHOW INDEX FROM MT_GUESTBOOK;

결과 입니다.

idx_1은 seq와 my_email을 결합해봤는데 효과 있는지는 아직 모르겠어요...사람들이 몰리는 시간이 아니라..

그리고 mysql 5.6이하기 때문에 update에 explain은 안먹고요

select explain 결과입니다.

EXPLAIN SELECT * FROM mt_guestbook WHERE MY_EMAIL = 'XXX@gmail.com' AND SEQ >= CONVERT('513515', UNSIGNED);

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra

1,SIMPLE,mt_guestbook,index_merge,PRIMARY,idx_my_email,idx_1,idx_my_email,PRIMARY,302,4,null,118,Using intersect(idx_my_email,PRIMARY); Using where

하드웨적으로는 top명령어로 봤을때 이상없어요..

free -m -t 명령어로 봤을때도 이상이 없는데..

사람들이 갑자기 몰리는 시간에 그러네요..ㅜㅜ..원인을 정말 모르겠습니다.ㅜㅜ

최진규(cjg1012)님이 2012-02-02 15:20에 작성한 댓글입니다.

idx_1은 의미 없는 index입니다.(drop하세요.ㅋ)

CREATE INDEX idx_1 ON mt_guestbook( seq, my_email );

과 같은 식으로 만드셨을 것으로 생각됩니다. 이것을

CREATE INDEX idx_2 ON mt_guestbook( my_email, seq );

로 컬럼의 순서를 바꾸시면 효과를 보실 수 있을 것으로 보입니다.
explain 결과를 보시면 index_merge를 my_email로 돌려서 seq에 대해서 사용하겠다는 계획을 보실 수 있는데요,
컬럼의 순서를 바꾸게 되면 my_email이 XXX@naver.com인 애들 중에서 seq가 510656애를 딱 찍은 후에 my_email이 XXX@naver.com이 아닌 레코드까지 몽창 다 update하자로 동작할 겁니다. 즉, update를 할 대상만 콕 찍어서 들여다 볼 수 있겠죠.

where절에 사용되는 seq가 쿼리 때마다 바뀌는지 궁금합니다.
예를 들어 과거의 어느 날 시스템 개편을 했던 특정 시점 seq=510656 이후에 data에 대해서 처리하는 관계로 맨날 같은 510656이라는 숫자가 쿼리에 들어가는 거라면 시간이 지나면 계속 느려지는 요소가 될 거라는 생각이 듭니다.

혹시 그 외의 요소가 있는지 --log-slow-queries 로 잡아 보신거죠? ㅋ

우욱님이 2012-02-02 17:14에 작성한 댓글입니다.
이 댓글은 2012-02-02 17:58에 마지막으로 수정되었습니다. Edit Ｘ

조언 감사합니다. 컬럼의 순서도 중요하네요..

우선 seq는 매번 변합니다.

읽을때마다 변할수도 있고 안변할수도 있죠..

my_email로 글이 계속 등록되면 읽을때마다 seq는 변하거든요..

읽을때 limit가 0, 10 이렇게 제한되어 있어 limt 10,10일 경우에는 seq가 변하죠..

조언되로 인덱스 컬럼의 순서를 한번 변경해보겠습니다.

그리고 log-slow-queries 보면 제다 위에 있는 update쿼리입니다.

그래서 이 update 쿼리에 대해서 질문한거구요

최진규(cjg1012)님이 2012-02-02 17:54에 작성한 댓글입니다.
이 댓글은 2012-02-02 18:01에 마지막으로 수정되었습니다.

컬럼 순서를 바꿨더니 아까와는 완전 다르네요..ㅎㅎ

show index from mt_guestbook; 하니

Table,Non_unique,Key_name,Seq_in_index,Column_name,Collation,Cardinality,Sub_part,Packed,Null,Index_type,Comment

mt_guestbook,0,PRIMARY,1,seq,A,451549,null,null,,BTREE,

mt_guestbook,1,idx_from_email,1,from_email,A,56443,null,null,,BTREE,

mt_guestbook,1,idx_my_email,1,my_email,A,50172,null,null,,BTREE,

mt_guestbook,1,idx_1,1,my_email,A,50172,null,null,,BTREE,

mt_guestbook,1,idx_1,2,seq,A,451549,null,null,,BTREE,

이렇게 나오는군요...idx_1 에 my_email로 한번거르고 seq로 찾을꺼 같습니다.

EXPLAIN SELECT * FROM mt_guestbook WHERE MY_EMAIL = 'ksy92070786@gmail.com' AND SEQ >= CONVERT('513515', UNSIGNED);

의 결과도

id;select_type;table;type;possible_keys;key;key_len;ref;rows;Extra

1;SIMPLE;mt_guestbook;range;PRIMARY,idx_my_email,idx_1;idx_1;306;null;35;Using where

이렇게 나오는군요..

이렇게 하면 효과가 있어보는데..

괜찮을까요??

최진규(cjg1012)님이 2012-02-02 18:00에 작성한 댓글입니다.
이 댓글은 2012-02-02 18:04에 마지막으로 수정되었습니다.

괜찮을껄요? ㅋ

우욱님이 2012-02-02 20:28에 작성한 댓글입니다. Edit Ｘ

[Top]

No.	제목	작성자	작성일	조회
30202	컬럼의 charset 과 collation 변경 관련 질문 [2]	땡필이	2012-02-06	9160
30201	innodDB commit 실행시간 문제.. [1]	최진규	2012-02-04	8929
30198	my sql 를 사용중 MS Access 2010 을 이용하여 [1]	이석현	2012-02-02	7534
30197	자꾸 lock timeout에 걸리는데요.. [6]	최진규	2012-02-02	11007
30196	MySQL DB 시간표 ERD 관련... [2]	한동희	2012-02-01	9461
30195	효율적인 query ? [1]	백영민	2012-01-26	7703
30194	update 할떄 where 절에 조건을 주려고 합니다...(자체조인) [4]	박순채	2012-01-25	10507