Hi all
Today we had a problem with the toolserver.namespace table being blocked on sql-s1. After some digging, I think I have identified the cause: there was a very long running solect on that table. By itself, that wouldn't be a problem. But once a day, the table gets updated from the master copy on zedler - so the slow select blocked that update, and the pending update blocked any further select. This way, all tools trying to use toolserver.namespace on sql-s1 were effectively dead.
To avoid this, please don't run very slow queries on the toolserver.* tables. If you have an idea how this kind of lockout can be avoided, perhabs be a smarter way to copy the table from the master, I'd be happy to hear it. Currently, i sumpl create a copy with mysqldump and then improt it.
-- daniel
On Wed, Sep 2, 2009 at 7:52 AM, Daniel Kinzlerdaniel@brightbyte.de wrote:
Today we had a problem with the toolserver.namespace table being blocked on sql-s1. After some digging, I think I have identified the cause: there was a very long running solect on that table. By itself, that wouldn't be a problem. But once a day, the table gets updated from the master copy on zedler - so the slow select blocked that update, and the pending update blocked any further select. This way, all tools trying to use toolserver.namespace on sql-s1 were effectively dead.
To avoid this, please don't run very slow queries on the toolserver.* tables. If you have an idea how this kind of lockout can be avoided, perhabs be a smarter way to copy the table from the master, I'd be happy to hear it. Currently, i sumpl create a copy with mysqldump and then improt it.
(copied from my e-mail to admins@toolserver.org)
That doesn't normally happen with InnoDB, AFAIK. Selects don't block updates; the selects just read the old, un-updated rows as the update proceeds, using MVCC. Uncommitted updates don't even necessarily block ordinary selects AFAIK -- again, the select can just get a consistent read of the old version. See here for details (and other pages in the section):
http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html http://dev.mysql.com/doc/refman/5.1/en/innodb-lock-modes.html
So an ordinary select should not take out any locks, and therefore not block any other statement at all from occurring except maybe things like ALTER TABLE. However, selects do take out locks if used with LOCK IN SHARE MODE or FOR UPDATE, or if they're run at SERIALIZABLE isolation level. I would guess one of those was the culprit, without being able to see the exact query.
Aryeh Gregor schrieb:
That doesn't normally happen with InnoDB, AFAIK. Selects don't block updates; the selects just read the old, un-updated rows as the update proceeds, using MVCC. Uncommitted updates don't even necessarily block ordinary selects AFAIK -- again, the select can just get a consistent read of the old version. See here for details (and other pages in the section):
http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html http://dev.mysql.com/doc/refman/5.1/en/innodb-lock-modes.html
So an ordinary select should not take out any locks, and therefore not block any other statement at all from occurring except maybe things like ALTER TABLE. However, selects do take out locks if used with LOCK IN SHARE MODE or FOR UPDATE, or if they're run at SERIALIZABLE isolation level. I would guess one of those was the culprit, without being able to see the exact query.
Actually, I think the problem is not an UPDATE or INSERT, the problem is that I use mysqldump to make a copy and then import the resulting dump - which starts by *dropping* the table and re-creating it. Apparently, the DROP is not performed while a SELECT is in progress (makes sense).
A workable solution would be to suppress the DROP (there's an option for that) and to use REPLACE instead of INSERT - but that is only supported by mysqldump since 5.1.3 apparently, we are on 5.0.something. Also, it would not propagate row deletions - not a big deal in this case though, since rows rarely get killed from toolserver.* tables. Seems like I need to write my own mysqlcopy or something...
-- daniel
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Daniel Kinzler:
A workable solution would be to suppress the DROP (there's an option for that) and to use REPLACE instead of INSERT - but that is only supported by mysqldump since 5.1.3 apparently, we are on 5.0.something.
you will find a more recent version on willow.
% mysqldump -V mysqldump Ver 10.13 Distrib 5.1.35, for pc-solaris2.10 (x86_64)
- river.
River Tarnell schrieb:
Daniel Kinzler:
A workable solution would be to suppress the DROP (there's an option for that) and to use REPLACE instead of INSERT - but that is only supported by mysqldump since 5.1.3 apparently, we are on 5.0.something.
you will find a more recent version on willow.
% mysqldump -V mysqldump Ver 10.13 Distrib 5.1.35, for pc-solaris2.10 (x86_64)
- river.
Ah, thanks for the hint. Running on willow now, with --no-create-db --no-create-info --skip-add-drop-table --replace. Seems to do the trick.
-- daniel
_______________________________________________ Toolserver-l mailing list (Toolserver-l@lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/toolserver-l Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette
On Thu, Sep 3, 2009 at 3:23 AM, Daniel Kinzlerdaniel@brightbyte.de wrote:
Actually, I think the problem is not an UPDATE or INSERT, the problem is that I use mysqldump to make a copy and then import the resulting dump - which starts by *dropping* the table and re-creating it. Apparently, the DROP is not performed while a SELECT is in progress (makes sense).
Yes, that would definitely do it. It will also break transactions and do all sorts of other bad things. You're not meant to drop tables in the course of ordinary operation.
A workable solution would be to suppress the DROP (there's an option for that) and to use REPLACE instead of INSERT - but that is only supported by mysqldump since 5.1.3 apparently, we are on 5.0.something. Also, it would not propagate row deletions - not a big deal in this case though, since rows rarely get killed from toolserver.* tables. Seems like I need to write my own mysqlcopy or something...
You can just replace the DROP TABLE with DELETE FROM and it should work fine. It will take out more locks than strictly necessary, but it's unlikely to be a problem for us. Everything should be wrapped in BEGIN/COMMIT so the intermediate state is hidden. This could be done easily and reliably with regex on mysqldump's output.
toolserver-l@lists.wikimedia.org