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]
