DEV Community

loading...
Cover image for Introduction to PingCap/TiDB/ Part-2 Monitoring TiDB MySQL data using Grafana

Introduction to PingCap/TiDB/ Part-2 Monitoring TiDB MySQL data using Grafana

Yared Solomon
Full Stack Web Developer(React js, Angular js, Node js,.Net Core, Golang,Django) and Flutter Mobile Application Developer
・6 min read

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.

tiup playground
Enter fullscreen mode Exit fullscreen mode

Then we will get this information from the terminal.

image

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.

image

The default username and password will be admin for both. so log in with this information.

it will give you an optional page that prompt you to change the default password for username admin.

image

change the password and click on the save button.

then after that, you will get the home page of the Grafana.

image

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.

Alt Text

Then you will get the Data source configuration page which exactly looks like this.

image
Click on the Add Data Source button at the top. and you will get a list of data sources categories.

image

from the categories select the MySql category because our database that runs on top of TiDB playground is MySQL. after this you will get a MySql Connection Page.

image

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.

image

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.

image

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.

image

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 menu and when the list of menus comes select the dashboard option.

Alt Text

then select the Add Query option.

image

after that, you will get a dashboard in which the data came from the default data source.

image

so change the data source to the data source that we have created before. which is Service-DS.

Alt Text

after this, we will get our dashboard with No Data Points.

image

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.

image

This is the whole table information.

image

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.

Alt Text

hit the + button on that row and add the Aggregate option and set it to max and give the Alias name.

image

at the bottom, we have 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.

Alt Text

immediately when removing the where condition the graph will be changed.

image

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.

image

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.

image

so let me update the beginning time to something like this 2020-05-03 09:37:13 and ending time to 2021-07-03 09:37:13 to include all the data's time from my database table.

image

hit on the Apply button then our graph will be changed.

image
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.

image

Then have a look at the Response to see the coming data.

image

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.

Alt Text

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.

image

when you hit enter the graph will be changed.

image

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.

Thank You!.

Discussion (0)