Agent Performance Detail locking database after upgrade

[jshasteen]

I recently upgraded my cluster by moving from a 4 server cluster to a 5 server cluster and also moving to Vicidial 2.12 from 2.10. Install was done using ViciBox 6.0.4. Before the upgrade I had a DB and WEB server with three telephony servers. Now I have a separate web server a separate database server and three telephony servers for a total of 5 servers. This cluster supports 35 agents. Some may even consider this overkill.

Before the upgrade the following server was doing BOTH WEB AND DATABSE duties, now it's only functioning as a WEB SERVER.

openSUSE 13.1 (x86_64)
3.11.10-21-default
Dell 2900
2x Intel(R) Xeon(R) CPU X5355 @ 2.66GHz (4 cores per CPU)
16GB DDR2
Dell PERC6/i - MegaRAID SAS 1078
Driver Modules: "megaraid_sas"
4 146GB 15k SAS drives in RAID 10

Here are the specs for my DATABASE ONLY SERVER:

Using settings from my-big.cnf
openSUSE 13.1 (x86_64)
3.11.10-29-default
Dell R710
2x Intel(R) Xeon(R) CPU E5620 @ 2.40GHz (4 cores, 8 threads per CPU)
36GB DDR3
Dell PERC H700 Integrated - LSI MegaSAS 9260
Driver Modules: "megaraid_sas"
6 146GB 15k SAS drives in RAID 10



Upgraded from 2.10 to:
Version: 2.12b0.5
SVN Version: 2561
DB Schema Version: 1462


The upgrade was not necessarily to address any performance issues but I wanted to 'beef' up my cluster and allow for future expansion. Now after the upgrade it seems that the web interface is less responsive and the real issue is that any user report (Agent performance detail specifically) 'locks' the 'vicidial_users' table and prevents agents from working until the report is complete. Prior to the upgradde we could run this report and it would complete without any issues in a matter of seconds, not minutes. I did not have that issue before the upgrade and find it really odd considering I moved to a cluster with a dedicated database with more power than the hardware that was doing both db and web.

Can anyone shed some light on this? What could be causing this. Maybe Vicibox 7 would have 'better' HD controller drivers since the OS is newer? Please help.

Thank you.
.


** The system in my signature is not the system I am referring to and irrelevant to my questions/issue above

[mflorell]

What specific queries are locking the database?

What was the version and build of admin.php prior to your upgrade?

Have you thought of getting a Slave DB to run reports on?

[williamconley]

It could be related to the transfer of data from the DB server to the Web server (which was local before). In the new configuration, all the data must be transferred to the php/apache/web server and then (after that is completed) processed in php and sent out through apache as html. It's entirely possible you would get the original results if you were running the report directly on the DB server.

We've had a few clients with similar experience (but not recently).

How's the network connectivity between the servers in the cluster? All Gigabit?

[jshasteen]

Thank you for the feedback!

What specific queries are locking the database?

Waiting for table level lock | UPDATE vicidial_agent_log set dispo_sec='0' where dispo_sec>43999 and event_time > "2016-08-18 20:03

What was the version and build of admin.php prior to your upgrade?

Not sure about this one. In the future I will take note of the version prior to upgrading.

Have you thought of getting a Slave DB to run reports on?

Great idea. That may be the path of least resistance.

[jshasteen]

It could be related to the transfer of data from the DB server to the Web server (which was local before). In the new configuration, all the data must be transferred to the php/apache/web server and then (after that is completed) processed in php and sent out through apache as html. It's entirely possible you would get the original results if you were running the report directly on the DB server.

We've had a few clients with similar experience (but not recently).

How's the network connectivity between the servers in the cluster? All Gigabit?

Yes, it's gigabit but I think the switch may be a little over utilized. I need to restructure things a bit but I have been putting it off.


Thank you for the input!