Spring Boot Java

Performance Optimization — Caching & Query Tuning

Write a Comment on Performance Optimization — Caching & Query Tuning

Identifying Performance Bottlenecks

Before optimizing anything, you need to know what is slow. Premature optimization — fixing things that are not problems — wastes time and adds complexity.

The 80/20 Rule of Performance

In most web applications, 80% of the response time comes from 20% of the code. That 20% is almost always database queries. Network I/O (database round trips), slow queries, and the N+1 problem from Lecture 7 are the usual suspects.

How to Identify Bottlenecks

Step 1: Enable SQL Logging

We already have this from Lecture 5:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

# Show query execution time
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.stat=DEBUG

# Enable Hibernate statistics
spring.jpa.properties.hibernate.generate_statistics=true

With statistics enabled, Hibernate logs a summary after each session:

Session Metrics {
    1200000 nanoseconds spent acquiring 3 JDBC connections;
    800000 nanoseconds spent releasing 3 JDBC connections;
    5600000 nanoseconds spent preparing 8 JDBC statements;
    32000000 nanoseconds spent executing 8 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
}

Step 2: Add Request Timing

Create a simple filter that logs response time for every request:

@Component
public class RequestTimingFilter extends OncePerRequestFilter {

    private static final Logger log = LoggerFactory.getLogger(RequestTimingFilter.class);

    @Override
    protected void doFilterInternal(HttpServletRequest request,
                                     HttpServletResponse response,
                                     FilterChain filterChain)
            throws ServletException, IOException {

        long startTime = System.currentTimeMillis();
        filterChain.doFilter(request, response);
        long duration = System.currentTimeMillis() - startTime;

        if (duration > 500) {
            log.warn("SLOW REQUEST: {} {} — {}ms", request.getMethod(),
                    request.getRequestURI(), duration);
        } else {
            log.debug("Request: {} {} — {}ms", request.getMethod(),
                    request.getRequestURI(), duration);
        }
    }
}

Any request taking more than 500ms is logged as a warning. This immediately surfaces the slow endpoints.

Step 3: Count Queries per Request

The most common performance problem is executing too many queries for a single request. Enable Hibernate statistics or use a library like datasource-proxy to count queries.

A healthy REST API endpoint should execute 1–5 queries. If you see 20+ queries for a single request, you have an N+1 problem (covered in Lecture 7).

Spring Cache Abstraction — @Cacheable, @CacheEvict

The Concept

Caching stores the result of an expensive operation so that subsequent requests for the same data are served from memory instead of hitting the database.

First request:   GET /api/posts/1 → Database query (50ms) → Response
Second request:  GET /api/posts/1 → Cache hit (0.1ms) → Response (same data)

Spring’s Cache Annotations

Spring provides a cache abstraction that works with any cache provider. You annotate methods, and Spring handles the caching logic.

@Service
@Transactional(readOnly = true)
public class PostService {

    // @Cacheable — if the result is already in the cache, return it.
    // If not, execute the method, cache the result, and return it.
    //
    // "posts" is the cache name (a logical group).
    // The default key is the method parameter (id in this case).
    @Cacheable(value = "posts", key = "#id")
    public PostResponse getPostById(Long id) {
        log.debug("Cache MISS — loading post from database: id={}", id);
        Post post = postRepository.findByIdWithAllDetails(id)
                .orElseThrow(() -> new ResourceNotFoundException("Post", "id", id));
        long commentCount = commentRepository.countByPostId(id);
        return postMapper.toResponse(post, commentCount);
    }

    // @CacheEvict — removes an entry from the cache.
    // Called when data is modified to ensure the cache stays in sync.
    @CacheEvict(value = "posts", key = "#id")
    @Transactional
    public PostResponse updatePost(Long id, UpdatePostRequest request) {
        log.debug("Evicting cache for post: id={}", id);
        // ... update logic
    }

    // @CacheEvict — remove after delete
    @CacheEvict(value = "posts", key = "#id")
    @Transactional
    public void deletePost(Long id) {
        // ... delete logic
    }

