database.sarang.net
UserID
Passwd
Database
DBMS
ㆍMySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MySQL Q&A 30719 게시물 읽기
No. 30719
컨넥션 연결이 제한되는 것 같습니다..도움부탁드립니다.
작성자
entest(pentest)
작성일
2014-04-18 09:33
조회수
14,039

안녕하세요 

 

mysql 사용중 문제가 발생하여, 도움을 부탁드립니다. 

 

PC는 우분투 13.10에  램 16기가, i7 cpu를 사용하고 있구요..

 

SQL 버전은  5.5.35-0ubuntu0.13.10.2 입니다.

 

컬럼은 다음과 같이 4개 필드로 돼있고, 약 210만개의 row가 저장되어 있습니다.

 

 

| Field   | Type         | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| domain  | varchar(200) | YES  | MUL | NULL    |       |

| state   | int(11)      | YES  |     | NULL    |       |

| country | varchar(20)  | YES  |     | NULL    |       |

| count   | int(11)      | YES  |     | NULL    |       |

+---------+--------------+------+-----+---------+-------+

 

프로그램 하나가 한번에 데이터 500개를 랜덤으로 가져온 후, update 500번을 돌려 state를 1로 셋팅하고 있습니다.

 

select domain from radar_url where state != 1 order by rand() limit 500;

update radar_url set state =1 where domain ='%s'

 

 

이런식으로 프로그램 20개를 돌리는데, 10개 정도는 제대로 도는데 10개 정도는 죽어버리고 맙니다. 

에러는 "Lock wait timeout exceeded; try restarting transaction"이 뜹니다.

 

이 에러를 고치려고 먼저 innodb_lock_wait_timeout을 200초로 늘려줬는데, 안되길래 다시 50초로 내렸습니다.

/etc/mysql/my.cnf에 max_connections와 table cache도 수정하였습니다.

 

 

 

 

max_connections        = 500
table_cache            = 128
 
 
그런데 이 값을 수정하고 나서부터는 아예 1개 프로그램밖에 접속이 안되네요.. 
 
 
어떻게 하면 connection을 늘릴수 있을지 도움부탁드립니다. 
 
감사합니다. 
 
 
 
auto_increment_increment        1
auto_increment_offset   1
autocommit      ON
automatic_sp_privileges ON
back_log        50
basedir /usr
big_tables      OFF
binlog_cache_size       32768
binlog_direct_non_transactional_updates OFF
binlog_format   STATEMENT
binlog_stmt_cache_size  32768
bulk_insert_buffer_size 8388608
character_set_client    utf8
character_set_connection        utf8
character_set_database  latin1
character_set_filesystem        binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
character_sets_dir      /usr/share/mysql/charsets/
collation_connection    utf8_general_ci
collation_database      latin1_swedish_ci
collation_server        latin1_swedish_ci
completion_type NO_CHAIN
concurrent_insert       AUTO
connect_timeout 10
datadir /var/lib/mysql/
date_format     %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_storage_engine  InnoDB
default_week_format     0
delay_key_write ON
delayed_insert_limit    100
delayed_insert_timeout  300
delayed_queue_size      1000
div_precision_increment 4
engine_condition_pushdown       ON
error_count     0
event_scheduler OFF
expire_logs_days        10
external_user
flush   OFF
flush_time      0
foreign_key_checks      ON
ft_boolean_syntax       + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit        20
ft_stopword_file        (built-in)
general_log     OFF
general_log_file        /var/lib/mysql/sirt-mysql.log
group_concat_max_len    1024
have_compress   YES
have_crypt      YES
have_csv        YES
have_dynamic_loading    YES
have_geometry   YES
have_innodb     YES
have_ndbcluster NO
have_openssl    DISABLED
have_partitioning       YES
have_profiling  YES
have_query_cache        YES
have_rtree_keys YES
have_ssl        DISABLED
have_symlink    YES
 
