G-Tech Moment | GBase Database Inspection Guide: Comprehensive Data Security Protection

In the digital economy era, databases serve as critical infrastructure for managing enterprise data assets, and their stability and security are directly related to the stability of enterprise information systems and business operations. With the rapid growth of data volumes and increasingly complex business scenarios, database systems often face challenges such as high-concurrency workloads and resource bottlenecks. Conducting regular, systematic database inspections can effectively identify potential performance issues, optimize resource allocation, prevent data risks, and provide "full lifecycle" health assurance for business systems.

This article uses the GBase database as an example to explain how to conduct a comprehensive health check through a series of inspection metric queries and SQL queries.

Inspection Objectives and User Permissions

The purpose of database inspection is to identify potential issues in a timely manner, optimize performance, and ensure data integrity and security. To perform these inspection tasks, we need to create a database user with sufficient permissions. The following example creates a user named `db_inspector` and grants the required permissions:

CREATE USER db_inspector WITH SYSADMIN MONADMIN PASSWORD 'gbase;123';

Inspection Metrics and SQL Queries

1. Tablespace Check

A tablespace is a logical area in GBase8c used to store data files. By checking tablespace usage, storage space shortages can be identified in advance.

SELECT    
to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "Inspection Time",   
spcname AS "Name",   
pg_catalog.pg_get_userbyid(spcowner) AS "Owner",   
pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS "Size(Tablespace Size)"
FROM pg_catalog.pg_tablespace
ORDER BY 1;

Metric to monitor: tablespace size

Check whether the tablespace size is approaching the storage limit, and plan capacity expansion in advance.

2. Database Check

Check the size of each database, excluding system databases (such as template0, template1, and postgres).

SELECT datname, pg_size_pretty(pg_database_size(oid))
FROM pg_database
WHERE datname NOT IN ('template0', 'template1', 'postgres')
ORDER BY pg_database_size(oid) DESC;

Metric to monitor: database size

Check whether database size is growing abnormally, and investigate whether there is data accumulation or uncleaned logs.

3. User Check

Check database user permissions, connection limits, and resource quotas.

SELECT usename, usecreatedb, usesuper, valbegin, valuntil, spacelimit, tempspacelimit, spillspacelimit FROM pg_user;

Metrics to monitor:  user permissions and resource quotas

Check whether user permissions are properly assigned and whether highly privileged users are abusing resources.

4. Current Number of Database Connections

Check the usage of current connections against the maximum number of connections.

SELECT    
to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "Inspection Time",   
setting::int8 AS "max_conn(Maximum Connections)",   
(SELECT count(*) FROM pg_stat_activity) AS "now_conn(Current Connections)",   
setting::int8 - (SELECT count(*) FROM pg_stat_activity) AS "remain_conn(Remaining Connections)"
FROM pg_settings
WHERE name = 'max_connections';

Metrics to monitor: current connections and maximum connections 

If the current number of connections exceeds 90% of the maximum number of connections, optimize the connection pool or increase the maximum number of connections.

5. Number of Currently Idle Sessions

Check the number of idle connections. Too many idle connections may consume system resources.

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

6. Sessions with Long-Uncommitted Transactions

Check transactions that have not been committed for a long time, as they may cause table locks or resource consumption.

SELECT count(*) 
FROM pg_stat_activity 
WHERE state = 'idle in transaction' AND now() - state_change > INTERVAL '5 mins';

Metric to monitor: session IDs in the currently returned records

If a large number of connections remain idle and uncommitted for an extended period, investigate application logic issues.

7. TOP 20 Sessions Consuming the Most Memory

Check the sessions consuming the most memory to prevent insufficient memory issues.

SELECT sessid, pg_size_pretty(sum(totalsize)), pg_size_pretty(sum(freesize))
FROM gs_session_memory_detail
GROUP BY sessid
ORDER BY sum(totalsize) DESC
LIMIT 20;

Metric to monitor: session memory

If a session consumes more than 1GB of memory, review the SQL queries of that session and optimize them where necessary.

8. Database Conflict Event Check

