How We Cut 1‑Second Query Times in a Legacy WAF Dashboard Using Redis Caching

Background

The client uses a WAF firewall that logs and blocks malicious requests. As log volume grew, several reporting pages became extremely slow, degrading user experience.

Technology Stack

SSM (Spring, Spring MVC, MyBatis) + Gateway + Redis + Kafka + MySQL.

The Gateway performs security filtering and rate limiting, then forwards request parameters, security status, attack flag, and IP information to Kafka, which persists the logs in MySQL.

Optimization Strategy

The original reporting method was a monolithic function of nearly 1,000 lines with vague comments and meaningless variable names (e.g., format1, data1). The refactor focused on the core reporting requirements:

Event status counts (normal, abnormal, total)

Per‑hour statistics for the last 12/24 hours

Top‑5 attacking IPs

Top‑5 accessed interfaces

Security type distribution

Example of the cleaned‑up logic for status counting:

if (pageResultDTO.isPresent()) {
    List<SecurityIncidentDTO> data = pageResultDTO.get().getData();
    Long count = Long.parseLong(pageResultDTO.get().getCount().toString());
    long normalCount = data.stream()
        .filter(log -> "正常".equals(log.getType()))
        .count();
    response.setTotalCount(count);
    response.setNormalCount(normalCount);
    response.setAbNormalCount(count - normalCount);
    // Group by hour for normal events
    Map<String, List<SecurityIncidentDTO>> normalByHour = data.stream()
        .filter(log -> "正常".equals(log.getType()))
        .collect(Collectors.groupingBy(item ->
            new SimpleDateFormat("yyyy-MM-dd HH")
                .format(DateUtil.pars2Calender(item.getTime()).getTime())));
    // Additional grouping for abnormal events, IP aggregation, etc.
}

Data Archiving Logic

To avoid heavy SQL queries, logs are archived by hour and event status. When a new log arrives, the system checks whether a record for the current hour and status exists; if so, it increments the count in Redis, otherwise it inserts a new row. This reduces the amount of data scanned for each report.

/**
 * Archive event details by normal/abnormal status and cache the count in Redis.
 */
@Override
public void handleWebEventStatus(Log log) {
    String siteId = antispiderDetailLog.getSiteId();
    Date curr = new Date();
    DateTime beginOfHour = DateUtil.beginOfHour(curr);
    int eventStatus = log.getAntispiderRule().intValue() == 0 ? 0 : 1;
    String cacheKey = StrUtil.format(RedisConstant.REPORT_WEB_TIME_EXIST,
        siteId, DateUtil.format(beginOfHour, timeFormat), eventStatus);
    String cacheKeyAll = StrUtil.format(RedisConstant.REPORT_WEN_TIME_ALL,
        DateUtil.format(beginOfHour, timeFormat), eventStatus);
    if (redisService.exist(cacheKeyAll)) {
        redisService.increment(cacheKeyAll, 1L);
    } else {
        redisService.setValueByHour(cacheKeyAll, 1, 2L);
    }
    if (redisService.exist(cacheKey)) {
        redisService.increment(cacheKey, 1L);
    } else {
        redisService.setValueByHour(cacheKey, 1, 2L);
    }
}

/**
 * Sync the current hour's data and the previous hour's data to the database.
 */
@Scheduled(cron = "0 0/30 * * * ?")
public void synRedisDataToDB() {
    synchronized (lock) {
        reportWebEventStatusService.synRedisDataToDB();
        reportWebEventTopService.synRedisDataToDB();
        reportWebIpTopService.synRedisDataToDB();
    }
}

Hourly Synchronization of Historical Data

Statistics for the past 23 hours become immutable. A scheduled task runs every hour to copy those stable aggregates into Redis, so queries for a recent 24‑hour window only need to read the current hour from the database while the rest are served from cache.

@Scheduled(cron = "0 0 0/1 * * ?")
public void synAllSiteWebEventDataToRedis() {
    synchronized (lock) {
        synReportWebDataToRedis();
    }
}

Dashboard Assembly with Asynchronous Execution

The final API aggregates four independent sub‑reports (top‑5 events, status counts, top‑5 attacking IPs, top‑5 visited sites) using a thread pool. Each sub‑report is submitted as a Future, allowing parallel execution and later composition.

@Override
public ApiDashboardResponse webDashboardV2(DashboardRequest request) throws Exception {
    ApiDashboardResponse response = new ApiDashboardResponse();
    Future<ReportWebEventTopVo> eventTopFuture = reportTaskExecutor.submit(() ->
        reportWebEventTopService.getWebEventTopVo(request.getSiteId(), request.getTimeType()));
    Future<ReportWebEventStatusVo> statusFuture = reportTaskExecutor.submit(() ->
        reportWebEventStatusService.getReportWebEventStatus(request.getSiteId(), request.getTimeType()));
    Future<ReportWebIpTopVo> ipTopFuture = reportTaskExecutor.submit(() ->
        reportWebIpTopService.getReportWebIpTop5(request.getSiteId(), request.getTimeType()));
    Future<ReportWebSiteTopVo> siteTopFuture = reportTaskExecutor.submit(() ->
        reportWebSiteTopService.getWebSiteTop5Vo(request.getSiteId(), request.getTimeType()));
    // Assemble results
    response.setEventTop(eventTopFuture.get());
    response.setStatus(statusFuture.get());
    response.setIpTop(ipTopFuture.get());
    response.setSiteTop(siteTopFuture.get());
    return response;
}

Results and Conclusion

After introducing hourly aggregation, Redis caching, and asynchronous dashboard assembly, query times for 1.5 million log entries dropped to under one second—a several‑fold improvement over the original SQL‑based approach. The case study shows that, without adding external data‑processing frameworks, careful data archiving and a cache‑first strategy can dramatically accelerate reporting in legacy systems. Further gains are possible by refining lock granularity and extending parallelism.

Performance Comparison

Before optimization, a 1.5 million‑record query took many seconds. After optimization, the same query completes in less than one second.