Nov
19
2018
--

Installing and Configuring JIT in PostgreSQL 11

JIT with PostgreSQL

JIT in PostgreSQLJust-in-time (JIT in PostgreSQL) compilation of SQL statements is one of the highlighted features in PostgreSQL 11. There is great excitement in the community because of the many claims of up to a 30% jump in performance. Not all queries and workloads get the benefit of JIT compilation. So you may want to test your workload against this new feature.

However, It is important to have a general understanding of what it does and where we can expect the performance gains. Installing PostgreSQL 11 with the new JIT compilation feature requires few extra steps and packages. Taking the time and effort to figure out how to do this shouldn’t be a reason to shy away from trying these cutting-edge features and testing a workload against the JIT feature. This blog post is for those who want to try it.

What is JIT and What it does in PostgreSQL

Normal SQL execution in any DBMS software is similar to what an interpreted language does to the source code. No machine code gets generated out of your SQL statement. But we all know that how dramatic the performance gains can be from a JIT compilation and execution of the machine code it generates. We saw the magic Google V8 engine did to JavaScript language. The quest for doing a similar thing with SQL statement was there for quite some time. But it is a challenging task.

It is challenging because we don’t have the source code (SQL statement) ready within the PostgreSQL server. The source code that needs to undergo JIT need to come from client connections and there could be expressions/functions with a different number of arguments, and it may be dealing with tables of different number and type of columns.

Generally, a computer program won’t get modified at this level while it is running, so branching-predictions are possible. The unpredictability and dynamic nature of SQL statements coming from client connections and hitting the database from time-to-time give no scope for doing advance prediction or compilation in advance. That means the JIT compiler should kick in every time the database gets an SQL statement. For this reason, PostgreSQL needs the help of compiler infrastructure like LLVM  continuously available behind. Even though there were a couple of other options, the main developer of this feature (Andres Freund) had a strong reason why LLVM was the right choice.

. In PostgreSQL 11, the JIT feature currently does:

  1. Accelerating expression evaluation: Expressions in  WHERE clauses, target lists, aggregates and projections
  2. Tuple deforming: Converting on-disk image to corresponding memory representation.
  3. In-lining: bodies of small custom functions, operators and user-defined data types are inline-ed into the expressions using them
  4. You can use compiler optimizations provided by LLVM for preparing optimized machine code.

In this blog, we are going to see how to install PostgreSQL with JIT. Just like regular PostgreSQL installations, we have two options:

  1. Get PostgreSQL from the packages in the PGDG repository
  2. Build PostgreSQL from source

Option 1. Install from PGDG repository.

Compiling from source requires us to install all compilers and tools. We might want to avoid this for various reasons. Installing packages from a PGDG repository is straightforward. On production systems or a container, you might want to install only the bare minimum required packages. Additional packages you don’t really use are always a security concern. Distributions like Ubuntu provide more recent versions of libraries and tool-sets in their default repos. However, distributions like CentOS / RHEL are quite conservative — their priority is stability and proven servers rather than cutting-edge features. So In this section of the post is mostly relevant for CentOS7/RHEL 7.

Here are the steps for the bare minimum installation of PostgreSQL with JIT feature on CentOS7

Step 1. Install PGDG repo and Install PostgreSQL server package.

This is usually the bare minimum installation if we don’t need the JIT feature.

sudo yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm
sudo yum install postgresql11-server

At this stage, we can initialize the data directory and start the service if we don’t need JIT:

sudo /usr/pgsql-11/bin/postgresql*-setup initdb
sudo systemctl start postgresql-11

Step 2. Install EPEL repository

sudo yum install epel-release

Step 3. Install package for PostgreSQL with llvmjit

sudo yum install postgresql11-llvmjit

Since we have already added the EPEL repository, now the dependancy can be resolved by YUM and it can pull and install the necessary package from EPEL. Installation message contains the necessary packages.

