StarBlog Bonus Episode (1) Brand new visit statistics feature, asynchronous queue, sharded storage

Preface

Although my current work focus has shifted primarily to AI, compared to the grand narratives of various large language models, I still prefer to conceptualize features, write code, and enjoy the process of solving problems and deploying them online.

As I mentioned during previous updates to the StarBlog series, as features evolve, I will continue to write bonus content after the tutorial series concludes. Well, here comes the first piece of bonus content.

This is a newly redesigned visit statistics feature.

Visit Statistics

The visit statistics feature was implemented long ago, as described in this earlier post: Developing a Blog Project Based on .NetCore - StarBlog (11) Implementing Visit Statistics

Problems with the Old Implementation

Previously, a middleware VisitRecordMiddleware was added, and every request was written to the database.

This leads to two problems:

1. It affects performance.
2. It leads to a database that becomes too large and difficult to back up.

New Implementation

I've never been quite satisfied with the previous implementation.

This time, I've essentially redesigned it to address all the aforementioned issues in one go.

I used mermaid to draw a simple diagram (my first attempt at inserting a mermaid-drawn diagram in an article, not sure how it will look).

https://mermaid.js.org/syntax/flowchart.html

---
title: New Access Statistics Feature Design Diagram
---
flowchart LR
    Request(用户请求) --> Middleware(访问日志中间件)
    Middleware(访问日志中间件) --> Queue[/日志队列/]
    Worker[后台定时任务] --取出日志--- Queue[/日志队列/]
    Worker[后台定时任务] --写入数据库--> DB[(访问日志独立数据库)]

The new implementation uses a queue to temporarily store access logs.

A background task is added to periodically retrieve the access logs from the queue and write them to the database.

This way, it won't affect the access speed.

With this, the introduction to the new feature is basically complete.

Of course, there will be some details to pay attention to in the specific implementation, which will be covered in the code section that follows.

New Tech Stack

For this project, I chose EFCore as the ORM.

The reasons and how to integrate it are explained in my previous article: Asp-Net-Core Development Notes: Quickly Introducing EFCore into an Existing Project

The main goal was to use EFCore to more easily implement database sharding (分库).

Implementation

Next comes the implementation details.

Queue

Add the VisitRecordQueueService.cs file in StarBlog.Web/Services.

public class VisitRecordQueueService {
  private readonly ConcurrentQueue<VisitRecord> _logQueue = new ConcurrentQueue<VisitRecord>();
  private readonly ILogger<VisitRecordQueueService> _logger;
  private readonly IServiceScopeFactory _scopeFactory;

  /// <summary>
  /// Batch size
  /// </summary>
  private const int BatchSize = 10;

  public VisitRecordQueueService(ILogger<VisitRecordQueueService> logger, IServiceScopeFactory scopeFactory) {
    _logger = logger;
    _scopeFactory = scopeFactory;
  }

  // Enqueue a log
  public void EnqueueLog(VisitRecord log) {
    _logQueue.Enqueue(log);
  }

  // Periodically batch write to the database
  public async Task WriteLogsToDatabaseAsync(CancellationToken cancellationToken) {
    if (_logQueue.IsEmpty) {
      // Wait temporarily to avoid high-frequency meaningless checks
      await Task.Delay(1000, cancellationToken);
      return;
    }

    var batch = new List<VisitRecord>();
    // Dequeue a batch of logs from the queue
    while (_logQueue.TryDequeue(out var log) && batch.Count < BatchSize) {
      batch.Add(log);
    }

    try {
      using var scope = _scopeFactory.CreateScope();
      var dbCtx = scope.ServiceProvider.GetRequiredService<AppDbContext>();
      await using var transaction = await dbCtx.Database.BeginTransactionAsync(cancellationToken);
      try {
        dbCtx.VisitRecords.AddRange(batch);
        await dbCtx.SaveChangesAsync(cancellationToken);
        await transaction.CommitAsync(cancellationToken);
        _logger.LogInformation("Access logs Successfully wrote {BatchCount} logs to the database", batch.Count);
      }
      catch (Exception) {
        await transaction.RollbackAsync(cancellationToken);
        throw;
      }
    }
    catch (Exception ex) {
      _logger.LogError(ex, "Access logs Error writing logs to the database: {ExMessage}", ex.Message);
    }
  }
}

