|
안녕하세요,
여기저기 자료를 뒤지고 열심히 분석을 해 봤지만 도저히 답이 안나와서..
조언을 부탁드리기 위해 글을 올립니다.
다름이 아니라,
DB사용이 많은 것 같아서, Web과 DB서버를 분리시켜 운영중인데요.
분리하고 나면 성능이 더 나아져야 하는데, 그렇지가 못한 것 같습니다.
접속자가 엄청 많은 것 같지도 않은데..
데몬이 많이 뜰 때는, 명령어 응답이 엄청나게 느려질 정도입니다.
이런 때는 processlist 를 확인하면, locking이 걸린 프로세스가 무척 많은 편이구요.
제가 서버 설정을 잘못해 준 것인가요??
조언을 주시기에 참고가 될 만한
접속이 많이 몰려 있을 때의 DB서버 모니터링 값을 정리했습니다..
$ mysqladmin extended-status;
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 27 |
| Aborted_connects | 41 |
| Bytes_received | 17787278 |
| Bytes_sent | 1193535438 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 71056 |
| 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 | 0 |
| Com_delete | 44 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 3618 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 19 |
| 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_select | 137285 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_stat | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 14 |
| Com_show_slave_stat | 0 |
| Com_show_status | 1 |
| Com_show_tables | 0 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 19 |
| Com_update | 24850 |
| Connections | 64634 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 5998 |
| Created_tmp_files | 1492 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 44 |
| Handler_read_first | 45 |
| Handler_read_key | 89819 |
| Handler_read_next | 1271525 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 17128414 |
| Handler_read_rnd_next | 1495374369 |
| Handler_update | 24570 |
| Handler_write | 209323 |
| Key_blocks_used | 1224 |
| Key_read_requests | 790872 |
| Key_reads | 1172 |
| Key_write_requests | 25974 |
| Key_writes | 25096 |
| Max_used_connections | 250 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 256 |
| Open_files | 263 |
| Open_streams | 0 |
| Opened_tables | 715 |
| Questions | 301245 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 84 |
| Select_range_check | 0 |
| Select_scan | 41229 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 803 |
| Slow_queries | 3588 |
| Sort_merge_passes | 746 |
| Sort_range | 31252 |
| Sort_rows | 17128635 |
| Sort_scan | 34547 |
| Table_locks_immediate | 133436 |
| Table_locks_waited | 32281 |
| Threads_cached | 0 |
| Threads_created | 64633 |
| Threads_connected | 235 |
| Threads_running | 134 |
| Uptime | 119227 |
+--------------------------+------------+
$ mysqladmin variables;
+---------------------------------+---------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------+
| back_log | 50 |
| basedir | /usr/local/mysql/ |
| binlog_cache_size | 32768 |
| character_set | euc_kr |
| character_sets | euc_kr ..... (길어서 생략)|
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | /usr/local/mysql/var/ |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| have_bdb | NO |
| have_gemini | NO |
| have_innodb | NO |
| have_isam | YES |
| have_raid | NO |
| have_openssl | NO |
| init_file | |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 67104768 |
| language | /usr/local/mysql/share/mysql/english|
| large_files_support | ON |
| locked_in_memory | OFF |
| log | ON |
| log_update | OFF |
| log_bin | ON |
| log_slave_updates | OFF |
| log_long_queries | ON |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 250 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 256 |
| myisam_max_sort_file_size | 2047 |
| myisam_recover_options | 0 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 7168 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| open_files_limit | 0 |
| pid_file | /usr/local/mysql/var/db.pid | 3306|
| protocol_version | 10 |
| record_buffer | 131072 |
| record_rnd_buffer | 131072 |
| query_buffer_size | 0 |
| safe_show_database | OFF |
| server_id | 1 |
| slave_net_timeout | 3600 |
| skip_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer | 524280 |
| sql_mode | 0 |
| table_cache | 256 |
| table_type | MYISAM |
| thread_cache_size | 0 |
| thread_stack | 65536 |
| transaction_isolation | READ-COMMITTED |
| timezone | KST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| version | 3.23.49-log |
| wait_timeout | 28800 |
+---------------------------------+---------------------------+
slow query를 좀 살펴보자면, 대부분 \select * from\ 구문인데
쿼리 실행방식을 알기 위해서는 explain 명령으로 돌려보라고 하는데요,
어떻게 사용을 하는 것인지요?
또, 최적화 관련한 문서를 보면 \select * from\ 쿼리 대신,
다른 쿼리로 변경을 해서 쓰라고 하더군요.
뭘로 써야 한다는 것인지...
조언을 좀 부탁드립니다..
꾸벅~
|
