MySQL Error: Too Many Connections!

[LancerForums]

I just got a ton of emails from vB with DB errors that all said this:

Database error in vBulletin :

Link-ID == false, connect failed
mysql error: Too many connections

mysql error number: 1040

Date: Thursday 26th of September 2002 04:12:35 AM

Here's my my.cnf:

[mysqld]
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
set-variable = key_buffer=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=8

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout

and here's a link to my dbstats.php:

http://www.evolutionm.net/dbstats.php

I'm guessing I need to open more connections for MySQL, but I thought I'd make sure with the gurus :)

Thanks,

Mark

[eva2000]

change my.cnf to below and restart mysql


[mysqld]
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=450
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=9600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
open_files_limit=8192
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout

[LancerForums]

Thanks! I changed my.cnf... hopefully I won't run into that problem again.

Mark

[eva2000]

Originally posted by LancerForums
Thanks! I changed my.cnf... hopefully I won't run into that problem again.

Mark no probs :)

[SweetSVT99]

I looked at the my.cnf file, and I already don't have the limit line... Am I missing something?

-steve

[paulsjv]

just add that line to your my.cnf file and it should work just fine.. but you might have to restart you mysql deamon... :)

[LancerForums]

It looks like the problem has arose again. I'm getting a "Too many mysql connections" message emailed to me every once in a while. It appears to last for a few minutes at the most and goes away, but it's very annoying. I'm using persistant connections through vBulletin... just thought that I'd throw that out in case it was a source of the problem.

My dbstats file is available here:
http://www.evolutionm.net/dbstats.php

Here's what my top looks like at Oct. 17th at 12AM Midnight (I removed the non-mysql processes from the list):

11:55am up 9 days, 14:10, 1 user, load average: 0.95, 0.92, 0.89
79 processes: 77 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: 16.2% user, 5.6% system, 0.0% nice, 78.1% idle
Mem: 513184K av, 358544K used, 154640K free, 0K shrd, 2872K buff
Swap: 1052216K av, 39268K used, 1012948K free 217864K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
10406 mysql 16 0 74796 38M 4136 S 1.7 7.7 0:02 mysqld
10101 mysql 15 0 74796 38M 4136 S 0.7 7.7 0:05 mysqld
10058 mysql 15 0 74796 38M 4136 S 0.3 7.7 0:09 mysqld
10055 mysql 15 0 74796 38M 4136 S 0.1 7.7 0:08 mysqld
10402 mysql 15 0 74796 38M 4136 S 0.1 7.7 0:03 mysqld
830 mysql 15 0 74796 38M 4136 S 0.0 7.7 2:30 mysqld
849 mysql 15 0 74796 38M 4136 S 0.0 7.7 0:01 mysqld
850 mysql 15 0 74796 38M 4136 S 0.0 7.7 0:50 mysqld
858 mysql 20 0 74796 38M 4136 S 0.0 7.7 0:00 mysqld
8713 mysql 15 0 74796 38M 4136 S 0.0 7.7 0:23 mysqld
9873 mysql 15 0 74796 38M 4136 S 0.0 7.7 0:16 mysqld
9876 mysql 15 0 74796 38M 4136 S 0.0 7.7 0:13 mysqld
9944 mysql 15 0 74796 38M 4136 S 0.0 7.7 0:07 mysqld
9987 mysql 16 0 74796 38M 4136 S 0.0 7.7 0:11 mysqld
9999 mysql 15 0 74796 38M 4136 S 0.0 7.7 0:08 mysqld
10093 mysql 16 0 74796 38M 4136 S 0.0 7.7 0:07 mysqld
10148 mysql 15 0 74796 38M 4136 S 0.0 7.7 0:07 mysqld
10181 mysql 20 0 74796 38M 4136 S 0.0 7.7 0:07 mysqld
10182 mysql 15 0 74796 38M 4136 S 0.0 7.7 0:07 mysqld
10210 mysql 15 0 74796 38M 4136 S 0.0 7.7 0:10 mysqld

Thanks,

Mark

[eva2000]

as root user in telnet type

mysqladmin -u root -p variables

and look at the max_connections value what is it?

100 or 450 as in my.cnf ?

[LancerForums]

Good catch eva! It still says 100, but here's the weird thing. My my.cnf file has returned to it's original form. It no longer lists the max connections, etc that I copied from your my.cnf above. I'm guessing my.cnf is reset when I restart mysql, but why??? I hope there's a way around this because 100 connections won't cut it I guess :(

Mark

[Dave#]

Originally posted by LancerForums
Good catch eva! It still says 100, but here's the weird thing. My my.cnf file has returned to it's original form. It no longer lists the max connections, etc that I copied from your my.cnf above. I'm guessing my.cnf is reset when I restart mysql, but why??? I hope there's a way around this because 100 connections won't cut it I guess :(

Mark

using a control panel to manage your server?

[LancerForums]

No control panel at all. I'm using RH 7.3 but I never installed it... my host did that for me and they probably used an rpm. I think mysql was an rpm install as well, but would those be reasons for overwriting my.cnf?

Mark

[Dave#]

Originally posted by LancerForums
No control panel at all. I'm using RH 7.3 but I never installed it... my host did that for me and they probably used an rpm. I think mysql was an rpm install as well, but would those be reasons for overwriting my.cnf?

Mark


Unlikely that RPM's are the problem unless your host has upgrade mysql recently - RPM's are just packages

[LancerForums]

Any idea what could be the source of the problem?

Thanks,

Mark

[LancerForums]

Does anyone happen to any sites that may be able to help me with my problem?

Thanks,

Mark