    // @CacheEvict with allEntries — clear the entire cache.
    // Useful when a bulk operation invalidates many entries.
    @CacheEvict(value = "posts", allEntries = true)
    @Transactional
    public PostResponse publishPost(Long id) {
        // Publishing changes list results, so clear all cached posts
        // ... publish logic
    }
}

@CachePut — Update the Cache

@CachePut always executes the method and updates the cache with the result (unlike @Cacheable, which skips execution on cache hit):

// After updating, put the new value in the cache
@CachePut(value = "posts", key = "#id")
@Transactional
public PostResponse updatePost(Long id, UpdatePostRequest request) {
    // ... update the post
    return postMapper.toResponse(updatedPost);
    // The returned value is stored in the cache
}

Caching List Endpoints

Caching paginated lists is trickier because the cache key must include page, size, sort, and filter parameters:

@Cacheable(value = "postLists",
           key = "'status:' + #status + ':page:' + #pageable.pageNumber + ':size:' + #pageable.pageSize")
public Page<PostResponse> getPosts(String status, Pageable pageable) {
    // ...
}

// When any post changes, evict ALL list caches
@CacheEvict(value = "postLists", allEntries = true)
@Transactional
public PostResponse createPost(CreatePostRequest request) {
    // ...
}

Cache Providers — Simple, Caffeine, Redis

Spring’s cache abstraction is provider-agnostic. You choose the underlying cache implementation.

Simple (ConcurrentMapCache)

A HashMap-based cache built into Spring. No dependencies needed.

spring.cache.type=simple

Pros: Zero setup. Cons: No eviction policy (grows forever), no TTL, not suitable for production.

A high-performance, in-memory cache library with automatic eviction, TTL, and size limits.

Pros: Extremely fast, configurable TTL and max size, production-ready. Cons: Local to one server — not shared across multiple instances.

A distributed, in-memory data store. All server instances share the same cache.

Pros: Shared cache across servers, persistence, pub/sub for cache invalidation. Cons: External dependency, network latency, operational overhead.

Which to Choose?

Scenario Provider
Development, single server, simple setup Caffeine
Multiple servers, microservices Redis
Just trying caching, no extra deps Simple

For our blog API (single server), Caffeine is the best choice.

Setting Up Caffeine Cache

Add Dependency

<dependency>
    <groupId>com.github.ben-manes.caffeine</groupId>
    <artifactId>caffeine</artifactId>
</dependency>

Enable Caching

Add @EnableCaching to your main application class:

@SpringBootApplication
@EnableJpaAuditing
@EnableCaching  // Activates Spring's cache annotations
public class BlogApiApplication {
    public static void main(String[] args) {
        SpringApplication.run(BlogApiApplication.class, args);
    }
}

Configure Caches

In application.properties:

# Use Caffeine as the cache provider
spring.cache.type=caffeine

# Define cache names and their configurations.
# Format: cacheName=maximumSize=N,expireAfterWrite=Ns
spring.cache.caffeine.spec=maximumSize=1000,expireAfterWrite=600s

This applies the same settings to all caches: max 1000 entries, expire 10 minutes after write.

For different caches with different TTLs, configure them programmatically:

package com.example.blogapi.config;

import com.github.benmanes.caffeine.cache.Caffeine;
import org.springframework.cache.CacheManager;
import org.springframework.cache.annotation.EnableCaching;
import org.springframework.cache.caffeine.CaffeineCacheManager;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.concurrent.TimeUnit;

@Configuration
@EnableCaching
public class CacheConfig {

    @Bean
    public CacheManager cacheManager() {
        CaffeineCacheManager cacheManager = new CaffeineCacheManager();

        // Default cache settings
        cacheManager.setCaffeine(Caffeine.newBuilder()
                .maximumSize(500)                          // Max 500 entries
                .expireAfterWrite(10, TimeUnit.MINUTES)    // Expire 10 min after write
                .recordStats());                           // Enable statistics for monitoring

        // Register specific caches
        cacheManager.setCacheNames(java.util.List.of(
                "posts",       // Individual post responses
                "postLists",   // Paginated post lists
                "categories",  // Category list (changes rarely)
                "tags",        // Tag list (changes rarely)
                "userProfiles" // User profile data
        ));

        return cacheManager;
    }
}

