MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:John Stannard Date:November 4 2004 11:20pm
Subject:MyISAM table locking problem on AMD64
View as plain text  
I recently migrated a myisam database to a dual processor AMD Opteron
(see specs below) host and found that tables would sporadically lock and
not release and so all subsequent inserts would be blocked.  In this
state, I was unable to nicely restart the server.  It would simply give
up.  Repairing the tables, as you could expect, I would get the message
that some number of connections had terminated without properly closing
the table, but no other indications of table corruption.  Restarting the
server, everything would run fine until the problem manifested again. 

There were no errors reported in the error log, except in some
instances, I'd see a single msg "Forcing close of thread 3  user:..."
but at this point I'm unclear which shutdown that referenced.  

I'm using the precompiled binary 4.1.6-gamma-standard and did a lot of
searching for similar problems and any fix references in 4.1.7, etc.  I
found one thread that was similar
(http://archives.neohapsis.com/archives/mysql/2004-q2/0937.html) which
recommend a workaround of turning concurrent_insert off, which I did and
that does indeed stop the problem, but not really the optimal fix.

If I could force it to repeat, I'd try and file a more extensively
documented bug, but I'm unclear what series of events is causing the
problem.  Happy to try and isolate if someone can suggest a course of
action to do so.  Otherwise, I guess I'll have to live with the
workaround for now but wanted to see if anyone could suggest anything
else to try, test or possibly refer me to a known bug on this issue.
I've included server vars and other OS, CPU info below. 

Thanks.  John 


+---------------------------------+-------------------------------------
-----------------------------------------------------+
| Variable_name                   | Value
|
+---------------------------------+-------------------------------------
-----------------------------------------------------+
| back_log                        | 50
|
| basedir                         |
/usr/local/mysql-standard-4.1.6-gamma-unknown-linux-x86_64-glibc23/
|
| 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-standard-4.1.6-gamma-unknown-linux-x86_64-glibc23/share
/mysql/charsets/ |
| collation_connection            | latin1_swedish_ci
|
| collation_database              | latin1_swedish_ci
|
| collation_server                | latin1_swedish_ci
|
| concurrent_insert               | OFF
|
| connect_timeout                 | 5
|
| datadir                         | /export/louann2/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_compress                   | YES
|
| have_crypt                      | YES
|
| have_csv                        | NO
|
| have_example_engine             | NO
|
| have_geometry                   | YES
|
| have_innodb                     | YES
|
| have_isam                       | NO
|
| 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_locks_unsafe_for_binlog  | OFF
|
| innodb_flush_log_at_trx_commit  | 1
|
| innodb_flush_method             |
|
| innodb_force_recovery           | 0
|
| innodb_lock_wait_timeout        | 50
|
| 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_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-standard-4.1.6-gamma-unknown-linux-x86_64-glibc23/share
/mysql/english/  |
| large_files_support             | ON
|
| license                         | GPL
|
| local_infile                    | ON
|
| locked_in_memory                | OFF
|
| log                             | OFF
|
| log_bin                         | OFF
|
| 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           | 18446744073709551615
|
| max_binlog_size                 | 1073741824
|
| max_connect_errors              | 10
|
| max_connections                 | 200
|
| 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_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
|
| net_buffer_length               | 16384
|
| net_read_timeout                | 30
|
| net_retry_count                 | 10
|
| net_write_timeout               | 60
|
| new                             | OFF
|
| old_passwords                   | OFF
|
| open_files_limit                | 1234
|
| pid_file                        | /export/louann2/data/louann.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                | 33554432
|
| 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
|
| 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                          | /tmp/mysql.sock
|
| sort_buffer_size                | 2097144
|
| sql_mode                        |
|
| storage_engine                  | MyISAM
|
| sync_binlog                     | 0
|
| sync_frm                        | ON
|
| system_time_zone                | PDT
|
| 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.6-gamma-standard
|
| version_comment                 | Official MySQL-standard binary
|
| version_compile_machine         | x86_64
|
| version_compile_os              | unknown-linux
|
| wait_timeout                    | 28800
|
+---------------------------------+-------------------------------------
-----------------------------------------------------+


processor       : 0
vendor_id       : AuthenticAMD
cpu family      : 15
model           : 5
model name      : AMD Opteron(tm) Processor 246
stepping        : 8
cpu MHz         : 1992.037
cache size      : 1024 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 1
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush mmx fxsr sse sse2 syscall nx mmxext lm
3dnowext 3dnow
bogomips        : 3971.48
TLB size        : 1088 4K pages
clflush size    : 64
address sizes   : 40 bits physical, 48 bits virtual
power management: ts ttp

processor       : 1
vendor_id       : AuthenticAMD
cpu family      : 15
model           : 5
model name      : AMD Opteron(tm) Processor 246
stepping        : 8
cpu MHz         : 1992.037
cache size      : 1024 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 1
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush mmx fxsr sse sse2 syscall nx mmxext lm
3dnowext 3dnow
bogomips        : 3971.48
TLB size        : 1088 4K pages
clflush size    : 64
address sizes   : 40 bits physical, 48 bits virtual
power management: ts ttp

Linux louann 2.4.21-9.ELsmp #1 SMP Thu Jan 8 16:52:31 EST 2004 x86_64
x86_64 x86_64 GNU/Linux
Thread
MyISAM table locking problem on AMD64John Stannard5 Nov