Hi,

we have a site that is getting increasingly busy.
In preparation of that we purchased a server that should be able to handle
roughly 10 times our current traffic.

So, as far as we can tell we should be able to handle any requests for the
time been.

But for the first time today we had one of our table locked "IN USE" for at
least 15 minutes.
I doubt it was a hardware problem, so I guess that the table was locked by
code.
But what code could cause the table to be locked for so long?

We carefully looked at the code and we never have more than 4 or 5 queries
per pages.
So, what could flag a table as "IN USE"?

Many thanks,

Simon
--
http://urlkick.com/
Free URL redirection service. Turns a long URL into a much shorter one.

>we have a site that is getting increasingly busy.
>In preparation of that we purchased a server that should be able to handle
>roughly 10 times our current traffic.
>
>So, as far as we can tell we should be able to handle any requests for the
>time been.
>
>But for the first time today we had one of our table locked "IN USE" for at
>least 15 minutes.

What was the exact text of the error message?
What was the query that got this error message?

For example, mysqldump with the --lock-tables option potentially locks
tables for a long time if there's a lot of data to dump or it's
going to a slow destination.

If some types "flush tables with read lock" at a mysql command prompt
and goes on coffee break, that can lock things up until he comes back.

>I doubt it was a hardware problem, so I guess that the table was locked by
>code.
>But what code could cause the table to be locked for so long?
>
>We carefully looked at the code and we never have more than 4 or 5 queries
>per pages.

A single query that does a 5-way join on tables with 10 million rows
with an accidentally forgotten WHERE clause might take days. Or
centuries.

>So, what could flag a table as "IN USE"?

What TOLD you a table was in use?

Gordon L. Burditt

"Gordon Burditt" <gordonb.obk92@burditt.org> wrote in message
news:124mbjhmf04emb1@corp.supernews.com...
>
> What was the exact text of the error message?
> What was the query that got this error message?

See bellow.

>
> For example, mysqldump with the --lock-tables option potentially locks
> tables for a long time if there's a lot of data to dump or it's
> going to a slow destination.
>
> If some types "flush tables with read lock" at a mysql command prompt
> and goes on coffee break, that can lock things up until he comes back.

No, we had no one using the command prompt at the time, the problem was with
the php code.
>
>
> A single query that does a 5-way join on tables with 10 million rows
> with an accidentally forgotten WHERE clause might take days. Or
> centuries.

We have no such queries. The more complex query is a SELECT on 3 tables at
the same time, that query is used for 000's of page views a day.

>
>>So, what could flag a table as "IN USE"?
>
> What TOLD you a table was in use?
>

First of all, all our warning messages are not displayed, so no error
message actually makes it to the browser.

But we noticed a problem in the display, and we also noticed that we could
no longer log on.
So I immediately used phpmyadmin to check what the problem might be.

When I looked at the database structure, the "user" table was simply flagged
as "In use".
I selected that table and chose the "repair" option.

I don't know if that helped, but after the table was repaired, the table was
no longer "In Use" and everything was back to normal.

The whole thing took about 15 minutes.

That is why I don't know what caused the problem in the first place. We only
have about 100 queries in the whole site, almost all of them are "select".
But I never heard of the "In Use" case. I don't think a "select" statement
can do that, maybe an "update" or "Insert" that didn't work.

Simon
--
http://urlkick.com/
Free URL redirection service. Turns a long URL into a much shorter one.

Do you use a program to enter your queries? I know of a database utility
(forgot the name, but it was a commercial program) that used
transactions by default. This meant that updates would NOT show in an
InnoDb table unless you closed the program's view of it and re-opened it
again. Sometimes it made the localhost webserver "hang" because the
viewing program locked a table for an update by the webserver.

Hope this can help,
Willem Bogaerts

Simon wrote:
> "Gordon Burditt" <gordonb.obk92@burditt.org> wrote in message
> news:124mbjhmf04emb1@corp.supernews.com...
>> What was the exact text of the error message?
>> What was the query that got this error message?
>
> See bellow.
>
>> For example, mysqldump with the --lock-tables option potentially locks
>> tables for a long time if there's a lot of data to dump or it's
>> going to a slow destination.
>>
>> If some types "flush tables with read lock" at a mysql command prompt
>> and goes on coffee break, that can lock things up until he comes back.
>
> No, we had no one using the command prompt at the time, the problem was with
> the php code.
>>
>> A single query that does a 5-way join on tables with 10 million rows
>> with an accidentally forgotten WHERE clause might take days. Or
>> centuries.
>
> We have no such queries. The more complex query is a SELECT on 3 tables at
> the same time, that query is used for 000's of page views a day.
>
>>> So, what could flag a table as "IN USE"?
>> What TOLD you a table was in use?
>>
>
> First of all, all our warning messages are not displayed, so no error
> message actually makes it to the browser.
>
> But we noticed a problem in the display, and we also noticed that we could
> no longer log on.
> So I immediately used phpmyadmin to check what the problem might be.
>
> When I looked at the database structure, the "user" table was simply flagged
> as "In use".
> I selected that table and chose the "repair" option.
>
> I don't know if that helped, but after the table was repaired, the table was
> no longer "In Use" and everything was back to normal.
>
> The whole thing took about 15 minutes.
>
> That is why I don't know what caused the problem in the first place. We only
> have about 100 queries in the whole site, almost all of them are "select".
> But I never heard of the "In Use" case. I don't think a "select" statement
> can do that, maybe an "update" or "Insert" that didn't work.
>
> Simon

"Simon" <spambucket@example.com> wrote:
> "Gordon Burditt" <gordonb.obk92@burditt.org> wrote in message
> news:124mbjhmf04emb1@corp.supernews.com...
>>
>> What was the exact text of the error message?
>> What was the query that got this error message?
>
> See bellow.

As far I can see, you actually never had an error.

>> A single query that does a 5-way join on tables with 10 million rows
>> with an accidentally forgotten WHERE clause might take days. Or
>> centuries.
>
> We have no such queries. The more complex query is a SELECT on 3 tables at
> the same time, that query is used for 000's of page views a day.

So it is a 3-way JOIN anyway. Whithout proper indexes such a query
*could* lead to significant database contention. Especially if it is
executed often.

>>>So, what could flag a table as "IN USE"?
>>
>> What TOLD you a table was in use?

> I ... used phpmyadmin to check what the problem might be.

Why not using the MySQL tools like 'mysqladmin' or just 'mysql'?
phpMyAdmin is not a particularly good choice for troubleshooting
a database (reason: it quite often adds extra trouble).

> When I looked at the database structure, the "user" table was simply flagged
> as "In use".

I don't know what phpMyAdmin wants to express with that. I would
expect *every* table in a database to be "in use" somehow.

> I selected that table and chose the "repair" option.

So you don't know what phpMyAdmin wanted to say either. You acted
without knowing the problem. Doing REPAIR TABLE is not a good choice
unless the table is *really* broken. If you have a problem with lock
contention - I would expect that - REPAIR TABLE will not fix it.
Instead it will add extra locks on the table; as long as REPAIR TABLE
is running, nobody can write on that table.

There are several measures to troubleshoot a contention problem:

1. Let the MySQL server write a slow query log. Examine queries from
the slow-log with EXPLAIN. Optimize tables/indexes/queries to avoid
slow queries. Look out for queries with a long 'Locked' time.

2. If you experience a congestion situation, run SHOW FULL PROCESSLIST
in the 'mysql' client (alternatively use 'mysqladmin -v processlist'
it's essentially the same). Look out for connections in the 'Locked'
state, find the query that causes the lock, optimize that query.
If you are in need for an instant solution, kill the query that
causes the lock (mysqladmin kill). However this should be seen as
a desparate measure. Don't do that regularly! Fix the real problem!

3. If optimization doesn't help: switch write-intensive tables to a
transactional engine (i.e. InnoDB). You should get rid of most of
the locks that way. If not, your application may need redesign.

Last not least, read the manual chapter on locking issues:
http://dev.mysql.com/doc/refman/5.0/...e-locking.html


more general comments:

> First of all, all our warning messages are not displayed, so no error
> message actually makes it to the browser.

That may not be the best decision. If you make sure no sensitive
information leaks, you might write SQL errors/warnings as HTML comments
into the output stream. The average user wouldn't notice that but your
engineers will get nice hints from it.

The better solution is, to write SQL errors and warnings to a separate
logfile. You could even write general diagnostics like the time needed
to get an answer to a certain query. If you use one of the typical
'database abstraction layers' for PHP, this is not too difficult.


HTH, XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/