search

cobub / razor

Cobub Razor - Open Source Mobile Analytics Solution
http://www.cobub.com
1.13k stars 431 forks source link

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

Open ray58750034 opened 11 years ago

ray58750034 commented 11 years ago

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 commented 11 years ago

can you provide which SQL is locked in rundim?

ray58750034 commented 11 years ago

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 commented 11 years ago

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