Help: Timeouts and ExecuteReader returns null under load

[Matt Houser]

I have an application where I am doing some heavy reads, inserts, and updates in a table. The table does not have any FK and has one index.

Most of the time, my server sits around 100 connections according to MySQL Workbench Performance Dashboard.

At certain times, activity in my application increases and 2 things happen:

1. I get Timeout exceptions during ExecuteReader:

MySql.Data.MySqlClient.MySqlException (0x80004005): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.TimeoutException: Timeout in IO operation\r\n at MySql.Data.MySqlClient.TimedStream.StopTimer()\r\n at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)\r\n at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)\r\n at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)\r\n at MySql.Data.MySqlClient.MySqlStream.LoadPacket()\r\n at MySql.Data.MySqlClient.MySqlStream.ReadPacket()\r\n at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)\r\n at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)\r\n at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)\r\n at MySql.Data.MySqlClient.MySqlDataReader.NextResult()\r\n at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)\r\n at MySql.Data.MySqlClient.ExceptionInterceptor.Throw(Exception exception)\r\n at MySql.Data.MySqlClient.MySqlConnection.Throw(Exception ex)\r\n at MySql.Data.MySqlClient.MySqlConnection.HandleTimeoutOrThreadAbort(Exception ex)\r\n at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)\r\n at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()

2. ExecuteReader will actually return a null value. You'll see the null check in my code below.

I am not setting any explicit timeout anywhere. Based on my logging, the timeout experienced in #1 above is almost instantaneous. Timestamps of the exception message and the line preceding the ExecuteReader is less than 200ms. So the reader is not sitting there for 30 or 60 seconds.

Each of my "worker" servers has n working threads that execute database calls. When I have 3 workers running all is OK during heavy load, but there are fewer db calls per second.

Increasing the 6 worker servers (all still having n working threads) increases the db calls per second on the database and that's when the issues happen.

So each worker is still processing the same amount of data.

back_log and max_connections are all above 100. Is it possible it's related to back_log?

Is there anything else I can look at? I suspect it's server-related since the number of worker servers affects the results.

--------------
MySQL Server:

Amazon RDS Instance m4.xlarge, Multi-AZ


--------------
From 'SHOW VARIABLES':

back_log: 314
max_connections: 1320

--------------

Table schema:


CREATE TABLE IF NOT EXISTS `States` (
`StateId` INT NOT NULL AUTO_INCREMENT,
`ItemId` INT NOT NULL,
`Name` VARCHAR(255) NOT NULL,
`IntValue` INT NULL DEFAULT NULL,
`StringValue` VARCHAR(5000) NULL DEFAULT NULL,
`BoolValue` BIT NULL DEFAULT NULL,
`DateTimeValue` DATETIME NULL DEFAULT NULL,
`BigIntValue` BIGINT NULL DEFAULT NULL,
`ObjectValue` MEDIUMTEXT NULL DEFAULT NULL,
PRIMARY KEY (`StateId`),
INDEX `IX_States_2` (`ItemId` ASC))
ENGINE = InnoDB;

--------------

Update Code:

public void UpdateState(State state)
{
if (state == null)
throw new ArgumentNullException("state");

using (MySqlConnection connection = new MySqlConnection(GetConnectionString(false)))
{
connection.Open();

string commandText = @"
UPDATE `States`
SET
`ItemId` = @ItemId,
`Name` = @Name,
`IntValue` = @IntValue,
`BigIntValue` = @BigIntValue,
`StringValue` = @StringValue,
`BoolValue` = @BoolValue,
`DateTimeValue` = @DateTimeValue,
`ObjectValue` = @ObjectValue
WHERE
`StateId` = @StateId;
SELECT row_count() AS RowCount;
";

using (MySqlCommand command = new MySqlCommand(commandText, connection))
{
command.Parameters.Add("@StateId", MySqlDbType.Int32).Value = state.StateId;
command.Parameters.Add("@ItemId", MySqlDbType.Int32).Value = state.ItemId;
command.Parameters.Add("@Name", MySqlDbType.VarChar).Value = state.Name;
command.Parameters.Add("@IntValue", MySqlDbType.Int32).Value = state.IntValue;
command.Parameters.Add("@BigIntValue", MySqlDbType.Int64).Value = state.BigIntValue;
command.Parameters.Add("@StringValue", MySqlDbType.VarChar).Value = state.StringValue;
command.Parameters.Add("@BoolValue", MySqlDbType.Bit).Value = state.BoolValue;
command.Parameters.Add("@DateTimeValue", MySqlDbType.DateTime).Value = state.DateTimeValue;
command.Parameters.Add("@ObjectValue", MySqlDbType.String).Value = state.ObjectValue;

using (var reader = command.ExecuteReader())
{
if (reader == null)
throw new NullReferenceException("ExecuteReader returned null reader");

reader.Read();
int nCount = reader.GetInt32("RowCount");
if (nCount < 1)
throw new Exception("State not updated.");
}
}
}
}