পারফরম্যান্স টিউনিং আর DBA-র প্রতিদিনের কাজ

১ · অনুমান নয়, পদ্ধতি — মাপুন, খুঁজুন, একটি বদলান, আবার মাপুন

“ডেটাবেস ধীর” — এই অভিযোগ দিয়েই সাধারণত শুরু। তারপর কেউ বলেন RAM বাড়াতে, কেউ বলেন সব কলামে index দিতে, কেউ বলেন MongoDB-তে চলে যাই। এগুলো অনুমান। Performance tuning মানে অনুমান নয়, একটি বৈজ্ঞানিক পদ্ধতি: আগে মাপা, তারপর সবচেয়ে বড় বাধাটি — bottleneck — খুঁজে বের করা, তারপর শুধু সেটিই সারানো।

একটি নিয়ম বিশেষভাবে গুরুত্বপূর্ণ: একবারে একটি জিনিস বদলান। একসাথে index, কনফিগারেশন আর কোয়েরি বদলালে গতি বাড়লেও জানবেন না কোনটি কাজ করেছে — আর কোনটি চুপচাপ ক্ষতি করছে। আরেকটি নিয়ম: শুরুর আগেই লক্ষ্য লিখুন, যেমন “অর্ডারের পাতার p95 ৩০০ ms-এর নিচে”. লক্ষ্য না থাকলে tuning কখনো শেষ হয় না। নিচে একটি বাস্তবধর্মী উদাহরণে চক্রটি দেখুন (সংখ্যাগুলো ব্যাখ্যার জন্য ধরে নেওয়া):

২ · সময় কোথায় যায় — CPU, I/O, অপেক্ষা, network আর অ্যাপ

একটি অনুরোধের সময়কে কয়েকটি বাক্সে ভাগ করা যায়। প্রতিটি বাক্সের লক্ষণ আর মাপার উপায় আলাদা:

শেষ সারিটি সবচেয়ে বেশি উপেক্ষিত। N+1 মানে: একটি কোয়েরিতে N টি সারির তালিকা, তারপর প্রতিটি সারির জন্য আলাদা আরেকটি কোয়েরি — মোট N + 1টি। ORM (পর্ব ৫০) সম্পর্কিত ডেটা অলসভাবে (lazy) আনলে এটি নিঃশব্দে ঘটে। প্রতিটি কোয়েরি একটি round trip: অ্যাপ থেকে সার্ভার, পার্স, চালানো, ফল ফেরত। নিচের Python প্রোগ্রামটি (stdlib sqlite3) সময় না মেপে কতগুলো statement পাঠানো হলো গোনে — set_trace_callback প্রতিটি statement-এ একবার ডাকা হয়। ফল সবসময় একই, তাই তুলনা নির্ভরযোগ্য:

import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("CREATE TABLE cust (id INTEGER PRIMARY KEY);"
                  "CREATE TABLE ord (id INTEGER PRIMARY KEY, cust_id INTEGER, amount INTEGER);"
                  "CREATE INDEX ord_cust ON ord(cust_id);")
con.executemany("INSERT INTO cust VALUES (?)", [(i,) for i in range(1, 101)])
con.executemany("INSERT INTO ord VALUES (?, ?, ?)", [(i, 1 + i % 100, 100 + i * 37 % 900) for i in range(1, 1001)])
sent = []                                   # every SQL statement the app sends
con.set_trace_callback(sent.append)
def n_plus_one():                           # 1 query for the list + 1 per customer
    ids = [r[0] for r in con.execute("SELECT id FROM cust ORDER BY id LIMIT 50")]
    return sum(con.execute("SELECT SUM(amount) FROM ord WHERE cust_id = ?", (c,)).fetchone()[0] for c in ids)
def one_join():                             # the database does the loop
    return con.execute("SELECT SUM(o.amount) FROM (SELECT id FROM cust ORDER BY id LIMIT 50) c "
                       "JOIN ord o ON o.cust_id = c.id").fetchone()[0]
for f in (n_plus_one, one_join):
    sent.clear(); total = f()
    print(f"{f.__name__:10}: {len(sent):3} statements, total = {total}, "
          f"at 2 ms network per trip = {2 * len(sent)} ms")

n_plus_one:  51 statements, total = 273850, at 2 ms network per trip = 102 ms
one_join  :   1 statements, total = 273850, at 2 ms network per trip = 2 ms

৩ · ধীর কোয়েরি খুঁজে বের করা আর plan পড়া

ডেটাবেসের ভেতরে সময় কোথায় যায় জানতে প্রতিটি বড় DBMS-এর নিজস্ব হাতিয়ার আছে। এগুলো এখানে চালানো যায় না; আকৃতিটি দেখুন:

-- postgresql.conf-এ: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements;
SELECT calls, round(total_exec_time) AS total_ms,
       round(mean_exec_time, 1) AS mean_ms, rows,
       left(query, 60) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;          -- সেকেন্ডে; ডিফল্ট 10
