2020-12-08

数据库

Monitor MariaDB database on the arm64 platform

Author: zhaorenhai

This article attempts to explore some useful performance monitoring tools on the arm64 platform that can be used to monitor MariaDB databases.

The test platform still uses Huawei Cloud’s Kunpeng virtual machine, and the OS uses Ubuntu 18.04. A pair of MariaDB primary-replica databases have been deployed on other virtual machines in the same intranet in advance, and database user names and passwords for remote connections have been established. MariaDB uses the 10.1 version that comes with Ubuntu.

This article is going to test four free and open source tools including two command line tools, and two large graphical operation and maintenance tools.

The two command line tools are innotop and mytop.

Graphical operation and maintenance tools are Zabbix and Prometheus.

innotop

Innotop is a monitoring tool written in Perl. The source code is here https://github.com/innotop/innotop .

We directly download the latest code.

1	git clone https://github.com/innotop/innotop

Compile:

1 2	cd innotop perl Makefile.PL

installation:

1	sudo make install

Run (-w parameter represents the persistence mode, the configuration will be persisted to the file):

1	innotop -w

Enter ? , you can see the instructions. As follows:

Query List (? for help)                                                                                                                                                                                                 Servers: server

Switch to a different mode:
   A  Dashboard         I  InnoDB I/O Info     Q  Query List
   B  InnoDB Buffers    K  InnoDB Lock Waits   R  InnoDB Row Ops
   C  Command Summary   L  Locks               S  Variables & Status
   D  InnoDB Deadlocks  M  Replication Status  T  InnoDB Txns
   F  InnoDB FK Err     O  Open Tables         U  User Statistics

Actions:
   a  Toggle the innotop process    n  Switch to the next connection
   c  Choose visible columns        p  Pause innotop
   d  Change refresh interval       q  Quit innotop
   e  Explain a thread's query      r  Reverse sort order
   f  Show a thread's full query    s  Change the display's sort column
   h  Toggle the header on and off  t  Toggle slave processes
   i  Toggle idle processes         x  Kill a query
   k  Kill a query's connection

Other:
 TAB  Switch to the next server group   /  Quickly filter what you see
   !  Show license and warranty         =  Toggle aggregation

   #  Select/create server groups       @  Select/create server connections

   $  Edit configuration settings       \  Clear quick-filters
Press any key to continue

We enter an @ and press Enter to configure a database and to see the effect.

This is the first time running, there is no connection. You can input a name, name the new connection, and press Enter.

Then follow the prompts to enter the database address, port, configuration database address and port format as follows:

1	DBI:mysql:;host=192.168.0.204;port=3306

Then press Enter, you will be prompted for your username, password, etc. The prompts are very friendly.

After configuration, it can be used normally.

We can enter uppercase A, B, Q, I and other commands to switch between different interfaces to see the database cache information, IO information, TPS and so on.

In order to display multiple databases on the same interface, we can enter @ to configure multiple database links.

Then enter a # to create a server group, enter multiple connection names that have been established, separated by spaces, and that’s it.

When we select a server group during operation, multiple servers can be displayed on the same interface.

For example, we configured a primary database and a replica database and placed them in the server group.

The following is the IO interface of the two databases displayed, the effect is still very good:

InnoDB I/O Info (? for help)                                                                                                                                                                                            Servers: server

_____________________________ I/O Threads ______________________________
CXN     Thread  Purpose               Thread Status
master       0  insert buffer thread  waiting for completed aio requests
master       1  log thread            waiting for completed aio requests
master       2  read thread           waiting for completed aio requests
master       3  read thread           waiting for completed aio requests
master       4  read thread           waiting for completed aio requests
master       5  read thread           waiting for completed aio requests
master       6  write thread          waiting for completed aio requests
master       7  write thread          waiting for completed aio requests
master       8  write thread          waiting for completed aio requests
master       9  write thread          waiting for completed aio requests
slave        0  insert buffer thread  waiting for completed aio requests
slave        1  log thread            waiting for completed aio requests
slave        2  read thread           waiting for completed aio requests
slave        3  read thread           waiting for completed aio requests
slave        4  read thread           waiting for completed aio requests
slave        5  read thread           waiting for completed aio requests
slave        6  write thread          waiting for completed aio requests
slave        7  write thread          waiting for completed aio requests
slave        8  write thread          waiting for completed aio requests
slave        9  write thread          waiting for completed aio requests

________________________________ Pending I/O _________________________________
CXN     Async Rds  Async Wrt  IBuf Async Rds  Sync I/Os  Log Flushes  Log I/Os
master                                     0          0            0         0
slave                                      0          0            0         0

____________________________ File I/O Misc _____________________________
CXN     OS Reads  OS Writes  OS fsyncs  Reads/Sec  Writes/Sec  Bytes/Sec
master       178        984        372       0.00        0.00          0
slave        182       1529        584       0.00        0.00          0

