Mysql – Sleeping connections

cconnection-poolinginnodbmysql-5

I have a dedicated MySQL server(8GB) with following configuration.

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
lower_case_table_names = 1
default-storage-engine=InnoDB
innodb_buffer_pool_size=5G
innodb_log_file_size=512M
query_cache_size=512M
table_cache=64
innodb_flush_method=O_DIRECT

#Replication 

log-bin=mysql-bin
server-id=1
sync_binlog=1
innodb_flush_log_at_trx_commit=1

binlog-do-db=test_db1
binlog-do-db=test_db2

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

I have 2 applications which are connecting to different databases (test_db1 and test_db2) and perform all kinds of operations to the respective dbs. Both applications are written in C# and close the MySQL connections from application side.

 if (Connection != null)
            {
                if (Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                    Connection = null;
                }
            }

The 2 applications were running and doing their jobs perfectly. We set up replication recently and (I believe after that only) now connections are sleeping for long time and application have problem. I am getting an error Two many connections after a certain time and both applications stop working.

show status like '%onn%';

Variable_name             Value
Aborted_connects                 4
Connections                    1818
Max_used_connections         99
Ssl_client_connects              0
Ssl_connect_renegotiates         0
Ssl_finished_connects        0
Threads_connected                6

I have checked different posts asked about sleeping connection issues. Majority of the posts gave the reply like client side(application) is not disconnecting connection correctly or to reduce the value of interactive time_out and connection_time-out. Both solutions are not working for me.

Best Answer

In the c# code you posted the connection is only closed if it is 'Open', if the state was 'Connecting', 'Broken', 'Executing' or 'Fetching' it would be left open. It is standard practice in c# wrap your db connection in a using statement, this will make sure the connection is always properly disposed of, without the need to call connection.Close.

Here is an example where a stored proc is called from a connection with a using:

using (SqlConnection connection = new SqlConnection(connectionString)) 
{
    try    
    {
        connection.Open();
        SqlCommand command = new SqlCommand("StoredProcName", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@SomeID", someID));
        command.ExecuteNonQuery();    
    } 
    catch (Exception) 
    { 
        /*Handle error*/ 
    }
}
Related Question