Hello: After haveing a look at the `show processlist` I realized that querys will be locked if there is an update or insert statement otherwise the `STATUS` is as regular "copying to temp", "sorting" etc. So that not all querys will be locked automatically. (But when checking there where lots of insert intos so that every query seemd to be locked)
some select querys `SELECT kk2.k_id FROM katalog_katalog kk LEFT JOIN katalog_katalog kk1 ON k_id=kk1.kat_id LEFT JOIN katalog_katalog kk2 ON kk1.k_kat_id=kk2.kat_id WHERE kk.kat_id=34678 AND kk1.k_kategorie_id=56 AND kk2.k_kategorie_id=24; An insert into/update looks like this: INSERT INTO katalog SET kategorie_id=36, titel="foo foo bar", artikel="bar bar bar foo" The Update gets an additonal `WHERE id=1234` about the create statements: Two of many tables hopefully give an insight //----- snip CREATE TABLE `katalog` ( `id` int(11) NOT NULL auto_increment, `kategorie_id` int(11) NOT NULL default '0', `datum` datetime NOT NULL default '0000-00-00 00:00:00', `titel` varchar(200) collate latin1_german2_ci default NULL, `untertitel` text collate latin1_german2_ci, `einleitung` text collate latin1_german2_ci, `artikel` text collate latin1_german2_ci, `quelle` text collate latin1_german2_ci, `bild_id` int(11) default NULL, `txt1` text collate latin1_german2_ci, `txt2` text collate latin1_german2_ci, `txt3` text collate latin1_german2_ci, `txt4` text collate latin1_german2_ci, PRIMARY KEY (`id`), KEY `kategorie_id_idx` (`kategorie_id`), KEY `datum_idx` (`datum`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci PACK_KEYS=1 ; and CREATE TABLE `katalog_katalog` ( `kat_id` int(11) NOT NULL default '0', `kategorie_id` int(11) NOT NULL default '0', `k_id` int(11) NOT NULL default '0', `k_kategorie_id` int(11) NOT NULL default '0', KEY `kat_id_idx` (`kat_id`), KEY `kategorie_id_idx` (`kategorie_id`), KEY `k_id_idx` (`k_id`), KEY `k_kategorie_id_idx` (`k_kategorie_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci PACK_KEYS=1; //----- snap I do have written the skip-dbd in /etc/mysql/my.cnf The idea with the not working skip-dbd came because of the numbers shown in `show variables` bdb_cache_size | 8388600 May my guess was not right. Thanx so far for the help!!! yours mathias >-----Ursprüngliche Nachricht----- >Von: Gleb Paharenko [mailto:[EMAIL PROTECTED] >Gesendet: Sonntag, 16. Oktober 2005 14:45 >An: mysql@lists.mysql.com >Betreff: Re: limited threads to two but 25 waiting?! > > >Hello. > >Please, send to the list your queries and CREATE statements for tables >which are used by you queries. Include the output of SHOW PROCESSLIST. > >> 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. > >Are you sure that server reserves the memory for BDB? According to > > http://dev.mysql.com/doc/refman/5.0/en/bdb-start.html > >it shouldn't. It could show the values of different bdb related >variables, but isn't using them. To what file are you trying to >write? > > > >M.E. Koch wrote: >> 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 >> >> >> > >-- >For technical support contracts, goto https://order.mysql.com/?ref=ensita >This email is sponsored by Ensita.NET http://www.ensita.net/ > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / Gleb Paharenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] >/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET > <___/ www.mysql.com > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