Check database conflict events, which may cause data inconsistency or performance degradation.

SELECT * 
FROM pg_stat_database_conflicts 
WHERE datname NOT IN ('template0', 'template1');

9. Buffer Cache Hit Rate Query

Check the buffer cache hit rate to evaluate memory utilization efficiency.

SELECT sum(n_blocks_hit) / sum(n_blocks_fetched) AS cache_hit_rate FROM dbe_perf.statement;

Metric to monitor: buffer cache hit rate

If the buffer cache hit rate is below 90%, more memory may be needed or queries may need to be optimized.

10. Replication Slot Check

Check the status of replication slots to ensure normal data synchronization.

SELECT * FROM pg_replication_slots;

Metric to monitor: replication slot status

The active column of a replication slot should be t, and restart_lsn should continue advancing.

11. Node Memory Usage

Check node memory usage to prevent insufficient memory.

SELECT * FROM pg_total_memory_detail;

Metric to monitor: node memory

If dynamic_peak_memory approaches max_dynamic_memory, consider increasing memory.

12. Check Important Database Configurations

Check key configuration parameters to ensure they meet production environment requirements.

SELECT    
to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "Inspection Time",   
name, setting
FROM pg_settings
WHERE name IN (
  'data_directory', 'port', 'client_encoding', 'config_file', 'hba_file',
   'ident_file', 'archive_mode', 'logging_collector', 'log_directory',
'log_filename', 'log_truncate_on_rotation', 'log_statement',
'log_min_duration_statement', 'max_connections', 'listen_addresses')
ORDER BY name;

13. Check Table Locks

Check table locks to avoid performance issues caused by long-duration table locks.

SELECT    
to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "Inspection Time",   
relname AS "relname(Table Name)",   
b.nspname AS "shemaname(Schema Name)",   
c.rolname AS "user(User Name)",   
d.locktype AS "locktype(Locked Object Type)",   
d.mode AS "mode(Lock Type)",   
d.pid AS "pid(Process ID)",   
e.query AS "query(Table-locking SQL)",   
current_timestamp - state_change AS "lock_duration(Table Lock Duration)"
FROM pg_class a
INNER JOIN pg_namespace b ON a.relnamespace = b.oid
INNER JOIN pg_roles c ON a.relowner = c.oid
INNER JOIN pg_locks d ON a.oid = d.relation
LEFT JOIN pg_stat_activity e ON d.pid = e.pid
WHERE d.mode = 'AccessExclusiveLock'
ORDER BY "lock_duration(Table Lock Duration)" DESC;

Recommendation: If a long-duration table lock is found, use SELECT pg_terminate_backend(pid); to terminate the relevant session.

14. Check TOP 5 Long Transactions

Check transactions that have been running for a long time, as they may cause resource consumption or table locks.

SELECT    
to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "Inspection Time",   
relname AS "relname(Table Name)",   
b.nspname AS "shemaname(Schema Name)",   
c.rolname AS "user(User Name)",   
d.locktype AS "locktype(Locked Object Type)",   
d.mode AS "mode(Lock Type)",   
d.pid AS "pid(Process ID)",   
e.query AS "query(Table-locking SQL)",   current_timestamp - state_change AS "lock_duration(Table Lock Duration)"
FROM pg_class a
INNER JOIN pg_namespace b ON a.relnamespace = b.oid
INNER JOIN pg_roles c ON a.relowner = c.oid
INNER JOIN pg_locks d ON a.oid = d.relation
LEFT JOIN pg_stat_activity e ON d.pid = e.pid
WHERE d.mode = 'AccessExclusiveLock'
ORDER BY "lock_duration(Table Lock Duration)" DESC;

15. Check TOP 5 Table Bloat

Check table bloat, which may lead to performance degradation.