Monitoring Cache Performance

With recordStats() enabled, you can expose cache statistics via a REST endpoint or Spring Boot Actuator:

@RestController
@RequestMapping("/api/admin/cache")
@PreAuthorize("hasRole('ADMIN')")
public class CacheController {

    private final CacheManager cacheManager;

    public CacheController(CacheManager cacheManager) {
        this.cacheManager = cacheManager;
    }

    @GetMapping("/stats")
    public Map<String, Object> getCacheStats() {
        Map<String, Object> stats = new HashMap<>();

        cacheManager.getCacheNames().forEach(name -> {
            var cache = (com.github.benmanes.caffeine.cache.Cache<?, ?>)
                    cacheManager.getCache(name).getNativeCache();
            var cacheStats = cache.stats();
            stats.put(name, Map.of(
                    "size", cache.estimatedSize(),
                    "hitCount", cacheStats.hitCount(),
                    "missCount", cacheStats.missCount(),
                    "hitRate", String.format("%.2f%%", cacheStats.hitRate() * 100),
                    "evictionCount", cacheStats.evictionCount()
            ));
        });

        return stats;
    }

    @DeleteMapping
    public Map<String, String> clearAllCaches() {
        cacheManager.getCacheNames().forEach(name ->
                cacheManager.getCache(name).clear());
        return Map.of("message", "All caches cleared");
    }
}

Response example:

{
  "posts": {
    "size": 42,
    "hitCount": 1580,
    "missCount": 42,
    "hitRate": "97.41%",
    "evictionCount": 0
  },
  "categories": {
    "size": 4,
    "hitCount": 320,
    "missCount": 4,
    "hitRate": "98.77%",
    "evictionCount": 0
  }
}

A 97% hit rate means 97% of requests are served from cache without touching the database.

Query Optimization — Analyzing Slow Queries

Enable MariaDB Slow Query Log

-- Connect as root
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- Log queries taking more than 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Or in the MariaDB config (my.cnf):

