- The CS Proactive Trending Service (PTS) is a monitoring and analysis service where metrics about your database are collected and sent to Consistent State.
- These metrics are processed through a DSS based schema (isolated databases per customer), and an extensive set of trending graphs/reports are generated daily and staged on the customer web portal. Customers can access the web portal any time to view the most recent trending charts.
- The Consistent State staff of professional PostgreSQL DBAs then review each and every customer report on a monthly basis and generate 'Summary and Recommendations' that are staged on the customer portal.
- The Summary and Recommendations include recommended database administration steps designed to maintain reliability, performance and most of all no surprises within your PostgreSQL database servers.
- Summary and Recommendations items are broken out into categories of Critical, Warning and Informational
- The Consistent State team Actively manages the monthly recommendations over the course of each month, taking on a proactive admin type of role. The time to manage the recommendations is included in the base cost. Only when we're given to go-ahead for action (i.e. to implement specific recommendations) do we bill hourly or pull from Admin-Pack hours.
How It Works
The Data Collector
Data Collector (DC) is installed within the client network (one per data center, or physical network). The DC is easily configured to meet the ongoing needs of the client. The DC contains simple configuration meta-data that controls the servers to monitor, which data to pull and the poll frequency. The DC simply pulls system catalog metrics and ships a zipped file containing all the metrics for a poll to the CS network. No database data (user data) is shipped to the CS network. Note: some add-on packages require additional data such as system stats (iostat, sysstat) and the postgres log files, however these are not a requirement for the base service.
Secure Delivery of Metrics
After each metrics poll within the client network(s), an archive of the full set of metrics (from the most recent poll) is transported in a secure manner (via sftp) to a staging server within the CS network. The staging server is secured from intrusion. We implement several levels of security including encrypted key based access and a reduced command set. Non-critical commands such as ls and find have been disabled. The metrics archive files are then pulled for data processing and subsequently removed from the staging server. Access is only available via encrypted key access from both sides of the staging server.
Once the client poll metrics are securely staged in the CS network, then the data is extracted, transformed as needed to manage data integrity and then loaded into a DSS (Decision Support System) based data model. Each customer's data is isolated in its own database and the data transformation and DSS schema processing are all done within each database in an isolated environment. The DSS schema allows for the processing, analysis and reporting of metrics in a time-series or snapshot context, thus allowing the generation of accurate trending metrics across time. These trending metrics are customized within a continuous improvement framework to facilitate accurate, efficient analysis of your data.
Trending Graphs & Charts
An extensive set of trending graphs / charts are generated daily based on collected data. These charts are staged daily on a web portal which allows customer access at at any time. These trending graphs cover metrics associated with the following key areas within each monitored database cluster :
- Cluster Stats (global cluster stats)
- Database Stats (for each database within the cluster)
- Table Stats (top 10 table lists within a set of relevant metrics)
- Index Stats (top 10 index lists within a set of relevant metrics)
- Add-On Package Stats (for Add-On services such as SLONY, backup/recovery, and warm standby Add-Ons)
The set of generated graphs continues to grow and improve since the entire report generation and review process resides within a continuous improvement framework.
Analysis and Review
The Consistent State DBA staff reviews and analyzes the full set of trending reports for each client on a regular basis. The continuous improvement framework allows the Consistent State DBA staff to continually define new trend graphs and graph groupings. This helps us maximize efficiency by highlighting correlations that reveal cause and effect scenarios. As a result, our next step recommendations generally address the root cause(s) as opposed to recommendations that only address issue symptoms. All DBA feedback based on this analysis phase are then incorporated into monthly Summary and Recommendations data that is then staged / updated on the customer web portal. Each item recommending action becomes a ticket in our ticketing system (which customers have access to) so nothing gets lost.
Summary and Recommendations
Throughout each month the CS staff actively manage the recommendations & tickets. We initiate frequent communication with customers, taking on an 'admin role' to ensure that recommendations / tickets continue to move to completion. The CS Staff also responds to customer initiated tickets and manages before & after results for implemented action. Thus we maintain an audit trail of implemented actions and overall server results on an ongoing basis.
In addition to the base cluster monitoring service, we also offer a number of Add-On Packages. Add-On's add monitoring functionality for specific features outside of the base cluster metrics.
The currently available Add-On's are as follows:
- System Stats Monitoring - Includes metrics around memory, cpu and disk usage
- Query Monitoring - Leverages pgFouine to present metrics about weekly query activity
- DB Backups Monitoring - backup frequency & size related metrics
- Warm Standby Monitoring - Includes metrics around warm standby lag
- Table Partition Monitoring - Includes metrics about partition growth, size, usage, etc for all partitioned tables
Admin-Packs are recurring monthly blocks of consulting/admin hours from our DBA staff for use by our clients. These hours can be used to implement our recommendations, tune queries, research performance issues, etc. - anything related to the client PostgreSQL database clusters. Admin-Packs work like a cell phone plan; a specific number of hours are available each month, for a fixed cost, additional hours are billed at a pre-set rate. Packages are increasingly discounted as the number of monthly hours increases.