Influxdb+grafana Business Data Visualization

It is necessary to understand the TPS and QPS of the database as an operational DBA, and what is TPS, the QPS, and the simple understanding is:

QPS: Number of queries per second, which is the number of DML operations per second on the database

TPS: Transaction per second, which is the number of DDL operations per second on the database

By understanding them, you can master the basic operational status of an instance

How to visualize them is an artifact of DBA, this chapter mainly introduces the implementation of simple Python code through Time series database (INFLUXDB) +grafana+

Monitor them from time to time, what is a time series database can be understood in other chapters, here is not too much to introduce

Let ' s go .....

1, direct yum install INFLUXDB, after installation to generate the default configuration file/etc/influxdb/influxdb.conf temporarily not modified

2. On/of Service

Service Influxdb Start/stop

3, INFLUXDB Database page Management Console (the default), this is only convenient for certain operations, specific operation or to the Terminal Input command influx, this through http://IP:8083 through the page login to add

A user name and password for Grafana connection influxdb use, user name password to define themselves

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8B/5C/wKioL1hKvFqipzqHAADpKCXGSYI319.png-wh_500x0-wm_3 -wmp_4-s_291862535.png "title=" 19.png "alt=" Wkiol1hkvfqipzqhaadpkcxgsyi319.png-wh_50 "/>

Select Create user and query to create user " Enter your password according to the actual input username " with PASSWORD '

[Email protected] ~]# influx

Visit https://enterprise.influxdata.com to register for updates, InfluxDB Server management, and monitoring.

Connected to http://localhost:8086 version 0.13.0

InfluxDB Shell version:0.13.0

This can be logged in, is not very familiar with the specific operation to see the other chapters of the blog.

Note: Database URL (default): http://localhost:8086 # #这个在后面的grafana会被用到, data transfer using

4. Yum Install Grafana Pack

Post-Install on/off service:/etc/init.d/grafana-server start/stop

5, Grafana page login, the default use of 3000 port:

192.168.1.114:3000 login name and password default is admin

6. Implement simultaneous operation of MySQL and influxdb by Python code

#注意: Python operation MySQL need to install Python-mysql driver package, Baidu download A, generally in the extracted directory after the execution of 1, Python setup.py build 2, python setup.py install

Python operation Influxdb also requires python-influxdb

$ pip Install influxdb$ pip install--upgrade influxdb$ pip uninstall Influxdb # #注意如果没有安装PIP自己百度安装吧.

7. Python script:

#!/usr/bin/env python

#_ *_ Coding:utf-8 _*_

Import MySQLdb

Import datetime

Import JSON

#qps

Import time

From influxdb import influxdbclient

#import influxdb

Try

Conn=mysqldb.connect (host= "192.168.15.104", user= "Dlan", passwd= "root123", port=3306)

Client=influxdbclient (host= ' 192.168.15.104 ', port=8086, username= ' root ', password= ' root ', database= ' Telegraf ')

Cur=conn.cursor ()

While True:

sql = "Show global status where Variable_name in (' Com_select ', ' com_insert ', ' com_delete ', ' com_update ', ' Com_insert_ Select ', ' uptime ') '

Cur.execute (SQL)

AA = Cur.fetchall ()

Aa=list (AA)

delete = Int (aa[0][1])

insert1 = Int (aa[1][1])

insert2 = Int (aa[2][1])

select = Int (aa[3][1])

update = INT (aa[4][1])

uptime1 = Int (aa[5][1])

Qps1=delete+insert1+insert2+select+update

Time.sleep (1)

While True:

sql = "Show global status where Variable_name in (' Com_select ', ' com_insert ', ' com_delete ', ' com_update ', ' Com_insert_ Select ', ' uptime ') '

Cur.execute (SQL)

AA = Cur.fetchall ()

AA = List (AA)

delete_2 = Int (aa[0][1])

insert_2 = Int (aa[1][1])

insert2_2 = Int (aa[2][1])

select_2 = Int (aa[3][1])

update_2 = Int (aa[4][1])

uptime2_2 = Int (aa[5][1])

QPS2 = delete_2 + insert_2 + insert2_2 + select_2 + update_2

Commit=qps2-qps1

