Building a High‑Performance SpringBoot Log Analyzer for Slow SQL Detection from Scratch

Problem Background

Production MyBatis log files can reach hundreds of megabytes, making manual identification of slow SQL statements impractical. Log formats vary across projects, high‑performance processing is required, and analysis must cover execution time, parameters, and frequency.

Technology Stack

Backend: Spring Boot + Java 8+ (stable, rich ecosystem)

Frontend: Vue.js + Element UI (rapid UI development)

Database: MySQL (lightweight deployment)

Core Algorithm: Regular expressions + multithreaded processing (high performance)

System Architecture

┌─────────────────┐   ┌─────────────────┐   ┌─────────────────┐
│   Frontend UI   │◄──►│   Backend API   │◄──►│    Database     │
│   Vue.js       │   │   Spring Boot   │   │   MySQL         │
│   Element UI   │   │   MyBatis       │   │   log_template  │
└─────────────────┘   └─────────────────┘   └─────────────────┘

Core Modules

Log parsing engine – parses various log formats

Template management – customizable log‑format templates

Performance monitoring – real‑time metrics of processing

Result display – UI with data export capability

Key Implementations

High‑Performance Log Parsing

Multithreaded Batch Processing

List<Future<Void>> futures = new ArrayList<>();
for (int i = 0; i < lines.size(); i += BATCH_SIZE) {
    final int startIndex = i;
    int endIndex = Math.min(i + BATCH_SIZE, lines.size());
    List<String> batch = lines.subList(i, endIndex);
    Future<Void> future = executorService.submit(() -> {
        processBatch(batch, startIndex, sqlExecutionMap, slowSqlResults);
        return null;
    });
    futures.add(future);
}

Smart Cache Management

Map<String, SqlExecutionInfo> sqlExecutionMap = new ConcurrentHashMap<>();

private void cleanupExpiredCache(Map<String, SqlExecutionInfo> sqlExecutionMap) {
    long currentTime = System.currentTimeMillis();
    long expireTime = currentTime - 300000; // 5 minutes
    sqlExecutionMap.entrySet().removeIf(entry -> {
        SqlExecutionInfo info = entry.getValue();
        return info.getStartTime() != null &&
               info.getStartTime().toEpochSecond(java.time.ZoneOffset.UTC) * 1000 < expireTime;
    });
}

Flexible Regular‑Expression Templates

The system supports three main regex templates:

Preparing: matches SQL preparation statements

Parameters: matches SQL parameter lines

Total: matches execution result lines

String preparingRegex = "(?<time>\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}) \\[(?<thread>[^\\]]+)\\] DEBUG (?<class>[\\w\\.]+) - ==>  Preparing: (?<sql>.+)";
String parametersRegex = "(?<time>\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}) \\[(?<thread>[^\\]]+)\\] DEBUG (?<class>[\\w\\.]+) - ==> Parameters: (?<params>.+)";
String totalRegex = "(?<time>\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}) \\[(?<thread>[^\\]]+)\\] TRACE (?<class>[\\w\\.]+) - <==      Total: (?<total>\\d+)";

Supported formats:

MyBatis standard format – most common MyBatis logs

Spring Boot format – supports various timestamp patterns

Nginx access log – enables HTTP request performance analysis

Performance Monitoring System

@Component
public class LogAnalysisPerformanceMonitor {
    private final AtomicLong totalProcessedLines = new AtomicLong(0);
    private final AtomicLong totalProcessedBytes = new AtomicLong(0);
    private final AtomicLong totalProcessingTime = new AtomicLong(0);
    private final AtomicInteger totalSlowSqlCount = new AtomicInteger(0);
    private final AtomicInteger totalFilesProcessed = new AtomicInteger(0);
    private final AtomicLong totalRegexMatches = new AtomicLong(0);
    private final AtomicLong totalRegexAttempts = new AtomicLong(0);
    private final AtomicLong maxCacheSize = new AtomicLong(0);
    private final AtomicLong totalCacheCleanups = new AtomicLong(0);
}

The system generates detailed real‑time reports covering file statistics, processing speed, regex efficiency, cache usage, and error counts.

Frontend Virtual Scrolling

// Virtual scrolling implementation
updateVisibleItems() {
    const start = Math.floor(this.scrollTop / this.itemHeight);
    const end = Math.min(start + this.visibleCount, this.filteredResults.length);
    this.visibleItems = this.filteredResults
        .slice(start, end)
        .map((item, index) => ({
            ...item,
            offset: (start + index) * this.itemHeight,
        }));
}

// Debounced search input
handleSearch() {
    if (this.searchTimeout) {
        clearTimeout(this.searchTimeout);
    }
    this.searchTimeout = setTimeout(() => {
        this.performSearch();
    }, 300);
}

Pitfalls and Solutions

Regex Matching Issues

Initial regexes missed many SQL statements due to slight format differences, inconsistent timestamps, and special‑character escaping.

private String adjustTimeRegex(String regex) {
    // Support timestamps with or without milliseconds
    return regex.replace("(?<time>\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2})",
                         "(?<time>\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}(?::\\d{3}|\\.\\d{3})?)");
}

Memory Overflow

Loading entire log files into memory caused OOM errors, and caches were not cleared promptly.

private static final int BATCH_SIZE = 1000; // batch processing size
private static final int MAX_CACHE_SIZE = 10000; // max cached SQL entries

private List<String> readFileLines(MultipartFile file) throws IOException {
    List<String> lines = new ArrayList<>();
    try (BufferedReader reader = new BufferedReader(new InputStreamReader(file.getInputStream(), StandardCharsets.UTF_8))) {
        String line;
        while ((line = reader.readLine()) != null) {
            lines.add(line);
        }
    }
    return lines;
}

Performance Optimizations

Multithreading: thread‑pool size tuned to CPU cores; batch size balances memory use and throughput

Memory: object reuse, LRU cache with timely cleanup, stream‑based file processing to avoid full in‑memory loads

Algorithm: pre‑filtering to skip irrelevant lines, map‑based indexing for fast lookups, batch DB operations to reduce round‑trips

Actual Results

Processing speed: average 55 000 lines / second

Throughput: 4 MB / second

Memory usage: stable between 500 MB and 2 GB

Accuracy: SQL matching > 95%

Source Code

Repository: https://gitee.com/jack_fly/analysis_log