GBase 8c Distributed Cluster Operations: Pitfalls, Performance Tuning, and Quick Fixes

-- Connections
ALTER SYSTEM SET max_connections = 2000;
ALTER SYSTEM SET max_prepared_transactions = 1000;

-- Memory (50–60% of node RAM)
ALTER SYSTEM SET shared_buffers = 8GB;        -- adjust to your node
ALTER SYSTEM SET work_mem = 64MB;
ALTER SYSTEM SET maintenance_work_mem = 1GB;

-- SSD I/O tuning
ALTER SYSTEM SET effective_io_concurrency = 200;
ALTER SYSTEM SET random_page_cost = 1.1;

-- WAL sync
ALTER SYSTEM SET wal_buffers = 64MB;
ALTER SYSTEM SET synchronous_commit = on;

SELECT pg_reload_conf();

-- Add indexes on frequent filter columns
CREATE INDEX idx_user_userid ON "user"(user_id);
CREATE INDEX idx_order_orderno_createtime ON "order"(order_no, create_time);

-- Avoid SELECT *
SELECT user_id, user_name, phone FROM "user" WHERE user_id = 123;

-- Use COPY for bulk loads (10x faster than INSERT)
COPY "user"(user_id, user_name, phone) FROM '/data/user_data.csv' WITH CSV;

-- shared_buffers: 50–60% of RAM
ALTER SYSTEM SET shared_buffers = 8GB;   -- for 16GB node

-- work_mem: smaller for high concurrency, larger for complex queries
ALTER SYSTEM SET work_mem = 32MB;        -- high concurrency
ALTER SYSTEM SET work_mem = 128MB;       -- low concurrency, complex queries

-- Background writer
ALTER SYSTEM SET bgwriter_delay = 200ms;
ALTER SYSTEM SET bgwriter_lru_maxpages = 100;
SELECT pg_reload_conf();

ALTER SYSTEM SET wal_compression = on;
SELECT pg_reload_conf();

-- Check shard distribution
SELECT * FROM pg_dist_shard;
SELECT node_name, count(*) AS shard_count FROM pg_dist_shard GROUP BY node_name;

gs_om -t status --detail   # All nodes must be Normal

top                         # CPU ≤70%, Memory ≤80%
iostat -x 1                 # %util ≤80%

SELECT count(*) FROM pg_stat_activity;
SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';
SELECT * FROM pg_stat_replication;

gs_sync_check
gs_basebackup -C -D /backup/gbase8c/full/... -h ... -p 5432 -U backup_user -v
grep -E "error|warn|fail" /GBase_HOME/log/gbase-xxxx.log

gbase_ctl status -D /data/gbase8c/dn1
grep -E "error|fail" /GBase_HOME/log/gbase-xxxx.log

# Restart the node
gbase_ctl start -D /data/gbase8c/dn1
# Or restart network
systemctl restart network
# Or recover from backup

SELECT pid, query, duration FROM pg_stat_activity WHERE duration > 10000;
EXPLAIN ANALYZE SELECT ...;

SELECT pid, query, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event_type = 'Lock';
SELECT pg_terminate_backend(12345);
ALTER SYSTEM SET lock_timeout = 30000;
SELECT pg_reload_conf();