Uptime=uptime2_2-uptime1

AA = (commit/uptime)

Json_body = [

{

"Measurement":' My_tps ',

"Tags": {

"Host": "Mycat"

},

"Fields": {

"Influxdb": "Qps1",

"QPS": AA

}

}

]

#aa = "query_per_sec host=mycat,role=db,influxdb=qps qps=%d"% (commit/uptime)

#aa = (commit/uptime)

#print Aa,json_body

Client.write_points (Json_body)

Break

Except Mysqldb.error,e:

Print "MySQL error%d:%s"% (E.args[0],e.args[1])

# #脚本需要注意4个地方: 1, connected to the database information, is the monitoring side of the

2. MySQL's QPS statistics collection of stored databases

3. The collected data turns the string into JSON format

Json_body = [

{

"Measurement":' My_tps ', # # #注意这个红色位置不能用双引号, estimated to be a python problem, Java with double quotes no problem, is a pit oh ~~~! This can be set you like, other do not modify, direct use

"Tags": {

"Host": "Mycat"

},

"Fields": {

"QPS": AA

}

}

]

4, need to add a database in Influxdb, example name: test_influxdb (custom definition), client=influxdbclient (host= ' 192.168.15.104 ', port=8086, username= ' Root ', password= ' root ', database= 'telegraf'), according to the actual definition, the following grafana will use this name

It is strongly recommended that the operation of the database be done through the terminal:

[Email protected] ~]# influx

Visit https://enterprise.influxdata.com to register for updates, InfluxDB Server management, and monitoring.

Connected to http://localhost:8086 version 0.13.0

InfluxDB Shell version:0.13.0

> CREATE DATABASE Test_influxdb

> show databases;

Name:databases

---------------

Name

Telegraf

_internal

Mytab

MyDB

Stress

Test_influxdb

8, the data collected in the Grafana configuration information:

1, add data source and configuration, click the Grafana chart drop-down single point data sources---"Add the data source

2. Configure Data source information

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/8B/5F/wKiom1hKxMzBp18UAAELp-fNRD8798.png "title=" 2222. PNG "alt=" Wkiom1hkxmzbp18uaaelp-fnrd8798.png "/>

3, Datshboards->news-> left upper small green lattice->add panel->graph

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/8B/60/wKiom1hKxZbjX76lAADP7jWJmn8978.png-wh_500x0-wm_3 -wmp_4-s_1858480114.png "title=" 333.png "alt=" Wkiom1hkxzbjx76laadp7jwjmn8978.png-wh_50 "/>

4. Select the metrics configuration as follows:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/8B/60/wKiom1hKxpnQa-PzAACwIdjGL_g174.png "title=" 33333. PNG "alt=" Wkiom1hkxpnqa-pzaacwidjgl_g174.png "/>

Explanation: 1, the name of the data source you just created

2. Add a query

3. Expand Query

4, here according to the previous JSON format "measurement":' My_tps ' this My_tps, can be understood as a Time series database table.

5. Select Host

6, choose the value of the tags tag json mycat

"Tags": {

"Host": "Mycat"

7, for the JSON "QPS": AA of the QSP, equivalent to fields, for field

8, for the statistical way, see the basic introduction of its INFLUXDB

9. Statistics are often

10, for the symbol of the curve, you can add multiple query in a diagram, so that each name corresponds to a different color

Finally click the Save button, at the top there is an icon, save after the selection has 4 blocks of the general's name just defined

At last:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/8B/5C/wKioL1hKyWjD55gxAADBrrEudhs918.png "title=" 44444. PNG "alt=" Wkiol1hkywjd55gxaadbrreudhs918.png "/>

# #这里多提点: By such a definition, you can collect some regular data from the business, which can be counted on-line, such as the Dau\pcu\acu in the game .... The site's PV and so on can be data collection visualization display ..... Complete
This Python script can be optimized, INFLUXDB can insert data in bulk, reliable message times is 2-3w write is no problem.

Python script starts Python mysql_qps.py & no background characters will stay stuck,

This article is from the "DBSpace" blog, so be sure to keep this source http://dbspace.blog.51cto.com/6873717/1881371

Influxdb+grafana Business Data Visualization