Percona

Modified: 22 Nov 2017 17:39 UTC

The Joyent Percona image is a dynamically scalable SmartOS base64 image that is optimized as a Percona MySQL Server. This appliance includes components such as the XtraDB storage engine, Sphinx plugin, and Quickbackup-percona.



Overview

Percona (MySQL) runs under the 'mysql' user as the SMF service svc:/pkgsrc/percona:default. The instance is provisioned listening on the private IP and comes tuned according to your VM size.

Directory layout

Starting with image version 13.x, the Percona libraries are installed in /opt/local and databases are stored in /var/mysql.

Image Version 13.x and later

Description Directory
Percona Server MySQL binaries /opt/local/bin
MySQL data directory /var/mysql
MySQL configuration /opt/local/etc/my.cnf
Backups from Joyent QuickBackup /var/backups/percona
MySQL DTrace scripts /opt/local/bin

Image Version 1.6.0 and earlier

Description Directory
Percona Server MySQL binaries /mysql
MySQL data directory /databases
MySQL configuration /mysql.cnf
Backups from Joyent QuickBackup /backups
MySQL DTrace scripts /root/bin

Starting Percona MySQL

By default Percona MySQL comes enabled. To start, stop, or restart a Percona server, use the following svcadm commands. You must use sudo be logged in as root to use svcadm commands.

Image 13.3.0 Image 13.1.0 Image 1.6.0
Starting svcadm enable percona svcadm enable percona-server svcadm enable mysql:percona
Stopping svcadm disable percona svcadm disable percona-server svcadm disable mysql:percona
Restarting svcadm restart percona svcadm restart percona-server svcadm restart mysql:percona

Connecting to Percona MySQL

Command Line

Percona MySQL comes pre-configured with the root password set to the 'mysql_pw' metadata. To connect through command line first you need to login as 'admin' or 'root' account and then use the mysql client shown below.

    # mysql -uroot -p$(mdata-get mysql_pw)
    mysql>

The Percona MySQL server is configured to listen on its private IP address. Although you can change this configuration, it's best to connect to the Percona instance from another instance with a public IP address.

You can find the private IP for your instance in the Instance Details page of the Triton Compute Service portal, or by using ifconfig.

If you want to connect to your Percona server directly, but you don't want to have it listen on a public IP address, you can use a SSH tunnel. Some MySQL administrator tools (for example, Navicat) already support SSH tunneling. If you plan to use a tool like Navicat, use these values when configuring it:

Memory usage

Monitoring the processes memory usages is easy with project support.

MySQL Percona memory usage can be monitored under the 'mysql' project using prstat -J.

    # prstat -J
    PROJID    NPROC  SWAP   RSS MEMORY      TIME  CPU PROJECT
       924        1 1708M  654M   0.7%   0:01:27 0.0% mysql
         1        5   24M   16M   0.0%   0:00:00 0.0% user.root
         0       17   76M   49M   0.0%   0:00:21 0.0% system

Backing up Percona MySQL

A component that comes with the Percona MySQL Instance is Joyent QuickBackup. This is a simple and fast way to backing up your Percona databases.

See the JOyent QuickBackup page.

Managing Percona MySQL with smtools

By default the Percona Instance comes with the 'smtools' package installed which has helper scripts for managing common database tasks. Always ensure you have the latest 'smtools' package by executing:

    # pkgin -y up && pkgin install smtools

Listing databases (sm-list-db)

By default sm-list-db will use your portal credentials to connect to MySQL. If you have changed them you will need to specify the -u (username) and -p (password) options in the command.

    # sm-list-dbs mysql
    * Listing MySQL databases for 836fec8f-e394-e06d-9af4-b4479c5f9a1b..
    information_schema
    mysql
    performance_schema
    tester1
    tester2
    db1
    db2

Listing database users (sm-list-dbusers)

By default sm-list-dbusers will use your portal credentials to connect to MySQL. If you have changed them you will need to specify the -u (username) and -p (password) options in the command.

    # sm-list-dbusers mysql
    * Listing MySQL users for 836fec8f-e394-e06d-9af4-b4479c5f9a1b..
    User    Host
    repluser        10.%
    root    10.1.1.1
    qb-b4479c5f9a1b localhost
    root    localhost

Creating a database (sm-create-db)