The following are used here:

• ConcurrentQueue, a thread-safe FIFO queue
• A transaction is used during the batch database write, with automatic rollback if an error occurs

Middleware

Modify StarBlog.Web/Middlewares/VisitRecordMiddleware.cs

public class VisitRecordMiddleware {
  private readonly RequestDelegate _next;

  public VisitRecordMiddleware(RequestDelegate requestDelegate) {
    _next = requestDelegate;
  }

  public Task Invoke(HttpContext context, VisitRecordQueueService logQueue) {
    var request = context.Request;
    var ip = context.GetRemoteIpAddress()?.ToString();
    var item = new VisitRecord {
      Ip = ip?.ToString(),
      RequestPath = request.Path,
      RequestQueryString = request.QueryString.Value,
      RequestMethod = request.Method,
      UserAgent = request.Headers.UserAgent,
      Time = DateTime.Now
    };
    logQueue.EnqueueLog(item);

    return _next(context);
  }
}

Nothing special here—it's just replacing the previous database operations with adding items to a queue.

Note that dependency injection cannot be used in the middleware's constructor, as the dependency injection container isn't fully ready when the IApplicationBuilder registers the middleware.

Background Tasks

Add the VisitRecordWorker.cs file in StarBlog/Services

public class VisitRecordWorker : BackgroundService {
  private readonly ILogger<VisitRecordWorker> _logger;
  private readonly IServiceScopeFactory _scopeFactory;
  private readonly VisitRecordQueueService _logQueue;
  private readonly TimeSpan _executeInterval = TimeSpan.FromSeconds(30);

  public VisitRecordWorker(ILogger<VisitRecordWorker> logger, IServiceScopeFactory scopeFactory, VisitRecordQueueService logQueue) {
    _logger = logger;
    _scopeFactory = scopeFactory;
    _logQueue = logQueue;
  }

  protected override async Task ExecuteAsync(CancellationToken stoppingToken) {
    while (!stoppingToken.IsCancellationRequested) {
      await _logQueue.WriteLogsToDatabaseAsync(stoppingToken);
      await Task.Delay(_executeInterval, stoppingToken);
      _logger.LogDebug("Background Task VisitRecordWorker ExecuteAsync");
    }
  }
}

Please note that BackgroundService has a singleton lifetime, whereas database-related services have a scoped lifetime. Therefore, you should first obtain a scope before using them, rather than injecting them directly.

Here, IServiceScopeFactory is used instead of IServiceProvider.

In a multi-threaded environment, this ensures that an instance from the root container can be obtained, which is also the recommended approach in Microsoft's documentation.

Database Sharding and Refactoring (分库与重构)

Introducing EFCore

As mentioned above, access logs can be quite large. After launching this feature, within a few months, it accumulated hundreds of thousands of records, occupying over 100 MB in the database. Although this is far from reaching the database's bottleneck, for our lightweight project, when I wanted to back up the data, this hundred-megabyte access log became redundant data compared to just a few megabytes of blog data. This part hardly has any significance for backup.

Therefore, separating the database becomes imperative.

This time, I used EFCore to exclusively manage this new database.

Regarding how to introduce and implement it in detail, the previous article covered it thoroughly and will not be repeated here.

Asp-Net-Core Development Notes: Quickly Introducing EFCore into an Existing Project

Refactoring Services

Since EFCore is now in use, the related services also need adjustments, switching from FreeSQL to EFCore.

Modify StarBlog.Web/Services/VisitRecordService.cs

public class VisitRecordService {
  private readonly ILogger<VisitRecordService> _logger;
  private readonly AppDbContext _dbContext;

