Untuk artikel kali ini, kita akan membahas tentang contoh implementasi sharding database dengan PHP & MySqli yang praktis dan cukup lengkap untuk dipakai sebagai starting point. Adapun tujuannya adalah:
- Memetakan request ke shard berdasarkan shard key (mis. user_id atau seller_id).
- Memisahkan read dan write (master + replica per shard).
- Menyediakan fallback: skip replica/master yang mati, dan bila master mati coba promote replica sementara (sederhana).
Catatan: ini hanya contoh aplikasi. Untuk produksi gunakan ProxySQL / Orchestrator, healthchecks terjadwal, monitoring, dan strategi promotasi yang lebih kuat.
Apa itu Sharding Database?
- Sharding adalah teknik membagi data besar menjadi potongan-potongan kecil (shard) yang disimpan di beberapa server database berbeda.
- Setiap shard adalah database yang utuh, tetapi hanya menyimpan sebagian subset data.
- Tujuannya: mendistribusikan beban baca/tulis (read/write) ke beberapa server agar tidak semua request menumpuk di satu database.
Ilustrasi Sederhana
Bayangkan punya 1 juta pelanggan. Kalau semua data disimpan di 1 database server, beban sangat berat. Dengan sharding, data bisa dipecah:
- Shard 1 → menyimpan pelanggan dengan ID 1–250k
- Shard 2 → menyimpan pelanggan dengan ID 250k–500k
- Shard 3 → menyimpan pelanggan dengan ID 500k–750k
- Shard 4 → menyimpan pelanggan dengan ID 750k–1,000,000
Jadi, setiap shard hanya menangani sebagian kecil data.
Perbedaan dengan Master–Slave
Master–Slave = satu database master untuk write, beberapa slave untuk read (scaling baca).
Sharding = memecah write dan read ke banyak master (setiap shard punya master sendiri). Artinya sharding bisa scaling write juga, tidak hanya read.
Strategi Sharding
Ada beberapa cara untuk menentukan data masuk shard mana:
- Range-based Sharding. Data dibagi berdasarkan rentang ID. Contoh: user_id 1–1M di Shard A, user_id 1M–2M di Shard B. Kelebihan: mudah dipahami. Kekurangan: shard awal bisa lebih berat dipakai (hotspot).
- Hash-based Sharding. Menggunakan fungsi hash (hash(user_id) % jumlah_shard). Distribusi data lebih merata. Kekurangan: sulit dipindah kalau jumlah shard berubah (kecuali pakai consistent hashing).
- Directory/Lookup Sharding. Ada tabel khusus (shard_map) yang menyimpan mapping ID → shard. Fleksibel, mudah dipindah data antar shard. Tapi butuh lookup tambahan.
- Functional/Entity Sharding. Data dibagi berdasarkan jenis. Contoh: data order di Shard A, data user di Shard B, data produk di Shard C. Biasanya dipakai di sistem microservices.
Tantangan Sharding
- Cross-shard query. Query yang butuh data dari banyak shard (contoh: laporan semua pelanggan) jadi lebih sulit. Solusi: jalankan query ke semua shard (fan-out query) lalu gabungkan hasilnya.
- Join antar shard. Tidak bisa langsung JOIN tabel kalau datanya tersebar di shard berbeda. Solusi: denormalisasi data atau query di aplikasi lalu merge.
- Menambah shard baru. Kalau pakai hash modulo, menambah shard membuat semua data harus di-reshard. Solusi: gunakan consistent hashing atau directory sharding.
- Monitoring & Failover. Tiap shard harus punya backup, failover (master–slave) sendiri. Infrastruktur lebih kompleks.
Contoh E-commerce dengan Sharding
- User & Seller Shard → data user disebar berdasarkan user_id.
- Product Shard → produk per seller disimpan di shard sesuai seller_id.
- Order Shard → order besar sekali, biasanya di-shard pakai order_id atau user_id.
- Payment & Wallet → disimpan di shard terpisah demi konsistensi tinggi.
Dengan cara ini, jutaan user tidak akan overload ke satu database saja.
Contoh Routing Sederhana (PHP)
Misal kita sharding berdasarkan user_id:
<?php
function getShardId($userId) {
// total ada 4 shard
return $userId % 4;
}
$userId = 123456;
$shardId = getShardId($userId); // hasilnya 0..3
// mapping shard ke DB
$shards = [
0 => ['host'=>'db1.local','db'=>'shop_0'],
1 => ['host'=>'db2.local','db'=>'shop_1'],
2 => ['host'=>'db3.local','db'=>'shop_2'],
3 => ['host'=>'db4.local','db'=>'shop_3'],
];
$cfg = $shards[$shardId];
$conn = new mysqli($cfg['host'], 'root', 'password', $cfg['db']);
?>
Struktur File (Saran) – Contoh Implementasi Sharding Database
- ShardConfig.php — konfigurasi shard (array).
- ShardConnectionManager.php — sangat penting: buka / reuse koneksi, health check singkat, routing read/write, failover sederhana.
- ExampleUsage.php — contoh pemakaian (read, write, transaction).
- healthcheck_cron.php (opsional) — script cron untuk update status node ke DB atau file.
ShardConfig.php
<?php
// ShardConfig.php
return [
// contoh 2 shard
// setiap shard punya 'master' + array 'replicas'
'shards' => [
[
'id' => 0,
'name' => 'shard_0',
'master' => ['host'=>'127.0.0.1','port'=>3306,'user'=>'root','pass'=>'root','db'=>'shop_a'],
'replicas' => [
['host'=>'127.0.0.1','port'=>3307,'user'=>'replica','pass'=>'replica','db'=>'shop_a'],
],
],
[
'id' => 1,
'name' => 'shard_1',
'master' => ['host'=>'127.0.0.1','port'=>3308,'user'=>'root','pass'=>'root','db'=>'shop_b'],
'replicas' => [
['host'=>'127.0.0.1','port'=>3309,'user'=>'replica','pass'=>'replica','db'=>'shop_b'],
],
],
],
// fallback shard id jika routing gagal
'fallback_shard' => 0,
// health check options
'ping_timeout_sec' => 2,
];
?>
ShardConnectionManager.php
<?php
// ShardConnectionManager.php
class ShardConnectionManager {
private $shardConfigs;
private $fallbackShard;
private $pingTimeout;
private $connections = []; // cache connections [shardId]['master'|'replica'][index] = mysqli
private $replicaIndex = []; // round-robin index per shard
public function __construct(array $config) {
$this->shardConfigs = $config['shards'];
$this->fallbackShard = $config['fallback_shard'] ?? 0;
$this->pingTimeout = $config['ping_timeout_sec'] ?? 2;
// init replicaIndex
foreach ($this->shardConfigs as $s) {
$this->replicaIndex[$s['id']] = 0;
$this->connections[$s['id']] = ['master' => null, 'replicas' => []];
}
}
/* -------------------------
* Routing: pilih shard id berdasarkan shard key (int/string)
* strategi sederhana: hash modulo jumlah shard
* ------------------------- */
public function getShardIdByKey($key) {
// convert key to integer-like hash
if (is_int($key)) {
$hv = $key;
} else {
$hv = crc32((string)$key);
}
$count = count($this->shardConfigs);
if ($count === 0) return $this->fallbackShard;
return $hv % $count;
}
/* -------------------------
* Dapatkan koneksi MASTER untuk shard
* - memastikan koneksi hidup (ping)
* - jika gagal, coba promote salah satu replica (sederhana)
* ------------------------- */
public function getMasterConnection(int $shardId) {
$cfg = $this->getShardConfig($shardId);
if (!$cfg) {
// fallback
$cfg = $this->getShardConfig($this->fallbackShard);
}
// reuse if exists and alive
if ($this->connections[$shardId]['master'] instanceof mysqli) {
$conn = $this->connections[$shardId]['master'];
if (@$conn->ping()) {
return $conn;
} else {
// close stale
@$conn->close();
$this->connections[$shardId]['master'] = null;
}
}
// try connect to defined master
$m = $cfg['master'];
$conn = $this->connect($m);
if ($conn) {
$this->connections[$shardId]['master'] = $conn;
return $conn;
}
// master down -> try promote first healthy replica
foreach ($cfg['replicas'] as $repCfg) {
$repConn = $this->connect($repCfg);
if ($repConn) {
// NOTE: this is application-level 'promotion' only.
// In production, promotion needs proper MySQL role changes and data safety checks.
$this->connections[$shardId]['master'] = $repConn;
// remove promoted replica from replicas list unless you want it both roles
// but here we keep it simple: keep it as master and remove from replicas cache
$this->connections[$shardId]['replicas'] = array_filter(
$this->connections[$shardId]['replicas'],
function($c) use ($repConn) { return $c !== $repConn; }
);
error_log("Shard $shardId: master down, promoted replica {$repCfg['host']}");
return $repConn;
}
}
// no master nor healthy replica -> try fallback shard master
if ($shardId !== $this->fallbackShard) {
return $this->getMasterConnection($this->fallbackShard);
}
throw new Exception("No available master for shard $shardId and fallback shard.");
}
/* -------------------------
* Dapatkan koneksi REPLICA (read) untuk shard
* - round robin antar replica
* - jika tidak ada replica sehat -> fallback ke master
* ------------------------- */
public function getReplicaConnection(int $shardId) {
$cfg = $this->getShardConfig($shardId);
if (!$cfg) {
$cfg = $this->getShardConfig($this->fallbackShard);
}
// ensure 'replicas' connection array exists
if (!isset($this->connections[$shardId]['replicas'])) {
$this->connections[$shardId]['replicas'] = [];
}
// try existing cached replicas first (round robin)
$replicas = $this->connections[$shardId]['replicas'];
$repCount = count($replicas);
// clean invalid cached replicas
foreach ($replicas as $i => $cachedConn) {
if (!($cachedConn instanceof mysqli) || !@$cachedConn->ping()) {
@$cachedConn->close();
unset($this->connections[$shardId]['replicas'][$i]);
}
}
// reindex
$this->connections[$shardId]['replicas'] = array_values($this->connections[$shardId]['replicas']);
$replicas = $this->connections[$shardId]['replicas'];
$repCount = count($replicas);
if ($repCount > 0) {
$idx = $this->replicaIndex[$shardId] % $repCount;
$this->replicaIndex[$shardId] = ($this->replicaIndex[$shardId] + 1) % max(1, $repCount);
return $replicas[$idx];
}
// no cached replicas -> try to connect to configured replicas
foreach ($cfg['replicas'] as $repCfg) {
$conn = $this->connect($repCfg);
if ($conn) {
$this->connections[$shardId]['replicas'][] = $conn;
}
}
// after connecting attempt, if we have replicas return round robin
$replicas = $this->connections[$shardId]['replicas'];
if (count($replicas) > 0) {
$idx = $this->replicaIndex[$shardId] % count($replicas);
$this->replicaIndex[$shardId] = ($this->replicaIndex[$shardId] + 1) % max(1, count($replicas));
return $replicas[$idx];
}
// fallback: return master connection
return $this->getMasterConnection($shardId);
}
/* -------------------------
* Helper: koneksi MySQLi dasar dengan timeout
* ------------------------- */
private function connect(array $node) {
// suppress warnings with @ and handle manually
$host = $node['host'];
$port = $node['port'] ?? 3306;
$user = $node['user'];
$pass = $node['pass'];
$db = $node['db'];
// Create mysqli with host:port
$mysqli = @new mysqli();
// set options if needed
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, $this->pingTimeout);
$connected = @$mysqli->real_connect($host, $user, $pass, $db, $port);
if (!$connected) {
// can't connect
return null;
}
// ensure ping ok
if (!@$mysqli->ping()) {
@$mysqli->close();
return null;
}
// set charset
$mysqli->set_charset('utf8mb4');
return $mysqli;
}
private function getShardConfig(int $shardId) {
foreach ($this->shardConfigs as $s) {
if ($s['id'] === $shardId) return $s;
}
return null;
}
/* -------------------------
* Close all connections (cleanup)
* ------------------------- */
public function closeAll() {
foreach ($this->connections as $shardId => $arr) {
if (!empty($arr['master']) && $arr['master'] instanceof mysqli) {
@$arr['master']->close();
}
if (!empty($arr['replicas'])) {
foreach ($arr['replicas'] as $c) {
if ($c instanceof mysqli) @$c->close();
}
}
}
$this->connections = [];
}
}
?>
ExampleUsage.php
Contoh: routing berdasarkan seller_id sebagai shard key. Menunjukkan operasi write (order insert) dan read (ambil produk) menggunakan connection manager.
<?php
require_once 'ShardConfig.php';
require_once 'ShardConnectionManager.php';
// load config
$config = include __DIR__ . '/ShardConfig.php';
$manager = new ShardConnectionManager($config);
// contoh fungsi: insert order (WRITE) -> harus ke master shard
function createOrder($manager, $sellerId, $orderData) {
$shardId = $manager->getShardIdByKey($sellerId);
$master = $manager->getMasterConnection($shardId);
// ex: order table in shard DB
$stmt = $master->prepare("INSERT INTO orders (seller_id, user_id, total, created_at) VALUES (?, ?, ?, ?)");
if (!$stmt) throw new Exception("Prepare failed: " . $master->error);
$createdAt = date('Y-m-d H:i:s');
$stmt->bind_param('iids', $orderData['seller_id'], $orderData['user_id'], $orderData['total'], $createdAt);
if (!$stmt->execute()) {
// handle write error (could be dead master) -> throw or retry
throw new Exception("Insert order failed: " . $stmt->error);
}
$orderId = $stmt->insert_id;
$stmt->close();
return $orderId;
}
// contoh fungsi: fetch products (READ) -> gunakan replica (read scaling)
function getProductsForSeller($manager, $sellerId, $limit = 20) {
$shardId = $manager->getShardIdByKey($sellerId);
$conn = $manager->getReplicaConnection($shardId);
// use prepared statement for safety
$stmt = $conn->prepare("SELECT id, seller_id, name, price, stock FROM products WHERE seller_id = ? LIMIT ?");
if (!$stmt) throw new Exception("Prepare failed (read): " . $conn->error);
$stmt->bind_param('ii', $sellerId, $limit);
if (!$stmt->execute()) throw new Exception("Read failed: " . $stmt->error);
$res = $stmt->get_result();
$rows = $res->fetch_all(MYSQLI_ASSOC);
$stmt->close();
return $rows;
}
/* -------------------------
Example run
------------------------- */
try {
// create order (write)
$sellerId = 12345; // contoh key
$orderData = [
'seller_id' => $sellerId,
'user_id' => 9876,
'total' => 150000.00,
];
$orderId = createOrder($manager, $sellerId, $orderData);
echo "Order created with id: $orderId\n";
// read products for this seller (read from replica)
$products = getProductsForSeller($manager, $sellerId, 10);
foreach ($products as $p) {
echo "Product {$p['id']}: {$p['name']} - Rp {$p['price']} (stock: {$p['stock']})\n";
}
} catch (Exception $e) {
echo "Error: " . $e->getMessage();
} finally {
$manager->closeAll();
}
?>
Fitur ekstra yang sebaiknya kamu tambahkan di lingkungan nyata (produksi)
- Periodic health check daemon (cron/worker) yang update DB db_nodes atau file status; jangan hanya rely pada per-request ping.
- Proxy layer (ProxySQL / HAProxy): buat app hanya terhubung ke proxy, biarkan proxy handle master/replica routing dan failover.
- Proper promotion: bila promote replica menjadi master, lakukan promotion di level MySQL (stop slave, change master, ensure GTID/binlog pos). Aplikasi tidak boleh “memaksa” promotion tanpa koordinasi.
- Consistent hashing jika shard count berubah, supaya minim migrasi.
- Global unique ID generator (Snowflake/UUID) agar id unik across shards.
- Cross-shard transaction: hindari; gunakan sagas atau distributed transaction coordinator bila perlu.
- Observability & alerting: metrics untuk replication lag, QPS, error rate.
- Retry & idempotency: untuk operasi write yang mungkin dimulai ulang.
Penjelasan singkat flow & asumsi
- getShardIdByKey() memakai crc32(key) % shard_count. Ini mudah dan deterministic.
- getReplicaConnection() mencoba cached replicas terlebih dahulu, jika tidak ada akan mencoba menghubungi replica dari konfigurasi. Kalau semua replica mati → fallback ke master.
- getMasterConnection() kalau master tidak tersedia mencoba konek ke replica dan menganggap replica itu dipromosikan (aplikasi-level). Ini hanya contoh — promotion yang benar harus dilakukan via orchestrator dan konfigurasi MySQL.
- Semua koneksi menggunakan mysqli dan prepare untuk mencegah SQL injection.
Hal-hal keamanan & performance
- Gunakan user DB dengan privilege minimal (app user hanya SELECT/INSERT/UPDATE/DELETE sesuai kebutuhan).
- Gunakan TLS untuk koneksi DB jarak jauh.
- Set connect_timeout dan read_timeout agar request tak menggantung lama.
- Gunakan connection pooling eksternal (ProxySQL) bila traffic sangat tinggi.