Jun
20
2014
--

How to setup Docker for Percona ClusterControl and add existing Percona XtraDB Cluster

In my previous post I showed you how to setup Percona XtraDB Cluster 5.6 on Docker. This time I will show you how to setup Percona ClusterControl and add the existing Percona XtraDB Cluster 5.6 that we’ve managed to setup from the previous post.

Let us note the following details about our existing containers:

  • 172.17.0.2 dockerpxc1
  • 172.17.0.3 dockerpxc2
  • 172.17.0.4 dockerpxc3
  • 172.17.0.5 dockerccui-test

A quick tip for everyone who has followed my previous blog on setting up Percona XtraDB Cluster 5.6 on Docker: I did not install OpenSSH on the Docker instances on purpose and relied on ‘docker attach’ command to be able to get into each container. For this case however, we will need to install openssh-server in each container and make sure SSH is running as well as setup SSH key access for the Percona ClusterControl container to SSH into each Percona XtraDB Cluster node.

Create the Percona ClusterControl UI Docker container

We will need to create a docker container manually instead of building a container from a Dockerfile since we can’t run the ClusterControl installation non-interactively.

root@Perconallc-Support / # docker run --name dockerccui-test -p 80 -i -t ubuntu:12.04 bash

Notice that I had to add ‘-p 80′ to expose port 80 to the host network so we can access the Percona ClusterControl UI from a web browser, we will use Ubuntu 12.04 docker image. I will show you how to check the port that was dynamically allocated on the host network.

I would recommend to run ‘apt-get upgrade’ and ‘apt-get dist-upgrade’ just to make sure we have the latest software packages installed. Install wget and lsb-release packages since these are needed in the next steps.

Download and run the Percona ClusterControl installer and follow instructions on the prompt.

root@Perconallc-Support / # chmod +x install-cc.sh
root@Perconallc-Support / # ./install-cc.sh

The installer will give you several options, one of which is to install Percona Server as Percona ClusterControl’s backend database, I’d highly recommend to choose ‘Yes’. If everything goes well you will need to continue setup of the Percona ClusterControl on the web browser. If you missed installing lsb-release earlier then you will get an error midway through the installation, but you can always install lsb-release package and re-run the installation.

To identify the exposed port on the host’s side we will need to verify it:

root@Perconallc-Support / # docker inspect dockerccui-test | grep HostPort
                    "HostPort": "49154"
                    "HostPort": "49154"

As we can see, port 49154 was dynamically allocated to the host network and mapped to port 80 on the docker instance. You may explicitly set the port mapping on the host network ‘-p {hostPort}:{containerPort}’, please consult official Docker documentation for further reading.

Setup Percona ClusterControl on the web browser

We can now access the Percona ClusterControl user interface through http://{Host}:49154/clustercontrol. Use the username (in email form) that you indicated during the installation and the default password ‘admin’ to log in.

We should see the following page after successfully logging in:

Percona ClusterControl

Percona ClusterControl Wizard

Select ‘Add an existing cluster’ and click Next then follow further instructions to get to the next page:

add existing cluster

Add existing cluster

If all goes well you will be seeing the Database Clusters and can view your cluster nodes.

Percona ClusterControl

Percona ClusterControl UI on Docker container with Percona XtraDB Clusters 5.6 with each node on docker containers


Summary

In this blog I showed you how to setup Percona ClusterControl on Docker and adding an existing cluster to ClusterControl.

* Create a Docker container for Percona ClusterControl
* Download Percona ClusterControl and installed it
* Added existing Percona XtraDB Cluster on the Percona ClusterControl UI
* Profit!

You may also read the following blogs related to Percona ClusterControl:

For those who are new to Docker and containerization you may read through Patrick Galbraith’s blog series about Docker.

The post How to setup Docker for Percona ClusterControl and add existing Percona XtraDB Cluster appeared first on MySQL Performance Blog.

May
12
2014
--

Using Percona Server 5.6 with the Docker open-source engine

