sometimes very simple queries are extreemly slow

MySQL & MariaDB Other MySQL® Questions
1

Hello,

We are facing some strange problem here. Sometimes, mysql is performing a very simple queries.

A good example is e.g. (from slow query log)

User@Host: user[user] @ [62.x.x.x]

Query_time: 14 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SET timestamp=1171200111;
select unix_timestamp();

So it looks like all queries are collescted, like they are waiting for something, and then all of them are performed, the same moment.

All tables are MyISAM. When looking on those “hanging” processes with mysql processlist, their status is NULL.

Sometimes I could notice that with those NULL process one or two others are on status UPDATE or SENDING DATA status. Although tables involved in working queries are not the one related to NULL processes.(And even unix_timestamp() is hanging).

What I also understood from mysql docs is that NULL status is not possible for any long period.

Please, any advice would be welcome, because I totally stuck overhere :frowning:

Problem is noticed on 5.0.18 and 5.0.27 both.

thanks,
Sergey

2

This is very strange.

Did you notice anything specific of system load when this happens and which OS version do you use ?

If you notice hanging of such simple query I’d connect to MySQL with GDB to trace where exactly it is waiting.

It could be some condition notification gets lost or something similar.

select unix_timestamp() should not even lock any tables so it should be very low on mutexes.

3

Oke, issue came up to a bug in mysqld when setting a large query cache size (500MB in my case).

When cache is off it works fine.