Re: too many connections
Hi, I think you need to take into consideration the following thins: 1. The number of connections that can come at any point of time (depending up on the statisics) set global max_connections=1000 2.decrease the wait time out variable wait_timeout=30 or even lower value depending upon connections. 3. Also check if the queries are getting locked or do you have any slow queries during that time. please let me know what are your current values for the above parameters what is the value of `netstat -an |grep -i est |wc -l` during the time of the error Regards, Pradeep Chandru. Brent Baisley wrote: One thing a lot of people miss is that web server KeepAliveTimeout setting has an effect on pconnect. Apache will keep the thread handling that client open for the KeepAliveTimeout duration, which will keep the database connection open for reuse. You can lower your KeepAliveTimeout or not use pconnect. Brent Baisley On Fri, Sep 19, 2008 at 3:51 PM, Jaime Fuentes [EMAIL PROTECTED] wrote: You have to use mysql 64bits on S.O. 64bits --Mensaje original-- De: Martin Gainty Para: Kinney, Gail Para: 'mysql@lists.mysql.com' Enviado: 19 Sep 2008 10:51 Asunto: RE: too many connections in my.cnf configuration file try upping the number of connections max_connections=3072 to max_connections=6144 Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Date: Fri, 19 Sep 2008 09:33:58 -0600 Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] _ Want to do more with Windows Live? Learn 10 hidden secrets from Jamie. http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008 Enviado desde mi BlackBerry de Claro. ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
too many connections
Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED]
Re: too many connections
Restart MySQL server On 9/19/08, Kinney, Gail [EMAIL PROTECTED] wrote: Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: too many connections
On Fri, Sep 19, 2008 at 9:07 PM, Olexandr Melnyk [EMAIL PROTECTED] wrote: Restart MySQL server On 9/19/08, Kinney, Gail [EMAIL PROTECTED] wrote: Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. run the following from shell, mysqladmin flush-hosts and edit my.cnf and add or change the value ( max_connections by default is 100 ) max_connections=500 save my.cnf and restart mysql.
RE: too many connections
in my.cnf configuration file try upping the number of connections max_connections=3072 to max_connections=6144 Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Date: Fri, 19 Sep 2008 09:33:58 -0600 Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] _ Want to do more with Windows Live? Learn “10 hidden secrets” from Jamie. http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008
Re: too many connections
On 9/19/08, Kinney, Gail [EMAIL PROTECTED] wrote: Hello, We have MySQL 4.0.14 and have just gotten an error: Please help. Answer the door, 2004 is calling. -- -jp I hope that someday we will be able to put away our fears and prejudices and just laugh at people. deepthoughtsbyjackhandy.com
RE: too many connections
Gail, I know the list has already recommended allowing more connections but the bigger question is what is sucking them all up. Even with 1000 connections things like apache can only use the number of connections that there are processes (* the number of connections used within each process). As a fast workaround, increase the connections but for a long term solution you really need to find out what the problem is, now how to work around it. Gary From: Kinney, Gail [mailto:[EMAIL PROTECTED] Sent: Fri 9/19/2008 8:33 AM To: 'mysql@lists.mysql.com' Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED]
Re: too many connections
In case you're using PHP, in theory all database connections should be closed when script stops execution. I'm not sure if it's always like that in practice. Persistent connections can be a quick fix to your problem, but as was mentioned in the previous mail, it's better to find out why there's so many of them. On 9/19/08, Gary W. Smith [EMAIL PROTECTED] wrote: Gail, I know the list has already recommended allowing more connections but the bigger question is what is sucking them all up. Even with 1000 connections things like apache can only use the number of connections that there are processes (* the number of connections used within each process). As a fast workaround, increase the connections but for a long term solution you really need to find out what the problem is, now how to work around it. Gary From: Kinney, Gail [mailto:[EMAIL PROTECTED] Sent: Fri 9/19/2008 8:33 AM To: 'mysql@lists.mysql.com' Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: too many connections
Then killing the server process should be safe. Except that server startup may take a while. On Fri, Sep 19, 2008 at 7:25 PM, Kinney, Gail [EMAIL PROTECTED]wrote: We are using the default storage engine - INNODB *From:* Olexandr Melnyk [mailto:[EMAIL PROTECTED] *Sent:* Friday, September 19, 2008 9:44 AM *To:* Kinney, Gail *Subject:* Re: too many connections Are there any UPDATE queries being executed? Which storage engines are you using? On 9/19/08, *Kinney, Gail* [EMAIL PROTECTED] wrote: Ok, we tried that but we are getting and error that it can't be stopped (timed out), although status says that it is stopping. Do we need to reboot the entire machine? *From:* Olexandr Melnyk [mailto:[EMAIL PROTECTED] *Sent:* Friday, September 19, 2008 9:40 AM *To:* Kinney, Gail *Subject:* Re: too many connections Yes, that's what I was referring to On 9/19/08, *Kinney, Gail* [EMAIL PROTECTED] wrote: Thanks you for responding. Our MySQL is on a web server for the entire campus. Can we just restart the service? *From:* Olexandr Melnyk [mailto:[EMAIL PROTECTED] *Sent:* Friday, September 19, 2008 9:38 AM *To:* Kinney, Gail; mysql@lists.mysql.com *Subject:* Re: too many connections Restart MySQL server On 9/19/08, *Kinney, Gail* [EMAIL PROTECTED] wrote: Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: too many connections
PHP provides both msql_connect and mysql_pconnect. The former does indeed create a new connection to process each request and closes it auto-magically upon completion. The latter creates a rather half-assed connection pool; once a connection is allocated by PHP, it is held open and reused for subsequent requests. New connections are created if no persistent connection is available. Unfortunately, connections allocated though mysql_pconnect are never closed. If the rate of requests should spike, PHP will potentially allocate every connection and never release them, even after traffic returns to normal, which counter-indicates using the method for any pratiacl web application. Most developers/admins prefer to take the hit and use mysql_connect, opening and closing a connection for each request rather than risk having all connections consumed. - michael dykman On Fri, Sep 19, 2008 at 12:07 PM, Olexandr Melnyk [EMAIL PROTECTED] wrote: In case you're using PHP, in theory all database connections should be closed when script stops execution. I'm not sure if it's always like that in practice. Persistent connections can be a quick fix to your problem, but as was mentioned in the previous mail, it's better to find out why there's so many of them. On 9/19/08, Gary W. Smith [EMAIL PROTECTED] wrote: Gail, I know the list has already recommended allowing more connections but the bigger question is what is sucking them all up. Even with 1000 connections things like apache can only use the number of connections that there are processes (* the number of connections used within each process). As a fast workaround, increase the connections but for a long term solution you really need to find out what the problem is, now how to work around it. Gary From: Kinney, Gail [mailto:[EMAIL PROTECTED] Sent: Fri 9/19/2008 8:33 AM To: 'mysql@lists.mysql.com' Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many connections
You have to use mysql 64bits on S.O. 64bits --Mensaje original-- De: Martin Gainty Para: Kinney, Gail Para: 'mysql@lists.mysql.com' Enviado: 19 Sep 2008 10:51 Asunto: RE: too many connections in my.cnf configuration file try upping the number of connections max_connections=3072 to max_connections=6144 Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Date: Fri, 19 Sep 2008 09:33:58 -0600 Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] _ Want to do more with Windows Live? Learn “10 hidden secrets” from Jamie. http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008 Enviado desde mi BlackBerry de Claro.
Re: too many connections
One thing a lot of people miss is that web server KeepAliveTimeout setting has an effect on pconnect. Apache will keep the thread handling that client open for the KeepAliveTimeout duration, which will keep the database connection open for reuse. You can lower your KeepAliveTimeout or not use pconnect. Brent Baisley On Fri, Sep 19, 2008 at 3:51 PM, Jaime Fuentes [EMAIL PROTECTED] wrote: You have to use mysql 64bits on S.O. 64bits --Mensaje original-- De: Martin Gainty Para: Kinney, Gail Para: 'mysql@lists.mysql.com' Enviado: 19 Sep 2008 10:51 Asunto: RE: too many connections in my.cnf configuration file try upping the number of connections max_connections=3072 to max_connections=6144 Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Date: Fri, 19 Sep 2008 09:33:58 -0600 Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] _ Want to do more with Windows Live? Learn 10 hidden secrets from Jamie. http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008 Enviado desde mi BlackBerry de Claro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many connections
Hi All, I am trying to connect to mysql server. Buts, Its giving too many connections. How to increase the max_connection on mysql server. When i am giving mysql -u root -ppassword Still, its giving too many connections. How to solve this problem. --Thanks Krishna Chandra Prajapati
Re: Too many connections
Thanks a lot. Is there any way to increase the maximum no of connections without restarting mysql server. On Tue, Aug 5, 2008 at 2:39 PM, Vladislav Vorobiev [EMAIL PROTECTED] wrote: 2008/8/5 Krishna Chandra Prajapati [EMAIL PROTECTED]: Hi All, I am trying to connect to mysql server. Buts, Its giving too many connections. How to increase the max_connection on mysql server. When i am giving mysql -u root -ppassword Still, its giving too many connections. How to solve this problem. set-variable=max_connections=your value in my.cnf and restart mysql server. I think this is a problem. Vladislav -- Krishna Chandra Prajapati
Re: Too many connections
you can do this set global max_connections=2500; On 8/5/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Thanks a lot. Is there any way to increase the maximum no of connections without restarting mysql server. On Tue, Aug 5, 2008 at 2:39 PM, Vladislav Vorobiev mymir.org@ googlemail.com wrote: 2008/8/5 Krishna Chandra Prajapati [EMAIL PROTECTED]: Hi All, I am trying to connect to mysql server. Buts, Its giving too many connections. How to increase the max_connection on mysql server. When i am giving mysql -u root -ppassword Still, its giving too many connections. How to solve this problem. set-variable=max_connections=your value in my.cnf and restart mysql server. I think this is a problem. Vladislav -- Krishna Chandra Prajapati
Re: Too many connections
2008/8/5 Krishna Chandra Prajapati [EMAIL PROTECTED]: Hi All, I am trying to connect to mysql server. Buts, Its giving too many connections. How to increase the max_connection on mysql server. When i am giving mysql -u root -ppassword Still, its giving too many connections. How to solve this problem. set-variable=max_connections=your value in my.cnf and restart mysql server. I think this is a problem. Vladislav -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many connections crashing MySQL?
Hello. Please, could you provide a resolved stack trace. I know sometimes, it is difficult in a heavy loaded production environment, but check if the problem still exists on the official binaries of the latest release. Have a look here as well: http://bugs.mysql.com/bug.php?id=15868 sheeri kritzer wrote: We're running MySQL version 4.1.12 on Fedora Core 3 64-bit. we've been crashing; here is a mysqld.err file from one crash: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagno= se the problem, but since we have already crashed, something is definitely wro= ng and this may fail. key_buffer_size=3D335544320 read_buffer_size=3D131072 max_used_connections=3D2049 max_connections=3D2048 threads_connected=3D371 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =3D 4784112 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 060108 14:43:07 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. [InnoDB crash recovery elided] - We have 6G of memory on the server, and we checked -- we're not running out of memory. I'm guessing that mysqld crashed because of that 2049th connection -- shouldn't it just refuse the connection, not crash? The variables in the mysqld.err match the /etc/my.cnf: [mysqld] old-passwords tmpdir =3D /tmp/ datadir =3D /var/lib/mysql socket =3D /var/lib/mysql/mysql.sock port=3D 3306 key_buffer =3D 320M max_allowed_packet =3D 16M table_cache =3D 1024 thread_cache=3D 80 ft_min_word_len =3D 3 # Use this to prevent access via TCP/IP # skip_networking # Query Cache Settings - OFF due to overload of Session table query_cache_size =3D 32M query_cache_type =3D 2 # Log queries taking longer than long_query_time seconds long_query_time =3D 4 log-slow-queries =3D /var/lib/mysql/slow-queries.log log-error =3D /var/lib/mysql/mysqld.err # Try number of CPU's*2 for thread_concurrency thread_concurrency =3D 12 interactive_timeout =3D 28800 wait_timeout =3D 30 # when you change this recalculate total possible mysqld memory usage!! # key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections max_connections =3D 2048 max_connect_errors =3D 128 # Replication Master Server (default) # binary logging is required for replication log-bin server-id =3D 15 max_binlog_size =3D 2G # InnoDB tables innodb_data_home_dir =3D /var/lib/mysql/ innodb_data_file_path =3D ibdata1:3G;ibdata2:3G; innodb_log_group_home_dir =3D /var/lib/mysql/ innodb_log_arch_dir =3D /var/lib/mysql/ innodb_buffer_pool_size =3D 4G innodb_additional_mem_pool_size =3D 40M innodb_log_file_size =3D 160M innodb_log_buffer_size =3D 80M innodb_flush_log_at_trx_commit =3D 0 innodb_lock_wait_timeout =3D 50 innodb_thread_concurrency =3D 8 innodb_file_io_threads =3D 4 ---= - Any help is appreciated. We've been crashing around the same time every day, our busiest time of day. -Sheeri -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many connections crashing MySQL?
I would have provided a resolved stack trace if there was one referred to in the mysqld.err. I believe it's what Alex said: innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB uses more memory than I have. To the MySQL folks, can the crash error be changed? I appreciate that it's in the docs, and I should know them back and forth of course, but when the crash error says that the total possible memory is key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size) that's actually wrong. Thanx for everyone's help! -Sheeri On 1/10/06, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Please, could you provide a resolved stack trace. I know sometimes, it is difficult in a heavy loaded production environment, but check if the problem still exists on the official binaries of the latest release. Have a look here as well: http://bugs.mysql.com/bug.php?id=15868 sheeri kritzer wrote: We're running MySQL version 4.1.12 on Fedora Core 3 64-bit. we've been crashing; here is a mysqld.err file from one crash: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagno= se the problem, but since we have already crashed, something is definitely wro= ng and this may fail. key_buffer_size=3D335544320 read_buffer_size=3D131072 max_used_connections=3D2049 max_connections=3D2048 threads_connected=3D371 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =3D 4784112 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 060108 14:43:07 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. [InnoDB crash recovery elided] - We have 6G of memory on the server, and we checked -- we're not running out of memory. I'm guessing that mysqld crashed because of that 2049th connection -- shouldn't it just refuse the connection, not crash? The variables in the mysqld.err match the /etc/my.cnf: [mysqld] old-passwords tmpdir =3D /tmp/ datadir =3D /var/lib/mysql socket =3D /var/lib/mysql/mysql.sock port=3D 3306 key_buffer =3D 320M max_allowed_packet =3D 16M table_cache =3D 1024 thread_cache=3D 80 ft_min_word_len =3D 3 # Use this to prevent access via TCP/IP # skip_networking # Query Cache Settings - OFF due to overload of Session table query_cache_size =3D 32M query_cache_type =3D 2 # Log queries taking longer than long_query_time seconds long_query_time =3D 4 log-slow-queries =3D /var/lib/mysql/slow-queries.log log-error =3D /var/lib/mysql/mysqld.err # Try number of CPU's*2 for thread_concurrency thread_concurrency =3D 12 interactive_timeout =3D 28800 wait_timeout =3D 30 # when you change this recalculate total possible mysqld memory usage!! # key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections max_connections =3D 2048 max_connect_errors =3D 128 # Replication Master Server (default) # binary logging is required for replication log-bin server-id =3D 15 max_binlog_size =3D 2G # InnoDB tables innodb_data_home_dir =3D /var/lib/mysql/ innodb_data_file_path =3D ibdata1:3G;ibdata2:3G; innodb_log_group_home_dir =3D /var/lib/mysql/ innodb_log_arch_dir =3D /var/lib/mysql/ innodb_buffer_pool_size =3D 4G innodb_additional_mem_pool_size =3D 40M innodb_log_file_size =3D 160M innodb_log_buffer_size =3D 80M innodb_flush_log_at_trx_commit =3D 0 innodb_lock_wait_timeout =3D 50 innodb_thread_concurrency =3D 8 innodb_file_io_threads =3D 4 ---= - Any help is appreciated. We've been crashing around the same time every day, our busiest time of day. -Sheeri -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives:
too many connections crashing MySQL?
We're running MySQL version 4.1.12 on Fedora Core 3 64-bit. we've been crashing; here is a mysqld.err file from one crash: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=335544320 read_buffer_size=131072 max_used_connections=2049 max_connections=2048 threads_connected=371 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 4784112 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 060108 14:43:07 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. [InnoDB crash recovery elided] - We have 6G of memory on the server, and we checked -- we're not running out of memory. I'm guessing that mysqld crashed because of that 2049th connection -- shouldn't it just refuse the connection, not crash? The variables in the mysqld.err match the /etc/my.cnf: [mysqld] old-passwords tmpdir = /tmp/ datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock port= 3306 key_buffer = 320M max_allowed_packet = 16M table_cache = 1024 thread_cache= 80 ft_min_word_len = 3 # Use this to prevent access via TCP/IP # skip_networking # Query Cache Settings - OFF due to overload of Session table query_cache_size = 32M query_cache_type = 2 # Log queries taking longer than long_query_time seconds long_query_time = 4 log-slow-queries = /var/lib/mysql/slow-queries.log log-error = /var/lib/mysql/mysqld.err # Try number of CPU's*2 for thread_concurrency thread_concurrency = 12 interactive_timeout = 28800 wait_timeout = 30 # when you change this recalculate total possible mysqld memory usage!! # key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections max_connections = 2048 max_connect_errors = 128 # Replication Master Server (default) # binary logging is required for replication log-bin server-id = 15 max_binlog_size = 2G # InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:3G;ibdata2:3G; innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ innodb_buffer_pool_size = 4G innodb_additional_mem_pool_size = 40M innodb_log_file_size = 160M innodb_log_buffer_size = 80M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 innodb_thread_concurrency = 8 innodb_file_io_threads = 4 Any help is appreciated. We've been crashing around the same time every day, our busiest time of day. -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many connections crashing MySQL?
HI, The below equation as been obtained from the docs in mysql.com. As per this equation and looking @ your configs, if definitely looks like a memory problem. innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB In an ideal case the above equation should evaluate to a value lesser than the physical memory available. Thanx Alex On Mon, 09 Jan 2006 22:12:53 +0530, sheeri kritzer [EMAIL PROTECTED] wrote: We're running MySQL version 4.1.12 on Fedora Core 3 64-bit. we've been crashing; here is a mysqld.err file from one crash: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=335544320 read_buffer_size=131072 max_used_connections=2049 max_connections=2048 threads_connected=371 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 4784112 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 060108 14:43:07 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. [InnoDB crash recovery elided] - We have 6G of memory on the server, and we checked -- we're not running out of memory. I'm guessing that mysqld crashed because of that 2049th connection -- shouldn't it just refuse the connection, not crash? The variables in the mysqld.err match the /etc/my.cnf: [mysqld] old-passwords tmpdir = /tmp/ datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock port= 3306 key_buffer = 320M max_allowed_packet = 16M table_cache = 1024 thread_cache= 80 ft_min_word_len = 3 # Use this to prevent access via TCP/IP # skip_networking # Query Cache Settings - OFF due to overload of Session table query_cache_size = 32M query_cache_type = 2 # Log queries taking longer than long_query_time seconds long_query_time = 4 log-slow-queries = /var/lib/mysql/slow-queries.log log-error = /var/lib/mysql/mysqld.err # Try number of CPU's*2 for thread_concurrency thread_concurrency = 12 interactive_timeout = 28800 wait_timeout = 30 # when you change this recalculate total possible mysqld memory usage!! # key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections max_connections = 2048 max_connect_errors = 128 # Replication Master Server (default) # binary logging is required for replication log-bin server-id = 15 max_binlog_size = 2G # InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:3G;ibdata2:3G; innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ innodb_buffer_pool_size = 4G innodb_additional_mem_pool_size = 40M innodb_log_file_size = 160M innodb_log_buffer_size = 80M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 innodb_thread_concurrency = 8 innodb_file_io_threads = 4 Any help is appreciated. We've been crashing around the same time every day, our busiest time of day. -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
too many connections
Hi,
I guess it is a stupid simple question:
I have seen the following error in the log files:
DBI connect('database=[database]','[username]',...) failed: #08004Too many
connections at /[path_to_script] line 12
I have taken a look in my.cnf but I couldn't find some settings for
increasing the possible number of connections or something like that.
Can you tell me what can I do to do this?
Teddy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many connections
use a variable called max_connections( if its not there in my.cnf just add
it ) and restart mysql
eg . max_connections = 100
Kishore Jalleda
On 9/27/05, Octavian Rasnita [EMAIL PROTECTED] wrote:
Hi,
I guess it is a stupid simple question:
I have seen the following error in the log files:
DBI connect('database=[database]','[username]',...) failed: #08004Too many
connections at /[path_to_script] line 12
I have taken a look in my.cnf but I couldn't find some settings for
increasing the possible number of connections or something like that.
Can you tell me what can I do to do this?
Teddy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many connections
Joeffrey Betita wrote: hello i just installed mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz, httpd-2.0.54.tar.gz, php-5.0.4.tar.gz etc. on a Intel(R) Pentium(R) 4 CPU 2.40GHz with 1GB RAM this is just a temporary until we buy a new high end server. my-large.cnf is the configuration in the /etc/my.cnf CentOS release 4.0 (Final) is the linux distribution. what is the ideal configuration so that 20,000 user does not encounter the too many connections error when they browse our website. thank you very much. Do you work for Intel? Or do you just like putting (R) after every Registered Trademark(R)? Anyway, on to your problem... Do you expect 20,000 users to all hit your website within a second or so of each other? Somehow I doubt that. The too many connections error refers to how many connections can be open *to the MySQL server* at once. When someone is just viewing one of your pages, they don't have a connection open to Apache, and therefore your PHP scripts aren't holding a MySQL connection open (unless you use persistent connections to MySQL, which isn't the default for PHP). You will only experience a problem if the number of connections at once is greater than the number set up in my.cnf. For example, if MySQL is set up to handle 1000 connections, then it can handle 1000 people all hitting refresh or entering the URL of your website, all at once. There might be 100,000 people looking at your website, but as long as 1000 of them don't all click a link at once, you're sweet. Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
too many connections
hello i just installed mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz, httpd-2.0.54.tar.gz, php-5.0.4.tar.gz etc. on a Intel(R) Pentium(R) 4 CPU 2.40GHz with 1GB RAM this is just a temporary until we buy a new high end server. my-large.cnf is the configuration in the /etc/my.cnf CentOS release 4.0 (Final) is the linux distribution. what is the ideal configuration so that 20,000 user does not encounter the too many connections error when they browse our website. thank you very much. rgds, Joeffrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many connections
Dear all,
I have a problem with the mysql interface for c, which after a couple of
hours thinking about bad or faulty programming are eliminated with
probability almost 100%.
within a loop, I do a mysql_query(), which is executed and I poll the result
and everything is fine. Up to when max_connections of the mysqld is reached:
each of the queries leaves after executing a sleeping mysql process behind on
the server, which hence throws the Too many connections error (or under
certain conditions even a Can't create TCP/IP socket (24)).
Just because of this problem I recently upgraded mysql (including the
libraries) from 4.0.21 to 4.1.12, but the problem still persists.
Does anybody have an idea of how to solve this problem?
Any suggestions are appreciated.
Best,
Ronny
p.s.: code
#define GET_W_NR \
select wort_nr from wortliste where wort_bin='%s' limit 1
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
char *query;
init...
{
mysql_init(mysql);
if ( !mysql_real_connect( mysql, 127.0.0.1, username, passwd,
dbase,
0, /var/lib/mysql/mysql.sock, 0 ) )
{
fprintf(stderr, Failed: %s\n,mysql_error(mysql));
}
if (!(query = (char*) malloc(256*sizeof(char fprintf(stderr, no
pointer);
}
this code of a function is called in a loop and returns with a mysql_error
after `max_connections` cycles:
{
sprintf(query, GET_W_NR, refWort);
if ( mysql_query( mysql, query ) )
{
fprintf(stderr, query failed: %s\n, mysql_error(mysql));
return 0;
}
if ( !( res = mysql_store_result( mysql ) ) )
{fprintf(stderr, store failed: %s\n, mysql_error(mysql)); return
0;}
if ( row = mysql_fetch_row( res ) )
ref_word_nr = atoi(row[0]);
else
{fprintf(stderr, fetch failed: %s\n, mysql_error(mysql)); return
0;}
mysql_free_result(res);
}
exit...
free(query);
mysql_close(mysql);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many connections
Dear all,
I have a problem with the mysql interface for c, which after a couple of
hours thinking about bad or faulty programming are eliminated with
probability almost 100%.
within a loop, I do a mysql_query(), which is executed and I poll the result
and everything is fine. Up to when max_connections of the mysqld is reached:
each of the queries leaves after executing a sleeping mysql process behind on
the server, which hence throws the Too many connections error (or under
certain conditions even a Can't create TCP/IP socket (24)).
Just because of this problem I recently upgraded mysql (including the
libraries) from 4.0.21 to 4.1.12, but the problem still persists.
Does anybody have an idea of how to solve this problem?
Any suggestions are appreciated.
Best,
Ronny
p.s.: code
#define GET_W_NR \
select wort_nr from wortliste where wort_bin='%s' limit 1
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
char *query;
init...
{
mysql_init(mysql);
if ( !mysql_real_connect( mysql, 127.0.0.1, username, passwd,
dbase,
0, /var/lib/mysql/mysql.sock, 0 ) )
{
fprintf(stderr, Failed: %s\n,mysql_error(mysql));
}
if (!(query = (char*) malloc(256*sizeof(char fprintf(stderr, no
pointer);
}
this code of a function is called in a loop and returns with a mysql_error
after `max_connections` cycles:
{
sprintf(query, GET_W_NR, refWort);
if ( mysql_query( mysql, query ) )
{
fprintf(stderr, query failed: %s\n, mysql_error(mysql));
return 0;
}
if ( !( res = mysql_store_result( mysql ) ) )
{fprintf(stderr, store failed: %s\n, mysql_error(mysql)); return
0;}
if ( row = mysql_fetch_row( res ) )
ref_word_nr = atoi(row[0]);
else
{fprintf(stderr, fetch failed: %s\n, mysql_error(mysql)); return
0;}
mysql_free_result(res);
}
exit...
free(query);
mysql_close(mysql);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Too many connections
Hello. May be this would be helpful: http://dev.mysql.com/doc/mysql/en/user-resources.html Jan Pieter Kunst [EMAIL PROTECTED] wrote: Hello all, Suppose I have a LAMP server which functions good enough for daily use by humans, but is occasionally brought to its knees by an automated website-downloader, when such a download involves a lot of database searches, which uses up all the available MySQL connections. Is there anything to do in the MySQL layer of the server to mitigate this problem? I was thinking of a setting like 'database xxx can have only n percent of the maximum number of connections at any given time', but such a setting doesn't seem to exist. Any ideas? Thanks, Jan Pieter Kunst -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many connections
Hello all, Suppose I have a LAMP server which functions good enough for daily use by humans, but is occasionally brought to its knees by an automated website-downloader, when such a download involves a lot of database searches, which uses up all the available MySQL connections. Is there anything to do in the MySQL layer of the server to mitigate this problem? I was thinking of a setting like 'database xxx can have only n percent of the maximum number of connections at any given time', but such a setting doesn't seem to exist. Any ideas? Thanks, Jan Pieter Kunst -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem: Too many connections
Hello. Have you been at: http://dev.mysql.com/doc/mysql/en/too-many-connections.html [snip] Hi, I have a live site using mysql. It is heavily used with over thousand users per minute, with 3 select / update queries per user per minute accounting to over 3000 queries per minute. The problem is that many times connection to this mysql server fails with the error Too many connection also sometimes the server becomes down bcoz of overload. The server has around 4GB of RAM with mysql version of 4.0.23 So my question is , is the problem related with just tuning of configuration parameters or can the problem be solved by any means too. Any kind of help is deeply appreciated. [snip] sapna murari todwal [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem: Too many connections
At 01:25 AM 3/12/2005, sapna murari todwal wrote: Hi, I have a live site using mysql. It is heavily used with over thousand users per minute, with 3 select / update queries per user per minute accounting to over 3000 queries per minute. The problem is that many times connection to this mysql server fails with the error Too many connection also sometimes the server becomes down bcoz of overload. The server has around 4GB of RAM with mysql version of 4.0.23 So my question is , is the problem related with just tuning of configuration parameters or can the problem be solved by any means too. Any kind of help is deeply appreciated. Is it a single or dual processor? Processor Speed? Operating system? Type of database.INNODB, MyISAM? I don't have that many connections on any of my machines. Since you are saying that your server is being overloaded and bogged down you might think about having a fast backend mysql cluster ( http://www.mysql.com/cluster ) instead of it on the same machine as your web server. Possibly even a cluster for the web servers too, but you might not need that. I am more curious about your processor(s). We are creating a program that because of the high hits that we are expecting (actually more about processing then the number of hits through the web server itself), will have to have 3 clusters of servers to run it. One cluster will be for the backend MySQL servers using MySQL's new (or relatively new) clustering. I have never used it yet, but I think it would need at least 3 or 4 servers just to run it at its minimum for what we are going to be doing. The second cluster will be for the web frontend. We actually might be able to get away with just one frontend web server instead of the cluster though. The third cluster will be for the special program that takes a minimum of 30 minutes (on a small test) to run for each client. Some tests on live data have taken more than 5 hours to do all of the computations on a dual XEON with 2 gigs of mem. You are getting more hits than we expect in any minute to your web server, but we will probably have more hits to the DB than you..maybe. :) Or at least hits that take longer to get all of the data they need. Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem: Too many connections
Back to the original problem ;) Please post your my.cnf. -Original Message- From: Steve Buehler [mailto:[EMAIL PROTECTED] Sent: Samstag, 12. März 2005 16:02 To: mysql@lists.mysql.com Subject: Re: Problem: Too many connections At 01:25 AM 3/12/2005, sapna murari todwal wrote: Hi, I have a live site using mysql. It is heavily used with over thousand users per minute, with 3 select / update queries per user per minute accounting to over 3000 queries per minute. The problem is that many times connection to this mysql server fails with the error Too many connection also sometimes the server becomes down bcoz of overload. The server has around 4GB of RAM with mysql version of 4.0.23 So my question is , is the problem related with just tuning of configuration parameters or can the problem be solved by any means too. Any kind of help is deeply appreciated. Is it a single or dual processor? Processor Speed? Operating system? Type of database.INNODB, MyISAM? I don't have that many connections on any of my machines. Since you are saying that your server is being overloaded and bogged down you might think about having a fast backend mysql cluster ( http://www.mysql.com/cluster ) instead of it on the same machine as your web server. Possibly even a cluster for the web servers too, but you might not need that. I am more curious about your processor(s). We are creating a program that because of the high hits that we are expecting (actually more about processing then the number of hits through the web server itself), will have to have 3 clusters of servers to run it. One cluster will be for the backend MySQL servers using MySQL's new (or relatively new) clustering. I have never used it yet, but I think it would need at least 3 or 4 servers just to run it at its minimum for what we are going to be doing. The second cluster will be for the web frontend. We actually might be able to get away with just one frontend web server instead of the cluster though. The third cluster will be for the special program that takes a minimum of 30 minutes (on a small test) to run for each client. Some tests on live data have taken more than 5 hours to do all of the computations on a dual XEON with 2 gigs of mem. You are getting more hits than we expect in any minute to your web server, but we will probably have more hits to the DB than you..maybe. :) Or at least hits that take longer to get all of the data they need. Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem: Too many connections
Hi, I have a live site using mysql. It is heavily used with over thousand users per minute, with 3 select / update queries per user per minute accounting to over 3000 queries per minute. The problem is that many times connection to this mysql server fails with the error Too many connection also sometimes the server becomes down bcoz of overload. The server has around 4GB of RAM with mysql version of 4.0.23 So my question is , is the problem related with just tuning of configuration parameters or can the problem be solved by any means too. Any kind of help is deeply appreciated. Thanks in advance, Sapna
Error: 1040 too many connections
Hi, MySQL 4.0.14 This may have been queried a lot here. We get this error and after re-starting the server (MySQL) it seems to go away for a while. As per instructions we have changed the max connection in the My.ini to 500 (max_connections=500). MySQLAdmin displays connections = 120. Is there anything else we can do to deal with this issue? regards ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: 1040 too many connections
I had the same error for quite some time, the issue seemed to be server resources not being able to kill off connections quickly enough. We had a lot of traffic and some pretty slow queries. Optimizing the queries bought us some time, but ultimately we had to upgrade hardware. This bought us about a year and we are against the wall again now with the 1040 issue. We are probably going to get another server and set up replication. I'd be interested to hear anyone elses stories around this issue, it's been quite a headache for us... Quoting A Z [EMAIL PROTECTED]: Hi, MySQL 4.0.14 This may have been queried a lot here. We get this error and after re-starting the server (MySQL) it seems to go away for a while. As per instructions we have changed the max connection in the My.ini to 500 (max_connections=500). MySQLAdmin displays connections = 120. Is there anything else we can do to deal with this issue? regards ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: 1040 too many connections
Hello. There reason can be in big queries wich consume the resources (temp space for example). Turn on log_slow_queries to find the guilty queries. See: http://dev.mysql.com/doc/mysql/en/Slow_query_log.html You have old enough version of MySQL. Use the latest release. A Z [EMAIL PROTECTED] wrote: Hi, MySQL 4.0.14 This may have been queried a lot here. We get this error and after re-starting the server (MySQL) it seems to go away for a while. As per instructions we have changed the max connection in the My.ini to 500 (max_connections=500). MySQLAdmin displays connections = 120. Is there anything else we can do to deal with this issue? regards ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too Many connections
Hi all we are having a strange problem at the web site I work for (it's an Italian registar). Sometimes (that means: every 15/20 days) our db (MySQL) just hangs with a Too many connections error. The strange thing is that the DB server is stuck untill we restart it (actually, we always had to restart the whole server as root didn't have SUPER privileges, so nobody could connect to the db...) Since I'm pretty sure that we don't have such a traffic that justifies this error (it happened once at 2 a.m), I would *really* like to know what the heck is going on. Odd things are that: - we already raised the number of connections, and that didn't help - we lowered the wait_timeout var, setting it to 3000, but this didn't help neither (we did this because, with a 'show processlist;' we noticed sometimes some hanging queries and we thought: Well if me make the die quicker maybe they won't pile up and won't block our server anymore. Wrong.) Last time this thing happened the 'top' command showed about 380 'httpd' processes sleeping (and the system was very very slow, 'top' again showed a load average above 100, while it usually is at around 0.5). Now what I'm asking is: - has anybody else ever experienced this problem? - in any case, what do you think we should monitor? Top? MySQL logs (We once turned on the General Query Log, but nothing happened and the file became huge in very little time...)? Apache logs? - could it be just a client issue (that is, PHP or Apache don't close the connection - although in PHP we only use mysql_connect, and never do a _pconnect)? How could we monitor this? Our system runs with: - Linux 2.4.22-1.2188.nptl (Fedora 1) - MySQL 4.0.13 - PHP 4.3.4 - Apache 2.0.48 Thank's everybody for any help! Silvio P.S: This is the output of 'show variables\G': *** 1. row *** Variable_name: back_log Value: 50 *** 2. row *** Variable_name: basedir Value: / *** 3. row *** Variable_name: binlog_cache_size Value: 32768 *** 4. row *** Variable_name: bulk_insert_buffer_size Value: 8388608 *** 5. row *** Variable_name: character_set Value: latin1 *** 6. row *** Variable_name: character_sets Value: latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 *** 7. row *** Variable_name: concurrent_insert Value: ON *** 8. row *** Variable_name: connect_timeout Value: 5 *** 9. row *** Variable_name: convert_character_set Value: *** 10. row *** Variable_name: datadir Value: /var/lib/mysql/ *** 11. row *** Variable_name: delay_key_write Value: ON *** 12. row *** Variable_name: delayed_insert_limit Value: 100 *** 13. row *** Variable_name: delayed_insert_timeout Value: 300 *** 14. row *** Variable_name: delayed_queue_size Value: 1000 *** 15. row *** Variable_name: flush Value: OFF *** 16. row *** Variable_name: flush_time Value: 0 *** 17. row *** Variable_name: ft_boolean_syntax Value: + -()~*:| *** 18. row *** Variable_name: ft_min_word_len Value: 4 *** 19. row *** Variable_name: ft_max_word_len Value: 254 *** 20. row *** Variable_name: ft_max_word_len_for_sort Value: 20 *** 21. row *** Variable_name: ft_stopword_file Value: (built-in) *** 22. row *** Variable_name: have_bdb Value: NO *** 23. row *** Variable_name: have_crypt Value: YES *** 24. row *** Variable_name: have_innodb Value: YES *** 25. row *** Variable_name: have_isam Value: YES *** 26. row *** Variable_name: have_raid Value: NO *** 27
Re: Too Many connections
Hello, Silvio. I guess you are using MySQL as shipped in Fedora distibution. See: http://dev.mysql.com/doc/mysql/en/Linux.html You may find that sometimes with non-official binaries happens little problems. So my advice for you in this situtation - upgrade to the latest release and use official binaries from MySQL. Silvio Porcellana [EMAIL PROTECTED] wrote: Hi all we are having a strange problem at the web site I work for (it's an Italian registar). Sometimes (that means: every 15/20 days) our db (MySQL) just hangs with a Too many connections error. The strange thing is that the DB server is stuck untill we restart it (actually, we always had to restart the whole server as root didn't have SUPER privileges, so nobody could connect to the db...) Since I'm pretty sure that we don't have such a traffic that justifies this error (it happened once at 2 a.m), I would *really* like to know what the heck is going on. Odd things are that: - we already raised the number of connections, and that didn't help - we lowered the wait_timeout var, setting it to 3000, but this didn't help neither (we did this because, with a 'show processlist;' we noticed sometimes some hanging queries and we thought: Well if me make the die quicker maybe they won't pile up and won't block our server anymore. Wrong.) Last time this thing happened the 'top' command showed about 380 'httpd' processes sleeping (and the system was very very slow, 'top' again showed a load average above 100, while it usually is at around 0.5). Now what I'm asking is: - has anybody else ever experienced this problem? - in any case, what do you think we should monitor? Top? MySQL logs (We once turned on the General Query Log, but nothing happened and the file became huge in very little time...)? Apache logs? - could it be just a client issue (that is, PHP or Apache don't close the connection - although in PHP we only use mysql_connect, and never do a _pconnect)? How could we monitor this? Our system runs with: - Linux 2.4.22-1.2188.nptl (Fedora 1) - MySQL 4.0.13 - PHP 4.3.4 - Apache 2.0.48 Thank's everybody for any help! Silvio P.S: This is the output of 'show variables\G': *** 1. row *** Variable_name: back_log Value: 50 *** 2. row *** Variable_name: basedir Value: / *** 3. row *** Variable_name: binlog_cache_size Value: 32768 *** 4. row *** Variable_name: bulk_insert_buffer_size Value: 8388608 *** 5. row *** Variable_name: character_set Value: latin1 *** 6. row *** Variable_name: character_sets Value: latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 *** 7. row *** Variable_name: concurrent_insert Value: ON *** 8. row *** Variable_name: connect_timeout Value: 5 *** 9. row *** Variable_name: convert_character_set Value: *** 10. row *** Variable_name: datadir Value: /var/lib/mysql/ *** 11. row *** Variable_name: delay_key_write Value: ON *** 12. row *** Variable_name: delayed_insert_limit Value: 100 *** 13. row *** Variable_name: delayed_insert_timeout Value: 300 *** 14. row *** Variable_name: delayed_queue_size Value: 1000 *** 15. row *** Variable_name: flush Value: OFF *** 16. row *** Variable_name: flush_time Value: 0 *** 17. row *** Variable_name: ft_boolean_syntax Value: + -()~*:| *** 18. row *** Variable_name: ft_min_word_len Value: 4 *** 19. row *** Variable_name: ft_max_word_len Value: 254 *** 20. row *** Variable_name: ft_max_word_len_for_sort Value: 20 *** 21. row
Re: bad too many connections error (os x)
Michael Winston [EMAIL PROTECTED] wrote: Okay, so the first thing to try is obviously enlarge the max_connections. Have you tried this? Yes. It's set to 400 (a number we will never reach unless there's some sort of logjam). max_connect_errors is set to 200. Is it a webserver backend database? Yes. PHP-generated pages. pconnect? Apache limits? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
Michael Winston [EMAIL PROTECTED] wrote: Okay, so the first thing to try is obviously enlarge the max_connections. Have you tried this? Is it a webserver backend database? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
On Sep 2, 2004, at 6:04 AM, Egor Egorov wrote: Michael Winston [EMAIL PROTECTED] wrote: Okay, so the first thing to try is obviously enlarge the max_connections. Have you tried this? Yes. It's set to 400 (a number we will never reach unless there's some sort of logjam). max_connect_errors is set to 200. Is it a webserver backend database? Yes. PHP-generated pages. Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to admin a server that currently has too many connections?
Justin Swanhart [EMAIL PROTECTED] wrote: The problem is that another application has been issuing queries against the table and those queries have never timed out. So now I can't kill the connections, because I can't make a database connection to mysql to even see their thread ids: [EMAIL PROTECTED] mysql]$ mysqladmin -u root -p processlist Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Too many connections' I don't want to kill the mysqld process, because that would stop my index creation. Is there anything you can do in this situation? I don't think connections from mysqladmin should ever be denied. Is this a feature request that I should make? You only have to wait for some connections to free. Next time enlarge max_connections variable. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bad too many connections error (os x)
Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bad too many connections error (os x)
Is it a too many connections or Host blocked because of many connection erros? If it is the later you have reached max_connect_errors and need to issue flush-hosts. -Original Message- From: Michael Winston To: [EMAIL PROTECTED] Sent: 9/1/04 11:02 AM Subject: bad too many connections error (os x) Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael We've run into this problem ourselves, also using 4.0 but on a 64bit AIX. The problem we found was that some queries were firing off threads which never ended. These threads blocked other threads, which blocked other threads... A logjam resulted with all connections ended up being used by the offending threads. The fix was to *ahem* fix our queries so they'd close their database connections once they were complete. You may wish to do a code inspection and verify that every open connection has a matching close. Best of luck, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
I think that there is an error in your code. I have had this problem with a small application I wrote: The problem arises when you don't send the quit command to mysql before dropping tcp. You must send a quit command in every case (if you opened a socket). I can not be more precise because i don't know what labguage you use (C, php, ...). There is a command to reenable connection; if my memory is good: mysqladmin -flush-hosts or something (see mysqladmin reference or help). Santino At 9:02 -0700 1-09-2004, Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
On Sep 1, 2004, at 9:10 AM, V. M. Brasseur wrote: Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael We've run into this problem ourselves, also using 4.0 but on a 64bit AIX. The problem we found was that some queries were firing off threads which never ended. These threads blocked other threads, which blocked other threads... A logjam resulted with all connections ended up being used by the offending threads. The fix was to *ahem* fix our queries so they'd close their database connections once they were complete. You may wish to do a code inspection and verify that every open connection has a matching close. Hmmm. All of our connections are coming from php-generated web pages. PHP automatically closes the connection at the end of the script. Unless I completely misunderstand how this stuff works. Plus, this problem only happens once every few weeks. If some of our queries are causing this, I would expect the problem to occur more often. I'll look into this, though. Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
Michael Winston wrote: On Sep 1, 2004, at 9:10 AM, V. M. Brasseur wrote: Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael We've run into this problem ourselves, also using 4.0 but on a 64bit AIX. The problem we found was that some queries were firing off threads which never ended. These threads blocked other threads, which blocked other threads... A logjam resulted with all connections ended up being used by the offending threads. The fix was to *ahem* fix our queries so they'd close their database connections once they were complete. You may wish to do a code inspection and verify that every open connection has a matching close. Hmmm. All of our connections are coming from php-generated web pages. PHP automatically closes the connection at the end of the script. Unless I completely misunderstand how this stuff works. Plus, this problem only happens once every few weeks. If some of our queries are causing this, I would expect the problem to occur more often. I'll look into this, though. Thanks, Michael You'll also find this problem if you have some badly-optimised queries, or writes that take a long time to run on a frequently-accessed table. For example, if you have a table that frequently accessed and run a slow update on it, any thread trying to read from that table will block. If you get more selects happening to that table coming in while it's still locked, your number of connections in use will shoot upwards rapidly until the slow update finishes and the table is unlocked. Have a look in your slow query log (or turn it on if it's not enabled) to look for any queries like this. Regards, -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bad too many connections error (os x)
Not if the host that is blocked is `localhost`. -Original Message- From: Michael Winston To: Victor Pendleton Cc: '[EMAIL PROTECTED] ' Sent: 9/1/04 11:24 AM Subject: Re: bad too many connections error (os x) This would make sense since they all the connections are coming from the same website. But if this is true, then why can't we connect using 'mysqladmin -uroot'? Shouldn't that work from any host? Thanks, Michael On Sep 1, 2004, at 9:08 AM, Victor Pendleton wrote: Is it a too many connections or Host blocked because of many connection erros? If it is the later you have reached max_connect_errors and need to issue flush-hosts. -Original Message- From: Michael Winston To: [EMAIL PROTECTED] Sent: 9/1/04 11:02 AM Subject: bad too many connections error (os x) Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
This would make sense since they all the connections are coming from the same website. But if this is true, then why can't we connect using 'mysqladmin -uroot'? Shouldn't that work from any host? Thanks, Michael On Sep 1, 2004, at 9:08 AM, Victor Pendleton wrote: Is it a too many connections or Host blocked because of many connection erros? If it is the later you have reached max_connect_errors and need to issue flush-hosts. -Original Message- From: Michael Winston To: [EMAIL PROTECTED] Sent: 9/1/04 11:02 AM Subject: bad too many connections error (os x) Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
This also may caused by the TTL of the http socket and the launched zombie forks by the http/php server ( the FIN_WAIT_2 problem!!). Thats what it happend to me . I dunno who waited for who, and became zombie, was it MySQL waiting for timeout or was socket wating to timeout. You can test this easily. Simultaneously issue same instance of the service in question from same network and watch the netstat and top -t. -- Aftab Jahan Subedar CEO/Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 North Jatrabari Dhaka 1204 Bangladesh tel://+88027519050 EMail://[EMAIL PROTECTED] - Directly to my notebook Alex Greg wrote: Michael Winston wrote: On Sep 1, 2004, at 9:10 AM, V. M. Brasseur wrote: Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael We've run into this problem ourselves, also using 4.0 but on a 64bit AIX. The problem we found was that some queries were firing off threads which never ended. These threads blocked other threads, which blocked other threads... A logjam resulted with all connections ended up being used by the offending threads. The fix was to *ahem* fix our queries so they'd close their database connections once they were complete. You may wish to do a code inspection and verify that every open connection has a matching close. Hmmm. All of our connections are coming from php-generated web pages. PHP automatically closes the connection at the end of the script. Unless I completely misunderstand how this stuff works. Plus, this problem only happens once every few weeks. If some of our queries are causing this, I would expect the problem to occur more often. I'll look into this, though. Thanks, Michael You'll also find this problem if you have some badly-optimised queries, or writes that take a long time to run on a frequently-accessed table. For example, if you have a table that frequently accessed and run a slow update on it, any thread trying to read from that table will block. If you get more selects happening to that table coming in while it's still locked, your number of connections in use will shoot upwards rapidly until the slow update finishes and the table is unlocked. Have a look in your slow query log (or turn it on if it's not enabled) to look for any queries like this. Regards, -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to admin a server that currently has too many connections?
I am creating an index on a very large innodb table that is taking a very long time to complete which I understand is a limitation of innodb. The problem is that another application has been issuing queries against the table and those queries have never timed out. So now I can't kill the connections, because I can't make a database connection to mysql to even see their thread ids: [EMAIL PROTECTED] mysql]$ mysqladmin -u root -p processlist Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Too many connections' I don't want to kill the mysqld process, because that would stop my index creation. Is there anything you can do in this situation? I don't think connections from mysqladmin should ever be denied. Is this a feature request that I should make? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to admin a server that currently has too many connections ?
Is there already a mysql privileged account logged in? The database will allow for an administrative account to login, if one is not already active, in the event of too many connections. It sounds as if you may be running one or more accounts with this privilege. -Original Message- From: Justin Swanhart To: [EMAIL PROTECTED] Sent: 8/31/04 11:43 AM Subject: How to admin a server that currently has too many connections? I am creating an index on a very large innodb table that is taking a very long time to complete which I understand is a limitation of innodb. The problem is that another application has been issuing queries against the table and those queries have never timed out. So now I can't kill the connections, because I can't make a database connection to mysql to even see their thread ids: [EMAIL PROTECTED] mysql]$ mysqladmin -u root -p processlist Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Too many connections' I don't want to kill the mysqld process, because that would stop my index creation. Is there anything you can do in this situation? I don't think connections from mysqladmin should ever be denied. Is this a feature request that I should make? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Too Many Connections
Dear Mark, The best way to fix this is by correctly setting your from address in your mailer to [EMAIL PROTECTED] Thanks, Mike -Original Message- From: Michael McTernan Sent: 08 April 2004 10:33 To: [EMAIL PROTECTED] Subject: Too Many Connections What is the best way to diagnose the root cause of this error? What scripts are doing the connecting and totalling them up? Warning: mysql_connect(): User ultimated has already more than 'max_user_connections' active connections I have a very active phpBB but I'm on a new server and its not pulling a server loading over 0.5. I ran some data before (crontab php script gathered the info for me every 5 minutes for several weeks) and the problem happened before related to server loading..not necessarily how many users I had on that site posting. That was an older Cobalt RaQ4. I seemed to be having a lot of search bots accessing the site then. [mysqld] set-variable = max_connections=512 set-variable = max_user_connections=200 set-variable = key_buffer=64M set-variable = table_cache=256 set-variable = sort_buffer=4M set-variable = wait_timeout=300 I've only had this problem this week, its run 3 weeks fine. I do have a corrupted MYI file according to myisamck. Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Too Many Connections
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 18 June 2004 12:52 pm, Michael McTernan wrote: Dear Mark, The best way to fix this is by correctly setting your from address in your mailer to [EMAIL PROTECTED] Your email software seems to be wrong. All these people can't be doing something wrong. I think you need to take this off list as well. - -- You go Uruguay, I'll go mine. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFA06Ehld4MRA3gEwYRAqrzAJ9q7BmXrcPJmq5a84LOcr4qi293vgCfZqa+ nC0Ck3uV8agimCIqWlL6JMI= =2qNu -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too Many Connections
What is the best way to diagnose the root cause of this error? What scripts are doing the connecting and totalling them up? Warning: mysql_connect(): User ultimated has already more than 'max_user_connections' active connections I have a very active phpBB but I'm on a new server and its not pulling a server loading over 0.5. I ran some data before (crontab php script gathered the info for me every 5 minutes for several weeks) and the problem happened before related to server loading..not necessarily how many users I had on that site posting. That was an older Cobalt RaQ4. I seemed to be having a lot of search bots accessing the site then. [mysqld] set-variable = max_connections=512 set-variable = max_user_connections=200 set-variable = key_buffer=64M set-variable = table_cache=256 set-variable = sort_buffer=4M set-variable = wait_timeout=300 I've only had this problem this week, its run 3 weeks fine. I do have a corrupted MYI file according to myisamck. Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Too Many Connections
Run mysqladmin extended-status Look for something like this: | Max_used_connections | 138| If it says, 512 is your max connections that you have used, then you need to raise it. If your number is much lower and you are getting that problem, it's a different problem, but that's just what mysql is reporting. Donny -Original Message- From: Mark Susol | Ultimate Creative Media [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 10:35 PM To: [EMAIL PROTECTED] Subject: Too Many Connections What is the best way to diagnose the root cause of this error? What scripts are doing the connecting and totalling them up? Warning: mysql_connect(): User ultimated has already more than 'max_user_connections' active connections I have a very active phpBB but I'm on a new server and its not pulling a server loading over 0.5. I ran some data before (crontab php script gathered the info for me every 5 minutes for several weeks) and the problem happened before related to server loading..not necessarily how many users I had on that site posting. That was an older Cobalt RaQ4. I seemed to be having a lot of search bots accessing the site then. [mysqld] set-variable = max_connections=512 set-variable = max_user_connections=200 set-variable = key_buffer=64M set-variable = table_cache=256 set-variable = sort_buffer=4M set-variable = wait_timeout=300 I've only had this problem this week, its run 3 weeks fine. I do have a corrupted MYI file according to myisamck. Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Too many connections
Randy Thanks but I am not really sure where the connections are kept alive or 'asleep' I am quite sure that I am closing my objects, setting them to nothing and disposing them in my script (actually, ASP.NET is not relly script) but I don't know if IIS is keeping the connection objects in the connection pool, if the .NET garbage collector needs to dispose them or if MySQL keeps them in sleep mode. I didn't have this problem when both services were running on the same machine, only after I separated them. In the MyINI file you can alse set the timeout for sleep connections, by default it is 28800 seconds. I tried to redûce it to 5 seconds, and BOOM - it smashed my MySQL service I am really very confused about where the cause of the problem is. Finding this out would maybe help finding a solution Any thoughts or experiences, anybody ? Freddie -Ursprüngliche Nachricht- Von: Randy Clamons [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 3. März 2004 22:05 An: Freddie Sorensen; [EMAIL PROTECTED] Betreff: Re: Too many connections Freddie, Try tuning startup parameters in my.ini or my.cnf. The default value for max_connections is 100. That's a lot for a web site with a single server. If max_connections is set in my.ini (or my.cnf) to a low number, try a larger value. If max_connections is not set, your connections are staying open. Make sure your scripts are closing your connections. If none of this works for you, try tuning the memory parameters to get better performance from mysqld. Randy Clamons Astro-auction.com At 09:15 PM 03/03/2004 +0100, Freddie Sorensen wrote: Hi, I am using MySQL 4.1.1 as backend for an ASP.NET webapplication using the provider from ByteFX. Until now, IIS and MySQL were running on the same machine and now I have moved the webserver to another machine. Now I start getting 'Too many connections' errors from the MySQL server. This did not happen before when I was connecting to localhost Anybody knows why this starts happening now and what I can do to solve it ? Thanks in advance Freddie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: Too many connections
Freddie Sorensen wrote: Randy Thanks but I am not really sure where the connections are kept alive or 'asleep' I am quite sure that I am closing my objects, setting them to nothing and disposing them in my script (actually, ASP.NET is not relly script) but I don't know if IIS is keeping the connection objects in the connection pool, if the .NET garbage collector needs to dispose them or if MySQL keeps them in sleep mode. I didn't have this problem when both services were running on the same machine, only after I separated them. In the MyINI file you can alse set the timeout for sleep connections, by default it is 28800 seconds. I tried to redûce it to 5 seconds, and BOOM - it smashed my MySQL service I am really very confused about where the cause of the problem is. Finding this out would maybe help finding a solution Any thoughts or experiences, anybody ? Freddie -Ursprüngliche Nachricht- Von: Randy Clamons [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 3. März 2004 22:05 An: Freddie Sorensen; [EMAIL PROTECTED] Betreff: Re: Too many connections Freddie, Try tuning startup parameters in my.ini or my.cnf. The default value for max_connections is 100. That's a lot for a web site with a single server. If max_connections is set in my.ini (or my.cnf) to a low number, try a larger value. If max_connections is not set, your connections are staying open. Make sure your scripts are closing your connections. If none of this works for you, try tuning the memory parameters to get better performance from mysqld. Randy Clamons Astro-auction.com At 09:15 PM 03/03/2004 +0100, Freddie Sorensen wrote: Hi, I am using MySQL 4.1.1 as backend for an ASP.NET webapplication using the provider from ByteFX. Until now, IIS and MySQL were running on the same machine and now I have moved the webserver to another machine. Now I start getting 'Too many connections' errors from the MySQL server. This did not happen before when I was connecting to localhost Anybody knows why this starts happening now and what I can do to solve it ? Is there an option to disable persistent connections and avoid pooling altogether? If yes, turn it on. Otherwise, set wait_timeout to some compromise value, eg. 100. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help! Too many connections error
I started getting an error: #1049: Too many connections Around 10 hours ago, and I don't know what it means. My admin is sort of a go between with the host provider, so I need to give him as much information as possible to get the problem fixed. I haven't made any changes to the site, although I was testing some optimized SQL statements using LEFT JOIN with phpMyAdmin prior to the error occurring. Thanks for any help you might provide. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [Mysql-discussions] Help! Too many connections error
probably you had over 100 connections to you SQL db, on tcp level. the default max is 100 connections, perhaps you can increase it a little --max-connections=200 for example (in your startup config) hope it helps you. cheers -- Kind regards, Remko Lodder Elvandar.org/DSINet.org www.mostly-harmless.nl Dutch community for helping newcomers on the hackerscene -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Mark Verzonden: zondag 8 februari 2004 19:12 Aan: MySQL List Onderwerp: [Mysql-discussions] Help! Too many connections error I started getting an error: #1049: Too many connections Around 10 hours ago, and I don't know what it means. My admin is sort of a go between with the host provider, so I need to give him as much information as possible to get the problem fixed. I haven't made any changes to the site, although I was testing some optimized SQL statements using LEFT JOIN with phpMyAdmin prior to the error occurring. Thanks for any help you might provide. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ Mysql-discussions mailing list [EMAIL PROTECTED] http://lists.elvandar.org/mailman/listinfo/mysql-discussions -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many connections
I'm using mysql 3.23.55 and Linux Mandrake 9.1. I have about 60 users connecting to mysql using our accounting system. The users get some times a Too many connections error. I have set max_connections at 200, and the system opens only one connection per user at start up. Can somebody indicate where to look to calculate this value and eliminate this error? Thank you. -- Alfredo J. Cole http://www.acyc.com http://www.clshonduras.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[PRB] REPAIR TABLE causes Too many connections on big table
Hello, I have a broken table (all the INSERTs cause error 'Duplicate entry for key 1: number'). When I run 'REPAIR TABLE tablename' it repairs table fine, but after the short period of thme since the repair process has been run, other mysql cliens start receiving the error Too many connections. It stops when 'repair table' is done. The type of table if MyISAM, it has ~2 000 000 entries (int, varchar(255), int) When I look onto ps -A | grep mysql output when repair table is run, I can see a lot of mysqld processes started Is this a normal behavior? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1040 error, too many connections?
I'm running a series of queries from a Python program using the MySQLdb module. The program opens a connection and then procedes to make two queries and then close the connection. It does this for each file provided as an argument to the program so there can be quite a few such cycles. I'm getting an 1040 error of 'Too many connections'. I can't figure out how this program would ever have more than one connection open at a time. Could anyone explain what I'm doing wrong? I've tried forcing the connection closed at the end of each cycle but that doesn't seem to help. Thanks. -- Peace, Love, Linux Michael [EMAIL PROTECTED] http://kavlon.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1040 error, too many connections?
What happens if you keep the original connection open and ALWAYS query with that connection -M - Original Message - From: Michael [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, July 27, 2003 6:41 AM Subject: 1040 error, too many connections? I'm running a series of queries from a Python program using the MySQLdb module. The program opens a connection and then procedes to make two queries and then close the connection. It does this for each file provided as an argument to the program so there can be quite a few such cycles. I'm getting an 1040 error of 'Too many connections'. I can't figure out how this program would ever have more than one connection open at a time. Could anyone explain what I'm doing wrong? I've tried forcing the connection closed at the end of each cycle but that doesn't seem to help. Thanks. -- Peace, Love, Linux Michael [EMAIL PROTECTED] http://kavlon.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1040 error, too many connections?
Hi Michael, How many client connections does mysql SHOW PROCESSLIST show you ? Best regards Nils Valentin Tokyo/Japan 2003 7 27 22:41Michael : I'm running a series of queries from a Python program using the MySQLdb module. The program opens a connection and then procedes to make two queries and then close the connection. It does this for each file provided as an argument to the program so there can be quite a few such cycles. I'm getting an 1040 error of 'Too many connections'. I can't figure out how this program would ever have more than one connection open at a time. Could anyone explain what I'm doing wrong? I've tried forcing the connection closed at the end of each cycle but that doesn't seem to help. Thanks. -- Peace, Love, Linux Michael [EMAIL PROTECTED] http://kavlon.org -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many connections for MySQL proc that cannot be killed
Hi all, I have been having a problem with MySQL lately on my machine - I apologize for the long post. Specs are: RedHat Linux 7.3 MySQL 3.23.56, for pc-linux (i686) (same behavior with 4.0.12 as well) Apache 1.3.27 PHP 4.3.1 (most (99%) of the MySQL connections are made through PHP scripts running as an apache module) The problem happens as such: MySQL will be running fine for a day or so, then any connections attempted to MySQL will start to 'hang' - no error messages or connection refused just sitting there trying to connect. After around 10 minutes MySQL will start to return an error message that there are too many connections and the connection has been refused. I assume this is because of all the 'hung' connections that were trying to connect before and were not able to finish. A look at 'mysqladmin processlist' shows a large number of processes (the number of connections set in the my.cnf file) in various states, opening tables, querying, etc. At this point, a look in 'top' will show a single MySQL process spinning at 99.9% of CPU. Any attempts to restart the MySQL server will result in an error such as: 030529 09:13:36 mysqld started 030529 9:13:36 Can't start server: Bind on TCP/IP port: Address already in use 030529 9:13:36 Do you already have another mysqld server running on port: 3306 ? 030529 9:13:36 Aborting There are no other errors listed in the error file for before or during the crash/incident. At this point, a 'kill -9 pid' on the pid of the MySQL process spinning in top has no effect and does not kill the process. I also tried to send it every other signal I could think of with no effect. In order to get MySQL started again I have to do a 'ps -l' to get the parents of the process spinning at 99.9% and kill all of the parents of the processes (except init of course). At this point I am able to restart MySQL and it functions correctly; however, the original MySQL process is still spinning at 99.9% CPU in top and the only way I have found to get rid of this is a server reboot. The last time this happened the process sitting in top was pid 8648. I had the general MySQL log running; however, the only instance of pid 8648 was: 8648 Connect [EMAIL PROTECTED] on 8648 Init DB database 8648 Query SELECT * FROM ad_info WHERE ad_id = 67 8648 Quit This was 3 hours before MySQL got messed up, and I ran the select after MySQL was back online without incident, it is just a simply selection of one row out of a table. This query was not logged to the slow log, which has a long-query-time of 1. Interestingly enough, the process id that MySQL was on and logging at the time of the incident was 14314, way beyond 8648. At this point I'm lost on what to do, any help would be appreciated. -Steven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Too many connections for MySQL proc that cannot be killed
---Original Message- --From: Steven [mailto:[EMAIL PROTECTED] --RedHat Linux 7.3 --MySQL 3.23.56, for pc-linux (i686) (same behavior with 4.0.12 as well) --Apache 1.3.27 --PHP 4.3.1 -- --(most (99%) of the MySQL connections are made through PHP scripts --running as an apache module) -- --The problem happens as such: MySQL will be running fine for a day or so, --then any connections attempted to MySQL will start to 'hang' - no error --messages or connection refused just sitting there trying to connect. --After around 10 minutes MySQL will start to return an error message that --there are too many connections and the connection has been refused. Mysql might be doing a table scan which requires a huge calculation. Activate a slow_query_log log all queries that take more then a second to track it down. -- --At this point, a look in 'top' will show a single MySQL process spinning --at 99.9% of CPU. Any attempts to restart the MySQL server will result in --an error such as: In Linux threads show up as processes this is a mysql thread spinning most likely on a table scan. Execute a show full process list once you get a connection to see for yourself that all your connections are waiting on a table that is locked due to a table scan or some other action that has locked a crucial table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many connections high processor utilization
Good Day, I have Linux 7.3 server with mysql and apache 1.3 with php 4.1 with the first web site using php and mysql I install on the server, I found the processor utilization too high processor idle time is 0% and mysql is about 85%, I don't know why. Also the site gives too many connections error from mysql and php, while the programmer says there is only 10 connections (users browsing the site) It is not a complex site, just a db for movies in the cinema and photos with few articles for each film so I do not know how to troubleshoot this problem. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Another 'Too many connections' question...
A couple of weeks ago I came across this error which effectively closed down our site. Two things: (1) I am using Perl DBI to interface mySQL is there any way to effectively capture such errors and provide an alternate web page? I guess there has to be, but I experimented with the 'PrintError' and 'RaiseError' with little joy. (2) At present our MySQL needs are provided by our ISP by means of a shared server (dedicated to MySQL I think!). The 'max_connections' variable (SHOW VARIABLES) is currently set to 300, which seems a bit on low side for a server with 770 databases (SHOW DATABASES) with I don't know how many tables. What causes this error, 'any' connection, admin type connections, etc.? And does this error require a server/mysqld restart on the part of our ISP? They were typically unhelpful when I spoke to them (hope that's not due to ignorance.) Have been looking at dedicated hosting for a while now, but finding the cash and moreover the time to move is proving a slow process. Any help gratefully Rx'd, Tom. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Another 'Too many connections' question...
Too many connections Is the message you get when all are used up. The limit is about 1000 (Linux) but can be set higher. With MySQL you can set a limit for each user. So I would set this to say 100 for each user and 1000 in total. What courses this is pconnects or threads that will not drop. If you use pconnects and don't re use them client side these can had around till they are dropped (8 hours standard). The other thing that can happen is when a table is locked by backups or a log question other connections can get backed up. Hope this helps Simon -Original Message- From: Tom Norwood [mailto:[EMAIL PROTECTED]] Sent: 21 August 2002 10:55 To: mysql-list Subject: Another 'Too many connections' question... A couple of weeks ago I came across this error which effectively closed down our site. Two things: (1) I am using Perl DBI to interface mySQL is there any way to effectively capture such errors and provide an alternate web page? I guess there has to be, but I experimented with the 'PrintError' and 'RaiseError' with little joy. (2) At present our MySQL needs are provided by our ISP by means of a shared server (dedicated to MySQL I think!). The 'max_connections' variable (SHOW VARIABLES) is currently set to 300, which seems a bit on low side for a server with 770 databases (SHOW DATABASES) with I don't know how many tables. What causes this error, 'any' connection, admin type connections, etc.? And does this error require a server/mysqld restart on the part of our ISP? They were typically unhelpful when I spoke to them (hope that's not due to ignorance.) Have been looking at dedicated hosting for a while now, but finding the cash and moreover the time to move is proving a slow process. Any help gratefully Rx'd, Tom. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Too many connections
Dear Sir, We are facing a problem related to mysql connections. The error saying that:- TOO MANY CONECTONS OPEN: Mysql server max_connections variable is currently set to 100, please tell me how i can change the max_connections value. Thank you, Mansoor Alam. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Too many connections
Mansoor, Tuesday, August 20, 2002, 4:55:28 PM, you wrote: MA We are facing a problem related to mysql connections. MA The error saying that:- MA TOO MANY CONECTONS OPEN: MA Mysql server max_connections variable is currently set to 100, MA please tell me how i can change the max_connections value. You can specify value in the my.cnf file [mysqld] set-variable=max_connections=# or run mysqld with -O max_connections=# -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Too many connections
Hello, Edit your my.cnf and add the following line in the [mysqld] section: set-variable = max_connections=500 or set it to anything you need. Regards, Iikka ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** On Tue, 20 Aug 2002, Mansoor Alam wrote: Dear Sir, We are facing a problem related to mysql connections. The error saying that:- TOO MANY CONECTONS OPEN: Mysql server max_connections variable is currently set to 100, please tell me how i can change the max_connections value. Thank you, Mansoor Alam. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Too Many Connections
MySQL keeps locking up (I get a Too many connections error.) Is there a way that I can increase the number of connections that MySQL will take? SQL and Query David McInnis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Too Many Connections
Hi, Change the max_connections variable's value to a higher value. By default it is set to 100. You can see what yours is set to with SHOW VARIABLES command. See: http://www.mysql.com/doc/T/o/Too_many_connections.html http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html Gurhan -Original Message- From: David McInnis [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 12:49 PM To: [EMAIL PROTECTED] Subject: Too Many Connections MySQL keeps locking up (I get a Too many connections error.) Is there a way that I can increase the number of connections that MySQL will take? SQL and Query David McInnis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Too Many Connections
On Mon, 06 May 2002 12:56:52 -0400 Gurhan Ozen [EMAIL PROTECTED] wrote: Hi, Change the max_connections variable's value to a higher value. By default it is set to 100. You can see what yours is set to with SHOW VARIABLES command. See: http://www.mysql.com/doc/T/o/Too_many_connections.html http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html but you also have to check your Operating System configuration ... usually they have 1024 max connection to same port / socket. -- Let's call it an accidental feature. -- Larry Wall - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Too many connections
Hello, i have error : Too many connections . when i try to backup mydatabase , even if i want browse board how to fix this and is there limit for mysql connections? Thanks _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Too many connections
Eqab, Tuesday, April 16, 2002, 4:24:56 PM, you wrote: EA i have error : Too many connections . when i try to backup mydatabase , even EA if i want browse board how to fix this and is there limit for mysql EA connections? Yes, there is a limit that is determinated by max_connections variable. By default max_connections=100, but if you want, you can increase this value. Take a look at: http://www.mysql.com/doc/T/o/Too_many_connections.html EA Thanks -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Too Many Connections error
Mysql in a shared environment is prone to punish all clients with too many connections errors if one client is hyperactive. There seems to be no way to deal with this within the standard framework. This could be dealt with by adding max_connections_per_ip and max_connections_per_user to protect against deliberate or accidental denial of service by slurping all the available connections. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Too Many Connections error
I agree with you, sometimes my server experiences some DoS and because MySQL cannot handle all that connections, the load goes really high and we can't do anything :( - Original Message - From: Dave Dyer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 16, 2002 11:17 PM Subject: Too Many Connections error Mysql in a shared environment is prone to punish all clients with too many connections errors if one client is hyperactive. There seems to be no way to deal with this within the standard framework. This could be dealt with by adding max_connections_per_ip and max_connections_per_user to protect against deliberate or accidental denial of service by slurping all the available connections. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Too Many Connections error
Won't this handle half of your issues? http://www.mysql.com/doc/S/e/Security.html If you want to restrict the number of connections for a single user, you can do this by setting the max_user_connections variable in mysqld. nickg -Original Message- From: Dave Dyer [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 16, 2002 4:18 PM To: [EMAIL PROTECTED] Subject: Too Many Connections error Mysql in a shared environment is prone to punish all clients with too many connections errors if one client is hyperactive. There seems to be no way to deal with this within the standard framework. This could be dealt with by adding max_connections_per_ip and max_connections_per_user to protect against deliberate or accidental denial of service by slurping all the available connections. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Too many connections (again) (could Mark answer this please)
I've answered this twice, both in personal e-mails, but I'll answer it
again, one last time :).
Three things, First, you're using an old version of the driver. Please
upgrade to the latest (you should do this when you suspect it might be the
driver. Always check http://mmmysql.sourceforge.net/ as this is
_the_only_official_ MM.MySQL download site. I can't vouch for anything you
download from somewhere else).
Second, you are not closing your connections in finally{} blocks, so you can
not guarantee that they are being closed! Many browsers terminate the
connection to the servlet/JSP early (IE for example), which can cause code
you think should be executing not to execute. You should _always_ get rid of
expensive resources in a finally{} block to make sure that it actually
happens.
Third, please subscribe to the [EMAIL PROTECTED] This forum is not the
correct place to ask these types of questions, and I only get it in digest
mode, so it takes me a while to read/get back to people who ask JDBC
questions in the mysql list.
-Mark
- Original Message -
Message-ID: 000701c17707$7d6a7610$7300a8c0@yilmaz
From: yilmaz [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: Too many connections (again) (could Mark answer this please)
Date: Tue, 27 Nov 2001 13:50:59 +0800
MIME-Version: 1.0
Content-Type: text/plain;
charset=iso-8859-1
Content-Transfer-Encoding: 7bit
Me , too, have the same problem.
it seems that every opened page establishes a connection but
those connections can't be closed, although i explicitly close in my code
I posted a message related with this problem a few days ago,
unfortunately couldn't get a satisfying answer. So, i request from Mark
Matthew
to help us with this problem , since he is the author of Mysql.
(my Mysql version is 3.23 , i use jdbc through tomcat 4 b7, on win 2000.)
Thanks in advance
cheers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Too many connections (again) (could Mark answer this please)
thanks a lot Mark,
now i figured it out, i should have used finally {} statements around
close() functions.
Best regards :)
- Original Message -
From: Mark Matthews [EMAIL PROTECTED]
To: yilmaz [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, November 27, 2001 10:07 PM
Subject: Re: Too many connections (again) (could Mark answer this please)
I've answered this twice, both in personal e-mails, but I'll answer it
again, one last time :).
Three things, First, you're using an old version of the driver. Please
upgrade to the latest (you should do this when you suspect it might be the
driver. Always check http://mmmysql.sourceforge.net/ as this is
_the_only_official_ MM.MySQL download site. I can't vouch for anything you
download from somewhere else).
Second, you are not closing your connections in finally{} blocks, so you
can
not guarantee that they are being closed! Many browsers terminate the
connection to the servlet/JSP early (IE for example), which can cause code
you think should be executing not to execute. You should _always_ get rid
of
expensive resources in a finally{} block to make sure that it actually
happens.
Third, please subscribe to the [EMAIL PROTECTED] This forum is not the
correct place to ask these types of questions, and I only get it in digest
mode, so it takes me a while to read/get back to people who ask JDBC
questions in the mysql list.
-Mark
- Original Message -
Message-ID: 000701c17707$7d6a7610$7300a8c0@yilmaz
From: yilmaz [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: Too many connections (again) (could Mark answer this please)
Date: Tue, 27 Nov 2001 13:50:59 +0800
MIME-Version: 1.0
Content-Type: text/plain;
charset=iso-8859-1
Content-Transfer-Encoding: 7bit
Me , too, have the same problem.
it seems that every opened page establishes a connection but
those connections can't be closed, although i explicitly close in my code
I posted a message related with this problem a few days ago,
unfortunately couldn't get a satisfying answer. So, i request from Mark
Matthew
to help us with this problem , since he is the author of Mysql.
(my Mysql version is 3.23 , i use jdbc through tomcat 4 b7, on win 2000.)
Thanks in advance
cheers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Too many connections (again)
I have a lot of databased websites running on my server (1 Ghz P3/256 MB/20GB/RH7.1) and I am continually getting connection problems. None of the sites are high volume, so I am surprised by this and I suspect that connections are not being reused quickly enough. Here's my config, can anyone help me sort this out? [root@server1 /root]# vi /etc/my.cnf [mysqld] #datadir=/var/lib/mysql datadir=/usr/mysql #socket=/usr/mysql/mysql.sock socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib max_connections=300 [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Chris Mason [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Too many connections (again)
I keep getting that error too. But I couldn't figure out the cause. So I just used Apache::DBI for persistent DB connection ( you can't do that unless your scripts are running under mod_perl ) -- sherzodR On Mon, 26 Nov 2001, Chris Mason wrote: I have a lot of databased websites running on my server (1 Ghz P3/256 MB/20GB/RH7.1) and I am continually getting connection problems. None of the sites are high volume, so I am surprised by this and I suspect that connections are not being reused quickly enough. Here's my config, can anyone help me sort this out? [root@server1 /root]# vi /etc/my.cnf [mysqld] #datadir=/var/lib/mysql datadir=/usr/mysql #socket=/usr/mysql/mysql.sock socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib max_connections=300 [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Chris Mason [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Too many connections (again) (could Mark answer this please)
Me , too, have the same problem. it seems that every opened page establishes a connection but those connections can't be closed, although i explicitly close in my code I posted a message related with this problem a few days ago, unfortunately couldn't get a satisfying answer. So, i request from Mark Matthew to help us with this problem , since he is the author of Mysql. (my Mysql version is 3.23 , i use jdbc through tomcat 4 b7, on win 2000.) Thanks in advance cheers - Original Message - From: sherzodR [EMAIL PROTECTED] To: Chris Mason [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Tuesday, November 27, 2001 1:19 PM Subject: Re: Too many connections (again) I keep getting that error too. But I couldn't figure out the cause. So I just used Apache::DBI for persistent DB connection ( you can't do that unless your scripts are running under mod_perl ) -- sherzodR On Mon, 26 Nov 2001, Chris Mason wrote: I have a lot of databased websites running on my server (1 Ghz P3/256 MB/20GB/RH7.1) and I am continually getting connection problems. None of the sites are high volume, so I am surprised by this and I suspect that connections are not being reused quickly enough. Here's my config, can anyone help me sort this out? [root@server1 /root]# vi /etc/my.cnf [mysqld] #datadir=/var/lib/mysql datadir=/usr/mysql #socket=/usr/mysql/mysql.sock socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib max_connections=300 [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Chris Mason [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
'too many connections'
Hi, My website database allows 300 connections, but every once and a while the database stops accepting new connections saying that there is too many. PHP *should* automatically close connections when my scripts end, but perhaps its not doing that. Anyhow, is there a way to have my connections timeout faster, when they're inactive for a short period of time, say 10 seconds or so? Show processlist doesn't show any activity so all 300 of the connections are doing nothing. Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 'too many connections'
Hi, My website database allows 300 connections, but every once and a while the database stops accepting new connections saying that there is too many. PHP *should* automatically close connections when my scripts end, but perhaps its not doing that. Anyhow, is there a way to have my connections timeout faster, when they're inactive for a short period of time, say 10 seconds or so? Show processlist doesn't show any activity so all 300 of the connections are doing nothing. This can be due to your apache configuration. do you use mysql_pconnect function in php. Check you apache setting agains MaxClients StartServers and etc... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 'too many connections'
No, I'm not using mysql_pconnect, and my apache configuration seems fine. I don't have very many users at the moment so there is no reason for the connections filling up. This can be due to your apache configuration. do you use mysql_pconnect function in php. Check you apache setting agains MaxClients StartServers and etc... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 'too many connections'
Christopher Book wrote: No, I'm not using mysql_pconnect, and my apache configuration seems fine. I don't have very many users at the moment so there is no reason for the connections filling up. This can be due to your apache configuration. do you use mysql_pconnect function in php. Check you apache setting agains MaxClients StartServers and etc... Try adding something like set-variable=wait_timeout=1800 to your my.cnf. We found that when using mysql_pconnect the connections stuck around for far to long. MySQL's default is pretty high for a webserver that gets a few hits. After adding wait_timeout=1800 we have never seen 'too many connections' again. We still use mysql_pconnect. Regards, Arne -- Arne K. Haaje | T: 69 92 04 90 Enebakkveien 2 | F: 69 92 04 91 1625 Tomter | M: 92 88 44 66 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 'too many connections'
You mentioned this solved the problem when using pconnect. I'm using connect, not pconnect so will this still work? Will using pconnect instead of connect do anything for me? Basically everything runs properly but then every once and a while I get a ton of processes that get stuck and send the whole thing to hell... ie many fulltext searches that just die along with other queriest. I'd be happy if I could just set it up so that any connection taking longer than a certain period of time would be killed regardless of if its working on a query or something. Chris -Original Message- From: Arne K. Haaje [mailto:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 4:29 PM To: Christopher Book Cc: [EMAIL PROTECTED] Subject: Re: 'too many connections' Christopher Book wrote: No, I'm not using mysql_pconnect, and my apache configuration seems fine. I don't have very many users at the moment so there is no reason for the connections filling up. This can be due to your apache configuration. do you use mysql_pconnect function in php. Check you apache setting agains MaxClients StartServers and etc... Try adding something like set-variable=wait_timeout=1800 to your my.cnf. We found that when using mysql_pconnect the connections stuck around for far to long. MySQL's default is pretty high for a webserver that gets a few hits. After adding wait_timeout=1800 we have never seen 'too many connections' again. We still use mysql_pconnect. Regards, Arne -- Arne K. Haaje | T: 69 92 04 90 Enebakkveien 2 | F: 69 92 04 91 1625 Tomter | M: 92 88 44 66 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL said too many connections
hello. My server uses SunOS dns2 5.7 Generic_106541-02 sun4u sparc SUNW,Ultra-250, and my web server software is APACHE 1.3.20 configured with PHP 4.0.6 which was build with the support of mysql. The mysql server has several databases that are used by several virtual hosts. I have a account having (and have only) the privileges of one of the databases on the mysql server. My site uses this database throught PHP scripts using the account I said above. But my php scripts said too many connections when calling mysql_pconnect in the past days. I know it was caused by the PHP scripts because of not closing the connections. I execute the command show processlist and it printed many rows. I repaired the bug and killed the connections. But, now, the problem still exists, and when I exeute show processlist there were only a few rows. What's the problem? How does mysql limit the connections? Does it limit all the connections to a certain quantity or limit each database seperately? When I execute show processlist, which connections does it shows? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL said too many connections
hello. My server uses SunOS dns2 5.7 Generic_106541-02 sun4u sparc SUNW,Ultra-250, and my web server software is APACHE 1.3.20 configured with PHP 4.0.6 which was build with the support of mysql. The mysql server has several databases that are used by several virtual hosts. I have a account having (and have only) the privileges of one of the databases on the mysql server. My site uses this database throught PHP scripts using the account I said above. But my php scripts said too many connections when calling mysql_pconnect in the past days. I know it was caused by the PHP scripts because of not closing the connections. I execute the command show processlist and it printed many rows. I repaired the bug and killed the connections. But, now, the problem still exists, and when I exeute show processlist there were only a few rows. What's the problem? How does mysql limit the connections? Does it limit all the connections to a certain quantity or limit each database seperately? When I execute show processlist, which connections does it shows? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Too many connections!
Ciao guys, I am using MySQL 3.23 on a Linux with Apache/PHP. Last week I first run it, and had a 'too many connections' warning. So, I just set the max_connections variable to 200. And since then, no problem. Here is the /etc/my.cnf file under the [mysqld] group: set-variable=key_buffer=16777216 set-variable=max_connections=200 set-variable=table_cache=2048 I want to ask you if there's a way to keep this under control. I mean. What should I do to do this? I use to give a look at the status and extended-status with mysqladmin, but are there any attributes I should keep an eye on? And, why does max_used_connections always says: 100? Kinda weird, isn't it? Ciao and thanks! -Gabriele Ciao, Ciao -Gabriele - Gabriele Bartolini - Computer Programmer U.O. Rete Civica - Comune di Prato Prato - Italia - Europa e-mail: [EMAIL PROTECTED] http://www.po-net.prato.it - A Supernova is the celestial equivalent of rm -rf /* with root permissions. - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problems with Too Many Connections
Hello. I wrote a few days ago when I was having trouble with too many connections to my server. Since then I upgraded to 3.23.36 to see if that would help... Basically I have a PHP (4.0.4pl1) application that does both SELECTS and INSERTS against mySQL and is very active. It's my understanding that PHP closes the connection after it delivers the page to the user. When the server gets into the "too many connections" state I can't even login (though the manual seems to claim I should be able to since it does max_connections+1, I still cannot) so I can't see what's in the processlist. I'm trying to discover the best way to troubleshoot this problem. I tried upping the user max_connections variable but that didn't help... It's hard to troubleshoot since I don't know what's going on. Any help would be appreciated. Hunter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Hanging w/ Too Many Connections
I have mySQL threads that are sleeping with LONG times from a PHP app. What kind of PHP call could sleep that long w/o dying? Hunter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Too Many Connections error
I'm having a problem, which has occured before, in which I keep getting "Too Many Connections" Errors in my logs on the web server for MySQL. I am using Apache::DBI to connect. I fixed the problem before by just increasing the max_connections. Unfortunately, I am at the max amount of max_connections (the MySQL docs say that in order to increase it, you have to compile it in with the code, which I would rather not do.), and I cannot add any more. If I switch to just regular DBI, will that solve my problem, or what else could be causing this problem to occur? I've got my boss breathing down my neck, and I'm unsure what answer to give him. -Jesse Stay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Too Many Connections error
On Wed, 18 Apr 2001, Jesse E. Stay II wrote: I'm having a problem, which has occured before, in which I keep getting "Too Many Connections" Errors in my logs on the web server for MySQL. I am using Apache::DBI to connect. I fixed the problem before by just increasing the max_connections. Unfortunately, I am at the max amount of max_connections (the MySQL docs say that in order to increase it, you have to compile it in with the code, which I would rather not do.), and I cannot add any more. If I switch to just regular DBI, will that solve my problem, or what else could be causing this problem to occur? I've got my boss breathing down my neck, and I'm unsure what answer to give him. Here's some random thoughts: If you have more Apache processes running than you have maximum connections (I think the hard maximum is around 1000), then you will run out of connections. One thing you could try is setting "MaxClients" in httpd.conf to your max_connections. This will prevent Apache from spawning too many processes, but may cause people viewing your website to have to wait longer. If you use the normal DBI instead of Apache::DBI, then connections will be non-persistent and you'll have more to go around, at the cost of slightly slower website response time. There might be a problem somewhere that is causing your system to use up more MySQL connections than it should. 1000 is a lot of connections, and it shouldn't use that many unless your website is very heavily loaded. (I had a website that got 3 million page views a month and it fit in 40 simultaneous connections, but it was all static files so queries could be served quickly.) -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Too Many Connections error
Hi everybody!! I've got a pb during the installation of the binary of mysql-3.22.32 (the same for 3.23.36 version): there is no mysql/var directory, so when i write: chown -R mysql /usr/local/mysql/var that doesn't work. And it's exactly the same for mysql/bin. So PLEASE!!! if someone can help me Thanks in advance Phelles - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Too Many Connections error
That's weird - my Server will only let me have 210 connections if I set max_connections to 300, it sets at 210. If I set it to 500, it still stays at 210. Any ideas on how to increase this? -Original Message- From: Philip Mak [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 18, 2001 3:30 PM To: Jesse E. Stay II Cc: [EMAIL PROTECTED] Subject: Re: "Too Many Connections" error On Wed, 18 Apr 2001, Jesse E. Stay II wrote: I'm having a problem, which has occured before, in which I keep getting "Too Many Connections" Errors in my logs on the web server for MySQL. I am using Apache::DBI to connect. I fixed the problem before by just increasing the max_connections. Unfortunately, I am at the max amount of max_connections (the MySQL docs say that in order to increase it, you have to compile it in with the code, which I would rather not do.), and I cannot add any more. If I switch to just regular DBI, will that solve my problem, or what else could be causing this problem to occur? I've got my boss breathing down my neck, and I'm unsure what answer to give him. Here's some random thoughts: If you have more Apache processes running than you have maximum connections (I think the hard maximum is around 1000), then you will run out of connections. One thing you could try is setting "MaxClients" in httpd.conf to your max_connections. This will prevent Apache from spawning too many processes, but may cause people viewing your website to have to wait longer. If you use the normal DBI instead of Apache::DBI, then connections will be non-persistent and you'll have more to go around, at the cost of slightly slower website response time. There might be a problem somewhere that is causing your system to use up more MySQL connections than it should. 1000 is a lot of connections, and it shouldn't use that many unless your website is very heavily loaded. (I had a website that got 3 million page views a month and it fit in 40 simultaneous connections, but it was all static files so queries could be served quickly.) -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
