Problem with Tables being locked [Archive]

Vigile

Fri 12th Mar '04, 4:45pm

I really hope some one can help me here... I am random spurts of tables being locked. Different tables at different times from either inserting or updating a database entry.

Here is mysqladmin processlist:

[root@localhost MBPlatforms]# mysqladmin processlist -p
Enter password:
+------+----------+-----------+-------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+----------+-----------+-------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| 3201 | pcpadmin | localhost | MBPlatforms | Query | 430 | update | REPLACE INTO Motherboards VALUES ('122', '15', '1', '9', '4', '2',
'1', '5', '6', '2', '2', '2', |
| 3222 | root | localhost | users | Query | 395 | Locked | SHOW TABLE STATUS FROM `MBPlatforms` |
| 3235 | root | localhost | users | Query | 386 | Locked | SHOW TABLE STATUS FROM `MBPlatforms` |
| 3237 | pcpuser | localhost | MBPlatforms | Query | 381 | Locked | SELECT *
FROM Motherboards, Manus
WHERE Motherboards.ManuID = Manus.ID
ORDER BY Manus.Nam |
| 3239 | root | localhost | MBPlatforms | Query | 374 | Locked | ANALYZE TABLE `Motherboards` |
| 3247 | root | localhost | MBPlatforms | Query | 337 | Waiting for table | CHECK TABLE `Manus` |
| 3256 | root | localhost | users | Query | 314 | Waiting for table | SHOW TABLE STATUS FROM `MBPlatforms` |
| 3277 | pcpuser | localhost | MBPlatforms | Query | 234 | Waiting for table | SELECT *
FROM Motherboards, Manus
WHERE Motherboards.ManuID = Manus.ID
ORDER BY Manus.Nam |
| 3292 | root | localhost | | Refresh | 169 | Flushing tables | |
| 3298 | pcpuser | localhost | MBPlatforms | Query | 134 | Waiting for table | SELECT *
FROM Motherboards, Manus
WHERE Motherboards.ManuID = Manus.ID
ORDER BY Manus.Nam |
| 3335 | root | localhost | | Query | 0 | | show processlist |
+------+----------+-----------+-------------+---------+------+-------------------+----------------------------------------------------------------------------

Here is extended-status:

[root@localhost MBPlatforms]# mysqladmin extended-status -p
Enter password:
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 4 |
| Bytes_received | 2421416 |
| Bytes_sent | 128339679 |
| Com_admin_commands | 2 |
| Com_alter_table | 1 |
| Com_analyze | 50 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 3606 |
| Com_change_master | 0 |
| Com_check | 192 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 54 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 1 |
| Com_grant | 0 |
| Com_insert | 9 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 1 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 18262 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 15 |
| Com_show_fields | 51 |
| Com_show_grants | 0 |
| Com_show_keys | 21 |
| Com_show_logs | 0 |
| Com_show_master_status | 1 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 2 |
| Com_show_slave_status | 0 |
| Com_show_status | 9 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 178 |
| Com_show_variables | 1 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 8 |
| Connections | 3357 |
| Created_tmp_disk_tables | 40 |
| Created_tmp_tables | 40 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 2 |
| Handler_delete | 3 |
| Handler_read_first | 60 |
| Handler_read_key | 44224 |
| Handler_read_next | 32003 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 26637 |
| Handler_read_rnd_next | 47061 |
| Handler_update | 8 |
| Handler_write | 4138 |
| Key_blocks_used | 107 |
| Key_read_requests | 79582 |
| Key_reads | 106 |
| Key_write_requests | 23 |
| Key_writes | 13 |
| Max_used_connections | 12 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 19 |
| Open_files | 34 |
| Open_streams | 0 |
| Opened_tables | 116 |
| Questions | 28419 |
| Select_full_join | 19 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 1760 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 2896 |
| Sort_rows | 26637 |
| Sort_scan | 1518 |
| Table_locks_immediate | 31592 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 3356 |
| Threads_connected | 11 |
| Threads_running | 11 |
| Uptime | 9325 |
+--------------------------+-----------+

Finally, here is variables:

[root@localhost MBPlatforms]# mysqladmin variables -p
Enter password:
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log | 50 |
| basedir | /usr/ |
| bdb_cache_size | 8388600 |
| bdb_log_buffer_size | 32768 |
| bdb_home | /var/lib/mysql/ |
| bdb_max_lock | 10000 |
| bdb_logdir | |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (September 11, 2003) |
| binlog_cache_size | 32768 |
| character_set | latin1 |
| character_sets | latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin2 latin5 swe7 usa7 win1250 win1251 win1251ukr ujis sjis tis620 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | /var/lib/mysql/ |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| have_bdb | YES |
| have_gemini | NO |
| have_innodb | DISABLED |
| have_isam | YES |
| have_raid | NO |
| have_openssl | NO |
| init_file | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| 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_mirrored_log_groups | 1 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 8388600 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| log_long_queries | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 500 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 256 |
| myisam_max_sort_file_size | 4294967295 |
| myisam_recover_options | 0 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| open_files_limit | 0 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 131072 |
| record_rnd_buffer | 131072 |
| query_buffer_size | 0 |
| safe_show_database | OFF |
| server_id | 0 |
| slave_net_timeout | 3600 |
| skip_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer | 2097144 |
| sql_mode | 0 |
| table_cache | 64 |
| table_type | MYISAM |
| thread_cache_size | 0 |
| thread_stack | 65536 |
| transaction_isolation | READ-COMMITTED |
| timezone | EST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| version | 3.23.58 |
| wait_timeout | 28800 |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

I've spent about 6 hours beating my head against my desk to no avail...thanks in advance for any help!!!

Vigile

Fri 12th Mar '04, 5:11pm

Because more information never hurts, heres a bit.

Even when executing a VERY simple query like so:

UPDATE `IOs` SET `Name` = 'Standardasdd' WHERE `ID` = '1' LIMIT 1 ;

If I run myisamchk on it, the table seems to remain open:

[root@localhost MBPlatforms]# myisamchk -s *.MYI
myisamchk: MyISAM file IOs.MYI
myisamchk: warning: 1 clients is using or hasn't closed the table properly
MyISAM-table 'IOs.MYI' is usable but should be fixed

Vigile

Fri 12th Mar '04, 5:31pm

Vigile

Sat 13th Mar '04, 1:30pm

bump....please?

Vigile

Sat 13th Mar '04, 5:58pm

Scott MacVicar

Sat 13th Mar '04, 6:05pm

It isn't caused by badly coded scripts or badly designed databases.

You'll need to not do the analyze table has this forces a lock and whatever poll_ip is is causing problems.

Maybe consider switching those tables to the innodb table type which has row locking rather than table locking and fixing the scripts

Vigile

Sat 13th Mar '04, 6:18pm

FIrst, what do you mean by "need to not do the analyze table"? I don't see an analyze table command in there...?

Secondly, what do you mean at the end by "fixing the scripts" if you said it wouldn't be caused by badly coded scripts?
*EDIT* Oh, I see, you are saying that poll_ip script is causing problems? I don't know if that is true, because all scripts that I have been running have caused this same problem at point or another. Even ones not written by me.

Lastly, would upgrading to mysql 4.0.x help this ?

Scott MacVicar

Sat 13th Mar '04, 6:28pm

I suspect its bad database design thats causing the problem.

REPLACE INTO motherboards is causing the first locking
INSERT INTO memoryslots
INSERT INTO poll_ip

It could be caused by memory allocated incorrectly for buffers though.

MySQL 4 is the most stable release so an upgrade would be a good idea.