Too Many open connections

[WinDrop]

Hi All,

Would like to ask question regarding mysql connections.
We have a call center that uses vici dialer and we are very happy with the software but it seems that it has a issue.
Seems that Vici doesn't close the mysql connection. Our MySQL server has more then 500 connections open at all times. Which i believe isn't normal.
We also have FreePBX and Queuemetrics and it's been used by 3x more agents then Vici but it only has maximum of 10 connections at the time.
Can somebody tell me is there a patch for this or maybe developers can suggest me where to close the mysql connection ( on which page )
we use astguiclient v2.2.1

Thank you very much.

[williamconley]

Use the scratch installation instructions from the wiki and modify your mysql to allow enough connections and you'll be good. Server load is not affected by the number of live connections, but by the volume of data requests. And that is not going to change except by the load on each server. If you use the proper installation technique, there are instructions for modifying the my.cnf file to allow enough connections.

Vicidial hammers the mysql server, it's part of what Vicidial "does" to reach the level of usefulness it has achieved. I have had a single vicidial server (4 processor 2.4Ghz system with 4G RAM) with 400 channels on it. FreePBX will never do that. It would melt down.

Vicidial hits the mysql server with perl and php from agent workstations, manager workstations, and management scripts on all servers simultaneously. While it is possible that at some point in the future there may be an effort to reduce the number of live connections ... that's not something I see as being high on anyone's priority list right now.

[WinDrop]

Just downloaded the source of v2.2.1
searched for "mysql_close" in all of the files and found a strange thing.
You open connection to queuemetrics database, do the stuff you have to do and then close it after that.

In the same time, you open connection to vici database ( well... when you load the page ) but you never close it... Why?
What's the reason of keeping connections open? in MySQL they just hand in "Sleep" status. They don't do anything at all.
Wouldn't that be better to add mysql_close(); at the end of vicidial.php page to close connection after page have been fully loaded?
or maybe there is a reason why you are not closing it? If that's the case could you please tell me the reason?

Thanks.

[mflorell]

How many agents do you have?

Usually this is a symptom of an overloaded or under-powered database server.

[WinDrop]

How many agents do you have?

Usually this is a symptom of an overloaded or under-powered database server.

300+ just on vici.

[mflorell]

what are your database server hardware specifications, including the CPU, RAM, hard drive specs and RAID card model and how it is configured.

[WinDrop]

what are your database server hardware specifications, including the CPU, RAM, hard drive specs and RAID card model and how it is configured.

Why you are asking these questions?
I asked about completely different thing.
Why you never close mysql connection after the page is loaded? Why you keep it idle? What's the point in keeping 500+ idle connections and just increase max_connections setting in mysql if you can set it to 50 and just add mysql_close() where it has to be added?

That was my question. My servers work fine, there are no problems with them. I'm just wondering why it has been done as it is now? increasing max_connections every time we increase amount of agents isn't right...

[williamconley]

try modifying the code to close them and see if it has an effect on your system. report your results whether good/bad/neutral. i'm interested to hear your results.

[mflorell]

PHP automatically closes connections at the end of the script running, so it is unnecessary to add the mysql_close unless you use pconnect, and we never use pconnect because it causes all sorts of problems.

We have clients with more agents on their systems that don't have this problem. The reason I asked about your hardware is that when that happens it is usually because of inadequate or improperly configured hardware.

[williamconley]

he said his system is working fine, he was merely curious about having to raise the limit of allowable connections and checking to see if this could be avoided by closing them implicitly.

the concept that they are closing automatically upon completion of the script would imply that the massive number of open connections is due to the number of agents/management scripts etc accessing mysql and not related to the lack of closure.

but i'd definitely be interested in hearing if it makes a difference to implicitly close them.

[WinDrop]

he said his system is working fine, he was merely curious about having to raise the limit of allowable connections and checking to see if this could be avoided by closing them implicitly.

the concept that they are closing automatically upon completion of the script would imply that the massive number of open connections is due to the number of agents/management scripts etc accessing mysql and not related to the lack of closure.

but i'd definitely be interested in hearing if it makes a difference to implicitly close them.

I'm wondering, where to add mysql_close() then?
at the end of vicidial.php page?
Maybe you include vicidial.php page anywhere else? Because connection should be closed on the page that loads as the last one.

I haven't looked at the code so much to understand these little things

Thanks.

[mflorell]

If you wanted to add mysql_close() at the end of every connection you would have to go through every php script in the agc and vicidial folders and look for every "exit" and put it in before that(two for the ones that have two open connections), as well as at the end of every script. And if there is no mysql connection PHP will throw an error on seeing the close. It is not a simple task and will take you a while.

[williamconley]

Not a small task. LOL

Of course .. an experiment could be useful. Just do it on one page (like a report page? or some other fairly small one ...?) and see if the connections from that page start and stop as the page loads and unloads. May require turning off all the other scripts to be "sure" you're looking at real connections from that page.

[WinDrop]

I will do my research and will let you later.

Thanks.