SELECT COUNT_STAR AS calls,
       ROUND(SUM_TIMER_WAIT / 1e12, 1) AS total_s,
       LEFT(DIGEST_TEXT, 60) AS query
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

sqlite> .timer on
sqlite> .eqp on
sqlite> .scanstats on

তালিকা সাজান মোট সময় (বার × গড়) ধরে, শুধু গড় দিয়ে নয়। মাসে একবার চলা ২০ সেকেন্ডের রিপোর্টের চেয়ে দিনে পাঁচ লাখবার চলা ৪ ms-এর কোয়েরি সার্ভারের অনেক বেশি সময় খায় (৫,০০,০০০ × ৪ ms = ২,০০০ সেকেন্ড, প্রতিদিন)।

Plan পড়ে সমস্যা ধরা — তিনটি পরিচিত চেহারা

ধীর কোয়েরি পেলে পরের কাজ plan পড়া — পর্ব ৪১-এ EXPLAIN আর পর্ব ৪৩-এ optimizer-এর অনুমান দেখেছেন। tuning-এ বারবার আসে তিনটি চেহারা:

এবার লাইভ। এই পর্বের বেশিরভাগ এডিটর দুটি তৈরি-করা টেবিলে চলে, যা প্রতিবার recursive CTE দিয়ে নতুন করে বানানো হয় (নমুনা ডেটার বাক্সে স্ক্রিপ্টটি দেখতে পাবেন)। cust-এ ২,০০০ গ্রাহক: id, name, city (Dhaka, Chattogram, Khulna, Sylhet — সমান ভাগে)। ord-এ ২০,০০০ অর্ডার: id, cust_id (প্রতি গ্রাহকের ঠিক ১০টি), created (২০২৫ সালের তারিখ, দিনে প্রায় ৫৫টি), status (delivered ৯০%, pending ৮%, returned ২%) আর amount (১০০ থেকে ৪,৯৯৯ টাকা)। প্রথমে একটি ধীর কোয়েরি, তার plan, তারপর একটি index:

-- গ্রাহকের সর্বশেষ ৫টি অর্ডার: আগে
EXPLAIN QUERY PLAN
SELECT id, created, amount FROM ord
WHERE cust_id = 77 ORDER BY created DESC LIMIT 5;
-- একটি পরিবর্তন: (cust_id, created) index
CREATE INDEX ord_cust_created ON ord(cust_id, created);
EXPLAIN QUERY PLAN
SELECT id, created, amount FROM ord
WHERE cust_id = 77 ORDER BY created DESC LIMIT 5;
SELECT id, created, amount FROM ord
WHERE cust_id = 77 ORDER BY created DESC LIMIT 5;
-- শুধু দরকারি কলাম চাইলে: টেবিলে যেতেই হয় না
EXPLAIN QUERY PLAN
SELECT id, created FROM ord WHERE cust_id = 77 ORDER BY created DESC LIMIT 5;

৪ · কোয়েরি নতুন করে লেখা — সবচেয়ে সস্তা সমাধান

কোয়েরি বদলানো সাধারণত সবচেয়ে কম ঝুঁকির সমাধান: সার্ভার বা schema ছোঁয়া লাগে না, আর প্রভাব শুধু সেই কোয়েরিতে। চারটি প্রচলিত rewrite.

ক. Sargable শর্ত — কলামকে ফাংশনে মুড়বেন না

একটি শর্ত sargable (Search ARGument ABLE) যখন index দিয়ে তার মান খোঁজা যায়: কলাম অপারেটর মান আকারে। কলামকে ফাংশন বা হিসাবে মুড়লে B+ tree-র ক্রম আর কাজে লাগে না — index-এ আছে created-এর মান, strftime(…, created)-এর মান নয়:

CREATE INDEX ord_created ON ord(created);
-- non-sargable: কলাম ফাংশনে মোড়া
EXPLAIN QUERY PLAN
SELECT COUNT(*), SUM(amount) FROM ord WHERE strftime('%Y-%m', created) = '2025-03';
-- sargable: একই প্রশ্ন, half-open range
EXPLAIN QUERY PLAN
SELECT COUNT(*), SUM(amount) FROM ord WHERE created >= '2025-03-01' AND created < '2025-04-01';
SELECT COUNT(*), SUM(amount) FROM ord WHERE strftime('%Y-%m', created) = '2025-03';
SELECT COUNT(*), SUM(amount) FROM ord WHERE created >= '2025-03-01' AND created < '2025-04-01';

খ. EXISTS, IN না JOIN?

প্রশ্ন: কোন গ্রাহকদের অন্তত একটি অর্ডার ফেরত এসেছে? তিনভাবে লেখা যায়, কিন্তু তিনটি একই প্রশ্নের উত্তর দেয় না:

