MySQL table locks and scalability
Added by Vincent Swart about 12 years ago
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.
Go to top