...
Installing:
postgresql11-llvmjit      x86_64     11.1-1PGDG.rhel7     pgdg11    9.0 M
Installing for dependencies:
llvm5.0                   x86_64     5.0.1-7.el7          epel      2.6 M
llvm5.0-libs              x86_64     5.0.1-7.el7          epel      13 M
...

As we can see, there are two packages: llvm5.0 and llvm5.0-libs get installed.

Note for Ubuntu users:

As we already mentioned, Repositories of recent versions of Ubuntu contains recent versions of LLVM libraries. For example, Ubuntu 16.04 LTS repo contains libllvm6.0 by default. Moreover, PostgreSQL server package is not divided to have a separate package for jit related files. So default installation of PostgreSQL 11 can get you JIT feature also.

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt install postgresql-11

Option 2. Building from Source

The primary means of distributing PostgreSQL is the source code. Building a minimal PostgreSQL instance requires just a C compiler. But building JIT options requires a few more things. One of the challenges you can run into is different errors during the build process due to older versions of LLVM and Clang present in the system.

Step 1. Download PostgreSQL source tarball and unpack

Tarballs are available in the repository. We can grab and unpack the latest:

curl -LO https://ftp.postgresql.org/pub/source/v11.0/postgresql-11.0.tar.bz2
tar -xvf postgresql-11.0.tar.bz2

Step 2.  Get SCL Repository and Install toolset

Latest versions of LLVM, CLang and GCC are available in SCL. We can get everything in a stretch:

sudo yum install centos-release-scl
sudo yum install llvm-toolset-7 llvm-toolset-7-llvm-devel.x86_64

Now either you can set or edit your PATH to have all new tools in PATH. I would prefer to put that into my profile file:

PATH=/opt/rh/devtoolset-7/root/usr/bin/:/opt/rh/llvm-toolset-7/root/usr/bin/:$PATH

Alternatively, we can open a shell with SCL enabled:

scl enable devtoolset-7 llvm-toolset-7 bash

We should attempt to compile the source from a shell with all these paths set.

Step 3. Install Additional libraries/tools

Based on the configuration options you want, this list may change. Consider this as a sample for demonstration purposes:

sudo yum install  readline-devel zlib-devel libxml2-devel openssl-devel

Step 4. Configure with –with-llvm option and make

Now we should be able to configure and make with our preferred options. The JIT feature will be available if the 

--with-llvm

 option is specified. For this demonstration, I am using an installation directory with my home (/home/postgres/pg11):

./configure --prefix=/home/postgres/pg11 --with-openssl --with-libxml --with-zlib --with-llvm
make
make install

Enabling JIT

You may observe that there is a new directory under the PostgreSQL’s lib folder with name

bit code

Which contains lots of files with .bc extension these are pre-generated bytecodes for LLVM for facilitating features like in-lining.

By default, the JIT feature is disabled in PostgreSQL 11. If you want to test it, you may have to enable the parameter

jit

:

postgres=# ALTER SYSTEM SET jit=on;
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)
postgres=# show jit;
 jit
-----
 on
(1 row)

By default, most of the simple queries won’t use JIT because of the cost. The cost is high when JIT kicks in. In case we want to test if JIT is properly configured, we can lie to PostgreSQL that that cost is very low by adjusting the parameter value. However, we should keep in mind that we are accepting negative performance gains. Let me show a quick example:

postgres=# SET jit_above_cost=5;
SET
postgres=# create table t1 (id int);
CREATE TABLE
postgres=# insert into t1 (SELECT (random()*100)::int FROM generate_series(1,800000) as g);
INSERT 0 800000
postgres=# analyze t1;
ANALYZE
postgres=# explain select sum(id) from t1;
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=8706.88..8706.89 rows=1 width=8)
   ->  Gather  (cost=8706.67..8706.88 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=7706.67..7706.68 rows=1 width=8)
               ->  Parallel Seq Scan on t1  (cost=0.00..6873.33 rows=333333 width=4)
 JIT:
   Functions: 6
   Options: Inlining false, Optimization false, Expressions true, Deforming true
(8 rows)

As we can see in the above example, a separate JIT section comes up in the explain plan.

We expect JIT compilation to make a difference in complex analytical queries because the overhead in JIT compilation gets compensated only if the code runs for the duration. Here is a simple aggregate query for demonstration. (I know this is not a complex query, and not the perfect example for demonstrating JIT feature):

postgres=# EXPLAIN ANALYZE SELECT COMPANY_ID,
      SUM(SHARES) TOT_SHARES,
      SUM(SHARES* RATE) TOT_INVEST,
      MIN(SHARES* RATE) MIN_TRADE,
      MAX(SHARES* RATE) MAX_TRADE,
      SUM(SHARES* RATE * 0.002) BROKERAGE
FROM TRADING
GROUP BY COMPANY_ID;
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=757298.72..758741.91 rows=5005 width=138) (actual time=16992.290..17011.395 rows=5000 loops=1)
   Group Key: company_id
   ->  Gather Merge  (cost=757298.72..758466.64 rows=10010 width=138) (actual time=16992.270..16996.919 rows=15000 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=756298.70..756311.21 rows=5005 width=138) (actual time=16983.900..16984.356 rows=5000 loops=3)
               Sort Key: company_id
               Sort Method: quicksort  Memory: 1521kB
               Worker 0:  Sort Method: quicksort  Memory: 1521kB
               Worker 1:  Sort Method: quicksort  Memory: 1521kB
               ->  Partial HashAggregate  (cost=755916.09..755991.16 rows=5005 width=138) (actual time=16975.997..16981.354 rows=5000 loops=3)
                     Group Key: company_id
                     ->  Parallel Seq Scan on trading  (cost=0.00..287163.65 rows=12500065 width=12) (actual time=0.032..1075.833 rows=10000000 loops=3)
 Planning Time: 0.073 ms
 Execution Time: 17013.116 ms
(15 rows)

We can switch on the JIT parameter at the session level and retry the same query:

postgres=# SET JIT=ON;
SET
postgres=# EXPLAIN ANALYZE SELECT COMPANY_ID,
      SUM(SHARES) TOT_SHARES,
      SUM(SHARES* RATE) TOT_INVEST,
      MIN(SHARES* RATE) MIN_TRADE,
      MAX(SHARES* RATE) MAX_TRADE,
      SUM(SHARES* RATE * 0.002) BROKERAGE
FROM TRADING
GROUP BY COMPANY_ID;
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=757298.72..758741.91 rows=5005 width=138) (actual time=15672.809..15690.901 rows=5000 loops=1)
   Group Key: company_id
   ->  Gather Merge  (cost=757298.72..758466.64 rows=10010 width=138) (actual time=15672.781..15678.736 rows=15000 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=756298.70..756311.21 rows=5005 width=138) (actual time=15661.144..15661.638 rows=5000 loops=3)
               Sort Key: company_id
               Sort Method: quicksort  Memory: 1521kB
               Worker 0:  Sort Method: quicksort  Memory: 1521kB
               Worker 1:  Sort Method: quicksort  Memory: 1521kB
               ->  Partial HashAggregate  (cost=755916.09..755991.16 rows=5005 width=138) (actual time=15653.390..15658.581 rows=5000 loops=3)
                     Group Key: company_id
                     ->  Parallel Seq Scan on trading  (cost=0.00..287163.65 rows=12500065 width=12) (actual time=0.039..1084.820 rows=10000000 loops=3)
 Planning Time: 0.072 ms
 JIT:
   Functions: 28
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 5.844 ms, Inlining 137.226 ms, Optimization 201.152 ms, Emission 125.022 ms, Total 469.244 ms
 Execution Time: 15696.092 ms
