Mastering MySQL Performance: Connection Pool Tuning, Built‑in Cache, and Application‑Level Caching

1. Connection‑Pool Parameter Tuning

1.1 Role and Principle

The connection pool pre‑creates and manages MySQL connections, avoiding the overhead of TCP handshakes and authentication for each request.

1.2 Core Parameters

Maximum Connections (max_connections)

-- View current max connections
SHOW VARIABLES LIKE 'max_connections';
-- Temporarily set max connections
SET GLOBAL max_connections = 500;
-- Permanent configuration (my.cnf)
-- max_connections = 500

Formula:

max_connections = (available_memory - system_overhead) / memory_per_connection

Each connection consumes roughly 8‑20 MB.

Production recommendation: 200‑500, adjusted to server specs.

Connection‑Timeout Parameters

-- Connection timeout (seconds)
SET GLOBAL wait_timeout = 600;      -- non‑interactive
SET GLOBAL interactive_timeout = 600;  -- interactive
-- View current timeout settings
SHOW VARIABLES LIKE '%timeout%';

Java Connection‑Pool Implementation (HikariCP)

// HikariCP configuration example
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
config.setUsername("root");
config.setPassword("password");
config.setMaximumPoolSize(20);      // max connections
config.setMinimumIdle(10);          // min idle connections
config.setIdleTimeout(300000);      // idle timeout (ms)
config.setConnectionTimeout(30000); // connection timeout (ms)
config.setMaxLifetime(1800000);    // max lifetime (ms)
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
HikariDataSource dataSource = new HikariDataSource(config);

1.3 Monitoring and Diagnosis

-- View current connections
SHOW PROCESSLIST;
SHOW STATUS LIKE 'Threads_%';
-- View active connections (exclude Sleep)
SELECT user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep';
-- Connection statistics
SHOW STATUS LIKE 'Connections%';   -- total connections
SHOW STATUS LIKE 'Max_used_connections'; -- historical max

2. Query Cache and Memory Tables

2.1 MySQL Query Cache

How It Works

The query cache stores the hash of a SELECT statement and its result set in memory; identical queries later return the cached result directly.

-- View query‑cache status
SHOW VARIABLES LIKE 'query_cache%';
-- Enable query cache
SET GLOBAL query_cache_type = 1;   -- 0=off, 1=on, 2=on‑demand
SET GLOBAL query_cache_size = 67108864; -- 64 MB
-- View cache hit rate
SHOW STATUS LIKE 'Qcache%';

Cache Hit‑Rate Calculation

SELECT ROUND(100 * Qcache_hits / (Qcache_hits + Com_select), 2) AS hit_rate
FROM (
    SELECT VARIABLE_VALUE AS Qcache_hits
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Qcache_hits'
) AS hits,
(
    SELECT VARIABLE_VALUE AS Com_select
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Com_select'
) AS selects;

Memory‑Table Features and Limitations

Advantages : data resides entirely in memory for fast read/write; supports hash and B‑tree indexes; uses table‑level lock (suitable for read‑heavy, write‑light scenarios).

Limitations :

Size limited by max_heap_table_size.

Data lost after server restart.

TEXT/BLOB types not supported.

Rows stored with fixed length.

Memory‑Table Configuration

-- Set memory‑table maximum size
SET GLOBAL max_heap_table_size = 134217728;  -- 128 MB
SET GLOBAL tmp_table_size = 67108864;       -- 64 MB
-- Permanent configuration (my.cnf)
-- max_heap_table_size = 256M
-- tmp_table_size = 128M

3. Application‑Layer Cache Design

3.1 Cache Design Patterns

Cache‑Aside (旁路缓存)

public class UserService {
    private Cache cache;
    private UserRepository repository;

    public User getUserById(Long id) {
        // 1. Check cache first
        String cacheKey = "user:" + id;
        User user = cache.get(cacheKey, User.class);
        if (user != null) {
            return user; // cache hit
        }
        // 2. Cache miss – query DB
        user = repository.findById(id);
        if (user != null) {
            // 3. Write to cache (5 min)
            cache.set(cacheKey, user, 300);
        }
        return user;
    }

    public void updateUser(User user) {
        // 1. Update DB
        repository.update(user);
        // 2. Invalidate cache
        cache.delete("user:" + user.getId());
    }
}

Write‑Through (直写模式)

public class WriteThroughCache {
    private Cache cache;
    private Database db;

    public void write(String key, Object value) {
        // 1. Write to cache
        cache.set(key, value);
        // 2. Write to DB
        db.write(key, value);
    }
}

3.2 Multi‑Level Cache Architecture

┌─────────────────┐
│ Client Cache    │ ← Browser/CDN (short TTL)
├─────────────────┤
│ Application Cache│ ← Caffeine/Guava (high‑frequency)
├─────────────────┤
│ Distributed Cache│ ← Redis cluster (shared data)
├─────────────────┤
│ DB Cache         │ ← Buffer Pool / Query Cache
└─────────────────┘

3.3 Cache Strategies and Failure‑Protection

