Hi, I have searched and tried and have no clue why the db on 4.1.11-Debian_4sarge2 behaves like this. I have no TABLE LOCK query anywhere in my code! About the server (LAMP/ 2x3Mhz, 4GB RAM)
1. prob. the mysql> show processlist gives me a list of 25 threads waiting for there work. even on heavy load `pgrep mysql` will just show two PIDs doing somthing even if there are just SELECT queries on the DB. therefor the server get's really slow. The case get's even more worse if there is a UPDATE or INSERT statement. (LOCK problem) 2. When writng in my.cnf the skip-bdb option still the server reserves memmory for berklyDB aswell with innoDB. But writing somthing wrong into the file will cause mysql to throw an error. I have no idea anymore where to look or what to check pls help. any tuning-tipps are wellcome! mysql> show variables; gives me that +---------------------------------+------------------+ | Variable_name | Value | +---------------------------------+------------------+ | back_log | 50 | | basedir | /usr/ | | bdb_cache_size | 8388600 | | bdb_home | | | bdb_log_buffer_size | 0 | | bdb_logdir | | | bdb_max_lock | 10000 | | bdb_shared_data | OFF | | bdb_tmpdir | | | 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/share/mysql/charsets/| | collation_connection | latin1_swedish_ci | | collation_database | latin1_german2_ci | | collation_server | latin1_german2_ci | | concurrent_insert | ON | | 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 | | 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 | DISABLED | | have_blackhole_engine | NO | | have_compress | YES | | have_crypt | YES | | have_csv | YES | | have_example_engine | NO | | have_geometry | YES | | have_innodb | DISABLED | | have_isam | YES | | have_ndbcluster | DISABLED | | have_openssl | NO | | have_query_cache | YES | | have_raid | YES | | 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 | | | 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 | | bdb_cache_size | 8388600 | | bdb_home | | | bdb_log_buffer_size | 0 | | bdb_logdir | | | bdb_max_lock | 10000 | | bdb_shared_data | OFF | | bdb_tmpdir | | | 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/share/mysql/charsets/| | collation_connection | latin1_swedish_ci | | collation_database | latin1_german2_ci | | collation_server | latin1_german2_ci | | concurrent_insert | ON | | 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 | | expire_logs_days | 0 | | flush | OFF | | 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 | 2093056 | | key_buffer_size | 402653184 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | language | /usr/share/mysql/english/| | large_files_support | ON | | license | GPL | | local_infile | ON | | locked_in_memory | OFF | | log | OFF | | log_bin | ON | | log_error | | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_update | OFF | | log_warnings | 1 | | long_query_time | 10 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 1047552 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 104857600 | | 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 | 4294967295 | | max_length_for_sort_data | 1024 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length | 1024 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | | myisam_data_pointer_size | 4 | | myisam_max_extra_sort_file_size | 2147483648 | | myisam_max_sort_file_size | 2147483647 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 134217728 | | ndb_autoincrement_prefetch_sz | 32 | | ndb_force_send | ON | | ndb_use_exact_count | ON | | ndb_use_transactions | ON | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old_passwords | ON | | open_files_limit | 2158 | | pid_file | /var/run/mysqld/mysqld.pid| | port | 3306 | | preload_buffer_size | 32768 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 33554432 | | query_cache_min_res_unit | 4096 | | query_cache_size | 268435456 | | 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 | 262144 | | 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 | OFF | | skip_show_database | OFF | | slave_net_timeout | 3600 | | slave_transaction_retries | 0 | | slow_launch_time | 2 | | socket | /var/run/mysqld/mysqld.sock| | sort_buffer_size | 2097144 | | sql_mode | | | storage_engine | MyISAM | | sql_notes | ON | | sql_warnings | ON | | sync_binlog | 0 | | sync_replication | 0 | | sync_replication_slave_id | 0 | | sync_replication_timeout | 0 | | sync_frm | ON | | system_time_zone | CEST | | table_cache | 1024 | | table_type | MyISAM | | thread_cache_size | 64 | | thread_stack | 196608 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | tmp_table_size | 33554432 | | tmpdir | /tmp | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | version | 4.1.11-Debian_4sarge2-log| | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (April 1, 2005)| | version_comment | Source distribution| | version_compile_machine | i386 | | version_compile_os | pc-linux-gnu | | wait_timeout | 28800 | +---------------------------------+-------------------+ thx for the help in advance ------------------------------------------------------------- _/_/_/ Mathias E. Koch _/_/_/ Florastrasse 46 _/_/_/ D-47799 Krefeld T 02151 6199066 (VOIP FreeNet) mailto:[EMAIL PROTECTED] F 02151 150 528 (auch Tel/Fax) mailto:[EMAIL PROTECTED] M 0171 6450 137 (D1) W http://www.AdOptimize.de http://www.kunstmarkt.com ---------------------------------- ** New-Media-Angels.de ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
