Skip to main content

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 SingleStoreConnector;

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 = "passkey";

        // 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 SingleStoreConnection();
            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 Worker()
        {
            // Create another connection per thread
            using (IDbConnection conn = new SingleStoreConnection())
            {
                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.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;
            }
        }
    }
}