identity        0
ignore_builtin_innodb   OFF
init_connect
init_file
init_slave
innodb_adaptive_flushing        ON
innodb_adaptive_hash_index      ON
innodb_additional_mem_pool_size 8388608
innodb_autoextend_increment     8
innodb_autoinc_lock_mode        1
innodb_buffer_pool_instances    1
innodb_buffer_pool_size 134217728
innodb_change_buffering all
innodb_checksums        ON
innodb_commit_concurrency       0
innodb_concurrency_tickets      500
innodb_data_file_path   ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite      ON
innodb_fast_shutdown    1
innodb_file_format      Antelope
innodb_file_format_check        ON
innodb_file_format_max  Antelope
innodb_file_per_table   OFF
innodb_flush_log_at_trx_commit  1
innodb_flush_method
innodb_force_load_corrupted     OFF
innodb_force_recovery   0
innodb_io_capacity      200
innodb_large_prefix     OFF
innodb_lock_wait_timeout        50
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  8388608
innodb_log_file_size    5242880
innodb_log_files_in_group       2
innodb_log_group_home_dir       ./
innodb_max_dirty_pages_pct      75
innodb_max_purge_lag    0
innodb_mirrored_log_groups      1
innodb_old_blocks_pct   37
innodb_old_blocks_time  0
innodb_open_files       300
innodb_print_all_deadlocks      OFF
innodb_purge_batch_size 20
innodb_purge_threads    0
innodb_random_read_ahead        OFF
innodb_read_ahead_threshold     56
innodb_read_io_threads  4
innodb_replication_delay        0
innodb_rollback_on_timeout      OFF
innodb_rollback_segments        128
innodb_spin_wait_delay  6
innodb_stats_method     nulls_equal
innodb_stats_on_metadata        ON
innodb_stats_sample_pages       8
innodb_strict_mode      OFF
innodb_support_xa       ON
innodb_sync_spin_loops  30
innodb_table_locks      ON
innodb_thread_concurrency       0
innodb_thread_sleep_delay       10000
innodb_use_native_aio   ON
innodb_use_sys_malloc   ON
innodb_version  5.5.35
innodb_write_io_threads 4
 
interactive_timeout     28800
join_buffer_size        131072
keep_files_on_create    OFF
key_buffer_size 16777216
key_cache_age_threshold 300
key_cache_block_size    1024
key_cache_division_limit        100
large_files_support     ON
large_page_size 0
large_pages     OFF
last_insert_id  0
lc_messages     en_US
lc_messages_dir /usr/share/mysql/
lc_time_names   en_US
license GPL
local_infile    ON
lock_wait_timeout       31536000
locked_in_memory        OFF
log     OFF
log_bin OFF
log_bin_trust_function_creators OFF
log_error       /var/log/mysql/error.log
log_output      FILE
log_queries_not_using_indexes   OFF
log_slave_updates       OFF
log_slow_queries        OFF
log_warnings    1
long_query_time 10.000000
low_priority_updates    OFF
lower_case_file_system  OFF
lower_case_table_names  0
max_allowed_packet      16777216
max_binlog_cache_size   18446744073709547520
max_binlog_size 104857600
max_binlog_stmt_cache_size      18446744073709547520
max_connect_errors      10
max_connections 500
max_delayed_threads     20
max_error_count 64
max_heap_table_size     16777216
max_insert_delayed_threads      20
max_join_size   18446744073709551615
max_length_for_sort_data        1024
max_long_data_size      16777216
max_prepared_stmt_count 16382
max_relay_log_size      0
max_seeks_for_key       18446744073709551615
max_sort_length 1024
max_sp_recursion_depth  0
max_tmp_tables  32
max_user_connections    0
max_write_lock_count    18446744073709551615
metadata_locks_cache_size       1024
min_examined_row_limit  0
multi_range_count       256
myisam_data_pointer_size        6
myisam_max_sort_file_size       9223372036853727232
myisam_mmap_size        18446744073709551615
myisam_recover_options  BACKUP
myisam_repair_threads   1
myisam_sort_buffer_size 8388608
myisam_stats_method     nulls_unequal
myisam_use_mmap OFF
net_buffer_length       16384
net_read_timeout        30
 
