Lock problem with MariaDB

[macaruchi]

Hi!
Iam having problem with mariaDB each time all sql get lock and I have to restart the database.
I have 5 agents , max 8 , working with this server.

My server is Dell 8 core, 8gbRAM
ViciBox 7.0.3
OPenSuse 42.1
VERSION: 2.12-565a
BUILD: 160827-0917

There is any document for tuning MariaDB to Avoid this or something to reduce this. I have had this situation 3 times in this day
I have images but I cant send any images

Any cluees?

[macaruchi]

This is what I get rigth now....

| 149271 | root | ::1 | asterisk | Query | 386 | Copying to tmp table | Select DATE(alg.`event_time`) as Days, ((SELECT COUNT(*) from `vicidial_list` where `vici | 0.000 |
| 149284 | root | localhost | asterisk | Query | 385 | Sending data | Select DATE(alg.`event_time`) as Days, ((SELECT COUNT(*) from `vicidial_list` where `vici | 0.000 |
| 149346 | cron | localhost | asterisk | Query | 378 | Waiting for table level lock | UPDATE vicidial_agent_log set wait_sec='13',talk_epoch='1481144774',lead_id='32061' where agent_log | 0.000 |
| 149348 | root | localhost | asterisk | Query | 378 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>', COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149357 | cron | localhost | asterisk | Query | 377 | Waiting for table level lock | UPDATE vicidial_agent_log set wait_sec='14',talk_epoch='1481144775',lead_id='32061' where agent_log | 0.000 |
| 149364 | cron | localhost | asterisk | Query | 376 | Waiting for table level lock | UPDATE vicidial_agent_log set wait_sec='15',talk_epoch='1481144776',lead_id='32061' where agent_log | 0.000 |
| 149370 | root | localhost | asterisk | Query | 375 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>',COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149375 | cron | localhost | asterisk | Query | 375 | Waiting for table level lock | UPDATE vicidial_agent_log set wait_sec='16',talk_epoch='1481144777',lead_id='32061' where agent_log | 0.000 |
| 149381 | cron | localhost | asterisk | Query | 374 | Waiting for table level lock | UPDATE vicidial_agent_log set wait_sec='17',talk_epoch='1481144778',lead_id='32061' where agent_log | 0.000 |
| 149389 | root | localhost | asterisk | Query | 373 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>',COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149392 | cron | localhost | asterisk | Query | 372 | Waiting for table level lock | UPDATE vicidial_agent_log set wait_sec='18',talk_epoch='1481144779',lead_id='32061' where agent_log | 0.000 |
| 149398 | cron | localhost | asterisk | Query | 371 | Waiting for table level lock | SELECT wait_epoch,wait_sec,dead_epoch from vicidial_agent_log where agent_log_id='18357' | 0.000 |
| 149400 | cron | localhost | asterisk | Query | 371 | Waiting for table level lock | UPDATE vicidial_agent_log set wait_sec='19',talk_epoch='1481144780',lead_id='32061' where agent_log | 0.000 |
| 149408 | root | localhost | asterisk | Query | 371 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>',COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149409 | cron | localhost | asterisk | Query | 371 | Waiting for table level lock | UPDATE vicidial_agent_log set wait_sec='20',talk_epoch='1481144781',lead_id='32061' where agent_log | 0.000 |
| 149415 | cron | localhost | asterisk | Query | 370 | Waiting for table level lock | SELECT wait_epoch,wait_sec,dead_epoch from vicidial_agent_log where agent_log_id='18357' | 0.000 |
| 149419 | cron | localhost | asterisk | Query | 369 | Waiting for table level lock | UPDATE vicidial_agent_log set wait_sec='21',talk_epoch='1481144782',lead_id='32061' where agent_log | 0.000 |
| 149421 | root | localhost | asterisk | Query | 369 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>',COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149424 | root | localhost | asterisk | Query | 369 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>',COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149429 | cron | localhost | asterisk | Query | 369 | Waiting for table level lock | SELECT wait_epoch,wait_sec,dead_epoch from vicidial_agent_log where agent_log_id='18357' | 0.000 |
| 149431 | cron | localhost | asterisk | Query | 369 | Waiting for table level lock | UPDATE vicidial_agent_log set wait_sec='22',talk_epoch='1481144783',lead_id='32061' where agent_log | 0.000 |
| 149433 | root | localhost | asterisk | Query | 368 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>',COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149438 | cron | localhost | asterisk | Query | 368 | Waiting for table level lock | SELECT wait_epoch,wait_sec,dead_epoch from vicidial_agent_log where agent_log_id='18357' | 0.000 |
| 149440 | root | localhost | asterisk | Query | 367 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>',COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149441 | cron | localhost | asterisk | Query | 367 | Waiting for table level lock | UPDATE vicidial_agent_log set wait_sec='23',talk_epoch='1481144784',lead_id='32061' where agent_log | 0.000 |
| 149451 | cron | localhost | asterisk | Query | 367 | Waiting for table level lock | SELECT wait_epoch,wait_sec,dead_epoch from vicidial_agent_log where agent_log_id='18357' | 0.000 |
| 149453 | cron | localhost | asterisk | Query | 367 | Waiting for table level lock | UPDATE vicidial_agent_log set wait_sec='24',talk_epoch='1481144785',lead_id='32061' where agent_log | 0.000 |
| 149459 | cron | localhost | asterisk | Query | 366 | Waiting for table level lock | SELECT wait_epoch,wait_sec,dead_epoch from vicidial_agent_log where agent_log_id='18357' | 0.000 |
| 149467 | root | localhost | asterisk | Query | 363 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>',COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149468 | cron | localhost | asterisk | Query | 362 | Waiting for table level lock | UPDATE vicidial_list set status='NEW', called_count='0',user='cfrancisco' where lead_id='32062' | 0.000 |
| 149476 | root | localhost | asterisk | Query | 359 | Waiting for table level lock | SELECT (SELECT COUNT(*) from `vicidial_list` `dtl`, `vicidial_lists` lst | 0.000 |
| 149486 | cron | localhost | asterisk | Query | 357 | Waiting for table level lock | UPDATE vicidial_list set status='NEW', called_count='0',user='cfrancisco' where lead_id='32062' | 0.000 |
| 149496 | root | localhost | asterisk | Query | 353 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>', COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149499 | cron | localhost | asterisk | Query | 353 | Waiting for table level lock | SELECT list_id,province,security_phrase FROM vicidial_list where lead_id='32062' | 0.000 |
| 149513 | root | localhost | asterisk | Query | 347 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>', COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149516 | cron | localhost | asterisk | Query | 347 | Waiting for table level lock | UPDATE vicidial_list set status='NEW', called_count='0',user='cfrancisco' where lead_id='32062' | 0.000 |
| 149518 | cron | localhost | asterisk | Query | 346 | Waiting for table level lock | UPDATE vicidial_list set status='NEW', called_count='0',user='cfrancisco' where lead_id='32062' | 0.000 |
| 149520 | root | localhost | asterisk | Query | 343 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>',COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149523 | root | localhost | asterisk | Query | 337 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>',COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149525 | root | localhost | asterisk | Query | 333 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>',COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149533 | root | localhost | asterisk | Query | 327 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>',COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149534 | root | localhost | asterisk | Query | 323 | Waiting for table level lock | select usr.`full_name` as '<b>NAME</b>',COALESCE((sum(`agent_log`.`talk_sec`)+sum(`agent | 0.000 |
| 149553 | cron | localhost | asterisk | Query | 211 | Waiting for table level lock | UPDATE vicidial_list set status='ERI', user='' where status IN('QUEUE','INCALL') and user ='cfrancis | 0.000 |
| 149555 | root | localhost | NULL | Sleep | 0 | | NULL | 0.000 |
| 149557 | root | localhost | asterisk | Query | 0 | init | show processlist | 0.000 |
| 149558 | cron | localhost | asterisk | Query | 156 | Waiting for table level lock | UPDATE vicidial_list set status='ERI', user='' where status IN('QUEUE','INCALL') and user ='rjackson | 0.000 |
| 149560 | cron | localhost | asterisk | Query | 152 | Waiting for table level lock | UPDATE vicidial_list set status='ERI', user='' where status IN('QUEUE','INCALL') and user ='rjackson | 0.000 |
| 149569 | cron | localhost | asterisk | Query | 142 | Waiting for table level lock | UPDATE vicidial_list set status='ERI', user='' where status IN('QUEUE','INCALL') and user ='rjackson | 0.000 |
| 149572 | cron | localhost | asterisk | Query | 91 | Waiting for table metadata lock | OPTIMIZE table vicidial_campaigns | 0.000 |
| 149578 | cron | localhost | asterisk | Query | 68 | Waiting for table metadata lock | SELECT closer_campaigns from vicidial_campaigns | 0.000 |
| 149579 | root | localhost | asterisk | Query | 65 | Waiting for table level lock | SELECT (SELECT COUNT(*) FROM `vicidial_list` WHERE status='A' and list_id=ls.`list_id` | 0.000 |
| 149580 | root | localhost | asterisk | Query | 58 | Waiting for table level lock | SELECT (SELECT COUNT(*) FROM `vicidial_list` WHERE status='A' and list_id=ls.`list_id` | 0.000 |
| 149581 | root | localhost | asterisk | Query | 48 | Waiting for table level lock | SELECT (SELECT COUNT(*) FROM `vicidial_list` WHERE status='A' and list_id=ls.`list_id` | 0.000 |
| 149582 | root | localhost | asterisk | Query | 38 | Waiting for table level lock | SELECT (SELECT COUNT(*) FROM `vicidial_list` WHERE status='A' and list_id=ls.`list_id` | 0.000 |
| 149585 | cron | localhost | asterisk | Sleep | 2 | | NULL | 0.000 |
| 149591 | cron | localhost | asterisk | Query | 30 | Waiting for table metadata lock | INSERT IGNORE into vicidial_campaign_stats (campaign_id) select campaign_id from vicidial_campaigns | 0.000 |
| 149592 | root | localhost | asterisk | Query | 28 | Waiting for table level lock | SELECT (SELECT COUNT(*) FROM `vicidial_list` WHERE status='A' and list_id=ls.`list_id` | 0.000 |
| 149593 | root | localhost | asterisk | Query | 18 | Waiting for table level lock | SELECT (SELECT COUNT(*) FROM `vicidial_list` WHERE status='A' and list_id=ls.`list_id` | 0.000 |

[mflorell]

Looks like you're running a lot of horrible non-standard SQL queries in there(one example):

" Select DATE(alg.`event_time`) as Days, ((SELECT COUNT(*) from `vicidial_list` where `vici"

I would suggest not running that stuff if you want your VICIdial system to function properly.