Manager Handbook for Centralized AntDB-T - P4–1
Tags: database
Published: 2023-08-15 09:03:58

Routine maintenance
Routine maintenance check items
Check lock information
Lock mechanism is an important tool for database to ensure data consistency, and checking related information can check the transaction and operation status of the database.

```
#Query lock information in database。
postgres=# SELECT * FROM pg_locks;
```
End the system process
Find the running system process, and then use kill command to end this process.

```
ps ux
kill -9 pid
```
Count event data
SQL statement running for a long time will occupy a lot of system resources, users can check the time of the event and the memory size it occupies to know the current database running status.
Query the time of the event
Query the thread start time, transaction start time, SQL start time and state change time of the event.

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

```
postgres=# SELECT count(*) FROM pg_stat_activity;
```

Object check
Tables, indexes, partitions, constraints, etc. are the core storage objects of the database, and their core information and object maintenance are important daily tasks of DBA.
View table details

```
postgres=# \d+ table_name
```
Query table statistics

```
postgres=# SELECT * FROM pg_statistic;
```
View index details

```
postgres=# \d+ index_name
```
Collect statistical information
Use the ANALYZE statement to collect database related statistics.
Use the VACUUM statement to reclaim space and update statistical information.
Query constraint information

```
postgres=# SELECT * FROM pg_constraint;
```
SQL report checking
Use the EXPLAIN statement to check the execution plan.

```
#Simple Examples:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
-------------------------------------------------------------------​-----------
 Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)
         Index Cond: (unique1 < 100)
```
Basic information checking
Basic information includes version, component, patch set and other information. Checking database information regularly and registering it are two important elements of database life cycle management.
Version information

```
postgres=# SELECT version();
```
Capacity check
```
postgres=# SELECT pg_table_size('table_name');

postgres=# SELECT pg_database_size('database_name');
```
Monitor resource consumption
Linux command monitoring:
During the operation of AntDB, it is necessary to pay attention to the resource consumption of the host, which is generally in these four main categories:
1.CPU: usage rate (usr, sys, wai)
2.Memory: memory usage, SWAP usage
3.Disk: disk busyness (iops), disk throughput (read/write speed)
4.Network: traffic, PPS (number of network packets per second)
What Linux commands are available to get this monitoring data?

```
dstat -a      #Collect data for -cpu-, -disk-, -net-, -paging-, -system-
iostat -x 1   #Collect IO data
top           #Monitor CPU and memory usage

#Other monitoring commands：
strace        #Monitor the interaction between user space processes and the kernel
perf          #Performance profiling tool
sar           #Used to collect, report, or save system activity information
proc file system  # Linux virtual file system
```
When investigating and analyzing a problem, you can combine the actual situation and go get the appropriate information on demand.
Monitor with SQL functions
View the disk usage of any table:

```
postgres=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'table_name';
```
View the size of indexes:

```
SELECT c2.relname, c2.relpages
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'table_name' AND
      c.oid = i.indrelid AND
      c2.oid = i.indexrelid
ORDER BY c2.relname;
```
Find the largest tables and indexes:

```
SELECT relname, relpages
FROM pg_class
ORDER BY relpages DESC;
```
Daily cleanup
AntDB databases require periodic cleanup maintenance.
Basics of cleanup
AntDB's VACUUM command must periodically process each table for several reasons:
1.To recover or reuse disk space occupied by updated or deleted rows.
2.Update statistics used by AntDB's query planner.
3.Update visibility mappings, which speed up scans of use-only indexes.
4.Protect old data from being lost due to transaction ID rollbacks or multi-transaction ID rollbacks.
There are two variants of VACUUM: standardVACUUM and VACUUM FULL.VACUUM FULL recovers more disk space but runs more slowly. Also, theVACUUM in standard form can run in parallel with production database operations (theSELECT, INSERT, UPDATE, DELETE, etc. commands will continue to work normally, but you cannot use theALTER TABLE etc. commands to update table definitions during cleanup).VACUUM FULL requires an exclusive lock on the table it is working on, so it cannot be used in parallel with other uses of this table. Therefore, administrators should usually try to use the standard VACUUMand avoid VACUUM FULL.
Recover disk space
In AntDB, the old version of the row is removed not immediately by disposable UPDATEor DELETE. This approach is necessary to benefit from Multiple Version Concurrency Control (MVCC): while the old version may still be visible to other transactions, it cannot be removed. But in the end, no transaction will be interested in an obsolete or deleted version of a row anymore. The space it occupies must be reclaimed for new rows, thus avoiding an unbounded growth of disk space requirements. This is accomplished by runningVACUUM.
The standard VACUUMremoves the dead row versions from tables and indexes and marks the space as available for future reuse. However, it will not give that space back to the operating system, except in the special case where one or more pages at the end of the table become completely free and an exclusive table lock can be easily obtained. Instead, VACUUM FULLactively tightens the table  by writing the contents outside the dead space into a complete new version of the table file. This minimizes the size of the table, but takes longer. It also requires additional disk space for a new copy of the table until the operation is complete.
The general goal of routine cleanup is to do more of the standard VACUUMto avoid the need of VACUUM FULL. The auto-cleanup daemon tries to work this way and never actually issues VACUUM FULL. The idea of this method is not to keep the minimum size of the tables, but to keep the stable state of disk space usage: the space occupied by each table is equal to its minimum size plus the space run out of before the cleanup. Although VACUUM FULLcan be used to contract a table to its minimum size and deliver that disk space back to operating system, it would be meaningless if that table would grow again in the future. Therefore, for the maintenance for those constantly upgraded tables, it is better to run standard VACUUM than to runVACUUM FULL.
Some administrators prefer to do the cleanup according their own plan, for example, do all the work in the night when the load is low. The difficulty with doing cleanup based on a fixed schedule is that if a table has an unanticipated spike in update activity, it can balloon to the point where it really needsVACUUM FULL to reclaim space. This problem can be mitigated by using automatic cleanup daemon, which dynamically plans cleanup operations based on update activity. Unless your load is completely predictable, it is not sensible to disable daemons altogether. One possible compromise is to set the parameters of the daemon so that it will only react to abnormally large amounts of update activity, thus ensuring that things don't get out of hand, and instead use a planned VACUUMto do batch work when the load is normal.
For those who do not use automatic cleanup, a typical approach is to schedule a database-wideVACUUM, which performs once a day during low-usage periods, supplemented by more frequent cleanups on heavily updated tables as needed (some installations with extremely high update rates will clean up their busiest tables every few minutes). If you have multiple databases in a cluster, don't forget VACUUMeach one, you will need the [vacuumdb] program.
Log maintenance
Types of logs
Compared with the resource monitoring mentioned earlier, logs record the past operation status. There are 2 types of logs inside AntDB environment, one is the database log and the other is the system log.
Formats similar to AntDB logs:

```
data_dir/pg_log/postgresql-2021-07-16_000000.csv
```
System logs

```
/var/log/messages
/var/log/secure
/var/log/cron
```
AntDB logs
Log parameters
There are several parameters that control the log output within AntDB, and we set these values by default when deploying the environment:

```
logging_collector = on                   # Switch to off to prevent the database from generating log files
log_destination = csvlog 
log_directory = pg_log 
log_truncate_on_rotation = on
log_rotation_age = '7d' 
log_rotation_size = '100MB' 
log_min_messages = error                 # Control which level of messages are output to the log file
log_min_error_statement = error          # Control which severe level of SQL errors will output SQL to the log file
log_duration = off
log_statement = 'ddl'                    # Control which SQL statements will be output to the log file
```
Log format
AntDB logs are all in pg_log in the data directory, with a csv ending file, you can choose the desired log file based on the time in the file name. log formats are as follows:

```
2022-12-27 16:44:38.187 CST,,,201659,,63aab076.313bb,1,,2022-12-27 16:44:38 CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,"","postmaster"
2022-12-27 16:44:38.187 CST,,,201659,,63aab076.313bb,2,,2022-12-27 16:44:38 CST,,0,LOG,00000,"starting PostgreSQL 13.3 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit",,,,,,,,,"","postmaster"
2022-12-27 16:44:38.187 CST,,,201659,,63aab076.313bb,3,,2022-12-27 16:44:38 CST,,0,LOG,00000,"listening on IPv4 address ""0.0.0.0"", port 5551",,,,,,,,,"","postmaster"
2022-12-27 16:44:38.187 CST,,,201659,,63aab076.313bb,4,,2022-12-27 16:44:38 CST,,0,LOG,00000,"listening on IPv6 address ""::"", port 5551",,,,,,,,,"","postmaster"
2022-12-27 16:44:38.188 CST,,,201659,,63aab076.313bb,5,,2022-12-27 16:44:38 CST,,0,LOG,00000,"listening on Unix socket ""/tmp/.s.PGSQL.5551""",,,,,,,,,"","postmaster"
2022-12-27 16:44:38.189 CST,,,201659,,63aab076.313bb,6,,2022-12-27 16:44:38 CST,,0,LOG,00000,"antdb license expiration time is 2023-01-26 16:44:37, license type is trail license",,,,,,,,,"","postmaster"
```
In order: timestamp with milliseconds, username, database name, process ID, client host:port number, session ID, line number per session, command label, session start time, virtual transaction ID, normal transaction ID, error severity, SQLSTATE code, error message, error message details, hint, internal query causing the error (if any), character count where the error was located, error context, user query causing the error (if any and enabled by log_min_error_statement), character count of the error location, location of the error in the AntDB source code (if log_error_verbosity is set to verbose), and application name.