[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log

Common Query Performance Problems

Problem 1: SELECT * — Loading all columns when you only need a few.

-- ❌ Loads everything including the large "content" TEXT column
SELECT * FROM posts WHERE status = 'PUBLISHED';

-- ✅ Load only what you need (use projections — Lecture 6)
SELECT id, title, slug, excerpt, status, created_at FROM posts WHERE status = 'PUBLISHED';

Problem 2: Missing indexes — Full table scans on frequently filtered columns.

-- Without index on status: scans ALL rows to find matches
SELECT * FROM posts WHERE status = 'PUBLISHED';
-- With index on status: jumps directly to matching rows

Problem 3: N+1 queries — One query for the list, one query per item for related data.

-- N+1: 1 query + 100 author queries
SELECT * FROM posts;
SELECT * FROM users WHERE id = 1;  -- for post 1
SELECT * FROM users WHERE id = 2;  -- for post 2
-- ... 98 more

-- Fixed with JOIN FETCH: 1 query total
SELECT p.*, u.* FROM posts p JOIN users u ON p.author_id = u.id;

Problem 4: Large offsets — Pagination with high page numbers.

-- ❌ Slow: MariaDB reads and discards 100,000 rows to find the last 10
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 100000;

-- ✅ Fast: Use keyset pagination (cursor-based)
SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 10;

Using MariaDB EXPLAIN

EXPLAIN shows how MariaDB executes a query — which indexes it uses, how many rows it examines, and what strategy it chooses. This is your primary tool for query optimization.

Basic Usage

EXPLAIN SELECT * FROM posts WHERE status = 'PUBLISHED' ORDER BY created_at DESC;

Output:

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | posts | ref  | idx_posts_status| idx_posts_status| 82      | const |   45 | Using where; Using filesort |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------------+

Reading the EXPLAIN Output

The most important columns:

type — How MariaDB accesses the table. From best to worst:

Type Meaning Performance
system / const Single row lookup by primary key Excellent
eq_ref One row per join (unique index) Excellent
ref Multiple rows from an index Good
range Index range scan (BETWEEN, <, >) Good
index Full index scan Moderate
ALL Full table scan (no index used) Bad — needs optimization

key — Which index MariaDB actually uses. NULL means no index — the entire table is scanned.

rows — Estimated number of rows MariaDB examines. Lower is better.

Extra — Additional information:

  • Using index — Data is read directly from the index (fastest)
  • Using where — Rows are filtered after reading
  • Using filesort — MariaDB sorts results in memory (slow for large datasets)
  • Using temporary — A temp table is created (slow)

EXPLAIN Examples

-- Good: uses idx_posts_status index, examines only 45 rows
EXPLAIN SELECT * FROM posts WHERE status = 'PUBLISHED';
-- type=ref, key=idx_posts_status, rows=45

-- Bad: no index on category_id → full table scan
EXPLAIN SELECT * FROM posts WHERE category_id = 3;
-- type=ALL, key=NULL, rows=10000

-- Fix: add an index
CREATE INDEX idx_posts_category_id ON posts(category_id);

-- Now: uses the new index
EXPLAIN SELECT * FROM posts WHERE category_id = 3;
-- type=ref, key=idx_posts_category_id, rows=15

EXPLAIN ANALYZE (MariaDB 10.1+)

EXPLAIN ANALYZE actually runs the query and shows real execution time:

EXPLAIN ANALYZE SELECT p.*, u.full_name
FROM posts p
JOIN users u ON p.author_id = u.id
WHERE p.status = 'PUBLISHED'
ORDER BY p.created_at DESC
LIMIT 10;

This gives actual timing instead of estimates — invaluable for diagnosing real performance issues.

Database Indexing Strategies

Index Decisions for Our Blog

Based on our query patterns, here are the indexes we need:

-- Already created in our migrations:
-- Primary keys (automatic): users.id, posts.id, comments.id, etc.
-- Unique indexes: users.username, users.email, posts.slug
-- Foreign keys: posts.author_id, posts.category_id, comments.post_id

-- Additional indexes for query performance:
-- Posts filtered by status (most common query)
CREATE INDEX idx_posts_status ON posts(status);

-- Posts sorted by published date (for "recent posts")
CREATE INDEX idx_posts_published_at ON posts(published_at);

-- Composite index for the most common query: published posts sorted by date
CREATE INDEX idx_posts_status_published ON posts(status, published_at);

-- Comments counted per post (comment_count calculation)
CREATE INDEX idx_comments_post_id ON comments(post_id);

-- Full-text search on title and content
ALTER TABLE posts ADD FULLTEXT INDEX idx_posts_fulltext (title, content);

Composite Index Ordering

The order of columns in a composite index matters. Put the most selective (most filtering) column first:

-- ✅ Good: status filters first (3 values), then published_at sorts within each status
CREATE INDEX idx_posts_status_published ON posts(status, published_at);

-- Supports these queries efficiently:
SELECT * FROM posts WHERE status = 'PUBLISHED' ORDER BY published_at DESC;
SELECT * FROM posts WHERE status = 'PUBLISHED';
SELECT * FROM posts WHERE status = 'DRAFT' AND published_at > '2024-01-01';

-- Does NOT help with:
SELECT * FROM posts WHERE published_at > '2024-01-01';  -- Cannot skip first column

When NOT to Index

  • Columns with very low cardinality (e.g., is_active with only TRUE/FALSE)
  • Columns that are rarely used in WHERE, ORDER BY, or JOIN
  • Tables with very few rows (full scan is fast enough)
  • Columns that are frequently updated (index maintenance overhead)

Connection Pooling with HikariCP

Why Connection Pooling Matters

Creating a database connection is expensive — TCP handshake, authentication, SSL negotiation. Without a pool, every query opens a new connection and closes it afterward. With a pool, connections are reused.

HikariCP Configuration

HikariCP is Spring Boot’s default connection pool. It is already configured, but let us tune it:

# ============================================
# HikariCP Connection Pool
# ============================================

# Maximum number of connections in the pool.
# Rule of thumb: connections = (2 * CPU cores) + effective_disk_spindles
# For a 4-core server with SSD: (2 * 4) + 1 = 9, round to 10
spring.datasource.hikari.maximum-pool-size=10

# Minimum number of idle connections kept in the pool.
# For steady traffic, set equal to maximum-pool-size.
# For bursty traffic, set lower (e.g., 5).
spring.datasource.hikari.minimum-idle=5

# Maximum time (ms) to wait for a connection from the pool.
# If all connections are busy for this long, an exception is thrown.
spring.datasource.hikari.connection-timeout=30000

# Maximum time (ms) a connection can sit idle in the pool.
# After this, idle connections are removed (down to minimum-idle).
spring.datasource.hikari.idle-timeout=600000

# Maximum lifetime (ms) of a connection in the pool.
# Connections older than this are gracefully retired and replaced.
# Set slightly lower than MariaDB's wait_timeout (default 28800s = 8h).
spring.datasource.hikari.max-lifetime=1800000

# Keepalive interval (ms) — sends a test query to keep connections alive.
spring.datasource.hikari.keepalive-time=300000

# Name for logging and JMX
spring.datasource.hikari.pool-name=BlogDBPool

Common Pool Sizing Mistakes

Too many connections: 50 connections from the app + 50 from another service = 100 connections fighting for MariaDB resources. More connections ≠ more performance. The optimal pool size is usually 5–20.

Too few connections: If your app handles 100 concurrent requests but has only 5 connections, 95 requests wait in the connection queue. Watch for connection-timeout exceptions.

Monitoring the pool: Enable HikariCP metrics via Spring Boot Actuator:

management.endpoints.web.exposure.include=health,metrics

Then query http://localhost:8080/actuator/metrics/hikaricp.connections.active to see real-time pool usage.

Batch Operations with JPA

The Problem with Individual Saves

// ❌ Bad: 100 individual INSERT statements (100 round trips to the database)
for (Tag tag : tags) {
    tagRepository.save(tag);
}

Enable Batch Inserts

# Batch multiple INSERT/UPDATE statements into one network call
spring.jpa.properties.hibernate.jdbc.batch_size=25

# Order statements to maximize batching efficiency
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true

Now Hibernate groups 25 INSERT statements into a single batch:

// ✅ Good: 100 inserts are sent in 4 batches of 25 (4 round trips instead of 100)
tagRepository.saveAll(tags);

Important: Batch Inserts and IDENTITY Generation

With GenerationType.IDENTITY (which we use for MariaDB), Hibernate cannot batch INSERT statements because it needs the auto-generated ID back from each insert to set on the entity.

To enable true batch inserts, you would need to switch to GenerationType.SEQUENCE or GenerationType.TABLE — but MariaDB does not natively support sequences in the same way PostgreSQL does.

Practical impact: For most web applications, individual inserts are fast enough. Batch optimization matters for bulk operations (importing 10,000 records). For those cases, consider using native batch inserts:

@Repository
public class BulkInsertRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // Native batch insert bypasses JPA entirely — much faster for bulk operations
    public void bulkInsertTags(List<Tag> tags) {
        String sql = "INSERT INTO tags (name, slug, created_at) VALUES (?, ?, NOW())";

        jdbcTemplate.batchUpdate(sql, tags, 100, (ps, tag) -> {
            ps.setString(1, tag.getName());
            ps.setString(2, tag.getSlug());
        });
    }
}

