Slow Select Performance

Off-Topic
1

We are starting to evaluate this product and have run into an issue with performance. We have a table with 2,000 records and running a lookup by id takes 6ms on the same server. We have run the query twice to ensure that its all warmed up, and makes no difference. We are using the docker insta-cluster setup.

Is this normal?

How should we proceed?

2

Could you please share your table definition?

3

Table,Create Table
TA,“CREATE TABLE TA (
Id char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Created datetime NOT NULL,
Account char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Enabled bit(1) NOT NULL,
Deleted bit(1) NOT NULL,
BS char(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
RM char(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
IsMaster bit(1) NOT NULL,
Currency char(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
ConnectionStatus char(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
LastTick datetime DEFAULT NULL,
LastHeartbeat datetime DEFAULT NULL,
Drawdown decimal(27,2) NOT NULL,
WarningLevel decimal(27,2) NOT NULL,
SoftStopLevel decimal(27,2) NOT NULL,
HardStopLevel decimal(27,2) NOT NULL,
ClosedProfit decimal(27,2) NOT NULL,
ProfitCalculationStartAmount decimal(27,2) NOT NULL,
MaxProfit decimal(27,2) NOT NULL,
OpenProfit decimal(27,2) NOT NULL,
WarningNotified bit(1) NOT NULL,
MaximumLeverage decimal(27,2) NOT NULL,
MaximumLeveragePerTrade decimal(27,2) NOT NULL,
SoftStopNotified bit(1) NOT NULL,
HardStopNotified bit(1) NOT NULL,
MaximumLeveragePerSymbol decimal(27,2) NOT NULL,
MaximumDrawdownPerTrade decimal(27,2) NOT NULL,
CopyStops bit(1) NOT NULL,
CopyPendingOrders bit(1) NOT NULL,
Login int(11) DEFAULT NULL,
Password varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
/*!90618 SHARD / KEY id (Id)
) /!90623 AUTOSTATS_CARDINALITY_MODE=PERIODIC, AUTOSTATS_HISTOGRAM_MODE=CREATE / /!90623 SQL_MODE=‘STRICT_ALL_TABLES’ */”

4

And what’s the read query that’s 6ms?

5

SELECT * FROM TA WHERE Id = ‘699bd1ed-4efb-4547-a6ef-364ae47f428a’;

6

Just to add, the read query is being run from a .NET Core application using the standard MySql connector. For the purpose of the test we did not actually parse the response, just got it from the server

7
  1. Try making ID primary key’
  2. Make sure you are not counting establishing a connection for latency (connect one and run queries multiple times)
  3. Try using a python client to make sure it’s not in .NET

We have had .NET apps with sub 1ms response time

8

So, changed to a primary key, and that has increased latency to 138ms!

I am not including the connection, please see code below:

using (var connection = new MySqlConnection(_connectionString))
{
connection.Open();

var stm = "SELECT * FROM TA WHERE Id = '699bd1ed-4efb-4547-a6ef-364ae47f428a'";
var cmd = new MySqlCommand(stm, connection);

var sww = new Stopwatch();

sww.Start();
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
    while (rdr.Read())
    {
        Console.WriteLine(rdr.GetGuid(0));
    }
}

sww.Stop();
Log.Error(sww.ElapsedMilliseconds.ToString());

sw.Restart();
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
    while (rdr.Read())
    {
        Console.WriteLine(rdr.GetGuid(0));
    }
}

sww.Stop();
Log.Error(sww.ElapsedMilliseconds.ToString());

}

With regards Python, i’m not in a position to test this, but to put this into context, this same .NET app is able to lookup from a local Redis server in around 0.25ms

9

This is really strange. You don’t need a shard key. By default we shard by a primary key.

You also are better off by making id BINARY rather then CHAR. UTF-8 comparison is more expensive than binary. Having said that 128ms doesn’t ‘make sense to me. It should be a <1ms command.

I’ll check with the team to see what else can be off…

10

I agree that a binary key would be better, but this is an existing application that is being ported, so we are not in a position to make such big changes. Also, Redis is more than happy with this as the key.

The redis solution is working well, but it scares the living daylights out of us to move away from MSSql where the data is currently stored and put it 100% into redis… we would much prefer an SQL based solution, but lookups under 1ms are a requirement.

I am sure that the product does work, so am wiling to bare with you for a bit until we get to the bottom of it.

11

So 6ms is likely a product of MemSQL deployed in a small container. If you put it on a real server this should be solved.

128ms is a likely you are doing a table scan vs lookup on a key. explain should give you a an answer to that. Feel free to paste the output of explain here.

I agree onMemSQL vs Redis. If you want system of records capabilities you want a real database.

12

I have dropped and recreated the table and things have improved, down to 2.5ms now.

It is running on a production spec server, 24 cores, ssd, 64gb RAM etc, but is only in a single container. Would scaling this out over another server improve things? We need to see latency around 1ms to proceed.

13

further to this, when including the connection (pooled) establishment into the mix, we are back into double digit latencies.

are you able to get any information on the setup for .NET where you have seen sub 1ms latencies?

14

Please send an email to nikita@memsql.com. I’ll introduce you to folks that achieved <1ms latencies with .NET. It may require poking around your setup.