어디까지나 참고 사항이다.
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 |
