ibm x460 모델이고요
8way 에 64기가 시스템입니다. --
리눅스 Red Hat Enterprise AS 4 시스템이구요
mysql 버젼은 4.1.20입니다.
my.cnf 내용은
[client]
port = 3306
socket = /STORAGE/mysql/mysql.sock
default-character-set = euckr
[mysqld]
#log-bin
#read_only
#slave-skip-errors = all
old-password = 1
default-character-set = euckr
init_connect = "set names euckr"
default-collation = euckr_bin
#init_connect = SET collation_connection = euckr_bin
character-set-server = euckr
collation-server = euckr_bin
#collation-database = euckr_bin
skip-bdb
skip-host-cache
#skip-locking
skip-name-resolve
#================= PARAMETER ADD =========================
set-variable = back_log= 1024
# log slow queries : debug...
#set-variable = log_slow_queries=1
log-slow-queries = /STORAGE/mysql/mysql_slow_query.log
log-long-format
long_query_time=10
#==================================
# Query cache setting
#==================================
#query_cache_type = 1
#query_cache_size = 4M
#// source = http://www.vbulletin.com/forum/showthread.php?t=150521
query_cache_limit = 600M
query_cache_size = 6144M
query_cache_type = 1
#query_prealloc_size = 16384
#query_alloc_block_size = 16384
# 4GB file size limit broken
myisam_data_pointer_size = 6
#log=/STORAGE/mysql/mysql_general.log
#=========================================================
# per server
key_buffer_size = 16384M
max_allowed_packet = 64M
#table_cache = 3600
table_cache = 8192
join_buffer_size = 128M
# per thread
max_heap_table_size = 2048M
sort_buffer_size = 128M
read_buffer_size = 128M
myisam_sort_buffer_size = 1024M
myisam_max_sort_file_size = 20G
myisam_max_extra_sort_file_size = 20G
tmp_table_size = 1024M
#thread_cache = 200 => 128 => 400
thread_cache_size = 400
thread_cache = 400
thread_concurrency = 32
max_connections = 100000
#open_files_limit = 32768
open_files_limit = 0
max_connect_errors = 5000000
connect_timeout = 120
#net_read_timeout = 31536000
#net_write_timeout = 31536000
interactive_timeout = 12800
#wait_timeout = 3153600 #default=28800
wait_timeout = 100
#======================================
# INNODB
#======================================
innodb_data_home_dir = /STORAGE/mysql
innodb_log_group_home_dir = /STORAGE/mysql
innodb_log_arch_dir = /STORAGE/mysql
innodb_log_files_in_group = 3
innodb_log_file_size = 1300M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
#innodb_lock_wait_timeout = 1073741824
innodb_lock_wait_timeout = 50
innodb_thread_concurrency=16
#inndDB set-up
innodb_autoextend_increment = 50
#innodb_file_per_table
innodb_data_file_path = ibdata1:5000M;ibdata2:5000M;ibdata3:5000M;ibdata4:5000M;ibdata5:5000M;ibdata6:5000M;ibdata7:5000M;ibdata8:5000M;ibdata9:5000M;ibdata10:5000M;ibdata11:5000M;ibdata12:5000M;ibdata13:5000M;ibdata14:5000M;ibdata15:5000M;ibdata16:5000M;ibdata17:5000M;ibdata18:5000M;ibdata19:5000M;ibdata20:5000M;ibdata21:5000M;ibdata22:5000M;ibdata23:5000M;ibdata24:5000M;ibdata25:5000M;ibdata26:5000M;ibdata27:5000M;ibdata28:5000M;ibdata29:5000M;ibdata30:5000M;ibdata31:5000M;ibdata32:5000M;ibdata33:5000M;ibdata34:5000M;ibdata35:5000M;ibdata36:5000M;ibdata37:5000M;ibdata38:5000M;ibdata39:5000M;ibdata40:5000M;ibdata41:5000M;ibdata42:5000M;ibdata43:5000M;ibdata44:5000M;ibdata45:5000M;ibdata46:5000M;ibdata47:5000M;ibdata48:5000M;ibdata49:5000M;ibdata50:5000M;ibdata51:5000M;ibdata52:5000M;ibdata53:5000M;ibdata54:5000M;ibdata55:5000M;ibdata56:5000M;ibdata57:5000M;ibdata58:5000M;ibdata59:5000M;ibdata60:5000M;ibdata61:5000M;ibdata62:5000M;ibdata63:5000M;ibdata64:5000M;ibdata65:5000M;ibdata66:5000M;ibdata67:5000M;ibdata68:5000M;ibdata69:5000M;ibdata70:5000M;ibdata71:5000M;ibdata72:5000M;ibdata73:5000M;ibdata74:5000M;ibdata75:5000M;ibdata76:5000M;ibdata77:5000M;ibdata78:5000M;ibdata79:5000M;ibdata80:5000M;ibdata81:5000M;ibdata82:5000M;ibdata83:5000M;ibdata84:5000M;ibdata85:5000M;ibdata86:5000M;ibdata87:5000M;ibdata88:5000M;ibdata89:5000M;ibdata90:5000M;ibdata91:5000M;ibdata92:5000M;ibdata93:5000M;ibdata94:5000M;ibdata95:5000M;ibdata96:5000M;ibdata97:5000M;ibdata98:5000M;ibdata99:5000M;ibdata100:5000M:autoextend
innodb_buffer_pool_size = 32000M
innodb_additional_mem_pool_size = 256M
socket = /STORAGE/mysql/mysql.sock
user = mysql
datadir = /STORAGE/mysql
[safe_mysqld]
open-files-limit = 32768
이렇게 세팅 되어 있습니다...
값이 잘못 들어 가 있거나.. 아니면 세팅이 잘못된 부분좀 지적해주시면 감사하겠습니다 (__)
서버의 load average 값이 자꾸 올라 가고 디비 엑세스 속도가 너무 느려져요..
테이블에 락도 자주 걸리고..
튜닝시 제일 신경써줘야되는 부분도 지적 좀 해주시면 감사 (__)
|