CREATE INDEX ord_status ON ord(status, cust_id);
-- JOIN: যত অর্ডার তত সারি
SELECT COUNT(*) AS join_rows, COUNT(DISTINCT c.id) AS customers
FROM cust c JOIN ord o ON o.cust_id = c.id WHERE o.status = 'returned';
-- EXISTS আর IN: প্রতি গ্রাহক একবার
SELECT COUNT(*) AS exists_rows FROM cust c
WHERE EXISTS (SELECT 1 FROM ord o WHERE o.cust_id = c.id AND o.status = 'returned');
SELECT COUNT(*) AS in_rows FROM cust
WHERE id IN (SELECT cust_id FROM ord WHERE status = 'returned');
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM cust c
WHERE EXISTS (SELECT 1 FROM ord o WHERE o.cust_id = c.id AND o.status = 'returned');
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM cust
WHERE id IN (SELECT cust_id FROM ord WHERE status = 'returned');

গ. Pagination — OFFSET নয়, keyset

“পাতা ১,৫০১” দেখাতে LIMIT 10 OFFSET 15000 লিখলে ডেটাবেসকে প্রথম ১৫,০০০টি সারি ক্রমে পড়ে ফেলে দিতে হয় — পাতা যত গভীর, খরচ তত বেশি, O(offset + limit). Keyset pagination আগের পাতার শেষ key মনে রাখে আর সেখান থেকে index দিয়ে সরাসরি শুরু করে, O(log n + limit):

-- OFFSET: ১৫,০০০ সারি পড়ে ফেলে দিয়ে তারপর ১০টি
EXPLAIN QUERY PLAN
SELECT id, created, amount FROM ord ORDER BY id LIMIT 10 OFFSET 15000;
-- keyset: আগের পাতার শেষ id মনে রেখে
EXPLAIN QUERY PLAN
SELECT id, created, amount FROM ord WHERE id > 15000 ORDER BY id LIMIT 10;
SELECT id, created, amount FROM ord ORDER BY id LIMIT 3 OFFSET 15000;
SELECT id, created, amount FROM ord WHERE id > 15000 ORDER BY id LIMIT 3;

SQLite-এর set_progress_handler(f, 10) কোয়েরি চালানোর ভেতরের যন্ত্র (virtual machine) প্রতি ১০টি নির্দেশ পার হলে f ডাকে। ঘড়ি নয়, কাজের পরিমাণ মাপা — তাই প্রতিবার একই সংখ্যা:

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE ord (id INTEGER PRIMARY KEY, amount INTEGER)")
con.executemany("INSERT INTO ord VALUES (?, ?)", [(i, i * 37 % 4900) for i in range(1, 100001)])
steps = 0
def tick():                       # SQLite calls this every 10 virtual-machine steps
    global steps; steps += 1
con.set_progress_handler(tick, 10)

def cost(sql, args):
    global steps; steps = 0
    rows = con.execute(sql, args).fetchall()
    return steps, rows[0][0]

print(" page | OFFSET work | keyset work | same first id")
for page in (1, 10, 100, 1000, 9000):
    skip = (page - 1) * 10        # ids are 1..100000, so the last id of page p-1 is skip
    a, first_a = cost("SELECT id FROM ord ORDER BY id LIMIT 10 OFFSET ?", (skip,))
    b, first_b = cost("SELECT id FROM ord WHERE id > ? ORDER BY id LIMIT 10", (skip,))
    print(f"{page:5} | {a:11} | {b:11} | {first_a == first_b}")

 page | OFFSET work | keyset work | same first id
    1 |           6 |           4 | True
   10 |          24 |           5 | True
  100 |         204 |           5 | True
 1000 |        2004 |           5 | True
 9000 |       18004 |           5 | True

ঘ. Batching আর অপ্রয়োজনীয় কাজ বাদ

• অনেক সারি একবারে: লাখো সারির UPDATE এক transaction-এ করলে লম্বা lock, বিশাল WAL আর replica-য় দেরি; এক-এক সারি আলাদা commit-এ করলে লাখো fsync. মাঝামাঝি: ধরুন ৫,০০০ সারির খণ্ডে, keyset দিয়ে এগিয়ে।
• অস্তিত্ব যাচাইয়ে COUNT(*) নয়: EXISTS বা LIMIT 1 প্রথম মিলেই থামে।
• একই হিসাব বারবার নয়: প্রতিটি পাতায় মোট বিক্রি নতুন করে না গুনে summary বা materialized view (অংশ ৮)।

৫ · Schema-র স্তরে সমাধান — টাইপ, index, partitioning আর আর্কাইভ

ঠিক data type. তারিখকে টেক্সটে '25/03/2025' আকারে রাখলে না ক্রমে সাজানো যায়, না range-এ খোঁজা (পর্ব ১৩)। ছোট টাইপ মানে প্রতি page-এ বেশি সারি, buffer pool-এ বেশি ডেটা: ১০ কোটি সারিতে BIGINT-এর বদলে INT কলামপ্রতি প্রায় ৪০০ MB বাঁচায় (১০^৮ × ৪ বাইট); UUID টেক্সটে ৩৬ বাইট, নিজস্ব টাইপে ১৬. join-এর দুই দিকের টাইপ এক রাখুন, নইলে রূপান্তরে index বাদ পড়তে পারে।