Batch Deletes

// ❌ Slow: loads each entity, then deletes one by one
commentRepository.deleteAll(comments);  // N SELECT + N DELETE

// ✅ Fast: single DELETE statement
@Modifying
@Query("DELETE FROM Comment c WHERE c.post.id = :postId")
void deleteByPostId(@Param("postId") Long postId);

// ✅ Also fast: deleteAllInBatch
commentRepository.deleteAllInBatch(comments);  // Single DELETE ... WHERE id IN (...)

Hands-on: Add Caching to Blog API & Optimize Queries

Step 1: Add Caching to PostService

@Service
@Transactional(readOnly = true)
public class PostService {

    private static final Logger log = LoggerFactory.getLogger(PostService.class);

    @Cacheable(value = "posts", key = "#id")
    public PostResponse getPostById(Long id) {
        log.debug("Cache MISS for post id={}", id);
        Post post = postRepository.findByIdWithAllDetails(id)
                .orElseThrow(() -> new ResourceNotFoundException("Post", "id", id));
        long commentCount = commentRepository.countByPostId(id);
        return postMapper.toResponse(post, commentCount);
    }

    @Cacheable(value = "posts", key = "'slug:' + #slug")
    public PostResponse getPostBySlug(String slug) {
        log.debug("Cache MISS for post slug={}", slug);
        Post post = postRepository.findBySlug(slug)
                .orElseThrow(() -> new ResourceNotFoundException("Post", "slug", slug));
        long commentCount = commentRepository.countByPostId(post.getId());
        return postMapper.toResponse(post, commentCount);
    }

