【转载“GBase 8s数据库”公众号】GBase 8s性能优化实战攻略

VPCLASS cpu,num=64,noage -- num 设置为 CPU 核心数

MULTIPROCESSOR 1

NETTYPE soctcp,10,300,NET -- 10 个轮询线程，每线程 300 连接

BUFFERPOOL size=16K,buffers=1000000,lrus=32,lru_min_dirty=50,lru_max_dirty=60

SHMVIRTSIZE 2048000 -- 虚拟内存初始大小（KB），约 2GB SHMADD 1024000 -- 自动扩展步长（KB），约 1GB

LOCKS 10000000 -- 初始锁数量，高并发场景设大值

DEF_TABLE_LOCKMODE row

-- 查看表的统计信息
SELECT tabname, nrows, npused, ustlowts
FROM systables WHERE tabname = 'your_table';
-- 查看索引统计信息
SELECT
    i.idxname,
    TRIM(CASE WHEN ix.part1  = 0 THEN '' ELSE c1.colname  END) ||
    TRIM(CASE WHEN ix.part2  = 0 THEN '' ELSE ',' || c2.colname  END) ||
    TRIM(CASE WHEN ix.part3  = 0 THEN '' ELSE ',' || c3.colname  END) ||
    TRIM(CASE WHEN ix.part4  = 0 THEN '' ELSE ',' || c4.colname  END) ||
    TRIM(CASE WHEN ix.part5  = 0 THEN '' ELSE ',' || c5.colname  END) ||
    TRIM(CASE WHEN ix.part6  = 0 THEN '' ELSE ',' || c6.colname  END) ||
    TRIM(CASE WHEN ix.part7  = 0 THEN '' ELSE ',' || c7.colname  END) ||
    TRIM(CASE WHEN ix.part8  = 0 THEN '' ELSE ',' || c8.colname  END) AS colname,
    t.tabname,
    i.nrows,
    i.ustlowts
FROM sysindices i
JOIN systables t ON i.tabid = t.tabid
LEFT JOIN sysindexes ix ON i.tabid = ix.tabid AND i.idxname = ix.idxname
LEFT JOIN syscolumns c1 ON i.tabid = c1.tabid AND ix.part1  = c1.colno
LEFT JOIN syscolumns c2 ON i.tabid = c2.tabid AND ix.part2  = c2.colno
LEFT JOIN syscolumns c3 ON i.tabid = c3.tabid AND ix.part3  = c3.colno
LEFT JOIN syscolumns c4 ON i.tabid = c4.tabid AND ix.part4  = c4.colno
LEFT JOIN syscolumns c5 ON i.tabid = c5.tabid AND ix.part5  = c5.colno
LEFT JOIN syscolumns c6 ON i.tabid = c6.tabid AND ix.part6  = c6.colno
LEFT JOIN syscolumns c7 ON i.tabid = c7.tabid AND ix.part7  = c7.colno
LEFT JOIN syscolumns c8 ON i.tabid = c8.tabid AND ix.part8  = c8.colno
WHERE t.tabname = 'your_table';

-- 全库 LOW 级别更新
update statistics;
-- 单表 LOW 级别更新
update statistics for table t;
-- 大表索引字段 HIGH 级别
update statistics high for table t(col1,col2);

-- 开启 SQLTRACE（最多跟踪 15000 条，约需 60MB 内存）
dbaccess sysadmin - <<< 'EXECUTE FUNCTION task("set sql tracing on","15000","4","high","global");'

-- 查看执行耗时 TOP SQL
dbaccess sysmaster - <<< 'unload to sql.unl select sql_maxtime,sql_avgtime,sql_statement from syssqltrace order by sql_maxtime desc;'

Set explain on;
-- 执行目标 SQL
Set explain off;

-- 避免全表扫描
SELECT /*+ AVOID_FULL(tab) */ * FROM tab WHERE ...;
-- 强制使用指定索引
SELECT /*+ INDEX(tab,idx_col) */ * FROM tab WHERE ...;
-- 强制使用哈希连接
SELECT /*+ USE_HASH(tab1) */ * FROM tab1, tab2 WHERE ...;

Set pdqpriority 10;     -- 使用 10% 并行资源
Select distinct col1 from large_table;
Set pdqpriority 0;       -- 关闭并行

-- 查看各 chunk 的页读写次数
onstat -D

-- 查看 checkpoint 历史和触发事件
onstat -g ckp
-- 查看 checkpoint 等待情况（ckpwaits 表示等待 checkpoint 完成的线程数）
onstat -p | grep ckpwaits
-- 推荐 LRU 设置（减少脏页集中写入）
lru_min_dirty=2.00
lru_max_dirty=5.0

-- 将页级锁表改为行级锁
Alter table tabname lock mode (row);
-- 设置锁等待超时（秒）
Set lock mode to wait 30;
-- 脏读模式（报表类查询适用）
Set isolation to dirty read;

-- 查看锁等待比例
onstat -p | grep lokwaits
-- 若 lokwaits/lockreqs > 1%，检查隔离级别和应用程序逻辑