By default sm-create-db will use your portal credentials to connect to MySQL. If you have changed them you will need to specify the -u (username) and -p (password) options in the command.

    # sm-create-db mysql database1
    * Created MySQL database database1.

Removing a database (sm-remove-db)

By default sm-remove-db will use your portal credentials to connect to MySQL. If you have changed them you will need to specify the -u (username) and -p (password) options in the command.

    # sm-remove-db mysql database1
    * Removed MySQL database database1.

Creating a database user (sm-create-dbuser)

By default sm-create-dbuser will use your portal credentials to connect to MySQL. If you have changed them you will need to specify the -u (username) and -p (password) options in the command.

    # sm-create-dbuser mysql username1 password1 all
    * Created MySQL user username1 pass password1 with access to all from anywhere.

Removing a database user (sm-remove-dbuser)

By default sm-remove-dbuser will use your portal credentials to connect to MySQL. If you have changed them you will need to specify the -u (username) and -p (password) options in the command.

    # sm-remove-dbuser mysql username1
    * Removed MySQL user username1.

Setting up master/slave replication (sm-sync-to-master)

Pre-requisites to running sm-sync-to-master:

In this example we'll use the following nodes for setting up master/slave replication.

Host Role
192.168.0.1 Master
192.168.0.2 Slave

sm-sync-to-master must be run on the slave node. In this case we run it on the 192.168.0.2 node.

    # sm-sync-to-master username1 password1 192.168.0.1 username2 password2 192.168.0.2

    sm-sync-to-master - Syncs mysql slaves to masters.

    * Sanity checking mysql environment..                                 OK.
    * Dumping master host (192.168.0.1) mysql..                           OK.
    * Importing dump (563K) to slave host (192.168.0.2)..                 OK.
    * Configuring replication..                                           OK.
    * Checking replication status..                                       OK.

Slave host (192.168.0.2) is now replicating from Master host (192.168.0.1).

DTracing Percona MySQL

This image has the dtracetools package installed by default. This package comes with DTrace scripts ready to use for MySQL Percona (in /opt/local/bin).

DTrace script Description
dtrace-mysql_query_monitor.d Shows queries by who and execution time
dtrace-mysql_query_filesorts.d Shows query filesort execution times
dtrace-mysql_querytype_counts.d Shows how many of each query was executed
dtrace-mysql_locks.d Shows table locks and times
dtrace-mysql_pid_slow.d Shows query times specified by min_ns
dtrace-mysql_pid_latency.d Shows query latency by distribution every second
dtrace_mysql_pid_avg.d Shows average query latency every second

Example #1 - dtrace-mysql_query_monitor.d

    # dtrace-mysql_query_monitor.d
    Who                  Database             Query                                    QC Time(ms)
    root@localhost       mysql                select * from mysql.user                 N  0
    root@localhost       mysql                show databases                           N  0