  public VisitRecordService(ILogger<VisitRecordService> logger, AppDbContext dbContext) {
    _logger = logger;
    _dbContext = dbContext;
  }

  public async Task<VisitRecord?> GetById(int id) {
    var item = await _dbContext.VisitRecords.FirstOrDefaultAsync(e => e.Id == id);
    return item;
  }

  public async Task<List<VisitRecord>> GetAll() {
    return await _dbContext.VisitRecords.OrderByDescending(e => e.Time).ToListAsync();
  }

  public async Task<IPagedList<VisitRecord>> GetPagedList(VisitRecordQueryParameters param) {
    var querySet = _dbContext.VisitRecords.AsQueryable();

    // Search
    if (!string.IsNullOrEmpty(param.Search)) {
      querySet = querySet.Where(a => a.RequestPath.Contains(param.Search));
    }

    // Sort
    if (!string.IsNullOrEmpty(param.SortBy)) {
      var isDesc = param.SortBy.StartsWith("-");
      var orderByProperty = param.SortBy.Trim('-');
      if (isDesc) {
        orderByProperty = $"{orderByProperty} desc";
      }

      querySet = querySet.OrderBy(orderByProperty);
    }
    
    IPagedList<VisitRecord> pagedList = new StaticPagedList<VisitRecord>(
      await querySet.Page(param.Page, param.PageSize).ToListAsync(),
      param.Page, param.PageSize,
      Convert.ToInt32(await querySet.CountAsync())
    );
    return pagedList;
  }

  /// <summary>
  /// Overview Data
  /// </summary>
  public async Task<object> Overview() {
    var querySet = _dbContext.VisitRecords
      .Where(e => !e.RequestPath.StartsWith("/Api"));

    return new {
      TotalVisit = await querySet.CountAsync(),
      TodayVisit = await querySet.Where(e => e.Time.Date == DateTime.Today).CountAsync(),
      YesterdayVisit = await querySet
        .Where(e => e.Time.Date == DateTime.Today.AddDays(-1).Date)
        .CountAsync()
    };
  }

  /// <summary>
  /// Trend Data
  /// </summary>
  /// <param name="days">View data for the last few days, default 7 days</param>
  public async Task<object> Trend(int days = 7) {
    var startDate = DateTime.Today.AddDays(-days).Date;
    return await _dbContext.VisitRecords
      .Where(e => !e.RequestPath.StartsWith("/Api"))
      .Where(e => e.Time.Date >= startDate)
      .GroupBy(e => e.Time.Date)
      .Select(g => new {
        time = g.Key,
        date = $"{g.Key.Month}-{g.Key.Day}",
        count = g.Count()
      })
      .OrderBy(e => e.time)
      .ToListAsync();
  }

  /// <summary>
  /// Statistics
  /// </summary>
  public async Task<object> Stats(DateTime date) {
    return new {
      Count = await _dbContext.VisitRecords
        .Where(e => e.Time.Date == date)
        .Where(e => !e.RequestPath.StartsWith("/Api"))
        .CountAsync()
    };
  }
}

The main changes were made to the GetPagedList and Overview interfaces.

• EFCore does not natively support sorting by field name, so I introduced the Microsoft.EntityFrameworkCore.DynamicLinq library to achieve this.
• EFCore does not seem to have FreeSQL's Aggregate API. It could be replaced with raw SQL, but I didn't do that and instead performed multiple queries; the impact is negligible.

The other changes are syntax differences and can be easily modified.

Summary

After a long hiatus, I'm back to developing new features for StarBlog. The C# development experience remains as smooth as ever.

However, the warning "Packages with vulnerabilities have been detected" also reminds me that the SDK version of this project is outdated.

So, I'll find time to upgrade as soon as possible.

A little preview: the next feature is related to backup.

Reference Materials

Here are some useful resources for further reading:

• https://learn.microsoft.com/zh-cn/dotnet/core/extensions/scoped-service
• https://www.cnblogs.com/wucy/p/16566495.html