Total Pageviews

Tuesday, April 13, 2021

OpenEye - Time-shifting to compare metrics over time (side by side last 7 days)

Comparing metrics over time and plotting each series on the same time interval and then understand the recent trend is important for all IT professional. The time shifting comparison is critical for any professional to find out anomalies and to understand unpredicted behavior of a system.

As OpenEye is built on PostgreSQL and using Timescaldb Extension, it is quite easy to draw such graph using Grafana. Following is one of the sample query that uses PostgreSQL LATERAL JOIN and generate_series functions from PostgreSQL and time_bucket function from Timescaldb to accomplish such useful graph.

select
	time,
	avg_sql_cpu as "Avg CPU",
	case
		when step = 0 then 'Today'
		else grp_day
	end as grp_day
from
	(
	select
		step,
		(step || 'day')::interval as interval
	from generate_series(0, 6) g(step)) g_offsets
join lateral (
	select
		time_bucket('10m',utc_time + interval)::timestamp as time,
		TO_CHAR(local_time, 'Day') as grp_day,
		AVG(sql_percent_processor_time) as avg_sql_cpu
	from tbl_sql_process
	where utc_time between ($__timeFrom()::timestamp - interval) 
                        and ($__timeTo()::timestamp - interval)
		and sql_server = '$server'
	group by 1, 2
	order by 1 ) as l on true
order by step

Time-shifted Graph in OpenEye Dashboard:





Take a look at:

Posted by at No comments:
Labels: ,

Wednesday, April 7, 2021

OpenEye - Seeing what others cannot see

It is a daunting task to remotely collect performance metrics without impacting the servers being monitored, accurately process and store the streaming data and then visualize it. 

My next generation monitoring solution has been created to effectively tackle this task in an informative and visual manner. It is now ready to be used and integrated with on premises Windows and SQL Server.
Download Link: https://bit.ly/3t5kj1l
Screenshots: Various Screenshots


Goal of OpenEye: 

The purpose of this application is to quickly collect, process and visualize performance metrics while performing all complex and processor intensive computational task outside of the monitored server but to the collector (central) server. Following are few objectives of this tool:
  • Visualize critical operational data fastest way possible.
  • Eliminate data collection overhead from the monitored servers,
  • Remote, agentless and centralizing data collection process.
  • Quickly gain insight of any monitored server.
  • Deep analytics at any time using the familiar SQL query.

OpenEye as a Performance Data Collection Process:

OpenEye uses mostly raw WMI and low foot-print based T-SQL DMV queries. It collects commonly used all critical operational performance metrics as well as a number of unique performance data. This will allow a SQL Server database professional to understand the server health status and resource consumptions instantly.

OpenEye is a command line single executable application written in Free Pascal. It is highly portable as it has no dependencies and being a native machine code executable, it can run on any Windows Desktop or Windows Server without requiring any extra configurations or libraries.

A brief guideline to implement OpenEye:

To start using OpenEye data collection process and visualizing data in real-time, you need following open source application either on Windows or Linux:

  1. PostgreSQL 12.x or Timescaldb v2.0: Download and install PostgreSQL on a Windows or Linux machine. Optionally, you can configure the Timescaldb Extension v2.0 to take advantage of the high performance of Time Series database.
  2. Grafana V7.5 or later: This is one of the most user-friendly, efficient and lighting fast time series database visualization application.
  3. OpenEye.Exe: Download the openeye.zip. The zip file contains openeye.exe and a ready to use Grafana dashboard.
  4. Create a database in PostgreSQL as openeyedb. Any other database name would not work.
  5. Create a data source in Grafana as openeye. The data source name needs to be openeye as the dashboard is hardcoded with this data source name.
  6. In Grafana, import the dashboard supplied with zip file.
  7. On a Windows Desktop or Server, open a Windows Command Prompt and run the openeye.exe to collect performance data.

Syntax to use OpenEye:

OpenEye has been developed in such a way that it can collect performance data remotely from a single collector server. It can also be deployed on a monitored server without any special configuration.

Following is the general syntax.

  • Main Syntax:  OpenEye.exe [Server] [Milliseconds] [TARGET] [OPTION] [PRINT|TEST]
  • Windows Only: OpenEye.exe [Server] [Milliseconds] [WIN] [PRINT|TEST]
  • Default SQL Server: OpenEye.exe [SQLServer] [Milliseconds] [SQL]\[AGENT] [PRINT|TEST]
  • Named SQL Server Instance: OpenEye.exe [SQLServer]\[SQLInstance] [Milliseconds] [SQL]\[AGENT] [PRINT|TEST]

[Server]:  is the target monitored server. Value could be Windows Server Name, SQL Server Name or SQL Server Named Instance.

[Milliseconds]: Is the wait time in milliseconds to get the delta values from the monitored server.  This value can be set from starting from 1000 (1 second) milliseconds, however, the ideal value is 10000 milliseconds (10 seconds).

[Option]: Option can be WIN, SQL or SQL\AGENT.

  1. WIN: collect only Windows performance data.
  2. SQL: Collect Windows and SQL Server data without SQL Server Agent.
  3. SQL\AGENT: Collect Windows, SQL and SQL Server Agent performance data.

Note that the SQL Server Express Edition is not supported.

[Print]:  This option will show which section of performance data are being collected.
[Test]: This option will show which section of performance data are being collected without inserting to the back-end PostgreSQL database.

Example Syntax:

To collect data from a remote or local Windows Server (without collecting SQL Server Data):

C:\PerfFolder>OpenEye.exe HAWinSrv05 5000 WIN PRINT
C:\PerfFolder>OpenEye.exe WinSrv2016 5000 WIN P
C:\PerfFolder>Windows: OpenEye.exe Finance201 10000 WIN

To collect data from a remote or local default SQL Server instance with SQL Server Agent:

C:\PerfFolder>OpenEye.exe SRV2019 10000 SQL\AGENT

To collect data from a remote or local default SQL Server instance without SQL Server Agent:

C:\PerfFolder>OpenEye.exe SRV2019 10000 SQL

To collect data from a remote or local named Instance of SQL Server with SQL Server Agent:

C:\PerfFolder>OpenEye.exe SRV2019\SQL2019 10000 SQL\AGENT

To collect data from a remote or local named Instance of SQL Server without SQL Server Agent:

C:\PerfFolder>OpenEye.exe SRV2019\SQL2019 10000 SQL

Required Permission to Collect Performance Data:
OpenEye only supports AD/Windows Accounts to collect performance metrics data. The AD/Windows Account which is used to execute OpenEye.exe needs following permission on the target (local or remote) Windows and SQL Server.

  • To collect Windows Server Metrics: WMI Read permission on the target (local or remote) Windows Server.
  • To collect SQL Server Metrics: VIEW SERVER STATE and SELECT permission on master and msdb database on the target (local or remote) SQL Server.

To collect SQL Server performance metrics, both WMI and SQL Server permission are required. SQL Server authentication is not supported.


Required Permission for PostgreSQL Database:
Generally, read\write permission is required, However, few other permissions may be required if you want OpenEye.exe to create table, indexes, hypertable etc.

Create a database on a PostgreSQL Server and then create a user (user name can be anything) who can read and write to the database. Note that the database name must be openeyedb on the PostgreSQL Server. You can use following sample SQL Statement to create the database and a user on a PostgreSQL Server:

CREATE DATABASE openeyedb;
CREATE USER openeyeuser WITH PASSWORD 'openeye123';
GRANT ALL PRIVILEGES ON DATABASE openeyedb TO openeyeuser;

Configuration file - “OpenEye.Ini”:
To run OpenEye.exe update the default configuration file as needed. The default configuration file can be generated using the OpenEye.exe.

To generate a default configuration file in D:\PerfFolder, type D:\PerfFolder>OpenEye.exe and then press enter on a command prompt.

The default configuration file contains the following parameters.


[OpenEyeToPostgreSQL]
Server=localhost
Database=openeyedb
Username=openeyeuser
Password=openeye123
Port=5432

[Collection]
ReconnectAttempt=10
WaitForDelta=0
DatabaseStatusCheck=60
DatabaseGrowthCheck=300
MemoryClerkCheck=300
DatabaseIOCheck=300
WaitTypeCheck=300
DiskSpaceCheck=300
DatabaseIOCPUCheck=300
CacheCounterCheck=300
BlockingDurationCheck=60
HowOftenBlockingCheck=30
LongRunningDurationCheck=300
HowOftenLongRunningCheck=120

[OpenEyeToPostgreSQL]: This section contains the PostgreSQL server, database and user information. Feel free to adjust as per your PostgreSQL server configuration and requirements.

[Collection]: This section contains various parameters. When the data collection process continues, this sections will be checked by OpenEye.exe in every 30 seconds and OpenEye data collection process will adjust data collection according to the new value. Any change to any parameter will be effective automatically.

Configuration parameters and settings are all global and will affect all data collection process if the openeye.exe reads the same configuration file in the same folder.

