Tables get locked and won't unlock

MySQL & MariaDB Other MySQL® Questions
1

Current Server hardware

MySQL Ver 14.12 Distrib 5.0.27
RHEL vs 5
584GB Raid 5 storage
8GB of RAM (I’m working on getting a 64bit OS to install so I can use all 8GB)
Dual 5130 processors (2.0GHz Intel Dual-Core Xeon)

Current my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
wait_timeout=30
default-character-set=utf8
max_allowed_packet = 14M
max_connections = 600
ft_min_word_len = 3

key_buffer_size = 2000M
sort_buffer = 9M
#read_rnd_buffer_size = 3M

table_cache = 350

server-id=1
log-error = /var/log/mysql/error.log
expire_logs_days = 3

Default to using old password format for compatibility with mysql 3.x

clients (those using the mysqlclient10 compatibility package).

old_passwords=0

[mysql.server]
user=mysql

[mysqld_safe]
err-log=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Everything is stored in MyISAM.

Tables range from small to largest being 500k Rows with size of 1.9GB (incl indexes) which is wrote to constantly.

(any other info I’ll gladly provide if needed).

Here’s my dilemma. The server runs fine for long periods of time… then all of a sudden tables Lock and queries build up … and just don’t catch up and easiest thing to do is do a restart of mysql to clear everything out.

I’ve noticed that sometimes when I run a large search on a table after after 60 seconds of searching that table (I’d like to get that down) Tables start locking and won’t unlock unless I kill the Search.

The search is searching over three FULL Text indexes in Boolean mode.

I am thinking my config is screwed up and needs tweaked but I’m at a miss on how to fine tune.

so I seek help here.

thanks.

2

This is just one thing that I’ve seen cause this. It might not be the problem you’re having.

I’ve seen this happen before when queries get executed on a table in the following order (from different threads):

  • A SELECT that takes a long time to execute
  • An UPDATE that modifies one of the same tables locked by that select

Any other queries (SELECTs included) that require that table will wait for the UPDATE to complete before they will execute, even if they are very simple queries that should be fast (this is because UPDATEs have higher priority than SELECTs).

I’ve tried UPDATE LOW_PRIORITY and SELECT HIGH_PRIORITY when I’ve had this happen in the past, but not with much luck. We ended up reworking our architecture to remove the need for UPDATEs (we use multiple tables, SELECTs, and INSERTs). Obviously that might not work for your application, though.

InnoDB might solve this problem because it supports row locking.

3

that’s exactly what is happening I bet… the select takes a long time, then an update comes in but can’t lock so it waits and then all new selects lock as well.

gotta love that. thanks for the reply

4

Yes it is likely to be updates stucking the queue as they are given priority. Check if running with low_priority_updates enabled solves the issue.

The other possiblity is stale table lock - when connection locks the table explicitly and then is not closed for a while.