ich habe seit ca. 4 Tagen eine hohe Load, die anscheinend durch die Datenbank veranlasst wird. Load steigt immer weiter an bis 40.(!) und wenn ich neustarte bleibt der Server ca. 20 Minuten "ruhig" bis es wieder anfängt langsam zu starten.
Zum System:
MD Opteron 1218 Dual Core
4 GB DDR2-667 RAM
(htop):
MySQL-Dienst: ca 90% CPU Auslastung
Apache-Dienst ca. 5%
Zu meinem System:
Debian Etch, Apache2.2, Mysql-Server5.0, PHP5
Das ganze hatte ich vor 2 Monaten bereits. Damals lag es an Indizes, die ich nicht gesetzt hatte. Jetzt habe ich im Slow-Query Logging keine Queries ausser einige, die keine Indizes nutzen - die gab es aber vor einer Woche auch schon mit normaler Auslastung.
Ich poste mal einige Ausgaben, hoffe es hilft euch - ich weiß nicht weiter wie ich da Troubleshooting betreiben soll. Jeder Ansatz wäre eine Hilfe! Danke
top:
Code: Select all
top - 17:18:33 up 13 days, 3:04, 2 users, load average: 1.62, 1.66, 1.95
Tasks: 193 total, 2 running, 191 sleeping, 0 stopped, 0 zombie
Cpu(s): 42.8%us, 7.3%sy, 0.0%ni, 49.0%id, 0.3%wa, 0.2%hi, 0.4%si, 0.0%st
Mem: 3984972k total, 3874644k used, 110328k free, 125580k buffers
Swap: 2096376k total, 44k used, 2096332k free, 3001868k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
18456 mysql 15 0 644m 138m 6100 S 81 3.6 11:25.69 mysqld
18306 www-data 15 0 111m 10m 5668 R 15 0.3 1:15.98 apache2
18888 www-data 15 0 111m 9296 4360 S 4 0.2 0:00.02 apache2
18613 www-data 15 0 111m 10m 5600 S 2 0.3 0:00.70 apache2
18615 www-data 15 0 111m 10m 6064 S 2 0.3 0:00.79 apache2
18664 www-data 15 0 111m 10m 5752 S 2 0.3 0:00.41 apache2
18863 www-data 15 0 111m 9200 4200 S 2 0.2 0:00.06 apache2
18890 root 15 0 10724 1320 884 R 2 0.0 0:00.02 top
1 root 15 0 6124 692 568 S 0 0.0 0:01.83 init
2 root RT 0 0 0 0 S 0 0.0 0:00.29 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.29 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/0
5 root RT 0 0 0 0 S 0 0.0 0:00.02 migration/1
6 root 34 19 0 0 0 S 0 0.0 0:00.13 ksoftirqd/1
Code: Select all
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
character-set-server = utf8
default-character-set = utf8
init-connect='SET NAMES utf8'
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 512M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
max_connections = 1000
table_cache = 1024
thread_concurrency = 4
max_heap_table_size = 32M
tmp_table_size = 64M
#
# * Query Cache Configuration
#
query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 16M
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes
skip-bdb
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
Code: Select all
Aborted_clients 0
Aborted_connects 61
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 243466113
Bytes_sent 245967434
Com_admin_commands 2
Com_alter_db 0
Com_alter_table 69
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 12814
Com_change_master 0
Com_check 88
Com_checksum 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 14
Com_create_user 0
Com_dealloc_sql 0
Com_delete 730
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 1
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 722
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 1658771
Com_set_option 12815
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 1
Com_show_collations 1
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 0
Com_show_grants 1
Com_show_innodb_status 0
Com_show_keys 0
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 1
Com_show_storage_engines 0
Com_show_tables 0
Com_show_triggers 0
Com_show_variables 3
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
Variable_name Value
Com_update 6967
Com_update_multi 1
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 OFF
Connections 12838
Created_tmp_disk_tables 497
Created_tmp_files 5
Created_tmp_tables 2129
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 78
Handler_delete 123
Handler_discover 0
Handler_prepare 0
Handler_read_first 1172
Handler_read_key 13233212
Handler_read_next 3001443
Handler_read_prev 1793
Handler_read_rnd 114447
Handler_read_rnd_next 1907001
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 158721
Handler_write 342333
Innodb_buffer_pool_pages_data 400
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 228
Innodb_buffer_pool_pages_free 0
Innodb_buffer_pool_pages_latched 2
Innodb_buffer_pool_pages_misc 112
Innodb_buffer_pool_pages_total 512
Innodb_buffer_pool_read_ahead_rnd 96
Innodb_buffer_pool_read_ahead_seq 666
Innodb_buffer_pool_read_requests 21157304
Innodb_buffer_pool_reads 59388
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 708
Innodb_data_fsyncs 341
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 1123782656
Innodb_data_reads 61117
Innodb_data_writes 424
Innodb_data_written 7591936
Innodb_dblwr_pages_written 228
Innodb_dblwr_writes 74
Innodb_log_waits 0
Innodb_log_write_requests 46
Innodb_log_writes 122
Innodb_os_log_fsyncs 190
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 86016
Innodb_page_size 16384
Innodb_pages_created 0
Innodb_pages_read 68456
Innodb_pages_written 228
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 0
Innodb_row_lock_time_avg 0
Innodb_row_lock_time_max 0
Innodb_row_lock_waits 0
Innodb_rows_deleted 66
Innodb_rows_inserted 60
Innodb_rows_read 7300435
Innodb_rows_updated 0
Key_blocks_not_flushed 0
Key_blocks_unused 424287
Key_blocks_used 4403
Key_read_requests 38274779
Key_reads 8225
Key_write_requests 356382
Key_writes 11008
Last_query_cost 0.000000
Max_used_connections 23
Ndb_cluster_node_id 0
Ndb_config_from_host
Ndb_config_from_port 0
Ndb_number_of_data_nodes 0
Not_flushed_delayed_rows 0
Open_files 193
Open_streams 0
Open_tables 155
Opened_tables 273
Prepared_stmt_count 0
Qcache_free_blocks 93
Qcache_free_memory 14350088
Qcache_hits 64750
Qcache_inserts 1654732
Qcache_lowmem_prunes 0
Qcache_not_cached 4048
Variable_name Value
Qcache_queries_in_cache 2287
Qcache_total_blocks 4699
Questions 1770605
Rpl_status NULL
Select_full_join 4
Select_full_range_join 0
Select_range 1635646
Select_range_check 0
Select_scan 1304
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 1296
Sort_merge_passes 0
Sort_range 3002
Sort_rows 820160
Sort_scan 2240
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 1692770
Table_locks_waited 3639
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 3
Threads_connected 6
Threads_created 114
Threads_running 5
Uptime 1116
Code: Select all
Queries: 2.0M qps: 1687 Slow: 0.0 Se/In/Up/De(%): 04/00/00/00
qps now: 3524 Slow qps: 0.0 Threads: 13 ( 7/ 2) 08/00/00/00
Key Efficiency: 100.0% Bps in/out: 0.1/ 13.6 Now in/out: 7.9/ 1.8k