Index — composite, covering আর partial. পর্ব ৪১-এর নিয়মগুলোর সাথে একটি শক্তিশালী কৌশল যোগ করুন: partial index, যা শুধু শর্ত-মানা সারির জন্য। “অপেক্ষমাণ অর্ডারের ড্যাশবোর্ড” মোট সারির ৮% দেখে; সব সারির index বানানোর দরকার নেই:

-- অপেক্ষমাণ অর্ডারের ড্যাশবোর্ড: শুধু pending সারির index
CREATE INDEX ord_pending ON ord(created) WHERE status = 'pending';
EXPLAIN QUERY PLAN
SELECT id, cust_id, created FROM ord
WHERE status = 'pending' AND created >= '2025-12-01' ORDER BY created;
SELECT COUNT(*) AS pending_dec FROM ord
WHERE status = 'pending' AND created >= '2025-12-01';
-- শর্ত মেলে না: partial index কাজে লাগে না
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM ord WHERE status = 'delivered' AND created >= '2025-12-01';

বড় টেবিল তারিখ ধরে ভাগ — partitioning

কোটি সারির লেনদেনের টেবিলে বেশিরভাগ কোয়েরি শেষ কয়েক মাসের, আর পুরোনো ডেটা শুধু জমে থাকে। Partitioning একটি যৌক্তিক টেবিলকে কয়েকটি ভৌত টেবিলে ভাগ করে — যেমন প্রতি মাসে একটি। লাভ তিনটি: কোয়েরির শর্ত দেখে অপ্রাসঙ্গিক ভাগ বাদ দেওয়া (partition pruning), প্রতিটি ভাগের index ছোট, আর পুরোনো ডেটা সরানো মানে লাখো সারির DELETE নয়, একটি পুরো ভাগ বিচ্ছিন্ন করা। PostgreSQL 10 থেকে declarative partitioning (এখানে চালানো যায় না):

CREATE TABLE payments (
  id bigint, merchant_id int, paid_at timestamptz NOT NULL, amount numeric(12,2)
) PARTITION BY RANGE (paid_at);
CREATE TABLE payments_2025_11 PARTITION OF payments
  FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');
CREATE TABLE payments_2025_12 PARTITION OF payments
  FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');
CREATE INDEX ON payments (merchant_id, paid_at);   -- প্রতিটি ভাগে নিজে তৈরি হয়
-- WHERE paid_at >= '2025-12-10' → plan-এ শুধু payments_2025_12
ALTER TABLE payments DETACH PARTITION payments_2025_11;  -- আর্কাইভ: মুহূর্তে

SQLite-এ partitioning নেই, কিন্তু ধারণাটি UNION ALL view দিয়ে অনুকরণ করা যায় — পুরোনো দিনের MySQL আর PostgreSQL-এও (inheritance দিয়ে) এভাবেই করা হতো:

CREATE TABLE ord_h1 (id INTEGER PRIMARY KEY, cust_id INT, created TEXT
  CHECK (created < '2025-07-01'), status TEXT, amount INT);
CREATE TABLE ord_h2 (id INTEGER PRIMARY KEY, cust_id INT, created TEXT
  CHECK (created >= '2025-07-01'), status TEXT, amount INT);
INSERT INTO ord_h1 SELECT * FROM ord WHERE created < '2025-07-01';
INSERT INTO ord_h2 SELECT * FROM ord WHERE created >= '2025-07-01';
CREATE INDEX h1_created ON ord_h1(created);
CREATE INDEX h2_created ON ord_h2(created);
CREATE VIEW ord_all AS SELECT * FROM ord_h1 UNION ALL SELECT * FROM ord_h2;
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM ord_all WHERE created >= '2025-12-01';
SELECT COUNT(*) AS dec_orders FROM ord_all WHERE created >= '2025-12-01';
-- পুরোনো অর্ধেক আর্কাইভ: সারি ধরে DELETE নয়, পুরো টেবিল সরানো
DROP VIEW ord_all;
DROP TABLE ord_h1;
SELECT COUNT(*) AS remaining FROM ord_h2;

আর্কাইভ. partitioning না থাকলেও নীতিটি একই: চালু টেবিলে শুধু চালু ডেটা। দুই বছরের পুরোনো অর্ডার একটি আলাদা টেবিল, আলাদা সস্তা স্টোরেজ বা data warehouse-এ (পর্ব ৫৫) সরান — ছোট খণ্ডে, রাতে, আগে কপি নিশ্চিত করে তারপর মুছে। আইনগতভাবে কত দিন ডেটা রাখতে হয়, তা প্রতিষ্ঠানের নীতি থেকে জেনে নিন।

