Percona Monitoring and Management Query Analytics reporting
I couldn't find a tool which could generate weekly PMM "Query Analytics" reports for our developers, so I wrote pmm_rep to solve this problem.
This script will fetch data from the PMM qan-api and create a multipart email report containing both a html and a 79 width column fixed text version.
NOTE: The script is hardcoded to set begin date to the preceding monday. This should be easy to adjust yourself.
I recommend that this script runs in a local::lib environment and there is a postfix/ssmtp/sendmail relay service running on the platform (Linux) where you choose deploy it.
- Create a dedicated user.
sudo useradd -d /opt/pmm_reporter -m -s /bin/bash -c"pmm reporter" pmm_reporter sudo su - pmm_reporter
- Run the following as the dedicated user to set up a local::lib environment:
wget -O- https://cpanmin.us | perl - -l $HOME/perl5 App::cpanminus local::lib && echo 'eval `perl -I $HOME/perl5/lib/perl5 -Mlocal::lib`' >> $HOME/.bash_profile && echo 'export MANPATH=$HOME/perl5/man:$MANPATH' >> $HOME/.bash_profile . .bash_profile
- Install the following dependencies with cpanm again as the dedicated user:
cpanm ExtUtils::MakeMaker Mojolicious Text::Wrap Email::Stuffer Sub::Identify namespace::autoclean DateTime HTML::Strip Config::Auto
- Clone the repository:
git clone https://github.com/RasmusEdgar/pmm_rep.git
- Copy and adjust config (it should be self explanatory):
cp ./pmm_rep/config/config.example ./pmm_rep/config/config && vim ./pmm_rep/config/config
- Test if script is working by running it by hand.
cd ./pmm_rep/script && ./pmm_rep.pl
- Schedule a cronjob:
PATH=/opt/pmm_reporter/perl5/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/opt/pmm_reporter/.local/bin:/opt/pmm_reporter/bin PERL5LIB=/opt/pmm_reporter/perl5/lib/perl5 PERL_MM_OPT=INSTALL_BASE=/opt/pmm_reporter/perl5 30 10 * * 1 cd /opt/pmm_reporter/pmm_rep/script && ./pmm_rep.pl > /dev/null 2>&1