urgent: mysql connections run out

[kalamake]

We have less than 10 user and mysql has default 100 connections. Some how sugar doesnt close connections so i have to reboot server once a day when no more connections are avaible to mysql server.

Is there anything that i can do prevent that? I use sugar 5.0 ga

[kuske]

As Sugar 5.0 ga is a Beta version I would take 5.0.0a.
Never saw that problem there.

[spithost]

Hi there,

Depending of the MySQL-version sometimes it takes quite long for MySQL-connections to time-out.

You can override some settings when you can access and/or edit /etc/my.cnf when running on a Linux-system.
Mine looks like this on my production web-servers:
---------8<--------
# The MySQL server
[mysqld]
set-variable = max_connections=800
set-variable = table_cache=1000
set-variable = interactive_timeout=1200
set-variable = wait_timeout=1200
---------8<--------
(I have some more settings but they are not really needed in this case)

This means so much as that there can be a maximum of 800 connections.
Propably 100 would suffice with you if you time-out the connections earlier. I suspect some of the connections stay open until that time-out time.
The table_cache is somewhat dependable on the number of connections and, with me, is heightened to 1000.
I can't really figure out if this is the right value for 800 connections but it works with me ;-)
Connections will time out (and will be closed) after 1200 seconds (20 minutes) of inactivity.
Connections wait for a timeout during the same period (20 minutes).
The defaults of the last two values are VERY long (several hours) which can be very annoying, resource-hogging and leading to the problems you describe.

You will have to stop and start MySQL for the new settings to get active.
I use MySQL 5.0x. I know that the versions OLDER then say 4.1 don't really do anything with those values except the number of connections.

If you use 4.0 or older you CAN heighten the number of connections and the table_cache.
Try with 800 and 1000 for example and check out what happens.
It will take some more memory but it's nicer then the errors....

Hope this helps....

Regards,
Klaus