평소 대비 5~6배이상 갑자기 폭주했는데요.. 

부하가 당연히 걸리겠지만 너무 심한거 같아 질문드려요..ㅜㅜ

현재 조인처리된 sql문은 전혀 처리못합니다.

단일테이블 select a from table a 이것도 5초정도 걸리니 OTL..

 

> explain 질의 - 조인 sql

id  select_type  table  type   possible_keys                          key                 key_len  ref    rows   Extra                                         

1   SIMPLE       A      ref    PRIMARY,idx1_mt_member,idx3_mt_member  idx3_mt_member      2        const  54465  Using where; Using temporary; Using filesort  

1   SIMPLE       B      range  idx_connect_yymmdd                     idx_connect_yymmdd  4               89887  Using where; Using index; Using join buffer   

 

 

그리고 innodb상태를 보면 읽기를 버퍼풀에서 요청한값과 실제 버퍼풀에서 읽은 값이 너무 차이가 나는데 문제가 있는거 같은데..

Innodb_buffer_pool_read_requests 3425035170

Innodb_buffer_pool_reads 9374

왠지 전현 버퍼 pool를 이용하지 않는 느낌이 있는데요..

 

>mysqladmin status

Uptime: 1905  Threads: 26  Questions: 794029  Slow queries: 2299  Opens: 827  Flush tables: 1  Open tables: 820  Queries per second avg: 416.813

(위 데이터는 사용자가 좀 줄었을 시간입니다)

 

서버스펙

CPU : Intel Xeon Quad-Core E3-1230

RAM : DDR3 4G 10600 U-ECCHDDSATA2 500GB (7200.ES/기업용)

LAN : 1Gbps 광(光) Dedicated Line

네트워크 : 10Mbps

 

 

그리고 상태 설정이예요..

 

> show status like '%innodb%';

 

Innodb_buffer_pool_pages_data 10190

Innodb_buffer_pool_pages_dirty 1912

Innodb_buffer_pool_pages_flushed 6075

Innodb_buffer_pool_pages_free 149327

Innodb_buffer_pool_pages_misc 483

Innodb_buffer_pool_pages_total 160000

Innodb_buffer_pool_read_ahead_rnd 33

Innodb_buffer_pool_read_ahead_seq 5

Innodb_buffer_pool_wait_free 0

Innodb_buffer_pool_write_requests 137732

Innodb_data_fsyncs 1429

Innodb_data_pending_fsyncs 0

Innodb_data_pending_reads 0

Innodb_data_pending_writes 0

Innodb_data_read 168316928

Innodb_data_reads 9768

Innodb_data_writes 24884

Innodb_data_written 217987584

Innodb_dblwr_pages_written 6075

Innodb_dblwr_writes 134

Innodb_log_waits 0

Innodb_log_write_requests 16109

Innodb_log_writes 21927

Innodb_os_log_fsyncs 1160

Innodb_os_log_pending_fsyncs 0

Innodb_os_log_pending_writes 0

Innodb_os_log_written 18873856

Innodb_page_size 16384

Innodb_pages_created 50

Innodb_pages_read 10140

Innodb_pages_written 6075

Innodb_row_lock_current_waits 0

Innodb_row_lock_time 22924

Innodb_row_lock_time_avg 1528

Innodb_row_lock_time_max 6580

Innodb_row_lock_waits 15

Innodb_rows_deleted 185

Innodb_rows_inserted 1238

Innodb_rows_read 850338471

Innodb_rows_updated 21199

 

> show variables like '%innodb%';

 

have_innodb YES

ignore_builtin_innodb OFF

innodb_adaptive_hash_index ON

innodb_additional_mem_pool_size 16777216

innodb_autoextend_increment 8

innodb_autoinc_lock_mode 1

innodb_buffer_pool_size 2621440000

innodb_checksums ON

innodb_commit_concurrency 0

innodb_concurrency_tickets 500

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

innodb_data_home_dir /home/mysql/var

innodb_doublewrite ON

innodb_fast_shutdown 1

innodb_file_io_threads 4

innodb_file_per_table OFF

innodb_flush_log_at_trx_commit 2

innodb_flush_method O_DIRECT

innodb_force_recovery 0

innodb_lock_wait_timeout 120

innodb_locks_unsafe_for_binlog OFF

innodb_log_buffer_size 4194304

innodb_log_file_size 5242880

innodb_log_files_in_group 2

innodb_log_group_home_dir ./

innodb_max_dirty_pages_pct 90

innodb_max_purge_lag 0

innodb_mirrored_log_groups 1

innodb_open_files 300

innodb_rollback_on_timeout OFF

innodb_stats_method nulls_equal

innodb_stats_on_metadata ON

innodb_support_xa ON

innodb_sync_spin_loops 20

innodb_table_locks ON

innodb_thread_concurrency 8

innodb_thread_sleep_delay 10000

innodb_use_legacy_cardinality_algorithm ON

 

그리고 my.cnt 내용입니다.

[mysqld]

init_connect=SET collation_connection = utf8_general_ci

init_connect=SET NAMES utf8

collation-server=utf8_general_ci

character-set-server=utf8

#character-set-client-handshake=false

table_cache=2000

max_connections=200

max_user_connections=0

max_connect_errors=10000

query_cache_type = 1

query_cache_size = 200M

#query_cache_type = 0

#query_cache_size = 0M

#query_cache_limit = 5M - firechoi

query_cache_limit = 5M

wait_timeout=1200

max_allowed_packet=16M

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

 

## added by stone

join_buffer_size = 8M

read_buffer_size = 4M

sort_buffer_size = 64M

#log-queries-not-using-indexes

thread_cache_size = 40

 

innodb_additional_mem_pool_size = 16M

#innodb_buffer_pool_size = 2500M - firechoi

innodb_buffer_pool_size = 2500M

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

innodb_data_home_dir = /home/mysql/var

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 4M

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

#innodb_flush_method = O_DSYNC

innodb_flush_method = O_DIRECT

 

[mysql]

default-character-set=utf8