JDBC Wrapper Valkey キャッシュ検証 — ElastiCache Serverless で初回タイムアウトの落とし穴

# 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 ap-northeast-1)
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 ap-northeast-1a --query 'Subnet.SubnetId' --output text --region ap-northeast-1)
SUBNET_C=$(aws ec2 create-subnet --vpc-id $VPC_ID --cidr-block 10.0.2.0/24 \
  --availability-zone ap-northeast-1c --query 'Subnet.SubnetId' --output text --region ap-northeast-1)
SUBNET_D=$(aws ec2 create-subnet --vpc-id $VPC_ID --cidr-block 10.0.3.0/24 \
  --availability-zone ap-northeast-1d --query 'Subnet.SubnetId' --output text --region ap-northeast-1)
 
# IGW（EC2 への SSH 用）
IGW_ID=$(aws ec2 create-internet-gateway --query 'InternetGateway.InternetGatewayId' \
  --output text --region ap-northeast-1)
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 ap-northeast-1)
aws ec2 create-route --route-table-id $RTB_ID --destination-cidr-block 0.0.0.0/0 \
  --gateway-id $IGW_ID --region ap-northeast-1
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 ap-northeast-1)
SG_AURORA=$(aws ec2 create-security-group --group-name jdbc-cache-test-aurora \
  --description "Aurora" --vpc-id $VPC_ID --query 'GroupId' --output text --region ap-northeast-1)
SG_CACHE=$(aws ec2 create-security-group --group-name jdbc-cache-test-cache \
  --description "ElastiCache" --vpc-id $VPC_ID --query 'GroupId' --output text --region ap-northeast-1)
 
MY_IP="$(curl -s https://checkip.amazonaws.com)/32"
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 ap-northeast-1
 
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 ap-northeast-1
 
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 ap-northeast-1
 
aws rds wait db-instance-available --db-instance-identifier jdbc-cache-test-writer --region ap-northeast-1

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 ap-northeast-1

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 ap-northeast-1)
 
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 ap-northeast-1
 
# Java + Maven + psql インストール
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) VALUES
  ('MacBook Pro 14', 'laptop', 248000, 50),
  ('ThinkPad X1 Carbon', 'laptop', 198000, 30),
  ('Dell XPS 15', 'laptop', 178000, 25),
  ('iPhone 16 Pro', 'phone', 159800, 100),
  ('Galaxy S25 Ultra', 'phone', 189800, 80),
  ('Pixel 9 Pro', 'phone', 129800, 60),
  ('iPad Air', 'tablet', 98800, 70),
  ('Galaxy Tab S10', 'tablet', 118800, 40),
  ('AirPods Pro', 'audio', 39800, 200),
  ('Sony WH-1000XM5', 'audio', 44800, 150);
"

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>cachetest</groupId>
    <artifactId>jdbc-cache-test</artifactId>
    <version>1.0</version>
    <properties>
        <maven.compiler.source>21</maven.compiler.source>
        <maven.compiler.target>21</maven.compiler.target>
    </properties>
    <dependencies>
        <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>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>2.0.16</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <version>3.4.2</version>
                <configuration>
                    <archive><manifest>
                        <mainClass>cachetest.QueryCacheTest</mainClass>
                    </manifest></archive>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-dependency-plugin</artifactId>
                <version>3.8.1</version>
                <executions>
                    <execution>
                        <id>copy-deps</id><phase>package</phase>
                        <goals><goal>copy-dependencies</goal></goals>
                        <configuration>
                            <outputDirectory>${project.build.directory}/lib</outputDirectory>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

package cachetest;
 
import java.sql.*;
import java.time.Duration;
import java.time.Instant;
import java.util.Properties;
 
public class QueryCacheTest {
    public static void main(String[] args) throws Exception {
        String dbEndpoint = args[0], dbPassword = args[1], cacheEndpoint = args[2];
 
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", dbPassword);
        props.setProperty("wrapperPlugins", "remoteQueryCache");
        props.setProperty("cacheEndpointAddrRw", cacheEndpoint + ":6379");
        // Serverless は TLS 必須のため cacheUseSSL はデフォルト(true)のまま
 
        String url = "jdbc:aws-wrapper:postgresql://" + dbEndpoint + ":5432/postgres";
        String query = "/* CACHE_PARAM(ttl=60s) */ SELECT * FROM products "
            + "WHERE category = 'laptop' ORDER BY price DESC";
 
        System.out.println("=== Cache Behavior Test (ElastiCache Serverless) ===");
        try (Connection conn = DriverManager.getConnection(url, props)) {
            for (int i = 1; i <= 8; i++) {
                Instant start = Instant.now();
                try (Statement s = conn.createStatement();
                     ResultSet r = s.executeQuery(query)) {
                    int count = 0;
                    while (r.next()) count++;
                    long ms = Duration.between(start, Instant.now()).toMillis();
                    System.out.printf("  Query %d: %d rows, %d ms%n", i, count, ms);
                }
                if (i <= 2) Thread.sleep(1000);
            }
        }
    }
}

# ディレクトリ構造を作成
mkdir -p jdbc-cache-test/src/main/java/cachetest
# pom.xml を jdbc-cache-test/ に、QueryCacheTest.java を jdbc-cache-test/src/main/java/cachetest/ に配置
 
export JAVA_HOME=/usr/lib/jvm/java-21-amazon-corretto
cd jdbc-cache-test && mvn package -q
 
CP="target/jdbc-cache-test-1.0.jar"
for jar in target/lib/*.jar; do CP="$CP:$jar"; done
 
java -cp "$CP" cachetest.QueryCacheTest \
  "<aurora-endpoint>" "<password>" "<cache-serverless-endpoint>"

PGPASSWORD='<password>' psql -h <aurora-endpoint> -U postgres -d postgres -c "
TRUNCATE products;
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;
"

# 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
 
# ElastiCache
aws elasticache delete-serverless-cache --serverless-cache-name jdbc-cache-test --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 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