All values in this section are in seconds.

  • ReconnectAttempt (default=10):  How many times the OpenEye.exe will try to reconnect before halting data collection process if there is a connectivity issue.
  • WaitForDelta (default=0): How long will the process wait to calculate delta? If this value is non-zero for example 5, 10 or 15 seconds, etc., then this value will override the “milliseconds” value which is used in the command line argument.
  • DatabaseStatusCheck (default=60): Duration to check database status, such as offline, read-only, suspect, recovery pending, and so on.
  • DatabaseGrowthCheck (default=300): Duration to check data and log space usage.
  • MemoryClerkCheck (default=300): Duration to check SQL Server Memory Clerk status (sys.dm_os_memory_clerks).
  • DatabaseIOCheck (default=300): Duration to check SQL Server virtual file stats (dm_io_virtual_file_stats).
  • WaitTypeCheck (default=300): Duration to check Waittype and Latch Status (sys.dm_os_wait_stats, sys.dm_os_latch_stats).
  • DiskSpaceCheck (default=300): Duration to check disk space utilization.
  • DatabaseIOCPUCheck (default=300): Duration to check CPU and IO consumption by each database.
  • CacheCounterCheck (default=300): Duration to check plan cache, cache counters and single use plan cache.
  • BlockingDurationCheck (default=60): Duration of SQL Blocking to check.
  • HowOftenBlockingCheck (default=30): How often blocking will be checked. This value paired with BlockingDurationCheck. This means that the OpenEye will check every 30 seconds to see if there is a blocking going on for more than 60 seconds.
  • LongRunningDurationCheck (default=300): Duration of Long Running queries to check.
  • HowOftenLongRunningCheck (default=120): How often the OpenEye will check long running queries. This value paired with LongRunningDurationCheck. This means that the OpenEye will check every 120 seconds (2 minutes) to see if there are any long running queries which has duration more than 300 seconds (5 minutes).


Creating the default Configuration “OpenEye.ini” file: The “openeye.ini” configuration is necessary to run the OpenEye data collector process. To create default “OpenEye.ini” file, follow the below steps.
  • Download and extract “OpenEye.zip” in a folder.
  • Open a command window and navigate to the “OpenEye.Exe” folder.
  • Type the below command on the command prompt without passing any values.

     D:\PerfFolder>OpenEye.exe 

Above action will cause to generate a default “OpenEye.ini”.

Creating Tables in PostgreSQL: OpenEye has built-in mechanism to create required tables and indexes. To create tables, follow below steps:

  • Create a new database as openeyedb in PostgreSQL Server.
  • Update the parameters value for server, database, user, password and port in “OpenEye.ini” file.
  • Open a Windows Command Window and navigate to the folder where the OpenEye.exe resides.
  • On the command prompt, type
        D:\PerfFolder>OpenEye Create
  •  All necessary tables and some basic indexes will be created in the database.

Testing local or Remote Server Connectivity for Data Collection: Before starting data collection, it is a good idea to test if you have permissions to collect performance data from a local or remote Windows and SQL Server. For example, we want to collet performance data form a remote SQL Server (which has a default SQL Server instance) SRV007FIN, follow the below steps.

  • Open a Windows Command Prompt and navigate to the folder where “OpenEye.exe” resides.
  • Type the following command and press enter on the command prompt.

D:\PerfFolder>OpenEye SRV007FIN 5000 SQL\AGENT TEST



Creating Hypertable for Timescaldb v2.0: First install and configure TimescaleDB v2.0 on the PostgreSQL Server. Add the extension to the backend database in PostgreSQL.  Next step is to convert PostgreSQL table tot a hyper table. To do this follow the below steps.

1.      Open the command window and then run the following command

C:\PerfFolder>OpenEye HyperTable

This will convert all OpenEye tables to TimescalDB's hypertable.

Collecting Real-Time Performance Metrics using Windows Task Scheduler:

To collect metrics data from multiple remote servers, Windows Scheduler Task can be used seamlessly. A Multiple Windows Scheduler task needs to be created to collect data from multiple servers. The AD/Windows Account which will be executing each task must have WMI and SQL Server permission on the target server:

  •  Security option: Make sure that the option “When running the task, use the following account” for the Windows Account which executes the OpenEye.Exe has WMI and SQL Server access permission on the target/remote server.
  • Create a Windows Scheduler Task with the following options:
  1. “Run whether user is logged on or not”;
  2. “Run with highest privileges”;
  3. “Hidden” and
  4. “Configure for:” choose the preferable server option. 
  • In the “Edit Action”, input the following information:

  1. Insert the location along with the OpenEye.exe. Example: D:\PerfFolder\OpenEye.exe
  2. “Add arguments (optional)”: Insert all the parameters without the executable name. Example: SHBMAIN\SQL2019 5000 SQL\AGENT
  3. “Start in (optional)”, insert the location of the executable. Example, D:\PerfFolder
  •  Schedule the task to run every XXX minutes, and make sure “Do not run a second instance” from the Settings of the task has been selected.
  • You may consider choosing “disable all task history” for smaller task histories.

Removing a Monitored Server from the Database: The syntax to Remove a monitored server’s data from the database (openeyedb) use the following syntax.

Syntax: OpenEye REMOVE [Server]

