I occasionally get the following SQL error when loading pages: UPDATE LOW_PRIORITY site_stats SET ss_total_views=(ss_total_views+1),ss_total_edits=(ss_total_edits), ss_good_articles=(ss_good_articles) WHERE ss_row_id=1 from within function "SiteStatsUpdate::doUpdate". MySQL returned error "1205: Lock wait timeout exceeded; Try restarting transaction".
This may directly be related to our hang problems. It appears to be an InnoDB problem according to a thread in
http://www.faqchest.com/prgm/mysql-l/mysql-02/mysql-0201/mysql-020115/
("InnoDB: Lock wait timeout exceeded")
The user there reports that he has this problem with a very large database and persistent connections. My summary of the ideas from the thread:
1) innodb_lock_wait_timeout in my.cnf could be adjusted. 2) In certain conditions, when AUTOCOMMIT is set to 0 or a table has been locked and not unlocked, every UPDATE/INSERT requires a COMMIT to unlock the row after the transaction. It may be advised to set AUTOCOMMIT to 1 before every action, or to encapsulate our SQL queries in BEGIN/COMMIT statements. 3) Don't use persistent connections.
These locking problems are most likely to occur with our site_stats table because it only has one row.
I assume these problems are solvable, as Kuro5hin is a very large dynamic site with InnoDB based tables. Any other ideas?
Regards,
Erik
On lun, 2003-01-27 at 05:55, Erik Moeller wrote:
I occasionally get the following SQL error when loading pages: ... MySQL returned error "1205: Lock wait timeout exceeded; Try restarting transaction".
...
The user there reports that he has this problem with a very large database and persistent connections. My summary of the ideas from the thread:
- innodb_lock_wait_timeout in my.cnf could be adjusted.
Attached is a copy of our my.cnf. Anyone who knows what the many magic numbers do is welcome to suggest improvements!
innodb_lock_wait_timeout is 50; if this is 50 seconds, we sure as heck do *not* want anything to take that long.
- In certain conditions, when AUTOCOMMIT is set to 0 or a table has been locked and not unlocked, every UPDATE/INSERT requires a COMMIT to unlock the row after the transaction. It may be advised to set AUTOCOMMIT to 1 before every action, or to encapsulate our SQL queries in BEGIN/COMMIT statements.
Autocommit is on by default.
I've put a couple of things additionally into begin/commit wrappers, but not everything. And as I recall, persistent connections can still be buggered if the connection gets _left_ partway through a transaction.
- Don't use persistent connections.
I've temporarily disabled them to see what happens. I also changed site_stats back to myisam for now.
Be back from class in a few hours... hopefully the site will still be up. :)
-- brion vibber (brion @ pobox.com)
wikitech-l@lists.wikimedia.org