|
mysql 5.0대를 사용하고 있습니다.
조그만한 회사라 제가 튜닝도 하고 있는데 당최 알 수 가 없습니다.
모든 쿼리가 인덱스를 타는지 살펴 봐야 하겠는데 엄두가 안나서 병행하려고 합니다.
컴터는 쿼드쿼어 9400
램 4기가
윈도xp에서 돌아가고 있습니다.
mysql5.0 래퍼런스 메뉴얼 보고 열공하고 있는데 뜻을 잘 모르겠습니다...
혹시 방법만이라도
알려 주심 감하겠습니다. 수정할때 명령어도...^^;;
메인테이블이 한 4-5개 정도 되고 해당 테이블마다 데이타는 약 40만개에서 80만개 들어있습니다.
============================================================================================
data 폴더 및에 에러파일에 이렇게 있습니다.
서버를 수동으로 재시작한적이 없는데 셧다운됐다가 다시 살아나는건지?로그를 보면
그리고 Progress in percents <---이건 뭐하는 건가요? 궁금합니다. 잠깐 잠깐 프로그램이 정지하는 듯한 원인이 혹 이것 때문이지요?
==============================================================================================================
InnoDB: Trx id counter is 0 24528384
100505 18:02:22 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Starting in background the rollback of uncommitted transactions
100505 18:02:22 InnoDB: Rolling back trx with id 0 24528081, 1 rows to undo
100505 18:02:22 InnoDB: Started; log sequence number 0 4125012371
InnoDB: Rolling back of trx id 0 24528081 completed
100505 18:02:23 InnoDB: Rollback of non-prepared transactions completed
100505 18:02:23 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.22-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL)
100505 19:27:58 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Normal shutdown
.
.
.
.
100505 19:28:00 InnoDB: Starting shutdown...
100505 19:28:03 InnoDB: Shutdown completed; log sequence number 0 4128812291
100505 19:28:03 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete
100505 19:30:41 InnoDB: Started; log sequence number 0 4128812291
100505 19:30:41 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.22-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL)
============================================================================================
data 폴더 아래에 ibdata1, ib_logfileX 이름으로 파일이 있습니다.
로그파일인거 같은데 로그파일 확인할 수 있는 방법이 무었인지요?
그리고 파일이 커서 그런데 필요 없으면 지워도 되는지요? 그냥 지워지지 않는거 같은데 어떻게 지워야 하나요?
========================================================================================
Variable_name,Value
auto_increment_increment,1
auto_increment_offset,1
automatic_sp_privileges,ON
back_log,50
basedir,C:\Program Files\MySQL\MySQL Server 5.0\
binlog_cache_size,32768
bulk_insert_buffer_size,8388608
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,C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\
collation_connection,latin1_swedish_ci
collation_database,latin1_swedish_ci
collation_server,latin1_swedish_ci
completion_type,0
concurrent_insert,1
connect_timeout,5
datadir,C:\Program Files\MySQL\MySQL Server 5.0\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
engine_condition_pushdown,OFF
expire_logs_days,0
flush,OFF
flush_time,1800
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,NO
have_compress,YES
have_crypt,NO
have_csv,NO
have_example_engine,NO
have_federated_engine,NO
have_geometry,YES
have_innodb,YES
have_isam,NO
have_ndbcluster,NO
have_openssl,DISABLED
have_query_cache,YES
have_raid,NO
have_rtree_keys,YES
have_symlink,YES
init_connect,
init_file,
init_slave,
innodb_additional_mem_pool_size,26214400
innodb_autoextend_increment,8
innodb_buffer_pool_awe_mem_mb,0
innodb_buffer_pool_size,1228931072
innodb_checksums,ON
innodb_commit_concurrency,0
innodb_concurrency_tickets,500
innodb_data_file_path,ibdata1:10M:autoextend
innodb_data_home_dir,
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,12582912
innodb_log_file_size,614465536
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_support_xa,ON
innodb_sync_spin_loops,20
innodb_table_locks,ON
innodb_thread_concurrency,10
innodb_thread_sleep_delay,10000
interactive_timeout,28800
join_buffer_size,131072
key_buffer_size,33554432
key_cache_age_threshold,300
key_cache_block_size,1024
key_cache_division_limit,100
language,C:\Program Files\MySQL\MySQL Server 5.0\share\english\
large_files_support,ON
large_page_size,0
large_pages,OFF
license,GPL
local_infile,ON
log,OFF
log_bin,OFF
log_bin_trust_function_creators,OFF
log_error,.\LB-MANAGER.err
log_slave_updates,OFF
log_slow_queries,OFF
log_warnings,1
long_query_time,10
low_priority_updates,OFF
lower_case_file_system,OFF
lower_case_table_names,1
max_allowed_packet,1048576
max_binlog_cache_size,4294967295
max_binlog_size,1073741824
max_connect_errors,10
max_connections,200
max_delayed_threads,20
max_error_count,64
max_heap_table_size,16777216
max_insert_delayed_threads,20
max_join_size,4294967295
max_length_for_sort_data,1024
max_prepared_stmt_count,16382
max_relay_log_size,0
max_seeks_for_key,4294967295
max_sort_length,1024
max_sp_recursion_depth,0
max_tmp_tables,32
max_user_connections,0
max_write_lock_count,4294967295
multi_range_count,256
myisam_data_pointer_size,6
myisam_max_sort_file_size,107374182400
myisam_recover_options,OFF
myisam_repair_threads,1
myisam_sort_buffer_size,386924544
myisam_stats_method,nulls_unequal
named_pipe,OFF
net_buffer_length,16384
net_read_timeout,30
net_retry_count,10
net_write_timeout,60
new,OFF
old_passwords,OFF
open_files_limit,1010
optimizer_prune_level,1
optimizer_search_depth,62
pid_file,C:\Program Files\MySQL\MySQL Server 5.0\Data\LB-MANAGER.pid
prepared_stmt_count,0
port,3306
preload_buffer_size,32768
protocol_version,10
query_alloc_block_size,8192
query_cache_limit,1048576
query_cache_min_res_unit,4096
query_cache_size,16777216
query_cache_type,ON
query_cache_wlock_invalidate,OFF
query_prealloc_size,8192
range_alloc_block_size,2048
read_buffer_size,61440
read_only,OFF
read_rnd_buffer_size,258048
relay_log_purge,ON
relay_log_space_limit,0
rpl_recovery_rank,0
secure_auth,OFF
shared_memory,OFF
shared_memory_base_name,MYSQL
server_id,0
skip_external_locking,ON
skip_networking,OFF
skip_show_database,OFF
slave_compressed_protocol,OFF
slave_load_tmpdir,C:\WINDOWS\TEMP\
slave_net_timeout,3600
slave_skip_errors,OFF
slave_transaction_retries,10
slow_launch_time,2
sort_buffer_size,262136
sql_mode,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_notes,ON
sql_warnings,ON
storage_engine,InnoDB
sync_binlog,0
sync_frm,ON
system_time_zone,
table_cache,256
table_lock_wait_timeout,50
table_type,InnoDB
thread_cache_size,8
thread_stack,196608
time_format,%H:%i:%s
time_zone,SYSTEM
timed_mutexes,OFF
tmp_table_size,193986560
tmpdir,
transaction_alloc_block_size,8192
transaction_prealloc_size,4096
tx_isolation,REPEATABLE-READ
updatable_views_with_limit,YES
version,5.0.22-community-nt
version_comment,MySQL Community Edition (GPL)
version_compile_machine,ia32
version_compile_os,Win32
wait_timeout,28800
==================================================================================
Variable_name,Value
Aborted_clients,44
Aborted_connects,7
Binlog_cache_disk_use,0
Binlog_cache_use,0
Bytes_received,2251
Bytes_sent,51089
Com_admin_commands,26
Com_alter_db,0
Com_alter_table,0
Com_analyze,0
Com_backup_table,0
Com_begin,0
Com_change_db,1
Com_change_master,0
Com_check,0
Com_checksum,0
Com_commit,0
Com_create_db,0
Com_create_function,0
Com_create_index,0
Com_create_table,0
Com_dealloc_sql,0
Com_delete,0
Com_delete_multi,0
Com_do,0
Com_drop_db,0
Com_drop_function,0
Com_drop_index,0
Com_drop_table,0
Com_drop_user,0
Com_execute_sql,0
Com_flush,0
Com_grant,0
Com_ha_close,0
Com_ha_open,0
Com_ha_read,0
Com_help,0
Com_insert,0
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_preload_keys,0
Com_prepare_sql,0
Com_purge,0
Com_purge_before_date,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_revoke_all,0
Com_rollback,0
Com_savepoint,0
Com_select,7
Com_set_option,2
Com_show_binlog_events,0
Com_show_binlogs,0
Com_show_charsets,0
Com_show_collations,0
Com_show_column_types,0
Com_show_create_db,0
Com_show_create_table,0
Com_show_databases,1
Com_show_errors,0
Com_show_fields,6
Com_show_grants,0
Com_show_innodb_status,0
Com_show_keys,1
Com_show_logs,0
Com_show_master_status,0
Com_show_ndb_status,0
Com_show_new_master,0
Com_show_open_tables,0
Com_show_privileges,0
Com_show_processlist,0
Com_show_slave_hosts,0
Com_show_slave_status,0
Com_show_status,6
Com_show_storage_engines,0
Com_show_tables,6
Com_show_triggers,0
Com_show_variables,6
Com_show_warnings,0
Com_slave_start,0
Com_slave_stop,0
Com_stmt_close,0
Com_stmt_execute,0
Com_stmt_fetch,0
Com_stmt_prepare,0
Com_stmt_reset,0
Com_stmt_send_long_data,0
Com_truncate,0
Com_unlock_tables,0
Com_update,0
Com_update_multi,0
Com_xa_commit,0
Com_xa_end,0
Com_xa_prepare,0
Com_xa_recover,0
Com_xa_rollback,0
Com_xa_start,0
Compression,ON
Connections,118467
Created_tmp_disk_tables,6
Created_tmp_files,1892
Created_tmp_tables,27
Delayed_errors,0
Delayed_insert_threads,0
Delayed_writes,0
Flush_commands,1
Handler_commit,0
Handler_delete,0
Handler_discover,0
Handler_prepare,0
Handler_read_first,3
Handler_read_key,14
Handler_read_next,1198760
Handler_read_prev,0
Handler_read_rnd,0
Handler_read_rnd_next,2710
Handler_rollback,0
Handler_savepoint,0
Handler_savepoint_rollback,0
Handler_update,0
Handler_write,2814
Innodb_buffer_pool_pages_data,42245
Innodb_buffer_pool_pages_dirty,54
Innodb_buffer_pool_pages_flushed,290841
Innodb_buffer_pool_pages_free,31049
Innodb_buffer_pool_pages_latched,0
Innodb_buffer_pool_pages_misc,1714
Innodb_buffer_pool_pages_total,75008
Innodb_buffer_pool_read_ahead_rnd,5
Innodb_buffer_pool_read_ahead_seq,83
Innodb_buffer_pool_read_requests,115756749
Innodb_buffer_pool_reads,26750
Innodb_buffer_pool_wait_free,0
Innodb_buffer_pool_write_requests,11540449
Innodb_data_fsyncs,107430
Innodb_data_pending_fsyncs,0
Innodb_data_pending_reads,0
Innodb_data_pending_writes,0
Innodb_data_read,528158720
Innodb_data_reads,32113
Innodb_data_writes,384024
Innodb_data_written,1403468288
Innodb_dblwr_pages_written,290841
Innodb_dblwr_writes,14582
Innodb_log_waits,0
Innodb_log_write_requests,910618
Innodb_log_writes,69376
Innodb_os_log_fsyncs,78391
Innodb_os_log_pending_fsyncs,0
Innodb_os_log_pending_writes,0
Innodb_os_log_written,458565632
Innodb_page_size,16384
Innodb_pages_created,10142
Innodb_pages_read,32103
Innodb_pages_written,290841
Innodb_row_lock_current_waits,0
Innodb_row_lock_time,199
Innodb_row_lock_time_avg,99
Innodb_row_lock_time_max,99
Innodb_row_lock_waits,2
Innodb_rows_deleted,702579
Innodb_rows_inserted,868401
Innodb_rows_read,79332358
Innodb_rows_updated,24616
Key_blocks_not_flushed,0
Key_blocks_unused,28694
Key_blocks_used,2
Key_read_requests,4
Key_reads,4
Key_write_requests,0
Key_writes,0
Last_query_cost,10.499000
Max_used_connections,21
Not_flushed_delayed_rows,0
Open_files,0
Open_streams,0
Open_tables,14
Opened_tables,39
Qcache_free_blocks,549
Qcache_free_memory,12696136
Qcache_hits,9642
Qcache_inserts,119307
Qcache_lowmem_prunes,0
Qcache_not_cached,120669
Qcache_queries_in_cache,3327
Qcache_total_blocks,7234
Questions,432808
Rpl_status,NULL
Select_full_join,0
Select_full_range_join,0
Select_range,0
Select_range_check,0
Select_scan,30
Slave_open_temp_tables,0
Slave_retried_transactions,0
Slave_running,OFF
Slow_launch_threads,0
Slow_queries,0
Sort_merge_passes,0
Sort_range,0
Sort_rows,0
Sort_scan,0
Ssl_accept_renegotiates,0
Ssl_accepts,0
Ssl_callback_cache_hits,0
Ssl_cipher,
Ssl_cipher_list,
Ssl_client_connects,0
Ssl_connect_renegotiates,0
Ssl_ctx_verify_depth,0
Ssl_ctx_verify_mode,0
Ssl_default_timeout,0
Ssl_finished_accepts,0
Ssl_finished_connects,0
Ssl_session_cache_hits,0
Ssl_session_cache_misses,0
Ssl_session_cache_mode,NONE
Ssl_session_cache_overflows,0
Ssl_session_cache_size,0
Ssl_session_cache_timeouts,0
Ssl_sessions_reused,0
Ssl_used_session_cache_entries,0
Ssl_verify_depth,0
Ssl_verify_mode,0
Ssl_version,
Table_locks_immediate,185622
Table_locks_waited,0
Tc_log_max_pages_used,0
Tc_log_page_size,0
Tc_log_page_waits,0
Threads_cached,3
Threads_connected,15
Threads_created,27
Threads_running,1
Uptime,158056 |
