Using Docker to visualize MySQL performance schema

Posted in: MySQL, Technical Track

Last week, I was pleased to present at Percona Live Amsterdam 2015 regarding the importance of Performance Schema and how to begin approaching visualizing the data that is available to diagnose performance issues and fine tune your MySQL environment. You can download the slides from the Percona Live conference page.

The session highlighted using the ELK (Elasticsearch+Logstash+Kibana) stack to assist visualizing event data, in addition to graphite+graphana to visualize time-series data.

As many people who attend conferences are aware, the availability of internet access is sketchy at best. To combat this, the visualization stack that I created was strictly local utilizing Docker, specifically the Docker Toolbox.

The docker-compose.yml file that creates the stack is fairly straightforward. Since the session was about the upcoming changes to the Performance Schema in MySQL 5.7, I chose to use MySQL’s official 5.7 Docker image:

Pay attention to two aspects of this output.

  • I am creating a mysql_data container. Using a Data Volume Container is highly recommended when working with any data that should be persisted and not baked into the Docker image.
  • I am building the container using the `build` command. Throughout the entire docker-compose.yml file, I am adding additional functionality to base containers provided by the Docker Hub. For the MySQL container, the Dockerfile looks like this:
    https://gist.github.com/dtest/23fc9d36a612ff98072f#file-mysql-dockerfile

The rest of the docker-compose.yml file follows similar guidelines, and I won’t explain each aspect here. You can find the full docker-compose.yml file and the Dockerfiles for each component of the stack in the dtest/visualize-mysql github repository.

To bring the stack up, the steps are straightforward:

  1. Install Docker Toolbox on your machine
  2. Create the docker-machine:
    https://gist.github.com/dtest/23fc9d36a612ff98072f#file-create_machine-sh
  3. Clone the dtest/visualize-mysql repository and change into the parent directory.
    https://gist.github.com/dtest/23fc9d36a612ff98072f#file-clone-repo-sh
  4. Bring the environment up:
    https://gist.github.com/dtest/23fc9d36a612ff98072f#file-docker-up-sh
  5. Verify that the containers are running, noting that the data volume containers should have exited with status 0:
    https://gist.github.com/dtest/23fc9d36a612ff98072f#file-docker-ps-sh
  6. You can then point your browser to the Kibana (http://<docker-machine IP>:9292/) and Graphana (http://<docker-machine IP>:8000/) dashboards. The Graphana dashboard requires login, and the default credentials are admin/admin.

The dashboards for the presentation are not automatically loaded, so you can find some basic dashboards in the dashboards directory of the repository. Unfortunately, I did not export them before discarding the machine so have lost some of the fine-tuning changes I had made. I do intend to recreate them and even try out the newer versions of the ELK stack available.

The goal here is to show how easy it is to setup a local environment using Docker and existing images on the Docker Hub to get started with your own projects.

 

Discover more about our expertise in MySQL.

email

Interested in working with Derek? Schedule a tech call.

About the Author

Director of Managed Services, Open Source Databases
Derek Downey is the Director of Managed Services for the OpenSource Database practice at Pythian, helping to align technical and business objectives for the company and for our clients. Derek loves automating MySQL, implementing visualization strategies and creating repeatable training environments.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *