Hallo zusammen,
ich betreibe auf einem rootserver eine LAMP-Applikation mit MySQL 4.1.22 =
und PHP 4.x sowie Apache 1.x
Wenn PHP eine Datenbankabfrage durchführt und es zu einem MySQL-Fehler =
kommt, dann bekomme ich eine e-mail mit genaueren Daten und MySQL
Fehlerrückmeldung.
Seit kurzem bekomme ich verstärkt diese Fehlermeldungen die aber keine =
Fehlerrückmeldung von MySQL enthalten. Dabei sind die Queries
unerhebelich, da es alle betrifft. Das einzig Auffalende ist, dass es
häufig durch bots angefragte Seiten sind (z.B. google bot) und die
häufig aufeinander folgen. Es scheint als ob der MySql-Server in die
Knie geht und die Anfragen nicht bearbeitet weil es zu viele sind in zu
kurzer Zeit. Das Fehlerlog von MySql zeigt keinerlei Einträge dazu.
Ich vermute das liegt an einer schlechten MySQL-Server Konfiguration.
Auf dem root-Server sind MySQL, Apache und PHP laufend und es handelt
sich um einen neueren Opteron Server mit 2 Gig RAM und Festplattenraid.
Die Einstellungen aus my.cnf sehen folgendermaßen aus:
skip-locking
key_buffer =3D 384M
max_allowed_packet =3D 1M
table_cache =3D 512
sort_buffer_size =3D 2M
read_buffer_size =3D 2M
read_rnd_buffer_size =3D 8M
myisam_sort_buffer_size =3D 64M
thread_cache_size =3D 8
query_cache_size =3D 128M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency =3D 8
Key buffer ist etwas hoch, da die Indexe gesamtgrößen von 190MB
aufweisen, ich würde das aber gerne lassen da die Indexe mit der Zeit
wachsen.
Bei query_cache_size vermute ich das dies zu hoch ist, dafür fehlt mir =
aber tiefergehendes Wissen über MySQL Server.
Wie viele connections kann so ein System gleichzeitig leisten bevor es
andere Anfragen ablehnt? Ich denke hier liegt der Knackpunkt. Da sind
vermutlich die Einstellungen in my.cnf falsch die dann dazu führen das =
Anfragen abgelehnt werden.
Ich füge noch einen Ausdruck von "show variables" und "show status" an.=
Vielen Dank für jeglichen Tipp der mir hilft das Problem in den Griff z=
u
bekommen.
Beste Grüße,
Merlin
mysql> Show variables;
+---------------------------------+------------------------- -------------=
--+
| Variable_name | Value =
|
+---------------------------------+------------------------- -------------=
--+
| back_log | 50 =
|
| basedir | /usr/local/mysql/ =
|
| binlog_cache_size | 32768 =
|
| bulk_insert_buffer_size | 8388608 =
|
| character_set_client | latin1 =
|
| character_set_connection | latin1 =
|
| character_set_database | latin1 =
|
| character_set_results | latin1 =
|
| character_set_server | latin1 =
|
| character_set_system | utf8 =
|
| character_sets_dir | /usr/local/mysql/share/mysql/charsets=
/ |
| collation_connection | latin1_swedish_ci =
|
| collation_database | latin1_swedish_ci =
|
| collation_server | latin1_swedish_ci =
|
| concurrent_insert | ON =
|
| connect_timeout | 5 =
|
| datadir | /home/mysqladm/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 =
|
| 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 | NO =
|
| have_bdb | NO =
|
| have_blackhole_engine | NO =
|
| have_compress | YES =
|
| have_crypt | YES =
|
| have_csv | NO =
|
| have_example_engine | NO =
|
| have_geometry | YES =
|
| have_innodb | YES =
|
| have_isam | NO =
|
| have_merge_engine | YES =
|
| have_ndbcluster | NO =
|
| 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 | 8388608 =
|
| innodb_data_file_path | ibdata1:10M:autoextend =
|
| innodb_data_home_dir | =
|
| innodb_fast_shutdown | ON =
|
| 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 | 1048576 =
|
| innodb_log_file_size | 5242880 =
|
| 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_table_locks | ON =
|
| innodb_thread_concurrency | 8 =
|
| interactive_timeout | 28800 =
|
| join_buffer_size | 131072 =
|
| key_buffer_size | 402653184 =
|
| key_cache_age_threshold | 300 =
|
| key_cache_block_size | 1024 =
|
| key_cache_division_limit | 100 =
|
| language | /usr/local/mysql/share/mysql/english/=
|
| large_files_support | ON =
|
| lc_time_names | en_US =
|
| license | GPL =
|
| local_infile | ON =
|
| locked_in_memory | OFF =
|
| log | OFF =
|
| log_bin | ON =
|
| log_error | /home/mysqladm/mysql-error.log =
|
| log_slave_updates | OFF =
|
| log_slow_queries | ON =
|
| log_update | OFF =
|
| log_warnings | 1 =
|
| long_query_time | 1 =
|
| low_priority_updates | OFF =
|
| lower_case_file_system | OFF =
|
| lower_case_table_names | 0 =
|
| max_allowed_packet | 1047552 =
|
| max_binlog_cache_size | 18446744073709551615 =
|
| max_binlog_size | 1073741824 =
|
| max_connect_errors | 10 =
|
| max_connections | 100 =
|
| 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_prepared_stmt_count | 16382 =
|
| max_relay_log_size | 0 =
|
| max_seeks_for_key | 18446744073709551615 =
|
| max_sort_length | 1024 =
|
| max_tmp_tables | 32 =
|
| max_user_connections | 0 =
|
| max_write_lock_count | 18446744073709551615 =
|
| myisam_data_pointer_size | 4 =
|
| myisam_max_extra_sort_file_size | 2147483648 =
|
| myisam_max_sort_file_size | 9223372036854775807 =
|
| myisam_recover_options | OFF =
|
| myisam_repair_threads | 1 =
|
| myisam_sort_buffer_size | 67108864 =
|
| myisam_stats_method | nulls_unequal =
|
| net_buffer_length | 16384 =
|
| net_read_timeout | 30 =
|
| net_retry_count | 10 =
|
| net_write_timeout | 60 =
|
| new | OFF =
|
| old_passwords | OFF =
|
| open_files_limit | 1134 =
|
| pid_file | /home/mysqladm/data/GS3.pid =
|
| port | 3306 =
|
| preload_buffer_size | 32768 =
|
| prepared_stmt_count | 0 =
|
| protocol_version | 10 =
|
| query_alloc_block_size | 8192 =
|
| query_cache_limit | 1048576 =
|
| query_cache_min_res_unit | 4096 =
|
| query_cache_size | 134217728 =
|
| query_cache_type | ON =
|
| query_cache_wlock_invalidate | OFF =
|
| query_prealloc_size | 8192 =
|
| range_alloc_block_size | 2048 =
|
| read_buffer_size | 2093056 =
|
| read_only | OFF =
|
| read_rnd_buffer_size | 8384512 =
|
| relay_log_purge | ON =
|
| relay_log_space_limit | 0 =
|
| rpl_recovery_rank | 0 =
|
| secure_auth | OFF =
|
| server_id | 1 =
|
| skip_external_locking | ON =
|
| skip_networking | ON =
|
| skip_show_database | OFF =
|
| slave_net_timeout | 3600 =
|
| slave_transaction_retries | 0 =
|
| slow_launch_time | 2 =
|
| socket | /var/lib/mysql/mysql.sock =
|
| sort_buffer_size | 2097144 =
|
| sql_mode | =
|
| sql_notes | ON =
|
| sql_warnings | ON =
|
| storage_engine | MyISAM =
|
| sync_binlog | 0 =
|
| sync_frm | ON =
|
| sync_replication | 0 =
|
| sync_replication_slave_id | 0 =
|
| sync_replication_timeout | 0 =
|
| system_time_zone | CEST =
|
| table_cache | 512 =
|
| table_type | MyISAM =
|
| thread_cache_size | 8 =
|
| thread_stack | 196608 =
|
| time_format | %H:%i:%s =
|
| time_zone | SYSTEM =
|
| tmp_table_size | 33554432 =
|
| tmpdir | =
|
| transaction_alloc_block_size | 8192 =
|
| transaction_prealloc_size | 4096 =
|
| tx_isolation | REPEATABLE-READ =
|
| version | 4.1.22-log =
|
| version_comment | Source distribution =
|
| version_compile_machine | x86_64 =
|
| version_compile_os | unknown-linux-gnu =
|
| wait_timeout | 28800 =
|
+---------------------------------+------------------------- -------------=
--+
Show status:
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 8 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 27020088 |
| Bytes_sent | 165614188 |
| Com_admin_commands | 606 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 61560 |
| 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 | 724 |
| Com_dealloc_sql | 0 |
| Com_delete | 45 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 696 |
| 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 | 455 |
| Com_insert_select | 1128 |
| 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 | 52174 |
| Com_set_option | 48 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 1 |
| Com_show_charsets | 12 |
| Com_show_collations | 12 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 12 |
| Com_show_errors | 0 |
| Com_show_fields | 122 |
| Com_show_grants | 5 |
| 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 | 37 |
| Com_show_variables | 28 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 2821 |
| Com_update_multi | 1 |
| Connections | 7929 |
| Created_tmp_disk_tables | 1957 |
| Created_tmp_files | 70 |
| Created_tmp_tables | 12090 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 15 |
| Handler_discover | 0 |
| Handler_read_first | 2164 |
| Handler_read_key | 25097238 |
| Handler_read_next | 22404943 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 388295 |
| Handler_read_rnd_next | 233243621 |
| Handler_rollback | 0 |
| Handler_update | 1135208 |
| Handler_write | 18415378 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 310871 |
| Key_blocks_used | 12489 |
| Key_read_requests | 70715435 |
| Key_reads | 14774 |
| Key_write_requests | 93158 |
| Key_writes | 856 |
| Max_used_connections | 35 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 316 |
| Open_streams | 0 |
| Open_tables | 171 |
| Opened_tables | 1625 |
| Qcache_free_blocks | 418 |
| Qcache_free_memory | 108877520 |
| Qcache_hits | 61906 |
| Qcache_inserts | 45045 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 7129 |
| Qcache_queries_in_cache | 17050 |
| Qcache_total_blocks | 34651 |
| Questions | 189854 |
| Rpl_status | NULL |
| Select_full_join | 243 |
| Select_full_range_join | 0 |
| Select_range | 3500 |
| Select_range_check | 62 |
| Select_scan | 19693 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 1 |
| Sort_merge_passes | 33 |
| Sort_range | 2595 |
| Sort_rows | 17369292 |
| Sort_scan | 13856 |
| Table_locks_immediate | 103990 |
| Table_locks_waited | 233 |
| Threads_cached | 4 |
| Threads_connected | 28 |
| Threads_created | 43 |
| Threads_running | 1 |
| Uptime | 3072 |
+----------------------------+-----------+
163 rows in set (0.00 sec)
