openGauss routine maintenance: daily maintenance check items
Tags: opengauss
Published: 2023-03-25 09:46:58

Daily Maintenance Check Items
Check openGauss status
Use the tools provided by openGauss to query the status of the database and instances, confirm that the databases and instances are in normal operation, and provide data services to the outside world.

Check instance status

gs_check -U omm -i CheckClusterState
check parameters

openGauss=# SHOW parameter_name;
In the above command, parameter_name needs to be replaced with a specific parameter name.

Change parameters

gs_guc reload  -D /gaussdb/data/dbnode -c "paraname=value"
Check lock information
The lock mechanism is an important means for the database to ensure data consistency. Checking relevant information can check the transaction and operation status of the database.

Query the lock information in the database

openGauss=# SELECT * FROM pg_locks;
Query the status information of threads waiting for locks

openGauss=# SELECT * FROM pg_thread_wait_status WHERE wait_status = 'acquire lock';
end system process

Find the running system process, and then use the kill command to end this process.

ps ux
kill -9 pid
Statistical event data
Running SQL statements for a long time will occupy a lot of system resources. Users can check the current running status of the database by checking the time when the event occurs and the memory size occupied.

Query the time of the event

Query the thread start time, transaction start time, SQL start time, and status change time of the query event.

openGauss=# SELECT backend_start,xact_start,query_start,state_change FROM pg_stat_activity;
Query the session count information of the current server

openGauss=# SELECT count(*) FROM pg_stat_activity;
Query system-level statistics

Query the session information that currently uses the most memory.

openGauss=# SELECT * FROM pv_session_memory_detail() ORDER BY usedsize desc limit 10;
object inspection
Tables, indexes, partitions, constraints, etc. are the core storage objects of the database, and its core information and object maintenance are important daily tasks for DBAs.

View table details

openGauss=# \d+ table_name 
Query table statistics

openGauss=# SELECT * FROM pg_statistic;
View index details

openGauss=# \d+ index_name
Query partition table information

openGauss=# SELECT * FROM pg_partition;
collect statistics

Use the ANALYZE statement to collect database-related statistics.

Use the VACUUM statement to reclaim space and update statistics.

Query constraint information

openGauss=# SELECT * FROM pg_constraint;
SQL report check
Use the EXPLAIN statement to view the execution plan.

backup
Data backup is more important than anything else. You should check the backup execution status and validity of the backup on a daily basis to ensure that the backup can ensure data security, and backup security encryption should also be taken into account.

Specify user to export database

gs_dump dbname -p port -f out.sql -U user_name -W password
export schema

gs_dump dbname -p port -n schema_name -f out.sql
export table

gs_dump dbname -p port -t table_name -f out.sql
Basic information check
Basic information includes information such as versions, components, and patch sets. Regularly checking and recording database information is one of the important contents of database lifecycle management.

Version Information

openGauss=# SELECT version();
capacity check

openGauss=# SELECT pg_table_size('table_name');
openGauss=# SELECT pg_database_size('database_name');