Gauss200使用分享

登录、授权控制

解锁账户

su - omm
gsql -d db_rdb -p 8000 -r


alter user ado_user account unlock;
alter user sig_qry_rpt account unlock;

参数控制 && 优化参考

只读模式解锁

su - omm
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "default_transaction_read_only = false"

设置白名单

vim /hwapp/gaussdb200/data/data_cn/pg_hba.conf

host    all    all   10.25.168.80/32    sha256

/hwapp/gaussdb200/data/data_cn/pg_hba.conf 的来源
find / -name pg_hba.conf

数据库参数配置

在服务器上搜名字为postgresql.conf的文件
配置文件的路径查询：SELECT distinct sourcefile FROM pg_settings where sourcefile is not null;

# 超时时间
SELECT name, setting, sourcefile FROM pg_settings where name like '%session_timeout%';

编辑配置(要重启数据库)

vim /hwapp/gaussdb200/data/data_cn/postgresql.conf

查看数据库最大连接设置

show max_connections;

查询数据库当前的连接数量

select count(1) from pg_stat_activity;

查看数据表大小

SELECT table_catalog,table_schema,table_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size 
FROM information_schema.tables
where table_catalog='db_rdb' and table_schema = 'sig_qry_rpt' --and table_name='t311_common_video_detail_mi'
ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') desc;

查询数据库的大小

select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

查看高斯200 连接情况、sql执行、卡表

select max(query_start) as query_start,max(state_change) as state_change,client_hostname,query,count(1) as num,max(state) 
from pg_stat_activity where query like '%443%' 
group by client_hostname,query order by query_start;

杀数据库进程

# 找到卡顿的进程id
select pid from pg_stat_activity;

# 发生停止信号(线程不会立即停止，需等其任务完成后才会终止)
SELECT pg_terminate_backend(pid);

重启数据库

systemctl restart gauss

定时任务

创建存储过程

CREATE OR REPLACE FUNCTION sig_qry_rpt.p_xxxx(sample_id bigint, current_export_time character varying) RETURNS void
    LANGUAGE plpgsql
    NOT FENCED SHIPPABLE
AS
$$
DECLARE
    current_ip1 varchar(50);
begin
    select (string_to_array(ipv4, ';'))[1] into current_ip1 from sig_qry_rpt.pl_data where id = sample_id;
    
    insert into sig_qry_rpt.t310_video_detail_mi_pl
    with tmp as (select (string_to_array(ipv4, ';'))[1] as ip1,
                        (string_to_array(ipv4, ';'))[2] as ip2,
                        (string_to_array(ipv4, ';'))[3] as ip3,
                        (string_to_array(ipv4, ';'))[4] as ip4,
                        (string_to_array(ipv4, ';'))[5] as ip5,
                        (string_to_array(ipv4, ';'))[6] as ip6
                 from sig_qry_rpt.pl_data
                 where id != sample_id
                 order by id)
    SELECT videosite,
           videositeid,
           stat_time
    from sig_qry_rpt.t310_video_detail_mi_pl t
             cross join tmp a
    where t.export_time = current_export_time
      and account in (current_ip1, current_ip2, current_ip3, current_ip4, current_ip5, current_ip6);
end;
$$;

开启定时任务

-- 10分钟执行一次
select sig_qry_rpt.add_jobs('call sig_qry_rpt.UPDATE_T409_TOTAL_COUNT();',sysdate + 10/(24*60),'sysdate+10/(24*60)');

select * from pg_job;

应用

查看建表信息（包含分区）DDL

select * from pg_get_tabledef('sig_qry_rpt.db_cmcc_info');

分区

新建分区

ALTER TABLE sig_qry_rpt.t315_tcp_common_detail_mi ADD PARTITION partition_1687708800 VALUES LESS THAN(1687708801);

查询表分区

SELECT part.relname,substring(part.relname, 11),to_timestamp(to_number(substring(part.relname,11))) from pg_partition part
join  pg_class pc on pc.oid = part.parentid
where pc.relname = lower('t408_user_dnsip_stat_d') and part.parttype='p' and part.relname like 'partition_%'
ORDER BY part.relname;

索引

建立索引

# 普通索引
DROP INDEX IF EXISTS sig_qry_rpt.t443_total_user_attr_d_account_index;
CREATE INDEX t443_total_user_attr_d_account_index ON sig_qry_rpt.t443_total_user_attr_d (account);

# 分区索引
DROP INDEX IF EXISTS ado_v1r5c05.T_FILTER_BOX_FLOW_index;
CREATE INDEX T_FILTER_BOX_FLOW_index ON ado_v1r5c05.T_FILTER_BOX_FLOW(DATA) LOCAL;

重建表（分区）的索引

-- 目前不支持单个分区重建索引
REINDEX TABLE sig_qry_rpt.t316_top_url_mi -- PARTITION partition_1685721600;

建分区表

CREATE TABLE sig_qry_rpt.test
(
    "account" varchar(400) NULL,
    protocolname varchar(400) NULL,
    subprotocolname varchar(400) NULL
)
WITH (orientation=column, compression=low)
DISTRIBUTE BY HASH(account)
TO GROUP group_version1
PARTITION BY RANGE (export_time)
(
     PARTITION partition_1664553600 VALUES LESS THAN ('1664553601') TABLESPACE pg_default,
     PARTITION partition_1664640000 VALUES LESS THAN ('1664640001') TABLESPACE pg_default
)
ENABLE ROW MOVEMENT;

特定时间段分组

将时间戳按照5分钟分组
to_number(statTime) - to_number(statTime) % (5 * 60)

特殊语法

-- 时间转星期
select (case when extract(DOW FROM '2023-05-05 00:00:00'::timestamp) = 0.0000 then 7 else extract(DOW FROM '2023-05-05 00:00:00'::timestamp) end)::int as weekday

字符串/时间戳互转

select EXTRACT(epoch FROM CAST('2023-06-24 18:00:00' AS TIMESTAMP)) - 8 * 3600;

select substring(to_timestamp(1687600800), 0, 20);

临时变量

SET var.startTime = '1690905600';
SELECT current_setting('var.startTime');

函数相关

字段判断处理case when

(case when a.effective = 'null' then null else a.effective end)

时间戳转时间

to_timestamp(to_number(statTime))

字符串相关

字符串包含判断 instr( )

-- like '%WLAN%'
instr('WLAN1DF', 'WLAN') > 0

分组后字段拼接 string_agg( )

string_agg(ipLocation,  ',')

聚合后分组，在对rtt1根据liu加权计算

【sum(rtt1 a * liu a) / sum(liu), a∈0, n】

select 
    sum(x) / sum(liu) as rtt1, name, string_agg(ip, ',') as ip, min("user")
from (
    select rtt1, liu, name, rtt1 * liu as x, ip, "user" from (
        select 6 as rtt1, 10 as liu,'ha' as name,null as ip,'abc' as user  from dual
        union all 
        select 7 as rtt1, 15 as liu,'ha' as name,'127.0.0.1' as ip,'abc' as user from dual
        union all 
        select null as rtt1, 5 as liu,'ha' as name,'127.0.0.2' as ip,'abc' as user from dual
    )
) group by name

注意点

聚合情况下，不存在的列最好统一风格：null::float4 as gameHeartbeatDelay