৬ · কনফিগারেশন আর রক্ষণাবেক্ষণ — memory, connection, VACUUM আর ANALYZE

কনফিগারেশনের মূল কথা

ডিফল্ট সেটিং বানানো হয় ছোট মেশিনেও নিরাপদে চালু হওয়ার জন্য, আপনার সার্ভারের জন্য নয়। তবে কনফিগারেশন কোয়েরি আর schema ঠিক করার পরে — খারাপ plan-কে কোনো সেটিং বাঁচায় না। নিচের সংখ্যাগুলো প্রচলিত শুরুর বিন্দু, নিয়ম নয়; প্রতিটি সংস্করণে ডিফল্ট বদলাতে পারে, তাই নিজের সংস্করণের নথি দেখুন আর নিজের workload-এ মেপে ঠিক করুন।

shared_buffers = 4GB            # ডিফল্ট 128MB; প্রচলিত শুরু RAM-এর ~25%
effective_cache_size = 12GB     # শুধু planner-এর অনুমান: OS cache সহ মোট কত
work_mem = 16MB                 # ডিফল্ট 4MB; প্রতিটি sort/hash-এর জন্য আলাদা!
maintenance_work_mem = 1GB      # VACUUM, CREATE INDEX-এর জন্য
max_connections = 200           # ডিফল্ট 100; বেশি নয় — pooler ব্যবহার করুন
checkpoint_timeout = 15min      # ডিফল্ট 5min
max_wal_size = 4GB              # ডিফল্ট 1GB; কম হলে ঘন ঘন checkpoint
random_page_cost = 1.1          # ডিফল্ট 4.0 (ঘোরানো ডিস্কের যুগের); SSD-তে কম

[mysqld]
innodb_buffer_pool_size = 10G   # ডিফল্ট 128M; নিবেদিত সার্ভারে প্রায়ই RAM-এর 50–75%
innodb_redo_log_capacity = 2G   # 8.0.30 থেকে; আগে innodb_log_file_size
max_connections = 300           # ডিফল্ট 151
slow_query_log = ON
long_query_time = 0.5

• Memory: shared_buffers আর innodb_buffer_pool_size হলো পর্ব ৩৯-এর buffer pool. PostgreSQL OS-এর page cache-ও ব্যবহার করে, তাই তার ভাগ InnoDB-র চেয়ে কম রাখা প্রচলিত।
• work_mem-এর গুণ: এটি প্রতিটি sort বা hash অপারেশনের সীমা, প্রতি connection-এর নয়। ২০০ connection × ৩টি sort × ১৬ MB = প্রায় ৯.৬ GB পর্যন্ত — খারাপ মুহূর্তে মেমরি ফুরিয়ে OS প্রক্রিয়া মেরে ফেলতে পারে। নির্দিষ্ট ভারী রিপোর্টের জন্য শুধু সেই session-এ বাড়ান: SET work_mem = '256MB'.
• Connection আর pooling: PostgreSQL-এ প্রতিটি connection একটি আলাদা OS প্রক্রিয়া, তাই হাজারো connection নিজেই ভার। ২,০০০ জন একসাথে অ্যাপ খুললেও ডেটাবেসে কয়েক ডজন connection যথেষ্ট, যদি মাঝে একটি connection pool থাকে — অ্যাপের ভেতরে (HikariCP-এর মতো), বা আলাদা PgBouncer. pool-এর আকার প্রায়ই CPU core-এর কয়েক গুণের মধ্যে রাখা হয় (পর্ব ৪৯)।
• Checkpoint: পর্ব ৪৮-এ দেখেছেন, checkpoint-এ dirty page ডিস্কে যায়। খুব ঘন ঘন হলে লেখার ঝাপটা আর বেশি full-page write; খুব দেরিতে হলে crash-এর পর recovery লম্বা। checkpoint_completion_target (নতুন সংস্করণে ডিফল্ট 0.9) লেখাকে সময়ে ছড়িয়ে দেয়।

রক্ষণাবেক্ষণ — VACUUM, bloat, ANALYZE আর index rebuild

ডেটাবেস একবার সাজিয়ে রেখে দিলে ধীরে ধীরে এলোমেলো হয়, ঠিক যেমন মুদি দোকানের খাতায় কাটাকাটি জমে। তিনটি নিয়মিত কাজ:

