I'll help you analyze resource utilization and identify cost optimization opportunities across your GKE, Cloud SQL, and ElastiCache infrastructure. Here's a systematic approach:

1. Kubernetes Resource Analysis

Check Current Resource Requests vs Usage

# Get resource requests vs actual usage for all pods
kubectl top pods --all-namespaces --containers | \
  awk 'NR>1 {cpu+=$2; mem+=$3} END {print "Total CPU usage:", cpu"m"; print "Total Memory usage:", mem"Mi"}'

# Compare with resource requests
kubectl get pods --all-namespaces -o custom-columns=\
"NAMESPACE:.metadata.namespace,NAME:.metadata.name,CPU_REQ:.spec.containers[*].resources.requests.cpu,MEM_REQ:.spec.containers[*].resources.requests.memory"

Install and Use Vertical Pod Autoscaler (VPA) for Recommendations

# vpa-recommender.yaml
apiVersion: autoscaling.k8s.io/v1
kind: VerticalPodAutoscaler
metadata:
  name: app-vpa
spec:
  targetRef:
    apiVersion: apps/v1
    kind: Deployment
    name: your-app
  updatePolicy:
    updateMode: "Off"  # Just recommendations, no auto-scaling
  resourcePolicy:
    containerPolicies:
    - containerName: '*'
      controlledResources: ["cpu", "memory"]

# Get VPA recommendations
kubectl get vpa app-vpa -o yaml | grep -A 10 recommendation

Node Utilization Analysis

# Check node resource utilization
kubectl top nodes

# Get detailed node capacity vs allocation
kubectl describe nodes | grep -A 5 "Allocated resources"

# Check for node over-provisioning
kubectl get nodes -o custom-columns="NAME:.metadata.name,CPU_ALLOCATABLE:.status.allocatable.cpu,MEMORY_ALLOCATABLE:.status.allocatable.memory"

2. GKE Cluster Optimization

Enable Cluster Autoscaler and Check Configuration

# Check current autoscaler settings
gcloud container clusters describe CLUSTER_NAME --zone=ZONE | grep -A 10 autoscaling

# Update autoscaler if needed
gcloud container clusters update CLUSTER_NAME \
  --enable-autoscaling \
  --min-nodes 1 \
  --max-nodes 10 \
  --zone=ZONE

Node Pool Analysis

# List node pools and their configurations
gcloud container node-pools list --cluster=CLUSTER_NAME --zone=ZONE

# Check for right-sized machine types
kubectl get nodes -o custom-columns="NAME:.metadata.name,INSTANCE_TYPE:.metadata.labels.beta\.kubernetes\.io/instance-type"

3. Cloud SQL PostgreSQL Analysis

Check Current Usage Metrics

# Get CPU and memory utilization (last 7 days)
gcloud sql instances describe INSTANCE_NAME --format="value(settings.tier)"

# Check database connections
gcloud sql operations list --instance=INSTANCE_NAME --limit=10

Monitor Key Metrics

-- Connect to your PostgreSQL instance and run these queries:

-- Check connection usage
SELECT count(*) as active_connections 
FROM pg_stat_activity 
WHERE state = 'active';

-- Check database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database 
ORDER BY pg_database_size(datname) DESC;

-- Check slow queries
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

Cloud SQL Recommendations Script

#!/bin/bash
# cloudsql-analysis.sh

INSTANCE_NAME="your-instance"
PROJECT_ID="your-project"

echo "=== Cloud SQL Instance Analysis ==="
echo "Current tier:"
gcloud sql instances describe $INSTANCE_NAME --format="value(settings.tier)"

echo -e "\nCPU utilization (avg over 7 days):"
gcloud monitoring timeseries list \
  --filter="resource.type=\"gce_instance\" AND metric.type=\"cloudsql.googleapis.com/database/cpu/utilization\"" \
  --interval-end-time=$(date -u +%Y-%m-%dT%H:%M:%SZ) \
  --interval-start-time=$(date -u -d '7 days ago' +%Y-%m-%dT%H:%M:%SZ) \
  --project=$PROJECT_ID

