MySQL Forums
Forum List  »  Performance

Strange lock on simple query
Posted by: Frank Grunwald
Date: February 22, 2005 12:46PM

I experienced some kind of lock on a very simple query :
SELECT id_user FROM Session WHERE id_session='xxxxxx';
The Session table only has a few fields and id_session is the primary key and has about 15000 records.

I found this disturbing information in the slow-query log :
# User@Host: xxx[xxx] @ [xxx.xxx.xxx.xxx]
# Query_time: 100 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
select id_user FROM session WHERE id_session='xxxxxxxxxx';

This is the context :
x Bi - 3.0 GHZ 64 bit CPU
x 6 Go RAM
x RedHat
x MyIsam table (1.5 Go of Key_Buffer)

This server is performing about 2K to 3K queries per second (and was on a quite heavy load when this thing happened)
The only strange thing I noticed is the ratio key_reads / key_read_requests : about 92% on Cache hits (usually it is about 99%)

At the same time, a lot lot of query get stuck (they lengthed for about 80 sec) but they were a bit more complicated than this one ...

In fact it looks like everything freezed for a few seconds .. I've already seen that kind of things a few times before but that's the first time it is so bad

If anyone got an idea (even a clue) thanks for your help
(Let me tell I won't go to sleep before understanding this ;))

Frank

Options: ReplyQuote


Subject
Views
Written By
Posted
Strange lock on simple query
2460
February 22, 2005 12:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.