net_write_timeout       60
new     OFF
old     OFF
old_alter_table OFF
old_passwords   OFF
open_files_limit        2500
optimizer_prune_level   1
optimizer_search_depth  62
optimizer_switch        index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
performance_schema      OFF
performance_schema_events_waits_history_long_size       10000
performance_schema_events_waits_history_size    10
performance_schema_max_cond_classes     80
performance_schema_max_cond_instances   1000
performance_schema_max_file_classes     50
performance_schema_max_file_handles     32768
performance_schema_max_file_instances   10000
performance_schema_max_mutex_classes    200
performance_schema_max_mutex_instances  1000000
performance_schema_max_rwlock_classes   30
performance_schema_max_rwlock_instances 1000000
performance_schema_max_table_handles    100000
performance_schema_max_table_instances  50000
performance_schema_max_thread_classes   50
performance_schema_max_thread_instances 1000
pid_file        /var/run/mysqld/mysqld.pid
plugin_dir      /usr/lib/mysql/plugin/
port    3306
preload_buffer_size     32768
profiling       OFF
profiling_history_size  15
protocol_version        10
proxy_user
pseudo_slave_mode       OFF
pseudo_thread_id        63
query_alloc_block_size  8192
query_cache_limit       1048576
query_cache_min_res_unit        4096
query_cache_size        16777216
query_cache_type        ON
query_cache_wlock_invalidate    OFF
query_prealloc_size     8192
rand_seed1      0
rand_seed2      0
range_alloc_block_size  4096
read_buffer_size        131072
read_only       OFF
read_rnd_buffer_size    262144
relay_log
relay_log_index
relay_log_info_file     relay-log.info
relay_log_purge ON
relay_log_recovery      OFF
relay_log_space_limit   0
report_host
report_password
report_port     3306
report_user
rpl_recovery_rank       0
secure_auth     OFF
secure_file_priv
server_id       0
skip_external_locking   ON
skip_name_resolve       OFF
 
skip_show_database      OFF
slave_compressed_protocol       OFF
slave_exec_mode STRICT
slave_load_tmpdir       /tmp
slave_max_allowed_packet        1073741824
slave_net_timeout       3600
slave_skip_errors       OFF
slave_transaction_retries       10
slave_type_conversions
slow_launch_time        2
slow_query_log  OFF
slow_query_log_file     /var/lib/mysql/sirt-mysql-slow.log
socket  /var/run/mysqld/mysqld.sock
sort_buffer_size        2097152
sql_auto_is_null        OFF
sql_big_selects ON
sql_big_tables  OFF
sql_buffer_result       OFF
sql_log_bin     ON
sql_log_off     OFF
sql_low_priority_updates        OFF
sql_max_join_size       18446744073709551615
sql_mode
sql_notes       ON
sql_quote_show_create   ON
sql_safe_updates        OFF
sql_select_limit        18446744073709551615
sql_slave_skip_counter  0
sql_warnings    OFF
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_key
storage_engine  InnoDB
stored_program_cache    256
sync_binlog     0
sync_frm        ON
sync_master_info        0
sync_relay_log  0
sync_relay_log_info     0
system_time_zone        KST
table_definition_cache  400
table_open_cache        128
thread_cache_size       8
thread_concurrency      10
thread_handling one-thread-per-connection
thread_stack    196608
time_format     %H:%i:%s
time_zone       SYSTEM
timed_mutexes   OFF
timestamp       1397780948
tmp_table_size  16777216
tmpdir  /tmp
transaction_alloc_block_size    8192
transaction_prealloc_size       4096
tx_isolation    REPEATABLE-READ
unique_checks   ON
updatable_views_with_limit      YES
version 5.5.35-0ubuntu0.13.10.2
version_comment (Ubuntu)
version_compile_machine x86_64
version_compile_os      debian-linux-gnu
wait_timeout    28800
 

 

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

 정확하지는 않습니다만,

