|
서버 사양은 redhat 7.3(2.4.18-3smp), Intel Xeon 2.4Dual, Ram 2G이고, Replication Master와 Slave로 구성하여 사용중입니다.
웹서버는 JSP로 Apache + Resin으로 사용하고 있으며, Connection pool은 Max값이 150이며, 웹서버가 3대입니다.
나타나는 현상은 사용자가 300명 정도 접속하면 mysqld 데몬의 CPU점유율이 증가하여,
700명정도 증가하면 DB서버에 접속하기조차 힘들정도로 버벅거립니다.
아래 값들은 현재 DB서버의 상태값들입니다. 현재 접속자 500명정도의 수치입니다.
고수님들 무엇이든 바꿔야 할게 있다면 말씀해주세요..
$ mysqladmin -u root -p status
Uptime: 262408 Threads: 26 Questions: 3381230 Slow queries: 27345 Opens: 6009 Flush tables: 1 Open tables: 768 Queries per second avg: 12.885
$ mysqladmin -u root -p extended-status
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 17481 |
| Aborted_connects | 196 |
| Bytes_received | 267669829 |
| Bytes_sent | 990282699 |
| Com_admin_commands | 2 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 74871 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 26 |
| Com_delete | 115 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 26 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 195365 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 1203575 |
| Com_set_option | 74835 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 1 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 26 |
| Com_show_slave_hosts | 1 |
| Com_show_slave_status | 0 |
| Com_show_status | 17 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 3 |
| Com_show_variables | 74822 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 1101073 |
| Connections | 75105 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 21 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 130 |
| Handler_read_first | 48 |
| Handler_read_key | 1219145 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 40816 |
| Handler_read_rnd_next | 1488971926 |
| Handler_rollback | 0 |
| Handler_update | 1046563 |
| Handler_write | 195631 |
| Key_blocks_used | 1707 |
| Key_read_requests | 2140028 |
| Key_reads | 1339 |
| Key_write_requests | 10640 |
| Key_writes | 10637 |
| Max_used_connections | 473 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 768 |
| Open_files | 818 |
| Open_streams | 0 |
| Opened_tables | 6009 |
| Questions | 3385447 |
| Qcache_queries_in_cache | 10334 |
| Qcache_inserts | 807190 |
| Qcache_hits | 462547 |
| Qcache_lowmem_prunes | 51474 |
| Qcache_not_cached | 397248 |
| Qcache_free_memory | 39389920 |
| Qcache_free_blocks | 4196 |
| Qcache_total_blocks | 24922 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 943187 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 648 |
| Slow_queries | 27345 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 40816 |
| Sort_scan | 3276 |
| Table_locks_immediate | 2065472 |
| Table_locks_waited | 361011 |
| Threads_cached | 6 |
| Threads_created | 29781 |
| Threads_connected | 22 |
| Threads_running | 4 |
| Uptime | 262587 |
+--------------------------+------------+
$ mysqladmin -u root -p variables
+---------------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------+
| back_log | 50 |
| basedir | /usr/local/mysql_4.0.17/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set | euc_kr |
| character_sets | euc_kr dec8 dos german1 hp8 koi8_ru latin1 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| convert_character_set | |
| datadir | /disk2/datafiles/ |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_min_word_len | 4 |
| ft_max_word_len | 254 |
| ft_max_word_len_for_sort | 20 |
| ft_stopword_file | (built-in) |
| have_bdb | NO |
| have_crypt | YES |
| have_innodb | YES |
| have_isam | YES |
| have_raid | NO |
| have_symlink | YES |
| have_openssl | NO |
| have_query_cache | YES |
| init_file | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| innodb_log_arch_dir | ./ |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| innodb_max_dirty_pages_pct | 90 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 536870912 |
| language | /usr/local/mysql_4.0.17/share/mysql/english|
| large_files_support | ON |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | ON |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | OFF |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 500 |
| max_connect_errors | 1000000 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 268435456 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_repair_threads | 1 |
| myisam_recover_options | OFF |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 2510 |
| pid_file | /disk2/datafiles/mysql.pid |
| log_error | |
| port | 3306 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_size | 50331648 |
| query_cache_type | ON |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 3141632 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 1 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 3145720 |
| sql_mode | 0 |
| table_cache | 768 |
| table_type | MYISAM |
| thread_cache_size | 8 |
| thread_stack | 196608 |
| tx_isolation | REPEATABLE-READ |
| timezone | KST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| version | 4.0.17-log |
| version_comment | Source distribution |
| wait_timeout | 28800 |
+---------------------------------+--------------------------------------------+
$ vmstat 5 10
procs memory swap io system cpu
r b w swpd free buff cache si so bi bo in cs us sy id
0 0 0 0 1587180 78736 186036 0 0 0 4 3 16 16 7 8
0 0 0 0 1587172 78740 186036 0 0 0 21 146 49 11 8 81
1 0 0 0 1584092 78744 186036 0 0 0 23 151 77 12 8 80
3 0 0 0 1577940 78744 186036 0 0 0 22 184 91 22 20 58
3 0 0 0 1581012 78744 186040 0 0 0 26 184 92 25 7 67
5 0 0 0 1574808 78744 186040 0 0 0 24 186 100 32 19 48
6 0 0 0 1574804 78744 186044 0 0 0 21 179 80 37 10 53
0 0 0 0 1587108 78744 186044 0 0 0 27 188 89 28 12 60
2 0 0 0 1584032 78744 186044 0 0 0 25 159 65 17 8 76
1 0 0 0 1584024 78744 186048 0 0 0 26 191 101 39 17 44
$ top
3:10pm up 3 days, 14:47, 1 user, load average: 3.16, 6.79, 5.29
98 processes: 93 sleeping, 5 running, 0 zombie, 0 stopped
CPU0 states: 55.0% user, 39.0% system, 0.0% nice, 5.0% idle
CPU1 states: 76.0% user, 14.0% system, 0.0% nice, 9.0% idle
CPU2 states: 88.0% user, 8.1% system, 0.0% nice, 2.1% idle
CPU3 states: 49.0% user, 43.1% system, 0.0% nice, 6.1% idle
Mem: 2065156K av, 485856K used, 1579300K free, 0K shrd, 79184K buff
Swap: 2097136K av, 0K used, 2097136K free 186968K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
4032 mysql 15 0 150M 150M 2984 S 99.9 7.4 18:53 mysqld
8351 treasure 17 0 1076 1076 828 R 31.7 0.0 0:00 top
1 root 15 0 480 480 420 S 0.0 0.0 0:06 init
2 root 15 0 0 0 0 SW 0.0 0.0 0:00 keventd
3 root 34 19 0 0 0 SWN 0.0 0.0 0:02 ksoftirqd_CPU0
4 root 34 19 0 0 0 SWN 0.0 0.0 0:00 ksoftirqd_CPU1
5 root 34 19 0 0 0 SWN 0.0 0.0 0:00 ksoftirqd_CPU2 |
