JDBC Wrapper Valkey キャッシュ検証 — Spring Boot + HikariCP で導入する

export AWS_REGION=ap-northeast-1
MY_IP="$(curl -s https://checkip.amazonaws.com)/32"
 
# VPC
VPC_ID=$(aws ec2 create-vpc --cidr-block 10.0.0.0/16 \
  --tag-specifications 'ResourceType=vpc,Tags=[{Key=Name,Value=jdbc-cache-test}]' \
  --query 'Vpc.VpcId' --output text --region $AWS_REGION)
aws ec2 modify-vpc-attribute --enable-dns-hostnames '{"Value":true}' --vpc-id $VPC_ID
 
# サブネット（3 AZ）
SUBNET_A=$(aws ec2 create-subnet --vpc-id $VPC_ID --cidr-block 10.0.1.0/24 \
  --availability-zone ${AWS_REGION}a --query 'Subnet.SubnetId' --output text --region $AWS_REGION)
SUBNET_C=$(aws ec2 create-subnet --vpc-id $VPC_ID --cidr-block 10.0.2.0/24 \
  --availability-zone ${AWS_REGION}c --query 'Subnet.SubnetId' --output text --region $AWS_REGION)
SUBNET_D=$(aws ec2 create-subnet --vpc-id $VPC_ID --cidr-block 10.0.3.0/24 \
  --availability-zone ${AWS_REGION}d --query 'Subnet.SubnetId' --output text --region $AWS_REGION)
 
# IGW（EC2 への SSH 用）
IGW_ID=$(aws ec2 create-internet-gateway --query 'InternetGateway.InternetGatewayId' \
  --output text --region $AWS_REGION)
aws ec2 attach-internet-gateway --internet-gateway-id $IGW_ID --vpc-id $VPC_ID
RTB_ID=$(aws ec2 describe-route-tables --filters "Name=vpc-id,Values=$VPC_ID" \
  --query 'RouteTables[0].RouteTableId' --output text --region $AWS_REGION)
aws ec2 create-route --route-table-id $RTB_ID --destination-cidr-block 0.0.0.0/0 --gateway-id $IGW_ID
aws ec2 modify-subnet-attribute --subnet-id $SUBNET_A --map-public-ip-on-launch
 
# セキュリティグループ
SG_EC2=$(aws ec2 create-security-group --group-name jdbc-cache-test-ec2 \
  --description "EC2" --vpc-id $VPC_ID --query 'GroupId' --output text --region $AWS_REGION)
SG_AURORA=$(aws ec2 create-security-group --group-name jdbc-cache-test-aurora \
  --description "Aurora" --vpc-id $VPC_ID --query 'GroupId' --output text --region $AWS_REGION)
SG_CACHE=$(aws ec2 create-security-group --group-name jdbc-cache-test-cache \
  --description "ElastiCache" --vpc-id $VPC_ID --query 'GroupId' --output text --region $AWS_REGION)
 
aws ec2 authorize-security-group-ingress --group-id $SG_EC2 --protocol tcp --port 22 --cidr $MY_IP
aws ec2 authorize-security-group-ingress --group-id $SG_AURORA --protocol tcp --port 5432 --source-group $SG_EC2
aws ec2 authorize-security-group-ingress --group-id $SG_CACHE --protocol tcp --port 6379 --source-group $SG_EC2

aws rds create-db-subnet-group --db-subnet-group-name jdbc-cache-test \
  --db-subnet-group-description "JDBC cache test" \
  --subnet-ids "$SUBNET_A" "$SUBNET_C" "$SUBNET_D" --region $AWS_REGION
 
aws rds create-db-cluster --db-cluster-identifier jdbc-cache-test \
  --engine aurora-postgresql --engine-version 16.6 \
  --master-username postgres --master-user-password '<password>' \
  --db-subnet-group-name jdbc-cache-test \
  --vpc-security-group-ids $SG_AURORA \
  --serverless-v2-scaling-configuration MinCapacity=0.5,MaxCapacity=2 \
  --storage-encrypted --no-deletion-protection --region $AWS_REGION
 
