Database Sharding dengan Vitess

# Download Vitess binary
VERSION="19.0.0"
wget https://github.com/vitessio/vitess/releases/download/v${VERSION}/vitess-${VERSION}-linux-amd64.tar.gz
tar xzf vitess-${VERSION}-linux-amd64.tar.gz
export PATH="$(pwd)/vitess-${VERSION}/bin:$PATH"

# Verifikasi
vtgate --version
vtctldclient --version

# Setup dengan Docker (recomendado untuk testing)
docker pull vitess/lite:v19.0.0

# Atau menggunakan local examples
git clone https://github.com/vitessio/vitess.git
cd vitess/examples/local

# Mulai cluster 2-shard
./101_initial_cluster.sh

# Ini akan membuat:
# - etcd (topology service)
# - 2 shards (each with 1 primary + 1 replica)
# - vtgate (query router)
# - vtctld (control plane)

# Verifikasi cluster
vtctldclient GetTablets

# Koneksi ke vtgate (port 15991)
mysql -h 127.0.0.1 -P 15991 -u root

# Buat keyspace dan tabel
# (melalui vtgate, otomatis di-propagate ke semua shard)

# Install Vitess Operator
kubectl apply -f https://github.com/vitessio/vitess/releases/download/v19.0.0/operator.yaml

# VitessCluster CRD
# vitess-cluster.yaml
apiVersion: planetscale.com/v2
kind: VitessCluster
metadata:
  name: my-vitess
spec:
  images:
    vtctld: vitess/lite:v19.0.0
    vtgate: vitess/lite:v19.0.0
    vttablet: vitess/lite:v19.0.0
    vtorc: vitess/lite:v19.0.0
  cells:
    - name: zone1
  globalLockserver:
    external:
      etcd:
        hosts:
          - etcd-client:2379
  keyspaces:
    - name: myapp
      partitionings:
        - equal:
            parts: 2  # 2 shards
            shardTemplate:
              databaseInitScriptSecret:
                name: init-script
              tabletPools:
                - cell: zone1
                  type: replica
                  replicas: 2
                  mysqld: {}
                  dataVolumeClaimSpec:
                    accessModes: ["ReadWriteOnce"]
                    resources:
                      requests:
                        storage: 10Gi
  vitessDashboard:
    cells:
      - zone1

# Apply
kubectl apply -f vitess-cluster.yaml

{
  "sharded": true,
  "vindexes": {
    "hash_user_id": {
      "type": "hash"
    },
    "binary_md5_category": {
      "type": "binary_md5"
    },
    "lookup_email": {
      "type": "consistent_lookup",
      "params": {
        "table": "user_email_idx",
        "from": "email",
        "to": "user_id"
      },
      "owner": "users"
    }
  },
  "tables": {
    "users": {
      "column_vindexes": [
        {
          "column": "user_id",
          "name": "hash_user_id"
        },
        {
          "column": "email",
          "name": "lookup_email"
        }
      ],
      "auto_increment": {
        "column": "user_id",
        "column_sequence": "user_seq"
      }
    },
    "orders": {
      "column_vindexes": [
        {
          "column": "user_id",
          "name": "hash_user_id"
        }
      ]
    },
    "products": {
      "column_vindexes": [
        {
          "column": "category",
          "name": "binary_md5_category"
        }
      ]
    }
  }
}

# Apply VSchema menggunakan vtctldclient
vtctldclient ApplyVSchema --vschema-file vschema.json myapp

# Atau dari MySQL shell (vtgate)
mysql> SHOW VSCHEMA TABLES;
mysql> SHOW VSCHEMA VINDEXES;

# Membuat tabel melalui vtgate (otomatis di-propagate ke semua shard)
mysql -h 127.0.0.1 -P 15991 -u root myapp <<EOF
CREATE TABLE users (
    user_id BIGINT NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id),
    UNIQUE KEY idx_email (email)
) ENGINE=InnoDB;

CREATE TABLE orders (
    order_id BIGINT NOT NULL AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    total DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id),
    KEY idx_user_id (user_id)
) ENGINE=InnoDB;
EOF

# Vitess akan otomatis menjalankan DDL di semua shard

-- Single-shard query (SANGAT DIREKOMENDASIKAN)
-- user_id = 1234 di-hash dan di-route ke shard yang tepat
SELECT * FROM users WHERE user_id = 1234;

-- Query dengan sharding key
SELECT * FROM orders WHERE user_id = 1234;
-- Route ke shard yang sama karena user_id adalah sharding key

-- Cross-shard query (Vitess kirim ke SEMUA shard lalu aggregate)
SELECT COUNT(*) FROM users;
-- Mengirim COUNT(*) ke semua shard, lalu sum hasilnya

-- Cross-shard join (lambat!)
SELECT u.name, o.total
FROM users u JOIN orders o ON u.id = o.user_id
WHERE u.user_id = 1234;
-- Route ke satu shard (karena ada filter user_id)

-- TANPA filter sharding key:
SELECT u.name, o.total
FROM users u JOIN orders o ON u.id = o.user_id;
-- SCATTER query: dikirim ke semua shard (LEMBAT!)

-- Transaction dalam satu shard
BEGIN;
UPDATE users SET name = 'Budi Updated' WHERE user_id = 1234;
UPDATE orders SET status = 'completed' WHERE user_id = 1234 AND order_id = 5678;
COMMIT;

-- Cross-shard transaction (2PC, lebih lambat)
BEGIN;
UPDATE users SET name = 'Budi' WHERE user_id = 1234;
UPDATE orders SET status = 'completed' WHERE user_id = 9999 AND order_id = 5678;
COMMIT;

-- Vitess mendukung EXPLAIN untuk melihat query routing
EXPLAIN SELECT * FROM users WHERE user_id = 1234;

-- Output akan menunjukkan:
-- 1. Apakah query single-shard atau scatter
-- 2. Shard mana yang dituju
-- 3. Estimasi rows

-- Membatasi query berbahaya
-- Vitess otomatis membatasi:
-- - FULL SCAN tanpa LIMIT
-- - CROSS JOIN yang sangat besar
-- - Query yang terlalu lama (query timeout)

# Melihat tablet status
vtctldclient GetTablets --keyspace myapp --shard "0"

# Planned Reparenting (graceful, zero downtime)
# Saat maintenance window
vtctldclient PlannedReparentShard myapp/0 --new-primary zone1-100

# Emergency Reparenting (primary sudah mati)
vtctldclient EmergencyReparentShard myapp/0 --new-primary zone1-200

# VTOrc: automatic failover
# VTOrc terus memantau kesehatan primary
# Jika primary mati > threshold, otomatis reparent ke replica terbaik

# Konfigurasi VTOrc
# --prevent-cross-cell-failover=true
# --wait-replicas-timeout=30s
# --topo-global-server-address=etcd:2379

# Monitoring replication lag
vtctldclient GetReplicationStatus zone1-100

# Saat ini: 2 shards (00-80, 80-FF)
# Target: 4 shards (00-40, 40-80, 80-C0, C0-FF)

# Step 1: Buat shard baru
vtctldclient CreateKeyspace --keyspace-type=SHARDED myapp
vtctldclient CreateShard myapp "00-40"
vtctldclient CreateShard myapp "40-80"
vtctldclient CreateShard myapp "80-C0"
vtctldclient CreateShard myapp "C0-FF"

# Step 2: Copy data dari shard lama ke shard baru (vtworker)
# (Automated via Reshard command)
vtctldclient Reshard --source-shards="0" --target-shards="-40,40-80,80-c0,c0-" myapp

# Step 3: Verify data konsisten
vtctldclient VDiff myapp myapp

# Step 4: Cutover (switch traffic ke shard baru)
# Vitess akan meng-update routing rules secara atomik
vtctldclient SwitchTraffic --tablet-type=replica myapp "0"
vtctldclient SwitchTraffic --tablet-type=primary myapp "0"

# Step 5: Cleanup shard lama (setelah yakin)
vtctldclient DeleteShard myapp "00-80"

# Monitoring selama resharding
vtctldclient Workflow --keyspace myapp list

# Merge 4 shards ke 2 shards
# Dari: 00-40, 40-80, 80-C0, C0-FF
# Ke: 00-80, 80-FF

# Prosesnya sama seperti split, tapi terbalik:
vtctldclient Reshard --source-shards="-40,40-80,80-c0,c0-" --target-shards="0,80-" myapp
vtctldclient VDiff myapp myapp
vtctldclient SwitchTraffic --tablet-type=primary myapp "-40,40-80,80-c0,c0-"
vtctldclient DeleteShard myapp "00-40"
vtctldclient DeleteShard myapp "40-80"
vtctldclient DeleteShard myapp "80-C0"
vtctldclient DeleteShard myapp "C0-FF"

# Status semua tablet
vtctldclient GetTablets

# Replication lag per shard
vtctldclient GetReplicationStatus zone1-100

# Vitess Dashboard (vtctld web UI)
# http://localhost:15000

# Metrics endpoint (Prometheus format)
curl http://localhost:15002/metrics  # vtctld
curl http://localhost:15991/debug/vars  # vtgate

# Key metrics:
# - vtgate_*: query routing stats, error rates, latencies
# - vttablet_*: replication lag, query times, tablet state
# - MySQL metrics: connections, buffer pool hit rate

# Grafana dashboard
# Vitess menyediakan Grafana dashboard template di:
# https://github.com/vitessio/vitess/tree/main/tools/monitoring