SELECT    
to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "Inspection Time",   
current_database() AS current_database,   
relname AS "table_name(Table Name)",   
schemaname AS "schema_name(Schema Name)",   
pg_size_pretty(pg_relation_size('"' || schemaname || '"."' || relname || '"')) AS "table_size(Table Size)",   
n_dead_tup AS "n_dead_tup(Invalid Record Count)",   
n_live_tup AS "n_live_tup(Valid Record Count)",   
to_char(round(n_dead_tup * 1.0 / (n_live_tup + n_dead_tup) * 100, 2), 'fm990.00') AS "dead_rate(Invalid Record Ratio%)"
FROM pg_stat_all_tables
WHERE n_live_tup + n_dead_tup <> 0
ORDER BY "dead_rate(Invalid Record Ratio%)" DESC
LIMIT 5;

Recommendation: Execute `VACUUM ANALYZE` on bloated tables.

16. Check Index Bloat

Check index bloat, which may lead to degraded query performance.

SELECT    
to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "Inspection Time",   
current_database() AS db,   
schemaname,   
tablename,   
bs,   
reltuples::bigint AS tups,   
relpages::bigint AS pages,   
otta,   
ROUND(CASE WHEN otta = 0 OR relpages = 0 OR relpages = otta THEN 0.0 ELSE relpages / otta::numeric END, 1) AS tbloat,   
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,   
CASE WHEN relpages < otta THEN 0 ELSE bs * (relpages - otta)::bigint END AS wastedbytes,   
CASE WHEN relpages < otta THEN '0 bytes' ELSE (bs * (relpages - otta))::bigint || ' bytes' END AS wastedsize
FROM (   
SELECT        
nn.nspname AS schemaname,       
cc.relname AS tablename,       
COALESCE(cc.reltuples, 0) AS reltuples,       
COALESCE(cc.relpages, 0) AS relpages,       
COALESCE(bs, 0) AS bs,       
COALESCE(CEIL((cc.reltuples * ((datahdr + ma - (CASE WHEN datahdr % ma = 0 THEN ma ELSE datahdr % ma END)) + nullhdr2 + 4)) / (bs - 20::float)), 0) AS otta   
FROM pg_class cc   
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'   
LEFT JOIN (       
SELECT            
ma, bs, foo.nspname, foo.relname,           
(datawidth + (hdr + ma - (CASE WHEN hdr % ma = 0 THEN ma ELSE hdr % ma END)))::numeric AS datahdr,           
(maxfracsum * (nullhdr + ma - (CASE WHEN nullhdr % ma = 0 THEN ma ELSE nullhdr % ma END))) AS nullhdr2       
FROM (           
SELECT                
ns.nspname, tbl.relname, hdr, ma, bs,               
SUM((1 - coalesce(null_frac, 0)) * coalesce(avg_width, 2048)) AS datawidth,               
MAX(coalesce(null_frac, 0)) AS maxfracsum,               
hdr + (                   
SELECT 1 + count(*) / 8                   
FROM pg_stats s2                   
WHERE null_frac <> 0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname               
) AS nullhdr           
FROM pg_attribute att           
JOIN pg_class tbl ON att.attrelid = tbl.oid           
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace           
LEFT JOIN pg_stats s ON s.schemaname = ns.nspname               
AND s.tablename = tbl.relname               
AND s.inherited = false               
AND s.attname = att.attname,           
(               
SELECT                    
(SELECT current_setting('block_size')::numeric) AS bs,                   
CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '"[0-9]+.[0-9]+"%' for '') IN ('8.0', '8.1', '8.2') THEN 27 ELSE 23 END AS hdr,                   
CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma               
FROM (SELECT version() AS v) AS foo           
) AS constants           
WHERE att.attnum > 0 AND tbl.relkind = 'r'           
GROUP BY 1, 2, 3, 4, 5       
) AS foo   
) AS rs ON cc.relname = rs.relname AND nn.nspname = rs.nspname
) AS sml;

Recommendation: Index bloat may degrade query performance. Rebuild or optimize indexes regularly.

Inspection Summary

Through the above inspection metrics and SQL queries, we can gain a comprehensive understanding of the health status and performance of the GBase8c database. Regularly performing these inspection tasks can help us identify potential issues in a timely manner, optimize resource allocation, and ensure stable database operation. It is recommended to package these queries into scripts and run them regularly through scheduled tasks, recording the results in log files for subsequent analysis and review.