There are a couple of posts about setting up Percona XtraDB Cluster on Vagrant and Percona Server on MySQL Sandbox – those are two of the top tools used by the Percona Support team for testing and bug processing among other things.

In this post, however, I will show you how to use Docker with Percona Server on Ubuntu 12.04.

As per Docker’s official site:

Docker is an open-source engine that automates the deployment of any application as a lightweight, portable, self-sufficient container that will run virtually anywhere.

Docker containers can encapsulate any payload, and will run consistently on and between virtually any server. The same container that a developer builds and tests on a laptop will run at scale, in production*, on VMs, bare-metal servers, OpenStack clusters, public instances, or combinations of the above.

To install Docker on Ubuntu 12.04 you need to follow instructions from Docker’s official documentation:
http://docs.docker.io/installation/ubuntulinux/#ubuntu-precise-1204-lts-64-bit

After installing Docker, you may either download docker images via ‘docker pull’ and store docker images on your server so you can spin a new docker container in an instance or you may choose to do a ‘docker run’ on the terminal and implicitly download/store the specific docker image from index.docker.io and run the image afterward.

root@Perconallc-Support / # docker images | grep centos
centos              centos6             0b443ba03958        2 weeks ago         297.6 MB
centos              latest              0b443ba03958        2 weeks ago         297.6 MB
centos              6.4                 539c0211cd76        13 months ago       300.6 MB

Let us create a CentOS docker container by running the following command:

root@Perconallc-Support / # docker run -i -t centos:latest bash
bash-4.1# cat /etc/redhat-release
CentOS release 6.5 (Final)

As you may have noticed, we have just created a new interactive (-i) CentOS 6.5 docker container and ran bash in a single line of command. Detaching from the container is as easy as typing CTRL+p – CTRL+q, you’ll get to your terminal if you typed the right keys.

Verify the active containers:

root@Perconallc-Support / # docker ps
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS               NAMES
202765d754b7        centos:centos6      bash                11 minutes ago      Up 11 minutes                           elegant_rosalind

To list all existing containers active or not use the following command:

root@Perconallc-Support / # docker ps -a
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS                   PORTS               NAMES
202765d754b7        centos:centos6      bash                12 minutes ago      Up 12 minutes                                elegant_rosalind
aae47d193a22        centos:6.4          bash                2 hours ago         Exited (1) 2 hours ago                       boring_bardeen

To attach to the active docker container:

root@Perconallc-Support / # docker attach 202765d754b7
bash-4.1#

*Tip: Hit enter twice to get to the container’s bash prompt.*

Install Percona Server 5.6

Now that you have a working docker container you will then have to install the needed packages and repository.

bash-4.1# rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
Retrieving http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
Preparing...                ########################################### [100%]
   1:percona-release        ########################################### [100%]
