Icon

Optimizing and Monitoring SQL Server with Metis and Stackgres

Learn how to use Metis with Stackgress to optimize and monitor your SQL Server instance. This guide includes creating a Kubernetes cluster using Minikube, installing the Stackgress operator, and more.

By Metis

In this guide, we'll learn how to integrate Metis with Stackgress to monitor a PostgreSQL server deployed on a Kubernetes cluster. The objective is to set up a live database observability tool, Metis, to extract detailed server activity metrics and optimize performance. We'll utilize Stackgress, an operator for managing PostgreSQL Server instances on Kubernetes, to facilitate this integration.

Creating a Kubernetes Cluster with Minikube

To begin, we'll create a Kubernetes cluster using Minikube. This step is crucial for setting up the environment to deploy and manage the PostgreSQL server with Stackgress.

Installing the Stackgress Operator with Helm

Next, we'll install the Stackgress operator using Helm, a tool for managing applications within Kubernetes clusters. This operator will handle the deployment and configuration of the PostgreSQL server instance.

Configuring the PostgreSQL Server for Metis

We'll proceed by setting up the PostgreSQL server instance with the necessary configurations for Metis to connect and monitor the server effectively. This involves creating a user, granting permissions, and installing required extensions like PGstatstatements.

Deploying Metis for Live Monitoring

Finally, we'll deploy Metis to live monitor the PostgreSQL server. We'll configure Metis to connect to the server, set up the necessary permissions, and initiate the monitoring process. Metis will provide insights into server performance, query optimization, and database metrics.

Let's get started.

In this video, we will learn how to use Metis with Stackgress. Metis is a live database observability tool that connects to your SQL Server instance to extract details about server activity, including infrastructure metrics, databases, schemas, live queries, indexes, transactions, and more. It also offers insights on optimization, performance enhancement, and automated issue troubleshooting. On the other hand, Stackgress is a solution for deploying PostgreSQL Server on your Kubernetes cluster. It acts as an operator for managing the deployment of your PostgreSQL Server instance. In this video, we will create a Kubernetes cluster using Minikube.

We will install the Stackgress operator and create the PostgreSQL Server instance. Then, we will connect Metis to it for live monitoring. All the steps are detailed on our blog, where the commands I use are explained. You can refer to the blog for guidance. First, we must create a Kubernetes cluster. I will use Minikube, but you can choose any tool you prefer.

For example, Kind or AWS EKS, or any other platform you have. I prefer using Minikube for its convenience. I already have Minikube installed on my machine, so I won't be installing it again. You can find a link to the documentation on how to install it on your operating system, be it Windows, Linux, or macOS. The process should be universal. Essentially, it involves downloading the binary and installing it. Next, we need to create the Kubernetes cluster using Minikube.

Let's start by copying Minikube. Go to your terminal window and execute the command. Minikube is creating the Kubernetes cluster using Docker. Ensure Docker is installed; it will utilize two CPUs and around four gigs of memory. The setup duration varies based on your configuration and host, typically taking 30 to 60 seconds.

Obviously, your mileage may vary. At the very end, this command will also configure kubectl. This means your kubectl will be set up to interact with your Kubernetes cluster.

1
minikube start

What we need to do now is install the StackGres operator. An operator is a tool that can install and manage components within a Kubernetes cluster. It can monitor drifts, apply configurations, and ensure proper functionality. To install the StackGres operator, we can utilize Helm. Helm is a tool for installing components in a cluster. Since I already have Helm installed on my machine, I will skip that step.

But once again, there is documentation you can follow. It should work on Windows, Linux, MacOS, everywhere. It basically involves downloading tar.gz, untarring it, and moving the binary to the appropriate place. Once you have Helm, what we need to do is add the repository. This repository indicates where the stackgres operator is located. Helm will then know where to download it from and how to use it.

So, we want to add the StackGres repository. Let's proceed with adding it. Once added, we can then install the operator and create a new namespace named StackGres to organize everything efficiently.

2
Click here
3
Click "helm install --create - namespace"
4
Click "helm install --create - namespace"
5
Click "helm install --create - namespace"
6
Type "Ctrl + C"
7
Click here

Let's execute this command.

8
Click here
9
Type "Enter"

This command is now being sent to the repository to download the StackGres operator and initiate its installation in the Kubernetes cluster. After that, initialization processes need to occur in the background.

10
Click here

We can use another command to synchronously wait for everything to complete before running it.

11
Click "kubectl"
12
Click "kubectl wait -n"
13
Click "kubectl wait -n"
14
Click "kubectl wait"
15
Click "kubectl wait -n"
16
Type "Ctrl + C"
17
Click here
18
Click here
19
Type "Enter"

And once this command finishes, we will have the operator up and running, ready for us to use. Let's allow it some time to complete. Then, we can check what has been installed and configured on our Kubernetes cluster. We can run another command to retrieve all the pods from the stackgres namespace.

20
Click here
21
Click "kubectl"
22
Click "kubectl get pods -n stackgres -l"
23
Click "kubectl get pods -n stackgres -l"
24
Type "Ctrl + C"
25
Click here
26
Click here
27
Type "Enter"

After running the command, the operator is up and running in our Kubernetes cluster. Next, we will utilize the stackgres operator to establish a PostgreSQL server instance. It is essential to configure the server for Metis usage by granting permissions, installing required extensions, and completing other necessary tasks. Let's revisit the instructions.

28
Click here

The first thing we need to do is create a username and a password.

29
Click "kubectl -n stackgres create secret generic metis - use"
30
Click here

We will create the username "metis" with the password "admin123."

31
Click "eate - user - sql = " create user metis password"
32
Click "eate - user - sql = " create user metis password"
33
Click "admin123 '"
34
Click "admin123"
35
Click here

Feel free to use a different password instead of sticking with the default one.

36
Click "kubectl"
37
Click "kubectl"
38
Click "kubectl"
39
Type "Ctrl + C"
40
Click here

Metis will use this user to monitor your cluster and database server.

41
Click here
42
Type "Enter"
43
Click here

Once we have that, what we need to do now is create a script that will initialize the server correctly on the first run.

44
Click here

In this guide, we will create a user with a password.

45
Click "secretKeyRef :"
46
Click here

We will grant PG monitor permissions to this user in Metis.

47
Click "GRANT pg_monitor TO metis"
48
Click here

We will create a database named Metis and assign ownership to the Metis user.

49
Click "CREATE DATABASE metis OWNER"
50
Click here
51
Click "GRANT CONNECT ON DATABASE met"

Then we will grant connect permissions to the user named Metis.

52
Click "T ON DATABASE metis TO metis"

Finally, we will create the extension called PGstatstatements, which Metis utilizes to monitor your database.

53
Click "script :"

Let's copy this command and create the script.

54
Click "cat << EOF"
55
Type "Ctrl + C"
56
Click here

This command is for the bash shell.

57
Click here
58
Type "Enter"

If you are using a different shell, such as PowerShell, batch, or any other, you may need to adjust the syntax. In bash, it works just like that - copy and paste. Now, the script has been created.

59
Click here

We need to create the configuration and trigger the creation of the SQL server.

60
Click "we give pg_monitor permissions"

To create a cluster with Postgres version 15, follow these steps.

61
Click "'15 .0 '"
62
Click here

With 5 gigabytes of storage volume and using the script we just created.

63
Click "¹5Gi '"
64
Click here
65
Click "- sgScript :"
66
Click "cluster - scripts"
67
Click "cat << EOF"

So, once again, let's copy this command and run it.

68
Type "Ctrl + C"
69
Click here
70
Click here
71
Type "Enter"

This item will now be sent to your Staggers operator to set up the server and initialize it correctly.

72
Click here

It will take about one minute on my machine. We can monitor the progress using the "get pods" command.

73
Click here
74
Click "kubectl get pods"
75
Click "kubectl"
76
Click "kubectl get pods"
77
Type "Ctrl + C"
78
Click here
79
Click here

We can now see that the cluster is being created and initialized.