Example #2 - dtrace-mysql_pid_slow.d

    # dtrace-mysql_pid_slow.d -p $(pgrep -x mysqld) 5
    Tracing... Min query time: 5000000 ns.

     TIME(ms) CPU(ms)  QUERY
     6        0        DESCRIBE wp_terms\0
     5        0        DESCRIBE wp_options\0
     69       9        CREATE TABLE wp_users (\n  ID bigint(20) unsigned NOT NULL auto_increment,\n  user_login varchar(60) NOT NULL ..

Example #3 - dtrace-mysql_querytype_counts.d

    # dtrace-mysql_querytype_counts.d

      delete                                                            0
      select                                                           11
      update                                                           13
      insert                                                           46

Metadata keys

With the Percona image are metadata keys that can be pre-defined during provision to provide additional customization to the instance.

Metadata Key Description
mysql_server_id MySQL server id to set
mysql_password MySQL root password to set

Version 16.x.x

16.4.1

Name percona
Description A SmartOS 64-bit image pre-configured and optimized as a Percona 5.7.16 64-bit MySQL server with Quickbackup.
UUID 2539bf26-e967-11e6-9d00-b70930296d8a
Based on base-64-lts 16.4.1
Pkgsrc PKG_PATH=https://pkgsrc.joyent.com/packages/SmartOS/2016Q4/x86_64/All
What's New In This Image
Software Included In This Image

Software installed as pkgsrc packages:

bzip2-1.0.6nb1       Block-sorting file compressor
curl-7.52.1          Client that groks URLs
db4-4.8.30           Berkeley DB version 4 from Oracle
dtracetools-0.7      DTraceTools for Joyent SmartMachines
gcc49-libs-4.9.4nb1  The GNU Compiler Collection (GCC) support shared libraries
gtar-base-1.29       The GNU tape archiver with remote magnetic tape support
htop-2.0.2           Enhanced version of top utility
nodejs-7.2.1         V8 JavaScript for clients and servers
openssl-1.0.2k       Secure Socket Layer and cryptographic library
percona-client-5.7.16 Percona Server, a MySQL server fork (client)
percona-server-5.7.16 Percona Server, a MySQL server fork (server)
percona-toolkit-2.2.19 Advanced command-line tools for open-source databases (MySQL)
percona57-xtrabackup-2.4.5 Percona XtraBackup
perl-5.24.0          Practical Extraction and Report Language
pkgin-0.9.4nb4       Apt / yum like tool for managing pkgsrc binary packages
postfix-3.1.3nb1     Postfix SMTP server and tools
python27-2.7.12nb4   Interpreted, interactive, object-oriented programming language
quickbackup-percona-3.6 Percona quickbackup SMF service
smtools-20160926     Joyent tools relevant to SmartOS and SmartMachines
sqlite3-3.15.2nb2    SQL Database Engine in a C Library
sudo-1.8.17p1        Allow others to run commands as root
wget-1.18nb3         Retrieve files from the 'net via HTTP and FTP
zoneinit-1.6.9       Joyent Instance initialization toolchain

Software installed as npm packages:

json@9.0.4
manta@4.1.1
smartdc@8.1.0

Version 15.x.x

Starting with version 13.1.0, we use the new version numbering scheme for SmartOS images.

15.4.1

Name percona
Description A SmartOS 64-bit image pre-configured and optimized as a Percona 5.6.29 64-bit MySQL server with Quickbackup and Sphinx Plug-in.
UUID 7d03bd9a-0634-11e6-924b-730341c39cf1
Based on base-64-lts 15.4.1
Pkgsrc 2015Q4
What's New In This Image
Software Included In This Image

Software installed as pkgsrc packages:

bzip2-1.0.6nb1       Block-sorting file compressor
curl-7.47.1          Client that groks URLs
db4-4.8.30           Berkeley DB version 4 from Oracle
dtracetools-0.7nb20160127 DTraceTools for Joyent SmartMachines
gcc47-libs-4.7.4nb1  The GNU Compiler Collection (GCC) support shared libraries
gcc49-libs-4.9.3nb1  The GNU Compiler Collection (GCC) support shared libraries
gtar-base-1.28nb1    The GNU tape archiver with remote magnetic tape support
htop-1.0.1nb1        Enhanced version of top utility
nodejs-5.10.0        V8 JavaScript for clients and servers
openssl-1.0.2g       Secure Socket Layer and cryptographic library
percona-client-5.6.29 Percona MySQL 5.5, a free SQL database (client)
percona-server-5.6.29 Percona MySQL 5.5, a free SQL database (server)
percona-toolkit-2.2.14nb1 Advanced command-line tools for open-source databases (MySQL)
percona56-xtrabackup-2.4.2 Percona XtraBackup
perl-5.22.0          Practical Extraction and Report Language
pkgin-0.9.4          Apt / yum like tool for managing pkgsrc binary packages
postfix-3.0.2nb2     Postfix SMTP server and tools
python27-2.7.11      Interpreted, interactive, object-oriented programming language
quickbackup-percona-3.5nb20140212 Percona quickbackup SMF service
smtools-20160316     Joyent tools relevant to SmartOS and SmartMachines
sqlite3-3.9.2        SQL Database Engine in a C Library
sudo-1.8.15          Allow others to run commands as root
wget-1.17.1          Retrieve files from the 'net via HTTP and FTP
zoneinit-1.6.8       Joyent Instance initialization toolchain

Software installed as npm packages:

json@9.0.3
manta@2.0.6
smartdc@8.1.0

15.1.1

Name percona
Description A SmartOS 64-bit image pre-configured and optimized as a Percona 5.6.22 64-bit MySQL server with Quickbackup and Sph
inx Plug-in.
UUID 18ce020a-09f9-11e5-bb6d-affb9518475b
Based on base-64 15.1.1
Pkgsrc 2015Q1
What's New In This Image
Software Included In This Image

Software installed as pkgsrc packages:

bzip2-1.0.6nb1
curl-7.42.0
db4-4.8.30
dtracetools-0.7
gcc47-libs-4.7.4nb1
gtar-base-1.28nb1
htop-1.0.1
nodejs-0.12.4
openssl-1.0.2a
percona-client-5.6.22
percona-server-5.6.22
percona-toolkit-2.2.9
percona56-xtrabackup-2.2.8
perl-5.20.2
pkgin-0.8.0
postfix-2.11.4
python27-2.7.9nb1
quickbackup-percona-3.5nb20140212
smtools-20150312
sqlite3-3.8.10
sudo-1.7.10p9
wget-1.16.3
zoneinit-1.6.8

Software installed as npm packages:

json@9.0.3
manta@1.5.1
smartdc@7.3.1

Version 14.x.x

14.4.2

Name percona
Description A SmartOS 64-bit image pre-configured and optimized as a Percona 5.6.22 64-bit MySQL server with Quickbackup and Sphinx Plug-in.
UUID c752b976-438f-11e6-acf0-d3023569a4e2
Based on base-64-lts 14.4.2
Pkgsrc 2014Q4
What's new in this image
Software included in this image

Software installed as pkgsrc packages:

bzip2-1.0.6nb1
curl-7.39.0nb1
db4-4.8.30
dtracetools-0.7
gcc47-libs-4.7.3nb7
gtar-base-1.28
htop-1.0.1
nodejs-0.12.0
openssl-1.0.1k
percona-client-5.6.22
percona-server-5.6.22
percona-toolkit-2.2.9
percona56-xtrabackup-2.2.8
perl-5.20.1
pkgin-0.7.0
postfix-2.11.3
python27-2.7.9
quickbackup-percona-3.5nb20140212
smtools-20150226
sqlite3-3.8.7.4
sudo-1.7.10p9
wget-1.16.1
zoneinit-1.6.8

Software installed as npm packages:

json@9.0.3
manta@1.5.1
smartdc@7.3.1

14.4.0

Name percona
Description A SmartOS 64-bit image pre-configured and optimized as a Percona 5.6.22 64-bit MySQL server with Quickbackup and Sphinx Plug-in.
UUID f4c39d9e-d2f2-11e4-bef3-bf7870572bb8
Based on base-64-lts 14.4.-
Pkgsrc 2014Q4
What's new in this image
Software included in this image

Software installed as pkgsrc packages:

bzip2-1.0.6nb1
curl-7.39.0nb1
db4-4.8.30
dtracetools-0.7
gcc47-libs-4.7.3nb7
gtar-base-1.28
htop-1.0.1
nodejs-0.12.0
openssl-1.0.1k
percona-client-5.6.22
percona-server-5.6.22
percona-toolkit-2.2.9
percona56-xtrabackup-2.2.8
perl-5.20.1
pkgin-0.7.0
postfix-2.11.3
python27-2.7.9
quickbackup-percona-3.5nb20140212
smtools-20150226
sqlite3-3.8.7.4
sudo-1.7.10p9
wget-1.16.1
zoneinit-1.6.8

Software installed as npm packages:

json@9.0.3
manta@1.5.1
smartdc@7.3.1

14.3.0

Name percona
Description A SmartOS 64-bit image pre-configured and optimized as a Percona 5.6.21 64-bit MySQL server with Quickbackup and Sphinx Plug-in.
UUID e62f4756-8478-11e4-b202-2304793d3eeb
Based on base64 14.3.0
Pkgsrc 2014Q3
What's new in this image
Software included in this image

Software installed as pkgsrc packages:

    bzip2-1.0.6nb1
    curl-7.38.0
    db4-4.8.30
    gcc47-libs-4.7.3nb7
    gtar-base-1.28
    nodejs-0.10.33
    openssl-1.0.1j
    perl-5.20.0nb2
    pkgin-0.6.4nb7
    postfix-2.11.1nb1
    python27-2.7.8nb1
    quickbackup-percona-3.5nb20140212
    smtools-20141209
    sqlite3-3.8.6
    sudo-1.7.10p8
    wget-1.16
    zoneinit-1.6.8

Software installed as npm packages

    manta@1.4.6
    smartdc@7.3.0

14.2.0

Name percona
Description A SmartOS 64-bit image pre-configured and optimized as a Percona 5.6.19 64-bit MySQL server with Quickbackup and Sphinx Plug-in.
UUID a95d2898-2c5d-11e4-a4eb-07dbc6315324
Based on base64 14.2.0
Pkgsrc 2014Q2
What's new in this image
Software included in this image

Software installed as pkgsrc packages:

    bzip2-1.0.6nb1
    curl-7.37.0nb1
    db4-4.8.30
    gcc47-libs-4.7.3nb4
    gtar-base-1.27.1nb1
    nodejs-0.10.30
    openssl-1.0.1h
    perl-5.20.0nb1
    pkgin-0.6.4nb7
    postfix-2.11.1nb1
    python27-2.7.7nb2
    quickbackup-percona-3.5nb20140212
    smtools-20140728
    sqlite3-3.8.5
    sudo-1.7.10p8
    wget-1.15nb2
    zoneinit-1.6.8

Software installed as npm packages

    jsontool@7.0.2
    manta@1.4.2
    smartdc@7.3.0

Version 13.x.x

13.4.2

Name percona
Description A SmartOS 64-bit image pre-configured and optimized as a Percona 5.6.13 64-bit MySQL server with Quickbackup and Sphinx Plug-in.
UUID 9ab3f722-d136-11e3-9f3a-6fcb23726ee3
Based on base64 13.4.2
Pkgsrc 2013Q4
What's new in this release
Software included in this release
    bzip2-1.0.6nb1
    curl-7.33.0
    db4-4.8.30
    duo-unix-1.9.7
    gcc47-libs-4.7.3nb3
    gtar-base-1.26nb2
    libarchive-2.8.4nb2
    nodejs-0.10.26
    openssl-1.0.1fnb1
    perl-5.18.1nb3
    pkgin-0.6.4nb4
    postfix-2.10.2nb1
    python27-2.7.6nb1
    qpress-1.1
    smtools-20140303
    sqlite3-3.8.2
    sudo-1.7.10p7
    wget-1.14nb3
    zoneinit-1.6.7

13.3.0

Name percona
Description A SmartOS 64-bit image pre-configured and optimized as a Percona 5.6.13 64-bit MySQL server with Quickbackup and Sphinx Plug-in.
UUID fb6e7820-60ee-11e3-93b6-7f34ea3f2616
Based on base64 13.3.0
Pkgsrc 2013Q3
What's new in this release
Software included in this release
    bash-4.2nb3
    bzip2-1.0.6nb1
    db4-4.8.30
    gcc47-libs-4.7.3nb3
    gtar-base-1.26nb2
    nodejs-0.10.22
    openssl-1.0.1enb2
    percona-client-5.6.13nb1
    percona-server-5.6.13nb4
    percona-toolkit-2.1.7nb2
    percona-xtrabackup-2.1.3nb1
    perl-5.18.1nb1
    pkgin-0.6.4nb1
    postfix-2.9.8
    python27-2.7.5nb3
    smtools-20131031
    sqlite3-3.8.0.2
    sudo-1.7.10p7

13.1.0

Name percona
Description A SmartOS 64-bit image pre-configured and optimized as a Percona 5.5.29 64-bit MySQL server with Quickbackup, Handlersocket, and Sphinx Plug-in.
UUID 3882b5da-b0e8-11e2-b3a9-dbcf26c3e051
Based on base64 13.1.0
Pkgsrc 2013Q1
What's new in this release
Software included in this release
    bash-4.2nb2
    bzip2-1.0.6
    db4-4.8.30
    gcc47-libs-4.7.2nb4
    gtar-base-1.26nb2
    nodejs-0.10.5
    openssl-1.0.1e
    percona-client-5.5.29
    percona-server-5.5.29
    percona-toolkit-2.1.7nb1
    percona-xtrabackup-2.0.5
    perl-5.16.2nb4
    pkgin-0.6.3.1.1
    postfix-2.9.6nb1
    python27-2.7.3nb3
    smtools-20130417
    sqlite3-3.7.15.2nb2
    sudo-1.7.10p7

Version 1.x.x

Note: The Percona images version 1.x.x have reached end-of-life and, as of September 2017, are no longer supported or provided.