bash-4.1# yum install Percona-Server-server-56 Percona-Server-client-56 Percona-Server-shared-56
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp.hosteurope.de
 * extras: ftp.plusline.de
 * updates: ftp.plusline.de
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package Percona-Server-client-56.x86_64 0:5.6.17-rel65.0.el6 will be installed
--> Processing Dependency: /usr/bin/perl for package: Percona-Server-client-56-5.6.17-rel65.0.el6.x86_64
---> Package Percona-Server-server-56.x86_64 0:5.6.17-rel65.0.el6 will be installed
--> Processing Dependency: libaio.so.1(LIBAIO_0.1)(64bit) for package: Percona-Server-server-56-5.6.17-rel65.0.el6.x86_64
--> Processing Dependency: libaio.so.1(LIBAIO_0.4)(64bit) for package: Percona-Server-server-56-5.6.17-rel65.0.el6.x86_64
--> Processing Dependency: libaio.so.1()(64bit) for package: Percona-Server-server-56-5.6.17-rel65.0.el6.x86_64
---> Package Percona-Server-shared-56.x86_64 0:5.6.17-rel65.0.el6 will be installed
--> Running transaction check
---> Package libaio.x86_64 0:0.3.107-10.el6 will be installed
---> Package perl.x86_64 4:5.10.1-136.el6 will be installed
--> Processing Dependency: perl-libs = 4:5.10.1-136.el6 for package: 4:perl-5.10.1-136.el6.x86_64
--> Processing Dependency: perl-libs for package: 4:perl-5.10.1-136.el6.x86_64
--> Processing Dependency: perl(version) for package: 4:perl-5.10.1-136.el6.x86_64
--> Processing Dependency: perl(Pod::Simple) for package: 4:perl-5.10.1-136.el6.x86_64
--> Processing Dependency: perl(Module::Pluggable) for package: 4:perl-5.10.1-136.el6.x86_64
--> Processing Dependency: libperl.so()(64bit) for package: 4:perl-5.10.1-136.el6.x86_64
--> Running transaction check
---> Package perl-Module-Pluggable.x86_64 1:3.90-136.el6 will be installed
---> Package perl-Pod-Simple.x86_64 1:3.13-136.el6 will be installed
--> Processing Dependency: perl(Pod::Escapes) >= 1.04 for package: 1:perl-Pod-Simple-3.13-136.el6.x86_64
---> Package perl-libs.x86_64 4:5.10.1-136.el6 will be installed
---> Package perl-version.x86_64 3:0.77-136.el6 will be installed
--> Running transaction check
---> Package perl-Pod-Escapes.x86_64 1:1.04-136.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
========================================================================================================================================================================
 Package                                           Arch                            Version                                       Repository                        Size
========================================================================================================================================================================
Installing:
 Percona-Server-client-56                          x86_64                          5.6.17-rel65.0.el6                            percona                          6.8 M
 Percona-Server-server-56                          x86_64                          5.6.17-rel65.0.el6                            percona                           19 M
 Percona-Server-shared-56                          x86_64                          5.6.17-rel65.0.el6                            percona                          714 k
Installing for dependencies:
 libaio                                            x86_64                          0.3.107-10.el6                                base                              21 k
 perl                                              x86_64                          4:5.10.1-136.el6                              base                              10 M
 perl-Module-Pluggable                             x86_64                          1:3.90-136.el6                                base                              40 k
 perl-Pod-Escapes                                  x86_64                          1:1.04-136.el6                                base                              32 k
 perl-Pod-Simple                                   x86_64                          1:3.13-136.el6                                base                             212 k
 perl-libs                                         x86_64                          4:5.10.1-136.el6                              base                             578 k
 perl-version                                      x86_64                          3:0.77-136.el6                                base                              51 k
Transaction Summary
========================================================================================================================================================================
Install      10 Package(s)
Total download size: 38 M
Installed size: 158 M
Is this ok [y/N]: y
Downloading Packages:
(1/10): Percona-Server-client-56-5.6.17-rel65.0.el6.x86_64.rpm                                                                                   | 6.8 MB     00:02
(2/10): Percona-Server-server-56-5.6.17-rel65.0.el6.x86_64.rpm                                                                                   |  19 MB     00:00
(3/10): Percona-Server-shared-56-5.6.17-rel65.0.el6.x86_64.rpm                                                                                   | 714 kB     00:00
(4/10): libaio-0.3.107-10.el6.x86_64.rpm                                                                                                         |  21 kB     00:00
(5/10): perl-5.10.1-136.el6.x86_64.rpm                                                                                                           |  10 MB     00:00
(6/10): perl-Module-Pluggable-3.90-136.el6.x86_64.rpm                                                                                            |  40 kB     00:00
(7/10): perl-Pod-Escapes-1.04-136.el6.x86_64.rpm                                                                                                 |  32 kB     00:00
(8/10): perl-Pod-Simple-3.13-136.el6.x86_64.rpm                                                                                                  | 212 kB     00:00
(9/10): perl-libs-5.10.1-136.el6.x86_64.rpm                                                                                                      | 578 kB     00:00
(10/10): perl-version-0.77-136.el6.x86_64.rpm                                                                                                    |  51 kB     00:00
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                   7.4 MB/s |  38 MB     00:05
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
** Found 2 pre-existing rpmdb problem(s), 'yum check' output follows:
udev-147-2.51.el6.x86_64 has missing requires of /sbin/service
udev-147-2.51.el6.x86_64 has missing requires of MAKEDEV >= ('0', '3.11', None)
  Installing : Percona-Server-shared-56-5.6.17-rel65.0.el6.x86_64                                                                                                  1/10
  Installing : 1:perl-Pod-Escapes-1.04-136.el6.x86_64                                                                                                              2/10
  Installing : 1:perl-Module-Pluggable-3.90-136.el6.x86_64                                                                                                         3/10
  Installing : 4:perl-libs-5.10.1-136.el6.x86_64                                                                                                                   4/10
  Installing : 3:perl-version-0.77-136.el6.x86_64                                                                                                                  5/10
  Installing : 1:perl-Pod-Simple-3.13-136.el6.x86_64                                                                                                               6/10
  Installing : 4:perl-5.10.1-136.el6.x86_64                                                                                                                        7/10
  Installing : Percona-Server-client-56-5.6.17-rel65.0.el6.x86_64                                                                                                  8/10
  Installing : libaio-0.3.107-10.el6.x86_64                                                                                                                        9/10
  Installing : Percona-Server-server-56-5.6.17-rel65.0.el6.x86_64                                                                                                 10/10
