ERROR 1040 (00000): Too many connections

[jeffmic]

I'm having this error come up on my OSX 10.5 server in the past 3 days.

ERROR 1040 (00000): Too many connections

I am using the default settings in that 10.5 server's httpd.conf file
Is there something I should do to increase parameters to stop this?
It's causing my http pages to hang longer than they should

BTW, how DO you clear those pages faster?

thanks,

[billc108]

I'm having this error come up on my OSX 10.5 server in the past 3 days.

ERROR 1040 (00000): Too many connections

I am using the default settings in that 10.5 server's httpd.conf file
Is there something I should do to increase parameters to stop this?
It's causing my http pages to hang longer than they should

What are the specs on your machine(s) and your traffic load? Is there plenty of free space on the drives/directories in question?

Are you on a shared host? If so, there may be another user's script which is opening up spurrious (sp?) connections and clogging up the works on you.

You might have a look at

http://www.mysql.com/doc/en/Too_many_connections.html

Hope that helps.

[jeffmic]

I am using a new Mac Pro box with 2 gig of memory and a 8 core 2.8 set of processors. I'm running OS X 10.5.2 Server. Screamin fast, but not with the settings I have in my.cnf apparently.

The mySql becomes clogged and won't release the connections.
I've tried tons of settings, but I really need some help to avoid restarting the server everyday now.

I just found this comment on a website today. There is not resolution to it, but it's exactly the same thing I'm going through....


<<TOO MANY CONNECTIONS ERROR## on MySQL 2007-10-09

The past few months, we've been noticing an intermittant problem with MySQL in which some sites stop working because of a 'TOO MANY CONNECTIONS' error.## This morning, MySQL was clogged again and we think we found the problem.## One group's website is generating massive connections which then sleep and are not released.## This continues until it floods all the server's available MySQL connections until the maximum allowed is reached, effectively shutting down all the other MySQL sites on the server (including the Spirit support team's sites and our application system).

The group's site has been temporarily taken offline until we can work with them on fixing it.## Sorry to those groups whose sites were malfunctioning or inaccessible because of this.>>



Here is my.cnf settings.
Can anyone care to comment on a better way to set it to make it not get clogged up. I do not have much business on my website, but something on my end if wrong apparently.

thanks so much!
Jeff


----my.cnf settings below---


# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /var/mysql/mysql.sock
skip-locking
max_connections = 96
key_buffer = 128M
max_allowed_packet = 2M
table_cache = 512
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 16M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size = 32M
tmp_table_size = 64M
thread_cache = 8
thread_cache_size = 50
query_cache_limit = 1M
query_cache_min_res_unit = 1k
query_cache_type = 1
interactive_timeout = 100
wait_timeout = 15
connect_timeout = 10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
skip-thread-priority

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 384M
#bdb_max_lock = 100000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/mysql/
#innodb_log_arch_dir = /var/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

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

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[DrByte]

When it appears to be hanging, can you log into the mysql application as root, and run the SHOW PROCESSLIST command?
What information comes back?

(You could use phpMyAdmin, but have to login as root during a hang, and click on the Processes tab/option, to get the same information in a GUI format.)

This should reveal which processes are locked and on what tables, etc. This could give a clue to the cause.

[Website Rob]

Not familiar with a Mac Server, I use L.A.M.P., but here are two suggestions.

In your Server 'php.ini' file, turn OFF persistent connections for mySQL.


In the 'my.cnf' file you posted above, change this setting.

from
max_connections = 96 << that is a very low setting

to
max_connections = 300

[jeffmic]

When it appears to be hanging, can you log into the mysql application as root, and run the SHOW PROCESSLIST command?
What information comes back?

(You could use phpMyAdmin, but have to login as root during a hang, and click on the Processes tab/option, to get the same information in a GUI format.)

This should reveal which processes are locked and on what tables, etc. This could give a clue to the cause.

so far, this is all that I am seeing
(but the SQL may not be clogged right now)
I surely do not understand what it means below

12549 root localhost:52238 mczencart Sleep 14
12551 root localhost:52241 mczencart Query 0 show processlist

[DrByte]

In the 'my.cnf' file you posted above, change this setting.

from
max_connections = 96 << that is a very low setting

to
max_connections = 300

I was thinking of suggesting raising that number, too. But that may not actually be the real cause, and lifting it without inspecting what's happening during a freeze-up may simply just mask the real root cause.

The default appears to be 100 according to mysql documentation. But I suppose that depends on the config model used during setup.

[DrByte]

12549 root localhost:52238 mczencart Sleep 14
12551 root localhost:52241 mczencart Query 0 show processlist

The first one is just a sleeping open connection.
The second is your query to display the list of processes.

I suspect you'll see 97 entries when your server is "clogged", not just 2.

[Website Rob]

I was thinking of suggesting raising that number, too. But that may not actually be the real cause, and lifting it without inspecting what's happening during a freeze-up may simply just mask the real root cause.

The default appears to be 100 according to mysql documentation. But I suppose that depends on the config model used during setup.

I believe the 'cause' is due to open connections not closing in a timely manner. That is why the suggestion to turn OFF persistent mySQL connections. There are other tweaks that can be done to mySQL settings but this is not a mySQL Forum.

As to the number of connections, on busy sites with lots of mySQL connections -- such as this Forum -- I wouldn't be surprized if an even higher amount is used. Default settings are fine to start with but once the Server usage is better ascertained, that's when tweaking / customizing for that specific Server comes in.

[DrByte]

Understood. Agreed in principle.

Zen Cart does not use persistent connections, unless you intentionally change the coding in core files in order to use them. The configure.php setting is presently ignored, and the db layer always uses non-persistent connections.