New Aria Operations True Visibility Management Pack Releases
I have been using Aria Operations and True Visibility Management Packs quite a bit recently. This is mostly to get visibility into databases that are being provisioned by VMware Data Services Manager (DSM). I just learnt that we released a bunch of new Management Packs (v9.1) only last week, including updated Management Packs for both PostgreSQL and MySQL. In this post, I will deploy a new PostgreSQL database via Data Services Manager (DSM) 2.0, add the necessary database configuration options and extensions, and then add the database to Aria Operations True Visibility for PostgreSQL databases for monitoring.
You might ask why I am so interested in this. We believe that DSM can give DBAs back the time that they currently spend on mundane tasks, such as DB provisioning, patching, backups, etc. They should instead use their time to bring value to their line of business. We feel that Aria Operations with True Visibility can help DBAs here, and assist them in identifying performance issues, deadlocks, optimising queries, etc.
Note: We are no longer using Customer Connect for distributing the Management Packs. We are now using the marketplace. Click this link for the PostgreSQL Management Pack download.
Step 1: Deploy PostgreSQL database with advanced options
When deploying databases via DSM 2.0, the option to add advanced database configuration options is available at deployment time. Thus, the necessary configuration options to allow “query” metrics to be gathered by True Visibility can be added before the database comes online for the first time. The options are documented here. These are:
- shared_preload_libraries = pg_stat_statements
- pg_stat_statements.max = 10000
- pg_stat_statements.track = all
Here is a screenshot of the Advanced Options being added to the database via the DSM UI. Be careful to make sure that there are no ‘white spaces’ around the parameter names or values.
There is also a requirement to create an extension in the database. The extension is database bound and must be created for each database that you wish to pull data from. We will do that next.
Step 2: Create pg_stat_statements Extension
To create an extension, you must connect to the database. The nice thing about DSM is that administrators can quickly copy the database connection string from the UI, as shown below. This is a connection string for a special user called pgadmin which DSM has created. It has full access to its own database, which avoids using the postgres ‘superuser’ and the postgres database itself.
Using psql command line tool, or the pgAdmin tool, you can now connect to the database and create the pg_stat_statements extension. This is needed to track planning and execution statistics of SQL queries. The section “postgres:***” is the connection string copied from DSM. The IP address has been obfuscated in the below example, fyi.
% psql postgres://pgadmin:591n8sSY7w9OYjutZQUD7wD2N5H9kf@xx.xx.xx.xx:5432/pg-vrops-02 psql (15.3, server 14.10 (VMware Postgres 14.10.0)) Type "help" for help. pg-vrops-02=# CREATE EXTENSION IF NOT EXISTS pg_stat_statements; CREATE EXTENSION pg-vrops-02=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' AND installed_version IS NOT NULL; name | default_version | installed_version | comment --------------------+-----------------+-------------------+------------------------------------------------------------------------ pg_stat_statements | 1.9 | 1.9 | track planning and execution statistics of all SQL statements executed (1 row)
Note that the version of pg_stat_statements will vary between different PostgreSQL versions. The database is now ready to be monitored by Aria Operations and the True Visibility Management Pack for PostgreSQL.
Step 3: Configure PostgreSQL True Visibility Management Pack
Login to your Aria Operations UI. From the Integrations section, click on Add and provide the details of the PostgreSQL Management Pack v9.1 which you have downloaded from the marketplace. After installing the Postgres Pack, it should be come visible in the repository shown below.
The next step is to add an account. Once again, the details from the pgadmin user connection string can be used to setup connectivity between the database and Aria Operations. One other important point to note is that if you want to monitor table metrics, ensure that the Monitor Tables option is set to True in the account setup. You can also adjust the number of queries and functions that you wish to have displayed in the dashboards (limited to 10 each by default).
You also have the option to validate the connection, and this should be working successfully before you save the account information. Once the account is saved, PostgresSQL dashboards should start to populate within a matter of moments.
Step 4: Validating that data is being captured
Below is a sample dashboard (PostgreSQL Overview) taken a few moment after I saved the account information in the previous step. I can see the Database Instance, the Database and PostgreSQL Queries. However, there are no Functions or Tables populated. One other item to highlight is that the Database Instance is displaying the IP address of the Pod on the database is running (DSM provisions databases onto Kubernetes clusters).
If you are doing this as part of a Proof Of Concept, or even just to verify to yourself that it is working, you might want to to add some sample tables and functions. For some sample tables, I typically use a simple dvdrental sample database. The tutorial outlines various ways in which the tables can be imported, such as the pg_restore command line or the pgAdmin tool. Once added, you should soon see the PostgreSQL Tables populated in the dashboard.
For functions, I usually just build a few simple ones from other tutorials that I have found. this first one just compares the two integers provided, and tells you which is larger or smaller, or if they are the same. These can be created using the psql command line too, or the pgAdmin Tool which has a built in PSQL Tool.
create or replace function my_Check (x integer default 0, y integer default 0) RETURNS text AS $BODY$ BEGIN IF x > y THEN return 'first parameter is higher than second parameter'; ELSIF x < y THEN return 'second parameter is higher than first parameter'; ELSE return ' both parameters are equal'; END IF; END; $BODY$ language 'plpgsql';
This next one simply adds and multiplies the two integers provided, and returns the result:
create or replace function mysummult (IN integer, IN integer, OUT w integer, OUT z integer) AS $BODY$ BEGIN w := $1 + $2; z := $1 * $2; END; $BODY$ language 'plpgsql';
This is how you would run those functions:
pg-vrops-02=# select my_Check(5,5); my_check ---------------------------- both parameters are equal (1 row) pg-vrops-02=# select my_Check(5,51); my_check ------------------------------------------------- second parameter is higher than first parameter (1 row) pg-vrops-02=# select my_Check(52,51); my_check ------------------------------------------------- first parameter is higher than second parameter (1 row) pg-vrops-02=# select mysummult(2,3); mysummult ----------- (5,6) (1 row) pg-vrops-02=# select mysummult(21,32); mysummult ----------- (53,672) (1 row) pg-vrops-02=# select mysummult(20,30); mysummult ----------- (50,600) (1 row)
And after a few moments, once the data has been captured, you should see the PostgreSQL Overview dashboard fully populated.
Summary
We can now see that the newly updated True Visibility Management Packs are working successfully with the latest versions of databases. This is great news for Data Services Manager 2.0 as we now have a reliable mechanism for monitoring databases which is included with VMware Cloud Foundation (VCF). Data Services Manager 2.0, Aria Operations and True Visibility are all part of the full stack VCF offering. This not only provides automation capabilities around database provisioning (perhaps carried out by the VI Admins or other parts of IT operations) but also frees up DBAs to monitor how well the database is performing.
