데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Innodb와 MyISAM을 같이 사용합니다.

MyISAM은 슬레이브로서 리플리케이션 되는 셀렉트 전용DB이구요, InnoDB는 잦은 인서트가 일어나는 DB입니다.

서버 OS는 CentOS 64Bit, CPU는 쿼드코어2개(하이퍼쓰레드적용, 16쓰레드), 램은 12기가입니다.

시스템에서 따로 메모리가 2기가정도 필요해서 innodb_buffer_pool_size를 6GB -> 4.5GB로 낮췄는데도, free메모리가 얼마 안남네요.

Innodb를 처음 사용해봐서 적당한 설정값을 모르겠네요.

계속 튜닝중인데요, 적당한 설정인지 조언 부탁드립니다.

[testdb /db/data # ] free
             total       used       free     shared    buffers     cached
Mem:      12290988   12232852      58136          0     163224    7899344
-/+ buffers/cache:    4170284    8120704
Swap:     13309844       6884   13302960
[testdb /db/data # ] cat /proc/meminfo
MemTotal:     12290988 kB
MemFree:         58628 kB
Buffers:        163272 kB
Cached:        7899464 kB
SwapCached:        632 kB
Active:        4410400 kB
Inactive:      6732000 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:     12290988 kB
LowFree:         58628 kB
SwapTotal:    13309844 kB
SwapFree:     13302960 kB
Dirty:            2124 kB
Writeback:           4 kB
AnonPages:     3078984 kB
Mapped:          14100 kB
Slab:           303192 kB
PageTables:      11016 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit: 19455336 kB
Committed_AS: 9874432 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    303292 kB
VmallocChunk: 34359434999 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB
[testdb /db/data # ] top
top - 06:41:23 up 21 days, 13:17, 4 users, load average: 0.10, 0.06, 0.01
Tasks: 364 total,   1 running, 363 sleeping,   0 stopped,   0 zombie
Cpu(s): 0.8%us, 1.4%sy, 0.0%ni, 97.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 12290988k total, 12232088k used,    58900k free,   163308k buffers
Swap: 13309844k total,     6884k used, 13302960k free, 7899580k cached

PID USER      PR NI VIRT RES SHR S %CPU %MEM    TIME+ COMMAND
22464 mysql     15   0 9675m 2.9g 4640 S 0.0 24.8   0:03.13 mysqld
22466 mysql     18   0 9675m 2.9g 4640 S 0.0 24.8   0:00.00 mysqld
22467 mysql     16   0 9675m 2.9g 4640 S 0.0 24.8   0:00.00 mysqld
22468 mysql     15   0 9675m 2.9g 4640 S 0.0 24.8   0:07.40 mysqld
22469 mysql     15   0 9675m 2.9g 4640 S 0.0 24.8   0:00.61 mysqld

+-----------------------------------------+--------------------------------------------+
| Variable_name                           | Value                                      |
+-----------------------------------------+--------------------------------------------+
| auto_increment_increment                | 1                                          |
| auto_increment_offset                   | 1                                          |
| automatic_sp_privileges                 | ON                                         |
| back_log                                | 128                                        |
| basedir                                 | /                                          |
| binlog_cache_size                       | 32768                                      |
| bulk_insert_buffer_size                 | 8388608                                    |
| character_set_client                    | euckr                                      |
| character_set_connection                | euckr                                      |
| character_set_database                  | euckr                                      |
| character_set_filesystem                | binary                                     |
| character_set_results                   | euckr                                      |
| character_set_server                    | euckr                                      |
| character_set_system                    | utf8                                       |
| character_sets_dir                      | /usr/share/mysql/charsets/                 |
| collation_connection                    | euckr_korean_ci                            |
| collation_database                      | euckr_korean_ci                            |
| collation_server                        | euckr_korean_ci                            |
| completion_type                         | 0                                          |
| concurrent_insert                       | 1                                          |
| connect_timeout                         | 10                                         |
| datadir                                 | /db/data/                                  |
| date_format                             | %Y-%m-%d                                   |
| datetime_format                         | %Y-%m-%d %H:%i:%s                          |
| default_week_format                     | 0                                          |
| delay_key_write                         | ON                                         |
| delayed_insert_limit                    | 100                                        |
| delayed_insert_timeout                  | 300                                        |
| delayed_queue_size                      | 1000                                       |
| div_precision_increment                 | 4                                          |
| keep_files_on_create                    | OFF                                        |
| engine_condition_pushdown               | OFF                                        |
| expire_logs_days                        | 0                                          |
| flush                                   | OFF                                        |
| flush_time                              | 0                                          |
| ft_boolean_syntax                       | + -><()~*:""&|                             |
| ft_max_word_len                         | 84                                         |
| ft_min_word_len                         | 4                                          |
| ft_query_expansion_limit                | 20                                         |
| ft_stopword_file                        | (built-in)                                 |
| group_concat_max_len                    | 1024                                       |
| have_archive                            | YES                                        |
| have_bdb                                | NO                                         |
| have_blackhole_engine                   | YES                                        |
| have_compress                           | YES                                        |
| have_community_features                 | YES                                        |
| have_profiling                          | YES                                        |
| have_crypt                              | YES                                        |
| have_csv                                | YES                                        |
| have_dynamic_loading                    | YES                                        |
| have_example_engine                     | YES                                        |
| have_federated_engine                   | YES                                        |
| have_geometry                           | YES                                        |
| have_innodb                             | YES                                        |
| have_isam                               | NO                                         |
| have_merge_engine                       | YES                                        |
| have_ndbcluster                         | DISABLED                                   |
| have_openssl                            | NO                                         |
| have_ssl                                | NO                                         |
| have_query_cache                        | YES                                        |
| have_raid                               | NO                                         |
| have_rtree_keys                         | YES                                        |
| have_symlink                            | YES                                        |
| hostname                                | t.co.kr                     |
| init_connect                            | SET collation_connection = euckr_korean_ci |
| init_file                               |                                            |
| init_slave                              |                                            |
| innodb_additional_mem_pool_size         | 33554432                                   |
| innodb_autoextend_increment             | 8                                          |
| innodb_buffer_pool_awe_mem_mb           | 0                                          |
| innodb_buffer_pool_size                 | 4831838208                                 |
| innodb_checksums                        | ON                                         |
| innodb_commit_concurrency               | 0                                          |
| innodb_concurrency_tickets              | 500                                        |
| innodb_data_file_path                   | ibdata1:10M:autoextend                     |
| innodb_data_home_dir                    |                                            |
| innodb_adaptive_hash_index              | ON                                         |
| innodb_doublewrite                      | ON                                         |
| innodb_fast_shutdown                    | 1                                          |
| innodb_file_io_threads                  | 4                                          |
| innodb_file_per_table                   | OFF                                        |
| innodb_flush_log_at_trx_commit          | 1                                          |
| innodb_flush_method                     |                                            |
| innodb_force_recovery                   | 0                                          |
| innodb_lock_wait_timeout                | 50                                         |
| innodb_locks_unsafe_for_binlog          | OFF                                        |
| innodb_log_arch_dir                     |                                            |
| innodb_log_archive                      | OFF                                        |
| innodb_log_buffer_size                  | 67108864                                   |
| innodb_log_file_size                    | 943718400                                  |
| 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_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                                         |
| interactive_timeout                     | 28800                                      |
| join_buffer_size                        | 8388608                                    |
| key_buffer_size                         | 4294967296                                 |
| key_cache_age_threshold                 | 300                                        |
| key_cache_block_size                    | 1024                                       |
| key_cache_division_limit                | 100                                        |
| language                                | /usr/share/mysql/english/                  |
| large_files_support                     | ON                                         |
| large_page_size                         | 0                                          |
| large_pages                             | OFF                                        |
| lc_time_names                           | en_US                                      |
| license                                 | GPL                                        |
| local_infile                            | ON                                         |
| locked_in_memory                        | OFF                                        |
| log                                     | ON                                         |
| log_bin                                 | OFF                                        |
| log_bin_trust_function_creators         | OFF                                        |
| log_error                               | /db/data/rwintradb.err                     |
| log_queries_not_using_indexes           | OFF                                        |
| log_slave_updates                       | OFF                                        |
| log_slow_queries                        | ON                                         |
| log_warnings                            | 1                                          |
| long_query_time                         | 10                                         |
| low_priority_updates                    | ON                                         |
| lower_case_file_system                  | OFF                                        |
| lower_case_table_names                  | 0                                          |
| max_allowed_packet                      | 33554432                                   |
| max_binlog_cache_size                   | 18446744073709547520                       |
| max_binlog_size                         | 1073741824                                 |
| max_connect_errors                      | 10                                         |
| max_connections                         | 1024                                       |
| 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_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                       |
| multi_range_count                       | 256                                        |
| myisam_data_pointer_size                | 6                                          |
| myisam_max_sort_file_size               | 9223372036853727232                        |
| myisam_recover_options                  | OFF                                        |
| myisam_repair_threads                   | 1                                          |
| myisam_sort_buffer_size                 | 134217728                                  |
| myisam_stats_method                     | nulls_unequal                              |
| ndb_autoincrement_prefetch_sz           | 1                                          |
| ndb_force_send                          | ON                                         |
| ndb_use_exact_count                     | ON                                         |
| ndb_use_transactions                    | ON                                         |
| ndb_cache_check_time                    | 0                                          |
| ndb_connectstring                       |                                            |
| net_buffer_length                       | 32768                                      |
| net_read_timeout                        | 30                                         |
| net_retry_count                         | 10                                         |
| net_write_timeout                       | 60                                         |
| new                                     | OFF                                        |
| old_passwords                           | ON                                         |
| open_files_limit                        | 33802                                      |
| optimizer_prune_level                   | 1                                          |
| optimizer_search_depth                  | 62                                         |
| pid_file                                | /db/data/test.co.kr.pid        |
| plugin_dir                              |                                            |
| port                                    | 3306                                       |
| preload_buffer_size                     | 32768                                      |
| profiling                               | OFF                                        |
| profiling_history_size                  | 15                                         |
| protocol_version                        | 10                                         |
| query_alloc_block_size                  | 8192                                       |
| query_cache_limit                       | 2097152                                    |
| query_cache_min_res_unit                | 4096                                       |
| query_cache_size                        | 67108864                                   |
| query_cache_type                        | ON                                         |
| query_cache_wlock_invalidate            | OFF                                        |
| query_prealloc_size                     | 8192                                       |
| range_alloc_block_size                  | 4096                                       |
| read_buffer_size                        | 67108864                                   |
| read_only                               | OFF                                        |
| read_rnd_buffer_size                    | 8388608                                    |
| relay_log                               | rwintradb-relay-bin                        |
| relay_log_index                         |                                            |
| relay_log_info_file                     | relay-log.info                             |
| relay_log_purge                         | ON                                         |
| relay_log_space_limit                   | 0                                          |
| rpl_recovery_rank                       | 0                                          |
| secure_auth                             | OFF                                        |
| secure_file_priv                        |                                            |
| server_id                               | 3                                          |
| skip_external_locking                   | ON                                         |
| skip_networking                         | OFF                                        |
| skip_show_database                      | OFF                                        |
| slave_compressed_protocol               | OFF                                        |
| slave_load_tmpdir                       | /tmp/                                      |
| slave_net_timeout                       | 3600                                       |
| slave_skip_errors                       | OFF                                        |
| slave_transaction_retries               | 10                                         |
| slow_launch_time                        | 2                                          |
| socket                                  | /var/lib/mysql/mysql.sock                  |
| sort_buffer_size                        | 4194304                                    |
| sql_big_selects                         | ON                                         |
| sql_mode                                |                                            |
| sql_notes                               | ON                                         |
| sql_warnings                            | OFF                                        |
| ssl_ca                                  |                                            |
| ssl_capath                              |                                            |
| ssl_cert                                |                                            |
| ssl_cipher                              |                                            |
| ssl_key                                 |                                            |
| storage_engine                          | InnoDB                                     |
| sync_binlog                             | 0                                          |
| sync_frm                                | ON                                         |
| system_time_zone                        | KST                                        |
| table_cache                             | 16384                                      |
| table_lock_wait_timeout                 | 50                                         |
| table_type                              | InnoDB                                     |
| thread_cache_size                       | 1024                                       |
| thread_stack                            | 262144                                     |
| time_format                             | %H:%i:%s                                   |
| time_zone                               | SYSTEM                                     |
| timed_mutexes                           | OFF                                        |
| tmp_table_size                          | 134217728                                  |
| tmpdir                                  | /tmp/                                      |
| transaction_alloc_block_size            | 8192                                       |
| transaction_prealloc_size               | 4096                                       |
| tx_isolation                            | REPEATABLE-READ                            |
| updatable_views_with_limit              | YES                                        |
| version                                 | 5.0.85-community-log                       |
| version_comment                         | MySQL Community Edition (GPL)              |
| version_compile_machine                 | x86_64                                     |
| version_compile_os                      | unknown-linux-gnu                          |
| wait_timeout                            | 600                                        |
+-----------------------------------------+--------------------------------------------+

[Top]

No.	제목	작성자	작성일	조회
29336	특정 필드가 숫자로 표현되는데 특정 자리수 이상을... [1]	초보김대리	2009-09-28	6326
29335	두개의 디비시스템에서 트리거 연동이 가능할까요?	김선아	2009-09-25	6290
29334	쿼리 분석좀 해주세요 [1]	최동성	2009-09-24	6062
29333	적당한 설정인지 조언 부탁드립니다.	박찬영	2009-09-24	6754
29326	프로시저 질문입니다. [1]	흥흥흥	2009-09-23	5749
29325	연속된 값을 찾는게 가능한가요?? [3]	Deck	2009-09-22	7414
29312	숫자를 합치려면 어떻게 하나요? [2]	Deck	2009-09-18	6307

작업시간: 0.018초, 이곳 서비스는
PostgreSQL v16.2로 자료를 관리합니다