Monitor your databases with this open supply instrument

I’ve been utilizing databases for lots longer than I care to confess, and numerous that point has been spent wanting on the entrails of servers, making an attempt to find out precisely what they have been doing. Thank goodness for the engineers behind the MySQL Performance Schema and Information Schema and their efforts to offer strong data. And then got here the Sys schema with useful prepackaged peeks on the server. Before the arrival of these schemas, there was no simple option to get granular details about a database occasion.

But peering at tabulated shows on the data of 1 time limit doesn’t enable for trend-spotting or a fast look to determine a server’s standing. Being in a position to spot a development on a graph or have alerts despatched when a threshold is reached is significant. My associates within the PostgreSQL and MongoDB worlds had the identical downside. The excellent news is that there’s an open supply resolution for all three databases that’s simple to put in and use.

I’m a reasonably new worker at Percona however have been round MySQL for a lengthy time. One of my first targets was to discover ways to arrange and use Percona Monitoring and Measurement (PMM). I do know a complete slew of parents who use it fortunately, however I had solely labored with it for just a few moments at a commerce present. My pure trepidation at putting in something that offered graphical data linked to a database was established properly earlier than open supply databases have been round. This hesitation is rooted in makes an attempt to configure different monitoring software program for proprietary databases.

The TL;DR is that PMM is straightforward to put in and use. The documentation is properly written, detailed, and useful. The software program itself is straightforward to acquire and set up. The general expertise is a ten out of ten.

Test case

I prefer to attempt new stuff on previous laptops, so I put in a recent copy of Ubuntu 20.04 LTS. Next, I learn the PMM documentation.

I ought to say I learn the documentation twice whereas muttering it could possibly’t be that easy. At the top of this text you’ll find the instructions I entered to put in the PMM server and the brokers (MySQL, PostgreSQL, and Mongo). It’s a cookbook exhibiting learn how to recreate this take a look at case.

Here are the Percona Database Distributions:

Prerequisite

The prerequisite for putting in PMM is Docker, which proves to be essentially the most intensive a part of your complete set up. Thankfully the package deal administration software program for Ubuntu (apt) makes this quite simple. 

Installing the Percona Monitoring and Measurement server

Installing the PMM server is straightforward for anybody accustomed to the APT package deal supervisor. Pay consideration to the output that gives the URLs for connecting to the PMM server. In my case, the addresses have been https://127.0.0.1:443/, https://192.168.1.209:443/, and https://172.17.0.1:443/.

You must log in to the PMM dashboard (default account and password are admin and admin).

After a profitable login, the PMM shows a dashboard.

At this time, PMM is simply monitoring the underlying system and the database it makes use of for gathering statistics—that’s what the Monitored Nodes and Monitored DB counts within the above picture are each displaying 1.

The database purchasers

The purchasers monitor the database cases, and once more purchasers are easy to put in with the package deal supervisor. The subsequent step is registering the consumer with the server:

$ sudo pmm-admin config --server-insecure-tls --server-url=https://admin:admin@127.0.0.1

The ultimate steps are configuring the database server. In the instance beneath, Percona’s MySQL is put in, and an account is created for gathering statistics. Finally, the consumer collects data from the database occasion:

$ sudo pmm-admin add mysql --username=pmm --password=move --query-source=perfschema

For PostgreSQL and MongoDB, exchange the identify of the database on the add choice to the respective occasion you need to watch.

You will discover that the Monitored Node depend elevated to 2 above.

PPM offers you entry to many various views of what the server is doing. You can see the general well being of the server itself.

The basic MySQL dashboard shows the general well being of the system.

And you’ll be able to simply research the load on the system.

Or research particular person queries.

Wrap up

Percona Monitoring and Management is an open supply instrument to watch your MySQL, MongoDB, or PostgreSQL cases. It is straightforward to put in and offers nice perception into your servers.

Please let the creator have any suggestions or questions you will have on this topic.

Cookbook

The following are the instructions and responses wanted to put in PMM and Percona’s MySQL on a recent set up of Ubuntu 20.04 LTS.

1. Install Docker

$ sudo update-manager
$ apt-get set up ca-certificates curl gnupg lsudo sb-release
$ echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu 
  $(lsb_release -cs) stable" | sudo tee /and so on/apt/sources.checklist.d/docker.checklist > /dev/null
$ sudo apt-get replace

2. Install PMM Server

$ curl -fsSL https://www.percona.com/get/pmm | sudo /bin/bash
Gathering/downloading required elements, this will likely take a second
Checking docker set up - put in.
Starting PMM server...
Created PMM Data Volume: pmm-data
Created PMM Server: pmm-server
    Use the next command if you ever must replace your container by hand:
    docker run -d -p 443:443 --volumes-from pmm-data --name pmm-server --restart at all times percona/pmm-server:2
PMM Server has been efficiently setup on this technique!
You can entry your new server utilizing the considered one of following net addresses:
    https://127.0.0.1:443/
    https://192.168.1.209:443/
    https://172.17.0.1:443/
The default username is 'admin' and the password is 'admin' :)
Note: Some browsers might not belief the default SSL certificates whenever you first open one of many urls above.
If that is the case, Chrome customers might need to sort 'thisisunsafe' to bypass the warning.
Enjoy Percona Monitoring and Management!

3. Add the Percona repo

$ wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
$ sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
$ sudo apt-get replace
Get:1 http://repo.percona.com/percona/apt focal InRelease [15.8 kB]
Hit:2 http://us.archive.ubuntu.com/ubuntu focal InRelease                                                                     
Hit:3 http://us.archive.ubuntu.com/ubuntu focal-updates InRelease                                
Hit:4 http://us.archive.ubuntu.com/ubuntu focal-backports InRelease                              
Hit:5 http://safety.ubuntu.com/ubuntu focal-security InRelease                                 
Get:6 http://repo.percona.com/prel/apt focal InRelease [9,779 B]                                 
Hit:7 https://obtain.docker.com/linux/ubuntu focal InRelease                                   
Get:8 http://repo.percona.com/percona/apt focal/fundamental Sources [4,509 B]
Get:9 http://repo.percona.com/percona/apt focal/fundamental amd64 Packages [18.1 kB]
Get:10 http://repo.percona.com/percona/apt focal/fundamental i386 Packages [414 B]
Get:11 http://repo.percona.com/prel/apt focal/fundamental i386 Packages [750 B]
Get:12 http://repo.percona.com/prel/apt focal/fundamental amd64 Packages [851 B]
Fetched 50.2 kB in 2s (22.7 kB/s)
Reading package deal lists... Done

4. Install the brokers

$ sudo apt-get set up pmm2-client
Reading package deal lists... Done
Building dependency tree       
Reading state data... Done
The following NEW packages might be put in:
  pmm2-client
0 upgraded, 1 newly put in, 0 to take away and 1 not upgraded.
Need to get 78.0 MB of archives.
After this operation, 195 MB of further disk area might be used.
Get:1 http://repo.percona.com/percona/apt focal/fundamental amd64 pmm2-client amd64 2.26.0-6.focal [78.0 MB]
Fetched 78.0 MB in 9s (9,078 kB/s)                                                                                            
Selecting beforehand unselected package deal pmm2-client.
(Reading database ... 144323 recordsdata and directories presently put in.)
Preparing to unpack .../pmm2-client_2.26.0-6.focal_amd64.deb ...
Adding system person `pmm-agent' (UID 127) ...
Adding new group `pmm-agent' (GID 134) ...
Adding new person `pmm-agent' (UID 127) with group `pmm-agent' ...
Creating residence listing `/usr/native/percona' ...
Unpacking pmm2-client (2.26.0-6.focal) ...
Setting up pmm2-client (2.26.0-6.focal) ...
Created symlink /and so on/systemd/system/multi-user.goal.needs/pmm-agent.service → /lib/systemd/system/pmm-agent.service.
$ sudo pmm-admin config --server-insecure-tls --server-url=https://admin:[email protected]:443
Checking native pmm-agent standing...
pmm-agent is operating.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/native/percona/pmm2/config/pmm-agent.yaml up to date.
Reloading pmm-agent configuration...
Configuration reloaded.
Checking native pmm-agent standing...
pmm-agent is operating.

5. Install Percona MySQL

$ wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
--2022-03-08 12:57:38-- https://repo.percona.com/apt/percona-release_latest.generic_all.deb
Resolving repo.percona.com (repo.percona.com)... 149.56.23.204
Connecting to repo.percona.com (repo.percona.com)|149.56.23.204|:443... related.
HTTP request despatched, awaiting response... 200 OK
Length: 11804 (12K) [application/x-debian-package]
Saving to: ‘percona-release_latest.generic_all.deb’

percona-release_latest.generic_ 100%[======================================================>] 11.53K --.-KB/s in 0s      

2022-03-08 12:57:38 (96.1 MB/s) - ‘percona-release_latest.generic_all.deb’ saved [11804/11804]


$ sudo dpkg -i percona-release_latest.generic_all.deb
(Reading database ... 144372 recordsdata and directories presently put in.)
Preparing to unpack percona-release_latest.generic_all.deb ...
Unpacking percona-release (1.0-27.generic) over (1.0-27.generic) ...
Setting up percona-release (1.0-27.generic) ...
* Enabling the Percona Original repository
<*> All carried out!
==> Please run "apt-get update" to use adjustments
* Enabling the Percona Release repository
<*> All carried out!
==> Please run "apt-get update" to use adjustments
The percona-release package deal now incorporates a percona-release script that may allow further repositories for our newer merchandise.

For instance, to allow the Percona Server 8.0 repository use:

  percona-release setup ps80

Note: To keep away from conflicts with older product variations, the percona-release setup command might disable our unique repository for some merchandise.

For extra data, please go to:
  https://www.percona.com/doc/percona-repo-config/percona-release.html


$ sudo percona-release setup pdps-8.0
* Disabling all Percona Repositories
* Enabling the Percona Distribution for MySQL 8.0 - PS repository
Hit:1 http://security.ubuntu.com/ubuntu focal-security InRelease
Get:2 http://repo.percona.com/pdps-8.0/apt focal InRelease [9,806 B]                              
Hit:3 http://us.archive.ubuntu.com/ubuntu focal InRelease                                                     
Hit:4 http://repo.percona.com/prel/apt focal InRelease                      
Hit:5 http://us.archive.ubuntu.com/ubuntu focal-updates InRelease           
Hit:6 https://download.docker.com/linux/ubuntu focal InRelease              
Hit:7 http://us.archive.ubuntu.com/ubuntu focal-backports InRelease
Get:8 http://repo.percona.com/pdps-8.0/apt focal/fundamental Sources [6,609 B]
Get:9 http://repo.percona.com/pdps-8.0/apt focal/fundamental amd64 Packages [67.4 kB]
Fetched 83.8 kB in 1s (63.1 kB/s)  
Reading package deal lists... Done


$ sudo apt set up percona-server-server

mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
    mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
    mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"


$ sudo apt set up percona-mysl-shell

mysqlsh root@localhost
SQL MODE
CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'move' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'localhost';

6. Start the consumer

$ sudo pmm-admin add mysql --username=pmm --password=move --query-source=perfschema
MySQL Service added.
Service ID : /service_id/d774faf2-fd3c-4758-9db9-3e1edb65b292
Service identify: test2-mysql
Table statistics assortment enabled (the restrict is 1000, the precise desk depend is 341).