Vicibox 11 Mysql settings for heavy use

[alo]

Hello Friends,

I know this topic has been discussed to death here, but I was wondering if things have changed now with vicibox 11.

I noticed that the my.cnf is very different with mostly everything commented out. I noticed doing show global variables that things like max_connection is set to 2000.

we have the DB with 94gb Ram, 32 cores, Raid 10, etc So I am wondering if there are different recommended settings for bigger DBs or better optimized for bigger clients.
Also different campaign settings too that might speed up the database even at the expense of some lesser used features.

Thanks!

[basha04]

The problem rarely stands on the configuration settings, surely some optimization can be done there. The main issue is that ViciDial uses MyISAM storage engine which doesn't support row lock (meaning even for a select you will have table lock). It requires a lot of work to switch to innodb engine , because a lot of queries used needs to change. What I suggest , monitor your database , see which queries are blocking the system, some of them can be optimized using composite/covering index. Have a slave/replica for reports

[williamconley]

It requires a lot of work to switch to innodb engine , because a lot of queries used needs to change.

Vicidial overloads innoDB. It's not that changing Vicidial to InnoDB would take too much effort: It's that Vicidial has been run on InnoDB and it failed miserably. MyISAM exists because it's faster. Significantly faster, in fact. InnoDB is transaction-safe, which is excellent for banking, but not for throughput. And Vicidial requires FAST throughput. LOL.

Vicidial record locking is role-based. For instance: Two READ queries can run parallel. Two WRITE queries can run parallel. But they can't be mixed and will block across roles.

What I suggest , monitor your database , see which queries are blocking the system, some of them can be optimized using composite/covering index. Have a slave/replica for reports

Often new techs will try to load more leads into the hopper (thousands!) to try to reduce the "hit" for loading leads into the hopper. Unfortunately, that exacerbates the problem as now the entire vicidial_list table has to be queried more often to keep that hopper level high. There are many small but important trade-offs such as this that can trick you into slowing the system down while thinking you're improving performance.

So go with basha04's advice: Find out which specific query is slowing you down, and attack that query. Find out what script is generating it first (sometimes you find it's just a report and all you need is to NOT run that report until after shift, or install a replication server) and bring that query and it's initiating script here and perhaps we can help you improve throughput.