call rundim(): #1205 Lock wait timeout exceeded; try restarting transaction

[ray58750034]

archive hourly task always failed, and try to call rundim() throws "#1205 Lock wait timeout exceeded; try restarting transaction" exception.

Database: razor_fact_clientdata has 852,309 records, while razor_fact_usinglog has 1,301,013 records.

Do you have any idea how to optimize DB?

[cobub]

can you provide which SQL is locked in rundim?

[ray58750034]

Restart mysql solve this problem.

But the cron tasks cost too long time now, so we stopped the tasks.

When I "call rundim()" manually, the procedure cost 180 seconds.
call runfact('2013-02-07 10:00:00','2013-02-07 10:59:59') cost 25 seconds.
call runsum('2013-02-07 10:00:01') cost 210 seconds.

The max table I found in razor database is razor_clientusinglog ~ 3,419,202 records.

[cobub]

It seems that speed of Store procedure is normal, maybe the cron task executed timeout.
Any more information can be provided about this?