MySQL Performance: MySQL => Tables get locked and won't unlock

Members

Help

Home

Home » Performance » MySQL » Tables get locked and won't unlock

Show: Today's Messages :: Show Polls :: Message Navigator

Tables get locked and won't unlock [message #1736]

Tue, 04 September 2007 16:29

JustinDC
Messages: 2
Registered: September 2007

Junior Member

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.

Report message to a moderator

Re: Tables get locked and won't unlock [message #1745 is a reply to message #1736 ]

Wed, 05 September 2007 09:31

james
Messages: 17
Registered: September 2007
Location: Arlington, TX

Junior Member

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.

Report message to a moderator

Re: Tables get locked and won't unlock [message #1746 is a reply to message #1745 ]

Wed, 05 September 2007 09:35

JustinDC
Messages: 2
Registered: September 2007

Junior Member

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

Report message to a moderator

Re: Tables get locked and won't unlock [message #1752 is a reply to message #1736 ]

Wed, 05 September 2007 10:23

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

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.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Previous Topic:	Surprising: Where a quote usage dramatically slows down a request
Next Topic:	SELECT * FROM thistable ORDER BY date

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 8 19:43:28 EST 2009

Total time taken to generate the page: 0.02216 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.