What is unusually high for the # of connections to MySQL?
I was wondering what would be considered unusually high for the # of connections to a MySQL Server? Also, if a high number of these are in sleep mode,does it make a difference? We have a web site (a few, actually) and MySQL (Version 5.0.67-community-nt-log) running on a WS08 server, and several times now, we have basically had the web site crash on us. One tech thought that it may be the # of connections. I have seen between 100 to 125 connections or so at one time 98% of them all from the same user. This is from our asp.net web application that we're using for testing. The app basically becomes unresponsive, but I'm not 100% convinced that this is a MySQL problem. The site does not even seem to be serving up pages when it gets into this mode. Also, there are other web sites on this same server (not being used a lot at all), and these sites all seem to come up just fine. There are no connection issues with the pages or with the data in those applications. My main questio is this. Is 100 to 125 unusually high? I have implemented a connection pool into my connection string in hopes that this will resolve the problem. Here is that string: uid=usernamer;password=password;Server=127.0.0.1;port=3306;Database=mydatabase;Allow Zero Datetime=true;pooling=true; max pool size=10; min pool size=3 Someone else suggested this string, but after implementing it and re-starting the server, we still had the same problem. My plan is to move the app to a WS03 server tonight in hopes that the issue is the O/S. Can anyone fill me in? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is unusually high for the # of connections to MySQL?
It depends, but 100 is not strange at all, particularly if you have sleeping connections (usually due to slow page loading (ajax?) and/or persistent connections from the app) and any number of connections cannot crash a server, can make it slow or unusable, but not crash it. Watch the app, you could have for loops banging the database, a not optimized app can kill cause a D.O.S.(=bad) of MySQL. Anyway the point is another. I think you cant afford guessing, it will take a huge amount of effort to try to guess why it crashes. Find the more information you can enabling all the logging possible, put server parameters under graphing, the more information you have on the crash, the less you will need to guess. Watch, cpu(load, context switches), ram(usage,swapping), IO. Guess less, know more. Claudio 2010/2/26 Jesse j...@msdlg.com I was wondering what would be considered unusually high for the # of connections to a MySQL Server? Also, if a high number of these are in sleep mode,does it make a difference? We have a web site (a few, actually) and MySQL (Version 5.0.67-community-nt-log) running on a WS08 server, and several times now, we have basically had the web site crash on us. One tech thought that it may be the # of connections. I have seen between 100 to 125 connections or so at one time 98% of them all from the same user. This is from our asp.net web application that we're using for testing. The app basically becomes unresponsive, but I'm not 100% convinced that this is a MySQL problem. The site does not even seem to be serving up pages when it gets into this mode. Also, there are other web sites on this same server (not being used a lot at all), and these sites all seem to come up just fine. There are no connection issues with the pages or with the data in those applications. My main questio is this. Is 100 to 125 unusually high? I have implemented a connection pool into my connection string in hopes that this will resolve the problem. Here is that string: uid=usernamer;password=password;Server=127.0.0.1;port=3306;Database=mydatabase;Allow Zero Datetime=true;pooling=true; max pool size=10; min pool size=3 Someone else suggested this string, but after implementing it and re-starting the server, we still had the same problem. My plan is to move the app to a WS03 server tonight in hopes that the issue is the O/S. Can anyone fill me in? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
Re: What is unusually high for the # of connections to MySQL?
Thank you so much for the reply. I think we may have stepped outside of the MySQL realm now, but here is what I know: * At least a couple times, recycling the application pool started things right up, but that did not always work. * When this is going on, I cannot even get to a page itself, let alone execute a function that runs a query. * One time when this happened, we moved the entire app to an OLD WS03 server. It had only 2 GB, I believe, and it ran like champ after that. Due to circumstances beyond our control, we had to move it back to the WS08 server, and here we are again with the same problem. * I can log on to the server, no problem. I can also log on to MySQL and run queries. I would think that if the database server were the problem, I would not be able to do that. * Do do frequently get errors when this is occurring. These are asp.net errors. here are a few of those: MySql.Data.MySqlClient.MySqlException: error connecting: Timeout expired System.IndexOutOfRangeException: Could not find specified column in results Object reference not set to an instance of an object System.IO.IOException: Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host 42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''SHOW VARIABLE'' Key cannot be null The list goes on. As you can see, the errors are all over the board. Some make sense, some do not. For instance, the you have an error in your sql does not, because this same area of code works perfectly Many times throughout the day, and I or no one else has changed it. Plus, the one stating ''SHOW VARIABLE'' makes no sense at all. I have not executed such a function in my code. Thanks, Jesse - Original Message - From: Claudio Nanni To: Jesse Cc: mysql@lists.mysql.com Sent: Thursday, February 25, 2010 6:28 PM Subject: Re: What is unusually high for the # of connections to MySQL? It depends, but 100 is not strange at all, particularly if you have sleeping connections (usually due to slow page loading (ajax?) and/or persistent connections from the app) and any number of connections cannot crash a server, can make it slow or unusable, but not crash it. Watch the app, you could have for loops banging the database, a not optimized app can kill cause a D.O.S.(=bad) of MySQL. Anyway the point is another. I think you cant afford guessing, it will take a huge amount of effort to try to guess why it crashes. Find the more information you can enabling all the logging possible, put server parameters under graphing, the more information you have on the crash, the less you will need to guess. Watch, cpu(load, context switches), ram(usage,swapping), IO. Guess less, know more. Claudio 2010/2/26 Jesse j...@msdlg.com I was wondering what would be considered unusually high for the # of connections to a MySQL Server? Also, if a high number of these are in sleep mode,does it make a difference? We have a web site (a few, actually) and MySQL (Version 5.0.67-community-nt-log) running on a WS08 server, and several times now, we have basically had the web site crash on us. One tech thought that it may be the # of connections. I have seen between 100 to 125 connections or so at one time 98% of them all from the same user. This is from our asp.net web application that we're using for testing. The app basically becomes unresponsive, but I'm not 100% convinced that this is a MySQL problem. The site does not even seem to be serving up pages when it gets into this mode. Also, there are other web sites on this same server (not being used a lot at all), and these sites all seem to come up just fine. There are no connection issues with the pages or with the data in those applications. My main questio is this. Is 100 to 125 unusually high? I have implemented a connection pool into my connection string in hopes that this will resolve the problem. Here is that string: uid=usernamer;password=password;Server=127.0.0.1;port=3306;Database=mydatabase;Allow Zero Datetime=true;pooling=true; max pool size=10; min pool size=3 Someone else suggested this string, but after implementing it and re-starting the server, we still had the same problem. My plan is to move the app to a WS03 server tonight in hopes that the issue is the O/S. Can anyone fill me in? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
Re: Re: What is unusually high for the # of connections to MySQL?
Hi Jesse, as you can see the most relevant is connection timed out, you could focus on this, this problem is typical of ODBC. this can happen because you use the persistent connection pool in your DSN (ODBC). So I would start focusing on the connection time out. I could say raise the timeout time but you could always meet the problem again, you have to see if you can set the driver (odbc) to refresh the connection automatically when it expires. Also in the code if you trap the error you can refresh the connection from the code. This is just where I would start. let me know Claudio Il giorno 26/feb/2010 00.38, Jesse j...@msdlg.com ha scritto: Thank you so much for the reply. I think we may have stepped outside of the MySQL realm now, but here is what I know: * At least a couple times, recycling the application pool started things right up, but that did not always work. * When this is going on, I cannot even get to a page itself, let alone execute a function that runs a query. * One time when this happened, we moved the entire app to an OLD WS03 server. It had only 2 GB, I believe, and it ran like champ after that. Due to circumstances beyond our control, we had to move it back to the WS08 server, and here we are again with the same problem. * I can log on to the server, no problem. I can also log on to MySQL and run queries. I would think that if the database server were the problem, I would not be able to do that. * Do do frequently get errors when this is occurring. These are asp.net errors. here are a few of those: MySql.Data.MySqlClient.MySqlException: error connecting: Timeout expired System.IndexOutOfRangeException: Could not find specified column in results Object reference not set to an instance of an object System.IO.IOException: Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host 42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''SHOW VARIABLE'' Key cannot be null The list goes on. As you can see, the errors are all over the board. Some make sense, some do not. For instance, the you have an error in your sql does not, because this same area of code works perfectly Many times throughout the day, and I or no one else has changed it. Plus, the one stating ''SHOW VARIABLE'' makes no sense at all. I have not executed such a function in my code. Thanks, Jesse - Original Message - From: Claudio Nanni To: Jesse Cc: mysql@lists.mysql.com Sent: Thursday, February 25, 2010 6:28 PM Subject: Re: What is unusually high for the # of connections to MySQL? It depends, but 100 is not strange at all, particularly if you have sleeping connections (usually due to slow page loading (ajax?) and/or persistent connections from the app) and any number of connections cannot crash a server, can make it slow or unusable, but not crash it. Watch the app, you could have for loops banging the database, a not optimized app can kill cause a DOS(=bad) of MySQL. Anyway the point is another. I think you cant afford guessing, it will take a huge amount of effort to try to guess why it crashes. Find the more information you can enabling all the logging possible, put server parameters under graphing, the more information you have on the crash, the less you will need to guess. Watch, cpu(load, context switches), ram(usage,swapping), IO. Guess less, know more. Claudio 2010/2/26 Jesse j...@msdlg.com I was wondering what would be considered unusually high for the # of connections to a MySQL Server? Also, if a high number of these are in sleep mode,does it make a difference? We have a web site (a few, actually) and MySQL (Version 5.0.67-community-nt-log) running on a WS08 server, and several times now, we have basically had the web site crash on us. One tech thought that it may be the # of connections. I have seen between 100 to 125 connections or so at one time 98% of them all from the same user. This is from our asp.net web application that we're using for testing. The app basically becomes unresponsive, but I'm not 100% convinced that this is a MySQL problem. The site does not even seem to be serving up pages when it gets into this mode. Also, there are other web sites on this same server (not being used a lot at all), and these sites all seem to come up just fine. There are no connection issues with the pages or with the data in those applications. My main questio is this. Is 100 to 125 unusually high? I have implemented a connection pool into my connection string in hopes that this will resolve the problem. Here is that string: uid=usernamer;password=password;Server=127.0.0.1;port=3306;Database=mydatabase;Allow Zero Datetime=true;pooling=true; max pool size=10; min pool size=3 Someone else
Re: Re: What is unusually high for the # of connections to MySQL?
I think its more on the app side that is causing the problem. Check if the connections are getting closed on the app side after the job is done. Also u might to check on slow or general query log to know what is happening on the db side. Also check the load on the db server. We have db's running with close to 600 connections without any issues. regards anandkl On Fri, Feb 26, 2010 at 12:53 PM, claudio.na...@gmail.com wrote: Hi Jesse, as you can see the most relevant is connection timed out, you could focus on this, this problem is typical of ODBC. this can happen because you use the persistent connection pool in your DSN (ODBC). So I would start focusing on the connection time out. I could say raise the timeout time but you could always meet the problem again, you have to see if you can set the driver (odbc) to refresh the connection automatically when it expires. Also in the code if you trap the error you can refresh the connection from the code. This is just where I would start. let me know Claudio Il giorno 26/feb/2010 00.38, Jesse j...@msdlg.com ha scritto: Thank you so much for the reply. I think we may have stepped outside of the MySQL realm now, but here is what I know: * At least a couple times, recycling the application pool started things right up, but that did not always work. * When this is going on, I cannot even get to a page itself, let alone execute a function that runs a query. * One time when this happened, we moved the entire app to an OLD WS03 server. It had only 2 GB, I believe, and it ran like champ after that. Due to circumstances beyond our control, we had to move it back to the WS08 server, and here we are again with the same problem. * I can log on to the server, no problem. I can also log on to MySQL and run queries. I would think that if the database server were the problem, I would not be able to do that. * Do do frequently get errors when this is occurring. These are asp.net errors. here are a few of those: MySql.Data.MySqlClient.MySqlException: error connecting: Timeout expired System.IndexOutOfRangeException: Could not find specified column in results Object reference not set to an instance of an object System.IO.IOException: Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host 42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''SHOW VARIABLE'' Key cannot be null The list goes on. As you can see, the errors are all over the board. Some make sense, some do not. For instance, the you have an error in your sql does not, because this same area of code works perfectly Many times throughout the day, and I or no one else has changed it. Plus, the one stating ''SHOW VARIABLE'' makes no sense at all. I have not executed such a function in my code. Thanks, Jesse - Original Message - From: Claudio Nanni To: Jesse Cc: mysql@lists.mysql.com Sent: Thursday, February 25, 2010 6:28 PM Subject: Re: What is unusually high for the # of connections to MySQL? It depends, but 100 is not strange at all, particularly if you have sleeping connections (usually due to slow page loading (ajax?) and/or persistent connections from the app) and any number of connections cannot crash a server, can make it slow or unusable, but not crash it. Watch the app, you could have for loops banging the database, a not optimized app can kill cause a DOS(=bad) of MySQL. Anyway the point is another. I think you cant afford guessing, it will take a huge amount of effort to try to guess why it crashes. Find the more information you can enabling all the logging possible, put server parameters under graphing, the more information you have on the crash, the less you will need to guess. Watch, cpu(load, context switches), ram(usage,swapping), IO. Guess less, know more. Claudio 2010/2/26 Jesse j...@msdlg.com I was wondering what would be considered unusually high for the # of connections to a MySQL Server? Also, if a high number of these are in sleep mode,does it make a difference? We have a web site (a few, actually) and MySQL (Version 5.0.67-community-nt-log) running on a WS08 server, and several times now, we have basically had the web site crash on us. One tech thought that it may be the # of connections. I have seen between 100 to 125 connections or so at one time 98% of them all from the same user. This is from our asp.net web application that we're using for testing. The app basically becomes unresponsive, but I'm not 100% convinced that this is a MySQL problem. The site does not even seem to be serving up pages when it gets into this mode. Also, there are other web sites on this same server (not being used a lot at all), and these sites all
Re: Unlimited client connections for MySQL
Dwight Tovey wrote: Brent Anderson wrote: Hello. I'm developing a client application for several platforms that will need to connect to a remote MySQL database. Unfortunately, MySQL refuses connections from external IP's that aren't allowed and since the clients using this will be on unknown IP addresses (their home computers), I'm in a bit of a situation. How does one setup a MySQL account with no IP restrictions? You probably have a line in your my.cnf that restricts the server to only listen on the localhost address. Look for bind-address = 127.0.0.1 Comment that line out, restart the server, and it should accept connections from all client machines (assuming that you don't have other firewall restrictions as well). Note however that this can be a big security hole. /dwight also check for: skip-networking -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unlimited client connections for MySQL
Hello. I'm developing a client application for several platforms that will need to connect to a remote MySQL database. Unfortunately, MySQL refuses connections from external IP's that aren't allowed and since the clients using this will be on unknown IP addresses (their home computers), I'm in a bit of a situation. How does one setup a MySQL account with no IP restrictions? Thanks, Brent Anderson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unlimited client connections for MySQL
GRANT (ALL|SELECT|INSERT|UPDATE|DELETE|etc) ON DATABASE.* TO 'user'@'%' IDENTIFIED BY 'password' See http://dev.mysql.com/doc/refman/5.0/en/grant.html for details. Note that localhost is considered as a special case, not included in the wildcard % HTH, Dan On 12/13/06, Brent Anderson [EMAIL PROTECTED] wrote: Hello. I'm developing a client application for several platforms that will need to connect to a remote MySQL database. Unfortunately, MySQL refuses connections from external IP's that aren't allowed and since the clients using this will be on unknown IP addresses (their home computers), I'm in a bit of a situation. How does one setup a MySQL account with no IP restrictions? Thanks, Brent Anderson -- 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: Unlimited client connections for MySQL
Brent Anderson wrote: Hello. I'm developing a client application for several platforms that will need to connect to a remote MySQL database. Unfortunately, MySQL refuses connections from external IP's that aren't allowed and since the clients using this will be on unknown IP addresses (their home computers), I'm in a bit of a situation. How does one setup a MySQL account with no IP restrictions? You probably have a line in your my.cnf that restricts the server to only listen on the localhost address. Look for bind-address = 127.0.0.1 Comment that line out, restart the server, and it should accept connections from all client machines (assuming that you don't have other firewall restrictions as well). Note however that this can be a big security hole. /dwight -- Dwight N. Tovey [EMAIL PROTECTED] http://www.dtovey.net/~dwight/ Please Do Not send me Microsoft Word attachments. See http://www.gnu.org/philosophy/no-word-attachments.html --- Work to Live : Live to Ride : Ride to Work -- 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]
Re: tracing connections to mysql.
Hello. I don't remember any built-in capability of MySQL to provide such information. But it seems as not a difficult task to write a script which will gather it. todd hewett [EMAIL PROTECTED] wrote: Thanks Gleb, That was educational. Is there a way to log connections in such a way that is easy to tell how many connections were happening at one time? Thanks, Todd -Original Message- -- 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: tracing connections to mysql.
Hello. See: http://dev.mysql.com/doc/mysql/en/mysql-threads.html Please, next time answer to the list as well. Gleb thankyou. That was exactly what I thought I was looking for. It revealed two connections when logged in as admin through CLI: admin and the connection for the app that was having issues. Are connections the same as [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: tracing connections to mysql.
Thanks Gleb, That was educational. Is there a way to log connections in such a way that is easy to tell how many connections were happening at one time? Thanks, Todd -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 12, 2005 12:26 AM To: mysql@lists.mysql.com Subject: Re: tracing connections to mysql. Hello. See: http://dev.mysql.com/doc/mysql/en/mysql-threads.html Please, next time answer to the list as well. Gleb thankyou. That was exactly what I thought I was looking for. It revealed two connections when logged in as admin through CLI: admin and the connection for the app that was having issues. Are connections the same as [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tracing connections to mysql.
Howdy Folks, I have an app that cannot get information from mysql database for some reason. Here is an query which application is trying to run but after some timeout (something about 2-3 minutes): 11 Jul 2005 03:32:18,485 DEBUG [Thread-20] (PressReleaseDAO.java:328) - sqlselect prd.press_release_id, pr.start_date, prd.attention_title, prd.headline, prd.sub_headline, prd.summary, prd.company_name, prd.body, prd.city, prd.state, ind.industry_id, ind.industry_name from press_release_detail prd, press_release pr, industry ind where pr.active_flag = 'Y' and pr.press_release_id = prd.press_release_id and prd.industry = ind.industry_id and start_date = date_add(current_timestamp(), INTERVAL 3 HOUR) order by pr.start_date desc Then errors follows: 11 Jul 2005 03:38:24,125 ERROR [Thread-18] (PressReleaseDAO.java:357) - SQLException:java.sql.SQLException: Communication link failure: java.net.SocketException 11 Jul 2005 03:38:24,126 ERROR [Thread-18] (BaseAction.java:75) - com.flierwire.common.FlierwireSystemException: A system error has occurred, please send an email to support at flierwire.com with details of what occurred. I've tried to run this request in mysql and it has been ran fine. After that I've increased max_connections limit in /etc/my.cnf and app runs fine. Is there a way to determin what connectiions to MySQL are being used by whom? Thanks, Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tracing connections to mysql.
Hello. Is there a way to determin what connectiions to MySQL are being used by whom? SHOW PROCESSLIST could be helpful. See: http://dev.mysql.com/doc/mysql/en/show-processlist.html [EMAIL PROTECTED] wrote: Howdy Folks, I have an app that cannot get information from mysql database for some reason. Here is an query which application is trying to run but after some timeout (something about 2-3 minutes): 11 Jul 2005 03:32:18,485 DEBUG [Thread-20] (PressReleaseDAO.java:328) - sqlselect prd.press_release_id, pr.start_date, prd.attention_title, prd.headline, prd.sub_headline, prd.summary, prd.company_name, prd.body, prd.city, prd.state, ind.industry_id, ind.industry_name from press_release_detail prd, press_release pr, industry ind where pr.active_flag = 'Y' and pr.press_release_id = prd.press_release_id and prd.industry = ind.industry_id and start_date = date_add(current_timestamp(), INTERVAL 3 HOUR) order by pr.start_date desc Then errors follows: 11 Jul 2005 03:38:24,125 ERROR [Thread-18] (PressReleaseDAO.java:357) - SQLException:java.sql.SQLException: Communication link failure: java.net.SocketException 11 Jul 2005 03:38:24,126 ERROR [Thread-18] (BaseAction.java:75) - com.flierwire.common.FlierwireSystemException: A system error has occurred, please send an email to support at flierwire.com with details of what occurred. I've tried to run this request in mysql and it has been ran fine. After that I've increased max_connections limit in /etc/my.cnf and app runs fine. Is there a way to determin what connectiions to MySQL are being used by whom? Thanks, Todd -- 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: Max Connections of MySQL on Linux
Hello. Check your results with official binaries. Set max_connections variable to big enough value. Combinations of different versions of compilers and glibc sometimes could give unpredictable results. huang leo [EMAIL PROTECTED] wrote: Hi, everyone: I had done a test on Linux2.6. I got the max connections of 1079 when I complied the MySQL with static link. But I got the max connections of 7159 when I complied the MySQL with dynamic link. Why has so much difference between the static link and dynamic link? Has anybody know it? $$ Best regards, leo huang 2005-06-27 _ $$$ MSN Hotmail$ http://www.hotmail.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]
Max Connections of MySQL on Linux
Hi, everyone: I had done a test on Linux2.6. I got the max connections of 1079 when I complied the MySQL with static link. But I got the max connections of 7159 when I complied the MySQL with dynamic link. Why has so much difference between the static link and dynamic link? Has anybody know it? Best regards, leo huang 2005-06-27 _ 享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connections for MySql
Hi, I am writing a thread safe connection pool class. I want to connect to MySQL through jdbc driver. In the initialization method, I write a method that makes connections to the database and stores them in a vector. When, I try making the 99th connection, MySQL throws an error that says java.sql.SQLException: General error, message from server: Too many connections. What is max. limit on the number of connections for the JDBC driver? Thanks, Deepak
Re: Connections for MySql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Deepak Vishwanathan wrote: | Hi, | | | | I am writing a thread safe connection pool class. I want to connect to | MySQL through jdbc driver. In the initialization method, I write a | method that makes connections to the database and stores them in a | vector. When, I try making the 99th connection, MySQL throws an error | that says java.sql.SQLException: General error, message from server: | Too many connections. | | | | What is max. limit on the number of connections for the JDBC driver? | | | | Thanks, | | Deepak Deepak, This is not a client-side (JDBC) issue. See: http://dev.mysql.com/doc/mysql/en/Too_many_connections.html Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAlV+9tvXNTca6JD8RAtECAJ47hlU16q0ieptGbI2lw4s7957e0ACghOM5 xyMxeNmyi1eP1Tn6wK9KtYY= =FU51 -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 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]
Re: Re: Rapid-fire connections causing MySQL grief
Hi.
On Wed 2002-09-11 at 15:10:22 -0400, [EMAIL PROTECTED] wrote:
On Wed, Sep 11, 2002 at 02:55:01PM -0400, Kent Hoover wrote:
Version: MySQL.3.22.32-log
Does this problem ring a bell with anyone?
I'm seeing two undesired behaviors, both, *I think*
seem to be because I'm pounding a lot of connections
against the same server/table, all from the same
remote host. The workload that reaches MySQL is
a succession of CONNECT-QUERY (very simple)- QUIT,
say 200 times per second. After about the first 4000
connections, MySQL restarts, complaining that
mysqld is hanging.
It is well possible, that you run against a TCP stack limit. To quote
Monty:
--
The problem is that when you close a TCP/IP socket there is a timeout
of up to 30 seconds until the resources are properly freed. This
means that after a while you will run out of free TCP/IP sockets.
--
They are in a state of TIME_WAIT (this phase stays for 120 secs
according to RFC, but will be lower in real implementations). Newer
kernels (i.e. 2.4.x) should encounter this problem far later, AFAIK,
because they have set the timeout lower.
The only thing that doesn't fit into this is that MySQL shouldn't
restart. But maybe that is the work of some watchdog which doesn't
know better?
We're running Red Hat Linux 6.2.
Oh, yes, we've considered upgrading. Kind of hesitant to take
that step so far.
Recompiling the kernel should help. From [EMAIL PROTECTED]:
--
in linux, look at /usr/src/linux/include/net/tcp.h and modify this field
and then recompile the kernel
#define TCP_TIMEWAIT_LEN (60*HZ) /* how long to wait to successfully
* close the socket, about 60 seconds */
--
I've read through the Fixed in version { } sections of the
manual, didn't recognize what I have here, and was hoping that
someone out here would say Yes, I remember that, and it was
fixed!
HTH,
Benjamin.
--
[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: Rapid-fire connections causing MySQL grief
On Wed, Sep 11, 2002 at 02:55:01PM -0400, Kent Hoover wrote: Version: MySQL.3.22.32-log Does this problem ring a bell with anyone? I'm seeing two undesired behaviors, both, *I think* seem to be because I'm pounding a lot of connections against the same server/table, all from the same remote host. The workload that reaches MySQL is a succession of CONNECT-QUERY (very simple)- QUIT, say 200 times per second. After about the first 4000 connections, MySQL restarts, complaining that mysqld is hanging. Which OS are you running? Also, that's a pretty old MySQL. Have you considered upgrading? Many bugs have been fixed since then. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 36 days, processed 736,307,896 queries (233/sec. avg) - 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: Re: Rapid-fire connections causing MySQL grief
On Wed, 11 Sep 2002, Jeremy Zawodny ([EMAIL PROTECTED]) wrote:
On Wed, Sep 11, 2002 at 02:55:01PM -0400, Kent Hoover wrote:
Version: MySQL.3.22.32-log
Does this problem ring a bell with anyone?
I'm seeing two undesired behaviors, both, *I think*
seem to be because I'm pounding a lot of connections
against the same server/table, all from the same
remote host. The workload that reaches MySQL is
a succession of CONNECT-QUERY (very simple)- QUIT,
say 200 times per second. After about the first 4000
connections, MySQL restarts, complaining that
mysqld is hanging.
Which OS are you running?
Also, that's a pretty old MySQL. Have you considered upgrading? Many
bugs have been fixed since then.
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
Jeremy:
We're running Red Hat Linux 6.2.
Oh, yes, we've considered upgrading. Kind of hesitant to take
that step so far.
I've read through the Fixed in version { } sections of the
manual, didn't recognize what I have here, and was hoping that
someone out here would say Yes, I remember that, and it was
fixed!
Thanks,
Kent Hoover
-
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
Rapid-fire connections causing MySQL grief
Version: MySQL.3.22.32-log Does this problem ring a bell with anyone? I'm seeing two undesired behaviors, both, *I think* seem to be because I'm pounding a lot of connections against the same server/table, all from the same remote host. The workload that reaches MySQL is a succession of CONNECT-QUERY (very simple)- QUIT, say 200 times per second. After about the first 4000 connections, MySQL restarts, complaining that mysqld is hanging. Secondly, prior to the restart, an occasional connection is denied because of an invalid password! All connections are requested by the same user with the same password. Please, someone, tell me that these 2 problems are both load related, were found and corrected by a later release than the one I have. Thanks, Kent Hoover - 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: Rapid-fire connections causing MySQL grief
At 01:55 PM 9/11/2002, you wrote: Version: MySQL.3.22.32-log Does this problem ring a bell with anyone? I'm seeing two undesired behaviors, both, *I think* seem to be because I'm pounding a lot of connections against the same server/table, all from the same remote host. The workload that reaches MySQL is a succession of CONNECT-QUERY (very simple)- QUIT, say 200 times per second. After about the first 4000 connections, MySQL restarts, complaining that mysqld is hanging. Secondly, prior to the restart, an occasional connection is denied because of an invalid password! All connections are requested by the same user with the same password. Please, someone, tell me that these 2 problems are both load related, were found and corrected by a later release than the one I have. Thanks, Kent Hoover Kent, Just a thought, but if your table is read-only and not extremely large, have you thought of changing it to a heap table? When the server starts up, create the heap table and import the MYISAM table into it. Use something like Create table h_mytable select * from mytable type=heap. Then build the indexes for it. Heap tables may solve the problem even for tables with a few thousand rows. Latency because of disk access is also reduced. If you do need to write to it, add a TimeStamp column and every 30 seconds write the modified rows back to the original table. Mike (sql query) - 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
Different Connections To MySQL
Are there any ways to remotely connect to MySQL besides TCP/IP? Also, what are the 'bad' implications for allowing to connect to MySQL via TCP remotely? Thanks for your advice in advance. - 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
Number of connections to mysql server
Hi, I have a client BD application that access a remote MYSQL server and its job is 4-7hours long and it should be connected to the server all this time :( My question is: how many simultaneous connections the mysql server can handle? And what does happen when this limit is overstepped? Thank u very much, Edilson. Edilson Vasconcelos de Melo Junior www.jrsoftwares.com.br - Portal JR [EMAIL PROTECTED] Fone: (+55)(19)3256-3577 Cel : (+55)(19)9111-5873 - 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: Number of connections to mysql server
At 22:45 -0300 3/26/02, Edilson Vasconcelos de Melo Junior wrote: Hi, I have a client BD application that access a remote MYSQL server and its job is 4-7hours long and it should be connected to the server all this time :( Why is that a problem? My question is: how many simultaneous connections the mysql server can handle? SHOW VARIABLES LIKE 'max\_connections'; And what does happen when this limit is overstepped? The server will refuse connections if they're all used up. Thank u very much, Edilson. Edilson Vasconcelos de Melo Junior www.jrsoftwares.com.br - Portal JR [EMAIL PROTECTED] Fone: (+55)(19)3256-3577 Cel : (+55)(19)9111-5873 - 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: Number of connections to mysql server
On Tue, 26 Mar 2002 22:45:18 -0300 Edilson Vasconcelos de Melo Junior [EMAIL PROTECTED] wrote: Hi, I have a client BD application that access a remote MYSQL server and its job is 4-7hours long and it should be connected to the server all this time :( My question is: how many simultaneous connections the mysql server can handle? And what does happen when this limit is overstepped? it depends on your Operating System (kernel), and also your my.cnf ... but the most important is the kernel. if it's overlimit, so the server (OS) can't handle or reject new connections. some OS become panic when it happens. -- Avoid the Gates of Hell. Use Linux -- unknown source - 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
connections to MySQL
Lonnie, Friday, February 15, 2002, 8:11:59 PM, you wrote: LC Hello All, LC I am new to this list and hope that someone could answer this LC question for me. LC I am running on a fresh install of Redhat 7.2 and am trying to LC connect to MySQL from a program that I have to test the connections, LC but am getting error messages saying that the host in not allowed to LC connect to this MySQL server. LC This is strange because I ran my test app on the same machine that LC the MySQL server is running. LC Is there some file that I must modify to tell MySQL to accept LC connections from various machines including localhost? LC Also, does some one have a simple test program that they know works LC that I might be able to use to try and connect to a simple database? You can find more info about connecting to the MySQL Server if you look at: http://www.mysql.com/doc/C/o/Connecting.html Check your privileges. If you have problems with privileges you can get some more info at: http://www.mysql.com/doc/G/R/GRANT.html or http://www.mysql.com/doc/A/c/Access_denied.html LC Thanks in advance, LC Lonnie -- For technical support contracts, goto https://order.mysql.com/ 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
connections to MySQL
Hello All, I am new to this list and hope that someone could answer this question for me. I am running on a fresh install of Redhat 7.2 and am trying to connect to MySQL from a program that I have to test the connections, but am getting error messages saying that the host in not allowed to connect to this MySQL server. This is strange because I ran my test app on the same machine that the MySQL server is running. Is there some file that I must modify to tell MySQL to accept connections from various machines including localhost? Also, does some one have a simple test program that they know works that I might be able to use to try and connect to a simple database? I need to validate my app is working correctly. Thanks in advance, Lonnie -- Lonnie Cumberland OutStep Technologies Incorporated (313) 832-7366 URL: http://www.outstep.com EMAIL: [EMAIL PROTECTED] : [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: connections to MySQL
does your test app connect with root as the user? -Original Message- From: Lonnie Cumberland [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 12:12 PM To: [EMAIL PROTECTED] Subject: connections to MySQL Hello All, I am new to this list and hope that someone could answer this question for me. I am running on a fresh install of Redhat 7.2 and am trying to connect to MySQL from a program that I have to test the connections, but am getting error messages saying that the host in not allowed to connect to this MySQL server. This is strange because I ran my test app on the same machine that the MySQL server is running. Is there some file that I must modify to tell MySQL to accept connections from various machines including localhost? Also, does some one have a simple test program that they know works that I might be able to use to try and connect to a simple database? I need to validate my app is working correctly. Thanks in advance, Lonnie -- Lonnie Cumberland OutStep Technologies Incorporated (313) 832-7366 URL: http://www.outstep.com EMAIL: [EMAIL PROTECTED] : [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: Getting more than 256 connections with MySQL/Linux
Alex Coke-Smyth wrote: max_connections=300 basically anything over 256 ends up giving the error : Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug after 256 connections are open. There is plenty of memory available and checking the docs says you should be able to get up to 1500 : If you are using our binary or RPM version 3.23.25 or later, you can safely set `max_connections' at 1500 using a linux 2.2.19 kernel and glibc2.1. Has anyone experienced this problem or know how I can get around it? Yes sure! This is limit of linux 2.2.x kernel. Upgrade to 2.4.x OR change values in file limits.h of linux 2.2.x kernel. There is a value of NR_TASKS set to 512 which you should to increase. There are some comments about it also. All this is documented in manual... -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Hong Kong, China ___/ 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
Getting more than 256 connections with MySQL/Linux
Hi, I am receiving errors when setting max_connections=300 basically anything over 256 ends up giving the error : Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug after 256 connections are open. There is plenty of memory available and checking the docs says you should be able to get up to 1500 : If you are using our binary or RPM version 3.23.25 or later, you can safely set `max_connections' at 1500 I am using these rpm's from the MySQL website: MySQL-3.23.38-1.i386.rpm MySQL-devel-3.23.38-1.i386.rpm MySQL-bench-3.23.38-1.i386.rpm MySQL-shared-3.23.38-1.i386.rpm MySQL-client-3.23.38-1.i386.rpm using a linux 2.2.19 kernel and glibc2.1. Has anyone experienced this problem or know how I can get around it? Many thanks Alex - 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
how many client connections can Mysql handle?
Hi, just want to know some limits of Mysql. by reading Mysql source code, I have found mysql still using select() to handle connections, so can anyone tell me if Mysql will handle connections less than 1024 in Linux ? I know Mysql uses per thread for client connection, on FreeBSD, thread number can be more than on Linux, can handle more connections than Linux, but this Mysql design is limited by select(), there will have file handle occupy handle slot, so I think Mysql will handle less than 1024 connections, right? Thanks David Xu