_________________________ Log Statistics _________________________
CXN     Sequence No.  Flushed To  Last Checkpoint  IO Done  IO/Sec
master  1818551       1818551     1818551              207    0.00
slave   1939016       1939016     1939016              306    0.00

This article is not an introduction document for innotop, just to test whether innotop can run normally on the arm64 platform, and whether it can monitor the MariaDB database normally. Therefore, the introduction of other usage and interface of inntop is no longer wordy. Interested friends can try it. There are also a lot of innotop information on the Internet. You can also see the instructions by using command man innotop .

mytop

mytop is also a small tool written in Perl, which is widely used. However, the disadvantage is that it can only connect to one database server, and it is relatively old, its functions are relatively simple, and the information obtained is relatively limited.

mytop can be installed directly from the apt command:

1	sudo apt install mytop

To configure, edit the following files:

1	vi ~/.mytop

Enter the following:

user=proxysql
pass=proxypassword
host=192.168.0.204
db=testdb
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1

Execute:

mytop

The interface is as follows:

MySQL on 192.168.0.204 (10.1.47)                                                                                                                                             load 0.00 0.00 0.00 1/1397 32133 up 16+23:58:33 [17:45:26]
 Queries: 50.0     qps:    0 Slow:     0.0         Se/In/Up/De(%):    12/00/00/00
 Sorts:      0 qps now:    1 Slow qps: 0.0  Threads:    5 (   2/   7) 00/00/00/00
 Key Efficiency: 84.0%  Bps in/out:   0.0/  0.2   Now in/out:  21.3/ 3.6k

       Id      User         Host/IP         DB       Time    Cmd    State Query
       --      ----         -------         --       ----    ---    ----- ----------
       46 replicati hadoop-arm-kae-               1466725 Binlog Master h
       63  proxysql hadoop-arm-kae-       test          4  Sleep
       57   monitor hadoop-arm-kae-                     3  Sleep
      838   monitor hadoop-arm-kae-                     0  Sleep
    24644  proxysql hadoop-arm-kae-     testdb          0  Query     init show full processlist

You can see that the style is similar to top, and the effect is pretty good.

For more information about mytop, please refer to the following link: http://jeremy.zawodny.com/mysql/mytop/ . There are also a lot of relevant information on the Internet, and you can also see relevant instructions with command man mytop.

Zabbix

Zabbix is a large-scale monitoring tool, not only used to monitor the database, but here we simply use it to monitor MariaDB.

Regarding the detailed introduction of its structure, functions, etc., the official website includes a lot of information on other websites, so I won’t go into details here. Here only describes the steps of testing on our test environment. (The specific installation steps may be different for different versions of Zabbix, and different OSs are also different. The Zabbix3.0 version that comes with Ubuntu 18.04 is installed here, for reference only)

Firstly we install Zabbix Server and Zabbix Frontend on the test virtual machine.

zabbix-server has two versions, PostgreSql and Mysql, according to the built-in database it uses. In order to distinguish it from the target database MariaDB we want to monitor, we install the PostgreSql version here.

1	sudo apt install zabbix-server-pgsql zabbix-frontend-php php-pgsql

Install Zabbix Agent on the master and slave database nodes we want to monitor

1	sudo apt install zabbix-agent

Zabbix Agent will start automatically after installation.

Zabbix Server needs to be configured to log in to the node where the Server is located

First create a database for Zabbix Server’s own use

1 2	sudo -u postgres createuser --pwprompt zabbix sudo -u postgres createdb -O zabbix -E Unicode -T template0 zabbix

Then import the data

1
2
3

zcat /usr/share/zabbix-server-pgsql/schema.sql.gz |sudo -u zabbix psql zabbix
zcat /usr/share/zabbix-server-pgsql/images.sql.gz |sudo -u zabbix psql zabbix
zcat /usr/share/zabbix-server-pgsql/data.sql.gz |sudo -u zabbix psql zabbix

Configure the relevant information of the self-use database just set up in the Zabbix Server configuration file

1	vi /etc/zabbix/zabbix_server.conf

Ensure that the following configuration items are configured correctly

DBHost=localhost
DBName=zabbix
DBUser=zabbix
DBPassword=zabbix

Start Zabbix Server

1 2	service zabbix-server start update-rc.d zabbix-server enable

Start configuring Frontend

1
2
3

cd /etc/apache2/conf-enabled
ln -s ../conf-available/zabbix-frontend-php.conf zabbix-frontend-php.conf
vi zabbix-frontend-php.conf

Configure the time zone parameters inside correctly, the default is as follows:

1	# php_value date.timezone Europe/Riga

We changed to the following settings:

1	php_value date.timezone Asia/Shanghai

After changing to the correct time zone, proceed as follows:

1	service apache2 restart

Then access the following link from the browser (note that the address should be replaced with the address of your environment, and if you want to access from the public network address, pay attention to configure the HUAWEI CLOUD security group rules to limit the address range to be logged in.)

http://ip address of the test machine/zabbix

Then click next and follow the prompts to configure step by step.

Configure the database connection as shown

image-20201118144826675