(19 rows)

Here we see a 7.7% improvement in performance. I executed this several times and found that the performance gain is consistently 7-8% for this simple query (which takes 15 seconds to execute). The gains are higher for queries with more calculations/expressions.

Summary

It is fairly simple to install and configure JIT with PostgreSQL as demonstrated above. One point we would like to highlight is that installing JIT packages and enabling the JIT feature can be done online while the database is up and running. This is because all JIT related parameters are dynamic in nature. Parameter changes can be loaded a SIGHUP signal or

SELECT pg_reload_conf()

 by the superuser. If it is not helping our workload, we can turn it off anytime. Nothing stops you from trying it in a non-production environment. We might not see a gain in small and simple queries that take less time for execution because the overhead in doing the JIT compilation can become more than executing the SQL statement. But we should expect a good gain in OLAP workload with complex queries that run for a longer duration.

Jan
11
2017
--

How to Replace MySQL with Percona Server on a CPanel, WHM VPS or Dedicated Server

Replace MySQL with Percona Server

Replace MySQL with Percona ServerIn this blog post, we’ll look at how to replace MySQL with Percona Server for MySQL on a CPanel, WHM VPS or dedicated server.

In general, CPanel and WHM have been leaning towards support of MariaDB over other flavors. This is partly due to the upstream repos replacing the MySQL package with MariaDB (for example, on CentOS).

MySQL 5.6 is still supported though, which means they are keeping support for core MySQL products. But if you want to get some extra performance enhancements or enterprise features for free, without getting too many bells and whistles, you might want to install Percona Server.

I’ve done this work on a new dedicated server with the latest WHM and CPanel on CentOS 7, with MySQL 5.6 installed. Besides the backup, this is a fairly quick process.

It’s pretty simple. From the Percona Server for MySQL 5.7 installation doc, we can get the YUM repo. (Run commands as root if you can, otherwise as sudo.)

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Now that we have the repo, let’s install Percona XtraBackup in case we need to roll this back at any point:

yum install percona-xtrabackup

This server had a drive mounted at /backup, so I created the backup with the following commands:

xtrabackup --target-dir=/backup/xtrabackup --backup
xtrabackup --target-dir=/backup/xtrabackup --prepare

Now that we have a good backup, let’s remove MySQL:

service mysql stop
yum remove MySQL* mysql*

Depending on your dependency chain, this could remove Percona XtraBackup, but that can be fixed. Let’s accept this uninstall.

Let’s install Percona Server for MySQL 5.7 and Percona Toolkit:

yum install Percona-Server-server-57 percona-toolkit percona-xtrabackup

Now that it’s installed ensure the mysql service is running. If it isn’t, start it. Now let’s upgrade:

mysql_upgrade

NOTE. This works if you can log in as root without a password; if you can’t, you will need to specify the

-u

 and

-p

 flags.

Once you run the upgrade, restart the mysql service:

service mysql restart

And there you go, you are now running on Percona Server for MySQL 5.7. If your managed providers tell you it’s not supported, don’t worry! It works as long as CPanel supports MySQL 5.6.

If you have any issues, just restore the backup.

NOTE: One thing to keep in mind is that 5.7 breaks CPanel’s ability to create users in MySQL. I believe this is due to the changes to the mysql.user table. If this is an issue for you, you can always use Percona Server for MySQL 5.6.

Mar
30
2016
--

Docker MySQL Replication 101

Docker

Precona Server DockerIn this blog post, we’ll discuss some of the basics regarding Docker MySQL replication. Docker has gained widespread popularity in recent years as a lightweight alternative to virtualization. It is ideal for building virtual development and testing environments. The solution is flexible and seamlessly integrates with popular CI tools.

 

This post walks through the setup of MySQL replication with Docker using Percona Server 5.6 images. To keep things simple we’ll configure a pair of instances and override only the most important variables for replication. You can add whatever other variables you want to override in the configuration files for each instance.

Note: the configuration described here is suitable for development or testing. We’ve also used the operating system repository packages; for the latest version use the official Docker images. The steps described can be used to setup more slaves if required, as long as each slave has a different server-id.

First, install Docker and pull the Percona images (this will take some time and is only executed once):

# Docker install for Debian / Ubuntu
apt-get install docker.io
# Docker install for Red Hat / CentOS (requires EPEL repo)
yum install epel-release # If not installed already
yum install docker-io
# Pull docker repos
docker pull percona

Now create locally persisted directories for the:

  1. Instance configuration
  2. Data files
# Create local data directories
mkdir -p /opt/Docker/masterdb/data /opt/Docker/slavedb/data
# Create local my.cnf directories
mkdir -p /opt/Docker/masterdb/cnf /opt/Docker/slavedb/cnf
### Create configuration files for master and slave
vi /opt/Docker/masterdb/cnf/config-file.cnf
# Config Settings:
[mysqld]
server-id=1
binlog_format=ROW
log-bin
vi /opt/Docker/slavedb/cnf/config-file.cnf
# Config Settings:
[mysqld]
server-id=2

Great, now we’re ready start our instances and configure replication. Launch the master node, configure the replication user and get the initial replication co-ordinates:

# Launch master instance
docker run --name masterdb -v /opt/Docker/masterdb/cnf:/etc/mysql/conf.d -v /opt/Docker/masterdb/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=mysecretpass -d percona:5.6
00a0231fb689d27afad2753e4350192bebc19ab4ff733c07da9c20ca4169759e
# Create replication user
docker exec -ti masterdb 'mysql' -uroot -pmysecretpass -vvv -e"GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'slavepass'G"
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
GRANT REPLICATION SLAVE ON *.* TO repl@"%"
--------------
Query OK, 0 rows affected (0.02 sec)
Bye
### Get master status
docker exec -ti masterdb 'mysql' -uroot -pmysecretpass -e"SHOW MASTER STATUSG"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
             File: mysqld-bin.000004
         Position: 310
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:

If you look carefully at the “docker run” command for masterdb, you’ll notice we’ve defined two paths to share from local storage:

/opt/Docker/masterdb/data:/var/lib/mysql

  • This maps the local “/opt/Docker/masterdb/data” to the masterdb’s container’s “/var/lib/mysql path”
  • All files within the datadir “/var/lib/mysql” persist locally on the host running docker rather than in the container
/opt/Docker/masterdb/cnf:/etc/mysql/conf.d

  • This maps the local “/opt/Docker/masterdb/cnf” directory to the container’s “/etc/mysql/conf.d” path
  • The configuration files for the masterdb instance persist locally as well
  • Remember these files augment or override the file in “/etc/mysql/my.cnf” within the container (i.e., defaults will be used for all other variables)

We’re done setting up the master, so let’s continue with the slave instance. For this instance the “docker run” command also includes the “–link masterdb:mysql” command, which links the slave instance to the master instance for replication.

After starting the instance, set the replication co-ordinates captured in the previous step:

docker run --name slavedb -d -v /opt/Docker/slavedb/cnf:/etc/mysql/conf.d -v /opt/Docker/slavedb/data:/var/lib/mysql --link masterdb:mysql -e MYSQL_ROOT_PASSWORD=mysecretpass -d percona:5.6
eb7141121300c104ccee0b2df018e33d4f7f10bf5d98445ed4a54e1316f41891
docker exec -ti slavedb 'mysql' -uroot -pmysecretpass -e'change master to master_host="mysql",master_user="repl",master_password="slavepass",master_log_file="mysqld-bin.000004",master_log_pos=310;"' -vvv
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
change master to master_host="mysql",master_user="repl",master_password="slavepass",master_log_file="mysqld-bin.000004",master_log_pos=310
--------------
Query OK, 0 rows affected, 2 warnings (0.23 sec)
Bye

