Important

New database features are no longer connected to engine versions; features are now enabled independently during scheduled update windows, and “major” and “minor” releases no longer exist in Helios. Please visit the release notes to view the latest features available in your database clusters.

C# / .NET Core

Dependencies

SingleStore Connector for .NET and .NET Core

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 = "svc-3nb226d8-ee13-47f0-8ca4-2dc820773442-dml.aws-oregon-2.svc.singlestore.com";
        public const int PORT = 3306;
        public const string USER = "admin";
        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 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();
            }
        }

        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.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;
            }
        }
    }
}

C# / .NET Core

Was this article helpful?

Was this article helpful?