...
Installed:
  Percona-Server-client-56.x86_64 0:5.6.17-rel65.0.el6   Percona-Server-server-56.x86_64 0:5.6.17-rel65.0.el6   Percona-Server-shared-56.x86_64 0:5.6.17-rel65.0.el6
Dependency Installed:
  libaio.x86_64 0:0.3.107-10.el6           perl.x86_64 4:5.10.1-136.el6         perl-Module-Pluggable.x86_64 1:3.90-136.el6    perl-Pod-Escapes.x86_64 1:1.04-136.el6
  perl-Pod-Simple.x86_64 1:3.13-136.el6    perl-libs.x86_64 4:5.10.1-136.el6    perl-version.x86_64 3:0.77-136.el6
Complete!
bash-4.1# /etc/init.d/mysql start
Starting MySQL (Percona Server). SUCCESS!
bash-4.1# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.17-65.0-56 Percona Server (GPL), Release 65.0, Revision 587
Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

There you have it, a freshly installed Percona Server 5.6.17 on CentOS 6.5 Linux container. Yay!

You can now create a new MySQL user to access the server outside the container:

mysql> grant all privileges on *.* to 'test_user'@'%' identified by 'test_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
root@Perconallc-Support / # mysql -h 172.17.0.2 -utest_user -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.17-65.0-56 Percona Server (GPL), Release 65.0, Revision 587
Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

We’re basically following installation instructions for Percona Server from the documentation.

Just the same way as you would do on any server, you can install other equally important Percona software on the same container to complete your docker environment.

As an added bonus, you can contribute to the Docker Community by commiting your container and pushing it to the docker registry.

Summary

I’ve shown you how easy it is to spin a docker container and install Percona Server 5.6 in it. This is by far one of the fastest ways to create test/staging environments to simulate production servers. For further reading you may want to read Docker’s official documentation here.

I hope that this has piqued your interest to try it out yourself. And if that happens we would love to know how Percona Server and other Percona software perform on your docker environment. Cheers!

The post Using Percona Server 5.6 with the Docker open-source engine appeared first on MySQL Performance Blog.

Feb
03
2014
--

Percona Toolkit collection: pt-visual-explain

This is the first in a series of posts highlighting a few of the seldom-used but still handy Percona Toolkit tools.

Have you ever had a problem understanding the EXPLAIN statement output? And are you the type of person who would rather use the command line than a GUI application? Then I would recommend that you use Percona’s pt-visual-explain toolkit. This is one of many Percona Toolkit tools that is useful for those who want to have a different view and an easier time understanding the EXPLAIN output aside from the usual table and vertical views.

As described in the documentation – http://www.percona.com/doc/percona-toolkit/2.2/pt-visual-explain.html#description