VACUUM আর bloat (PostgreSQL). পর্ব ৪৭-এর MVCC-তে UPDATE পুরোনো সারি মোছে না, নতুন সংস্করণ লেখে; DELETE শুধু চিহ্ন দেয়। পুরোনো সংস্করণগুলো dead tuple. VACUUM সেগুলোর জায়গা পুনর্ব্যবহারের জন্য মুক্ত করে (ফাইল সাধারণত ছোট করে না)। Autovacuum এটি নিজে চালায় — ডিফল্টে মোটামুটি টেবিলের ২০% সারি মরলে (autovacuum_vacuum_scale_factor = 0.2, সাথে ৫০ সারির threshold). বিশাল টেবিলে ২০% মানে কোটি সারি, তাই সেসব টেবিলে আলাদা করে ছোট scale factor দেওয়া প্রচলিত। সবচেয়ে বড় শত্রু লম্বা transaction: একটি ঘণ্টাব্যাপী খোলা transaction থাকলে VACUUM তার পরে মরা সারিও সরাতে পারে না, আর টেবিল ফুলতে থাকে — bloat. VACUUM বন্ধ রাখার আরেক বিপদ transaction ID wraparound, যা শেষ পর্যন্ত ডেটাবেসকে লেখা বন্ধ করতে বাধ্য করে।

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;
SELECT pid, now() - xact_start AS open_for, state, left(query, 40)
FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 5;
ALTER TABLE payments SET (autovacuum_vacuum_scale_factor = 0.02);

ফুলে যাওয়া টেবিল আসলে ছোট করতে VACUUM FULL পুরো টেবিল নতুন করে লেখে, কিন্তু পুরো সময় টেবিল আটকে রাখে (ACCESS EXCLUSIVE lock); চালু সিস্টেমে তাই pg_repack-এর মতো extension বেশি ব্যবহৃত হয়। MySQL InnoDB-তে purge থ্রেড পুরোনো সংস্করণ সরায়; জায়গা ফেরত পেতে OPTIMIZE TABLE টেবিল নতুন করে বানায়।

ANALYZE আর statistics. পর্ব ৪৩-এ দেখেছেন, ভুল statistics মানে ভুল plan. বড় আমদানি বা বিশাল DELETE-এর পর হাতে ANALYZE চালান; autovacuum বা InnoDB-র স্বয়ংক্রিয় হিসাব পরে আসবে, কিন্তু মাঝের সময়ে কোয়েরি পুরোনো ছবিতে চলে।

Index rebuild. বহু UPDATE আর DELETE-এর পর B+ tree-র page অর্ধেক খালি থাকতে পারে — index bloat. PostgreSQL 12 থেকে REINDEX INDEX CONCURRENTLY লেখা না থামিয়ে নতুন index বানায়; SQL Server-এ ALTER INDEX … REBUILD. তবে অন্ধভাবে নিয়মিত সব index rebuild নয় — আগে আকার আর bloat মাপুন। আর ব্যবহার না হওয়া index খুঁজে (PostgreSQL-এ pg_stat_user_indexes.idx_scan = 0, কয়েক সপ্তাহ ধরে) মুছে দেওয়াও রক্ষণাবেক্ষণ — প্রতিটি index প্রতিটি লেখায় দাম নেয়।

৭ · Monitoring আর alerting — গড় নয়, percentile

Monitoring মানে নিয়মিত মাপ জমিয়ে রাখা, যাতে সমস্যার আগে প্রবণতা আর সমস্যার সময় কারণ দেখা যায়। ডেটাবেসের জন্য মূল মাপগুলো:

গড় latency কেন যথেষ্ট নয়? কারণ latency-র বণ্টন প্রায় কখনো সমান নয় — বেশিরভাগ অনুরোধ দ্রুত, অল্প কয়েকটি অনেক ধীর (lock, cache miss, checkpoint-এর মুহূর্ত)। Percentile বলে: p95 = ১২০ ms মানে ৯৫% অনুরোধ ১২০ ms বা তার কম সময়ে শেষ, আর ৫% তার বেশি নেয়। এই লেজটিকে বলে tail latency. নিচে ২০টি অনুরোধে হাতে হিসাব — nearest-rank পদ্ধতিতে: সাজানো তালিকায় p-তম percentile হলো ⌈p/100 × n⌉-তম মান।

এবার একই হিসাব SQL-এ, window function দিয়ে (পর্ব ২৬)। query_log টেবিলে ৩,০০০টি সারি: id, query (কোন ধরনের কোয়েরি — order_page, search, report) আর ms (সময়)। এটি pg_stat_statements বা অ্যাপের নিজস্ব log-এর মতো একটি তৈরি-করা log:

WITH ranked AS (
  SELECT query, ms,
         ROW_NUMBER() OVER (PARTITION BY query ORDER BY ms) AS rn,
         COUNT(*) OVER (PARTITION BY query) AS n
  FROM query_log)
SELECT query, MAX(n) AS calls, ROUND(AVG(ms), 1) AS avg_ms,
       MAX(CASE WHEN rn = (50 * n + 99) / 100 THEN ms END) AS p50,
       MAX(CASE WHEN rn = (95 * n + 99) / 100 THEN ms END) AS p95,
       MAX(CASE WHEN rn = (99 * n + 99) / 100 THEN ms END) AS p99,
       MAX(ms) AS max_ms, SUM(ms) AS total_ms
FROM ranked GROUP BY query ORDER BY total_ms DESC;

