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,

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

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.

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

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.

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

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.

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

Website Rob:

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.

jeffmic:

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.

DrByte:

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. :wink:

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.

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.

Zen Cart not using persistent connections is something I should have mentioned as that is clearly shown in both config files. Trouble is, other scripts / processes could use mySQL and by disabling persistent connections Server wide, is the only sure way to eliminate it as a problem area.

Website Rob:

Trouble is, other scripts / processes could use mySQL and by disabling persistent connections Server wide, is the only sure way to eliminate it as a problem area.

Yes, good point.

I believe Jeff manages his own server, and I think it's only Zen Cart that's running on it.

Wondering if it might be useful to switch to only using persistent connections, instead of the other way around ... for this server?

That is a viable option but then other tweaking / modifing to the 'my.cnf' file would need to be done. That would also include testing plus trial & error to see what works best for that particular Server.

Mind you, that's not say with persistent connections turned OFF you don't have to worry. Tweaking plus trial & error still needs to be done, only not as much. :wink:

Here is the data that is displayed with SHOW PROCESSLIST when the server gets clogged. I have to shut off both the web and mySQL to get this to free up.
It's all part of the Too Many connections causing the server to be brought to it's knees. Does anyone see what is really causing the 'stuck' in this data below?

17442,root,localhost,mczencart,Query,346,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17445,root,localhost,mczencart,Query,338,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17447,root,localhost,mczencart,Query,336,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17453,root,localhost,mczencart,Query,320,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17459,root,localhost,mczencart,Query,303,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17465,root,localhost,mczencart,Query,287,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17467,root,localhost,mczencart,Query,282,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17468,root,localhost,mczencart,Query,282,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17479,root,localhost,mczencart,Query,268,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17480,root,localhost,mczencart,Query,268,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17485,root,localhost,mczencart,Query,264,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17486,root,localhost,mczencart,Query,264,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17493,root,localhost,mczencart,Query,246,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17503,root,localhost,mczencart,Query,237,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17504,root,localhost,mczencart,Query,237,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17505,root,localhost,mczencart,Query,237,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17506,root,localhost,mczencart,Query,236,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17507,root,localhost,mczencart,Query,236,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17508,root,localhost,mczencart,Query,236,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17511,root,localhost,mczencart,Query,232,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17512,root,localhost,mczencart,Query,232,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17514,root,localhost,mczencart,Query,227,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17515,root,localhost,mczencart,Query,227,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17521,root,localhost,mczencart,Query,222,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17522,root,localhost,mczencart,Query,222,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17528,root,localhost,mczencart,Query,197,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17529,root,localhost,mczencart,Query,197,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17530,root,localhost,mczencart,Query,191,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17531,root,localhost,mczencart,Query,191,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17540,root,localhost,mczencart,Query,166,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17541,root,localhost,mczencart,Query,166,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17547,root,localhost,mczencart,Query,145,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17550,root,localhost,mczencart,Query,136,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17551,root,localhost,mczencart,Query,136,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17571,root,localhost,mczencart,Query,116,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17572,root,localhost,mczencart,Query,114,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17573,root,localhost,mczencart,Query,114,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17574,root,localhost,mczencart,Query,113,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17575,root,localhost,mczencart,Query,113,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17588,root,localhost,mczencart,Query,98,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17589,root,localhost,mczencart,Query,97,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17590,root,localhost,mczencart,Query,97,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17591,root,localhost,mczencart,Query,97,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17598,root,localhost,mczencart,Query,86,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17599,root,localhost,mczencart,Query,85,Copying to tmp table,"select p.products_id, p.products_image\r\n from orders_products opa, orders_produc"
17600,root,localhost,mczencart,Query,77,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17601,root,localhost,mczencart,Query,72,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17602,root,localhost,mczencart,Query,70,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17605,root,localhost,mczencart,Query,60,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17606,root,localhost,mczencart,Query,56,Copying to tmp table,"select p.products_id, p.products_image\r\n from orders_products opa, orders_produc"
17611,root,localhost,mczencart,Query,42,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17617,root,localhost,mczencart,Query,32,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17623,root,localhost,mczencart,Query,24,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17629,root,localhost:63136,,Sleep,11,,
17631,root,localhost:63138,mczencart,Sleep,9,,
17634,root,localhost,mczencart,Query,3,Sending data,"select count(distinct p.products_id) as total from (products p, jeffsearch j, manufacturers m) whe"
17635,root,localhost:63139,mczencart,Sleep,0,,
17636,root,localhost:63140,mczencart,Query,0,,show processlist

Seems like it's clogging up on your custom search queries.

Going through the thread I have some issues with the same error.

This may not be your issue but it was mine.

the Sql Database got huge which is my fault for not running maintenance scripts on a cron job or a timely manner. this created my hard drive to overload.

It got down to 1 MB of available space.

Fixed by deleting archived files I had on the system giving me some space.
Then ran a repair and maint on the My sql and all where safe.

pain so now its on a cron job and I have set aside an hour a month in ical to email me to check the server.

The server has been running for years with out an outage so I tend to take it for granted that it will work.

Suggest you check the hard drive available space and it will work a lot better with a couple of GB of ram