aws rds create-db-instance --db-instance-identifier jdbc-cache-test-writer \
  --db-cluster-identifier jdbc-cache-test \
  --db-instance-class db.serverless --engine aurora-postgresql --region $AWS_REGION
 
aws rds wait db-instance-available --db-instance-identifier jdbc-cache-test-writer --region $AWS_REGION

aws elasticache create-cache-subnet-group \
  --cache-subnet-group-name jdbc-cache-test \
  --cache-subnet-group-description "JDBC cache test" \
  --subnet-ids "$SUBNET_A" "$SUBNET_C" "$SUBNET_D" --region $AWS_REGION
 
aws elasticache create-replication-group \
  --replication-group-id jdbc-cache-test-tls \
  --replication-group-description "JDBC cache test node-based TLS" \
  --engine valkey \
  --cache-node-type cache.t3.micro \
  --num-cache-clusters 1 \
  --cache-subnet-group-name jdbc-cache-test \
  --security-group-ids $SG_CACHE \
  --transit-encryption-enabled \
  --region $AWS_REGION
 
aws elasticache wait replication-group-available \
  --replication-group-id jdbc-cache-test-tls --region $AWS_REGION

aws elasticache create-serverless-cache \
  --serverless-cache-name jdbc-cache-test \
  --engine valkey \
  --subnet-ids "$SUBNET_A" "$SUBNET_C" "$SUBNET_D" \
  --security-group-ids $SG_CACHE --region $AWS_REGION

AMI_ID=$(aws ec2 describe-images --owners amazon \
  --filters "Name=name,Values=al2023-ami-2023.*-x86_64" "Name=state,Values=available" \
  --query 'sort_by(Images, &CreationDate)[-1].ImageId' --output text --region $AWS_REGION)
 
aws ec2 create-key-pair --key-name jdbc-cache-test --key-type ed25519 \
  --query 'KeyMaterial' --output text > jdbc-cache-test.pem
chmod 600 jdbc-cache-test.pem
 
aws ec2 run-instances --image-id $AMI_ID --instance-type t3.small \
  --key-name jdbc-cache-test --security-group-ids $SG_EC2 \
  --subnet-id $SUBNET_A \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=jdbc-cache-test}]' \
  --region $AWS_REGION
 
ssh ec2-user@<public-ip> 'sudo dnf install -y java-21-amazon-corretto-devel maven postgresql16'

PGPASSWORD='<password>' psql -h <aurora-endpoint> -U postgres -d postgres -c "
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category VARCHAR(50) NOT NULL,
  price NUMERIC(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0
);
INSERT INTO products (name, category, price, stock)
SELECT
  'Product-' || i,
  (ARRAY['laptop','phone','tablet','audio','camera','monitor','keyboard','mouse'])[1 + (i % 8)],
  (random() * 500000 + 1000)::numeric(10,2),
  (random() * 1000)::int
FROM generate_series(1, 1000000) AS i;
ANALYZE products;
"

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
           https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.4.4</version>
    </parent>
    <groupId>cachetest</groupId>
    <artifactId>spring-cache-test</artifactId>
    <version>1.0</version>
    <properties>
        <java.version>21</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>software.amazon.jdbc</groupId>
            <artifactId>aws-advanced-jdbc-wrapper</artifactId>
            <version>3.3.0</version>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.7.8</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-pool2</artifactId>
            <version>2.12.0</version>
        </dependency>
        <dependency>
            <groupId>io.valkey</groupId>
            <artifactId>valkey-glide</artifactId>
            <version>2.3.0</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

package cachetest;
 
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
 
@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

package cachetest;
 
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
 
@RestController
public class ProductController {
    private final JdbcTemplate jdbc;
 
    public ProductController(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }
 