Where “server” is the Windows Server Name.

Example, permanently delete all records of a monitored server WinHRM209:

D:\PerfFolder> OpenEye REMOVE WinHRM209

Example, permanently delete all records of the monitored server, SHBMAIN

D:\PerfFolder> OpenEye REMOVE SHBMAIN


Deleting Older than X days’ data of a server from the database: Removing X days’ data of all monitored server from the database (openeyedb), use the following syntax

Syntax: OpenEye DELETE [days]

Where “days” is the number of days older than today.

  1. Example, to remove 1 days’ data, use: D:\PerfFolder>OpenEye DELETE 1
  2. Example, to remove 7 days’ data, use: D:\PerfFolder>OpenEye DELETE 7
  3. Example, to remove 30 days’ data, use: D:\PerfFolder>OpenEye DELETE 30

 
Step-by-step to Implement OpenEye:
 
Part One: Installing PostgreSQL and Grafana:
  • Download, install and configure PostgreSQL v12.0 from https://www.postgresql.org(Note: If you don’t want to use TimescalDB (https://www.timescale.com/) then you can try to use other PostgreSQL edition. But I did not test OpenEye with any other version of PostgreSQL as the TimescaleDB v2.0 does not support older than PostgreSQL 12.0).
  • Create a PostgreSQL database as openeyedb. Note, if the database name is not openeyedb then OpenEye data collection process will not work.
  • Create a user as openeyeuser with password openeye123. You can choose any user name and password.
  • Create a PostgreSQL data source in Grafana as openeye. Choose the TimeScaleDB v2.0 option if you configured the Timescaldb extension.
  • Assuming you have already download the OpenEye.zip from the download section above.
  • Extract it in a folder, for example D:\PerfFolder
  • Import the dashboard in Grafana from D:\PerfFolder. The dashboard file name is “Open Eye - Seeing What Others Cannot See.json”.

 Part Two: First time or Initial configuration

  • Open the Windows command prompt and navigate to D:\PerfFolder.
  • On the command prompt, type and press enter OpenEye.Exe. A default “OpenEye.ini” is created. 
        D:\PerfFolder>OpenEye
  • Open the “OpenEye.ini” using notepad and change the PostgreSQL server, user, password and the port number as per your configuration.
  • Make sure that the database name is openeyedb, otherwise data collection process would not work.
  • To create tables, on the command prompt type “OpenEye Create” and then press enter. 
         D:\PerfFolder>OpenEye Create
  • If you already install and configured TimescaleDB Extension for PostgreSQL, then convert the table to HyperTable as follows, otherwise skip this step.
        D:\PerfFolder>OpenEye HyperTable

Assuming that the OpenEye.zip is in D:\PerfFolder. Let’s perform the following steps to configure the ini file:

Part Three: Start collecting performance data with OpenEye:

For example, we need to collect performance data from the server “SHBMAIN” and “SQL2019” is the named instance of SQL Server 2019.

  • Make sure that you have appropriate permission to read WMI on the Windows Server, VIEW SERVER STATE and SELECT permission on master and msdb database on the target (local or remote) SQL Server.
  • Open the command Prompt and use the follow command to verify if the data collection process is successful. 

 D:\PerfFolder>OpenEye SHBMAIN\SQL2019 5000 SQL\AGENT TEST

  • If the data collection process succeeds, then you will see similar message as follows,

          Writing to the database server…. [192.168.0.51\openeyedb]

  • You can collect data continuously from the command prompt, with the following command:

 D:\PerfFolder>OpenEye SHBMAIN\SQL2019 5000 SQL\AGENT  

Or

D:\PerfFolder>OpenEye SHBMAIN\SQL2019 5000 SQL\AGENT PRINT

  • To collect data non-attendant, create a Windows Scheduler task without the “TEST” switch as follows:
        D:\PerfFolder>OpenEye SHBMAIN\SQL2019 5000 SQL\AGENT

Restrictions: OpenEye version in the download section is fully function data collection agent with two restrictions:

  • The freely distributed version can collect data from maximum 25 servers.
  • PostgreSQL database name must be openeyedb.

 Before using OpenEye:

  • Consider excluding the OpenEye.exe from the Anti-virus application.
  • WMI service and the WMI repository for SQL Server are fully functional on the local or remote server.
  • Use AD/Windows account Login when collecting data. SQL Login is not supported.
  • AD/Windows account should have WMI read permission on the target (local or remote) Windows Server.
  • AD/Windows account should have VIEW SERVER STATE and SELECT permission on master and msdb database on the target (local or remote) SQL Server

Helpful Link:

Documentations: Currently, I am working on to develop various documentations and I'll post and update the link. Stay safe! 

Posted by at No comments:
Labels: ,
Subscribe to: Posts (Atom)