pt-visual-explain reverse-engineers MySQL’s EXPLAIN output into a query execution plan, which it then formats as a left-deep tree – the same way the plan is represented inside MySQL.”

Our sample query:

SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)

In Table view the EXPLAIN output would look like:

$ mysql -Dworld -e "explain SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)"
+----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref                | rows | Extra |
+----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------+
|  1 | SIMPLE      | Country | ALL  | PRIMARY       | NULL        | NULL    | NULL               |  183 |       |
|  1 | SIMPLE      | City    | ref  | CountryCode   | CountryCode | 3       | world.Country.Code |    1 |       |
+----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------+

While on vertical view, the EXPLAIN output would be:

$ mysql -Dworld -e "explain SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)\G"
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Country
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 183
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: City
         type: ref
possible_keys: CountryCode
          key: CountryCode
      key_len: 3
          ref: world.Country.Code
         rows: 1
        Extra:

Using the tool…

The EXPLAIN output in pt-visual-explain tree format, I will be showing you three different ways on how to use this handy toolkit.

Piping input into pt-visual-explain, like the one shown below:

$ mysql -Dworld -e "explain SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)" | pt-visual-explain

Parsing input to a file and then pt-visual-explain will reverse-engineer the EXPLAIN output to a query execution plan and formats it to a tree view, as shown below:

$ mysql -Dworld -e "explain SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)" > explain.out
$ cat explain.out
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  Country ALL     PRIMARY NULL    NULL    NULL    183
1       SIMPLE  City    ref     CountryCode     CountryCode     3       world.Country.Code      1
$ pt-visual-explain explain.out

Create a file containing the query and then parse the file while pt-visual-explain connects to the mysql instance, like the example shown below:

$ echo "SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)" > select.out
$ pt-visual-explain -Dworld --connect select.out

The tree structure below would be the result if you run any of the three methods shown above.

JOIN
+- Bookmark lookup
|  +- Table
|  |  table          City
|  |  possible_keys  CountryCode
|  +- Index lookup
|     key            City->CountryCode
|     possible_keys  CountryCode
|     key_len        3
|     ref            world.Country.Code
|     rows           1
+- Table scan
   rows           183
   +- Table
      table          Country
      possible_keys  PRIMARY

So how do we interpret the EXPLAIN plan? Simple. The query will do a table scan on Country table for 183 rows. For each row, it will do an index lookup into the City.CountryCode index with the value from Country.Code, then do a bookmark lookup into the City table.

Please read related posts about limits and errors, and Extended EXPLAIN here and here for more in-depth understanding of MySQL EXPLAIN plans.

And here’s another example using an EXPLAIN DELETE statement (works on MySQL version 5.6.3 and above) :

$ mysql -Dworld -e "EXPLAIN DELETE from Cities where CountryCode IN (select Code from Country where LifeExpectancy < 70.0)" | pt-visual-explain
DEPENDENT SUBQUERY
+- Filter with WHERE
|  +- Bookmark lookup
|     +- Table
|     |  table          Country
|     |  possible_keys  PRIMARY
|     +- Unique subquery
|        key            Country->PRIMARY
|        possible_keys  PRIMARY
|        key_len        3
|        ref            func
|        rows           1
+- Filter with WHERE
   +- Table scan
      rows           581
      +- Table
         table          Cities

Alternatively, one can use – -format=dump option when parsing input into pt-visual-explain and it will create a Data::Dumper output which I think may be harder to understand but may be useable in some other cases.

Conclusion

The next time you want to check your execution plan information you can use pt-visual-explain to compare the result from the output of a regular EXPLAIN statement. You can also use MySQL Workbench, a GUI application that can produce beautified representation of the visual explain with colors, numbers on lines and other information.

For further reading please consult the sections under Modules and Algorithm on the pt-visual-explain documentation.

Other helpful information: pt-visual-explain is derived from Maatkit specifically mk-visual-explain. You can download Percona Toolkit from our download page – http://www.percona.com/downloads/ or get pt-visual-explain only by using $wget percona.com/get/pt-visual-explain.

The post Percona Toolkit collection: pt-visual-explain appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com