echo -e "\nMemory utilization:"
gcloud monitoring timeseries list \
  --filter="resource.type=\"gce_instance\" AND metric.type=\"cloudsql.googleapis.com/database/memory/utilization\"" \
  --interval-end-time=$(date -u +%Y-%m-%dT%H:%M:%SZ) \
  --interval-start-time=$(date -u -d '7 days ago' +%Y-%m-%dT%H:%M:%SZ) \
  --project=$PROJECT_ID

4. ElastiCache Redis Analysis

Since you mentioned ElastiCache (AWS service), but you're using GKE (Google Cloud), I'll provide both:

For AWS ElastiCache:

# Check cache cluster utilization
aws elasticache describe-cache-clusters --show-cache-node-info

# Get CloudWatch metrics
aws cloudwatch get-metric-statistics \
  --namespace AWS/ElastiCache \
  --metric-name CPUUtilization \
  --dimensions Name=CacheClusterId,Value=your-cluster-id \
  --statistics Average \
  --start-time $(date -u -d '7 days ago' +%Y-%m-%dT%H:%M:%S) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%S) \
  --period 3600

For Google Cloud Memorystore (Redis):

# Check Redis instance details
gcloud redis instances describe INSTANCE_NAME --region=REGION

# Monitor memory usage
gcloud monitoring timeseries list \
  --filter="resource.type=\"redis_instance\" AND metric.type=\"redis.googleapis.com/stats/memory/usage_ratio\"" \
  --interval-end-time=$(date -u +%Y-%m-%dT%H:%M:%SZ) \
  --interval-start-time=$(date -u -d '7 days ago' +%Y-%m-%dT%H:%M:%SZ)

5. Comprehensive Cost Analysis Script

#!/bin/bash
# resource-optimization.sh

echo "=== Kubernetes Resource Optimization Report ==="
echo "Date: $(date)"
echo

echo "1. Node Utilization:"
kubectl top nodes
echo

echo "2. Pod Resource Requests vs Actual Usage:"
kubectl get pods --all-namespaces -o custom-columns=\
"NAMESPACE:.metadata.namespace,NAME:.metadata.name,CPU_REQ:.spec.containers[*].resources.requests.cpu,MEM_REQ:.spec.containers[*].resources.requests.memory,CPU_ACTUAL:.status.containerStatuses[*].usage.cpu,MEM_ACTUAL:.status.containerStatuses[*].usage.memory"
echo

echo "3. Pods without resource requests (potential waste):"
kubectl get pods --all-namespaces -o json | jq -r '.items[] | select(.spec.containers[].resources.requests == null) | "\(.metadata.namespace)/\(.metadata.name)"'
echo

echo "4. High CPU/Memory consumers:"
kubectl top pods --all-namespaces --sort-by=cpu | head -10
echo
kubectl top pods --all-namespaces --sort-by=memory | head -10

6. Key Optimization Recommendations

Immediate Actions:

1. Set appropriate resource requests/limits:

resources:
  requests:
    cpu: "100m"      # Based on actual usage
    memory: "128Mi"
  limits:
    cpu: "500m"      # 20-30% above requests
    memory: "256Mi"

2. Enable Horizontal Pod Autoscaler:

kubectl autoscale deployment your-app --cpu-percent=70 --min=2 --max=10

3. Use spot/preemptible instances:

gcloud container node-pools create spot-pool \
  --cluster=CLUSTER_NAME \
  --preemptible \
  --machine-type=e2-medium \
  --zone=ZONE

Monitoring Setup:

# prometheus-rules.yaml
groups:
- name: resource-usage
  rules:
  - alert: HighResourceRequest
    expr: |
      (
        sum(kube_pod_container_resource_requests_cpu_cores) /
        sum(kube_node_status_allocatable_cpu_cores)
      ) > 0.8
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "High CPU resource requests"

Run this analysis weekly and adjust resources based on actual usage patterns. Target 70-80% resource utilization for optimal cost-performance balance.