report     |  100 |   1385 | 1380 | 1920 | 1980 | 1990 | 138500
search     | 1500 |   49.6 |   49 |   77 |   79 |   79 |  74400
order_page | 1400 |   36.6 |   13 |   58 | 1088 | 1098 |  51230

Cache hit ratio — আর জমা হিসাবের ফাঁদ

PostgreSQL-এর pg_statio_user_tables প্রতিটি টেবিলের জন্য রাখে heap_blks_hit (buffer pool-এ পাওয়া page) আর heap_blks_read (বাইরে থেকে আনা)। hit ratio = hit / (hit + read). কিন্তু এই সংখ্যাগুলো সার্ভার চালুর (বা শেষ reset-এর) পর থেকে জমা — মাসের পুরোনো ভালো দিনগুলো আজকের খারাপ ঘণ্টাকে ঢেকে রাখে। তাই monitoring সিস্টেম নিয়মিত snapshot নেয়, আর দুই snapshot-এর পার্থক্য হিসাব করে। io_snap টেবিলে দুটি snapshot (১০টা আর ১১টা), তিনটি টেবিলের জমা সংখ্যা:

-- শুরু থেকে জমা হিসাব (cumulative)
SELECT relname, ROUND(100.0 * blks_hit / (blks_hit + blks_read), 2) AS hit_pct_total
FROM io_snap WHERE taken = '11:00' ORDER BY relname;
-- শেষ এক ঘণ্টার হিসাব: দুই snapshot-এর পার্থক্য
WITH d AS (
  SELECT taken, relname,
         blks_hit  - LAG(blks_hit)  OVER w AS hit,
         blks_read - LAG(blks_read) OVER w AS rd
  FROM io_snap WINDOW w AS (PARTITION BY relname ORDER BY taken))
SELECT relname, hit, rd, ROUND(100.0 * hit / (hit + rd), 2) AS hit_pct_last_hour
FROM d WHERE hit IS NOT NULL ORDER BY relname;
-- পুরো ডেটাবেসের এক ঘণ্টার অনুপাত
SELECT ROUND(100.0 * SUM(CASE WHEN taken = '11:00' THEN blks_hit ELSE -blks_hit END)
  / SUM(CASE WHEN taken = '11:00' THEN blks_hit + blks_read ELSE -(blks_hit + blks_read) END), 2) AS db_hit_pct
FROM io_snap;

Alert-এর নিয়ম: প্রতিটি alert-এ কাউকে কিছু করতে হবে এমন হওয়া চাই, আর কারণের চেয়ে লক্ষণে (ব্যবহারকারী যা টের পান — latency, error) alert দেওয়া ভালো। অপ্রয়োজনীয় alert-এর ভিড়ে আসলটিও উপেক্ষিত হয়। Google-এর SRE বইয়ে (২০১৬) এর জন্য চারটি “golden signal” প্রস্তাব করা হয়েছে: latency, traffic, errors আর saturation.

৮ · Cache, scaling আর capacity planning

Cache-এর স্তর

সবচেয়ে দ্রুত কোয়েরি হলো যেটি চালাতেই হয় না। একটি অনুরোধ ডেটাবেসে পৌঁছানোর আগে কয়েকটি স্তরে cache থাকতে পারে:

প্রতিটি cache একটি প্রশ্ন আনে: ডেটা বদলালে পুরোনো কপি কখন সরবে (cache invalidation)? পণ্যের ছবিতে পাঁচ মিনিট পুরোনো তথ্য চলে; ব্যাংকের ব্যালান্স বা স্টকে শেষ পিসটি — চলে না। তাই আগে ঠিক করুন কোন ডেটা কতটা পুরোনো হলে ক্ষতি নেই, তারপর cache.

Scale up না scale out?

• Scale up (vertical): একই সার্ভারে বেশি CPU, RAM, দ্রুত SSD. কোড বদলাতে হয় না, consistency অক্ষুণ্ণ — কিন্তু একটি সীমা আছে, আর ওপরের দিকে দাম প্রায়ই দ্রুত বাড়ে। বেশিরভাগ প্রতিষ্ঠানের জন্য এটিই প্রথম, আর প্রায়ই যথেষ্ট, পদক্ষেপ।
• Read replica: পর্ব ৫২-এর replication দিয়ে পড়ার কাজ কয়েকটি কপিতে ছড়ানো — রিপোর্ট, সার্চ, ড্যাশবোর্ড. লেখা তখনো একটি primary-তে। সাবধানতা: replica একটু পিছিয়ে থাকতে পারে, তাই “এইমাত্র যা লিখলাম তা দেখাও” পাতা primary থেকে পড়ুন।
• Sharding (horizontal): ডেটাকেই কয়েকটি সার্ভারে ভাগ — যেমন merchant_id অনুযায়ী। লেখার ক্ষমতাও বাড়ে, কিন্তু বহু-shard join, transaction আর rebalancing কঠিন হয়। বিস্তারিত পর্ব ৫৪-এ; নিয়ম হিসেবে, সহজ পথগুলো শেষ হলে তবেই।