    @CacheEvict(value = {"posts", "postLists"}, allEntries = true)
    @Transactional
    public PostResponse createPost(CreatePostRequest request) {
        log.info("Creating post — clearing cache");
        // ... existing create logic
    }

    @Caching(evict = {
        @CacheEvict(value = "posts", key = "#id"),
        @CacheEvict(value = "postLists", allEntries = true)
    })
    @Transactional
    public PostResponse updatePost(Long id, UpdatePostRequest request) {
        log.info("Updating post id={} — evicting cache", id);
        // ... existing update logic
    }

    @Caching(evict = {
        @CacheEvict(value = "posts", key = "#id"),
        @CacheEvict(value = "postLists", allEntries = true)
    })
    @Transactional
    public void deletePost(Long id) {
        log.info("Deleting post id={} — evicting cache", id);
        // ... existing delete logic
    }
}

Step 2: Add Performance Indexes

File: V13__add_performance_indexes.sql

-- V13: Add indexes for query performance optimization

-- Composite index for the most common query pattern:
-- "Get published posts sorted by date"
CREATE INDEX IF NOT EXISTS idx_posts_status_published_at
    ON posts(status, published_at DESC);

-- Composite index for author's posts by status
CREATE INDEX IF NOT EXISTS idx_posts_author_status
    ON posts(author_id, status);

-- Index for comment counting per post (very frequent operation)
-- post_id index should already exist from foreign key, but let's be explicit
CREATE INDEX IF NOT EXISTS idx_comments_post_created
    ON comments(post_id, created_at DESC);

-- Full-text index for search functionality
ALTER TABLE posts ADD FULLTEXT INDEX IF NOT EXISTS idx_posts_fulltext (title, content);

Step 3: Verify Optimization

# First request — cache miss, hits the database
curl http://localhost:8080/api/posts/1
# Console: "Cache MISS for post id=1"
# Console: Hibernate SQL query logged

# Second request — cache hit, no database query
curl http://localhost:8080/api/posts/1
# Console: (nothing — served from cache)

# Check cache stats (admin only)
curl -H "Authorization: Bearer $ADMIN_TOKEN" \
  http://localhost:8080/api/admin/cache/stats

Step 4: Analyze Queries with EXPLAIN

# Connect to MariaDB
docker exec -it blogdb mariadb -u bloguser -pblogpass blogdb

-- Check the most common query
EXPLAIN SELECT p.*, u.username, u.full_name
FROM posts p
JOIN users u ON p.author_id = u.id
WHERE p.status = 'PUBLISHED'
ORDER BY p.published_at DESC
LIMIT 10;

