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:

mysql_data:
  container_name: mysql_data
  image: tianon/true
  volumes:
    - /var/lib/mysql
    - /var/lib/mysql-files

mysql:
  container_name: mysql
  environment:
    - MYSQL_ROOT_PASSWORD=plam15
    - MYSQL_DATABASE=world
  build: mysql
  volumes_from:
    - mysql_data
  volumes:
    - "./mysql/config:/etc/mysql/conf.d"
  expose:
    - "3306"
  ports:
    - "3306:3306"

sysbench:
  container_name: sysbench
  build: ./sysbench
  links:
    - "mysql"

elasticsearch_data:
  container_name: elasticsearch_data
  image: tianon/true
  volumes:
    - /usr/share/elasticsearch/data

elasticsearch:
  container_name: elasticsearch
  build: elasticsearch
  volumes_from:
    - elasticsearch_data
  command: -Des.node.name="PLE15"
  ports:
    - "9200:9200"

graphite:
  container_name: graphite
  image: kamon/grafana_graphite
  ports:
    - "8000:80"
    - "8126:8126"

logstash:
  container_name: logstash
  build: logstash
  volumes:
    - "./logstash/scripts:/opt/logstash/scripts"
  environment:
    - ES_PROXY_HOST=$DOCKER_HOST
  links:
    - mysql
    - graphite
    - "elasticsearch:es"
  ports:
    - "9292:9292"

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:

mysql_data:
  container_name: mysql_data
  image: tianon/true
  volumes:
    - /var/lib/mysql
    - /var/lib/mysql-files

mysql:
  container_name: mysql
  environment:
    - MYSQL_ROOT_PASSWORD=ple15
    - MYSQL_DATABASE=world
  build: mysql
  volumes_from:
    - mysql_data
  volumes:
    - "./mysql/config:/etc/mysql/conf.d"
  expose:
    - "3306"
  ports:
    - "3306:3306"

Pay attention to two aspects of this output.

First, 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.

Second, 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:

FROM mysql:5.7

ADD world_innodb.sql /docker-entrypoint-initdb.d/world.sql

EXPOSE 3306
CMD ["mysqld"]

The rest of the docker-compose file follows similar guidelines, and I won’t explain each aspect here. You can find 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:

   $ docker-machine create -d virtualbox \
   --virtualbox-memory "2048" --virtualbox-cpu-count "2" \
   visualize
   $ eval $(docker-machine env visualize)

3. Clone the dtest/visualize-mysql repository and change into the parent directory.

   $ git clone git@github.com:dtest/visualize-mysql.git
   $ cd visualize-mysql

4. Bring the environment up:

   $ docker-compose up -d

Verify that the containers are running, noting that the data volume containers should have exited with status 0:

$ docker-compose ps
       Name                     Command               State                            Ports
---------------------------------------------------------------------------------------------------------------------
elasticsearch        /docker-entrypoint.sh -Des ...   Up       0.0.0.0:9200->9200/tcp, 9300/tcp
elasticsearch_data   /true                            Exit 0
graphite             /usr/bin/supervisord             Up       0.0.0.0:8000->80/tcp, 8125/udp, 0.0.0.0:8126->8126/tcp
logstash             /app/bin/boot                    Up       0.0.0.0:9292->9292/tcp
mysql                /entrypoint.sh mysqld            Up       0.0.0.0:3306->3306/tcp
mysql_data           /true                            Exit 0
sysbench             /etc/entrypoint.sh -d            Up

You can then point your browser to the Kibana and Graphana 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.