• AwesomeHive Team
• 0 Comments
• 2009 Views

One of the big advantages of microservices is, that they can be scaled independently. This article shows the benefits and challenges of scaling one microservice and its database.

You will create a working example application and manually implement application-layer sharding. It shows how to choose a shard key based on the use-cases and data model. This helps to apply the same principles to DBMS with integrated scaling like MongoDB, etc.

1. Use cases and Data model

The example application consists of a user and a post microservice. They communicate via messages:

The User microservice handles adding and modifying users. The Post microservices handles viewing and adding posts. There is far more interaction with the Post microservice. So when the load to the app increases the Post microservice will be the first microservice that needs to scale.

The name of the author is part of the PostService bounded context and therefore the Post microservice. Adding and modifying authors is done in the User microservice. The User microservice sends events when a new user is added or a username changes.

Logical Data Model of the PostService

Users can write posts in categories. They can also read the posts by category including the author name. Newest posts are on top. The categories are fixed and change seldom.

Based on these use-cases I decided to shard by category:

2. Implement the Microservice

Install Visual Studio Community (it’s free) with the ASP.NET and web development workload.

Create a solution and add an ASP.NET Core 5 Web API project with the name “PostService”. Disable HTTPS and activate OpenAPI Support.

Install the following NuGet packages:

• Microsoft.EntityFrameworkCore.Tools
• MySql.EntityFrameworkCore
• Newtonsoft.Json

Create the Entities

The index of the Post entity should speed up the retrieval of the latest posts in a category:

using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;

namespace PostService.Entities
{
    [Index(nameof(PostId), nameof(CategoryId))]
    public class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }

        public int UserId { get; set; }
        public User User { get; set; }

        [Required]
        public string CategoryId { get; set; }
        public Category Category { get; set; }
    }
}

The Version in the User entity will later help to handle out-of-order messages:

namespace PostService.Entities
{
    public class User
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int Version { get; set; }
    }
}

namespace PostService.Entities
{
    public class Category
    {
        public string CategoryId { get; set; }
    }
}

Create the PostServiceContext

using Microsoft.EntityFrameworkCore;

namespace PostService.Data
{
    public class PostServiceContext : DbContext
    {
        private readonly string _connectionString;

        public PostServiceContext(string connectionString)
        {
            _connectionString = connectionString;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseMySQL(_connectionString);
        }

        public DbSet<PostService.Entities.Post> Post { get; set; }
        public DbSet<PostService.Entities.User> User { get; set; }
        public DbSet<PostService.Entities.Category> Category { get; set; }
    }
}

Add connection strings for the shards in appsettings.Development.json (you will use two shards during debugging)

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "PostDbConnectionStrings": {
    "Shard0": "server=localhost; port=3310; database=post; user=root; password=pw; Persist Security Info=False; Connect Timeout=300",
    "Shard1": "server=localhost; port=3311; database=post; user=root; password=pw; Persist Security Info=False; Connect Timeout=300"    
  }
}

Add the DataAccess Code

GetConnectionString(string category)calculates the hash of the CategoryId. The first part of the hash modulo the number of configured shards (connection strings) determines the shard for the given category.

InitDatabase drops and recreates all tables in all shards and inserts dummy users and categories.

The other methods create and load posts.

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using PostService.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace PostService.Data
{
    public class DataAccess
    {
        private readonly List<string> _connectionStrings = new List<string>();

        public DataAccess(IConfiguration configuration)
        {
            var connectionStrings = configuration.GetSection("PostDbConnectionStrings");
            foreach(var connectionString in connectionStrings.GetChildren())
            {
                Console.WriteLine("ConnectionString: " + connectionString.Value);
                _connectionStrings.Add(connectionString.Value);
            }
        }

        public async Task<ActionResult<IEnumerable<Post>>> ReadLatestPosts(string category, int count)
        {
            using var dbContext = new PostServiceContext(GetConnectionString(category));
            return await dbContext.Post.OrderByDescending(p => p.PostId).Take(count).Include(x => x.User).Where(p => p.CategoryId == category).ToListAsync();
        }

        public async Task<int> CreatePost(Post post)
        {
            using var dbContext = new PostServiceContext(GetConnectionString(post.CategoryId));
            dbContext.Post.Add(post);
            return await dbContext.SaveChangesAsync();
        }

        public void InitDatabase(int countUsers, int countCategories)
        {
            foreach (var connectionString in _connectionStrings)
            {
                using var dbContext = new PostServiceContext(connectionString);
                dbContext.Database.EnsureDeleted();
                dbContext.Database.EnsureCreated();
                for (int i = 1; i <= countUsers; i++)
                {
                    dbContext.User.Add(new User { Name = "User" + i, Version = 1 });
                    dbContext.SaveChanges();
                }
                for (int i = 1; i <= countCategories; i++)
                {
                    dbContext.Category.Add(new Category { CategoryId = "Category" + i });
                    dbContext.SaveChanges();
                }
            }
        }

        private string GetConnectionString(string category)
        {
            using var md5 = MD5.Create();
            var hash = md5.ComputeHash(Encoding.ASCII.GetBytes(category));
            var x = BitConverter.ToUInt16(hash, 0) % _connectionStrings.Count;
            return  _connectionStrings[x];
        }
    }
}

Register DataAccess as a singleton in Startup.cs

public class Startup
    {
        ...

        public void ConfigureServices(IServiceCollection services)
        {

            services.AddControllers();
            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo { Title = "PostService", Version = "v1" });
            });

            services.AddSingleton<DataAccess>();
        }

        ---

Create the PostController

It uses the DataAccess class

using Microsoft.AspNetCore.Mvc;
using PostService.Data;
using PostService.Entities;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace PostService.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class PostsController : ControllerBase
    {
        private readonly DataAccess _dataAccess;

        public PostsController(DataAccess dataAccess)
        {
            _dataAccess = dataAccess;
        }

        [HttpGet]
        public async Task<ActionResult<IEnumerable<Post>>> GetLatestPosts(string category, int count)
        {
            return await _dataAccess.ReadLatestPosts(category, count);
        }

        [HttpPost]
        public async Task<ActionResult<Post>> PostPost(Post post)
        {
            await _dataAccess.CreatePost(post);
            return NoContent();
        }

        [HttpGet("InitDatabase")]
        public void InitDatabase([FromQuery] int countUsers, [FromQuery] int countCategories)
        {
            _dataAccess.InitDatabase(countUsers, countCategories);
        }
    }
}

3. Access a Database from the PostService

Install Docker Desktop

Create two MySql Containers (each command as one line)

C:\dev>docker run -p 3310:3306 --name=mysql1 -e MYSQL_ROOT_PASSWORD=pw -d mysql:5.6C:\dev>docker run -p 3311:3306 --name=mysql2 -e MYSQL_ROOT_PASSWORD=pw -d mysql:5.6

Use the swagger UI to interact with the service:

Init the Databases with 100 users and 10 categories:

Add a post to “Category1”:

{
  "title": "MyTitle",
  "content": "MyContent",
  "userId": 1,
  "categoryId": "Category1"
}

Read the top 10 posts in “Category1” to see your new post:

Connect to the database containers and verify which database contains the new post.

C:\dev>docker container exec -it mysql1 /bin/sh

Login to MySql with the password “pw” and read the posts:

The second instance does not contain any post:

C:\dev>docker container exec -it mysql2 /bin/sh

AwesomeHive Team

administrator