SingleStore Managed Service

C# / .NET Core

Dependencies

Code

using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Threading;
using System.Threading.Tasks;
using MySql.Data.MySqlClient; // dotnet add package MySql.Data

namespace SingleStoreDBTest
{
  public class SingleStoreDBTest
  {
    /**
    * Tweak the following globals to fit your environment
    * ###################################################
    */
    public const string HOST = "127.0.0.1";
    public const int PORT = 3306;
    public const string USER = "root";
    public const string PASSWORD = "";

    // Specify which database and table to work with.
    // Note: this database will be dropped at the end of this script
    public const string DATABASE = "test";
    public const string TABLE = "tbl";

    // The number of workers to run
    public const int NUM_WORKERS = 20;
    // Run the workload for this many seconds
    public const int WORKLOAD_TIME = 10; // seconds

    // Batch size to use
    public const int BATCH_SIZE = 5000;

    /**
    * Internal code starts here
    * #########################
    */

    private IDbCommand dbCommand;
    private string insertCommand;

    private void GetDbCommand()
    {
      IDbConnection conn = new MySqlConnection();
      conn.ConnectionString = $"Server={HOST};Port={PORT};Uid={USER};Pwd={PASSWORD};";
      conn.Open();
      dbCommand = conn.CreateCommand();

      string[] _batch = new string[BATCH_SIZE];
      Array.Fill(_batch, "(DEFAULT)");
      insertCommand = $"INSERT INTO {TABLE} VALUES {string.Join(",", _batch)}";
    }

    private void SetupTestDb()
    {
      dbCommand.CommandText = $"CREATE DATABASE IF NOT EXISTS {DATABASE}";
      dbCommand.ExecuteNonQuery();
      dbCommand.CommandText = $"USE {DATABASE}";
      dbCommand.ExecuteNonQuery();
      dbCommand.CommandText = $"CREATE TABLE {TABLE} (id int primary key auto_increment)";
      dbCommand.ExecuteNonQuery();
    }

    private void Warmup()
    {
      Console.WriteLine("Warming up workload");
      dbCommand.CommandText = insertCommand;
      dbCommand.ExecuteNonQuery(); // FRAGILE: included in count, not included in time
    }

    private void DoBenchmark()
    {
      Console.WriteLine($"Launching {NUM_WORKERS} workers for {WORKLOAD_TIME} sec");
      Thread[] workers = new Thread[NUM_WORKERS];
      for(int i = 0; i < NUM_WORKERS; i++)
      {
        workers[i] = new Thread(new ThreadStart(Worker));
        workers[i].Start();
      }
      Console.WriteLine($"{workers.Length} workers running...");
      for(int i = 0; i < NUM_WORKERS; i++)
      {
        workers[i].Join();
      }
    }

    /*
    // yields authentication error: https://bugs.mysql.com/bug.php?id=75917
    private async Task DoBenchmark()
    {
      List<Task> workers = new List<Task>();
      for(int i = 0; i < NUM_WORKERS; i++)
      {
        workers.Add(Task.Run(Worker));
      }
      Console.WriteLine($"{workers.Count} workers running...");
      await Task.WhenAll(workers);
    }
    */

    private void Worker()
    {
      // Create another connection per thread
      using (IDbConnection conn = new MySqlConnection())
      {
        conn.ConnectionString = $"Server={HOST};Port={PORT};database={DATABASE};Uid={USER};Pwd={PASSWORD};SslMode=None;";
        conn.Open();

        using (IDbCommand dbCommand = conn.CreateCommand())
        {
          dbCommand.CommandText = insertCommand;
          Stopwatch stop = new Stopwatch();
          stop.Start();
          while(stop.ElapsedMilliseconds < WORKLOAD_TIME*1000)
          {
            dbCommand.ExecuteNonQuery();
          }
        }
      }
    }

    private void ShowStats()
    {
      dbCommand.CommandText = $"USE {DATABASE}";
      dbCommand.ExecuteNonQuery();
      dbCommand.CommandText = $"SELECT COUNT(*) FROM {TABLE}";
      using (IDataReader reader = dbCommand.ExecuteReader())
      {
        long count = 0;
        while(reader.Read())
        {
          count = (long)reader["COUNT(*)"];
        }
        Console.WriteLine($"{count} rows inserted using {NUM_WORKERS} workers");
        Console.WriteLine($"{count / WORKLOAD_TIME} rows per second");
      }
    }

    private void CleanupTestDb()
    {
      if (dbCommand != null)
      {
        Console.WriteLine("Cleaning up");
        dbCommand.CommandText = $"USE `information_schema`";
        dbCommand.ExecuteNonQuery();
        dbCommand.CommandText = $"DROP DATABASE IF EXISTS {DATABASE}";
        dbCommand.ExecuteNonQuery();
        dbCommand = null;
      }
    }

    public static int Main(string[] args)
    {
      SingleStoreDBTest tester = new SingleStoreDBTest();
      try
      {
        tester.GetDbCommand();
        tester.SetupTestDb();
        tester.Warmup();
        tester.DoBenchmark();
        tester.ShowStats();
        tester.CleanupTestDb();
        return 0;
      }
      catch (Exception ex)
      {
        Console.WriteLine($"ERROR: {ex.Message}, {ex.GetType()}, {ex.StackTrace}");
        try
        {
          tester.CleanupTestDb();
        }
        catch
        {
          // ignore error
        }
        return 1;
      }
    }

  }
}