vicidial.org

Hi!
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

Your version is over a year old, and I know I have made some changes to the codebase since then that have fixed some of these issues. I would suggest upgrading to the latest svn/trunk version.

Will do and revert back here with the outcome.

So we finally had the opportunity to upgrade Vicidial versions and this vicidial_agent_log table still locks up from time to time. We do notice a trend when there are sufficient amount of agents in a pause status the table starts locking up. These are mostly ratio dial mode campaigns.

This has been escalated to Vicidial.com Support, I will post any progress here.

What size is that table? Do you archive it? There is a log archive script for such a purpose to prune old data and reduce table size ...

The vicidial_agent_log table is around 4 million records - I'll check. There is an archiving process every second day now just to try keep it as small as possible.

4 million AFTER you pruned it?

That sounds about right for overload on many systems (depending on i/o response time and the power of the system in general). If you archive that using the built-in script, all older records will push to vicidial_agent_log_archive (so they aren't actually "gone"). That would likely resolve your issue based on what I'm reading here.

Table vicidial_agent_log count(*) returns 2298439 a couple hours after being archived. This is quite a busy server "cluster".

Here is my archive job:

Code: Select all

00 2 1,3,5,7,9,12,15,17,19,21,24,25,26,27,28,29,31 * * /usr/share/astguiclient/ADMIN_archive_log_tables.pl --months=1