Introduction to PingCap/TiDB/ Part-2 Monitoring TiDB MySQL data using Grafana
In the last article, we have seen how to install TiDB in window os using Wsl if you haven’t install TiDB yet you better go and have a look at the previous article.
In this section, we are going to see that how can we set up Grafana for TiDB and How it is used to monitor and visualize data from TiDB Mysql Data Source.
Step 1:Start the TiDB playground
The Prometheus and Grafana will be installed by default when we run the TiDB playground so the first step will be to start the playground using the following command.
Then we will get this information from the terminal.
Step 2:Start Grafana and change the default password
from the above information copy, the Grafana link, and past it to any browser.
we will get this login page of grafana.
The default username and password will be an admin for both. so log in with this information.
it will give you an optional page that prompts you to change the default password for username admin.
change the password and click on the Save button.
then after that, you will get the home page of the Grafana.
Step 3:Add Data Source
The next step is to indicate the source of data that will be displayed on Grafana.
To do this from the home page of Grafana on the left side there is a list of menus. go to the Configuration menu and click on the Data Source option.
Then you will get the Data source configuration page which exactly looks like this.
Click on the Add Data Source button at the top. and you will get a list of data source categories.
from the categories select the MySql category because our database that runs on top of the TiDB playground is MySQL. after this you will get a MySql Connection Page.
we need to give all the required information in order to make a connection with MySql.
The First is Host we should provide the Host IP address of our MySql.so let us go back to the MySQL running on our computer and select the Host IP address.
As you can see from the beginning there is an IP address with a specific port of 127.0.0.1:4000. my database name is FixItDb. the default User for my case is root and the password will be empty. so now I am going to provide all this information to the MySql Connection Page.
After that click on the Save and Test button at the bottom. and if all your information is valid and the connection made successfully you will get a green confirmation at the bottom.
At the top of this page, we can also update the name for the data source. later when we create a dashboard we will access this data source based on the name we specified now.
we have also an option to set it as a Default Data Source you can make it a default data source if you want it to appear by default whenever you create a new dashboard.
Step 4:Create A DashBoard
After we successfully added our data source the next step will be creating a dashboard to show the data.
to do this from the left menu options click on the + create a menu and when the list of menus comes select the dashboard option.
then select the Add Query option.
after that, you will get a dashboard in which the data came from the default data source.
so change the data source to the data source that we have created before. which is Service-DS.
after this, we will get our dashboard with No Data Points.
This is because we haven’t configured our dashboard yet.
Step 5:Configuring the Dashboard to get data
First, let us select the table we want to visualize in my case, it is the Service table.
This is the whole table information.
Because Grafana will do monitoring based on the date-time we should have the Date Time Column in our table so I have created one called Added Time. which indicates the time in which the particular service is added. so in the Time Column option of the dashboard, I am going to select Added Time.
At the bottom of the FROM option of the dashboard, you can see that there is a SELECT nominated row. from this, we can select the Column we want to display on the Grafana. but this column should be an integer and because of this Grafana by default will add only the integer data type column on the menu.
hit the + button on that row and add the Aggregate option and set it to the max and give the Alias name.
at the bottom, we have a WHERE nominated row. we can remove this if we don’t have a condition to select the column from the database. so select on the condition and you will get the remove button at the bottom.
immediately when removing the where condition the graph will be changed.
from this, we can understand that grafana was able to fetch the data from the Service-DS MySQL data source. but the graph is a straight line. this is because of the selected time.at the top of the dashboard next to the setting icon. there is some option which says like Last 6 hours click on this.
by default, the Grafana is displaying data that have been added 6 hours before now. but from our database, there is only one data that have been added before 6 hours.
so let me update the beginning time to something like this 2020-05-03 09:37:13 and the ending time to 2021-07-03 09:37:13 to include all the data’s time from my database table.
hit on the Apply button then our graph will be changed.
now if we hover on the graph we can see that the data will be changed from one place to another place. because the visualization includes all rows from the database table based on the Added Time
Step 6:Query Inspection
Alternativ to the graph we have also an option to look at the response object that comes from the data source.
from the dashboard select the button next to the Add Query button called Query Selector.
Then have a look at the Response to see the coming data.
Step 7:Adding more select option
If you want to see some other column on this dashboard you can simply do it by adding another column from the SELECT row.
From the SELECT option at the end, there is a + button. when you click on it you will get an option called Column from the listed menu options to click on that.
then provide information about the column you want to display.
change the Alias to the new column name, in order to differentiate the two columns.
when you hit enter the graph will be changed.
The new data on the graph looked a straight line because I have constant data for the Advanced Price Column which is 10.
That is all about this article. In the next article, we will see the integration of TiDB with .Net core API.