    @GetMapping("/stats")
    public List<Map<String, Object>> categoryStats() {
        return jdbc.queryForList(
            "/* CACHE_PARAM(ttl=300s) */ "
            + "SELECT category, COUNT(*) as cnt, "
            + "AVG(price)::numeric(10,2) as avg_price "
            + "FROM products GROUP BY category "
            + "ORDER BY cnt DESC");
    }
 
    @GetMapping("/stats/nocache")
    public List<Map<String, Object>> categoryStatsNoCache() {
        return jdbc.queryForList(
            "SELECT category, COUNT(*) as cnt, "
            + "AVG(price)::numeric(10,2) as avg_price "
            + "FROM products GROUP BY category "
            + "ORDER BY cnt DESC");
    }
}

package cachetest;
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.stereotype.Component;
 
@Component
public class CacheWarmupRunner implements ApplicationRunner {
    private static final Logger log =
        LoggerFactory.getLogger(CacheWarmupRunner.class);
    private final DataSource dataSource;
 
    public CacheWarmupRunner(DataSource dataSource) {
        this.dataSource = dataSource;
    }
 
    @Override
    public void run(ApplicationArguments args) throws Exception {
        log.info("[Warmup] Sending dummy query to "
            + "initialize cache connection...");
        long start = System.currentTimeMillis();
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(
                 "/* CACHE_PARAM(ttl=1s) */ SELECT 1")) {
            rs.next();
        }
        log.info("[Warmup] Dummy query completed in {} ms",
            System.currentTimeMillis() - start);
        log.info("[Warmup] Waiting 5 seconds for "
            + "CacheMonitor recovery...");
        Thread.sleep(5000);
        log.info("[Warmup] Done. Cache is ready.");
    }
}

export JAVA_HOME=/usr/lib/jvm/java-21-amazon-corretto
cd spring-cache-test && mvn package -q -DskipTests
 
java -jar target/spring-cache-test-1.0.jar

# ElastiCache ノードベース TLS
aws elasticache delete-replication-group \
  --replication-group-id jdbc-cache-test-tls \
  --no-final-snapshot-identifier \
  --region ap-northeast-1
 
# ElastiCache Serverless
aws elasticache delete-serverless-cache \
  --serverless-cache-name jdbc-cache-test \
  --region ap-northeast-1
 
# Aurora
aws rds delete-db-instance --db-instance-identifier jdbc-cache-test-writer \
  --skip-final-snapshot --region ap-northeast-1
aws rds wait db-instance-deleted \
  --db-instance-identifier jdbc-cache-test-writer --region ap-northeast-1
aws rds delete-db-cluster --db-cluster-identifier jdbc-cache-test \
  --skip-final-snapshot --region ap-northeast-1
 
# EC2
aws ec2 terminate-instances --instance-ids <instance-id> --region ap-northeast-1
 
# 削除完了を待ってからネットワークリソースを削除
aws ec2 delete-key-pair --key-name jdbc-cache-test --region ap-northeast-1
aws ec2 delete-security-group --group-id <sg-aurora> --region ap-northeast-1
aws ec2 delete-security-group --group-id <sg-cache> --region ap-northeast-1
aws ec2 delete-security-group --group-id <sg-ec2> --region ap-northeast-1
aws elasticache delete-cache-subnet-group \
  --cache-subnet-group-name jdbc-cache-test --region ap-northeast-1
aws rds delete-db-subnet-group \
  --db-subnet-group-name jdbc-cache-test --region ap-northeast-1
aws ec2 detach-internet-gateway \
  --internet-gateway-id <igw-id> --vpc-id <vpc-id> --region ap-northeast-1
aws ec2 delete-internet-gateway \
  --internet-gateway-id <igw-id> --region ap-northeast-1
aws ec2 delete-subnet --subnet-id <subnet-a> --region ap-northeast-1
aws ec2 delete-subnet --subnet-id <subnet-c> --region ap-northeast-1
aws ec2 delete-subnet --subnet-id <subnet-d> --region ap-northeast-1
aws ec2 delete-vpc --vpc-id <vpc-id> --region ap-northeast-1