Archive for May, 2015

dBVigil (PostgreSQL monitoring application)

Logo

Main Activity:

Main

1. Environment Variables
2. Threshold
3. Sound / Vibrate Alert
4. Monitor
5. Backup / Restore
6. Maintenance

Environment Variables:

Environment

In order to connect to a database, user would first need to specify connection details in this section.
–> PostgreSQL < 9.2
Check this option to monitor a PostgreSQL database older than 9.2.x. If this is not setup properly the monitor will not display all checks.

–> Use SSL
Check this only if postgres database has been configured with open ssl, so that all data which is being sent / received to and from the database is encrypted. Private key authentication is currently being worked on and will be part of future release.

–> Hostname / IP
Enter the hostname (FQDN) or IP address of the database server.

–> Database name
This is the database name that you would like to monitor and maintain.

–> DB port
The port number that your database runs on. User can get this information from the postgresql.conf file or by looking at the show port psql output.

–> DB username
The postgres database username.

–> DB password
Password of the database user in the DB username field

After specifying the above it is a good practice to test connection using the “Test Connection” button. If there is an error you will see a pop up window with the error description. The timeout to check the connection is set to 20 seconds in case there are connectivity issues. You will see “Connection Sucessful” if the test connection passes.
Threshold:

Threshold

This screen is used to configure threshold limits for alert notifications. This is important if a user wants to receive real time alerts on the database. A mobile device will alert with sound and vibrate(if device is capable) incase a threshold is crossed.

Current thresholds include:

–> Connections: Monitors connection count and the threshold can be set from 0 to 1000. If you like to disable alerting on this particular check, slide the connection count to max (1000) and the alert for connections will be disabled.

–> Locks: Monitors number of locks in the database and threshold can be set from 0 to 10. If you like to disable alerting on this particular check, slide the locks count to max (10) and the alert for locks will be disabled.

–> Hung sessions: Monitors number of hung queries and the threshold can be set from 0 to 10. If you like to disable alerting on this particular check, slide the hung sessions count to max (10) and the alert for hung sessions will be disabled.

–> Hit %: Hit% of the entire database can be monitored using this threshold and the threshold can be set from 0 to 100. If you like to disable alerting on this particular check, slide the hit% count to mim (0) and the alert for hit% will be disabled. Lower the hit% the more seq scans are happening in the database.

–> Bloats: Number of bloated table threshold can be set from 0 to 10. If you like to disable alerting on this particular check, slide the bloats count to max (10) and the alert for bloats will be disabled.

–> Long_run: Number of long running queries threshold can be set from 0 to 10. If you like to disable alerting on this particular check, slide the long_run count to max (10) and the alert for long_running queries will be disabled.

–> Standby Lag: Standby replication lag threshold can be set from 0 to 1024. Currently this threshold is for all the standby servers using postgres inbuilt streaming replication. If you like to disable alerting on this particular check, slide the standby_lag mB to max (1024) and the alert for standby_lag will be disabled.
Sound / Vibrate Alert:

This button can be used to turn on / off all sound/vibrate alerts generated by dBVigil.
Monitor:

Monitor

The monitor screen is where you can see stats of your database at a glance. This screen shows the following information:

–> DB Conn’s : Total active database connections.
–> BG_Max : Total number of times background writer max value was exceeded.
–> Standby: Shows max lag on any of the standby servers.
–> Locks: Total number of active locks in the database.
–> Hung: Total number of hung queries (queries in idle in transaction mode for over a minute).
–> Long_run: Total number of long running queries over a minute.
–> Hit%: Hit% of the entire database.
–> Bloats: Total number of bloated tables.
–> DB_Size: Size of the current connected database.

Monitor Alert:

If any of the checks in the monitor screen passes a threshold value set in the application, the application will alert the user via sound and vibrate (if the sound and vibrate button is on) and the application will also notify the user which threshold has been crossed by blinking the corresponding check. If the sound and vibrate is set to off. The application will silently blink the check.

Check details:

Hung    Hit_ratio    Hung    Table_size

Every check in the Monitor screen shows more information about what the issue is. For eg: if you tap on the connections check, it will open another screen that will give a user information on which user is connected to the database, what ip address the user is connected from, how many active connections does the user have and whether or not the connection is in waiting state.

A user can tap on all the other checks as well in the monitor screen and will get relevant information about the check. This information can be helpful to further investigate the issue with the alert.
Active Monitoring:

Monitor Activity NEEDS TO BE OPEN if a user wants monitoring alert. A user can perform other activities on the device without interrupting active monitoring. Even if the device is in sleep mode dBVigil will actively monitor the database and send alerts. Since the application is for a mobile device I have made an effort to make it seamlessly work with network interruptions. This means that if network is lost / switched from mobile network to wifi, dBVigil will attempt to reconnect to the database to gather the current stats from the database as soon as the device gets a viable network to connect to.
Backup / Restore:

password_prompt    Backup

Backup and restore options are available using dBvigil:

–> Backup table to a backup table:
Specify the schema.tablename of the table to backup(in source table) and schema.table of the backup table(in backup to table) to backup the table and dBVigil will backup the source table for the user and notify when the backup completes.

–> Backup table to a file on the server:
Specify the schema.tablename of the table to backup(in source table) and full path and filename of backup file(in backup to file) to backup the table and dBVigil will backup the source table for the user and notify when the backup completes.

–> Restore backup of table from file:
Make sure there is a backup file that was used using COPY command in postgres. Specify the schema.tablename of the table to restore(table name) and full path and filename of backup file(in restore from file) to restore the table and dBVigil will restore the table for the user and notify when the restore completes.
Maintenance:

Maintenance    Reindex    reindex_completed    blocked_queries

Vacuum/Reindex and manage postgres processes:

The monitor screen will show and alert a user when there are issues with the database but in order to address some of the issues a user can take actions using the maintenance screen.

Kill Session/s

Every activity in this screen will prompt for the database user password that should match the password supplied in the environment variables screen. If the password does not match the task will fail.

Kill Session:
–> Kill blocking queries: This will kill all the blocking queries that are responsible for the locks locks shown in the monitor screen.
–> Kill hung sessions: Will kill all hung sessions that are in idle in transaction state for over a minute as shown in the monitor screen.
–> Kill by pid: Provide pid and click on the kill by pid button and this will terminate the process by pid.

Vacuum/Reindex DB

Use the appropriate button and provide the database user password to execute vacuum and reindex related jobs to run on the entire database. VacuumDB runs vacuum analyze on the entire database VS to vacuum full
Drill Down:

–> Database Vacuum: Specify a database name in the cluster to run vacuum analyze, this database name will be used for table vacuum and table reindex as well. Make sure the database name is specified.
–> Table Vacuum: Schema.table_name to run vaccum analyze.
–> Table Reindex Schema.table_name to run reindex.
FAQ’s

–> Does dBVigil work on cloud based postgres instances?
I have tested dBVigil on physical servers using VPN access, ec2 and RDS instances on amazon. dBVigil should work on other cloud instances as well as long as the jdbc driver can connect to a postgres instance.

–> How frequent is the data fetched from the database?
The checks on the monitor screen are divided into two blocks. Frequent checks and non frequent checks.
Frequent checks execute every 10 seconds and are listed below:

DB conn’s
BG_Max
Standby
Locks
Hung
Long_run

Non frequent checks run every 30 minutes (starting the time user opts to launch monitor screen)

Hit%
Bloats
DB_size

–> Is dBVigil secure?
dBVigil uses pg_catalog tables to gather statistics of the database. This is generic information of the database and does NOT extract data from any user created tables/functions/views etc. Moreover, using ssl connection a user can secure a database connection over mobile network. Future releases of dBVigil will have support for private key authentication over ssl encryption.

–> Is there proper connection management when using dBVigil?
Yes, I have made sure that every connection and every transaction that is called using dBVigil is closed before another request for connection is made. User will NOT see connections opened by dBVigil which are not being closed properly.

–> Limited visibility of query definition in details about various checks.
This is a limitation of android kitkat regarding nested horizontal scroll for rows. I wanted to add support for kitkat as a lot of users out there are still using kitkat. This issue will be addressed in upcoming release.

–> How many standby servers can I monitor?
The check on standby server in monitor queries for all added servers that the primary is aware of and reports the lag on the worst performing candidate.

–> Would there be multi server support
This concept will be an added feature in future releases.

–> Would dBVigil support different kinds of databases?
With the demand and requirement, implementation of mysql / other databases will be added in future releases.

–> How do I get support?
Send an email with questions/concerns to support@vikramrai.com

Leave a comment

%d bloggers like this: