MySQL connect error, even though disk isn´t full

[ccabrera]

Hello,

I have this server:

Xeon 2.30 Ghz 12 cores 64 GB of RAM, running 100 agents on Vicibox 4.0.3. VERSION: 2.8-415a BUILD: 131007-1234. Load average at time of problem is 0.63, 0.58, 0.51. It uses 5 E1s with 2 x Sangoma A104DE with dahdi chunk = 40.

I´m experiencing "MySQL connect error" on the realtime report: it just appears, disappears and so on. Also, server is experiencing double or sometimes triple calls on same agents, so everything points out to be a database problem.

I run archive scripts nightly for the logs table and send everything that is 3 months old to the archive.

Disk is not full. Tables are not crashed (or at least that´s what mysqld.log says). I used to have MySQL 5.5 as of yesterday, but last night I switched to MariaDB 5.2 with no luck yet.

What do you think I´m missing to check? This server has been running flawlessly for about 6 months with same amount of agents.

[GaD]

Do a 'SHOW PROCESSLIST' when this happens, could be that your DB per-se (not the server) is overloaded due to a locked table or something.... THis has happened to me when I had an in-group trying to match the caller-id with all the leads in the system with the default indexes in vicidial_list. Post your findings......

[williamconley]

Do a 'SHOW PROCESSLIST' when this happens, could be that your DB per-se (not the server) is overloaded due to a locked table or something.... THis has happened to me when I had an in-group trying to match the caller-id with all the leads in the system with the default indexes in vicidial_list. Post your findings......

Usually stuck mysql processes do not cause a fail-to-connect, just a slow-to-respond. In my experience. But definitely a valid place to look, as it is possible.

Usually when I hear something like fail-to-connect (usually posts on here, a client has never reported this issue directly) it is because there is a configuration problem with mysql or the server is heavily loaded (lots of agents in a clustered setting) and there aren't enough connections available to sql. Solution: tell sql to allow more connections. I note that you did not specify "single server", please clarify this.

Perhaps you have a really-smart mysql person, but this application is not a "really smart mysql application". So ... you outsmarted yourself by following best-practices in mysql configuration. This is the only reason I can think of that none of OUR clients (on servers we built) have ever reported this issue. WE use the settings generated by Kumba. You could also look for the heavy mysql usage settings Kumba has in place for the dedicated mysql servers.

Happy Hunting!

[GaD]

When an inbound call comes into a specific in-group I have the system search through ALL leads in fields phone_number, alt_phone and address3 to try to match the caller idwith any of those fields for the 3 000 000+ leads that my system handles. Every time a call would come in the realtime report would show the exact same error that ccabrera was reporting: MYSQL CONNECT ERROR. Using the SHOW PROCESSLIST I found out that this error would show when a lookup of this sort was going on, In the end I was able to replicate it in my production systems in a 60+ agent call center. I tried to solve the matter by doing some modifications to the agi_ALL_INBOUND agi and limiting the search lists in which to look the calls for (I modified the SQL statement to have something like: AND list_id IN (''100,'101') or something of that sort). The problem persisted but in a lesser manner. I later found out that alt_phone and address3 are not indexed and therefore a lookup of a single registry in a 3-million-plus records would take around 10 seconds to go through both alt_num and address3. I added indexes to both and voilá....

If it weren't for SHOW PROCESSLIST I would not have seen that the ammount or querries waiting for table unlocks would build up to three times the normal ammount of connections. I would go from around 80 connections to over three hundred on these lookups..... That is why I suggested the proceedure. Maybe you can follow my proceedure and see if my solution works for you.....

Happy hunting...!

[williamconley]

True enough. If any of your lead or log tables are in the high millions ... you may have issues (time to begin archiving log tables or pruning the leads table ...).