public class CacheStrategy {
    // 1. Cache‑penetration protection (bloom filter / null placeholder)
    public Object getWithPenetrationProtection(String key) {
        Object value = cache.get(key);
        if (value == null) {
            // cache null for 60 s
            cache.set(key, NULL_OBJECT, 60);
            return null;
        }
        return value == NULL_OBJECT ? null : value;
    }

    // 2. Cache‑avalanche protection (distributed lock + random TTL)
    public Object getWithAvalancheProtection(String key) {
        Object value = cache.get(key);
        if (value == null) {
            if (tryLock(key)) {
                try {
                    value = loadFromDB(key);
                    cache.set(key, value, 300 + random.nextInt(30)); // random expiry
                } finally {
                    releaseLock(key);
                }
            } else {
                Thread.sleep(50);
                return getWithAvalancheProtection(key);
            }
        }
        return value;
    }

    // 3. Cache warm‑up for hot data
    public void warmUpCache() {
        List<HotData> hotDataList = db.getHotData();
        for (HotData data : hotDataList) {
            cache.set("hot:" + data.getId(), data, 3600);
        }
    }
}

4. Redis + MySQL Architecture Practice

4.1 Read‑Write Separation

┌─────────────────────────────────────────┐
│               Application               │
├──────────────┬──────────────┬───────────┤
│   Write (master) │   Read (slave) │   Cache (Redis) │
└──────────────┴──────────────┴───────────┘
        │            │            │
        ▼            ▼            ▼
   MySQL cluster   MySQL slave   Redis cluster

4.2 Specific Implementation

Data‑Sync Strategy

public class DataSyncService {
    // 1. Dual‑write strategy
    public void writeData(String key, Object data) {
        // Write to MySQL first
        mysql.write(key, data);
        // Asynchronously write to Redis
        redisTemplate.opsForValue().set(key, data, 300, TimeUnit.SECONDS);
    }

    // 2. Binlog‑based sync (Canal/Debezium)
    public class BinlogSyncProcessor {
        // Listens to MySQL binlog and syncs changes to Redis in real time
    }
}

Hot‑Data Cache Solution

@Component
public class HotspotCacheManager {
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public Product getProductDetail(Long productId) {
        String cacheKey = "product:detail:" + productId;
        // 1. Try Redis
        Product product = (Product) redisTemplate.opsForValue().get(cacheKey);
        if (product != null) {
            // Refresh LRU
            redisTemplate.expire(cacheKey, 3600, TimeUnit.SECONDS);
            return product;
        }
        // 2. Fallback to MySQL
        product = jdbcTemplate.queryForObject(
            "SELECT * FROM products WHERE id = ? AND status = 1",
            new ProductRowMapper(), productId);
        if (product != null) {
            if (product.isHot()) {
                // Hot item: never expire, schedule periodic refresh
                redisTemplate.opsForValue().set(cacheKey, product);
                scheduleRefresh(productId);
            } else {
                // Normal item: random TTL to avoid avalanche
                redisTemplate.opsForValue().set(cacheKey, product, getRandomTTL(1800, 3600), TimeUnit.SECONDS);
            }
        }
        return product;
    }

    private long getRandomTTL(int min, int max) {
        return min + (long) (Math.random() * (max - min));
    }
}

Pagination Query Cache Optimization

public class PaginationCacheService {
    // Cache paginated results
    public List<Product> getProductsByPage(int page, int size, String category) {
        String cacheKey = String.format("products:%s:page:%d:size:%d", category, page, size);
        // Try cache first
        List<Product> products = redisTemplate.opsForList().range(cacheKey, 0, -1);
        if (products != null && !products.isEmpty()) {
            return products;
        }
        // Compute offset
        int offset = (page - 1) * size;
        // Query MySQL
        products = jdbcTemplate.query(
            "SELECT * FROM products WHERE category = ? ORDER BY create_time DESC LIMIT ? OFFSET ?",
            new ProductRowMapper(), category, size, offset);
        if (!products.isEmpty()) {
            // Cache result for 5 min
            redisTemplate.opsForList().rightPushAll(cacheKey, products);
            redisTemplate.expire(cacheKey, 300, TimeUnit.SECONDS);
            // Also cache individual product details
            cacheProductDetails(products);
        }
        return products;
    }
}

5. Best Practices

5.1 Connection‑Pool Best Practices

Set a reasonable max connection count (avoid OOM or starvation).

Monitor connection usage regularly (active, idle, waiting).

Choose an appropriate pool implementation (HikariCP > Druid > Tomcat JDBC > DBCP).

5.2 Cache Best Practices

Select cache granularity that matches business scenarios.

Configure TTL based on data update frequency.

Pre‑warm hot data before traffic spikes.

Combine local cache (e.g., Caffeine) with distributed cache (Redis) for multi‑level caching.

5.3 Redis + MySQL Best Practices

Define cache responsibilities: Redis for hot data, MySQL for persistent full data.

Ensure data consistency via dual‑write or binlog synchronization.

Establish comprehensive monitoring and alerting for cache health.

Plan capacity based on projected business growth.