vicidial_lists getting locked by AST_flush_DBQueue.pl

[rockgeneral]

Yesterday evening my agents were complaining that they couldn't make outbound calls. I ran "show full processlist;" and found that the vicidial_lists table was locked. There was a statement, OPTIMIZE table vicidial_lists which was also "Waiting for table metadata lock". When I killed this statement the lock on the table was released, all of the queued statements processed and the system began acting normally. To test I ran an OPTIMIZE table vicidial_lists statement from the command line and it too was "Waiting for table metadata lock" and needed to be killed. I hadn't rebooted in forever, so that evening I rebooted and then was able to execute the optimize on vicidial_lists and it completed as expected.

I ran a mysqlcheck medium check on the table and it came back normal, so I figured it just needed a reboot. Well, it is happening again today. Upon checking the various log files around the time it locked up, I found the script that is running the OPTIMIZE table vicidial_lists command. (AST_flush_DBqueue.pl) So every hour at the 11th minute this script runs, the vicidial_lists table gets locked and then I have to kill the command. The table only has 120 records in it. The optimize command should complete in milliseconds.

Does anyone have any ideas as to why the optimize table command would be hanging / locking?

Also, is it safe to rem the command out of the AST_flush_DBqueue.pl script for the time being while I sort this out?

[rockgeneral]

I've rem'd out the section of AST_flush_DBqueue.pl that runs the optimize table vicidial_lists statement. This is a temporary fix as I would like to get to the bottom of what is causing the optimize table vicidial_lists statement to get locked and fix it. It is as though it is locking itself. Locking the table while simultaneously being locked itself.

Also, out of curiosity, I was wondering why the vicidial_lists table would need to be optimized every hour? At least in my experience, the table doesn't get changed very often with the exception of the list_lastcalldate field.

Any insights would be greatly appreciated!

[mflorell]

If the vicidial_lists table is getting locked like that then you have some other kind of problem going on with your database server. A table that small should never stay locked for more than a second while being optimized.

It is optimized frequently because of the constant updates to the list_lastcalldate field every time a call is placed to a lead in that list.

[rockgeneral]

It almost looks as though the optimize table command is waiting for the vicidial_lists table to "unlock", but somehow it is locked by the optimize command itself. The processlist output shows this:

| 498011 | root | localhost | asterisk | Query | 30 | Waiting for table metadata lock | OPTIMIZE table vicidial_lists

Is it normal during an OPTIMIZE statement for the statement itself to be listed as "Waiting for table metadata lock"?

[mflorell]

No, that usually means there is something wrong with the server.

[rockgeneral]

Looks like it self-resolved. I commented it out of that section of the script for a day. Yesterday evening to test I manually ran the optimize table command fully expecting it to lock and it didn't. I've un-commented it and it has been running fine. Although I would like to know exactly what was causing the issue, it will likely remain a mystery. Hopefully, this was a one-off.

Thanks for your help.