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]
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]
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:
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]
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]
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]
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: 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]
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]
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]
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
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
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
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
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
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
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
Re: Too Many Connections
Since you feel that you should never have more than 15 simultaneous connections at a given time, I don't think modifying max_connections will help. I would first verify that the PHP code is closing the connection. If it isn't, then the connection will stay open until it times out. If you do, however, decide to modify some of the configurable mysqld parameters, then take a look at chapter 12 of the manual, the optimization one. It mentions some sample configurations for various levels of machines. I'd recommended scheduling a script to run every x minutes/hours that would capture what connections are established. A `mysqladmin processlist` should give you information about all open connections. Example output: 1000 $ mysqladmin processlist +--+-+++-+---+---+--+ | Id | User| Host | db | Command | Time | State | Info | +--+-+++-+---+---+--+ Good luck, Erik - 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
"Jesse E. Stay II" wrote: I seem to have run into a problem lately where all of the sudden my server keeps giving me "too many connections" errors, locking anyone out of the site. I have set max_connections to 210, which shouldn't matter anyway because we haven't had any more users than usual accessing the site. The only thing that has changed since then is the programming (using Apache::DBI in Perl to connect). Does anyone have any ideas what type of programming errors may be causing this? I do a mysqladmin processlist, and it appears that the processes seem to stay right up around 80, and don't seem to go much below that number - why is that? Any help would be extremely helpful - my boss is about ready to switch to Oracle, and I don't want to have to do that by all means. -Jesse Stay Maybe you are not closing your 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