Re: Slow queries on 4.0.13, FreeBSD 4.8 STABLE, linuxthreads, zawodny blog

[LS]

Thanks for the response! Sorry, I should have put more info in there in the first
place. The select and update queries you see below are operating on two different
databases, and the lock times are 0:

# Time: 030523 17:34:28
# User@Host: scott[tiger] @  [a.b.c.d]
# Query_time: 23  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
update my_big_table set some_flag='Y';
# User@Host: scott[tiger] @  [a.b.c.d]
# Query_time: 2  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use other_db;
insert into status (hostname, snapshotTimestamp, m1, m2, total) values
('host1','2003-05-23 17:34:26',252049128,1555431424,1782);

The update takes a while to churn through, which is normal. The insert takes 2
seconds to be processed, which is the unusual part. 

The machine is a dual-Xeon 2.4GHz with 4GB RAM and is reporting plenty of idle CPU
according to vmstat, even with 800qps reported through STATUS. The 4.0.13 was
compiled using gcc 3.2 with linuxthreads. 

I have other examples from the log showing INSERT DELAYED queries taking 2 seconds
to complete, which also worries me.

What's more, I am only able to get this to happen sometimes. I tried to force these
same queries to happen side by side so that I could run SHOW PROCESSLIST and maybe
get more information, but the insert query didn't get held up. But it happens
enough that I went to the trouble of migrating from 3.23 to 4.0.13 to try to avoid
it. ;-) 

A footnote: I had the same system originally running on a RH8.0 box (4.1.0-alpha
actually) with similar hardware, and didn't notice this slow query problem, which
coincides with Jeremy's blog entries. But right now I am trying to find a solution
on FreeBSD.

Any clues?


--- Dan Nelson <dnelson@stripped> wrote:
> In the last episode (May 23), LS said:
> > On my FreeBSD 4.8 machine running 4.0.13 with linuxthreads, I am seeing the
> > symptoms described in Jeremy's blog here
> > (http://jeremy.zawodny.com/blog/archives/000203.html):
> > 
> > The server is handling 800qps, and the slow query log is showing
> > hundreds of cases where trivial unrelated INSERT DELAYED queries are
> > being held up for 2 seconds. Is there something else I need to do to
> > get these slow queries to go away?
> 
> If you have a mix of INSERT and DELETE queries on a table, MySQL will
> not be able to do concurrent INSERT and SELECT queries (concurrent
> access only works if MySQL is able to append the inserted records to
> the end of the table).  It could be that your slow INSERT queries are
> waiting on the table lock of a complicated SELECT.  Show us a couple of
> the timing lines (the ones starting with "# Query_time:") from your
> slow-query log.  If Lock_Time: is nonzero, that means your query is
> waiting on the table lock.
> 
> -- 
> 	Dan Nelson
> 	dnelson@stripped


__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com