vicidial_live_agents keeps locking

[cyrenity]

We are running astguiclient for an inbound only campaign and getting constant db locks. most of the time the culprit is vicidial_live_agents table. this happens randomly, sometimes it runs without errors and problems for hours, and sometimes the LOCK statements become a nightmare, taking down the whole system down, restart mysql won't help and table again starts getting LOCKED. I have tried changing table engine (memory and myisam) but all in vain. Troubling "LOCK TABLES vicidial_live_agents" statement(s) always come from an asterisk server.

Here is a truncated "show processlist" output:

Code: Select all

mysql> show processlist;
+--------+------+--------------------+----------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id     | User | Host               | db       | Command | Time | State                           | Info                                                                                                 |
+--------+------+--------------------+----------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
|    831 | cron | xx.xx.xxx.59:33381 | asterisk | Sleep   |   16 |                                 | NULL                                                                                                 |
|    855 | cron | xx.xx.xxx.68:52646 | asterisk | Query   |    0 | starting                        | UPDATE server_updater set last_update='2015-02-27 14:45:43' where server_ip='xx.xx.xxx.68'           |
| 393148 | cron | xx.xx.xxx.61:38109 | asterisk | Query   |   63 | Waiting for table metadata lock | LOCK TABLES vicidial_live_agents WRITE                                                               |
| 394969 | cron | xx.xx.xxx.59:40535 | asterisk | Query   |   61 | Waiting for table metadata lock | SELECT user FROM vicidial_live_agents where server_ip='xx.xx.xxx.59' and status IN('PAUSED') and ext |
| 394970 | cron | xx.xx.xxx.61:38175 | asterisk | Query   |   61 | Waiting for table metadata lock | SELECT user FROM vicidial_live_agents where server_ip='xx.xx.xxx.61' and status IN('PAUSED') and ext |
| 394971 | cron | xx.xx.xxx.59:40536 | asterisk | Query   |   62 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where closer_campaigns LIKE "% SWT7000 %" and last_update_ |
| 394972 | cron | xx.xx.xxx.61:38176 | asterisk | Query   |   62 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where closer_campaigns LIKE "% SWT7000 %" and last_update_ |
| 394973 | cron | xx.xx.xxx.59:40537 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT count(*) FROM vicidial_live_agents where server_ip='xx.xx.xxx.59' and last_update_time < '201 |
| 394974 | cron | xx.xx.xxx.61:38177 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT user FROM vicidial_live_agents where callerid='Y2271440370000307785'                          |
| 395488 | cron | xx.xx.xxx.66:49632 | asterisk | Query   |   61 | Waiting for table metadata lock | SELECT user,extension,status,uniqueid,callerid,lead_id,campaign_id,call_server_ip FROM vicidial_live |
| 395494 | cron | xx.xx.xxx.66:49633 | asterisk | Query   |   62 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where closer_campaigns LIKE "% SWT7000 %" and last_update_ |
| 395516 | cron | xx.xx.xxx.66:49634 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT count(*) FROM vicidial_live_agents where server_ip='xx.xx.xxx.66' and last_update_time < '201 |
| 395613 | cron | xx.xx.xxx.68:57156 | asterisk | Query   |   61 | Waiting for table metadata lock | SELECT user,extension,status,uniqueid,callerid,lead_id,campaign_id,call_server_ip FROM vicidial_live |
| 395623 | cron | xx.xx.xxx.68:57157 | asterisk | Query   |   62 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where closer_campaigns LIKE "% LHR7000 %" and last_update_ |
| 395632 | cron | xx.xx.xxx.68:57158 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT count(*) FROM vicidial_live_agents where server_ip='xx.xx.xxx.68' and last_update_time < '201 |
| 396748 | cron | xx.xx.xxx.67:52729 | asterisk | Query   |   63 | Waiting for table metadata lock | select UNIX_TIMESTAMP(last_call_time) from vicidial_live_agents where lead_id='342330' and status='I |
| 396888 | cron | xx.xx.xxx.69:60899 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT count(*) FROM vicidial_live_agents where user='userid'            |
| 396995 | cron | xx.xx.xxx.62:53799 | asterisk | Query   |   63 | Waiting for table metadata lock | UPDATE vicidial_live_agents set random_id='15408776' where user='userid' |
| 396997 | cron | xx.xx.xxx.67:52829 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT external_hangup,external_status,external_pause,external_dial,external_update_fields,external_ |
| 396998 | cron | xx.xx.xxx.62:53800 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT external_hangup,external_status,external_pause,external_dial,external_update_fields,external_ |
| 396999 | cron | xx.xx.xxx.62:53801 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT external_hangup,external_status,external_pause,external_dial,external_update_fields,external_ |
| 397002 | cron | xx.xx.xxx.62:53806 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT external_hangup,external_status,external_pause,external_dial,external_update_fields,external_ |
| 397012 | cron | xx.xx.xxx.69:60953 | asterisk | Query   |   63 | Waiting for table metadata lock | UPDATE vicidial_live_agents set random_id='18279435' where user='userid' |
| 397016 | cron | xx.xx.xxx.67:52844 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT external_hangup,external_status,external_pause,external_dial,external_update_fields,external_ |
| 397018 | cron | xx.xx.xxx.67:52843 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT external_hangup,external_status,external_pause,external_dial,external_update_fields,external_ |
| 397021 | cron | xx.xx.xxx.69:60961 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT external_hangup,external_status,external_pause,external_dial,external_update_fields,external_ |
| 397027 | cron | xx.xx.xxx.69:60964 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT external_hangup,external_status,external_pause,external_dial,external_update_fields,external_ |
| 397031 | cron | xx.xx.xxx.69:60968 | asterisk | Query   |   63 | Waiting for table metadata lock | UPDATE vicidial_live_agents set random_id='17103522' where user='userid' |
| 397037 | cron | xx.xx.xxx.62:53831 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT external_hangup,external_status,external_pause,external_dial,external_update_fields,external_ |
| 397039 | cron | xx.xx.xxx.69:60969 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT external_hangup,external_status,external_pause,external_dial,external_update_fields,external_ |
| 397048 | cron | xx.xx.xxx.67:52856 | asterisk | Query   |   63 | Waiting for table metadata lock | UPDATE vicidial_live_agents set random_id='17133548' where user='userid'  |
| 397196 | cron | xx.xx.xxx.69:32783 | asterisk | Query   |   63 | Waiting for table metadata lock | select UNIX_TIMESTAMP(last_call_time) from vicidial_live_agents where lead_id='342285' and status='I |
| 397203 | cron | xx.xx.xxx.67:52878 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT status,callerid,agent_log_id,campaign_id,lead_id from vicidial_live_agents where user='userid'  |
| 397204 | cron | xx.xx.xxx.69:32790 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT status,callerid,agent_log_id,campaign_id,lead_id from vicidial_live_agents where user='userid' |
| 397205 | cron | xx.xx.xxx.62:53904 | asterisk | Query   |   63 | Waiting for table metadata lock | select UNIX_TIMESTAMP(last_call_time) from vicidial_live_agents where lead_id='342587' and status='I |
| 397207 | cron | xx.xx.xxx.62:53905 | asterisk | Query   |   63 | Waiting for table metadata lock | UPDATE vicidial_live_agents set random_id='14291153' where user='userid'  |
| 397208 | cron | xx.xx.xxx.58:43697 | asterisk | Query   |   63 | Waiting for table metadata lock | UPDATE vicidial_live_agents set random_id='18385981' where user='userid' |
| 397209 | cron | xx.xx.xxx.69:32797 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT status,callerid,agent_log_id,campaign_id,lead_id from vicidial_live_agents where user='userid' |
| 397210 | cron | xx.xx.xxx.62:53917 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT status,callerid,agent_log_id,campaign_id,lead_id from vicidial_live_agents where user='userid' |
| 397211 | cron | xx.xx.xxx.58:43701 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT external_hangup,external_status,external_pause,external_dial,external_update_fields,external_ |
| 397212 | cron | xx.xx.xxx.67:52887 | asterisk | Query   |   63 | Waiting for table metadata lock | UPDATE vicidial_live_agents set random_id='15923753' where user='userid' |
| 397213 | cron | xx.xx.xxx.67:52886 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT external_hangup,external_status,external_pause,external_dial,external_update_fields,external_ |
| 397214 | cron | xx.xx.xxx.55:38795 | asterisk | Sleep   |   63 |                                 | NULL                                                                                                 |
| 397215 | cron | xx.xx.xxx.58:43703 | asterisk | Query   |   63 | Waiting for table metadata lock | UPDATE vicidial_live_agents set random_id='17181695' where user='userid'  |
| 397223 | cron | xx.xx.xxx.58:43709 | asterisk | Query   |   63 | Waiting for table metadata lock | UPDATE vicidial_live_agents set random_id='15659998' where user='userid'  |
| 397225 | cron | xx.xx.xxx.62:53924 | asterisk | Query   |   63 | Waiting for table metadata lock | UPDATE vicidial_live_agents set random_id='13491614' where user='userid' |
| 397227 | cron | xx.xx.xxx.67:52893 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT lead_id,uniqueid,callerid,channel,call_server_ip,comments FROM vicidial_live_agents where ser |
| 397228 | cron | xx.xx.xxx.62:53925 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT status,callerid,agent_log_id,campaign_id,lead_id from vicidial_live_agents where user='userid' |
| 397236 | cron | xx.xx.xxx.62:53928 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'            |
| 397237 | cron | xx.xx.xxx.58:43714 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                      |
| 397239 | cron | xx.xx.xxx.67:52895 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT lead_id,uniqueid,callerid,channel,call_server_ip,comments FROM vicidial_live_agents where ser |
| 397241 | cron | xx.xx.xxx.62:53930 | asterisk | Query   |   63 | Waiting for table metadata lock | select extension,vicidial_live_agents.user,conf_exten,vicidial_live_agents.status,vicidial_live_agen |
| 397242 | cron | xx.xx.xxx.67:52897 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT lead_id,uniqueid,callerid,channel,call_server_ip,comments FROM vicidial_live_agents where ser |
| 397244 | cron | xx.xx.xxx.67:52899 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                     |
| 397249 | cron | xx.xx.xxx.62:53933 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'            |
| 397255 | cron | xx.xx.xxx.62:53953 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'             |
| 397256 | cron | xx.xx.xxx.67:52905 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'            |
| 397258 | cron | xx.xx.xxx.69:32813 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                     |
| 397259 | cron | xx.xx.xxx.58:43725 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                     |
| 397260 | cron | xx.xx.xxx.69:32815 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'            |
| 397264 | cron | xx.xx.xxx.62:53956 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'            |
| 397266 | cron | xx.xx.xxx.62:53959 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'            |
| 397267 | cron | xx.xx.xxx.58:43729 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                      |
| 397268 | cron | xx.xx.xxx.58:43730 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                     |
| 397269 | cron | xx.xx.xxx.58:43731 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'             |
| 397271 | cron | xx.xx.xxx.69:32816 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                     |
| 397274 | cron | xx.xx.xxx.58:43732 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                     |
| 397276 | cron | xx.xx.xxx.58:43734 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                     ||
| 397318 | cron | xx.xx.xxx.62:53970 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                     |
| 397322 | cron | xx.xx.xxx.55:38798 | asterisk | Query   |   63 | Waiting for table metadata lock | select closer_campaigns, count(*) as cnt from vicidial_live_agents where closer_campaigns IN (' ISB7 |
| 397324 | cron | xx.xx.xxx.58:43751 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'            |
| 397327 | cron | xx.xx.xxx.58:43753 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'            |
| 397330 | cron | xx.xx.xxx.69:32820 | asterisk | Query   |   63 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                     ||
| 397432 | cron | xx.xx.xxx.67:52943 | asterisk | Query   |   62 | Waiting for table metadata lock | select extension,vicidial_live_agents.user,conf_exten,vicidial_live_agents.status,vicidial_live_agen |
| 397434 | cron | xx.xx.xxx.67:52946 | asterisk | Query   |   61 | Waiting for table metadata lock | DELETE from vicidial_live_agents where user ='6782'                                                  |
| 397436 | cron | xx.xx.xxx.62:54038 | asterisk | Query   |   61 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'             |
| 397438 | cron | xx.xx.xxx.62:54040 | asterisk | Query   |   61 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                     
| 397451 | cron | xx.xx.xxx.58:43761 | asterisk | Query   |   59 | Waiting for table metadata lock | select extension,vicidial_live_agents.user,conf_exten,vicidial_live_agents.status,vicidial_live_agen |  |
| 397459 | cron | xx.xx.xxx.69:32872 | asterisk | Query   |   59 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                     |
| 397467 | cron | xx.xx.xxx.67:52956 | asterisk | Query   |   59 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                      |
| 397471 | cron | xx.xx.xxx.58:43764 | asterisk | Query   |   59 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'             |
| 397473 | cron | xx.xx.xxx.69:32879 | asterisk | Query   |   58 | Waiting for table metadata lock | UPDATE vicidial_live_agents set closer_campaigns=' KHI7000 -',last_state_change='2015-02-27 14:44:45 |
| 397476 | cron | xx.xx.xxx.58:43767 | asterisk | Query   |   56 | Waiting for table metadata lock | SELECT agent_log_id from vicidial_live_agents where user='userid'                                      |
| 397478 | cron | xx.xx.xxx.67:52961 | asterisk | Query   |   54 | Waiting for table metadata lock | select extension,vicidial_live_agents.user,conf_exten,vicidial_live_agents.status,vicidial_live_agen |
| 397481 | cron | xx.xx.xxx.61:38263 | asterisk | Query   |   46 | Waiting for table metadata lock | UPDATE vicidial_live_agents SET ring_callerid='' where ring_callerid='Y2271440330000342591'          |
| 397482 | cron | xx.xx.xxx.61:38264 | asterisk | Sleep   |   13 |                                 | NULL                                                                                                 |
| 397485 | cron | xx.xx.xxx.59:40558 | asterisk | Sleep   |   13 |                                 | NULL                                                                                                 |
| 397488 | cron | xx.xx.xxx.61:38270 | asterisk | Query   |   42 | Waiting for table metadata lock | SELECT COUNT(*) FROM vicidial_live_agents WHERE campaign_id='IN7000' and status IN ('READY','QUEUE', |
| 397491 | cron | xx.xx.xxx.59:40563 | asterisk | Query   |   42 | Waiting for table metadata lock | SELECT COUNT(*) FROM vicidial_live_agents WHERE campaign_id='IN7000' and status IN ('READY','QUEUE', |
| 397493 | cron | xx.xx.xxx.66:49646 | asterisk | Sleep   |   13 |                                 | NULL                                                                                                 |
| 397496 | cron | xx.xx.xxx.66:49651 | asterisk | Query   |   42 | Waiting for table metadata lock | SELECT COUNT(*) FROM vicidial_live_agents WHERE campaign_id='IN7000' and status IN ('READY','QUEUE', |
| 397497 | cron | xx.xx.xxx.68:57196 | asterisk | Sleep   |   13 |                                 | NULL                                                                                                 |
| 397501 | cron | xx.xx.xxx.68:57202 | asterisk | Query   |   42 | Waiting for table metadata lock | SELECT COUNT(*) FROM vicidial_live_agents WHERE campaign_id='IN7000' and status IN ('READY','QUEUE', |
| 397518 | cron | xx.xx.xxx.68:57204 | asterisk | Sleep   |    0 |                                 | NULL                                                                                                 |
| 397519 | cron | xx.xx.xxx.68:57205 | asterisk | Sleep   |   41 |                                 | NULL                                                                                                 |
| 397521 | cron | xx.xx.xxx.62:54058 | asterisk | Query   |   31 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'            |
| 397522 | cron | xx.xx.xxx.62:54059 | asterisk | Query   |   31 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'             |
| 397523 | cron | xx.xx.xxx.62:54060 | asterisk | Query   |   31 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'            |
| 397524 | cron | xx.xx.xxx.62:54061 | asterisk | Query   |   31 | Waiting for table metadata lock | SELECT count(*) from vicidial_live_agents where user='userid'            |


my.cnf looks like this:

Code: Select all

#general params
skip-name-resolve
connect_timeout=60
wait_timeout = 28800
concurrent_insert=2
expire_logs_days=3
skip-host-cache
skip-external-locking

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 2000
thread-cache-size              = 100
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 4000

# MyISAM #
key-buffer-size                = 4G
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000



Below i have listed all details of our vicidial setup

General Information:
1. OS: Ubuntu 12.04 LTS
2. Platform: VMWare
3. astguiclient versoin: 2.8-392c BUILD: 140312-2109

One Database Server:
CPU Cores: 16 Intel(R) Xeon(R) CPU X5570 @ 2.93GHz
RAM: 10G
Version: 5.5.7 milestone 15
Storage: vmdk on iSCSI
Replication: No

4 Asterisk Servers
Asterisk Version: 1.8.21.0-vici-beta
CPU: 8 Cores (Intel(R) Xeon(R) CPU X5570 @ 2.93GHz)
RAM: 4GB
Role: each server hosting ~30 agents
Phone Type: External (Routed to SIP gateway)
Storage: SAS


4 Web Servers
HTTP Server: Nginx
Clustered via pacemaker+heartbeat+ldirectord with (round robin connections - session based)

[mflorell]

Stop using vmware.

[cyrenity]

I am shifting asterisk servers from vmware to real servers. Should i also migrate db server to a real server. currently it's on a 16 core 10g ram vm

[mflorell]

Yes, you should not have anything on vmware, especially the database server.

[cyrenity]

Ok. shifted the DB and Asterisk server on Real servers, and the problem still exists.

i can see lots of "LOCK TABLES vicidial_live_agents WRITE" in show processlist;

[mflorell]

How many agents do you have?

What are the hardware specs on your database server?

[cyrenity]

it doesn't matter how many agents are logged in. problem remains same with 90 agents and even 30 agents.
specs are as follow.

1 database server (DELL-R710). 8GB ram. 12 cores. PERC 700 raid controller.
2 asterisk servers (DELL-R710) 6GB ram on each. 16 cores. PERC 700 raid controller.

i tried disabling two LOCK queries at LINE 2358 and 2487 in agi-VDAD-ALL-inbound.agi (version 2.8 140312) and it seems to fix the issue without any drawbacks or side-effects. i am sure you can elaborate more on this.

[mflorell]

Removing the locks will cause you problems if you ever get two calls at once, it can result in the agent getting the wrong call, or worse, two calls at once.

This is not a problem on systems that I have heard of, so this could be a hardware issue or an issue with something else in your configuration.

How is your RAID configured?

Would you be able to upgrade to the most recent svn/trunk? (The version you are on is almost a year old)

[cyrenity]

i know there is gioing to be a double call or wrong call issue. but for the time being it has resolved the problem, we are running a very critical inbound campaign and can not afford downtime, disabling these quries was a releaf.

I have configurd Raid 0 (2 disks) for all servers. I want to mention another thing that is we are running 4 webservers in ha mode (pacemaker+ldirectord). Clients/Agents access it via a single dns name vicidial.ourdomain.com.

I will most likely wait for few weeks before upgrading to latest svn/trunk. can't afford another downtime.

i see the database is a bottle neck in the system, we can load balance asterisk and web servers in vicidial architecture to achieve any number of users/calls. is there any way we can utilise two or more mysql servers with vicidial without loosing/splitting data. i can happily donate my time and skills for improving this part


edit: just got to know we are facing issue of double calls. but the good thing is it's not too frequent and our call center supervisors r not irritated over it ( as yet )

[mflorell]

The database is the bottleneck, other than off-loading reports to a slave database there is no way to cluster the database. But, for 90 agents there is no reason you can't run that off of a single database server. We have clients with over 500 agents that run on a single database server with no problems. If you have the proper hardware and the proper configuration it can handle a very large number of agents.

I wouldn't recommend a RAID 0 for DB usage, at the least you should use RAID 10, so any single drive failure won't cause data loss. Also, we only recommend LSI Logic MegaRAID cards(Dell Perc cards are NOT the same).

[cyrenity]

A note for people hosting DB on VMWare: DO NOT USE vCPUs. Keep your DB VM's CPU configuration close to hardware CPUs on HOST machine (e.g. ONE CORE PER ONE SOCKET PER CPU)

Faced the same issue with ViciBox 7 and i was able to fix the issue by reducing CPU configuration from (16 Sockets * 2 cores=32cores) to (16 sockets * 1 core=16cores).

[doberhol]

Can you perhaps clarify what actually fixed the problem? We're having the same problem and have tried the x socket * 1 cores proposal without success. Still getting the lock throughout the day and seems to get worse every day
We're moving the dB to physical tonight, but worried that this also didn't fix the problem in the thread above

[williamconley]

Sounds like he turned off the hyperthreading/fake core doubling to resolve the issue. We've had freaky isolated issues with virtualization technology in the past as well. This is dramatically more prevalent when inside a VMware or other virtualization environment (to the point the servers become unusable instead of merely unpredictable).

We normally keep all types of virtualization away from Vicidial for clients who want their servers to run error-free at all times. That technology is fine when you're running "services" that can self-heal but leave the client waiting for a few moe seconds while it gets "worked out". But that description does not apply to Vicidial.

[roger.milligan]

Still getting the lock throughout the day and seems to get worse every day
We're moving the dB to physical tonight, but worried that this also didn't fix the problem in the thread above

It would be good to see the relevant parts of a SHOW FULL PROCESSLIST to see what the locks are. Are they also "Waiting for table metadata" as in the first post in this topic? Try and find the process causing the lock and separate it from those waiting for it. Matching the queries listed to the source code may help explain what is happening.

I use this query rather than SHOW FULL PROCESSLIST:

Code: Select all

select now(), i.* from information_schema.PROCESSLIST i where time>0 and COMMAND not in ('SleXep','DaXemon','BXinlog Dump') and INFO NOT like '%inXformation_schema%' and INFO like '%%' order by STATE, time desc;

Makes it easier to filter and sort. (The "X"s are there to exclude the exclusions easily, remove then to get the match). I think it shows all the processes.

If you have ruled out over-configured Query Cache (which can cause long delays when it flushes), you should also double check that there are no external DB calls to the tables (or via Vici API calls) that may be hammering the DB (sometimes developers setup wallboards or reporting extracts that poll live_agents all the time).

Also look at enabling the Performance Schema which will give more details of the locking.

And then there that usual big question: What changes were made on the system that may have caused this? (For example, on one site someone pointed the DNS to and external site which caused some some Vici processes to be 100 times slower!) Did you suddenly ramp up the number of agents or the DB size or the dial rates?

Roger