Skip to content
Server
2016.07.26 22:57

mysql 16CPU / 16GB My.cnf

조회 수 750 추천 수 0 댓글 1
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄

어디까지나 참고 사항이다.


mysql 16CPU / 16GB My.cnf

내껀 

8코어 8GB


여기에 WEB과 DB를 동시에 돌리니 다르다.


그리고 기록을 많이 하는지, 조회를 많이 하는지에 따라 또 틀려진다.


경우의 수는 수만가지가 될 수 있다.

[mysqld_safe]
nice = -15
[mysqld]
max_binlog_size = 256M #max size for binlog before rolling
expire_logs_days = 1 #binlog files older than this will be purged
## Per-Thread Buffers * (max_connections) = total per-thread mem usage
thread_stack = 256K #default: 32bit: 192K, 64bit: 256K
sort_buffer_size = 1M #default: 2M, larger may cause perf issues
read_buffer_size = 1M #default: 128K, change in increments of 4K
read_rnd_buffer_size = 1M #default: 256K
join_buffer_size = 1M #default: 128K
binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries
## Query Cache
query_cache_size = 32M #global buffer
query_cache_limit = 512K #max query result size to put in cache
## Connections
max_connections = 2000 #multiplier for memory usage via per-thread buffers
max_connect_errors = 100 #default: 10
concurrent_insert = 2 #default: 1, 2: enable insert for all instances
connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10
## Table and TMP settings
max_heap_table_size = 1G #recommend same size as tmp_table_size
bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size
tmp_table_size = 1G #recommend 1G min
# set tmpdir to a ramdisk?
#tmpdir = /data/mysql-tmp0:/data/mysql-tmp1
## Thread settings
thread_concurrency = 0 #recommend 2x CPU cores [0 create as many as needed]
thread_cache_size = 100 #recommend 5% of max_connections
## MyISAM Engine
key_buffer = 1M #global buffer
myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes
myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes
## InnoDB IO settings - 5.5.x and greater
innodb_write_io_threads = 16
innodb_read_io_threads = 16
## InnoDB Plugin Independent Settings
innodb_file_per_table #enable always
innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend
innodb_buffer_pool_size = 12G #global buffer
innodb_additional_mem_pool_size = 4M #global buffer
innodb_flush_log_at_trx_commit = 0 #2/0 = perf, 1 = ACID
innodb_log_file_size = 256M
innodb_log_buffer_size = 128M #global buffer
innodb_lock_wait_timeout = 300
innodb_thread_concurrency = 16 #recommend 2x core quantity
innodb_commit_concurrency = 16 #recommend 4x num disks
innodb_flush_method = O_DSYNC #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI
innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush
skip-innodb-doublewrite
sync_binlog = 0
= SYSTEM INFORMATION
Linux Xen6-WH-Database 2.6.32-262.el6.x86_64 #1 SMP Sun Apr 8 18:38:00 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
=CPUINFO
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 45
model name : Intel(R) Xeon(R) CPU E5-2640 0 @ 2.50GHz
stepping : 7
cpu MHz : 2500.159
cache size : 15360 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat clflush mmx fxsr sse sse2 syscall lm rep_good unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 x2apic popcnt aes hypervisor lahf_lm
bogomips : 5000.31
clflush size : 64
cache_alignment : 64
address sizes : 46 bits physical, 48 bits virtual
power management:
= REMOVED IDENTICAL PROCESSORS THERE ARE 16 TOTAL PROCESSORS
processor : 15
vendor_id : GenuineIntel
cpu family : 6
model : 45
model name : Intel(R) Xeon(R) CPU E5-2640 0 @ 2.50GHz
stepping : 7
cpu MHz : 2500.159
cache size : 15360 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat clflush mmx fxsr sse sse2 syscall lm rep_good unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 x2apic popcnt aes hypervisor lahf_lm
bogomips : 5000.44
clflush size : 64
cache_alignment : 64
address sizes : 46 bits physical, 48 bits virtual
power management:
MEMORYINFO\n
MemTotal: 16313180 kB
MemFree: 6733304 kB
Buffers: 175304 kB
Cached: 5266544 kB
SwapCached: 0 kB
Active: 8468376 kB
Inactive: 662608 kB
Active(anon): 3691820 kB
Inactive(anon): 16 kB
Active(file): 4776556 kB
Inactive(file): 662592 kB
Unevictable: 34760 kB
Mlocked: 10232 kB
SwapTotal: 2064376 kB
SwapFree: 2064376 kB
Dirty: 64 kB
Writeback: 0 kB
AnonPages: 3723912 kB
Mapped: 23256 kB
Shmem: 220 kB
Slab: 202268 kB
SReclaimable: 167716 kB
SUnreclaim: 34552 kB
KernelStack: 4656 kB
PageTables: 15628 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 10220964 kB
Committed_AS: 14736532 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 55296 kB
VmallocChunk: 34359650952 kB
HardwareCorrupted: 0 kB
AnonHugePages: 3633152 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
DirectMap4k: 6144 kB
DirectMap2M: 16766976 kB
=DRIVEINFO
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_xen6whdatabase-lv_root
33G 2.2G 29G 7% /
tmpfs 7.8G 0 7.8G 0% /dev/shm
/dev/sda1 485M 33M 427M 8% /boot
/dev/sdb 296G 6.1G 275G 3% /var/lib/mysql
192.168.101.15:/var/lib/mysql/DataWareHouse-Outfiles/
591G 530G 32G 95% /var/lib/mysql/DataWareHouse-Outfiles
# Query: show table status;
Rows = 132
Auto_increment = 232
Check_time =
Data_length = 16384
Row_format = Compact
Checksum =
Update_time =
Engine = InnoDB
Create_options =
Comment =
Index_length = 16384
Data_free = 0
Avg_row_length = 124
Version = 10
Create_time = 2012-11-06 09:50:08
Max_data_length = 0
Name = QT213_agents
Collation = latin1_swedish_ci
# Query: explain select count(id) from QT213_ldusage;
Extra = Using index
key_len = 4
ref =
table = QT213_ldusage
rows = 19855689
key = PRIMARY
select_type = SIMPLE
possible_keys =
id = 1
type = index
# Query: flush status;
# Query: select count(id) from QT213_ldusage;
# Query: show status like 'Handler%';
Handler_commit = 1
Handler_delete = 0
Handler_discover = 0
Handler_prepare = 0
Handler_read_first = 1
Handler_read_key = 1
Handler_read_last = 0
Handler_read_next = 19927302
Handler_read_prev = 0
Handler_read_rnd = 0
Handler_read_rnd_next = 0
Handler_rollback = 0
Handler_savepoint = 0
Handler_savepoint_rollback = 0
Handler_update = 0
Handler_write = 0
# Query: show variables;
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 = 65536
binlog_direct_non_transactional_updates = OFF
binlog_format = STATEMENT
binlog_stmt_cache_size = 32768
bulk_insert_buffer_size = 1073741824
character_set_client = latin1
character_set_connection = latin1
character_set_database = latin1
character_set_filesystem = binary
character_set_results = latin1
character_set_server = latin1
character_set_system = utf8
character_sets_dir = /usr/share/mysql/charsets/
collation_connection = latin1_swedish_ci
collation_database = latin1_swedish_ci
collation_server = latin1_swedish_ci
completion_type = NO_CHAIN
concurrent_insert = ALWAYS
connect_timeout = 30
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 = 1
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/Xen6-WH-Database.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
hostname = Xen6-WH-Database
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 = 4194304
innodb_autoextend_increment = 8
innodb_autoinc_lock_mode = 1
innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 12884901888
innodb_change_buffering = all
innodb_checksums = ON
innodb_commit_concurrency = 16
innodb_concurrency_tickets = 500
innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend
innodb_data_home_dir =
innodb_doublewrite = OFF
innodb_fast_shutdown = 1
innodb_file_format = Antelope
innodb_file_format_check = ON
innodb_file_format_max = Antelope
innodb_file_per_table = ON
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DSYNC
innodb_force_load_corrupted = OFF
innodb_force_recovery = 0
innodb_io_capacity = 200
innodb_large_prefix = OFF
innodb_lock_wait_timeout = 300
innodb_locks_unsafe_for_binlog = OFF
innodb_log_buffer_size = 134217728
innodb_log_file_size = 268435456
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_purge_batch_size = 20
innodb_purge_threads = 0
innodb_random_read_ahead = OFF
innodb_read_ahead_threshold = 56
innodb_read_io_threads = 16
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 = OFF
innodb_sync_spin_loops = 30
innodb_table_locks = ON
innodb_thread_concurrency = 16
innodb_thread_sleep_delay = 10000
innodb_use_native_aio = ON
innodb_use_sys_malloc = ON
innodb_version = 1.1.8
innodb_write_io_threads = 16
insert_id = 0
interactive_timeout = 28800
join_buffer_size = 1048576
keep_files_on_create = OFF
key_buffer_size = 1048576
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/lib/mysql/Xen6-WH-Database.err
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 = 1048576
max_binlog_cache_size = 18446744073709547520
max_binlog_size = 268435456
max_binlog_stmt_cache_size = 18446744073709547520
max_connect_errors = 100
max_connections = 2000
max_delayed_threads = 20
max_error_count = 64
max_heap_table_size = 1073741824
max_insert_delayed_threads = 20
max_join_size = 18446744073709551615
max_length_for_sort_data = 1024
max_long_data_size = 1048576
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 = 268435456
myisam_mmap_size = 18446744073709551615
myisam_recover_options = OFF
myisam_repair_threads = 1
myisam_sort_buffer_size = 134217728
myisam_stats_method = nulls_unequal
myisam_use_mmap = OFF
net_buffer_length = 16384
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
new = OFF
old = OFF
old_alter_table = OFF
old_passwords = OFF
open_files_limit = 10000
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/lib/mysql/Xen6-WH-Database.pid
plugin_dir = /usr/lib64/mysql/plugin/
port = 3306
preload_buffer_size = 32768
profiling = OFF
profiling_history_size = 15
protocol_version = 10
proxy_user =
pseudo_thread_id = 847
query_alloc_block_size = 8192
query_cache_limit = 524288
query_cache_min_res_unit = 4096
query_cache_size = 33554432
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 = 1048576
read_only = OFF
read_rnd_buffer_size = 1048576
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_networking = OFF
skip_show_database = OFF
slave_compressed_protocol = OFF
slave_exec_mode = STRICT
slave_load_tmpdir = /tmp
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/Xen6-WH-Database-slow.log
socket = /var/lib/mysql/mysql.sock
sort_buffer_size = 1048576
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 = EST
table_definition_cache = 400
table_open_cache = 400
thread_cache_size = 100
thread_concurrency = 1
thread_handling = one-thread-per-connection
thread_stack = 262144
time_format = %H:%i:%s
time_zone = SYSTEM
timed_mutexes = OFF
timestamp = 1352220621
tmp_table_size = 1073741824
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.25a
version_comment = MySQL Community Server (GPL)
version_compile_machine = x86_64
version_compile_os = Linux
wait_timeout = 28800
warning_count = 0

