Shulou(Shulou.com)06/01 Report--

It is necessary to understand the TPS and QPS of the database as an OPS DBA. What are TPS and QPS? the simple understanding is:

QPS: the number of queries per second, that is, the number of operations on the database's DML per second

TPS: transaction per second, that is, the number of DDL operations per second on the database

By understanding them, you can grasp the basic working status of an instance.

How to visualize their pages is an artifact of DBA. This chapter mainly introduces the simple python code implementation through time series database (influxdb) + grafana+.

Monitor them from time to time. What is a time series database can be learned in other chapters. I won't introduce them too much here.

Download of Let's go.3 packages

Http://down.51cto.com/data/2287378

Http://down.51cto.com/data/2287380

Http://down.51cto.com/data/2287379

1. YUM directly installs influxdb. After installation, the default configuration file / etc/influxdb/influxdb.conf is generated without modification.

2. ON/OF service

Service influxdb start/stop

3. Influxdb database web page management console (default), this is just to facilitate some operations, the specific operation is to enter the command influx to the terminal, which is added by page login through http://IP:8083

A user name and password, used for grafana connection INFLUXDB, user name password defined by itself

Select create user and then query and you will have CREATE USER "enter the user name according to the actual user name" WITH PASSWORD "enter your password"

[root@mycat ~] # 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, the specific operation of see other chapters of the blog.

Note: database url (default): http://localhost:8086 # # this grafana will be used later, data transfer will be used

4. Install GRAFANA package for YUM

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

5. Log in to the page of GRAFANA. Port 3000 is used by default:

The login name and password of 192.168.1.114virtual 3000 is admin by default.

6. Realize the simultaneous operation of MySQL and influxdb through Python code.

# Note: to operate MySQL with python, you need to install the driver package of python-mysql and download it yourself. Generally, execute 1, python setup.py build 2, and python setup.py install in the decompressed directory.

Python-influxdb is also required for python operation INFLUXDB

$pip install influxdb$ pip install-- upgrade influxdb$ pip uninstall influxdb # # Note if you don't install PIP, install it on Baidu yourself.

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])

# # there are four points to pay attention to in the script: 1. The information connected to the database is on the monitored side.

2. The stored database collected by MySQL's QPS statistics

3. The collected data converts the string into JSON format

Json_body = [

{

"measurement": 'my_tps', # Note that double quotation marks cannot be used in this red position. It is probably a problem with PYTHON. It is no problem for JAVA to use double quotation marks. It is a pit! This can be set to what you like. Don't modify the rest, just use it.

"tags": {

"host": "mycat"

}

"fields": {

"qps": aa

}

}

]

4. Add a database to influxdb. The name of the example is: test_influxdb (custom definition), client=InfluxDBClient (host='192.168.15.104', port=8086, username='root', password='root', database='telegraf'). According to the actual definition, this name will be used in the following GRAFANA.

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

[root@mycat ~] # 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. Configure the collected data information in grafana:

1. To add data sources and configurations, click the drop-down single point data sources--- "add data source" in grafana's chart.

2. Configure data source information

3. Datshboards- > news- > upper left green grid-> add panel- > graph

4. Select metrics and configure as follows:

Explanation: 1. The name of the data source just created

2. Add a query

3. Expand query

4. Here, according to the previous json format "measurement": 'my_tps' this my_tps, it can be understood as the table of the time series database.

5. Select HOST

6. Select the value mycat of the tags tag of JSON

"tags": {

"host": "mycat"

7. "qps" of JSON: qsp of aa, equivalent to field, and field

8. For statistics, see the basic introduction of its INFLUXDB.

9. Statistics often

10. For the logo of the curve, you can add multiple query to a graph, so that each name corresponds to a different color

Finally, click the save button, and there is an icon at the top. After saving, select the name of the general just defined in the 4 squares.

The final effect picture:

# # one more point here: through this definition, you can collect some routine data in the business and make statistics online, such as DAU\ PCU\ ACU.... in the game. The PV of the website, etc., can be visually displayed for data collection. Over.

This PYTHON script can be optimized, INFLUXDB can insert data in batches, and it is no problem that reliable messages are written in 2-3W times.

The PYTHON script starts python mysql_qps.py & it will stay stuck without a background character.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.