The following reports are available for monitoring database activity:
● User Activity – Lists connected users and active SQL commands in execution
● Lock Status – Shows locks held on tables and indexes by users and what type of locks are held
● Buffer Cache – Shows count of table and index pages held in the buffer cache
● Configuration – Displays and allows for updating of database parameter settings for statistics collection
● View DB Logs – View the database server logs
● View Audit Logs – View the database server audit logs
Figure 10 DBA Management Server - Monitoring
This report contains information about current user activity within a specific database. This is primarly the number of active sessions associated with the database. The User Activity report contains the process ID, username, the time the current command was initiated, and the command being issued by that particular session.
Note: The current command appears in the report only if the stats_command_string parameter for the database is set to “true”. This parameter can be viewed and set in the Configuration report.
This report provides a real-time view of database activity. Keep in mind that there is a delay of approximately ten seconds between actual execution of the query and when it is reported in the database catalog. Hence this view lags the actual database activity by about ten seconds.
Ten seconds is the default refresh interval for this report. This default can be changed to any value between 5 - 99 seconds by typing in the desired refresh period in the Refresh Interval textbox.
Figure 11 User Activity
This report provides access to information about the locks held by open transactions within the database server. Each row shows an active lockable object, lock mode, the username, the transaction number, and the SQL command holding the lock. The same lockable object may appear mulitple times if multiple transactions are holding or waiting against that object.
This report can be particularly useful for detecting deadlocks; however, there is a delay of approximately ten seconds between actual execution of the SQL command and when it is reported in the database catalog. Hence this view lags the actual database activity by about ten seconds.
Ten seconds is the default refresh interval for this report, this default can be changed to any value between 5-99 seconds by typing in the desired refresh period in the Refresh Interval textbox.
Figure 12 Lock Status
Note: This report locks the pg_locks catalog table while it generates the output. Avoid refreshing this report too frequently as it will result in database performance degradation.
This report shows information about real-time queries on the shared buffer cache. Each row in this report shows the number of pages and amount of memory each relation or index is taking from the shared buffer cache.
Figure 13 Buffer Cache
Note: This report locks the pg_buffercache catalog table while it generates the output. Avoid refreshing this report too frequently as it will result in database performance degradation.
The parameters shown on this page are the configuration parameters that control the collection of runtime statistics for the database selected at the top of the report.
The message in red (as seen in the following figure) shows that the readOnly element of database mgmtsvr is set to “true” in the configure.xml file found in the mgmtsvr/server/default/deploy/edb-mgmtsvr.war/WEB-INF subdirectory of the Postgres Plus Advanced Server home directory.
Figure 14 Configuration – read-only mode
The following is the configure.xml file.
<?xml version="1.0" encoding="UTF-8"?> <config> <source> <host>localhost</host> <port>5445</port> <database>edb</database> <readOnly>false</readOnly> </source> <source> <host>localhost</host> <port>5445</port> <database>mgmtsvr</database> <readOnly>true</readOnly> </source> <source> <host>172.16.172.128</host> <port>5444</port> <database>edb</database> <readOnly>false</readOnly> </source> </config>
When the readOnly element is set to “false”, end users can freely change the status of all the parameters as shown in the following figure for database edb.
Figure 15 Configuration – writeable mode
The stats_reset_on_server_start and stats_start_collector check boxes are disabled as these parameters can be changed only at database server startup time.
To change any of the other parameter values, select the check-box and click on the Apply button.
The following is a description of each check box and the parameter it enables or disables for the database.
stats_block_level
This box must be checked to view the Memory Reads and Disk Reads bar charts on the home page. Furthermore, this box must be checked for accurately viewing all statistics related reports.
stats_command_string
When this box is checked the active SQL command of any new SQL Interactive session is displayed in the User Activity report.
stats_reset_on_server_start
If this box is checked and the database server is restarted, the following statistics are reset.
● Transactions bar chart
● Memory Reads bar chart
● Disk Reads bar chart
● Table IO Detail report
● Index IO Detail report
stats_row_level
When this box is checked, statistics are collected and displayed on the following reports.
● Table IO Detail report
● Index IO Detail report
stats_start_collector
When this box is checked the database server’s stats collector process is started upon database server start. This process is responsible for collection of database statistics. This box should be checked if either or both of the stats_block_level or stats_row_level boxes are checked.
Reset Collected Stats
When this checkbox is selected, the following report statistics are reset.
● Transactions bar chart
● Memory Reads bar chart
● Disk Reads bar chart
● Table IO Detail report
● Index IO Detail report
This feature provides the ability to browse through the database server log files. The log files of remote as well as local database servers can be viewed.
In order to produce a database server log file, the following configuration parameters must be set in the postgresql.conf file prior to database server startup.
● log_destination must include stderr
● redirect_stderr = on
● log_directory must be set to a valid existing, destination directory
● log_filename must be set to a valid file name pattern
The Log Name drop down list contains all the logs for the selected database server. By default, the most recent log file is displayed in the window. In the event the log file exceeds 1 MB, the latest 1 MB of the file is shown.
To view a specific part of a log file, specify values for the following text fields.
● Offset – Offset in bytes from the beginning of the log to begin viewing.
● Length – Length of the log in bytes to view.
Figure 16 View DB Logs
2.1.4.6 View Audit Logs
See Section 2.2.2.