Almost ready to go! The last step is to start replication and verify that replication running:

# Start replication
docker exec -ti slavedb 'mysql' -uroot -pmysecretpass -e"START SLAVE;" -vvv
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
START SLAVE
--------------
Query OK, 0 rows affected, 1 warning (0.00 sec)
Bye
# Verify replication is running OK
docker exec -ti slavedb 'mysql' -uroot -pmysecretpass -e"SHOW SLAVE STATUSG" -vvv
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SHOW SLAVE STATUS
--------------
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000004
          Read_Master_Log_Pos: 310
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 284
        Relay_Master_Log_File: mysqld-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 310
              Relay_Log_Space: 458
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 230d005a-f1a6-11e5-b546-0242ac110004
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)
Bye

Finally, we have a pair of dockerized Percona Server 5.6 master-slave servers replicating!

As mentioned before, this is suitable for a development or testing environment. Before going into production with this configuration, think carefully about the tuning of the “my.cnf” variables and the choice of disks used for the data/binlog directories. It is important to remember that newer versions of Docker recommend using “networks” rather than “linking” for communication between containers.

Jan
12
2015
--

Percona Server 5.6.22-71.0 is now available

Percona ServerPercona is glad to announce the release of Percona Server 5.6.22-71.0 on January 12, 2015. Download the latest version from the Percona web site or from the Percona Software Repositories.

Based on MySQL 5.6.22, including all the bug fixes in it, Percona Server 5.6.22-71.0 is the current GA release in the Percona Server 5.6 series. Percona Server is open-source and free – and this is the latest release of our enhanced, drop-in replacement for MySQL. Complete details of this release can be found in the 5.6.22-71.0 milestone on Launchpad.

New Features:

  • Percona Server has implemented improved slow log reporting for queries in stored procedures.
  • TokuDB storage engine package has been updated to version 7.5.4. Percona Server with an older version of TokuDB could hit an early scaling limit when the binary log was enabled. TokuDB 7.5.4 fixes this problem by using the hints supplied by the binary log group commit algorithm to avoid fsync’ing its recovery log during the commit phase of the 2 phase commit algorithm that MySQL uses for transactions when the binary log is enabled.

Bugs Fixed:

  • Debian and Ubuntu init scripts no longer have a hardcoded server startup timeout. This has been done to accommodate situations where server startup takes a very long time, for example, due to a crash recovery or buffer pool dump restore. Bugs fixed #1072538 and #1328262.
  • A read-write workload on compressed InnoDB tables might have caused an assertion error. Bug fixed #1268656.
  • Selecting from GLOBAL_TEMPORARY_TABLES table while running an online ALTER TABLE in parallel could lead to server crash. Bug fixed #1294190.
  • A wrong stack size calculation could lead to a server crash when Performance Schema tables were storing big amount of data or in case of server being under highly concurrent load. Bug fixed #1351148 (upstream #73979).
  • A query on an empty table with a BLOB column may crash the server. Bug fixed #1384568 (upstream #74644).
  • A read-write workload on compressed InnoDB tables might have caused an assertion error. Bug fixed #1395543.
  • If HandlerSocket was enabled, the server would hang during shutdown. Bug fixed #1397859.
  • The default MySQL configuration file, my.cnf, was not installed during a new installation on CentOS. Bug fixed #1405667.
  • The query optimizer did not pick a covering index for some ORDER BY queries. Bug fixed #1394967 (upstream #57430).
  • SHOW ENGINE INNODB STATUS was displaying two identical TRANSACTIONS sections. Bug fixed #1404565.
  • A race condition in Multiple user level locks per connection implementation could cause a deadlock. Bug fixed #1405076.

Other bugs fixed: #1394357, #1337251, #1399174, #1396330 (upstream #74987), and #1401776 (upstream #75189).

Known Issues:
If you’re upgrading TokuDB package on CentOS 5/6 you’ll need to restart the MySQL service after the upgrade, otherwise TokuDB storage engine won’t be initialized.

Release notes for Percona Server 5.6.22-71.0 are available in the online documentation. Please report any bugs on the launchpad bug tracker

The post Percona Server 5.6.22-71.0 is now available 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.

Apr
16
2014
--

‘Open Source Appreciation Day’ draws OpenStack, MySQL and CentOS faithful

Open Source Appreciation Day Brings Together OpenStack, MySQL, and CentOS Communities

210 people registered for the inaugural “Open Source Appreciation Day” March 31 in Santa Clara, Calif. The event will be held each year at Percona Live henceforth.

To kick off the Percona Live MySQL Conference & Expo 2014, Percona held the first “Open Source Appreciation Day” on Monday, March 31st. Over 210 people registered and the day’s two free events focused on CentOS and OpenStack.

The OpenStack Today event brought together members of the OpenStack community and MySQL experts in an afternoon of talks and sharing of best practices for both technologies. After a brief welcome message from Peter Zaitsev, co-founder and CEO of Percona, Florian Haas shared an introduction to OpenStack including its history and the basics of how it works.

Jay Pipes delivered lessons from the field based on his years of OpenStack experience at AT&T, at Mirantis, and as a frequent code contributor to the project. Jay Janssen, a Percona managing consultant, complemented Jay Pipes’ talk with a MySQL expert’s perspective of OpenStack. He also shared ways to achieve High Availability using the latest version of Galera (Galera 3) and other new features found in the open source Percona XtraDB Cluster 5.6.

Amrith Kumar’s presentation focused on the latest happenings in project Trove, OpenStack’s evolving DBaaS component, and Tesora’s growing involvement. Amrith also won quote of the day for his response to a question about the difference between “elastic” and “scalable.” Amrith: “The waistband on my trousers is elastic. It is not scalable.” Sandro Mazziotta wrapped up the event by sharing the challenges and opportunities of OpenStack from both an integrator as well as operator point of view based on the customer experiences of eNovance.

OpenStack Today was made possible with the support of our sponsors, Tesora and hastexo. Here are links to presentations from the OpenStack Today event. Any missing presentations will soon be added to the OpenStack Today event page.

Open Source Appreciation DaySpeakers in the CentOS Dojo Santa Clara event shared information about the current status of CentOS, the exciting road ahead, and best practices in key areas such as system administration, running MySQL, and administration tools. Here’s a rundown of topics and presentations from the event. Any missing presentations will soon be added to the CentOS Dojo Santa Clara event page.

  • Welcome and Housekeeping
    Karsten Wade, CentOS Engineering Manager, Red Hat
  • The New CentOS Project
    Karsten Wade, CentOS Engineering Manager, Red Hat
  • Systems Automation and Metrics at Pinterest
    Jeremy Carroll, Operations Engineer, Pinterest
  • Software Collections on CentOS
    Joe Brockmeier, Open Source & Standards, Red Hat
  • Two Years Living Your Future
    Joe Miller, Lead Systems Engineer, Pantheon
  • Running MySQL on CentOS Linux
    Peter Zaitsev, CEO and Co-Founder, Percona
  • Notes on MariaDB 10
    Michael Widenius, Founder and CTO, MariaDB Foundation
  • Happy Tools
    Jordan Sissel, Systems Engineer, DreamHost

Thank you to all of the presenters at the Open Source Appreciation Day events and to all of the attendees for joining.

I hope to see you all again this November 3-4  at Percona Live London. The Percona Live MySQL Conference and Expo 2015 will also return to the Hyatt Santa Clara and Santa Clara Convention Center from April 13-16, 2015 – watch for more details in the coming months!

The post ‘Open Source Appreciation Day’ draws OpenStack, MySQL and CentOS faithful appeared first on MySQL Performance Blog.

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