-- Verify indexes are used (type should be 'ref', not 'ALL')
-- key should show idx_posts_status_published_at

Step 5: Exercises

  1. Cache categories and tags: Add @Cacheable to getAllCategories() and getAllTags(). These change rarely so they are perfect cache candidates. Set a longer TTL (30 minutes).
  2. Implement keyset pagination: For the post list endpoint, add an alternative pagination mode that uses WHERE id < :lastId ORDER BY id DESC LIMIT :size instead of OFFSET. Compare performance with EXPLAIN on a table with 100,000 rows.
  3. Monitor cache hit rates: Call the cache stats endpoint after using the API for a while. Calculate the percentage of requests served from cache vs database. Aim for a 90%+ hit rate on frequently accessed posts.
  4. Profile with EXPLAIN ANALYZE: Run EXPLAIN ANALYZE on the search query (searchByKeyword). Compare execution time with and without the full-text index.
  5. Tune HikariCP: Set maximum-pool-size=3 and hit the API with 10 concurrent requests (use a tool like ab or wrk). Observe the connection timeout errors. Then increase to 10 and compare.

Summary

This lecture transformed your blog API from "works" to "works fast":

  • Identifying bottlenecks: Enable SQL logging, add request timing filters, count queries per request. Optimize what is slow, not what you guess is slow.
  • Spring Cache: @Cacheable returns cached results on cache hit, @CacheEvict removes stale entries on data modification, @CachePut updates the cache with new values.
  • Caffeine: High-performance in-memory cache with TTL, max size, and statistics. Configure per-cache TTLs for different data volatility.
  • Query optimization: Avoid SELECT *, fix N+1 queries with JOIN FETCH, use keyset pagination for large offsets, analyze every slow query with EXPLAIN.
  • EXPLAIN: Shows index usage (type column), rows examined, and execution strategy. ALL = full table scan (bad), ref = index lookup (good), const = primary key lookup (best).
  • Indexing: Index columns used in WHERE, ORDER BY, and JOIN. Composite indexes: most selective column first. Do not over-index — each index slows writes.
  • HikariCP: Default connection pool. Optimal size ≈ (2 × CPU cores) + 1. Monitor active connections via Actuator.
  • Batch operations: saveAll() with hibernate.jdbc.batch_size. For bulk inserts, use JdbcTemplate.batchUpdate(). For bulk deletes, use @Query DELETE or deleteAllInBatch().

What is Next

In Lecture 17, we will cover Deployment & Production Readiness — building a production JAR, Docker containerization, Docker Compose for Spring Boot + MariaDB, Spring Boot Actuator for health checks, and CI/CD pipeline basics.

Quick Reference

Concept Description
@EnableCaching Activates Spring's cache abstraction
@Cacheable Return cached result on hit; execute + cache on miss
@CacheEvict Remove entries from cache
@CachePut Always execute method and update cache
@Caching Combine multiple cache operations
Caffeine High-performance in-memory cache library
TTL (expireAfterWrite) Time-to-live — cache entry expires after N seconds
maximumSize Maximum number of entries before eviction
recordStats() Enable hit/miss statistics for monitoring
EXPLAIN Show how MariaDB executes a query
EXPLAIN ANALYZE Run query and show actual execution time
type=ALL Full table scan — missing index
type=ref Index lookup — efficient
type=const Primary key lookup — fastest
Composite index Multi-column index; order matters
HikariCP Default Spring Boot connection pool
maximum-pool-size Max concurrent database connections
connection-timeout Max wait time for a connection
hibernate.jdbc.batch_size Number of statements per JDBC batch
JdbcTemplate.batchUpdate() Native JDBC batch insert (bypasses JPA)
deleteAllInBatch() Single DELETE SQL instead of N individual deletes
Keyset pagination Use WHERE id > lastId instead of OFFSET for large datasets
Tagged In

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *