folgendes Problem:
Wenn ich per PMA einen User kopieren will, hängt sich der mysqld
komplett auf, und nimmt überhaupt gar keine Connections mehr an, auch
nicht per Socket. Deswegen muss ich den mysqld Prozess dann hart und
ungern per kill -9 abschiessen.
Der CPUs sind während dem Aussetzer komplett idle, so auch Festplatte
und Netzwerk (per dstat gecheckt).
Es wird leider überhaupt keine Log-Information geschrieben, das einzige
was ich anbieten kann ist ein lsof als der Server stand:
http://pastebin.com/705848 (db.firma.de ist der mysql-server, webserver.
der Server auf dem die Web-Portale laufen, PHP5/libmysqlclient14
v4.1.15-0.dotdeb.1)
Da auf dem Server kritische Daten liegen und er der zentrale DB-Server
für all unsere Anwendungen ist bin ich sehr daran interessiert das
Problem rasch zu identifizieren.
Hier die Umgebung:
Der Server ist ein 1und1-Rootserver, Dualcore Opteron 4GB RAM, mit
Debian AMD64 stable und folgendem Kernel:
Linux db 2.6.16.9 #2 SMP Sun Apr 23 13:22:33 CEST 2006 x86_64 GNU/Linux
MySQL ist von dotdeb als Package installiert:
mysqld Ver 5.0.20a-Debian_1.dotdeb.1-log for pc-linux-gnu on x86_64
(Dotdeb Sarge backport)
Alle Tabellen sind InnoDB.
Ich hab unten noch die my.cnf, STATUS und VARIABLES angehängt.
Cheers,
Ren
Code: Select all
------------ my.cnf ---------------
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
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-name-resolve
skip-external-locking
old_passwords = 1
bind-address = 87.106.x.x
log-warnings
thread_cache_size=40
max_connections=1000
wait_timeout=60
default-storage-engine = innodb
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
query_cache_limit = 1048576
query_cache_size = 20M
query_cache_type = 1
log-slow-queries = /var/log/mysql/mysql-slow.log
skip-bdb
innodb_data_file_path = ibdata1:3200M:autoextend
innodb_buffer_pool_size = 3200M
innodb_log_file_size = 800M
innodb_log_buffer_size = 8M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
SHOW STATUS;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 68 |
| Aborted_connects | 3 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 19215135 |
| Bytes_sent | 202099954 |
| Com_admin_commands | 2 |
| Com_alter_db | 0 |
| Com_alter_table | 59 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 140043 |
| Com_change_master | 0 |
| Com_check | 336 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 14 |
| Com_dealloc_sql | 0 |
| Com_delete | 989 |
| 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 | 9649 |
| 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 | 55698 |
| Com_set_option | 6 |
| 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 | 0 |
| Com_show_grants | 0 |
| 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 | 78 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 17 |
| Com_show_triggers | 0 |
| Com_show_variables | 4 |
| 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 | 2841 |
| 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 | 36118 |
| Created_tmp_disk_tables | 11368 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 11398 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 13471 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 19730 |
| Handler_read_key | 193046 |
| Handler_read_next | 41470381 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 24976 |
| Handler_read_rnd_next | 489807763 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 2202823 |
| Innodb_buffer_pool_pages_data | 114484 |
| Innodb_buffer_pool_pages_dirty | 13 |
| Innodb_buffer_pool_pages_flushed | 8507 |
| Innodb_buffer_pool_pages_free | 89844 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 472 |
| Innodb_buffer_pool_pages_total | 204800 |
| Innodb_buffer_pool_read_ahead_rnd | 4 |
| Innodb_buffer_pool_read_ahead_seq | 1565 |
| Innodb_buffer_pool_read_requests | 172490290 |
| Innodb_buffer_pool_reads | 14811 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 46429 |
| Innodb_data_fsyncs | 12175 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 1877381120 |
| Innodb_data_reads | 16809 |
| Innodb_data_writes | 19845 |
| Innodb_data_written | 286430720 |
| Innodb_dblwr_pages_written | 8507 |
| Innodb_dblwr_writes | 498 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 4082 |
| Innodb_log_writes | 10747 |
| Innodb_os_log_fsyncs | 11178 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 7452672 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 31 |
| Innodb_pages_read | 114453 |
| Innodb_pages_written | 8507 |
| 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 | 25 |
| Innodb_rows_inserted | 9649 |
| Innodb_rows_read | 731124274 |
| Innodb_rows_updated | 960 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 13396 |
| Key_blocks_used | 7 |
| Key_read_requests | 5434 |
| Key_reads | 244 |
| Key_write_requests | 3188 |
| Key_writes | 18 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 16 |
| Ndb_cluster_node_id | 0 |
| Ndb_connected_host | |
| Ndb_connected_port | 0 |
| Ndb_number_of_storage_nodes | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1 |
| Open_streams | 0 |
| Open_tables | 64 |
| Opened_tables | 1126 |
| Qcache_free_blocks | 1198 |
| Qcache_free_memory | 12954192 |
| Qcache_hits | 75860 |
| Qcache_inserts | 39677 |
| Qcache_lowmem_prunes | 171 |
| Qcache_not_cached | 16050 |
| Qcache_queries_in_cache | 1640 |
| Qcache_total_blocks | 4660 |
| Questions | 321620 |
| Rpl_status | NULL |
| Select_full_join | 5 |
| Select_full_range_join | 0 |
| Select_range | 10171 |
| Select_range_check | 0 |
| Select_scan | 20836 |
| 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 | 10567 |
| Sort_rows | 39682 |
| Sort_scan | 15849 |
| Table_locks_immediate | 70040 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 15 |
| Threads_connected | 1 |
| Threads_created | 16 |
| Threads_running | 1 |
| Uptime | 4276 |
+-----------------------------------+------------+
SHOW VARIABLES;
+---------------------------------+-------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| 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 | /usr/share/mysql/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 | /var/lib/mysql/ |
| 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 | 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 | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_example_engine | NO |
| have_federated_engine | YES |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | DISABLED |
| have_openssl | NO |
| 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 | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 3355443200 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:3200M: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 | 8388608 |
| innodb_log_file_size | 838860800 |
| 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 | 0 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| 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 |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 2 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 16776192 |
| max_binlog_cache_size | 18446744073709551615 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 1000 |
| 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_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 | 9223372036854775807 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| ndb_autoincrement_prefetch_sz | 32 |
| ndb_force_send | ON |
| ndb_use_exact_count | ON |
| ndb_use_transactions | OFF |
| ndb_cache_check_time | 0 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 5010 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/run/mysqld/mysqld.pid |
| 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 | 20971520 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| 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/run/mysqld/mysqld.sock |
| sort_buffer_size | 2097144 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | InnoDB |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 10 |
| system_time_zone | CEST |
| table_cache | 64 |
| table_lock_wait_timeout | 50 |
| table_type | InnoDB |
| thread_cache_size | 40 |
| thread_stack | 131072 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.20a-Debian_1.dotdeb.1-log |
| version_comment | Dotdeb Sarge backport |
| version_compile_machine | x86_64 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 60 |
+---------------------------------+-------------------------------+