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
--
FOKUS - Fraunhofer Insitute for Open Communication Systems
Project BerliOS -
http://www.berlios.de