Professional Web Applications Themes

Table 'in use', how/why? - MySQL

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 ...

  1. #1

    Default Table 'in use', how/why?

    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
    --
    [url]http://urlkick.com/[/url]
    Free URL redirection service. Turns a long URL into a much shorter one.


    Simon Guest

  2. #2

    Default Re: Table 'in use', how/why?

    >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 Guest

  3. #3

    Default Re: Table 'in use', how/why?

    "Gordon Burditt" <gordonb.obk92burditt.org> wrote in message
    news:124mbjhmf04emb1corp.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
    --
    [url]http://urlkick.com/[/url]
    Free URL redirection service. Turns a long URL into a much shorter one.


    Simon Guest

  4. #4

    Default Re: Table 'in use', how/why?

    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.obk92burditt.org> wrote in message
    > news:124mbjhmf04emb1corp.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
    Dikkie Dik Guest

  5. #5

    Default Re: Table 'in use', how/why?

    "Simon" <spambucketexample.com> wrote:
    > "Gordon Burditt" <gordonb.obk92burditt.org> wrote in message
    > news:124mbjhmf04emb1corp.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:
    [url]http://dev.mysql.com/doc/refman/5.0/en/table-locking.html[/url]


    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: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

Similar Threads

  1. Replies: 0
    Last Post: September 16th, 04:37 PM
  2. Replies: 0
    Last Post: September 10th, 10:49 PM
  3. Replies: 0
    Last Post: September 10th, 05:33 PM
  4. Replies: 0
    Last Post: September 10th, 05:29 PM
  5. Replies: 2
    Last Post: August 12th, 07:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139