MySQL table locks

[Vince-0]

Hi,
I have some issues with query concurrency on the following tables especially:
vicidial_list, vicidial_manager, vicidial_log, call_log .

These tables often show locked states with slow query log showing query times of sometimes over 2 seconds and lock times of up to 2 seconds.

I've tried some MySQL server variable tuning and also tried cleaning up the number of rows in each table but this is not helping. The vicidial_list table can easily have over 2 million records resulting in over 150 000 rows examined per query.

This is running on a dedicated database server with 2 Quad Core Xeon 2.5Ghz, 16GB RAM, SAS. The hardware is hardly stressed with a load average of around 2.00, under 10% disk utilization and under 2GB RAM used. I am aiming for well over 200 agents active on this multi-server system but these table locks are getting in the way of scalability and I don't understand why the hardware is so under utilized.

I want to use a MySQL cluster in stead of basic master-slave replication in view of supporting up to 1000 live agents in a mult-server system in the future.

Q: will the introduction of a MySQL cluster (trying 7.2 GA community release) be seamless to point GoAutoDial (latest) at and will this alleviate the query concurrency problem? IE: will ViciDial be able to use a cluster MySQL node transparently?

I will use professional support from the creators once this concept can be proven to scale to my needs. I look forward to the work involved with clustering each component: Asterisk, web server and MySQL and any advise will be greatly appreciated.

[mflorell]

Vicidial requires the MyISAM data engine type, which is not compatible with MySQL clustering, which is based upon INNOdb.

Vicidial is a real-time application that requires query queueing, something that is not enabled in InnoDB by default, and if you do enable it there can be serious performance issues at high load when compared to using MyISAM.

We have had success with vicidial clusters up to 500 seats using a well tuned MySQL master/slave combination and the right hardware, we haven't had the opportunity to test any larger installations than that.

If you could describe in more detail the operations, distribution, call handling method, call volume and other specifications we should be able to offer you some suggestions.

[Vince-0]

Currently 3 dialers, 1 web server, 1 database and 1 FTP server. 120 active agents with an average of 150 concurrent calls using mostly adaptive mode. The database and the web server are the beefiest hardware and we address the throughput problem on the DB first.

We are trying a risky migration of the problematic tables to MySQL cluster 7.2 / MySQL 5.5 right now with some success just for fun. I will let you know how it handles with production use under load.

One thing I don't understand with a master-slave replication configuration is how the application would know to use different hosts to read / write because there would only be one write master and the slaves would be read only.

Thanks for the advice Matt!

[mflorell]

With MySQL cluster you will most likely run into deadlock issues, that is where MySQL says it's busy and just drops the query you told it to run, it won't have it wait in line, it won't preserve query order, it just drops the query and spits out an error. This gets worse as load gets higher, and it causes all kinds of problems with Vicidial.

For master/slave you write everything to the Master server, and the slave can be used for reporting(configurable in System Settings) and as a hot spare if the Master has some kind of failure.

The most important parts of the DB server are:
- many CPUs/CPU cores
- as much RAM as possible
- a hardware caching RAID controller(we ONLY recommend LSI Logic MegaRAID)
- 15k RPM SAS/SCSI hard drives

The DB server is the most important part of your cluster, and a properly configured DB server can handle 500+ vicidial agents and thousands of phone lines.

[Vince-0]

I do see severe disc util%, w/sec and await spikes when the tables lock up and queries become back logged.

This particular server has a Dell perc 5i I think? SAS1068E PCI-Express Fusion-MPT SAS controller which we will replace with the higher performance one you've suggested.

We will also split the reporting to a slave replica and see how big it will scale.

Thanks for your help!

Vincent.

[mflorell]

The Dell Perc cards all have issues under high load in our experience, they are not designed for SMP Linux heavy RAID IO like the LIS MegaRAID caching RAID controllers are.