数据库日常巡检sql

1.数据库版本

ksql   -Usystem  test -c "select version();"
sys_ctl   -V

2.事务ID是否正常

SELECT datname, age(datfrozenxid) AS xid FROM sys_catalog.sys_database where xid > -1;

3.数据库启动时间

select   sys_postmaster_start_time();

4.各数据库大小

 SELECT datname, sys_size_pretty(sys_database_size(datname))  AS size FROM sys_catalog.sys_database;

5.是否允许修改系统表

select   setting  from  sys_settings  where name  ='allow_system_table_mods';

6.license有效期

select get_license_validdays();

7.日志检查

 egrep -iIn 'error|fatal|错误|严重错误'  data/sys_log/*

8.最大连接数/连接数/活跃连接

select count(*) count from sys_stat_activity;show max_connections;

select (select setting as "最大连接数" from sys_settings where name='max_connections'),
(select count(*)::text as "当前连接数" from sys_stat_activity),
(select count(*)::text as "活跃连接数" from sys_stat_activity where state <> 'idle');

9.检查TOP SQL

SELECT dbid, userid, count(queryid) as query_count, substr(query, 0, 80) short_query FROM sys_stat_statements group by dbid, userid, short_query HAVING(count(queryid) >

10.数据库所占空间

 select round(sum(sys_database_size(datname))/1024/1024,2) AS size FROM sys_catalog.sys_database;

11.锁等待数量

select count(*) count from sys_stat_activity where state is not null and wait_event_type in('LWLock','Lock');

select database,relation,sc.relname,pid,mode,granted FROM sys_locks sl left join sys_database sd on sl.database=sd.oid left join sys_class sc on  sl.relation=sc.oid WHERE sys_backend_pid()<>sl.pid and granted='f';

12.是否存在假死连接

 SELECT count(*) count FROM sys_stat_activity WHERE query_start < now() - INTERVAL '2 day';

13.TOP 20 big table

select relname, round(sys_relation_size(oid)/1024/1024,2) as size from sys_class where relkind = 'r' order by 2 desc limit 20

14.失效索引

select indexrelid::regclass , indrelid::regclass, indisvalid from sys_index where indisvalid=false order by 1;

15.冷索引

select schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch from sys_stat_all_indexes where schemaname not in ('pg_catalog','sysmac','pg_toast','perf') and (idx_scan=0 or idx_tup_read=0 or idx_tup_fetch=0 )order by idx_scan desc,idx_tup_read desc, idx_tup_fetch desc,schemaname,relname

16.锁等待详细信息

SELECT sl.pid,sl.locktype,sl.database,sl.relation, usename,MODE,GRANTED,ssa.datname, ssa.application_name,ssa.client_addr,ssa.client_port, ssa.backend_start, ssa.wait_event_type, ssa.state,transactionid,classid, query FROM sys_locks sl JOIN sys_stat_activity ssa ON sl.pid = ssa.pid WHERE transactionid IS NOT NULL ORDER BY backend_start, pid

17.流复制进程信息

select pid,usesysid,usename,application_name,client_addr,client_hostname,client_port,backend_start,backend_xmin,state,sent_lsn,write_lsn,flush_lsn,replay_lsn,write_lag::text,flush_lag::text,replay_lag::text,sync_priority,sync_state,reply_time FROM sys_stat_replication

18.复制槽信息

select * from sys_replication_slots；

19.集群WAL_进程信息

 select pid, backend_type from sys_stat_activity where backend_type like 'wal%';

20.查询qps

SELECT qps FROM qps();

21.前10的大表

(SELECT
	relname,
	relkind,
	relpages,
	pg_relation_size(oid) AS SIZE
FROM
	pg_class
WHERE
	relkind = 'r'
ORDER BY
	relpages DESC
LIMIT 10)
UNION ALL
(SELECT
	relname,
	relkind,
	relpages,
	pg_relation_size(oid) AS SIZE
FROM
	pg_class
WHERE
	relkind = 'i'
ORDER BY
	relpages DESC
LIMIT 10);

22.返回指定数据库中所有 SQL 语句的 I/O 统计信息，并按读取块所花费的总时间降序排

SELECT 
    queryid, 
    wal_records, 
    shared_blks_hit, 
    shared_blks_read, 
    shared_blks_written, 
    local_blks_hit, 
    local_blks_read, 
    local_blks_written, 
    temp_blks_read, 
    temp_blks_written, 
    blk_read_time, 
    blk_write_time
FROM 
    sys_stat_sqlio
WHERE 
    datid = (SELECT oid FROM sys_database WHERE datname = 'your_database_name')
ORDER BY 
    blk_read_time DESC;

23.统计数据库中 SQL 语句的运行次数

select  * from  sys_stat_sqltime ;

24.统计数据库中 SQL 语句与等待事件的信息

select  * from sys_stat_sqlwait ;

25.统计当前数据库的锁信息

SELECT pd.datname AS datname, pl.pid AS pid, pl.mode, psa.query AS query, pl.granted AS granted, psa.usename, psa.xact_start AS xactStart FROM (SELECT DISTINCT database, pid, mode, granted FROM pg_locks) pl JOIN pg_database pd ON pl.database = pd.oid JOIN pg_stat_activity psa ON pl.pid = psa.pid

26.复制延迟情况

SELECT slot_name, plugin, slot_type, database, temporary, active, active_pid, xmin, catalog_xmin, pg_wal_lsn_diff(pg_current_wal_insert_lsn(), restart_lsn) restart_delay, pg_catalog.pg_wal_lsn_diff(pg_current_wal_insert_lsn(), confirmed_flush_lsn) flush_delay FROM pg_catalog.pg_replication_slots

27.主备延迟查看

SELECT
    pg_current_wal_insert_lsn() AS insert_lsn,
    pg_current_wal_lsn() AS current_lsn,
    pg_current_wal_flush_lsn() AS flush_lsn,
    pg_current_wal_insert_lsn() - pg_current_wal_flush_lsn() AS lsn_diff_insert_flush,
    pg_current_wal_lsn() - pg_current_wal_flush_lsn() AS lsn_diff_current_flush
;

28.查看归档状态

SELECT
	pg_walfile_name(
		pg_current_wal_lsn()
	) AS current_wal_lsn,
	archived_count,
	COALESCE(
		last_archived_wal,
		'-'
	) AS last_archived_wal,
	COALESCE(
		last_archived_time,
		'1970-01-01 00:00:00.000000 +08:00'
	) AS last_archived_tim,
	COALESCE(
		failed_count,
		0
	) AS failed_count,
	COALESCE(
		last_failed_wal,
		'-'
	) AS last_failed_wal,
	COALESCE(
		last_failed_time,
		'1970-01-01 00:00:00.000000 +08:00'
	) AS last_failed_time,
	stats_reset
FROM
	pg_stat_archiver

29.

SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time / 1000 AS checkpoint_write_time, checkpoint_sync_time / 1000 AS checkpoint_sync_time, buffers_checkpoint, buffers_clean, maxwritten_clean, buffers_backend, buffers_backend_fsync, buffers_alloc, stats_reset FROM pg_stat_bgwriter

30.数据库的统计信息和活动指标

select coalesce(datname, '-') as datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, temp_files, temp_bytes, deadlocks, coalesce(checksum_failures, -1) as checksum_failures, coalesce(checksum_last_failure, '1970-01-01 00:00:00.000000 +08:00') as checksum_last_failure, blk_read_time / 1000 as blk_read_time, blk_write_time / 1000 as blk_write_time, coalesce(stats_reset, '1970-01-01 00:00:00.000000 +08:00') as stats_reset from pg_stat_database

31.数据库的统计信息和活动指标

SELECT datname,
blks_hit * 100 / (blks_hit + blks_read) AS cache_hit_ratio
FROM sys_stat_database
WHERE (blks_hit + blks_read) > 0;

32.库使用的临时文件

SELECT datname, temp_files, temp_bytes
FROM sys_stat_database
ORDER BY temp_bytes DESC;

33.冲突而取消的查询统计信息

select datname, confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock from sys_stat_database_conflicts

34.识别高冲突数据库

SELECT datname,
confl_tablespace + confl_lock + confl_snapshot +
confl_bufferpin + confl_deadlock AS total_conflicts
FROM sys_stat_database_conflicts
ORDER BY total_conflicts DESC;

35.重置统计信息

当前数据库所有统计信息
select  sys_stat_reset();    
重置特定表的统计信息
SELECT sys_stat_reset_single_table_counters('your_table_name'::regclass); 
重置函数统计信息
SELECT sys_stat_reset_single_function_counters(function_oid); 重置函数统计信息
这个函数会重置以下共享统计信息：数据库范围的统计信息（pg_stat_database）后台写入器进程的统计信息 归档和检查点活动的统计信息  预写式日志(WAL)活动的统计信息
SELECT sys_stat_reset_single_function_counters(function_oid); 
sys_stat_database - 数据库级统计
sys_stat_bgwriter - 后台写入器统计
sys_stat_archiver - WAL归档统计
sys_stat_checkpointer - 检查点统计

36.索引膨胀查询

SELECT schemaname||'.'||relname AS table_name,
    indexrelname AS index_name,
    sys_size_pretty(
	sys_relation_size(indexrelid)
) AS index_size,
    sys_size_pretty(
	sys_relation_size(relid)
) AS table_size,
    (
	sys_relation_size(indexrelid)::NUMERIC/NULLIF(
		sys_relation_size(relid),
		0
	)
) AS index_to_table_ratio FROM     sys_stat_user_indexes JOIN     sys_class ON
sys_stat_user_indexes.indexrelid = sys_class.oid  WHERE     sys_relation_size(indexrelid) > 10000000

37.表膨胀查询

SELECT
    schemaname||'.'||relname,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) AS dead_tup_ratio
FROM
    sys_stat_all_tables
WHERE
    n_dead_tup >= 10000
ORDER BY dead_tup_ratio DESC
LIMIT 10;

38.最大的五张表及大小

SELECT 
table_schema || '.' || table_name 
AS table_full_name, sys_size_pretty(sys_total_relation_size('"' ||table_schema || '"."' || table_name || '"')) AS size
FROM 
information_schema.tables
ORDER BY
    sys_total_relation_size('"' || table_schema || '"."' || table_name || '"')
DESC limit 5;

39.表膨胀率

SELECT
    schemaname AS schema_name,
    relname AS table_name,
    n_tup_ins AS rows_inserted,
    n_tup_upd AS rows_updated,
    n_tup_del AS rows_deleted,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    CASE 
        WHEN n_live_tup + n_dead_tup = 0 THEN 0
        ELSE ROUND((n_dead_tup::numeric * 100 / (n_live_tup + n_dead_tup)), 2)
    END AS bloat_percentage,
    sys_size_pretty(sys_relation_size(schemaname||'.'||relname)) AS table_size
FROM
    sys_stat_user_tables
WHERE
    n_live_tup > 0  -- 排除空表
ORDER BY
    bloat_percentage DESC,
    n_dead_tup DESC;

40.长事务

select pid, backend_start, xact_start, query_start, state, backend_xid,query from sys_stat_activity where current_timestamp - xact_start > interval '2 hour';

41.无效对象

select owner, object_name, object_type FROM dba_objects where  status= 'INVALID';

42.数据库服务

systemctl status kingbase8dsystemctl status kingbased

43.时间线

select   * from   sys_control_checkpoint() ;