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');
Top comments (0)