80
Type "Enter"

The Staggers operator, installed via Helm, is currently setting up the PostgreSQL server. The server will then initialize using the script prepared earlier. This script will create a user with the username and password configured as secrets in our cluster.

81
Type "Enter"

Let's run the command again to check if the pod is initializing. Wait a few more seconds. We should see that everything is up and running, indicating that the PostgreSQL server is initialized correctly with the triggered script.

82
Type "Enter"
83
Type "Enter"

And now, everything is ready for use.

84
Click here

We can verify that the database has been created properly with this command. It will list all the commands and all the databases.

85
Click "kubectl -n stackgres exec -it"
86
Click "kubectl -n stackgres exec -it clus"
87
Click "kubectl -n stackgres exec -it"
88
Type "Ctrl + C"
89
Click here
90
Click here
91
Type "Enter"

There is a database named "metis" with the owner "metis." All is good.

92
Click here

What we need to do now is to use the server created with Staggers. Next, we will utilize Metis to live monitor the server. Let's open the Metis application.

93
Click here
94
Click here

If it's your first time, you need to log in and go to monitoring. Then, click deploy.

95
Click "Deploy"

This wizard will guide you through the steps to deploy the Metis Docker container and monitor your database server live. Since we are hosting Postgres locally, I will select Postgres and proceed by clicking next.

96
Click "Postgres"
97
Click "Next →"

Metis explained how to configure the database server for its use. It involves creating the pg_stat_statement extension, setting up a new user, and granting pg_monitor and connect permissions to the database.

98
Click here
99
Click "agent should monitor"
100
Click "user"
101
Click "GRANT pg_monitor TO metis ;"
102
Click "monitor"
103
Click "monitor"

All the tasks we have completed for specifying the initialization script for the Staggers operator for the SQL server are done. Click "Next."

104
Click "Next →"

And now, I need to provide a connection string for connecting to the database.

105
Click "How To Run Metis On Top Of StackGres"

We can revisit the blog post to find a command that lists all services running in our cluster.

106
Click "kubectl get svc"
107
Click "kubectl get svc"
108
Click "stackgres"
109
Click "kubectl get svc -n stackgres"
110
Click "kubectl get svc -n stackgres"
111
Type "Ctrl + C"
112
Click here
113
Click here

One of the services available is the primary cluster with an external host name that can be used in the connection string.

114
Type "Enter"
115
Click "Cluster"
116
Click "< none >"

This host name is cluster.stagres.svc.cluster.local. This is the server name within the cluster.

117
Click here

The connection string we need to use is as follows.

118
Click "postgresql : // metis"
119
Click "postgresql : // metis"
120
Click "postgresql"
121
Type "Ctrl + C"
122
Click here

And we can copy it here.

123
Click "Enter host connection"
124
Type "Ctrl + V"

We want to connect to Postgres using the username "metis" and the password "admin123" to the server name we copied from the cluster. We will use the default port 5432 to access the database named Postgres.

125
Click "metis"
126
Click "metis"
127
Click "admin123@cluster.stackgres.svc.cluster.local"
128
Click "admin123@cluster.stackgres.svc.cluster.local"
129
Click "admin123@cluster.stackgres.svc.cluster.local"
130
Click "5432 / postgre"
131
Click "5432 / postgre"
132
Click ":"
133
Click ":"
134
Click "5432 / postgres"
135
Click "5432 / postgres ? sslmode = disable"

Metis will attempt to connect to all the databases available and disable SSL and TLS.

136
Click "5432 / postgres ? sslmode = disable"

This is what we need to do.

137
Click here

Now, click "Next." You can choose to run this command using Docker or Helm.

138
Click "Next →"

Since we use Helm to deploy items in our cluster, we will also utilize Helm here.

139
Click "Helm"

We need to copy this repository command to add the Metis repository for MMC.

140
Click "helm"
141
Click "helm"
142
Click "helm repo add metis https"
143
Click "metis https://charts.metisdata.io"
144
Click "--set API_KEY = ypEjSUURRw5sn5XFqSoLAaXi73HNHkav3ESj034"
145
Type "Ctrl + C"
146
Click here
147
Click here