20개의 프로그램이 동시에 실행 중일텐데, A process에서 SELECT 된 500개의 random record와 B process에서 SELECT된 500개의 random record가 겹치는 듯 합니다.

A에서 UPDATE된 레코드를 B에서 다시 UPDATE하려는 시도 같습니다.

제가 생각하는 해결 방법으로는

 

1) Transaction을 사용중이라면 auto commit mode로 사용하는 것은 어떨지 싶습니다.

2) 1)이 안 된다면 SELECT ... FOR UPDATE를 사용하여 내가 UPDATE될 레코드를 다른 프로세스가 SELECT하지 못하도록 하면 어떨지요?

3)  1), 2)도 어렵다면 아예 TABLE Lock을 사용하는 것도 방법이지만, 그렇다면 프로세스를 1개 돌리는 것과 별 다르지 않다고 생각되는군요.

1)번 방법이 젤 좋아보입니다.

 

그런데

1) 210만개 테이블에서 500개의 random record를 가져오는 방법이 너무 느려보입니다. SELECT COUNT(*) FROM radar_url WHERE state != 1의 결과는 보통 몇인지 궁금하네요.

2) domain 컬럼이 PK나 UNIQUE가 아닌데요. UPDATE radar_url SET state = 1 WHERE domain = ?가 SELECT해온 레코드 1건만 UPDATE하는 것인지도 확인해 보셔야 할 듯 합니다.

허정수(wertyu)님이 2014-04-18 10:03에 작성한 댓글입니다.

 Ram 16기가 대비 my.cnf 의 내용이 너무 적어 보입니다.

 

예를 들어 innodb_buffer_pool_size가 128MB인 듯 한데요. 팍팍 늘리셔도 될 듯 한데요. 다른 값들도 점검해 보시고요.

저도 써보진 않았는데, http://mysqltuner.com 이건 어떠실지.

허정수(wertyu)님이 2014-04-18 10:19에 작성한 댓글입니다.
이 댓글은 2014-04-18 10:20에 마지막으로 수정되었습니다.

 도움말씀 정말 감사드립니다. 

 

말씀하신데로 튜너 돌려서 설정도 고쳐보고 했는데.. 도저히 증상해결이 안돼서 

결국 하드웨어를 교체하였습니다. 

 

128gb ssd에 돌리니까.. 타임아웃이 발생하지 않네요 --;;

 

데이터 양은 늘어나지 않을것 같으니 그냥 여기다 돌려야겠습니다. 

 

다시한번 감사합니다. 

entest(pentest)님이 2014-04-22 16:39에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
30723MySQL 트렌젝션사용예제인데 문제점있을까요?
이재식
2014-05-07
9713
30721mysql 5.1.4버젼사용중 select [2]
이재식
2014-04-30
9419
30720code값 치환 관련 쿼리 질문. [1]
열혈파넵
2014-04-28
9363
30719컨넥션 연결이 제한되는 것 같습니다..도움부탁드립니다. [3]
entest
2014-04-18
14039
30718[질문] query 가능하신분 조언 부탁드립니다. [1]
안동석
2014-04-14
9460
30716[건의] MariaDB용 별도의 게시판이 있으면 좋겠는데 말입니다. [6]
MariaDB
2014-04-09
10497
30715MariaDB 10.0.10 정식버전(GA,Stable)이 이미 2014년3월31일에 나와버렸습니다. ( MySQL 5.6 에 대응됨. ) [5]
MariaDB10
2014-04-09
10829
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.021초, 이곳 서비스는
	PostgreSQL v16.4로 자료를 관리합니다