mysql problem - max connections

[dawlish]

I'm quite new to php / mysql and have developed a site that has recieved an increasing number of visitors. However yesterday I had to restart the mysql server twice. I believe this is due to the max number of connection being exceeded.

I have done a search on the web but cannot locate a great amount of info as to how I can increase the number of max connections.

I tried the following via telnet:

mysqld -u root --set-variable=max_connections=300

But it returned the following:

020710 19:20:04 Warning: setrlimit couldn't increase number of open files to mo
re than 150
020710 19:20:04 Warning: Changed limits: max_connections: 28 table_cache: 56
020710 19:20:04 Can't start server: Bind on TCP/IP port: Address already in use
020710 19:20:04 Do you already have another mysqld server running on port: 3306
?
020710 19:20:04 Aborting

Does this mean I cannot increase the connections and that the max connections are currently only 28?

I am running the site on a virtual private server(not shared hosting)
FreeBSD Unix 4.1 O/S
Web Server Apache 1.3 Web Server
CPU Dual 400Mhz Intel Pentium II processors.
Memory 256 MB 50ns ECC Viking.
Hard disk drives 3 10,000 RPM Seagate Hard Drives
- 18 GB user volume
- 18GB user volume live mirror
- 24 hour user volume backup
Disk I/O channels 2 Ultra SCSI 2" channels

I believe I should be able to increase the max connections but don't seem to be able to figure out how. Any help or advice would be greatly appreciated.

[Brett_Tabke]

I dug on this for a long time this morning and couldn't come up with an answer. I know I've run into this on a host before.

-set-variable=max_connections=300

That is a bit high. You'd have to have one seriously busy website to support that many. 30-50 should be more than enough. If it isn't, I'd think you have an application that isn't closing it's connections properly.

[ppg]

guessing somewhat here, but possibly you didn't shut down mysql before restarting with the command-line option?

020710 19:20:04 Can't start server: Bind on TCP/IP port: Address already in use
020710 19:20:04 Do you already have another mysqld server running on port: 3306

The syntax you've used to set the max_connections variable should work.

You can check what your max_connections is currently set to by typing the following at the mysql monitor prompt: SHOW VARIABLES.

you can check how many connections you currently have active with: SHOW STATUS. threads_connected is the number of currently open connectoins. Perhaps the information you get back from this will help.

I'm inclined to agree with Brett though, I'd check your code very carefully to make sure you are closing all connections. One good way to check your code is to install mysql on your desktop machine with a webserver, and access your pages. Then check your open connections at the mysql prompt or with a tool (I use the mysqladmin tool which comes with the windows distribution). If you have any open connections (apart from the one your mysql monitor shows) then you're not closing all your connections.

I recently found myself in a similar situation, was sure I'd double checked my code, but mysql would still stop accepting connections after heavy testing - in the end I tracked it down to not closing a connection.

I hope this random thrashing around in the dark helps you somewhat....

[dawlish]

Thanks for your advice guys