人大金仓日常运维

目录

一、数据库瘦身

1.找出占用空间最大的前5个表：

2.找出无效索引

3.找出冷索引

4.找出重复索引

二、筛查慢查询

1.通过数据字典抓取慢查询

2.设置数据库收集慢SQL日志

2.1配置基本的日志参数

2.2配置sql执行的跟踪参数

2.3重启数据库则可以在csv.log 查看SQL的执行时长等相关信息

三、创建索引 

1.在线创建索引

 2.加快创建索引的速度

四、处理长事务

1.查找运行中的长事务

2.在会话2中终止会话1中执行的长事务

3.预防出现运行时间超长的SQL、超长事务、超长会话

3.1设置sql执行的最长时间单位毫秒

3.2设置空闲事务超时

3.3设置空闲会话超时

五、垃圾回收

1.索引膨胀

1.1创建实验环境

1.2删除数据并vacuum 可以发现表回收成功，索引没有回收成功。

1.3执行vacuum full 然后查看索引大小，可以发现vaccum full之后，索引膨胀消除。

2.表膨胀

2.1创建实验环境

2.2更新t01表，使表增大1倍

2.3vacuum full t01，清理t01表空间。

一、数据库瘦身

1.找出占用空间最大的前5个表：

SELECT sys_relation_size(oid)/1024/1024||'MB' as relsize,relname
FROM sys_class
WHERE relkind='r'
ORDER BY sys_relation_size(oid) DESC LIMIT 5;

2.找出无效索引

SELECT indexrelid::regclass,indrelid::regclass 
FROM sys_index 
WHERE indisvalid=false;

3.找出冷索引

SELECT current_database(),schemaname,relname,indexrelname
FROM sys_stat_all_indexes
WHERE idx_scan=0 or idx_tup_read=0 or idx_tup_fetch=0;

4.找出重复索引

SELECT sys_size_pretty(SUM(sys_relation_size(idx))::BIGINT) AS SIZE,
　　   (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
　　   (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
FROM (SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| 
             indclass::text ||E'\n'|| indkey::text ||E'\n'||COALESCE(indexprs::text,'')||E'\n' || 
             COALESCE(indpred::text,'')) AS KEY FROM sys_index) sub 
GROUP BY KEY HAVING COUNT(*)>1 
ORDER BY SUM(sys_relation_size(idx)) DESC;

二、筛查慢查询

1.通过数据字典抓取慢查询

SELECT pid,query_start,now()-query_start as runtime,query 
FROM sys_stat_activity  
WHERE state<>'idle' AND now()-query_start>interval '60s';

2.设置数据库收集慢SQL日志

2.1配置基本的日志参数

添加配置信息到/data/kingbase.conf

log_destination = 'csvlog'
logging_collector = on
log_directory = 'sys_log'
log_filename = 'kingbase-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_min_messages = info

2.2配置sql执行的跟踪参数

添加配置信息到/data/kingbase.conf

log_min_duration_statement = 1
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_line_prefix = '%t [%p] %q%u@%d%a'

2.3重启数据库则可以在csv.log 查看SQL的执行时长等相关信息

sys_ctl restart

tail -5 /data/sys_log/kingbase-2022-01-17_120246.csv

三、创建索引 

1.在线创建索引

标准创建索引：
CREATE INDEX idx_t03_id ON t03（id）;

并发创建索引：
CREATE INDEX CONCURRENTLY idx_t03_name ON t03(name);

 2.加快创建索引的速度

创建测试环境
#DROP TABLE t01;
CREATE UNLOGGED TABLE t01(id bigserial,info varchar);
WITH a as(SELECT generate_series(1,1000000),random()::varchar) INSERT INTO t01 (SELECT * FROM a);
VACUUM ANALYZE t01;

快速创建索引：
\timing
SET maintenance_work_mem = '1MB';
CREATE INDEX idx_t01_id_bte1 ON t01(id);
DROP INDEX idx_t01_id_bte1;
SET maintenance_work_mem = '64MB';
CREATE INDEX idx_t01_id_bte1 ON t01(id);

四、处理长事务

1.查找运行中的长事务

SELECT pid,state,query 
FROM sys_stat_activity 
WHERE state <> 'idle' AND now()-xact_start > interval '60s';

2.在会话2中终止会话1中执行的长事务

SELECT sys_terminate_backend(16145);
SELECT pid,state,query FROM sys_stat_activity 
WHERE state <> 'idle' AND now()-xact_start > interval '300s';

3.预防出现运行时间超长的SQL、超长事务、超长会话

3.1设置sql执行的最长时间单位毫秒

set statement_timeout=5000; 

3.2设置空闲事务超时

set idle_in_transaction_session_timeout=10000;
begin;
SELECT now();
//等待10秒以上
SELECT now();

3.3设置空闲会话超时

ALTER SYSTEM SET client_idle_timeout =10000;
SELECT sys_reload_conf();
--重新登录后等待10秒以上
SELECT now();

五、垃圾回收

1.索引膨胀

1.1创建实验环境

DROP TABLE IF EXISTS t01;
CREATE TABLE t01(id int primary key, name text unique, age int);
CREATE INDEX t01_age_idx on t01(age);
INSERT INTO t01 SELECT generate_series(1, 200000),generate_series(1, 200000)||'name',generate_series(1, 200000);
SELECT sys_size_pretty(sys_relation_size('t01'));
SELECT sys_size_pretty(sys_indexes_size('t01'));

1.2删除数据并vacuum 可以发现表回收成功，索引没有回收成功。

DELETE FROM t01 ;
VACUUM t01 ;
SELECT sys_size_pretty(sys_relation_size('t01'));
SELECT sys_size_pretty(sys_indexes_size('t01'));

1.3执行vacuum full 然后查看索引大小，可以发现vaccum full之后，索引膨胀消除。

vacuum FULL t01 ;
SELECT sys_size_pretty(sys_relation_size('t01'));
SELECT sys_size_pretty(sys_indexes_size('t01'));

2.表膨胀

2.1创建实验环境

DROP TABLE IF EXISTS t01;
CREATE TABLE t01(id int,name text);
INSERT INTO t01 SELECT generate_series(1,5000000),md5(random());
SELECT sys_relation_size('t01')/1024/1024||'MB';

2.2更新t01表，使表增大1倍

UPDATE t01 set id=id+1;
SELECT sys_relation_size('t01')/1024/1024||'MB';

2.3vacuum full t01，清理t01表空间。

VACUUM FULL t01;
SELECT sys_relation_size('t01')/1024/1024||'MB';