Manager Handbook for Centralized AntDB-T - P4–2
Tags: database
Published: 2023-08-15 09:11:50

System logs
System logs require root access (sudo is fine) to read
•/var/log/messages: system logs
•/var/log/secure: logs of user login, password authentication, and other security-related logs
•/var/log/cron: logs of crontab tasks
In the event of database problems, in addition to the database log itself, you also need to pay attention to the system log, often some error messages, tips and information will appear in the system log.
Log file maintenance
It is a good idea to keep the log output of the database server in one place, rather than just discard them by /dev/null. Log output is invaluable when diagnosing problems. However, log output can be large (especially at higher debugging levels), so you don't want to keep it endlessly. You need to rotate the log files so that after a reasonable amount of time a new log file is started and the old one is removed.
If you simply direct stderr to a file, you will get log output, but the only way to truncate that log file is to stop and restart the server.
A better approach is to send the server's stderr output to some sort of log rotation program. We have a built-in log rotator, which you can enable by setting the configuration parameterlogging_collector astrue inpostgresql.conf. You can also use this method to capture the log data in machine-readable CSV (comma-separated value) format.
If you start the server with pg_ctl, then stderr already redirects to stdout, so you only need a pipeline command, e.g.:

```
pg_ctl start | rotatelogs /var/log/pgsql_log 86400
```
You can combine these methods by setting logrotate to collect the log files generated by AntDB's built-in log collector. In this case, the log collector defines the names and locations of the log files, and logrotate archives these files periodically. When starting a log rotation, logrotate must ensure that the application sends further output to the new file. This is usually done through postrotatescript that sends theSIGHUPsignal to the application to reopen the log files. In AntDB, you can run pg_ctlby using the logrotateoption. After the server receives this command, the server will switch to the new log file or reopen the existing file, depending on the logging configuration.
Another way to manage log output at the production level is to send them to syslog and let syslog handle the file rotation. To use this tool, we need to set the configuration parameterlog_destination inpostgresql.conf  as syslog(to record sysloglog). Then you can send a SIGHUPsignal to the syslog daemon when you want to force it to start writing a new log file. If you want to automate the log rotation, you can configure the logrotate program to process the log files from syslog.
However, on many systems, syslog is not very reliable, especially with large numbers of log messages; it may truncate or discard those messages when you need them most. Also, on Linux, syslog will swipe each message to disk, which will result in poor performance (you can disable this behavior by using a "-" at the beginning of the file name in the syslog configuration file).
Note that all of the solutions described above focus on starting a new log file at a configurable interval, but they do not handle the deletion of old, no-longer-needed log files. You may also need to set up a batch task to delete old log files on a regular basis. Another possibility is to configure the log rotator to cyclically overwrite old log files.
Clean up the running logs
Database will generate a lot of running logs during its operation, occupying a lot of disk space, it is recommended to clean up the expired log files and keep only one-month logs.
Operation steps
**Step 1 **Login to the corresponding node of the database.
Step 2 Clean up the logs.

```
cd $LOGDIR  #Enter the corresponding subdirectory and use the following method to delete logs generated more than 1 month ago。
rm The naming format of log files is "postgresql-year-month-day_HHMMSS"。
```
Check time consistency
Database of high availability requires clock synchronization between the host and standby machines. Configure clock synchronization via ntpd.

```
systemctl start ntpd   #Start the ntpd service
systemctl status ntpd  #View the status of the ntpd service
ntpstat                #View the current clock synchronization information
```
Check the number of application connections
If the number of application connections to the database exceeds the maximum, new connections cannot be established. It is recommended to check the number of connections daily and release free connections or increase the maximum number of connections in time.
Execute the following SQL statement to check the number of connections and the related connection configuration.

```
# View the current number of connections
postgres=#  SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

# View the existing maximum number of connections
postgres=#  SHOW max_connections;
```
Exception handling
If the number of connections displayed is close to the maximum number of connections max_connections of the database, you need to consider cleaning up the existing connections or adding new connections.
Execute the following SQL statement to see the connection information for the state field equal to idle and the state_change field has not been updated for a long time.

```
postgres=#  SELECT * FROM pg_stat_activity where state='idle' order by state_change;
```
Kill database connection
AntDB provides 2 functions to kill connections. The former can only cancel the query being executed, and the latter is to break a backend connection. The input of the functions is the process number, which can be found by pg_stat_activity.
Remember, AntDB cannot kill connections at the OS level by kill -9 pid, which has a certain probability of causing a database restart!

```
Select pg_cancel_backend(pid)
Select pg_terminate_backend(pid)
```
Routine maintenance table
In order to ensure the effective operation of the database, the database must do VACUUM FULL and ANALYZE periodically after insert/delete operations based on customer scenarios to update the statistics for better performance.
Related concepts
The VACUUM, VACUUM FULL, and ANALYZE commands are used to perform regular maintenance on each table for the following main reasons:
● VACUUM FULL reclaims the disk space occupied by updated or deleted data while merging small data files.
● VACUUM maintains a visual mapping for each table to track pages containing arrays visible to other active transactions. A normal index scan first fetches the corresponding arrays through the visual mapping to check if they are visible to the current transaction. If it is not available, it is then checked by means of a heap array fetch. Thus updating the visual mapping of a table speeds up a unique index scan.
● VACUUM can avoid the loss of original data caused by overlapping transaction IDs when the number of executed transactions exceeds the database threshold.
● ANALYZE collects statistical information related to the table contents in the database. The statistics are stored in the system table PG_STATISTIC. The query optimizer uses these statistics to generate the most efficient execution plan.
Related operations
Use the VACUUM or VACUUM FULL command to perform disk space reclamation.

```
postgres=# VACUUM table_name;   #Perform VACUUM operation on the table
postgres=# VACUUM FULL table_name;
```
Use the ANALYZE statement to update the statistics.

```
postgres=# ANALYZE table_name;          #Perform ANALYZE operation on the table
postgres=# ANALYZE VERBOSE table_name;  #Use ANALYZE VERBOSE statement to update statistics and output related information of the table.
postgres=# VACUUM ANALYZE table_name;   #VACUUM ANALYZE command can also be executed simultaneously for query optimization
```
Maintenance suggestions
● Do VACUUM FULL for some large tables periodically, and do VACUUM FULL for the whole library after the performance decreases, and tentatively do VACUUM FULL once a month at present.
● Do VACUUM FULL for system tables periodically, mainly PG_ATTRIBUTE.
● Enable the system automatic cleanup thread (AUTOVACUUM) to perform VACUUM and ANALYZE automatically, reclaim the space of records marked as deleted, and update the statistics of tables.
Slow SQL diagnosis
When the performance of SQL statement execution does not meet expectations, you can view SQL statement execution information to facilitate post-analysis of the behavior of SQL statement execution and thus diagnose problems related to SQL statement execution.
Get slow SQL from pg_stat_statements plug-in:

```
postgres=# create extension pg_stat_statements;
postgres=# \d pg_stat_statements                #Identify slow SQL based on execution time statistics
```
Get slow SQL from pg_stat_activity:

```
postgres=# \d pg_stat_activity                 #Stat slow SQL by SQL start time
```