Capacity planning — আগামী বছরের হিসাব আজ

Capacity planning মানে বৃদ্ধির হার মেপে আগেই বলা কবে কোন সম্পদ ফুরোবে। ডিস্কের হিসাব সরল: ১ TB ডিস্কে ৪০০ GB ব্যবহৃত, মাসে ২৫ GB বাড়ছে, alert ৮০%-এ (৮০০ GB) — (৮০০ − ৪০০) / ২৫ = ১৬ মাস। index, WAL আর বড় migration-এর অস্থায়ী জায়গাও হিসাবে রাখুন। Concurrency-র হিসাবে কাজে লাগে Little's law: গড়ে একসাথে চলা কাজ L = λ × W, যেখানে λ আগমনের হার আর W গড় সময়। প্রতি সেকেন্ডে ২,০০০টি কোয়েরি, প্রতিটি গড়ে ৫ ms হলে L = ২,০০০ × ০.০০৫ = ১০ — গড়ে মাত্র ১০টি কোয়েরি একসাথে চলে। তাই কয়েক ডজনের pool-ই যথেষ্ট; কিন্তু W ৫ থেকে ৫০ ms হলে L হয় ১০০ — pool ভরে যায়, অনুরোধ লাইনে দাঁড়ায়। ধীর কোয়েরি সবার জায়গা দখল করে।

৯ · কেস স্টাডি আর DBA-র রুটিন

এবার পুরো পদ্ধতি একসাথে — একটি অনলাইন দোকানে মাসের শেষে কী ঘটতে পারে (PostgreSQL; ঘটনাটি কাল্পনিক, সংখ্যাগুলো ব্যাখ্যার জন্য ধরে নেওয়া)। লক্ষ করুন, লক্ষণ যেখানে, কারণ সেখানে নয়:

DBA-র দৈনিক আর সাপ্তাহিক চেকলিস্ট

DBA (database administrator)-এর কাজের বড় অংশ নাটকীয় নয় — নিয়মিত, একঘেয়ে, আর সেজন্যই জরুরি। একটি প্রচলিত তালিকা:

DBA, developer আর cloud — কার কী দায়

Developer লেখেন কোয়েরি আর migration, আর তাঁর কোড থেকেই আসে বেশিরভাগ ধীর কোয়েরি; তাই plan পড়া আর N+1 চেনা তাঁরই দায়। DBA দেখেন পুরো সিস্টেম: কনফিগারেশন, backup ও recovery, replication, নিরাপত্তা, capacity আর সব দলের সম্মিলিত ভার। ভালো দলে দুইয়ের মাঝে দেয়াল নেই — বড় migration-এর আগে DBA-র পর্যালোচনা, developer-দের হাতে monitoring-এর ড্যাশবোর্ড।

Cloud-এর managed database (Amazon RDS, Google Cloud SQL, Azure Database ইত্যাদি; পর্ব ৬১) হার্ডওয়্যার, patch, স্বয়ংক্রিয় backup, replica আর failover-এর কাজ নিয়ে নেয়। নেয় না: schema আর index ডিজাইন, ধীর কোয়েরি, সঠিক কনফিগারেশন, অনুমতি, restore-এর পরীক্ষা — আর বিল। cloud-এ খারাপ কোয়েরি সরাসরি মাসিক খরচে দেখা দেয়। তাই ভূমিকাটি সরে যাচ্ছে স্বয়ংক্রিয়তা, পরিমাপ আর ডিজাইন-পরামর্শের দিকে।

১০ · AI-এর যুগে performance tuning

“এই কোয়েরি দ্রুত করো” বা “আমার PostgreSQL-এর সেরা কনফিগ দাও” — AI মুহূর্তে উত্তর দেয়। কিন্তু সে আপনার মাপ, plan, ডেটার আকার বা হার্ডওয়্যার দেখেনি। সাধারণ ভুল:

AI-এর দেওয়া tuning চালু করার আগে — চেকলিস্ট

• baseline মাপ আছে (p50/p95, মোট সময়), আর লক্ষ্য লেখা আছে
• একবারে একটি পরিবর্তন; আগে-পরে EXPLAIN আর একই লোডে latency মেপেছি
• নতুন কোয়েরির ফল পুরোনোটির সাথে দুই দিকেই EXCEPT দিয়ে আর COUNT(*) দিয়ে মিলিয়েছি
• কনফিগ বদলের মেমরি হিসাব (connection × অপারেশন × মান) করেছি, staging-এ চালিয়েছি
• নতুন index-এর লেখার খরচ আর জায়গা দেখেছি; চালু সিস্টেমে CONCURRENTLY বা online পদ্ধতি
• ফিরিয়ে নেওয়ার (rollback) পথ জানা আছে