Agent Perf report causing dialer to lock due to index issue

All installation and configuration problems and questions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

Agent Perf report causing dialer to lock due to index issue

Postby mjohn425 » Tue Jul 20, 2021 8:11 am

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)

OS: VICIBox 9.0.1 OpenSuse 15.1 | VERSION: 2.14-742a BUILD: 200327-1715 | Asterisk: 13.21.1-vici | SVN: 3205 DB Schema: 1588
Linux: 4.12.14-lp151.28.44-default | MYSQL: Ver 15.1 Distrib 10.2.31-MariaDB | Perl: v5.26.1 | php: v7.2.5
mjohn425
 
Posts: 51
Joined: Tue Jan 21, 2020 1:20 am

Re: Agent Perf report causing dialer to lock due to index is

Postby mflorell » Tue Jul 20, 2021 8:27 am

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.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Agent Perf report causing dialer to lock due to index is

Postby mjohn425 » Tue Jul 20, 2021 8:45 am

mflorell wrote: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.
OS: VICIBox 9.0.1 OpenSuse 15.1 | VERSION: 2.14-742a BUILD: 200327-1715 | Asterisk: 13.21.1-vici | SVN: 3205 DB Schema: 1588
Linux: 4.12.14-lp151.28.44-default | MYSQL: Ver 15.1 Distrib 10.2.31-MariaDB | Perl: v5.26.1 | php: v7.2.5
mjohn425
 
Posts: 51
Joined: Tue Jan 21, 2020 1:20 am

Re: Agent Perf report causing dialer to lock due to index is

Postby mflorell » Tue Jul 20, 2021 9:16 am

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.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Agent Perf report causing dialer to lock due to index is

Postby mjohn425 » Tue Jul 20, 2021 9:33 am

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.
OS: VICIBox 9.0.1 OpenSuse 15.1 | VERSION: 2.14-742a BUILD: 200327-1715 | Asterisk: 13.21.1-vici | SVN: 3205 DB Schema: 1588
Linux: 4.12.14-lp151.28.44-default | MYSQL: Ver 15.1 Distrib 10.2.31-MariaDB | Perl: v5.26.1 | php: v7.2.5
mjohn425
 
Posts: 51
Joined: Tue Jan 21, 2020 1:20 am

Re: Agent Perf report causing dialer to lock due to index is

Postby carpenox » Tue Jul 20, 2021 10:46 am

you should submit to the bug tracker with a .diff
Alma Linux 9.3 | Version: 2.14-911a | SVN Version: 3815 | DB Schema Version: 1710 | Asterisk 18.18.1
www.dialer.one -:- 1-833-DIALER-1 -:- https://linktr.ee/CyburDial -:- WhatsApp: +19549477572 -:- Skype: live:carpenox_3
carpenox
 
Posts: 2230
Joined: Wed Apr 08, 2020 2:02 am
Location: Coral Springs, FL


Return to Support

Who is online

Users browsing this forum: Google [Bot], Majestic-12 [Bot] and 97 guests