> Any thoughts? What kind of internal locks might be generated? Is there
> another command I can run to get the status of those queries that are
> hanging? It shows 'None' for the state (NULL), I've never seen a
Aha.. So I had a brainstorm while driving to lunch. Is it possible
this is related to the query cache? Obviously we did not have a query
cache before we upgraded to 4.0.
Over night, the heavy jobs running inserts millions of records into
Table A, but basically does hundreds of thousands of select's from Table
B. Table B is the table we're seeing the insert into that appears to be
freezing all the queries on the server (including 'select 1').
Presumably, if this is the very first insert into this table, the query
cache must be flushed for that table. I am assuming the query cache is
nearly full of queries against that table (as the system has been idle
since this job finished). Our query cache is 512M max. Can it really
take 25 seconds to clear the query cache of all those queries? Would
that hang all the queries in the manner we are seeing (Including 'select
1')? The Insert would be in the 'update' state while clearing the query
cache?
I can update my script to also pull back 'show status' from the server
tonight and see what the query cache is doing at the same time. If it
does turn out to be a query cache lock, what course of action can we
take? Periodic 'reset query cache', throughout the night? Is a query
cache of 512M beyond the recommended size? I wouldn't have expected it
to take 25 seconds to clear out a single table's query cache, is this
possible?
Thanks,
-Joe
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