We can add this repository. The repository has been successfully added.

148
Type "Enter"
149
Click here

The next step is to copy the command that will install MMC.

150
Click "helm repo add"
151
Click "helm repo add metis https"
152
Click "install"
153
Click "helm repo add metis https"
154
Type "Ctrl + C"
155
Click here
156
Click here

This command will install MMC using the API key. The API key will be unique to your Metis project and will utilize the connection string provided. Additionally, a tweak is required since everything was deployed in the namespace stackres.

157
Type "Ctrl + A"

We also need to extend this command to use the namespace stackres. Now, you can run this command to deploy MMC to the namespace stackres, where it will initialize.

158
Type " -n stackgres + Enter"
159
Click here

Once again, we can click "Done" here and return to the instructions.

160
Click "Done"
161
Click "How To Run Metis On Top Of StackGres"

And once again, we can try checking all the pods to see if everything is up and running.

162
Click "kubectl"
163
Click "kubectl get pods -n stackgres"
164
Click "kubectl get pods"
165
Type "Ctrl + C"
166
Click here
167
Click here
168
Type "Enter"

After running the command "get pods," you will observe that the MMC is deployed and running correctly.

169
Click "20s"
170
Click "Running"

So, at this point, the MMC is up and running in our cluster. MMC can now connect to the database. The MMC Metis Metadata Collector can connect to your server and offer live monitoring.

171
Click here

We can return to the monitoring page.

172
Click here
173
Click here

We can refresh this page.

174
Type "Ctrl + R"

We should now see a new server named cluster stackres-svc-cluster-local with two databases.

175
Click here

One of them is the default Postgres, and the other is the database Metis. Metis has started collecting various metrics and configurations. You can see that we do not have data yet because we have just created the observability agent. However, we already have configurations.

176
Click "Show More"

We can check your Postgres configuration and provide insights on how to reconfigure the server for optimal performance.

177
Click here
178
Click here
179
Click here
180
Click "Monitoring"

What we can also do is go to the database. We can see that we have started capturing metrics, and they will start flowing in every minute. We have also begun capturing other data.

181
Click here
182
Click here

So, what we can do now is create a table. Let's input some data and see if Metis can capture it.

183
Click "How To Run Metis On Top Of StackGres"

We can now run a command to create a table.

184
Click here
185
Click "" CREATE TABLE orders ( region VARCHA"
186
Click "" CREATE TABLE orders ( region VARCHA"
187
Click "" CREATE TABLE orders ( region VARCHA"

Let's get started.

188
Click here
189
Click here
190
Type "Enter"

We made a table.

191
Click here

We can run a command to insert the products we have created into the table.

192
Click "kubectl -n stackgres exec -it cluster - 0 -c postg"
193
Click "'"
194
Click ","
195
Click "'"
196
Type "Ctrl + C"
197
Click here
198
Click here
199
Type "Ctrl + Enter"

And then we can also run another command that will execute a select statement to check if the data is present.

200
Click here
201
Click ") ""
202
Type "Ctrl + C"
203
Click here
204
Click here

And you can see that, yes, we do have some data.

205
Type "Enter"

We have products and sample entries.

206
Click here

We can now return to our monitoring. Refresh the page to see that Metis is running tasks every minute.

207
Click "Metis"
208
Click "Monitoring"
209
Click here
210
Type "Ctrl + R"

Last time, it updated data about seven seconds ago.

211
Click here

So let's check if it captured the table we just made. We can see connections coming in. Click on "metis" to view the new table we created.

212
Click here
213
Click "orders"
214
Click here

And also, we began receiving data points for transactions, rows, and other items. This is how you can integrate Metis. Metis will now analyze the activities in your cluster. For example, it will analyze schemas as more data becomes available.

215
Click "Show More"

It can even analyze live queries that go to your database. All of that is available out of the box once you connect the Metis observability agent to your server.

216
Click "How To Run Metis On Top Of StackGres"
217
Click here
218
Click here
219
Click here