Problem:
I've got an issue with vicidial_agent_log table locks manifesting in an issue on the agents' screen whereby the customer information takes too long to display on receiving a call.
I'm certain this is a result of table locks as shown in the full process list on the master database server.
I have the archiving process running every two days to keep the tables as trimmed as possible to avoid these long running queries with some success.
I've tried tweaking MySQL server variables and using mysqltuner to see any recommendations.
My findings:
The slow log queries show multiple similar queries at exactly the same epoch.
The only vitals graphs that spike are on load average to over 40, the number of running MySQL running threads to over 400 from under 25 and MySQL slow queries at exactly when these table locks occur.
No hard disk IO or util% bottleneck is evident.
There are at least 11 active campaigns running in manual and ratio modes of between 1 and 3.5 and at least 160 agents logged in.
There are at least 7 million records in the vicidial_list table but table locks on this do not appear at the time of the visible issue.
mysqltuner and logs show that there are logs of join queries that are not using indexes however these are not showing as slow queries at the time of the issue.
So far my recommendation to alleviate the issue is to log a campaign or two off and wait for the queries to return. This seems to happen at around the same time each day.
I'm out of ideas on how to alleviate this table from locking for so long (over a minute in some cases).
Perhaps this post belongs in a MySQL support forum.
Are my my.cnf variables way out or is the table just too big?
Some stats:
- Code: Select all
mysql> select min(event_time) from vicidial_agent_log;
+---------------------+
| min(event_time) |
+---------------------+
| 2013-07-01 07:22:16 |
+---------------------+
1 row in set (0.00 sec)
mysql> select max(event_time) from vicidial_agent_log;
+---------------------+
| max(event_time) |
+---------------------+
| 2013-07-30 10:59:11 |
+---------------------+
1 row in set (0.00 sec)
mysql> select count(*) from vicidial_agent_log;
+----------+
| count(*) |
+----------+
| 3899339 |
+----------+
1 row in set (0.00 sec)
Pastebins:
MySQL slow query log: http://pastebin.com/EC8tKAtp
MySQL config: http://pastebin.com/xuwjdjmz
MySQL full processlist1: http://pastebin.com/7shRgR4p
MySQL full processlist2: http://pastebin.com/i5DVr4WM
MySQL explain example queries: http://pastebin.com/PgB16j8g
MySQL describe table vicidial_agent_log: http://pastebin.com/nB1qupzx
mysqltuner.pl output: http://pastebin.com/0ksSwGLq
Web server crontab: http://pastebin.com/nFC01jna
--
Version: 2.6-372a BUILD: 120713-2123 from Vicibox 3.1.15 ISO and upgraded to astguiclient 2.6
Load: 0.03 0.06 0.02
HW Specs:
Master DB: Dual Xeon Hex E5630@2.53GHz 32GB RAM LSI MegaRAID SAS-926508i RAID10
Slave DB: Dual Xeon E5420@2.50GHz 16GB RAM LSI MegaRAID SAS 1078 RAID1
Dialer x7: Xeon Quad X5355@2.66GHz 8GB RAM SAS Perc5i RAID1 Sangoma VoiceTimer UT50
Web: Dual Xeon Quad E5420@2.50GHz 16GB RAM SAS Perc5i RAID1
Codecs: ulaw on softphones / g729 to carrier.
Termination: VoIP IAX
OS: DB: CentOS 6.2, Web+Dialers OpenSuse from Vicibox 3.1.15