Re: MySQL Max connections?
Am Dienstag, den 07.12.2004, 11:47 -0600 schrieb Jacob S: Hello list, I am having a problem with mysql refusing connections intermittently on a webserver. It acts like an invisible limit has been hit on the maximum number of connections. Not finding any info in the config files about the max number of connections allowed, I did some googling. This url is the closest I can find, though I notice their syntax seems to be a lot different from the standard mysql config file in Debian. Not to mention that the page was last updated 09/2003. http://www-css.fnal.gov/dsg/external/freeware/mysqlTuning.html Can anyone tell me if this is the correct way to increase the max number of connections, or how to do it if it's not? I would try to test it myself, but it is a bit hard to do on a busy server and when I would need a huge number of connections. There are tow ways to set the limit of maximium connections at one time: - connect to mysql as root and type: mysql set global max_connections=xxx; you can check your vars with: mysql show variables; -- after restart of mysqld the default value is back. - edit /etc/my.cnf and add max_connections = xxx to the [mysqld] section -- this would cause mysqld to use this var after next restart good luck, Achim TIA, Jacob -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED]
Re: MySQL Max connections?
Can anyone tell me if this is the correct way to increase the max number of connections, or how to do it if it's not? I would try to test it myself, but it is a bit hard to do on a busy server and when I would need a huge number of connections. Back in the olden-days (1999) on Solaris boxes, a company I was working at ran into a problem where MySQL hit the maximum number of possible socket descriptors compiled into the kernel, so couldn't get a pipe from the webserver to the DB. Probably not your problem, but keep an eye out for gotchas in the environment too. -- Ward -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED]
Re: MySQL Max connections?
On Tue, 07 Dec 2004 19:07:08 +0100 Achim Schmidt [EMAIL PROTECTED] wrote: Am Dienstag, den 07.12.2004, 11:47 -0600 schrieb Jacob S: snip Can anyone tell me if this is the correct way to increase the max number of connections, or how to do it if it's not? I would try to test it myself, but it is a bit hard to do on a busy server and when I would need a huge number of connections. There are tow ways to set the limit of maximium connections at one time: - connect to mysql as root and type: mysql set global max_connections=xxx; you can check your vars with: mysql show variables; -- after restart of mysqld the default value is back. - edit /etc/my.cnf and add max_connections = xxx to the [mysqld] section -- this would cause mysqld to use this var after next restart Excellent information. Thanks. Do you or anybody else know what the default number is for max_connections? I suppose it could be system load causing my problem, since top usually shows an average of 4.0 or 5.0 when the problem occurs. It seems like the whole mysql server or apache would be killed though if that were the case. (Server is a 2.2Ghz P4 with 512MB of ram.) Thanks again, Jacob -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED]
Re: MySQL Max connections?
This one time, at band camp, Jacob S said: Do you or anybody else know what the default number is for max_connections? I suppose it could be system load causing my problem, since top usually shows an average of 4.0 or 5.0 when the problem occurs. It seems like the whole mysql server or apache would be killed though if that were the case. (Server is a 2.2Ghz P4 with 512MB of ram.) IIRC it's 100 by default. With basically no tuning here: mysql show variables like 'max%'; +---++ | Variable_name | Value | +---++ | max_connections | 100| | max_connect_errors| 10 | | max_delayed_threads | 20 | | max_user_connections | 0 | +---++ (some lines snipped as not useful in context). HTH, -- - | ,''`.Stephen Gran | | : :' :[EMAIL PROTECTED] | | `. `'Debian user, admin, and developer | |`- http://www.debian.org | - pgpeHXs787i4c.pgp Description: PGP signature
Re: MySQL Max connections?
On Tue, 7 Dec 2004 14:14:41 -0500 Stephen Gran [EMAIL PROTECTED] wrote: This one time, at band camp, Jacob S said: Do you or anybody else know what the default number is for max_connections? I suppose it could be system load causing my problem, since top usually shows an average of 4.0 or 5.0 when the problem occurs. It seems like the whole mysql server or apache would be killed though if that were the case. (Server is a 2.2Ghz P4 with 512MB of ram.) IIRC it's 100 by default. With basically no tuning here: mysql show variables like 'max%'; whoops... I guess that should have been obvious from Achim Schmidt's e-mail. Thanks just the same! +---++ | Variable_name | Value | +---++ | max_connections | 100| snip Jacob -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED]
Re: MySQL Max connections?
Am Dienstag, den 07.12.2004, 13:07 -0600 schrieb Jacob S: Do you or anybody else know what the default number is for max_connections? 100 is the default-value for max_connections I suppose it could be system load causing my problem, since top usually shows an average of 4.0 or 5.0 when the problem occurs. It seems like the whole mysql server or apache would be killed though if that were the case. (Server is a 2.2Ghz P4 with 512MB of ram.) just took a look and our primary webserver - it's running 185 mysqld and 36 httpd processes - load average is 0.11, 0.19, 0.18 on a 1.8 ghz celeron with 1.5 gb of physical ram. perhaps some more ram whould help fixing your problem - afaik mysql is getting much faster and efficient as more ram it can use. Achim -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED]
Re: MySQL Max connections?
On Tue, 2004-12-07 at 11:47 -0600, Jacob S wrote: Can anyone tell me if this is the correct way to increase the max number of connections, or how to do it if it's not? I would try to test it myself, but it is a bit hard to do on a busy server and when I would need a huge number of connections. Other replies have spoken about the max_connections mysql settings so I wont repeat. Just wanted to add some Debian Woody specific experience. With mysql 3.23 (or a back-ported 4.0) the maximum connections appear to be around 300, whatever mysql is set to. This can be increased to around 800 by altering the maximum user processes in the safe_mysqld (or mysqld_safe in v4.0) script. We simply added the line: ulimit -u unlimited However if you have InnoDB support enabled in mysqld the maximum still seems to be limited to just over 400 connections. Upgrading libc6 to 2.3 instead of 2.2 was the only 'fix' we found to get the limit, with InnoDB, back up to about 800. Of course the machine isn't strictly Woody then though. Be warned that upgrading libc6 (or especially trying to downgrade it later) is not always the easiest of things to do! Simon -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED]
