Hello yet again, Thanks for the quick answer, Gleb! I am quite sure that the system doesn't swap. I'll give the BTREE index a shot and I will let you know what effect it had.
Here's a the free Output from one of the systems: total used free shared buffers cached Mem: 2068804 2018276 50528 0 41644 902912 -/+ buffers/cache: 1073720 995084 Swap: 2096472 60652 2035820 'Show status' outputs the following: +--------------------------------+------------+ | Variable_name | Value | +--------------------------------+------------+ | Aborted_clients | 5071 | | Aborted_connects | 48869 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 339 | | Bytes_received | 1378529900 | | Bytes_sent | 632139443 | | Com_admin_commands | 7 | | Com_alter_db | 0 | | Com_alter_table | 1 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 339 | | Com_change_db | 849180 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 339 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 1 | | Com_dealloc_sql | 0 | | Com_delete | 792523 | | 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 | 2285560 | | Com_insert_select | 291 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 3 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 1 | | 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 | 4527361 | | Com_set_option | 729 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 83 | | Com_show_charsets | 159 | | Com_show_collations | 159 | | Com_show_column_types | 0 | | Com_show_create_db | 6 | | Com_show_create_table | 200 | | Com_show_databases | 71 | | Com_show_errors | 0 | | Com_show_fields | 768 | | Com_show_grants | 20 | | Com_show_innodb_status | 0 | | Com_show_keys | 19 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_privileges | 0 | | Com_show_processlist | 76 | | Com_show_slave_hosts | 3 | | Com_show_slave_status | 0 | | Com_show_status | 1 | | Com_show_storage_engines | 0 | | Com_show_tables | 356 | | Com_show_variables | 270 | | Com_show_warnings | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 64 | | Com_unlock_tables | 3 | | Com_update | 3764709 | | Com_update_multi | 0 | | Connections | 899094 | | Created_tmp_disk_tables | 0 | | Created_tmp_files | 3523 | | Created_tmp_tables | 7 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 1 | | Handler_commit | 340 | | Handler_delete | 46996 | | Handler_discover | 0 | | Handler_read_first | 894411 | | Handler_read_key | 25998919 | | Handler_read_next | 49651615 | | Handler_read_prev | 0 | | Handler_read_rnd | 2532093 | | Handler_read_rnd_next | 3122551376 | | Handler_rollback | 850295 | | Handler_update | 725456 | | Handler_write | 2652812 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 906 | | Key_blocks_used | 11 | | Key_read_requests | 2810 | | Key_reads | 27 | | Key_write_requests | 1071 | | Key_writes | 1071 | | Max_used_connections | 451 | | Not_flushed_delayed_rows | 0 | | Open_files | 5 | | Open_streams | 0 | | Open_tables | 256 | | Opened_tables | 3604862 | | Qcache_free_blocks | 4 | | Qcache_free_memory | 10470632 | | Qcache_hits | 1433007 | | Qcache_inserts | 731457 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 3798616 | | Qcache_queries_in_cache | 3 | | Qcache_total_blocks | 13 | | Questions | 14524815 | | Rpl_status | NULL | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 72614 | | Select_range_check | 0 | | Select_scan | 872790 | | Slave_open_temp_tables | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 2539 | | Sort_merge_passes | 0 | | Sort_range | 80461 | | Sort_rows | 2505454 | | Sort_scan | 14 | | 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 | 10574328 | | Table_locks_waited | 1564163 | | Threads_cached | 36 | | Threads_connected | 312 | | Threads_created | 83431 | | Threads_running | 229 | | Uptime | 67293 | +--------------------------------+------------+ The 'show variables' output: +---------------------------------+----------------------------------------- -------------------+ | Variable_name | Value | +---------------------------------+----------------------------------------- -------------------+ | back_log | 50 | | basedir | /usr/ | | bdb_cache_size | 8388600 | | bdb_home | /var/lib/mysql/ | | bdb_log_buffer_size | 131072 | | bdb_logdir | | | bdb_max_lock | 10000 | | bdb_shared_data | OFF | | bdb_tmpdir | /tmp/ | | 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_swedish_ci | | collation_server | latin1_swedish_ci | | concurrent_insert | ON | | connect_timeout | 6 | | 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 | YES | | have_compress | YES | | have_crypt | YES | | have_csv | YES | | have_example_engine | NO | | have_geometry | YES | | have_innodb | YES | | have_isam | YES | | have_ndbcluster | DISABLED | | have_openssl | YES | | 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 | 20971520 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 943718400 | | innodb_data_file_path | ibdata1:2G: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 | 8388608 | | 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 | 10 | | join_buffer_size | 1044480 | | key_buffer_size | 1048576 | | 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 | /var/log/mysql/mysql.err | | log_slave_updates | OFF | | log_slow_queries | ON | | 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 | 1073741824 | | max_connect_errors | 999999 | | max_connections | 450 | | 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 | 8388608 | | 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 | 32000 | | 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 | 10485760 | | query_cache_type | DEMAND | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size | 1044480 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log_purge | ON | | 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 | | slow_launch_time | 2 | | socket | /var/run/mysqld/mysqld.sock | | sort_buffer_size | 1048568 | | sql_mode | | | storage_engine | MyISAM | | sync_binlog | 0 | | sync_frm | ON | | system_time_zone | CEST | | table_cache | 256 | | table_type | MyISAM | | thread_cache_size | 64 | | thread_stack | 131072 | | 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.9-Debian_0.dotdeb.0-log | | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (January 11, 2005) | | version_comment | Source distribution | | version_compile_machine | i386 | | version_compile_os | pc-linux-gnu | | wait_timeout | 10 | +---------------------------------+----------------------------------------- -----------------+ Herzliche Grüße Hannes Rohde ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯ incoWEB.de - agentur für neue medien Stapenhorststr. 10 D-45329 Essen [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> http://www.incoWEB.de Phone & Fax 0700-0-4626932 0700-0-INCOWEB Diese E-Mail enthält vertrauliche Informationen, die nur für den o.g. Empfänger bestimmt sind! Jede Kenntnisnahme, Verteilung oder Vervielfältigung durch andere Personen ist nicht zulässig. Sollten Sie diese E-Mail irrtümlich erhalten haben, melden Sie uns dies bitte unverzüglich. This email, its content and any files transmitted with it are intended solely for the addressee(s). Access, distribution or copying by any other party is not permitted. If you are not the intended recipient, then please notify us immediately by returning it to the originator. -----Ursprüngliche Nachricht----- Von: Gleb Paharenko [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 7. Juli 2005 10:35 An: mysql@lists.mysql.com Betreff: Re: Locks on Heap tables Hello. Are you sure that your server doesn't swap? Providing output of 'SHOW STATUS', 'SHOW VARIABLES' and your table definition could give more information for suggestions. Also, if you have a hash index on a MEMORY table that has a high degree of key duplication (many index entries containing the same value), updates to the table that affect key values and all deletes are significantly slower. The degree of slowdown is proportional to the degree of duplication (or, inversely proportional to the index cardinality). You can use a BTREE index to avoid this problem. "Hannes Rohde" <[EMAIL PROTECTED]> wrote: > Hello everyone, > > We are using MySQL as the database backend on quite a big portal > page with about 50.000 users and 3 mio. PIs per day. MySQL is as well = > the > backend for the (php) session management. We are using a heap for that = > case > as well as for instance phpbb does.=20 > Lately we are experiencing long lasting table locks due to deletes or > updates on the session table. I know that heap tables only support table > wide locking, but shouldn't those locks be gone quite fast? I have = > already > checked the obvious reasons for this kind of behaviour like swapping but = > I > couldn't find anything. Even googling didn't bring anything useful up. > Hopefully someone got some ideas to solve this problem :-) > > Thank you in advance > Hannes Rohde > > =AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF= > =AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF > incoWEB.de - agentur f=FCr neue medien > Stapenhorststr. 10 > D-45329 Essen > > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > http://www.incoWEB.de > > Phone & Fax 0700-0-4626932 > 0700-0-INCOWEB > > Diese E-Mail enth=E4lt vertrauliche Informationen, die nur f=FCr den = > o.g. > Empf=E4nger bestimmt sind! Jede Kenntnisnahme, Verteilung oder > Vervielf=E4ltigung durch andere Personen ist nicht zul=E4ssig. Sollten = > Sie diese > E-Mail irrt=FCmlich erhalten haben, melden Sie uns dies bitte = > unverz=FCglich. > > This email, its content and any files transmitted with it are intended > solely for the addressee(s). Access, distribution or copying by any = > other > party is not permitted. If you are not the intended recipient, then = > please > notify us immediately by returning it to the originator.=20 > > > -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]