로그인 후 댓글쓰기가 가능합니다.

?

List of Articles
번호 분류 제목 날짜 조회 수
51 Server 아파치 httpd.conf 재시작 없이 설정 적용하기 2016.03.26 481
50 Server [아파치 error.log] Fatal Error Unable to reattach to base address file 2016.03.25 459
49 Server MySQL 5.6 my.cnf 글로벌 변수인지 세션변수인지 확인 2016.03.24 368
48 Server 아파치 캐시 설정 이후 잦은 아파치 자동멈춤 현상 원인분석 3 2016.03.23 528
47 Server APACHE PHP 에러 잡기 2016.03.23 1908
46 Server 서버 최적화 시키기(APACHE, PHP, MYSQL, XE 압축&캐싱&버퍼) 3 file 2016.03.18 1819
45 Server 아파치 모듈 mod_deflate 압축하기 1 2016.03.17 219
44 Server [Apache] mod_expires .htaccess을 수정하여 브라우저 캐싱하기 2016.03.16 490
43 Server 윈도우 time_wait 없애기 2016.03.08 314
42 Server CSS, JS파일, gzip으로 한꺼번에 압축해서 전송하기 2016.03.08 227
41 Server PHP Opcache 적용 전과 후의 비교 1 2016.03.07 295
40 Server PHP Opcache 속도개선 시키기 2016.03.07 486
39 Server 아파치 MPM 모드와 nginx 비교(apache 2.4 vs nginx) 2016.03.05 1627
38 Server PHP 가속기 secret 2016.03.05 10
37 Server XE성능 캐시활용으로 속도 100배 개선하기(Varnish 캐시서버 + XE(+패치) + nginx(혹은 아파치) / Wincache) file 2016.03.04 249
36 Server WAPM에서 Database 접속 속도 개선하기 2016.03.03 120
35 Server MySQL - 속도저하에 따른 웹페이지 접속 지연 2016.03.03 107
34 Server 아파치 ab 로 성능테스트 하기 2016.02.22 10627
33 Server 8기가 램에 맞는 Mysql config 셋팅 값 1 2016.02.22 775
32 Server 오토셋과 apmsetup file 2016.02.17 767
Board Pagination Prev 1 2 3 4 5 Next
/ 5

http://urin79.com

우린친구블로그

sketchbook5, 스케치북5

sketchbook5, 스케치북5

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소