Where the server is configured, just use the default value

image-20201118144923513

In the last step, you will be prompted to download a configuration file, download the configuration file and upload it to the /etc/zabbixdirectory.

image-20201118150217001

Finally restart apache2

1	service apache2 restart

Then re-login to the http://ip address of the test machine/zabbix web page, enter the user name Admin, password zabbix, and you can see the Zabbix system page.

Then we create two Hosts, one is the MariaDB master database and the other is the MariaDB slave database.

Click Configuration, Hosts, Create host, where Host name, Groups and IP address are required.

image-20201119101133383

After the host is created, you can click the Templates button circled in the above figure, go to the following page, enter mysql to search for the template, and then click the add button to associate the mysql monitoring template to the host

image-20201119101336228

The configuration of Frontend is complete.

1 2	cp /usr/share/doc/zabbix-agent/examples/userparameter_mysql.conf /etc/zabbix/zabbix_agentd.conf.d/ mkdir -p /var/lib/zabbix

Edit configuration file

1	vi /var/lib/zabbix/.my.cnf

Enter the following:

[mysql]
user=proxysql
password=proxypassword
host=127.0.0.1

[mysqladmin]
user=proxysql
password=proxypassword
host=127.0.0.1

Note that the entered user must be connected to MariaDB on this machine and have the relevant permissions to query.

Then edit the /etc/zabbix/zabbix_agentd.conffile to ensure that the configuration of the following three items is correct

1
2
3

Server=192.168.0.54
ServerActive=192.168.0.54
Hostname=mariadb_slave

Server and ServerActive are the IP addresses of Zabbix Server. And Hostname is the name of the Agent host name which you configured on Frontend, this configuration of each Agent is different.

Then restart the agent

1	service zabbix-agent restart

Now log in to the front end again, and you can see the monitoring effect.

Click Monitoring, Latest Data, you can see many monitoring items, bandwidth, slow query, select, insert, delete request qps and so on.

Let’s look at a bandwidth monitoring chart, the effect is as follows:

image-20201119102130275

If you feel that the default Mysql template function is not perfect enough, interested friends can customize the template by themselves, or download the customized template from the Percona website and import it. I won’t go into details here

Prometheus

Prometheus is also a large-scale monitoring tool. This time we will only briefly test its database monitoring function. I will not say more about its architecture, functions and other aspects.

Let’s first install Prometheus Server.

We download the latest arm64 version of Prometheus from the official website to install it.

1
2
3

wget https://github.com/prometheus/prometheus/releases/download/v2.22.2/prometheus-2.22.2.linux-arm64.tar.gz
tar -zxvf prometheus-2.22.2.linux-arm64.tar.gz
cd prometheus-2.22.2.linux-arm64

Download the latest mysqld_exporter on the master and slave database nodes to install

1
2
3

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.12.1/mysqld_exporter-0.12.1.linux-arm64.tar.gz
tar -zxvf mysqld_exporter-0.12.1.linux-arm64.tar.gz
cd mysqld_exporter-0.12.1.linux-arm64

Edit the file vi prometheus.ymland add the following configuration. The IP address inside is the IP of the master-slave database node, and the port is the port equivalent to the client process that we will install on the master-slave database node later. Let’s configure it first.

1
2
3

- job_name: 'mariadb'
  static_configs:
  - targets: ['192.168.0.204:9104','192.168.0.64:9104']

Start Prometheus

1	./prometheus --config.file = prometheus.yml

Edit the following files on the master and slave database nodes

1	vi .my.cnf

Enter the following content, pay attention to ensure that the entered user has the permission to connect to MariaDB locally and has the permission to query:

1
2
3

[client]
user=proxysql
password=proxypassword

Start mysqld_exporter

1	nohup ./mysqld_exporter --config.my-cnf=./.my.cnf &

Log in to the Prometheus page: (Note that the address should be replaced with the address of your environment. In addition, if you want to access from the public network address, pay attention to configuring the HUAWEI CLOUD security group rules to limit the address range to be logged in.)

[http://your IP address:9090](http://your IP address:9090)

You can see many indicators in the drop-down box next to the Execute button. We select a number of connections and adjust the monitoring time. For example, if we adjust it to the past 30 minutes, then we can see the monitoring chart as shown in the following figure.

image-20201120143145987

Summary

Through the above tests, we can find that these commonly used monitoring tools can all run well on the arm64 platform, and can monitor MariaDB databases with good results. And these tools are open source and free, interested friends can explore more functions.

Reference link:

https://www.cnblogs.com/ivictor/p/5101506.html

https://www.jianshu.com/p/b8508fe10b8e

https://github.com/innotop/innotop

https://github.com/jzawodn/mytop

http://jeremy.zawodny.com/mysql/mytop/

https://www.zabbix.com/documentation/4.0/zh/manual/introduction

https://prometheus.io/docs/prometheus/latest/getting_started/

https://www.cnblogs.com/heian99/p/12189317.html

https://prometheus.io/download/

# 数据库