Agent Perf report causing dialer to lock due to index issue

[mjohn425]

Hi guys,

We are having an issue where pulling the agent performance report is causing massive locks on our server and the report doesn't return for say 1-2 minutes.

I have done some investigating and found that it is because MySQL is no longer selecting the correct index to use on the query. If you see below, the top one is what it should be (I have added in a USE INDEX into this query to show proper behaviour although this works by default on other dialers without the USE INDEX) and bottom is what we are now getting. Time difference for a single query is 0.015sec vs 6 sec. More importantly when this runs, it uses close to 100% (single core) CPU and stops the MySQL process from doing virtually anything else. (Though table lock time very miniscule)

Any insight into this issue, is this something that needs to be added in code or is there something else I can do?

Obviously need to look at investing in a reporting server which we will, but the indexing issue will still remain.

(Note: I have already check, repair, optimized tables).

Thanks in advance.

Code: Select all

MariaDB [asterisk]> EXPLAIN SELECT full_name,vicidial_users.user,sum(pause_sec),sub_status,sum(wait_sec + talk_sec + dispo_sec), vicidial_users.user_group from vicidial_users,vicidial_agent_log USE INDEX (time_user) where event_time <= '2021-07-20 23:59:59' and event_time >= '2021-07-20 00:00:00' and sub_status='LOGIN' and vicidial_users.user=vicidial_agent_log.user and pause_sec<65000  and campaign_id IN('C0001') and vicidial_users.user_group IN('ADMIN')  group by user,full_name,sub_status order by user,full_name,sub_status desc limit 100000;
+------+-------------+--------------------+--------+---------------+-----------+---------+----------------------------------+-------+---------------------------------------------------------------------+
| id   | select_type | table              | type   | possible_keys | key       | key_len | ref                              | rows  | Extra                                                               |
+------+-------------+--------------------+--------+---------------+-----------+---------+----------------------------------+-------+---------------------------------------------------------------------+
|    1 | SIMPLE      | vicidial_agent_log | range  | time_user     | time_user | 6       | NULL                             | 11984 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | vicidial_users     | eq_ref | user          | user      | 62      | asterisk.vicidial_agent_log.user | 1     | Using where                                                         |
+------+-------------+--------------------+--------+---------------+-----------+---------+----------------------------------+-------+---------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [asterisk]> EXPLAIN SELECT full_name,vicidial_users.user,sum(pause_sec),sub_status,sum(wait_sec + talk_sec + dispo_sec), vicidial_users.user_group from vicidial_users,vicidial_agent_log where event_time <= '2021-07-20 23:59:59' and event_time >= '2021-07-20 00:00:00' and sub_status='LOGIN' and vicidial_users.user=vicidial_agent_log.user and pause_sec<65000  and campaign_id IN('C0001') and vicidial_users.user_group IN('ADMIN')  group by user,full_name,sub_status order by user,full_name,sub_status desc limit 100000;
+------+-------------+--------------------+------+---------------------------+------+---------+------------------------------+------+----------------------------------------------+
| id   | select_type | table              | type | possible_keys             | key  | key_len | ref                          | rows | Extra                                        |
+------+-------------+--------------------+------+---------------------------+------+---------+------------------------------+------+----------------------------------------------+
|    1 | SIMPLE      | vicidial_users     | ALL  | user                      | NULL | NULL    | NULL                         | 384  | Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | vicidial_agent_log | ref  | user,event_time,time_user | user | 63      | asterisk.vicidial_users.user | 9543 | Using where                                  |
+------+-------------+--------------------+------+---------------------------+------+---------+------------------------------+------+----------------------------------------------+
2 rows in set (0.000 sec)



[mflorell]

Over the years I've noticed that if you have to force an index on database queries like this, that the MySQL Query Optimier is not working properly and you probably need to reboot your database server, and/or add more RAM and/or upgrade your database hardware and/or archive log tables.

Also, adding a database slave server to run reports on is another option you could choose.

[mjohn425]

Over the years I've noticed that if you have to force an index on database queries like this, that the MySQL Query Optimier is not working properly and you probably need to reboot your database server, and/or add more RAM and/or upgrade your database hardware and/or archive log tables.

Also, adding a database slave server to run reports on is another option you could choose.

Definitely adding a slave db, but will still need to sort this indexing problem.

Server reboots every night, 64GB RAM (I know overkill, was from previous vendor), 8 core 3.3GHz CPU. Are there any settings that I should be looking at for MySQL to take advantage of the RAM. How often should we look at archiving log tables? Currently at 400MB table size for agent performance report, server has been running for approx 1.5 yrs.

[mflorell]

Yes, there certainly are my.cnf settings you can change to better utilize that much RAM. Although you do need to be careful because if you set them too high you can crash MySQL.

A good place to start is by looking at the "/usr/share/mysql/my-huge.cnf" file.

There is also the "extras/mysql-tuning.sh" script included in the VICIdial codebase.

I would suggest looking over each of the my.cnf settings, and searching about each of them to see how you might want to change them. It's been a couple years since I did a great deal of that myself, but some of these settings can have a major impact on performance, especially for larger VICIdial clusters.

[mjohn425]

Alright perfect, will check them out and report back. Cheers mate.

For anyone reading this thread in future, I have fixed the issue in code while I investigate, I have a patch file on request, just leave me a message.

[carpenox]

you should submit to the bug tracker with a .diff

[OVS]

Let me know if you can share code which is fixed.

[basha04]

When optimizing MySQL the last thing to do is increasing the hardware.

First do not use USE INDEX or FORCE INDEX , those are deprecated and will be removed on a future release on MySQL (I am not sure on MariaDB).

Secondly that is not the best index. Try adding the following indexes and let me know the results

Code: Select all

alter table vicidial_users add index usr_usrgrp_fllnm (user,user_group,full_name);
alter table vicidial_agent_log add index usr_subst_cmpid (user,sub_status,campaign_id,event_time,pause_sec,wait_sec,talk_sec,dispo_sec);

Note, MySQL index evaluates first equal operators