Planet MariaDB

April 28, 2016

MariaDB AB

MariaDB MaxScale 1.4.2 GA is available for download

Johan

We are pleased to announce that MariaDB MaxScale 1.4.2 GA is now available for download!

If MariaDB MaxScale is new to you, we recommend reading this page first.

1.4.2 is a bugfix release, not bringing any new features but fixing certain issues found in 1.4.1.

MariaDB MaxScale 1.4 brings:

  1. The Firewall Filter has been extended and can now be used for either black-listing or white-listing queries. In addition it is capable of logging both queries that match and queries that do not match.
  2. Client-side SSL has been available in MariaDB MaxScale for a while, but it has been somewhat unreliable. We now believe that client side SSL is fully functional and usable.

Additional improvements:

  • POSIX Extended Regular Expression Syntax can now be used in conjunction with qlafilter, topfilter and namedserverfilter.
  • Improved user grant detection.
  • Improved password encryption.

The release notes can be found here and the binaries can be downloaded here.

In case you want to build the binaries yourself, the source can be found at GitHub, tagged with 1.4.2.

We hope you will download and use this release, and we encourage you to create a bug report in Jira for any bugs you might encounter.

On behalf of the MariaDB MaxScale team.

About the Author

Johan's picture

Johan Wikman is a senior developer working on MaxScale at MariaDB Corporation. 

by Johan at April 28, 2016 08:23 AM

MariaDB ColumnStore, a new beginning

nishantvyas

MariaDB’s new analytics engine – MariaDB ColumnStore - has been in the works for some time. What is it and how did it come about? This post outlines our thinking in choosing the engine and features we implemented.

Databases are expanding from their roots as systems of record into new analytics applications that some people call “systems of intelligence.” That means that instead of just storing and querying transactional data, databases are increasingly being used to yield insights, predict the future and prescribe actions users should take. Led by the open-source Hadoop ecosystem, online analytic processing (OLAP) is making its way out of the corporate data center and into the hands of everyone who needs it.

In the last decade, as data analytics became more important, solving these problems became more challenging. Everyone was led to believe, through hype and skewed opinions, that scale-out, big clusters and data processing without using SQL is probably the only way to do data analytics. We were made to believe that solving analytics would require either scale-out or spending big money on proprietary solutions.

On one end, there are traditional OLAP data warehouses, which are powerful and SQL rich BUT too costly, proprietary and often black box appliances. On the other end, we saw the rise of hadoop ecosystems, which challenged traditional OLAP providers, paved a way to machine learning and data discovery that was not easy with traditional solutions but came with the complexity of scale out and lacked SQL interfaces.

We know our users choose MariaDB because they value performance, scalability, reliability, security and extensibility through open source, with 100% SQL compatibility. We wanted our OLAP choice to reflect the same values.

One commercial product that caught our eye is SAP’s HANA. It’s a database appliance that supports both transactional and analytical applications on the same platform. HANA has gotten rave reviews, not only for functionality but also for simplicity. But HANA appliances are expensive and they’re really intended for organizations that are using SAP (price: don’t ask). We knew there was an open-source, scalable, software-only columnar analytic DBMS alternative: InfiniDB.

We thought InfiniDB would be a terrific fit with our OLAP and big data strategy. It provides a columnar, massively parallel storage engine with performance that scales with the addition of database servers, Performance Modules. Redundancy and high availability are built in, and InfiniDB supports full SQL syntax including joins, window functions and cross engine joins. It also works with Hadoop’s HDFS file system to provide incredible scalability.

MariaDB ColumnStore

We saw a unique opportunity to fill the need for high performance analytic data engine in the open-source market by binding InfiniDB’s OLAP engine to MariaDB’s OLTP engine to enable users to run analytic queries on production data in near real-time. Much of our development work has been enabling that tight integration.

About the Author

nishantvyas's picture
Nishant joins MariaDB as Head of Product and Strategy from LinkedIn, where he was one of the early employees. During his almost nine-year tenure at LinkedIn, he contributed to building, scaling and operating production data stores using technologies like Oracle, MySQL, NoSQL and more. Nishant has extensive experience as a database engineer, database architect and DBA, and has held various leadership roles. He holds a bachelor's degree in engineering from Gujarat University and a master's degree in computer science from the University of Bridgeport. Based in the San Francisco bay area, Nishant is co-author of a patent in waterwheel sharding.

by nishantvyas at April 28, 2016 07:33 AM

Jean-Jerome Schmidt

Planets9s - Watch the replay: Become a MongoDB DBA (if you’re re really a MySQL user)

Welcome to this week’s Planets9s, covering all the latest resources and technologies we create around automation and management of open source database infrastructures.

Watch the replay: Become a MongoDB DBA (for MySQL users)

Thanks to everyone who participated in this week’s webinar on ‘Become a MongoDB DBA’! Our colleague Art van Scheppingen presented from the perspective of a MySQL DBA who might be called to manage a MongoDB database. Art also did a live demo on how to carry out the relevant DBA tasks using ClusterControl. The replay is now available to watch in cased you missed it or simply would like to see it again in your own time.

Watch the replay

Severalnines expands the reach of European scientific discovery for CNRS

We’re excited to announce our latest customer, the National Center for Scientific Research (CNRS), which is a subsidiary of the French Ministry of Higher Education and Research. As the largest fundamental research organization in Europe, CNRS carries out research in all fields of knowledge. Find out how we help CNRS keep costs down whilst increasing the potential of their open source systems. And how ClusterControl helps them both manage and use their LAMP applications, as well as cloud services.

Read the press release

Infrastructure Automation - Ansible Role for ClusterControl

If you are automating your server infrastructure with Ansible, then this blog is for you. We recently announced the availability of an Ansible Role for ClusterControl. It is available at Ansible Galaxy. And as a reminder, for those of you who are automating with Puppet or Chef, we already published a Puppet Module and Chef Cookbook for ClusterControl.

Read the blog

That’s it for this week! Feel free to share these resources with your colleagues and follow us in our social media channels.

Have a good end of the week,

Jean-Jérôme Schmidt
Planets9s Editor
Severalnines AB

by Severalnines at April 28, 2016 07:32 AM

April 27, 2016

Peter Zaitsev

Upgrading to MySQL 5.7, focusing on temporal types

temporal types

temporal typesIn this post, we’ll discuss how MySQL 5.7 handles the old temporal types during an upgrade.

MySQL changed the temporal types in MySQL 5.6.4, and it introduced a new feature: microseconds resolution in the TIME, TIMESTAMP and DATETIME types. Now these parameters can be set down to microsecond granularity. Obviously, this means format changes, but why is this important?

Are they converted automatically to the new format?

If we had tables in MySQL 5.5 that used TIME, TIMESTAMP or DATETIME are these fields are going to be converted to the new format when upgrading to 5.6? The answer is “NO.” Even if we run mysql_upgrade, it does not warn us about the old format. If we check the MySQL error log, we cannot find anything regarding this. But the newly created tables are going to use the new format so that we will have two different types of temporal fields.

How can we find these tables?

The following query gives us a summary on the different table formats:

SELECT CASE isc.mtype
 WHEN '6' THEN 'OLD'
 WHEN '3' THEN 'NEW'
 END FORMAT,
 count(*) TOTAL
FROM information_schema.tables AS t
INNER JOIN information_schema.columns AS c ON c.table_schema = t.table_schema
AND c.table_name = t.table_name
LEFT OUTER JOIN information_schema.innodb_sys_tables AS ist ON ist.name = concat(t.table_schema,'/',t.table_name)
LEFT OUTER JOIN information_schema.innodb_sys_columns AS isc ON isc.table_id = ist.table_id
AND isc.name = c.column_name
WHERE c.column_type IN ('time','timestamp','datetime')
 AND t.table_schema NOT IN ('mysql','information_schema','performance_schema')
 AND t.table_type = 'base table'
 AND (t.engine = 'innodb')
GROUP BY isc.mtype;

+--------+-------+
| FORMAT | TOTAL |
+--------+-------+
| NEW    | 1     |
| OLD    | 9     |
+--------+-------+

Or we can use show_old_temporals, which will highlight the old formats during a

show create table
.

CREATE TABLE `mytbl` (
  `ts` timestamp /* 5.5 binary format */ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `dt` datetime /* 5.5 binary format */ DEFAULT NULL,
  `t` time /* 5.5 binary format */ DEFAULT NULL
) DEFAULT CHARSET=latin1

MySQL can handle both types, but with the old format you cannot use microseconds, and the default DATETIME takes more space on disk.

Can I upgrade to MySQL 5.7?

Of course you can! But when

mysql_upgrade
 is running it is going to convert the old fields into the new format by default. This basically means an
alter table
 on every single table, which will contain one of the three types.

Depending on the number of tables, or the size of the tables, this could take hours – so you may need to do some planning.

....
test.t1
error : Table rebuild required. Please do "ALTER TABLE `t1` FORCE" or dump/reload to fix it!
test.t2
error : Table rebuild required. Please do "ALTER TABLE `t2` FORCE" or dump/reload to fix it!
test.t3
error : Table rebuild required. Please do "ALTER TABLE `t3` FORCE" or dump/reload to fix it!
Repairing tables
mysql.proxies_priv OK
`test`.`t1`
Running : ALTER TABLE `test`.`t1` FORCE
status : OK
`test`.`t2`
Running : ALTER TABLE `test`.`t2` FORCE
status : OK
`test`.`t3`
Running : ALTER TABLE `test`.`t3` FORCE
status : OK
Upgrade process completed successfully.
Checking if update is needed.

Can we avoid this at upgrade?

We can run

alter tables
 or use pt-online-schema-schange (to avoid locking) before an upgrade, but even without these preparations we can still avoid incompatibility issues.

My colleague Daniel Guzman Burgos pointed out that 

mysql_upgrade
 has an option called upgrade-system-tables. This will only upgrade the system tables, and nothing else.

Can we still write these fields?

The following query returns the schema and the table names that still use the old formats.

SELECT CASE isc.mtype
           WHEN '6' THEN 'OLD'
           WHEN '3' THEN 'NEW'
       END FORMAT,
       t.schema_name,
       t.table_name
FROM information_schema.tables AS t
INNER JOIN information_schema.columns AS c ON c.table_schema = t.table_schema
AND c.table_name = t.table_name
LEFT OUTER JOIN information_schema.innodb_sys_tables AS ist ON ist.name = concat(t.table_schema,'/',t.table_name)
LEFT OUTER JOIN information_schema.innodb_sys_columns AS isc ON isc.table_id = ist.table_id
AND isc.name = c.column_name
WHERE c.column_type IN ('time','timestamp','datetime')
    AND t.table_schema NOT IN ('mysql','information_schema','performance_schema')
    AND t.table_type = 'base table'
    AND (t.engine = 'innodb');

+--------+--------------+------------+
| FORMAT | table_schema | table_name |
+--------+--------------+------------+
| OLD    | test         | t          |
| OLD    | test         | t          |
| OLD    | test         | t          |
| NEW    | sys          | sys_config |
+--------+--------------+------------+
4 rows in set (0.03 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.11-4  |
+-----------+
1 row in set (0.00 sec)

As we can see, we’re using 5.7 and table “test.t” still has the old format.

The schema:

CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t1` time DEFAULT NULL,
`t2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`t3` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

mysql> select * from t;
+----+----------+---------------------+---------------------+
| id |    t1    |       t2            |        t3           |
+----+----------+---------------------+---------------------+
| 1  | 20:28:00 | 2016-04-09 01:41:58 | 2016-04-23 22:22:01 |
| 2  | 20:28:00 | 2016-04-09 01:41:59 | 2016-04-23 22:22:02 |
| 3  | 20:28:00 | 2016-04-09 01:42:01 | 2016-04-23 22:22:03 |
| 4  | 20:28:00 | 2016-04-09 01:42:03 | 2016-04-23 22:22:04 |
| 5  | 20:28:00 | 2016-04-09 01:42:08 | 2016-04-23 22:22:05 |
+----+----------+---------------------+---------------------+

Let’s try to insert a new row:

mysql> insert into `t` (t1,t3) values ('20:28','2016:04:23 22:22:06');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t;
+----+----------+---------------------+---------------------+
| id |    t1    |         t2          |          t3         |
+----+----------+---------------------+---------------------+
| 1  | 20:28:00 | 2016-04-09 01:41:58 | 2016-04-23 22:22:01 |
| 2  | 20:28:00 | 2016-04-09 01:41:59 | 2016-04-23 22:22:02 |
| 3  | 20:28:00 | 2016-04-09 01:42:01 | 2016-04-23 22:22:03 |
| 4  | 20:28:00 | 2016-04-09 01:42:03 | 2016-04-23 22:22:04 |
| 5  | 20:28:00 | 2016-04-09 01:42:08 | 2016-04-23 22:22:05 |
| 6  | 20:28:00 | 2016-04-09 01:56:38 | 2016-04-23 22:22:06 |
+----+----------+---------------------+---------------------+
6 rows in set (0.00 sec)

It was inserted without a problem, and we can’t see any related info/warnings in the error log.

Does the Replication work?

In many scenarios, when you are upgrading a replicaset, the slaves are upgraded first. But will the replication work? The short answer is “yes.” I configured row-based replication between MySQL 5.6 and 5.7. The 5.6 was the master, and it had all the temporal types in the old format. On 5.7, I had new and old formats.

I replicated from old format to old format, and from old format to new format, and both are working.

Conclusion

Before upgrading to MySQL 5.7, tables should be altered to use the new format. If it isn’t done, however, the upgrade is still possible without altering all the tables – the drawbacks are you cannot use microseconds, and it takes more space on disk. If you had to upgrade to 5.7, however, you could change the format later using

alter table
 or pt-online-schema-schange.

 

by Tibor Korocz at April 27, 2016 08:30 PM

Valeriy Kravchuk

Building MaxScale 1.4.2 form GitHub on Fedora 23

MariaDB MaxScale is mentioned in many blog posts recently. It's Application of the Year 2016 after all! I'd like to test it, follow posts like this etc, all that on my favorite and readily available testing platforms that are now Ubuntu of all kinds and, surely, Fedora 23 (on my wife's workstation, the most powerful hardware at hand).

My old habits force me to build open source software I test from source, and I do not want to even discuss the topic of "MaxScale binaries availability" that was quite "popular" some time ago. So, after building MaxScale 1.4.1 on CentOS 6.7 back on March 31, 2016 (mostly just following MariaDB KB article on the topic) using libmysqld.a from MariaDB 10.0.23, this morning I decided to check new branch, 1.4.2, and build it on Fedora 23, following that same KB article (that unfortunately does not even mention Fedora after the fix to MXS-248). Thing is, Fedora is not officially supported as a platform for MaxScale 1.4.x, but why should we, those who can build things from source for testing purposes, care about this?

I started with cloning MaxScale:

git clone https://github.com/mariadb-corporation/MaxScale.git
cd MaxScale
and then:

[openxs@fc23 MaxScale]$ git branch -r
...
  origin/HEAD -> origin/develop
...
  origin/release-1.4.2
...
I remember spending enough time fighting with develop branch while building on CentOS 6.7, mostly with sqlite-related things it contained, so this time I proceed immediately to the branch I want to build:

[openxs@fc23 MaxScale]$ git checkout release-1.4.2
Branch release-1.4.2 set up to track remote branch release-1.4.2 from origin.
Switched to a new branch 'release-1.4.2'
[openxs@fc23 MaxScale]$ git branch
  develop
* release-1.4.2

[openxs@fc23 MaxScale]$ mkdir build
[openxs@fc23 MaxScale]$ cd build

Last two steps originate from the KB article. We are almost ready for building, but what about the prerequisites? I've collected all the packages required for CentOS in that article and tried to install them all:
[openxs@fc23 build]$ sudo yum install mariadb-devel mariadb-embedded-devel libedit-devel gcc gcc-c++ ncurses-devel bison flex glibc-devel cmake libgcc perl make libtool openssl-devel libaio libaio-devel librabbitmq-devel libcurl-devel pcre-devel rpm-build[sudo] password for openxs:
Yum command has been deprecated, redirecting to '/usr/bin/dnf install mariadb-devel mariadb-embedded-devel libedit-devel gcc gcc-c++ ncurses-devel bison flex glibc-devel cmake libgcc perl make libtool openssl-devel libaio libaio-devel librabbitmq-devel libcurl-devel pcre-devel rpm-build'.
See 'man dnf' and 'man yum2dnf' for more information.
To transfer transaction metadata from yum to DNF, run:
'dnf install python-dnf-plugins-extras-migrate && dnf-2 migrate'

Last metadata expiration check: 0:26:04 ago on Wed Apr 27 10:43:24 2016.
Package gcc-5.3.1-6.fc23.x86_64 is already installed, skipping.
...
Package pcre-devel-8.38-7.fc23.x86_64 is already installed, skipping.
Dependencies resolved.
================================================================================
 Package                  Arch     Version                      Repository
                                                                           Size
================================================================================
Installing:
 autoconf                 noarch   2.69-21.fc23                 fedora    709 k
 automake                 noarch   1.15-4.fc23                  fedora    695 k
 dwz                      x86_64   0.12-1.fc23                  fedora    106 k
 flex                     x86_64   2.5.39-2.fc23                fedora    328 k
 ghc-srpm-macros          noarch   1.4.2-2.fc23                 fedora    8.2 k
 gnat-srpm-macros         noarch   2-1.fc23                     fedora    8.4 k
 go-srpm-macros           noarch   2-3.fc23                     fedora    8.0 k
 libcurl-devel            x86_64   7.43.0-6.fc23                updates   590 k
 libedit-devel            x86_64   3.1-13.20150325cvs.fc23      fedora     34 k
 librabbitmq              x86_64   0.8.0-1.fc23                 updates    43 k
 librabbitmq-devel        x86_64   0.8.0-1.fc23                 updates    52 k
 libtool                  x86_64   2.4.6-8.fc23                 updates   707 k
 mariadb-common           x86_64   1:10.0.23-1.fc23             updates    74 k
 mariadb-config           x86_64   1:10.0.23-1.fc23             updates    25 k
 mariadb-devel            x86_64   1:10.0.23-1.fc23             updates   869 k
 mariadb-embedded         x86_64   1:10.0.23-1.fc23             updates   4.0 M
 mariadb-embedded-devel   x86_64   1:10.0.23-1.fc23             updates   8.3 M
 mariadb-errmsg           x86_64   1:10.0.23-1.fc23             updates   199 k
 mariadb-libs             x86_64   1:10.0.23-1.fc23             updates   637 k
 ocaml-srpm-macros        noarch   2-3.fc23                     fedora    8.1 k
 patch                    x86_64   2.7.5-2.fc23                 fedora    123 k
 perl-Thread-Queue        noarch   3.07-1.fc23                  updates    22 k
 perl-generators          noarch   1.06-1.fc23                  updates    15 k
 perl-srpm-macros         noarch   1-17.fc23                    fedora    9.7 k
 python-srpm-macros       noarch   3-7.fc23                     updates   8.1 k
 redhat-rpm-config        noarch   36-1.fc23.1                  updates    59 k
 rpm-build                x86_64   4.13.0-0.rc1.13.fc23         updates   137 k

Transaction Summary
================================================================================
Install  27 Packages

Total download size: 18 M
Installed size: 64 M
Is this ok [y/N]: Y

...

Complete!
Now, let's try simple approach:

[openxs@fc23 build]$ cmake ..
...
-- MySQL version: 10.0.23
-- MySQL provider: MariaDB
-- Looking for pcre_stack_guard in MYSQL_EMBEDDED_LIBRARIES_STATIC-NOTFOUND
CMake Error: The following variables are used in this project, but they are set to NOTFOUND.
Please set them or make sure they are set and tested correctly in the CMake files:
MYSQL_EMBEDDED_LIBRARIES_STATIC
    linked by target "cmTC_2494a" in directory /home/openxs/git/MaxScale/build/CMakeFiles/CMakeTmp

CMake Error: Internal CMake error, TryCompile configure of cmake failed
-- Looking for pcre_stack_guard in MYSQL_EMBEDDED_LIBRARIES_STATIC-NOTFOUND - not found
-- PCRE libs: /usr/lib64/libpcre.so
-- PCRE include directory: /usr/include
-- Embedded mysqld does not have pcre_stack_guard, linking with system pcre.
CMake Error at cmake/FindMySQL.cmake:115 (message):
  Library not found: libmysqld.  If your install of MySQL is in a non-default
  location, please provide the location with -DMYSQL_EMBEDDED_LIBRARIES=<path
  to library>
Call Stack (most recent call first):
  CMakeLists.txt:37 (find_package)


-- Configuring incomplete, errors occurred!
See also "/home/openxs/git/MaxScale/build/CMakeFiles/CMakeOutput.log".
See also "/home/openxs/git/MaxScale/build/CMakeFiles/CMakeError.log".

Failure, cmake can not find libmysqld.a it seems. Let me try to find it:

[openxs@fc23 build]$ sudo find / -name libmysqld.a 2>/dev/null
/home/openxs/git/percona-xtrabackup/libmysqld/libmysqld.a
/home/openxs/dbs/5.7/lib/libmysqld.a
/home/openxs/dbs/p5.6/lib/libmysqld.a
/home/openxs/dbs/fb56/lib/libmysqld.a
/home/openxs/10.1.12/lib/libmysqld.a
That's all, even though I installed all packages that looked as required based on the article! I have the library in many places (in my own builds and even in sandbox with MariaDB 10.1.12), but it's not installed where expected. Some more desperate tries (installing MariaDB server with sudo yum install mariadb-server, searches for package that provides libmysqld.a etc), chat with engineers of MariaDB and I've ended up with the fact that my packages are from Fedora (not MariaDB) and they just do not include the static library. Looks like a bug in Fedora packaging, if you ask me.

I was not ready to add MariaDB's repository at the moment (to get MariaDB-devel etc, something KB article also suggests for supported platforms), so I decided that it would be fair just to build current MariaDB 10.1.13 from source and use everything needed from there. Last time I built 10.2 branch, so I had to check out 10.1 first:
[openxs@fc23 server]$ git checkout 10.1
Switched to branch '10.1'
Your branch is behind 'origin/10.1' by 2 commits, and can be fast-forwarded.
  (use "git pull" to update your local branch)
[openxs@fc23 server]$ git pull
Updating 1cf852d..071ae30
Fast-forward
 client/mysqlbinlog.cc                    | 523 ++++++++++++++++++++++---------
 mysql-test/r/mysqlbinlog_raw_mode.result | 274 ++++++++++++++++
 mysql-test/t/mysqlbinlog_raw_mode.test   | 387 +++++++++++++++++++++++
 sql/sql_priv.h                           |   3 +-
 storage/innobase/dict/dict0boot.cc       |  20 +-
 storage/xtradb/dict/dict0boot.cc         |  20 +-
 6 files changed, 1062 insertions(+), 165 deletions(-)
 create mode 100644 mysql-test/r/mysqlbinlog_raw_mode.result
 create mode 100644 mysql-test/t/mysqlbinlog_raw_mode.test
 Then I've executed the following while in server directory:

make clean
rm CMakeCache.txt
cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DWITH_SSL=system -DWITH_ZLIB=bundled -DMYSQL_MAINTAINER_MODE=0 -DENABLED_LOCAL_INFILE=1 -DWITH_JEMALLOC=system -DWITH_WSREP=ON -DWITH_INNODB_DISALLOW_WRITES=ON -DWITH_EMBEDDED_SERVER=ON -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/maria10.1

make

make install && make clean
Note that I've explicitly asked to build embedded server. I checked that the library is in the location I need:

[openxs@fc23 server]$ sudo find / -name libmysqld.a 2>/dev/null
/home/openxs/git/percona-xtrabackup/libmysqld/libmysqld.a
/home/openxs/dbs/maria10.1/lib/libmysqld.a
/home/openxs/dbs/5.7/lib/libmysqld.a
/home/openxs/dbs/p5.6/lib/libmysqld.a
/home/openxs/dbs/fb56/lib/libmysqld.a
/home/openxs/10.1.12/lib/libmysqld.a
Then I moved back to MaxScale/build directory and explicitly pointed out the location of headers, library and messages that I want to use with MaxScale:

[openxs@fc23 build]$ cmake .. -DMYSQL_EMBEDDED_INCLUDE_DIR=/home/openxs/dbs/maria10.1/include/mysql -DMYSQL_EMBEDDED_LIBRARIES=/home/openxs/dbs/maria10.1/lib/libmysqld.a -DERRMSG=/home/openxs/dbs/maria10.1/share/english/errmsg.sys -DCMAKE_INSTALL_PREFIX=/home/openxs/maxscale -DWITH_MAXSCALE_CNF=N
...
-- Build files have been written to: /home/openxs/git/MaxScale/build

[openxs@fc23 build]$ make
...
[ 95%] [BISON][ruleparser] Building parser with bison 3.0.4
ruleparser.y:34.1-13: warning: deprecated directive, use Б-?%name-prefixБ-? [-Wdeprecated]
 %name-prefix="dbfw_yy"
 ^^^^^^^^^^^^^
[ 96%] Building C object server/modules/filter/dbfwfilter/CMakeFiles/dbfwfilter.dir/ruleparser.c.o
[ 96%] Building C object server/modules/filter/dbfwfilter/CMakeFiles/dbfwfilter.dir/token.c.o
[ 97%] Linking C shared library libdbfwfilter.so
[ 97%] Built target dbfwfilter
Scanning dependencies of target maxadmin
[ 98%] Building C object client/CMakeFiles/maxadmin.dir/maxadmin.c.o
[ 98%] Linking C executable maxadmin
[100%] Built target maxadmin

It seems build completed without problems this time. We can try to test it (some tests do fail):







[openxs@fc23 build]$ make testcore
...
 1/22 Test  #1: Internal-TestQueryClassifier .....***Exception: Other  0.35 sec
      Start  2: Internal-CanonicalQuery
 2/22 Test  #2: Internal-CanonicalQuery ..........***Failed    0.25 sec
      Start  3: Internal-CanonicalQuerySelect
 3/22 Test  #3: Internal-CanonicalQuerySelect ....***Failed    0.04 sec
      Start  4: Internal-CanonicalQueryAlter
 4/22 Test  #4: Internal-CanonicalQueryAlter .....***Failed    0.04 sec
      Start  5: Internal-CanonicalQueryComment
 5/22 Test  #5: Internal-CanonicalQueryComment ...***Failed    0.04 sec
      Start  6: Internal-TestAdminUsers
 6/22 Test  #6: Internal-TestAdminUsers ..........   Passed    0.44 sec
      Start  7: Internal-TestBuffer
 7/22 Test  #7: Internal-TestBuffer ..............   Passed    0.01 sec
      Start  8: Internal-TestDCB
 8/22 Test  #8: Internal-TestDCB .................   Passed    0.01 sec
      Start  9: Internal-TestFilter
 9/22 Test  #9: Internal-TestFilter ..............   Passed    0.03 sec
...
(As a side note, make install in my case had NOT installed anything to /home/openxs/maxscale, something to deal with later, as on CentOS 6.7 it worked...)


In any case, I now have binaries to work with, of version 1.4.2:

[openxs@fc23 build]$ ls bin/
maxadmin  maxbinlogcheck  maxkeys  maxpasswd  maxscale
[openxs@fc23 build]$ bin/maxscale --version


MariaDB Corporation MaxScale 1.4.2      Wed Apr 27 13:24:01 2016
------------------------------------------------------
MaxScale 1.4.2

[openxs@fc23 build]$ bin/maxadmin --version
bin/maxadmin Version 1.4.2
To be continued one day... Stay tuned!




by Valeriy Kravchuk (noreply@blogger.com) at April 27, 2016 07:07 PM

MariaDB AB

MariaDB Server growth bolstered by OpenStack User Survey

colin

While many are at the ongoing OpenStack Summit in Austin, it came to my attention that the OpenStack project has recently published their latest user survey. From there, it is clear that there is growth in the usage of MariaDB Server -- up 6 points from the previous survey. MariaDB Galera Cluster dropped by 1 point, but it's worth noting that in MariaDB Server 10.1, you're getting it all as an integrated download so it is quite likely that people are just referring to it as "MariaDB" now.

Which databases are used for OpenStack components

(Screenshot taken from the user survey)

While MySQL still dominates at 35 percentage points, the largest pie of production OpenStack deployments are either based on MariaDB Server or MariaDB Galera Cluster which are developed by both the MariaDB Corporation & the MariaDB Foundation.

Some may say this is because "defaults matter" (as MariaDB Server gets to be the default in many Linux distributions), but the majority of OpenStack deployments today are using Ubuntu Server, which ships MySQL as a default provider when one requests for MySQL. So this is a conscious choice people are making to go with MariaDB Server or MariaDB Galera Cluster.

Thank you OpenStack deployers! If you ever want to talk about your deployment, feel free to drop me a line or even tweet @bytebot.

About the Author

colin's picture

Colin Charles is the Chief Evangelist for MariaDB since 2009, work ranging from speaking engagements to consultancy and engineering works around MariaDB. He lives in Kuala Lumpur, Malaysia and had worked at MySQL since 2005, and been a MySQL user since 2000. Before joining MySQL, he worked actively on the Fedora and OpenOffice.org projects. He's well known on the conference track having spoken at many of them over the course of his career.

by colin at April 27, 2016 02:20 PM

Jean-Jerome Schmidt

Watch the replay: Become a MongoDB DBA (if you’re really a MySQL user)

Thanks to everyone who participated in this week’s webinar on ‘Become a MongoDB DBA’! Our colleague Art van Scheppingen presented from the perspective of a MySQL DBA who might be called to manage a MongoDB database, which included a live demo on how to carry out the relevant DBA tasks using ClusterControl.

The replay and the slides are now available online in case you missed Tuesday’s live session or simply would like to see it again in your own time.

Watch the replay Read the slides

This was the first session of our new webinar series: ‘How to Become a MongoDB DBA’ to answer the question: ‘what does a MongoDB DBA do’?

In this initial webinar, we went beyond the deployment phase and demonstrated how you can automate tasks, monitor a cluster and manage MongoDB; whilst also automating and managing your MySQL and/or PostgreSQL installations. Watch out for invitations for the next session in this series!

This Session's Agenda

  • Introduction to becoming a MongoDB DBA
  • Installing & configuring MongoDB
  • What to monitor and how
  • How to perform backups
  • Live Demo

Speaker

Art van Scheppingen is a Senior Support Engineer at Severalnines. He’s a pragmatic MySQL and Database expert with over 15 years experience in web development. He previously worked at Spil Games as Head of Database Engineering, where he kept a broad vision upon the whole database environment: from MySQL to Couchbase, Vertica to Hadoop and from Sphinx Search to SOLR. He regularly presents his work and projects at various conferences (Percona Live, FOSDEM) and related meetups.

This series is based upon the experience we have using MongoDB and implementing it for our database infrastructure management solution, ClusterControl. For more details, read through our ‘Become a ClusterControl DBA’ blog series.

by Severalnines at April 27, 2016 12:49 PM

MariaDB AB

Automated server management with opensource tools

colin

In a recent presentation by Balazs Pocze from Gawker Media LLC made clear that there are many automation strategies for server management that would be worth packaging up into an extended solution like MariaDB Enterprise.

Automation is good because there are way less "ops people" than "dev people" at many companies. Man-hours are expensive, so you don't want to waste them on routine tasks. Automated tasks also mean that there is a lot less possibility to make mistakes. "Everybody needs automation," says Pocze.

Gawker still uses physical infrastructure, powered by Linux, though there is some migration to the cloud. Cobbler is their Linux installation server of choice, where the hosts are defined, and there is no manual DHCP or DNS management; this tool not only manages but also defines their environment.

For provisioning the hosts, Gawker's tool of choice is Puppet. Puppet manages all the hosts as well as defines the hosts, and Pocze said, "If it doesn't exist in Puppet, it doesn't exist at all". From a developer enablement standpoint, MariaDB Enterprise provides the MariaDB Enterprise Chef Cookbook, which we could say is as close as can be.

In a bit of a twist, they also make use of Ansible for running commands on hosts; all commands are organised into playbooks, and it is all about agentless installation and management. They do create modules and contribute them, and all nodes are managed over SSH.

To wrap it all up, they use Jenkins for continuous integration. The demo of how Gawker's Kinja is maintained by Jenkins, with the Ansible playbooks for slave management was also a highlight.

It would be great if more shared what their backend infrastructure and ops ran like. Because its clear that there's plenty of opportunity to have tooling finesse.

Don't forget to check out banyek on github as well as gawkermedia on github. During the talk, an audience member said it might also be a great idea to checkout Rundeck - a job scheduler and runbook automation, something I have not heard of before but will definitely look at in the near future.

About the Author

colin's picture

Colin Charles is the Chief Evangelist for MariaDB since 2009, work ranging from speaking engagements to consultancy and engineering works around MariaDB. He lives in Kuala Lumpur, Malaysia and had worked at MySQL since 2005, and been a MySQL user since 2000. Before joining MySQL, he worked actively on the Fedora and OpenOffice.org projects. He's well known on the conference track having spoken at many of them over the course of his career.

by colin at April 27, 2016 09:27 AM

April 26, 2016

Peter Zaitsev

How We Made MySQL Great Again, or Upgrading MySQL with Orchestrator

Upgrading MySQL with Orchestrator

Upgrading MySQL with OrchestratorIn this blog post, we’ll discuss upgrading MySQL with Orchestrator.

I recently had a client, Life360, that wanted to upgrade from Percona Server 5.5 to Percona Server 5.6, and implement GTID in their high transaction environment. They had co-masters and multiple read slaves.

Orchestrator made this job much easier for us. My colleague, Tibi, recently posted about Orchestrator here and here.

Daniel from Life360 saw Orchestrator and was very interested. So here is how he setup Orchestrator in his own words:

I did some initial testing with the vagrant boxes provided in the Orchestrator repo, to see how to configure the agents and get the Orchestrator server to do what we want.

I then moved to install the Orchestrator server, Orchestrator backend on RDS, and deploy the clients on the slaves and masters in our Amazon VPC MySQL instances.

Once the server setup was done, the clients were auto-detected through CNAME discovery of the masters, and the agents talked to the server (it took a while as CNAMES wasn’t working as expected, but that’s fixed in the new server version).

We were pretty amazed at the number of actions you can do through orchestrator itself, such as: moving slaves to a different master through drag and drop, enabling GTID on a node with the push of a button, setting up GTID based failover, taking LVM snapshots using Orchestrator Agent, etc.

We went ahead and tested the master change on drag and drop, and after a few successful attempts, we even brought it back to where it was initially. After those tests, we were pretty confident that we could leverage Orchestrator as one of our main tools to assist in the coming upgrade.

Here is a screenshot of the initial setup:

image02

Manjot: Once Daniel had Orchestrator setup, he wanted to leverage it to help with the MySQL upgrade. We set out to create a plan that worked within his constraints and still kept best practices in mind.

First, we installed Percona Server 5.6 fresh on our dedicated backup slave. That first 5.6 slave was created with MyDumper to achieve forward compatibility and not have any legacy tablespaces. Since MyDumper was already installed with the Percona Backup Service that Life360 has, this was fairly easy to accomplish.

The MyDumper slave rebuild works in the following way:

To take a mydumper backup:

  1. Go to your desired backups directory
  2. Install mydumper (sudo apt-get install mydumper)
  3. mydumper -t 8 -L mydumper.log –compress

To restore:

  1. Make sure MyDumper is installed: sudo apt-get install mydumper
  2. Copy the MyDumper backups over to a backups dir
  3. Export your BACKUP_DIR as env var
  4. Run this to restore with MyLoader (from https://gist.github.com/Dnile/4658b338d4a101cbe2eeb5080ebddf8e):
    #!/usr/bin/env sh
    cd $BACKUP_DIR
    export DESTHOST=127.0.0.1
    export BACKUP_DIR=/vol_mysql/backups
    mysqld --skip-grant-tables &
    for i in `ls -1 *-schema.dump.gz | cut -d'-' -f1`; do mysql -h $DESTHOST -e "CREATE DATABASE IF NOT EXISTS $i"; zcat $i-schema.dump.gz | mysql -h $DESTHOST $i; zcat $i-schema-post.dump.gz | mysql -h $DESTHOST $i; done
    /usr/bin/myloader --host=$DESTHOST --directory=$BACKUP_DIR --enable-binlog --threads=10 --queries-per-transaction=20 -v 3
    chown -R mysql:mysql /var/lib/mysql/

Once the first 5.6 slave was caught up, we used Xtrabackup to backup 5.6 and then restored to each slave, cycling them out of the read slave pool one at a time.

Once all the slaves were upgraded, we created a new 5.6 master and had it replicate off our primary 5.5 master.

Then we moved all of the slaves to replicate off the new 5.6 master.

Life360 had long cron jobs that ran on the second 5.5 master. We moved the cron applications to write to the primary 5.5 master, and locked all tables. We then stopped replication on the second co-master. Daniel stopped MySQL and decommissioned it.

We then moved all application writes to the new 5.6 master. While Orchestrator can use external scripts to move IPs, we used a manual process here to change application DSNs and HAProxy configuration.

On the 5.5 master that remained, we used Orchestrator to set it to read only.

image01

Daniel says this didn’t do a whole lot to get rid of connections that were still open on this server.

On the new master, we used the stop slave and reset slave buttons in the Orchestrator panel so it would no longer slave from the old master.

Once some of the thousands of connections had moved to the new master, we stopped MySQL on the 5.5 master, which took care of the rest and the application “gracefully” reconnected to the new 5.6 master.

There was some write downtime, as some connections did not drop off until they were forced to because php-fpm refused to let go. There is also always a high volume of transactions in this environment.

At this point our topology looks like this (ignore the globe icons for now):

image00

But as always Daniel wanted MOAR. It was time for GTID. While we could have done this during the upgrade, Life360 wanted to manage risk and not make too many production changes at one time.

We followed Percona’s guide, Online GTID Deployment, but used Orchestrator to shuffle the old and new masters and toggle read_only on and off. This made our job a lot easier and faster, and saved us from any downtime.

The globes in the topology screenshot above show that the slaves are now using GTID replication.

Orchestrator makes upgrades and changes much easier than before, just use caution and understand what it is doing in the background.

 

by Manjot Singh at April 26, 2016 07:56 PM

Jean-Jerome Schmidt

Top Nine Stand Out Facts About Percona Live in Santa Clara

We like our 9s at Severalnines, so here is our list of Top 9 Facts (in no particular order) that made the trip to Santa Clara worthwhile once again for this year’s Percona Live Conference. Thanks to the team at Percona and everyone else involved in keeping the Santa Clara tradition going by organising another great conference this year.

1. The conference opened its arms not only to the “traditional” MySQL community, but also put a focus on MongoDB users.

This year in particular, there was a real intent to provide a place to learn and mingle for those database users who deal with not only one datastore, but manage a mixed database environment. More and more users are called to operate in polyglot database landscapes and thus it was good to see this year’s conference provide a platform for that. Percona themselves of course now serve both the MySQL and MongoDB user community with their tools and offerings, and companies like ourselves strive to help organisations with polyglot IT environments with our solutions and resources as well. So it was great to see a good mix of tutorial and talk topics as well as of sponsors at the conference.

2. Galera Cluster continues to be a popular topic in the MySQL universe

With plenty of talks and tutorials discussing Galera Cluster and how to make best use of it, the technology developed by the Codership team confirmed its growing popularity with MySQL users. We ourselves see a lot of interest in it and we blog / talk about Galera Cluster on a regular basis, so it was good to hear about the latest and greatest on it and talk to users directly about their clustering needs.

3. The Oracle MySQL Team is very present in the community

As per last year, it was good to see the Oracle MySQL Team so present at the conference, perhaps even more so than previously. Some great work has been done with MySQL 5.7 and it was good to hear about the MySQL Document Store. The MySQL community has been evolving over the years with its particular history, and it seems that there is a good balance now. We’re looking forward of course to see what the team have in store next for MySQL!

4. Facebook’s RocksDB is gaining traction

Facebook developed RocksDB by forking and improving LevelDB. The RocksDB storage engine is available for both MongoDB and MySQL. Many of the talks at the conference were about the ins- and outs of MongoRocks and MyRocks. We foresee a great future and adoption for this innovation by Facebook.

5. MySQL In Containers

A number of talks addressed the issues users can be faced with when deploying MySQL in containerized environments. Containers allow for rapid creation and deletion, and a lot of care has to be taken when deploying MySQL; especially in production. Going forward it will most likely be common to see MySQL servers running in container environments.

6. Bill Nye’s keynote

After Steve Wozniak last year, this year's invited keynote speaker was Bill Nye. Bill is better known as The Science Guy and with his vision he gives a twist on tech and science. His keynote gave a lot of food for thought, but just in case you have missed it you can watch the recording here: https://www.youtube.com/watch?v=iQDrK3rEtWg

7. Did we mention that MongoDB was a big topic this year?

For everyone who’s been following Percona’s news, you’ll know that Percona is investing in MongoDB following their acquisition of Tokutek last year with the result of the new Percona Server for MongoDB. In addition to that, quite a few MongoDB related talks had been submitted (and accepted) for this year’s conference, which was good to see. One of them being our talk on how to manage MongoDB as a MySQL DBA. So while the prevalence of MongoDB as a topic might still seem unusual to those used to the MySQL user conference, it’s an interesting development that we’re keeping an eye on.

8. On a self-interested note: we hosted a ‘How to become a MySQL DBA’ tutorial this year!

Our colleagues Art van Scheppingen and Ashraf Sharif hosted a tutorial on ‘How to become a MySQL DBA’. It was great to spend the day with database enthusiasts who were keen to learn how to manage MySQL and share experiences with them. The tutorial was based on same-titled blog series, which has proven quite popular with our blog followers. Feel free to check it out.

9. Meet the friends!

We kind of have to part on that note: of course the conference is always a week to look forward to in the year, as it’s really the place where we get to see a lot of friends from past and present in the MySQL (and now MongoDB) community. It really is a grand tradition and it’s good to see that so many members of the community are to keen to continue to keep that going.

So here’s to seeing you all next year again!

by Severalnines at April 26, 2016 07:34 PM

MariaDB Foundation

MariaDB Galera Cluster 5.5.49 and updated connectors now available

The MariaDB project is pleased to announce the immediate availability of MariaDB Galera Cluster 5.5.49, MariaDB Connector/J 1.4.3, and MariaDB Connector/C 2.2.3. See the release notes and changelogs for details on these releases. Download MariaDB Galera Cluster 5.5.49 Release Notes Changelog What is MariaDB Galera Cluster? MariaDB APT and YUM Repository Configuration Generator Download MariaDB […]

The post MariaDB Galera Cluster 5.5.49 and updated connectors now available appeared first on MariaDB.org.

by Daniel Bartholomew at April 26, 2016 05:53 PM

April 25, 2016

MariaDB AB

Top database challenges for MariaDB

janlindstrom

The latest top research identifies a number of big trends and challenges for databases. Jan Lindström, developer at MariaDB, summarises the key issues and challenges and how MariaDB approaches them.

First of all, the opinions expressed in this article are the author's own and do not necessary reflect the view of the MariaDB Corporation. These views are based on the review article by Abadi, et.all.: The Beckman Report on Database Research, Communications of the ACM, Vol. 59, NO. 02, 02/2016. While this meeting with thirty leaders from the database research community met in October 2013, my view is that issues raised in this meeting are still more than valid.

The review article identifies big data as a defining challenge of our time. This is because it has become cheaper to generate data due to inexpensive storage, sensors, smart devices, social software, multiplayer games, and the Internet of Things. Additionally, it has become cheaper to process large amounts of data, due to advances in multicore CPUs, solid state storage, cheap cloud computing, and open source software.

By 2020, the International Data Corporation (IDC) predicts that the amount of digital information created and replicated in the world will grow to almost 40 zettabytes (ZB)—more than 50 times what existed in 2010 and amounting to 5,247 gigabytes for every person on the planet (see http://www.datacenterjournal.com/birth-death-big-data/).

This means that organizations have more and more unstructured and unused data that could contain valuable information for predicting business trends and making business decisions. Forbes predicted in 2015 that Buying and selling data will become the new business bread and butter.

In the recent years the database research and development community has strengthened core research and development in relational DBMSs and branched out into new directions: security, privacy, data pricing, data attribution, social and mobile data, spatiotemporal data, personalization and contextualization, energy constrained processing, and scientific data management.

These lofty research challenges must be taken down on a functionality, if not even feature level. Here are some features we're working on, in a chewing-an-elephant-a-bite-at-a-time fashion.

The review article identifies five big data challenges: scalable big/fast data infrastructures; coping with diversity in data management; end-to-end processing of data; cloud services; and the roles of the people in the data life cycle. Three of the challenges deal with the volume, velocity, and variety aspects of big data. The last two challenges deal with extending big data applications in the cloud and managing the involvement of people in these applications.

How can MariaDB address these challenges? By developing new storage engines for Big Data like MariaDB ColumnStore (earlier InfiniDB). MariaDB ColumnStore is a scalable columnar database management system built for big data analytics, business intelligence, data warehousing and other read-intensive application. Column-store architecture enables very quick load and query times. Its massive parallel processing (MPP) technology scales with any type of storage hardware.

Furthermore, MariaDB can support new datatypes and SQL-functions like:

Forbes predicted on 2015 that Security will become the killer app for big data analytics. Now that MariaDB 10.1 server provides tools for data at rest encryption, other storage engines can easily provide security feature for their data.

However, as seen from research challenges there is a lot of room for additional vision and development on relational database management systems like MariaDB.

Tags: 

by janlindstrom at April 25, 2016 11:54 AM

Jean-Jerome Schmidt

Webinar tomorrow: Become a MongoDB DBA (if you’re really a MySQL user)

Join us tomorrow, as we introduce a new webinar series: ‘How to Become a MongoDB DBA’ to answer the question: ‘what does a MongoDB DBA do?’.

This is a series of three webinars during which we will walk you through the most important tasks a MongoDB DBA routinely goes through and provide you with options on how to best complete these tasks.

It is not uncommon for MySQL DBAs, developers, network/system administrators or DevOps folks with general background to find themselves in a situation where they’ve been working with MySQL for a while and are now being asked to also properly maintain one or more MongoDB instances. In fact, with more organisations operating polyglot environments, it’s starting to become commonplace.

In this first webinar, we will show you how you can automate tasks, monitor a cluster and manage MongoDB; whilst also automating and managing your MySQL and/or PostgreSQL installations.

Date, Time & Registration

Europe/MEA/APAC

Tuesday, April 26th at 09:00 BST / 10:00 CEST (Germany, France, Sweden)
Register Now

North America/LatAm

Tuesday, April 26th at 09:00 Pacific Time (US) / 12:00 Eastern Time (US)
Register Now

Agenda

  • Introduction to becoming a MongoDB DBA
  • Installing & configuring MongoDB
  • What to monitor and how
  • How to perform backups
  • Live Demo

Speaker

Art van Scheppingen is a Senior Support Engineer at Severalnines. He’s a pragmatic MySQL and Database expert with over 15 years experience in web development. He previously worked at Spil Games as Head of Database Engineering, where he kept a broad vision upon the whole database environment: from MySQL to Couchbase, Vertica to Hadoop and from Sphinx Search to SOLR. He regularly presents his work and projects at various conferences (Percona Live, FOSDEM) and related meetups.

We look forward to “seeing” you there!

This session is based upon the experience we have using MongoDB and implementing it for our database infrastructure management solution, ClusterControl. For more details, read through our ‘Become a ClusterControl DBA’ blog series.

by Severalnines at April 25, 2016 11:42 AM

MariaDB AB

AliSQL and some features that have made it into MariaDB Server

colin

AliSQL is the fork of MySQL (maintained by Alibaba) that powers all of the group from Tmall, Taobao, Alibaba.com, 1688.com, AliExpress, Cainiao and the payment's network Alipay.

AliSQL isn't new - they've had this fork since 2011: AliSQL 5.1 (Bugfixes for DDL, eliminate race conditions), 2012: AliSQL 5.5 (parallel replication, optimize hot SKUs), 2014: AliSQL 5.6 (Enhanced threadpool, SQL firewall). The team have found/reported/fixed over 40+ bugs, added 41 new features, and optimised 27 bottlenecks. All recent releases of MySQL have had AliSQL contributions (5.6.25 onwards, 5.7.8 onwards, and even 5.8 branch).

AliSQL benefitting MariaDB Server

While not mentioned in the talk, its clear that MariaDB Server 10.0+ has benefited from their tree as well -- our log of contributions lists Lixun Peng/Taobao as having contributed to multi-source replication (something MariaDB Server had in a shipping GA release since March 2014; a similar feature appears in MySQL 5.7 GA released October 2015). But that is not all, there is also per-thread memory counting and usage, fixed in MDEV-4011. Don't forget that they've constantly released many of their features as opensource.

So what is per-thread memory counting? It is exposed either via SHOW STATUS:

show status like 'memory_used'\G
*************************** 1. row ***************************
Variable_name: Memory_used
        Value: 73808
1 row in set (0.00 sec)

But that is obviously not all. Have you seen the INFORMATION_SCHEMA.PROCESSLIST? Compare the difference when it comes to MySQL 5.7.12 and MariaDB Server 10.1.13:

MySQL 5.7.12

select * from INFORMATION_SCHEMA.processlist\G
*************************** 1. row ***************************
     ID: 8
   USER: msandbox
   HOST: localhost
     DB: NULL
COMMAND: Query
   TIME: 0
  STATE: executing
   INFO: select * from INFORMATION_SCHEMA.processlist
1 row in set (0.00 sec)

MariaDB Server 10.1.13

select * from INFORMATION_SCHEMA.processlist\G
*************************** 1. row ***************************
           ID: 4
         USER: msandbox
         HOST: localhost
           DB: NULL
      COMMAND: Query
         TIME: 0
        STATE: Filling schema table
         INFO: select * from INFORMATION_SCHEMA.processlist
      TIME_MS: 0.464
        STAGE: 0
    MAX_STAGE: 0
     PROGRESS: 0.000
  MEMORY_USED: 84552
EXAMINED_ROWS: 0
     QUERY_ID: 24
  INFO_BINARY: select * from INFORMATION_SCHEMA.processlist
          TID: 22005
1 row in set (0.00 sec)

What can we look forward to in the future from AliSQL?

It was brought up in the talk that AliSQL is not currently opensource, but there are some very interesting features around it that would likely benefit many MariaDB Server users:

  • optimising hot rows, which is a common issue in Alibaba referred to as an "optimisation for hot SKUs", typical during a Single's Day. This has been powering AliSQL in several versions, and they are working on ensuring there is a row cache, a new InnoDB row lock type, as well as group updates of associated transactions.
  • They have split the redo log buffers into two, one for each reading and writing.
  • InnoDB column compression using ZLIB, which also supports online DDL.
  • The idea of flashback and a recycle bin, all related to the "time machine" concept that we can expect to see soon.
  • Another interesting feature is the idea of thinking in terms of a glass of water. There is low water mark protection in where they have an enhanced threadpool that buffers queries while the thread is running. If for some reason it exceeds the the low water level, send it to the high water mark where they have a kind of "SQL firewall" that denies queries via a blacklist strategy. Their rule syntax makes use of an Abstract Syntax Tree (AST) between the parser and optimiser of their server.
  • They also do binlog speed throttling, and have enhanced information around deadlocks.

All in, here's hoping that the AliSQL tree is open, so that we can look at features and see what ends up in a future release of MariaDB Server.

About the Author

colin's picture

Colin Charles is the Chief Evangelist for MariaDB since 2009, work ranging from speaking engagements to consultancy and engineering works around MariaDB. He lives in Kuala Lumpur, Malaysia and had worked at MySQL since 2005, and been a MySQL user since 2000. Before joining MySQL, he worked actively on the Fedora and OpenOffice.org projects. He's well known on the conference track having spoken at many of them over the course of his career.

by colin at April 25, 2016 06:42 AM

April 24, 2016

Valeriy Kravchuk

Building MariaDB 10.1.x and Galera from Source for Multiple Node Cluster Testing Setup

My Facebook followers probably noted that I quit from Percona some time ago and work for MariaDB since March 1, 2016. I changed the company, but neither the job role (I am still a Support Engineer), nor the approach to do my job. I still prefer to test everything I suggest to customers and I usually use software I build from source myself for these tests.

While I try to avoid all kinds of clusters as much as possible for 15 years or so already (it does not matter if it's Oracle RAC, MySQL Cluster or Percona XtraDB Cluster, all of them), it's really hard to avoid Galera clusters while working for MariaDB. One of the reasons for this is that Galera, starting from MariaDB 10.1, can be easily "enabled"/used with any MariaDB 10.1.x instance, any time (at least when we speak about official binaries or those properly built - they are all "Galera ready"). Most of MariaDB customers do use Galera or can try to use it any time, so I have to be ready to test something Galera-specific any moment.

For simple cases I decided to use a setup with several (2 to begin with) cluster nodes on one box. This approach is described in the manual for Percona XtraDB Cluster and was also used by my former colleague Fernando Laudares for his blog post and many real life related tests.

So, I decided to proceed with the mix of ideas from the sources above and MariaDB's KB article on building Galera from source. As I decided to do this on my wife's Fedora 23 workstation, I checked this KB article for some details also. It lists prerequisites (boost-devel check-devel glibc-devel openssl-devel scons) and some of these packages (like scons in one of my cases) could be missing even on a system previosly used for builds for all kinds of MySQL related software. You can find something missing and fix the problem at later stage, but reading and following the manual or KB articles may help to save some time otherwise spent on trial and error.

I've started with making directories in my home directory (/home/openxs) for this Galera related testing setup, like these:
[openxs@fc23 ~]$ mkdir galera
[openxs@fc23 ~]$ cd galera
[openxs@fc23 galera]$ mkdir node1[openxs@fc23 galera]$ mkdir node2
[openxs@fc23 galera]$ mkdir node3
[openxs@fc23 galera]$ ls
node1  node2  node3
I plan to use 3 nodes one day, but for this blog post I'll set up only 2, to have the smallest possible and simplest cluster as a proof of concept.

Then I proceeded with cloning Galera from Codership's GitHub (this is supposed to be the latest and greatest). I changed current directory to my usual git repository and executed git clone https://github.com/codership/galera.git. When this command completed I've got a subdirectory named galera.

In that directory, assuming that all prerequisites are installed, to build current Galera library version it's enough to execute simple script while in galera directory, ./scripts/build.sh. I ended up with the following:
[openxs@fc23 galera]$ ls -l libgalera_smm.so
-rwxrwxr-x. 1 openxs openxs 40204824 Mar 31 12:21 libgalera_smm.so
[openxs@fc23 galera]$ file libgalera_smm.so
libgalera_smm.so: ELF 64-bit LSB shared object, x86-64, version 1 (GNU/Linux), dynamically linked, BuildID[sha1]=11457fa9fd69dabe617708c0dd288b218255a886, not stripped

[openxs@fc23 galera]$ pwd
/home/openxs/git/galera
[openxs@fc23 galera]$ cp libgalera_smm.so ~/galera/
and copied the library to the target directory for my testing setup (that should NOT conflict with whatever software I may have installed later from packages).

Now, time to build MariaDB properly to let it use Galera if needed. I already had recent (at the moment) 10.1.13 in the server subdirectory of my git repository. I've executed the following commands then:

[openxs@fc23 server]$ cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DWITH_SSL=system -DWITH_ZLIB=bundled -DMYSQL_MAINTAINER_MODE=0 -DENABLED_LOCAL_INFILE=1 -DWITH_JEMALLOC=system -DWITH_WSREP=ON -DWITH_INNODB_DISALLOW_WRITES=ON -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/maria10.1
-- Running cmake version 3.4.1
-- MariaDB 10.1.13
...

[openxs@fc23 server]$ time make -j 4...
real    9m28.164s
user    32m43.960s
sys     2m45.637s
This was my usual command line to build MariaDB 10.x with only 2 extra options added: -DWITH_WSREP=ON -DWITH_INNODB_DISALLOW_WRITES=ON.After make completed, I've executed make install && make clean and was ready to use my shiny new Galera-ready MariaDB 10.1.13.

To take into account the directories I am going to use for my cluster nodes and make sure they can start and communicate as separate mysqld instances, I have to create configuration files for them. I've changed working directory to /home/openxs/dbs/mariadb10.1 and started with this configuration file for the first node:

[openxs@fc23 maria10.1]$ cat /home/openxs/galera/mynode1.cnf                    
[mysqld]
datadir=/home/openxs/galera/node1
port=3306
socket=/tmp/mysql-node1.sock
pid-file=/tmp/mysql-node1.pid
log-error=/tmp/mysql-node1.err
binlog_format=ROW
innodb_autoinc_lock_mode=2

wsrep_on=ON # this is important for 10.1!
wsrep_provider=/home/openxs/galera/libgalera_smm.so
wsrep_cluster_name = singlebox
wsrep_node_name = node1
# wsrep_cluster_address=gcomm://
wsrep_cluster_address=gcomm://127.0.0.1:4567,127.0.0.1:5020?pc.wait_prim=no
It's one of the shortest possible. I had to specify unique datadir, error log location, pid file, port and socket for the instance, set binlog format and point out Galera library location, set cluster name and node name. With proper planning I was able to specify wsrep_cluster_address referring to all other nodes properly, but for initial setup of the first node I can have it "empty" as commented out in the above, so that we start as a new cluster node. There is one essential setting for MariaDB 10.1.x that is not needed for "cluster-specific" instances like Percona XtraDB Cluster or older 10.0.x Galera packages from MariaDB (where it's ON by default). This is wsrep_on=ON. Without it MariaDB works as normal, non-cluster instance and ignores anything cluster-related. You can save a lot of time in case of upgrade to 10.1.x if you put it in your configuration file explicitly right now, no matter what the version is used.

Then I copied and modified configuration file for the second node:
[openxs@fc23 maria10.1]$ cp /home/openxs/galera/mynode1.cnf /home/openxs/galera/mynode2.cnf
[openxs@fc23 maria10.1]$ vi /home/openxs/galera/mynode2.cnf                     

[openxs@fc23 maria10.1]$ cat /home/openxs/galera/mynode2.cnf                   
[mysqld]
datadir=/home/openxs/galera/node2
port=3307
socket=/tmp/mysql-node2.sock
pid-file=/tmp/mysql-node2.pid
log-error=/tmp/mysql-node2.err
binlog_format=ROW
innodb_autoinc_lock_mode=2

wsrep_on=ON # this is important for 10.1!wsrep_provider=/home/openxs/galera/libgalera_smm.so
wsrep_cluster_name = singlebox
wsrep_node_name = node2
wsrep_cluster_address=gcomm://127.0.0.1:4567,127.0.0.1:5020

?pc.wait_prim=nowsrep_provider_options = "base_port=5020;"
Note that while Galera node uses 4 ports, I specified only 2 unique ones explicitly, port for MySQL clients and base port for all Galera-related communication like IST and SST, with base_port setting. Note also how I referred to all cluster nodes with wsrep_cluster_address - this same value can be used for the configuration file of the first node actually. We can just start it as the first node of a new cluster (see below).

Now we have configuration files for 2 nodes ready (we can always add node3 later in the same way). But before starting new cluster we have to install system databases. For node1 it was performed in the following way:
[openxs@fc23 maria10.1]$ scripts/mysql_install_db --defaults-file=/home/openxs/galera/mynode1.cnf
Installing MariaDB/MySQL system tables in '/home/openxs/galera/node1' ...
2016-03-31 12:51:34 139766046820480 [Note] ./bin/mysqld (mysqld 10.1.13-MariaDB) starting as process 28297 ...
...

[openxs@fc23 maria10.1]$ ls -l /home/openxs/galera/node1
-rw-rw----. 1 openxs openxs    16384 Mar 31 12:51 aria_log.00000001
-rw-rw----. 1 openxs openxs       52 Mar 31 12:51 aria_log_control
-rw-rw----. 1 openxs openxs 12582912 Mar 31 12:51 ibdata1
-rw-rw----. 1 openxs openxs 50331648 Mar 31 12:51 ib_logfile0
-rw-rw----. 1 openxs openxs 50331648 Mar 31 12:51 ib_logfile1
drwx------. 2 openxs openxs     4096 Mar 31 12:51 mysql
drwx------. 2 openxs openxs     4096 Mar 31 12:51 performance_schema
drwx------. 2 openxs openxs     4096 Mar 31 12:51 test
Then I started node1 as a new cluster:
[openxs@fc23 maria10.1]$ bin/mysqld_safe --defaults-file=/home/openxs/galera/mynode1.cnf --wsrep-new-cluster &
and created a table, t1, with some data in it. After that I repeated installation of system tables etc for node2, just referencing proper configuration file, and started node2 that was supposed to join the cluster:
openxs@fc23 maria10.1]$ bin/mysqld_safe --defaults-file=/home/openxs/galera/mynode2.cnf &
Let's check if we do have both instances running and communicating in Galera cluster:
[openxs@fc23 maria10.1]$ tail /tmp/mysql-node2.err                             
2016-03-31 13:40:29 139627414767744 [Note] WSREP: Signalling provider to continue.
2016-03-31 13:40:29 139627414767744 [Note] WSREP: SST received: c91d17b6-f72b-11e5-95de-96e95167f593:0
2016-03-31 13:40:29 139627117668096 [Note] WSREP: 1.0 (node2): State transfer from 0.0 (node1) complete.
2016-03-31 13:40:29 139627117668096 [Note] WSREP: Shifting JOINER -> JOINED (TO: 0)
2016-03-31 13:40:29 139627117668096 [Note] WSREP: Member 1.0 (node2) synced with group.
2016-03-31 13:40:29 139627117668096 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
2016-03-31 13:40:29 139627414452992 [Note] WSREP: Synchronized with group, ready for connections
2016-03-31 13:40:29 139627414452992 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2016-03-31 13:40:29 139627414767744 [Note] /home/openxs/dbs/maria10.1/bin/mysqld: ready for connections.
Version: '10.1.13-MariaDB'  socket: '/tmp/mysql-node2.sock'  port: 3307  Source distribution

[openxs@fc23 maria10.1]$ tail /tmp/mysql-node1.err
2016-03-31 13:40:27 140071390934784 [Note] WSREP: Provider resumed.
2016-03-31 13:40:27 140072133322496 [Note] WSREP: 0.0 (node1): State transfer to 1.0 (node2) complete.
2016-03-31 13:40:27 140072133322496 [Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 0)
2016-03-31 13:40:27 140072133322496 [Note] WSREP: Member 0.0 (node1) synced with group.
2016-03-31 13:40:27 140072133322496 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
2016-03-31 13:40:27 140072429247232 [Note] WSREP: Synchronized with group, ready for connections
2016-03-31 13:40:27 140072429247232 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2016-03-31 13:40:27 140072141715200 [Note] WSREP: (c91c99ec, 'tcp://0.0.0.0:4567') turning message relay requesting off
2016-03-31 13:40:29 140072133322496 [Note] WSREP: 1.0 (node2): State transfer from 0.0 (node1) complete.
2016-03-31 13:40:29 140072133322496 [Note] WSREP: Member 1.0 (node2) synced with group.
Familiar messages (unfortunately...) that prove we had a second node joined and performed state transfer from the first one. Now it's time to connect and test how cluster works. This is what I had after node1 started and table with some data created there, but before node2 started:
[openxs@fc23 maria10.1]$ bin/mysql -uroot --socket=/tmp/mysql-node1.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.13-MariaDB Source distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like 'wsrep_cluster%';
+-----------------------+-------------------------------------------------------+
| Variable_name         | Value                                                 |
+-----------------------+-------------------------------------------------------+
| wsrep_cluster_address | gcomm://127.0.0.1:4567,127.0.0.1:5020?pc.wait_prim=no |
| wsrep_cluster_name    | singlebox                                             |
+-----------------------+-------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> show status like 'wsrep_cluster%';
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id    | 1                                    |
| wsrep_cluster_size       | 1                                    |
| wsrep_cluster_state_uuid | c91d17b6-f72b-11e5-95de-96e95167f593 |
| wsrep_cluster_status     | Primary                              |
+--------------------------+--------------------------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> select * from t1;
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.00 sec)
Then, when node2 joined the cluster, I checked that the data we've added on node1 are there:

[openxs@fc23 maria10.1]$ bin/mysql -uroot --socket=/tmp/mysql-node2.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.13-MariaDB Source distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> select * from t1;
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.00 sec)

MariaDB [test]> show status like 'wsrep_cluster%'; +--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id    | 2                                    |
| wsrep_cluster_size       | 2                                    |
| wsrep_cluster_state_uuid | c91d17b6-f72b-11e5-95de-96e95167f593 |
| wsrep_cluster_status     | Primary                              |
+--------------------------+--------------------------------------+
4 rows in set (0.01 sec)
So, the first basic test with the Galera cluster of 2 nodes (both running on the same box) built from current source of Galera and MariaDB 10.1.x on Fedora 23 is completed successfully. I plan to play with it more in the future, use current xtrabackup built from source for SST and so on, and create blog posts about these steps and any interesting tests in this setup. Stay tuned.

From the dates above you can conclude that it took me 3 weeks to publish this post. That's because I was busy with the company meeting in Berlin and some usual Support work, and was not sure is it really a good idea for me to write any post with "Galera" or "MariaDB" words used in it even once...



by Valeriy Kravchuk (noreply@blogger.com) at April 24, 2016 06:33 PM

April 22, 2016

Shlomi Noach

MySQL Community Awards 2016: the Winners

The MySQL Community Awards initiative is an effort to acknowledge and thank individuals and corporates for their contributions to the MySQL ecosystem. It is a from-the-community, by-the-community and for-the-community effort. The committee is composed of an independent group of community members of different orientation and opinion, themselves past winners or known contributors to the community.

The 2016 community awards were presented on April 21st, 2016, during the keynotes at the Percona Live conference. The winners are:

MySQL Community Awards: Community Contributor of the year 2016

  • Bill Karwin
    Bill has been working with the community for years, helping them understand SQL. Bill is the author of the great book "SQL Antipatterns". He has given a large amount of help on sites such as StackOverflow, Quora, and of course many conference talks. Bill has provided a huge amount of help to the community directly.
  • Domas Mituzas
    Domas Mituzas started in the MySQL ecosystem as a MySQL Support Engineer at MySQL AB. Since he had some spare time, he did a lot of work to scale MySQL at Wikipedia. He is now a small data engineer at Facebook, mostly working with user-facing data systems. He continues to write very interesting blog posts and bug reports. Domas is responsible for giving us MyDumper, PoorMansProfiler, and the infamous Query Cache tuner!
  • Yoshinori Matsunobu
    Yoshinori Matsunobu is currently leading the MyRocks effort to get the RocksDB storage engine for MySQL into production at Facebook. Previously (amongst his other accomplishments) he created HandlerSocket, and implemented MHA to support failover automation for MySQL – both of which have been used at many companies. He is a frequent speaker at community events, and his tutorials and slide decks do a lot to increase expertise in the community. He is a frequent bug reporter with a focus on replication (RBR, semi-sync).

MySQL Community Awards: Application of the year 2016

  • MaxScale
    MariaDB MaxScale is an Open Source dynamic routing gateway. It is widely used as a database load balancer for Galera Cluster deployments, for standard replication setups, and as a replication relay. It has a modular architecture which includes plugins for read-write splitting and query logging. It serves a variety of tasks, from load balancing to database firewall filtering to binlog server and is widely used in production in large topologies.

MySQL Community Awards: Corporate Contributor of the year 2016

  • Booking.com
    Booking.com has been a massive contributor to the MySQL ecosystem, sending many of their excellent DBAs to various conferences to talk. They have provided an innovative test bed for testing out, and giving a wealth of invaluable feedback about new releases (across a wide variety of MySQL and related software projects). Booking.com contributes to Open Source foundations, projects and communities by donating, sponsoring, making code contributions and hosting events. The quality of MySQL is undoubtedly much better today because of their help and input.

Congrats to all winners!

Committee members

  • Baron Schwartz
  • Colin Charles
  • Daniël van Eeden
  • Davi Arnaut
  • Frederic Descamps
  • Geoffrey Anderson
  • Giuseppe Maxia
  • Justin Swanhart
  • Mark Leith
  • Morgan Tocker
  • Philip Stoev
  • Ronald Bradford
  • Santiago Lertora

Co-secretaries:

  • Jeremy Cole
  • Shlomi Noach

Special thanks

Thank you to this year's anonymous sponsor for donating the goblets!

Thank you to Colin Charles for acquiring and transporting the goblets!

 

by shlomi at April 22, 2016 05:03 PM

MariaDB Foundation

MariaDB 5.5.49 and updated connectors now available

The MariaDB project is pleased to announce the immediate availability of MariaDB 5.5.49, MariaDB Connector/J 1.4.2, and MariaDB Connector/ODBC 2.0.10. See the release notes and changelogs for details on these releases. Download MariaDB 5.5.49 Release Notes Changelog What is MariaDB 5.5? MariaDB APT and YUM Repository Configuration Generator Download MariaDB Connector/J 1.4.2 Release Notes Changelog […]

The post MariaDB 5.5.49 and updated connectors now available appeared first on MariaDB.org.

by Daniel Bartholomew at April 22, 2016 02:11 PM

Peter Zaitsev

Percona Live 2016: Closing Comments and Prize Giveaway

Percona Live 2016

Percona Live 2016Well, that is it for Percona Live 2016! This year was bigger and better than last year and included more sponsors, speakers, technologies, and talks than ever before. Once again we’d like to thank everybody who participated, and the entire open source community in general: without your dedication and spirit, none of this would be possible.

At the prize ceremony, many of the exhibitors gave away a prize to the people who filled out and turned in a completed conference passport, as well as some gifts for those who completed surveys for the talks. Prizes ranged from $50 gift certificates, signed copies of Bill Nye’s book Unstoppable, an Amazon Echo, GoPro cameras, an Oculus Rift, a Playstation, and more. All the winners left happy (except maybe the guy who got the Mr. Spock socks, although I would have been pleased).

Thanks for coming, and we’ll see you in Amsterdam (and next year)!

Below is a video of the ceremony in full (it’s about 15 minutes long).

 

by Dave Avery at April 22, 2016 12:04 AM

April 21, 2016

Peter Zaitsev

Percona Live 2016: MongoDB Security

Percona Live 2016

Percona Live 2016It’s almost time for the closing remarks and passport prize give away at Percona Live 2016, but still the talks keep coming. I was able to quickly pop into a lecture on MongoDB Security with Stephane Combaudon, MySQL Consultant at Slice Technologies.

Stephane went over some of the reported security issues with MongoDB and explained that MongoDB has good security features. Data security is a concern for most people, and recent reports in the news show that significant amounts of user details stored in MongoDB are easily accessible. This doesn’t mean that MongoDB can’t secure your data. As Stephane explained, MongoDB can be correctly secured – but some features are not enabled by default. In this session, we learned the main items that need to be checked to get a robust MongoDB deployment. Those items include:

  • Network security: Stopping people from connecting to your MongoDB instances
  • Operating system security: stopping people from connecting to MongoDB and taking control of your servers
  • User security: how to make sure that users can only interact with specific portions of the data

I had a chance to quickly speak with Stephane after his talk:

See the rest of the Percona Live 2016 schedule here.

by Dave Avery at April 21, 2016 10:18 PM

Percona Live 2016: Sometimes “Less is More” for dash-boarding and graphing

percona live 2016We’re starting to wind down here at Percona Live 2016, but there are still talks to see even this late in the conference! One of the talks I saw was Sometimes “Less is More” for dash-boarding and graphing with Michael Blue, Senior DBA at Big Fish Games.

In this talk, Michael discussed how monitoring specific metrics can be more help than all the metrics. In a monitor/measure everything environment, to get the big picture sometimes “Less is More.” There are good monitoring tools available for DBAs, with tons of metrics to measure, but at a larger scale it seems impossible to get that big picture view of your environment without spending much of your time going over all the metrics your graphing, sifting through emails of false positives alerts, and reading tons of your cron job outputs. In the talk, Michael explained the approaches he took at Big Fish to create better dashboards for all audiences without visual overload, which helped the DBAs find potential issues that were not caught via conventional monitoring. This session included:

This session included:

  • The basics of visualization
  • Picking metrics to measure at scale
  • How they leverage custom dashboards, Graphite, and the MySQL Enterprise Monitor

I had a chance  to speak with Michael after his talk:

 Check out more of the Percona Live 2016 schedule here.

by Dave Avery at April 21, 2016 09:12 PM

Percona Live 2016: Percona Live Game Night!

percona live 2016

Wednesday night at Percona Live 2016 was reserved for fun, fun, fun! Once again, the Percona Live Game Night proved to be a popular and amazing event. There were more games this year than last, as well as food, drinks, and lots of friendly competition!

This year, besides the ever-crowd-pleasing Meltdown Challenge, there were Segway Races, pool, foosball, shuffleboard, Wii Boxing, Pac-Man Attack, a shootout gallery, darts, as well as virtual reality stations and a death-defying trampoline.

You can see Percona’s CEO Peter Zaitsev demonstrating how you use it, pro-level:

Below are some more photos of this outstanding night:

percona live 2016

Coed boxing: guys, you need to improve your skills!

.

percona live 2016

Some very intense foosball action!

.

percona live 2016

This Pac-Man Attack reminds me of 1983!

.

percona live 2016

Keep an eye on your wallets, gentlemen, I detect a hustle.

.

percona live 2016

More trampoline.

.

percona live 2016

For those who like less effort with their trampoline, virtual reality.

.

percona live 2016

A little social lubrication.

.

percona live 2016

Happy attendees (must have stopped at the previous picture).

.

percona live 2016

Hmm, that guy looks a bit confused. Must be too much tech talk for one day!

.

percona live 2016

I’d stay away from this table. Just saying.

.

percona live 2016

More happy.

.

Thanks to everybody who came out and participated in an awesome night! We’ll see you all next year!

 

 

by Dave Avery at April 21, 2016 08:09 PM

Percona Live 2016: MySQL Community Awards Ceremony

Percona Live 2016

Percona Live 2016We wrapped up Thursday morning’s Percona Live 2016 keynotes (April 21)  with The MySQL Community Award Ceremony. The MySQL Community Awards is a community-based initiative, with the goal of publicly recognizing contributors to the MySQL ecosystem. The idea is to publicly recognize contributors to the MySQL ecosystem. The entire process of discussing, voting and awarding is controlled by an independent group of community members, typically based on past winners or their representatives, as well as known contributors. It is a self-appointed, self-declared, self-making-up-the-rules-as-it-goes committee. It is also very aware of the importance of the community; a no-nonsense, non-political, adhering to tradition, self-criticizing committee.

The categories can be found here.

We celebrated the members of the open source community, and some big open source contributors from the past year! The winners this year were:

Community Contributors of the Year:

  • Bill Karwin, Senior Database Architect and Software Engineer at SchoolMessenger
  • Domas Mituzas, Small Data Engineer at Facebook
  • Yoshinori Matsunobu, Data Engineer at Facebook

Application of the Year:

Corporate Contributor of the Year:

 

Congratulation to all the winners! You can watch the whole ceremony below (it’s about 15 minutes long).

by Dave Avery at April 21, 2016 07:23 PM

Percona Live 2016: Day Three Keynote Talks

We’re heading into the final day here at Percona Live 2016! People are looking a little tired, but still excited for some excellent talks today. Once again the day started off with two great keynote lectures. Read to the end for an important announcement!

Percona Live 2016Peter Zaitsev, CEO of Percona
Winning with Open Source Databases

Peter discussed what makes the open source community so vibrant and powerful, and why it is essential to preserve that spirit. Data is critical to the success of your business. You rely on your database and its supporting systems to power the applications that drive your business. These systems must be reliable, scalable, efficient – and increasingly, open source. With the complexity of today’s web applications and the databases, systems and hardware that support them, organizations must use the right open source tools and technology for each job – without getting locked into a proprietary solution. With Percona, customers are assured a choice in technology options that are completely open source (and include enterprise features). We help our customers find the right technology for their specific needs, rather than sell a one-size-fits-all product. Percona is a true open source partner that helps you optimize your database performance to better run your business.

Percona Live 2016Patrick McFadin, Chief Evangelist at DataStax

Take back the power in your cloud applications with Apache Cassandra

Patrick discussed how cloud applications can help you to develop the applications you need in your business, but also outline why the cloud isn’t a panacea for every business issue. Database engineers have had to support the crazy dreams of application developers since the beginning of the internet. Patrick says it’s time to take back the power! He believes that Apache Cassandra is the tool that can help you eliminate downtime or span your data around the world with ease. Deploying to the cloud isn’t always easy, but Cassandra might be able to give your application developers the best chance they can get and sleep easy at night.

 

Post MongoDB World NY ShowPost MongoDB World New York Conference, June 30th, Hilton Mid-Town Manhattan

Peter also made an important announcement: Percona and ObjectRocket/Rackspace will be sponsoring a free post-MongoDB World Community Event! The event will take place on Thursday, June 30th at the Hilton Mid-Town Manhattan.

All are welcome.

Don’t miss out on this amazing opportunity to share ideas and get insights after MongoDB World.

Check out the rest of today’s Percona Live 2016 schedule here.

by Dave Avery at April 21, 2016 06:52 PM

Percona Live 2016: What’s New in MySQL

percona live 2016

The second day of Percona Live 2016 is wrapping up, but I still wanted to get to one last lecture. Fortunately, there was a great one available: What’s New in MySQL with Geir Høydalsvik, Senior Software Development Director at Oracle, and Simon Mudd, DBA at booking.com.

MySQL 5.7 is an extremely exciting new version of the world’s most popular open source database that is 2x faster than MySQL 5.6, while also improving usability, manageability, and security. MySQL 5.7, now in GA, is the result of almost three years of development and brings improvements to all areas of the product. Some key enhancements include:

  • MySQL improved InnoDB scalability and temporary table performance, enabling faster online and bulk load operations, and more.
  • They’ve added native JSON support.
  • For increased availability and performance, they’ve included multi-source replication, multi-threaded slave enhancements, online GTIDs, and enhanced semi-sync replication.
  • They’ve added numerous new monitoring capabilities so the Performance Schema for better insights, reduced the footprint and overhead, and significantly improved ease of use with the new SYS Schema.
  • They are fulfilling “secure by default” requirements, and many new MySQL 5.7 features will help users keep their database secure.
  • They have rewritten large parts of the parser, optimizer, and cost model. This has improved maintainability, extendability, and performance.
  • GIS is completely new in MySQL 5.7, as is InnoDB spatial indexes, the use of Boost.Geometry, and increased completeness and standard compliance.

 I had a chance to talk with Geir and Simon after their lecture:

Check out the Percona Live 2016 schedule for more talks!

by Dave Avery at April 21, 2016 05:14 AM

Percona Live 2016: MySQL and Docker Strategies

Percona Live 2016

The afternoon sessions at Percona Live 2016 are just as intriguing as the morning sessions. I’m trying to hit as many as I can, especially talks on interesting and upcoming technologies that are improving deployments and manageability. One of the talks I saw in this vein was MySQL and Docker Strategies, given by Patrick Galbraith, Senior Systems Engineer at Hewlett Packard, and Giuseppe Maxia, Quality Assurance Director at VMware.

Docker is a great new project for managing containers. Containers provide operating system resource isolation and allocation benefits as virtual machines, yet are more lightweight and allow you a lot of flexibility in how you can design your application and database architecture. This talk covered many of the useful things one can do with Docker and MySQL, such as:

  • Build ready-to-launch containers that are “batteries included” MySQL servers
  • Backup and restore MySQL databases using a container strategy that allows both containers and host systems access to the same data
  • Network containers across hosts
  • Implement container clustering solutions such as CoreOS, Kubernetes, and Mesos and how each of these solutions solves a similar requirement
  • Automate containerized MySQL using Ansible

Patrick and Giuseppe also provided summary details and a demonstration of their recent contribution to Kubernetes (a Galera cluster application), which showed how one can have a quickly deployable MySQL synchronous replication cluster in minutes and use it with other Kubernetes applications.

I had a chance to speak with Giuseppe after the talk:

 

by Dave Avery at April 21, 2016 01:19 AM

April 20, 2016

Peter Zaitsev

Percona Live 2016: Indexing Strategies and Tips for MongoDB

Percona Live 2016

The second day at Percona Live 2016 is moving along, and there is just too much to see to get to it all. Percona acquired Tokutek last year and with it a MongoDB platform. I was interested in learning more about MongoDB this year at Percona Live 2016, and I’m not disappointed! There is at least one MongoDB lecture ever hour, along with a MongoDB 101 track led by Percona experts who take attendees through the fundamentals of MongoDB tools and techniques.

One of the MongoDB lectures I attended was lead by Kimberly Wilkins, Principal Engineer/Databases at ObjectRocket by Rackspace. Her lecture was titled Indexing Strategies and Tips for MongoDB. In this presentation, Kimberly covered general indexing, options, and strategies. With the new indexing improvements and changes in MongoDB 3.2, there are more indexing options than ever before – including automated index builds and partial indexes. After this talk, I was able to walk away with real world examples of index use cases for running at scale and for high performance that have been tested with multiple high-level clients in small to very large MongoDB databases.

After the talk, I was able to chat with Kimberly for a minute:

See the rest of the Percona Live 2016 schedule here.

by Dave Avery at April 20, 2016 10:57 PM

Percona Live 2016: Operational Buddhism — Building Reliable Services From Unreliable Components

percona live 2016

It’s another packed day here at Percona Live 2016, with many, many database topics under discussion. Some technical, some strategical, and some operational. One such talk I sat in on was given by Ernie Souhrada, Database Engineer and Bit Wrangler at Pinterest. His talk was called Operational Buddhism: Building Reliable Services From Unreliable Components.

In it he discussed how the rise of utility computing has revolutionized much about the way organizations think about infrastructure and back-end serving systems, compared to the “olden days” of physical data centers. But success is still driven by meeting your SLAs. If services are up and sufficiently performant, you win. If not, you lose. In the traditional data center environment, fighting the uptime battle was typically driven by a philosophy Ernie calls “Operational Materialism.” The primary goal of OM is preventing failures at the infrastructure layer, and mechanisms for making this happen are plentiful and well-understood, many of which boil down to simply spending enough money to have at least N+1 of anything that might fail.

Ernie contends that in the cloud, Operational Materialism cannot succeed. Although the typical cloud provider tends to be holistically reliable, there are no guarantees that any individual virtual instance will not randomly or intermittently drop off the network or be terminated outright. Since we still need to keep our services up and running and meet our SLAs, we need a different mindset that accounts for the fundamentally opaque and ephemeral nature of the public cloud.

Ernie presented an alternative to OM, a worldview that he referred to as “Operational Buddhism.” Like traditional Buddhism, OB has Four Noble Truths:

  1. Cloud-based servers can fail at any time for any reason
  2. Trying to prevent this server failure is an endless source of suffering for DBAs and SREs alike
  3. Accepting the impermanence of individual servers, we can focus on designing systems that are failure-resilient, rather than failure-resistant
  4. We can escape the cycle of suffering and create a better experience for our customers, users, and colleagues.

To illustrate these concepts with concrete examples, he discussed how configuration management, automation, and service discovery help Pinterest to practice Operational Buddhism for both stateful (MySQL, HBase) and stateless (web) services. He also talked about some of the roads not taken, including the debate over Infrastructure-as-a-Service (IaaS) vs. Platform-as-a-Service (PaaS).

I was able to have a quick chat with Ernie after the talk:

 

See the rest of the Percona Live 2016 schedule here.

by Dave Avery at April 20, 2016 08:55 PM

Percona Live 2016: Day Two Keynote Talks

Percona Live 2016

We’ve started the second full day at Percona Live 2016, and again we had some pretty impressive and fascinating keynote speakers. This morning featured four keynote talks from some pretty heavy hitters in our industry:

Percona Live 2016Anurag Gupta, Vice President of Big Data Services, Amazon Web Services
AWS Big Data Services: Reimagining Big Data Processing in the Cloud

In this talk, we found out about the data challenges and trends that drove AWS to reimagine data processing and bet the future on new Big Data services that are innovating data infrastructure (from NoSQL databases to data warehouses). Anurag (and AWS) believe that one size doesn’t fit all, and multiple services are often necessary to address different challenges of Big Data. He provides an under-the-hood view of some of the most popular Big Data services at AWS, including:
  • Amazon DynamoDB – a NoSQL service
  • Amazon Redshift – a petabyte-scale data warehouse service
  • Amazon EMR – an elastic map-reduce service
  • Amazon Aurora – a massively scalable relational database service

He also discussed best practices in understanding customer desires and behavior from Big Data and how to achieve 1:1 customer experience at scale.

Data in the Cloud Keynote Panel: Cloudy with a chance of running out of disk space? Or Sunny times ahead?

Moderated by Matt Yonkovit, Percona’s Vice President of Professional Services, this panel discussion focused on the fact that larger and larger datasets are moving to the cloud, creating new challenges and opportunities in handling such workloads. As such, new technologies, revamped products, and a never ending stream of idea’s follow in the wake of this advance to keep pace. Even as these solutions attempt to improve the performance and manageability of cloud-based data, the question is are they enough? The panel brought our several areas that need to be addressed to keep ahead of the “data crisis,” including:

  • Compression
  • Visibility
  • Optimizing Storage
  • The trifecta of storage/analytics/processing
  • Security and privacy

Percona Live 2016Tomas Ulin, Vice President for the MySQL Engineering team, Oracle
MySQL: Combining SQL and NoSQL

In this talk, Tomas Ulin discussed how, now that MySQL 5.7 is GA, it’s delivered major improvements in performance, scalability and manageability. He explored how MySQL can deliver on the promises of NoSQL, while keeping all the proven benefits of SQL. This lecture allowed the audience to better understand the MySQL development priorities and what the future holds for the world’s most popular open source database.

Percona Live 2016Mark Callaghan, Software Engineer, Database Infrastructure, Facebook
MyRocks, MongoRocks and RocksDB

In this lecture, we learned about RocksDB, MyRocks, and MongoRocks. RocksDB is an embedded database engine. MyRocks and MongoRocks are RocksDB storage engines for MySQL and MongoDB. All of this is open-source. Facebook started these projects to get better performance, efficiency and compression with fast storage devices like flash-based SSD but they also work great with disks. Mark explained how and why MyRocks provides better performance, efficiency and compression using real and synthetic workloads. In one case, they got 2X better compression compared to compressed InnoDB for a production workload. Mark (and Facebook) expect MyRocks to become the web-scale storage engine for MySQL.

Those were the morning lectures today! And there is more to come! Check out our schedule here.

by Dave Avery at April 20, 2016 08:23 PM

Serge Frezefond

MariaDB AWS Key Management Service (KMS) Encryption Plugin

MariaDB 10.1 introduced Data at Rest Encryption. By default we provide a file_key_management plugin. This is a basic plugin storing keys in a file that can be itself encrypted. This file can come from a usb stick removed once keys have been brought into memory. But this remains a basic solution not suitable for security [...]

by Serge at April 20, 2016 07:08 PM

Jean-Jerome Schmidt

MySQL Replication failover: MaxScale vs MHA (part 4)

In the earlier blogs in this series, we concluded that MaxScale with MariaDB Replication Manager still has some way to go as a failover solution.

The failover mechanism relied on MariaDB GTID, needed a wrapper script around the decoupled replication manager and had no protection against flapping. Since then, MaxScale and MariaDB Replication Manager (MRM) have received a couple of updates to improve them. For MaxScale, the greatest improvement must be the availability of the community edition repository.

Integration

Previously, to configure MaxScale to execute the MariaDB Replication Manager upon master failure, one would add a wrapper shell script to translate MaxScale’s parameters to the command line options of MRM. This has now been improved, there is no need for the wrapper script anymore. That also means that there is now less chance of parameter mismatch.

The new configuration syntax for MaxScale is now:

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=svr_10101811,svr_10101812,svr_10101813
user=admin
passwd=B4F3CB4FD8132F78DC2994A3C2AC7EC0
monitor_interval=1000
script=script=/usr/local/bin/replication-manager --user root:admin --rpluser repluser:replpass --hosts $INITIATOR,$NODELIST --failover=force --interactive=false --logfile=/var/log/failover.log
events=master_down

Also, to know what happened during the failover, you can read this from the failover log as defined above.

Preferred master

In the second blog post, we mentioned you can’t set candidate masters like you are used to with MHA. Actually, as the author indicated in the comments, this is possible with MRM: by defining the non-candidate masters as servers to be ignored by MRM during slave promotion.

The syntax in the MaxScale configuration would be:

script=script=/usr/local/bin/replication-manager --user root:admin --rpluser repluser:replpass --hosts $INITIATOR,$NODELIST --ignore-servers=’172.16.2.123:3306,172.16.2.126’ --failover=force --interactive=false --logfile=/var/log/failover.log

Flapping

We also concluded the combination of MRM and MaxScale lacks the protection against flapping back and forth between nodes. This is mostly due to the fact that MRM and MaxScale are decoupled, they implement their own topology discovery. After MRM has performed its tasks, it exits. This could lead to an infinite loop where a slave gets promoted, fails due to the increase in load while the old-master becomes  healthy again and is re-promoted.

MRM actually has protection against flapping when used in the so called monitoring mode, where MRM runs as an application. The monitoring mode is an interactive mode where a DBA can either invoke a failover or have this done automatically. With the failover-limit parameter, you can limit the number of failovers before MRM will back off and stop promoting. Naturally this only works because MRM is keeping state in the interactive mode.

It would actually make sense to also add this functionality to the non-interactive mode and somewhere keep the state after the last failover(s). Then MRM would be able to stop performing the failover multiple times within a short timeframe.

Monitoring mode

MRM also features a so called “monitoring” mode where it constantly monitors the topology and could failover automatically if there is a master failure. With MaxScale we always set the mode to “force” to have MRM perform the failover without the need of a confirmation.  The monitoring mode actually invokes interactive mode, so unless you run it in screen, you can’t have MRM run in the background and perform the failover automatically for you.

Conclusion

MariaDB Replication Manager has improved over the past few weeks. With a few improvements, it has become more useful. Seeing the number of issues added (and resolved) indicate people are starting to test/use it. If MariaDB would provide binaries for MRM, the tool could receive wider adoption among the MariaDB users.

by Severalnines at April 20, 2016 05:20 PM

Press Release: Severalnines expands the reach of European scientific discovery

Stockholm, Sweden and anywhere else in the world - 20 April 2016 - Severalnines, the provider of database infrastructure management software, today announced its latest customer, the National Center for Scientific Research (CNRS), which is a subsidiary of the French Ministry of Higher Education and Research.

The CNRS has over 1,100 research units and is home to some of the largest scientific research facilities in the world. It partners with other global institutions and employs over 33,000 people. Working in partnership with universities, laboratories and dedicated scientists, CNRS has delivered advanced research in areas such as obesity, malaria and organic matter in space.

Having an international outreach means they have a dedicated department to handle the information infrastructure of the organisation called the Directorate of Information Systems (CNRS-DSI). Thousands of gigabytes (GB) of administrative data are processed by CNRS-DSI internal systems every week, but with a tight budget CNRS needed software, which was both cost effective whilst delivering a high quality, robust service.

To manage the high volume of data, CNRS deployed over 100 open source LAMP applications. The growth of the institution led to unprecedented usage of CNRS data from tens of thousands of users across the world accessing or transporting information. There was a need to increase the scalability, availability and robustness of the systems.

After launching a study to find a suitable database solution and realising traditional MySQL clusters were too complicated without a database administrator (DBA), they found Severalnines’ ClusterControl in conjunction with MariaDB Galera Cluster, MySQL’s “little sister fork”. ClusterControl offered a comprehensive solution, which is easy to access for all CNRS-DSI technical staff. The solution integrated well across the technological environment and was able to detect anomalies in the system.

Since Severalnines was deployed, the CNRS-DSI team runs a development and a production MariaDB Galera cluster thanks to ClusterControl with future plans to have all of its LAMP applications running in this environment. In fact, CNRS-DSI just recently extended all of its ClusterControl subscriptions.

Furthermore, beside these classical LAMP applications, CNRS-DSI is deploying a cloud storage solution for thousands of its users. For obvious performance and availability reasons, MariaDB Galera has also been chosen as the database component in place of the classical standalone MySQL; and Severalnines ClusterControl has been naturally chosen as the management solution for this critical service as well.

Olivier Lenormand, Technical Manager of CNRS-DSI, stated: “Technology is the backbone of scientific discovery which ultimately leads to human advancement. Data management is very important at CNRS because we want to continue our groundbreaking research and protect our data. Severalnines has helped us keep costs down whilst increasing the potential of our open source systems. We’ve found a database platform, which can both manage and use our LAMP applications, as well as cloud services. Severalnines is helping us enhance the capabilities at CNRS-DSI for the benefit of the global scientific community.”

Vinay Joosery, Severalnines CEO, said: “Data management in a large organisation like CNRS can present technical as well as economical challenges, but it should not get into the way of scientific research. We are really excited we can help CNRS use the best of open source software to increase collaboration in new, potentially life-saving research projects.

About Severalnines

Severalnines provides automation and management software for database clusters. We help companies deploy their databases in any environment, and manage all operational aspects to achieve high-scale availability.

Severalnines' products are used by developers and administrators of all skills levels to provide the full 'deploy, manage, monitor, scale' database cycle, thus freeing them from the complexity and learning curves that are typically associated with highly available database clusters. The company has enabled over 8,000 deployments to date via its ClusterControl solution. Currently counting BT, Orange, Cisco, CNRS, Technicolour, AVG, Ping Identity and Paytrail as customers. Severalnines is a private company headquartered in Stockholm, Sweden with offices in Singapore and Tokyo, Japan. To see who is using Severalnines today visit, http://www.severalnines.com/customers

by Severalnines at April 20, 2016 11:25 AM

MariaDB AB

MariaDB Roadshow 2016 is starting

juergengiesel

Team MariaDB is touring across several European regions again. The theme for 2016 is "Data Management for Mission Critical Applications". Beside valuable presentations around database security, Big Data analysis and better scalability and high availability our technical experts will help you understand how to take advantage of the wide range of new features and enhancements available now in MariaDB 10.1, MariaDB MaxScale 1.4 and other MariaDB solutions. They will share tips & tricks to help you get the most out of your database. You will also discover what’s coming up next in MariaDB and the MariaDB ecosystem.

MariaDB Roadshow Map 2016

We will start at Friday in Helsinki where Michael "Monty" Widenius (MySQL and MariaDB creator) is among the listed speakers. The week afterwards we will continue with Nürnberg, Germany. More dates and locations are listed below.

Join MariaDB at this free event to learn how to:

  • Ensure high availability and performance of your database
  • Protect your data
  • Leverage open source to benefit from community-drive innovation that addresses real-world challenges

Depending on the location, we are partnering with different companies like SUSE, Red Hat, Microsoft, Adfinis SyGroup, and Quru.

Don’t miss this opportunity to discover how the leading open source database can:

  • Quickly deliver new applications and functionality
  • Protect against security and regulatory risk
  • Meet business uptime and performance needs

After the presentations you will have a chance to network and of course ask us any additional questions you may have.

For agendas and other details, please visit the dedicated event registration pages:

We will add a few more locations and dates within the next few weeks - so please check the events page on a regular base if your location is currently missing!

About the Author

juergengiesel's picture

Jürgen Giesel is EMEA Marketing Manager at MariaDB Corporation Ab. He started to work for MySQL GmbH as DACH Marketing Manager in 2002. From 2008 to 2010 he was MySQL Marketing Consultant at Sun Microsystem and from 2010 until early 2014 EMEA Marketing Manager for MySQL, Oracle Linux and Virtualization at Oracle.

by juergengiesel at April 20, 2016 09:00 AM

Erkan Yanar

(MySQL) Performance Monitoring with Prometheus [UPDATE]

In my last I was looking for a way to do performance monitoring and I stumbled upon Prometheus. Prometheus is much more than monitoring a single node service. Anyway let’s get the idea of gathering metrics using MySQL as example.

This how a simple configuration of Prometheus could look like:

global: 
  scrape_interval: 1m
  scrape_timeout: 10s
  evaluation_interval: 1m

scrape_configs:
  - job_name: mysql
    scheme: http
    target_groups:
    - targets: 
        - '10.17.148.31:9104'
      labels:
        zone: mysql

Every minute Prometheus accesses 172.17.148.31:9104/metrics (/metrics is a Prometheus convention) and labels the result with zone=mysql. Querying the data you can use the labels.

This is a simple configuration. The fun of Prometheus is to have a lot of targets/jobs.

Let’s have a look at our specific endpoint:

> curl 10.17.148.31:9104/metrics
...
mysql_global_status_threads_cached 26
mysql_global_status_threads_connected 99
mysql_global_status_threads_created 125
mysql_global_status_threads_running 2
...

You as a MySQL administrator know what this is all about. The data is provided by an exporter. In our case a container :)

> docker run -d -p 9104:9104 --link=mysql:backend \
  -e DATA_SOURCE_NAME=prometheus:prometheus@secret(backend:3306)/ \ 
   prom/mysqld-exporter

This is old school Docker. Obviously the MySQL is running in a container also (mysql) and we are using the deprecated --link :)

The mysqld-exporter has a lot of options:

$ docker run --rm prom/mysqld-exporter --help
Usage of /bin/mysqld_exporter:
  -collect.auto_increment.columns
      Collect auto_increment columns and max values from information_schema
  -collect.binlog_size
      Collect the current size of all registered binlog files
  -collect.global_status
      Collect from SHOW GLOBAL STATUS (default true)
  -collect.global_variables
      Collect from SHOW GLOBAL VARIABLES (default true)
  -collect.info_schema.processlist
      Collect current thread state counts from the information_schema.processlist
  -collect.info_schema.tables
      Collect metrics from information_schema.tables (default true)
  -collect.info_schema.tables.databases string
      The list of databases to collect table stats for, or '*' for all (default "*")
  -collect.info_schema.tablestats
      If running with userstat=1, set to true to collect table statistics
  -collect.info_schema.userstats
      If running with userstat=1, set to true to collect user statistics
  -collect.perf_schema.eventsstatements
      Collect metrics from performance_schema.events_statements_summary_by_digest
  -collect.perf_schema.eventsstatements.digest_text_limit int
      Maximum length of the normalized statement text (default 120)
  -collect.perf_schema.eventsstatements.limit int
      Limit the number of events statements digests by response time (default 250)
  -collect.perf_schema.eventsstatements.timelimit int
      Limit how old the 'last_seen' events statements can be, in seconds (default 86400)
  -collect.perf_schema.eventswaits
      Collect metrics from performance_schema.events_waits_summary_global_by_event_name
  -collect.perf_schema.file_events
      Collect metrics from performance_schema.file_summary_by_event_name
  -collect.perf_schema.indexiowaits
      Collect metrics from performance_schema.table_io_waits_summary_by_index_usage
  -collect.perf_schema.tableiowaits
      Collect metrics from performance_schema.table_io_waits_summary_by_table
  -collect.perf_schema.tablelocks
      Collect metrics from performance_schema.table_lock_waits_summary_by_table
  -collect.slave_status
      Collect from SHOW SLAVE STATUS (default true)
  -config.my-cnf string
      Path to .my.cnf file to read MySQL credentials from. (default "/home/golang/.my.cnf")
  -log.level value
      Only log messages with the given severity or above. Valid levels: [debug, info, warn, error, fatal, panic]. (default info)
  -log_slow_filter
      Add a log_slow_filter to avoid exessive MySQL slow logging.  NOTE: Not supported by Oracle MySQL.
  -web.listen-address string
      Address to listen on for web interface and telemetry. (default ":9104")
  -web.telemetry-path string
      Path under which to expose metrics. (default "/metrics")

Prometheus ships with an expression browser. Giving you the opportunity to access and graph the data. It also provides his own query language :) Without graphing the following two queries should be self-explaining:

mysql_global_status_created_tmp_disk_tables

increase(mysql_global_status_created_tmp_disk_tables[2m])/120

[UPDATE]

Brian Brazil mentioned to use another (function)[https://prometheus.io/docs/querying/functions/] thx \o/

rate(mysql_global_status_created_tmp_disk_tables[2m])

I recommend to use Grafana as dashboard. You just need to provide the Prometheus server as source and reuse the Queries you used in the expression browser. There is also PomDash, but afaik Grafana is the way to go.

Prometheus rocks. Having a central point to do the performance analysis of the whole datacenter is awesome. There a lot of exporters you can use. Even writing your own exporter is quite easy.

Viel Spaß

Erkan

[Update]

There is a nice presentation I recommend to check it and see the nice graphs Grafana builds :)

[Update2]

Percona is always great in adopting new stuff. Today they announced there Percona Monitoring and Management. Of course it uses also some exporters, Prometheus and Grafana. I’m quite sure it would/could kill other solutions on the market \o/

by erkan at April 20, 2016 08:37 AM

Peter Zaitsev

Percona Live 2016: Running MongoRocks in Production

Percona Live 2016 Igor Canadi

It’s been a long first day at Percona Live 2016, filled with awesome insight and exciting topics. I was able to get to one more lecture before calling quits. For the final talk I saw today I listened to Igor Canadi, Software Engineer at Facebook, Inc., discuss Running MongoRocks in Production.

Facebook has been running MongoDB 3.0 with RocksDB storage engine (MongoRocks) at Parse since March of last year (2015). At this talk, they wanted to share some lessons learned about running MongoRocks in production. Igor was able to provide some interesting war stories and talk about performance optimization. Along with a little bit about RocksDB internals and which counters are most important to watch for.

RocksDB compares favorably to both the MMAP and WiredTiger storage engines when it comes to large write workloads.

The audience came away from the talk ready to get their feet wet with MongoRocks.

Below is a quick chat I had with Igor about RocksDB and MongoDB:

 

See the rest of the Percona Live schedule here.

 

by Dave Avery at April 20, 2016 06:38 AM

Percona Live 2016: High Availability Using MySQL Group Replication

Percona Live 2016 Luis Soares

Percona Live 2016 had a great first day, with an impressive number of speakers and topics. I was able to attend a session in the afternoon with Luis Soares, Principal Software Engineer at Oracle, on High Availability Using MySQL Group Replication. MySQL Group Replication is a MySQL plugin under development that brings together group communication techniques and database replication, providing both high availability (HA) and a multi-master update everywhere replication solution.

At MySQL Group Replication’s  core is a set of group communication primitives that act as the building blocks to creating reliable, consistent and dependable messaging between the servers in the group. This allows the set of MySQL servers to coordinate themselves and act as a consistently replicated state machine. As a consequence, the group itself is fault-tolerant, and so is the service it provides (i.e., the MySQL database service). The plugin also provides multi-master update-anywhere characteristics with automatic conflict detection and handling.

In this discussion, we learned about the technical details of the MySQL Group Replication plugin, and discussed how this fits into the overall picture of the MySQL HA. For instance, how it can be deployed together with MySQL Router to automate load balancing and failover procedures. We also discovered the newest enhancements and how to leverage them when deploying and experimenting with this plugin.

Listen to a brief chat I had with Luis on MySQL Group Replication:

Check out the rest of the conference schedule here.

by Dave Avery at April 20, 2016 06:16 AM

MariaDB AB

MariaDB Roadshow 2016 is starting

juergengiesel

Team MariaDB is touring across several European regions again. The theme for 2016 is "Data Management for Mission Critical Applications". Beside valuable presentations around database security, Big Data analysis and better scalability and high availability our technical experts will help you understand how to take advantage of the wide range of new features and enhancements available now in MariaDB 10.1, MariaDB MaxScale 1.4 and other MariaDB solutions. They will share tips & tricks to help you get the most out of your database. You will also discover what’s coming up next in MariaDB and the MariaDB ecosystem.

MariaDB Roadshow Map 2016

We will start at Friday in Helsinki where Michael "Monty" Widenius (MySQL and MariaDB creator) is among the listed speakers. The week afterwards we will continue with Nürnberg, Germany. More dates and locations are listed below.

Join MariaDB at this free event to learn how to:

  • Ensure high availability and performance of your database
  • Protect your data
  • Leverage open source to benefit from community-drive innovation that addresses real-world challenges

Depending on the location, we are partnering with different companies like SUSE, Red Hat, Microsoft, Adfinis SyGroup, and Quru.

Don’t miss this opportunity to discover how the leading open source database can:

  • Quickly deliver new applications and functionality
  • Protect against security and regulatory risk
  • Meet business uptime and performance needs

After the presentations you will have a chance to network and of course ask us any additional questions you may have.

For agendas and other details, please visit the dedicated event registration pages:

We will add a few more locations and dates within the next few weeks - so please check the events page on a regular base if your location is currently missing!

About the Author

juergengiesel's picture

Jürgen Giesel is EMEA Marketing Manager at MariaDB Corporation Ab. He started to work for MySQL GmbH as DACH Marketing Manager in 2002. From 2008 to 2010 he was MySQL Marketing Consultant at Sun Microsystem and from 2010 until early 2014 EMEA Marketing Manager for MySQL, Oracle Linux and Virtualization at Oracle.

by juergengiesel at April 20, 2016 05:51 AM

Peter Zaitsev

Percona Live 2016: A quick chat with Bill Nye, the Science Guy!

Bill Nye

Percona Live is humming along, and we had quite a whirlwind keynote session this morning. Bill Nye the Science Guy gave an amazing talk, Bill Nye’s Objective – Change the World, on how the process of science and critical thinking can help us not only be positive about the challenges we face in our world today, but also help us to come up with the big ideas we need to solve them. He discussed many topics, from how his parents met, their involvement in science (his mother worked on the Enigma Code in World War 2!), working at Boeing as an engineer, his involvement with Carl Sagan, and how he has worked to help harness renewable energy through solar panels, a solar water heater, and skylights at his own home in Studio City.

Bill Nye is also the CEO of The Planetary Society. The Planetary Society, founded in 1980 by Carl Sagan, Bruce Murray, and Louis Friedman, works to inspire and involve the world’s public in space exploration through advocacy, projects, and education. Today, The Planetary Society is the largest and most influential public space organization group on Earth.

After the talks, I was able to quickly catch Bill Nye and ask him a few questions.

 

 

by Dave Avery at April 20, 2016 12:18 AM

April 19, 2016

Peter Zaitsev

Percona Live 2016: Let Robots Manage your Schema (without destroying all humans)!

Yelp

We’re are rapidly moving through day one of the Percona Live Data Performance Conference, and I’m surrounded by incredibly smart people all discussing amazing database techniques and technology. The depth of solutions represented here, and the technical know-how needed to pull them off is astounding!

This afternoon I was able to catch Jenni Snyder, MySQL DBA  at Yelp deliver her talk on Let Robots Manage your Schema (without destroying all humans). While vaguely frightening, it was a fascinating talk on how automating schema changes helped Yelp’s development.

You’re probably already using automation to build your application, manage configuration, and alert you in case of emergencies. Jenni asks what’s keeping you from doing the same with your MySQL schema changes? For Yelp, the answer was “lots of things”. Today, Yelp uses Liquibase to manage their schema changes, pt-online-schema-change to execute them, and Jenkins to ensure that they’re run in all of their environments. During this session, she explained the history of MySQL schema management at Yelp, and how hard it was for both developers and DBAs.

Below is a video of her summarizing her team’s efforts and outcomes.

Check out the Percona Live schedule to see what is coming up next!

 

by Dave Avery at April 19, 2016 11:55 PM

Percona Live 2016: Performance of Percona Server for MySQL on Intel Server Systems using HDD, SATA SSD, and NVMe SSD as Different Storage Mediums

Intel’s Ken LeTourneau

We’re moving along on the first day at Percona Live 2016, and I was able to attend a lecture from Intel’s Ken LeTourneau, Solutions Architect at Intel, on Performance of Percona Server for MySQL on Intel Server Systems using HDD, SATA SSD, and NVMe SSD as Different Storage Mediums. In this talk, Ken reviewed some benchmark testing he did using MySQL on various types of storage mediums. This talk looked at the performance of Percona Server for MySQL for Linux running on the same Intel system, but with three different storage configurations. We looked at and compared the performance of:

  1. a RAID of HDD,
  2. a RAID of SATA SSD, and
  3. a RAID of NVMe SSD

In the talk,  Ken covered the hardware and system configuration and then discuss results of TPC-C and TPC-H benchmarks, as well as the overall system costs including hardware and software, and cost per transaction/query based on overall costs and benchmark results.

I got a chance to speak with Ken after his talk, check it out below!

 

by Dave Avery at April 19, 2016 09:33 PM

Day One of the Percona Live Data Performance Conference 2016 is off and running!

Bill Nye Small

Today was day one of the Percona Live Data Performance Conference! The day began with some excellent keynote speakers and exciting topics, and the packed room was eager to hear what our speakers had to say!

Peter ZaitsevPeter Zaitsev, CEO, Percona
Percona Opening  Keynote
Peter kicked it off today by thanking the sponsors, the speakers, the Percona Live committee, and the attendees for contributing and participating in this year’s event. It has grown and changed quite a bit from its initial creation. Peter emphasized how this a gathering of members of a community, one that changes and adapts, and discusses and debates many different points of views and opinions. No longer is just a conference about MySQL, but now includes MongoDB, Cassandra, and many other solutions and products that are all a part of the open source community. The purpose of the conference is to provide open and diverse opinions, quality content, a technical focus, and useful and practical ideas and solutions.

Chad JonesChad Jones, Chief Strategy Officer, Deep Information
Transcending database tuning problems: How machine learning helps DBAs play more ping pong

Next up was Chad Jones discussing how just as machine learning enables businesses to gain competitive advantage through predictive analytics, by looking deeper into the data stack we find the need for the same predictive capabilities for MySQL tuning. With over 10^13 possible tuning permutations, some requiring reboots or a rebuild, DBAs spend way too much time on MySQL tuning for a point-in-time situation that changes constantly. He demonstrated how unsupervised machine learning based on resource, workload and information modeling could predictively and continuously tune databases. DBAs can transcend the tuning game, saving precious time to work on important things, like improving your mad ping pong skills!

Bill NyeBill Nye, The Planetary Society, CEO
Bill Nye’s Objective – Change the World
F
inally this morning, we were treated to an outstanding lecture from world-renown scientist and media personality Bill Nye the Science Guy. Bill spent his time discussing his life, how he came to love science, and the ability it brings to understand the world. His experiences as an engineer at Boeing helped him appreciate the value in investing time and money into excellent design strategies and processes. Through the power of critical thinking and science, we can embrace optimism in a world that has many touch challenges. Bill Nye fights to raise awareness of the value of science, critical thinking, and reason. He hopes that the data he brings will help inspire people everywhere to change the world!

Those were the morning lectures today! Such a great set of speakers, I can’t wait for tomorrow! Check out our schedule here.

 

by Dave Avery at April 19, 2016 08:35 PM

Percona Server for MongoDB 3.2.4-1.0rc2 is now available

Percona_ServerfMDBLogoVert

Percona is pleased to announce the release of Percona Server for MongoDB 3.2.4-1.0rc2 on April 19, 2016. Download the latest version from the Percona web site or the Percona Software Repositories.

Percona Server for MongoDB is an enhanced, open source, fully compatible, highly scalable, zero-maintenance downtime database supporting the MongoDB v3.0 protocol and drivers. This release candidate is based on MongoDB 3.2.4, it extends MongoDB with MongoRocks and PerconaFT storage engines, as well as adding features like external authentication and audit logging. Percona Server for MongoDB requires no changes to MongoDB applications or code.

NOTE: The MongoRocks storage engine is still under development. There is currently no officially released version of MongoRocks that can be recommended for production. Percona Server for MongoDB 3.2.4-1.0rc2 includes MongoRocks 3.2.4, which is based on RocksDB 4.4.


This release includes all changes from MongoDB 3.2.4, and there are no additional improvements or new features on top of those upstream fixes.

NOTE: As of version 3.2, MongoDB uses WiredTiger as the default storage engine, instead of MMAPv1.

Percona Server for MongoDB 3.2.4-1.0rc2 release notes are available in the official documentation.

 

by Alexey Zhebel at April 19, 2016 08:20 PM

MariaDB AB

Generating MariaDB MaxScale PDF and HTML Documentation

markusmakela

I was recently talking on the #mariadb channel on FreeNode and I got a question about the often overlooked PDF documentation generation. In this blog I will show how to create a set of PDF files of the MariaDB MaxScale's documentation - a good way to keep the documentation handy. I did this on Ubuntu 14.04, but it should also work on other platforms.

Installing Packages

Since we'll be configuring MariaDB MaxScale, we'll need to install a few dependencies for MariaDB MaxScale and also grab the MariaDB server tarball. We start by installing the following packages.

sudo apt-get install git build-essential libssl-dev libaio-dev ncurses-dev bison flex cmake perl libtool libcurl4-openssl-dev libpcre3-dev tcl tcl-dev

Then we'll get the MariaDB server tarball which contains all the required parts for MariaDB MaxScale and extract it into our home directory.

wget --content-disposition https://downloads.mariadb.org/f/mariadb-10.0.24/bintar-linux-glibc_214-x86_64/mariadb-10.0.24-linux-glibc_214-x86_64.tar.gz/from/http%3A//mirror.netinch.com/pub/mariadb/?serve
tar -axf  mariadb-10.0.24-linux-glibc_214-x86_64.tar.gz

MariaDB MaxScale uses Pandoc and Latex to generate the PDF documentation from the raw Markdown files. This means that we'll have to install the following extra packages.

sudo apt-get install pandoc texlive texlive-xetex texlive-latex-extra

After installing these packages, we need to clone the MariaDB MaxScale repo, check out the 1.4.1 tag and create a directory where we can generate the PDF documentation.

git clone https://github.com/mariadb-corporation/MaxScale.git
git -C MaxScale checkout 1.4.1
mkdir build
cd build

Creating PDF Documentation

The final step is to configure MaxScale and run the generate_pdftarget. Since we used the binary tarball, we'll need to define the paths to various files in the extracted tarball in our home directory.

cmake ../MaxScale -DERRMSG=$HOME/mariadb-10.0.24-linux-x86_64/share/english/ \
-DMYSQL_EMBEDDED_LIBRARIES=$HOME/mariadb-10.0.24-linux-x86_64/lib/libmysqld.a \
-DMYSQL_EMBEDDED_INCLUDE_DIR=$HOME/mariadb-10.0.24-linux-x86_64/include/

After configuration, the only thing we need to do is to generate the actual PDF documentation.

make generate_pdf

After the generation is complete, all the PDF files can be found in the pdf/ directory.

Alternative HTML Documentation

MariaDB MaxScale can also create HTML documentation from the Markdown files. Creating it is as simple as running the make generate_html target.

make generate_html

This will create HTML documentation in the html/ directory.

And there we go, two different types of documentation ready for use. Although the contents of the PDF and HTML documents aren't as smooth as MariaDB MaxScale's Knowledge Base Articles, they can be a good substitute when browsing on a mobile device or when Internet access isn't available.

About the Author

markusmakela's picture

Markus Mäkelä is a Software Engineer working on MariaDB MaxScale. He graduated from Metropolia University of Applied Sciences in Helsinki, Finland.

by markusmakela at April 19, 2016 11:56 AM

April 18, 2016

Peter Zaitsev

Percona Monitoring and Management

Percona Monitoring and Management

Percona Monitoring and ManagementPercona is excited to announce the launch of Percona Monitoring and Management Beta!

Percona Monitoring and Management (PMM) is a fully open source solution for both managing MySQL platform performance and tuning query performance. It allows DBAs and application developers to optimize the performance of the Database Layer. PMM is an on-premises solution that keeps all of your performance and query data inside the confines of your environment, with no requirement for any data to cross the internet.

Assembled from a supported package of “best of breed” open source tools such as Prometheus, Grafana and Percona’s Query Analytics, PMM delivers results right out of the box.

With PMM, anyone with database maintenance responsibilities can get more visibility for actionable enhancements, realize faster issue resolution times, increase performance through focused optimization, and better manage resources. More information allows you to concentrate efforts on the areas that yield the highest value, rather than hunting and pecking for speed.

PMM monitors and provides performance data for Oracle’s MySQL Community and Enterprise Editions as well as Percona Server for MySQL and MariaDB.

Download Percona Monitoring and Management now.

 

CPU and Load

CPU and Load

QAN top 10

Top 10 Queries

QAN create table

QAN Create Table

QAN per-query metrics

QAN per-query metrics

QAN table indexes

QAN table indexes

by Bob Davis at April 18, 2016 09:09 PM

Oli Sennhauser

MariaDB 10.2 Window Function Examples

MariaDB 10.2 has introduced some Window Functions for analytical queries.

See also: Window Functions, Window Functions, Window function and Rows and Range, Preceding and Following

Function ROW_NUMBER()

Simulate a row number (sequence) top 3

SELECT ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY category_id) AS num
     , category.category_id
  FROM category
 LIMIT 3
;

or

SELECT ROW_NUMBER() OVER (ORDER BY category_id) AS num
     , category.category_id
  FROM category
 LIMIT 3
;
+-----+-------------+
| num | category_id |
+-----+-------------+
|   1 | ACTUAL      |
|   2 | ADJUSTMENT  |
|   3 | BUDGET      |
+-----+-------------+

ROW_NUMBER() per PARTITION

SELECT ROW_NUMBER() OVER (PARTITION BY store_type ORDER BY SUM(sf.store_sales) DESC) AS Nbr
     , s.store_type AS "Store Type", s.store_city AS City, SUM(sf.store_sales) AS Sales
  FROM store AS s
  JOIN sales_fact AS sf ON sf.store_id = s.store_id
 GROUP BY s.store_type, s.store_city
 ORDER BY s.store_type, Rank
;
+-----+---------------------+---------------+------------+
| Nbr | Store Type          | City          | Sales      |
+-----+---------------------+---------------+------------+
|   1 | Deluxe Supermarket  | Salem         | 1091274.68 |
|   2 | Deluxe Supermarket  | Tacoma        |  993823.44 |
|   3 | Deluxe Supermarket  | Hidalgo       |  557076.84 |
|   4 | Deluxe Supermarket  | Merida        |  548297.64 |
|   5 | Deluxe Supermarket  | Vancouver     |  534180.96 |
|   6 | Deluxe Supermarket  | San Andres    |  518044.80 |
|   1 | Gourmet Supermarket | Beverly Hills |  619013.24 |
|   2 | Gourmet Supermarket | Camacho       |  357772.88 |
|   1 | Mid-Size Grocery    | Yakima        |  304590.92 |
|   2 | Mid-Size Grocery    | Mexico City   |  166503.48 |
|   3 | Mid-Size Grocery    | Victoria      |  144827.48 |
|   4 | Mid-Size Grocery    | Hidalgo       |  144272.84 |
+-----+---------------------+---------------+------------+

Function RANK()

Ranking of top 10 salaries

SELECT full_name AS Name, salary AS Salary
     , RANK() OVER(ORDER BY salary DESC) AS Rank
  FROM employee
 ORDER BY salary DESC
 LIMIT 10
;
+-----------------+----------+------+
| Name            | Salary   | Rank |
+-----------------+----------+------+
| Sheri Nowmer    | 80000.00 |    1 |
| Darren Stanz    | 50000.00 |    2 |
| Donna Arnold    | 45000.00 |    3 |
| Derrick Whelply | 40000.00 |    4 |
| Michael Spence  | 40000.00 |    4 |
| Maya Gutierrez  | 35000.00 |    6 |
| Pedro Castillo  | 35000.00 |    6 |
| Laurie Borges   | 35000.00 |    6 |
| Beverly Baker   | 30000.00 |    9 |
| Roberta Damstra | 25000.00 |   10 |
+-----------------+----------+------+

Function DENSE_RANK()

SELECT full_name AS Name, salary AS Salary
     , DENSE_RANK() OVER(ORDER BY salary DESC) AS Rank
  FROM employee
 ORDER BY salary DESC
 LIMIT 10
;
+-----------------+----------+------+
| Name            | Salary   | Rank |
+-----------------+----------+------+
| Sheri Nowmer    | 80000.00 |    1 |
| Darren Stanz    | 50000.00 |    2 |
| Donna Arnold    | 45000.00 |    3 |
| Derrick Whelply | 40000.00 |    4 |
| Michael Spence  | 40000.00 |    4 |
| Maya Gutierrez  | 35000.00 |    5 |
| Pedro Castillo  | 35000.00 |    5 |
| Laurie Borges   | 35000.00 |    5 |
| Beverly Baker   | 30000.00 |    6 |
| Roberta Damstra | 25000.00 |    7 |
+-----------------+----------+------+

Aggregation Windows

SELECT full_name AS Name, salary AS Salary
     , SUM(salary) OVER(ORDER BY salary DESC) AS "Sum sal"
  FROM employee
 ORDER BY salary DESC
 LIMIT 10
;
+-----------------+----------+-----------+
| Name            | Salary   | Sum sal   |
+-----------------+----------+-----------+
| Sheri Nowmer    | 80000.00 |  80000.00 |
| Darren Stanz    | 50000.00 | 130000.00 |
| Donna Arnold    | 45000.00 | 175000.00 |
| Derrick Whelply | 40000.00 | 255000.00 |
| Michael Spence  | 40000.00 | 255000.00 |
| Laurie Borges   | 35000.00 | 360000.00 |
| Maya Gutierrez  | 35000.00 | 360000.00 |
| Pedro Castillo  | 35000.00 | 360000.00 |
| Beverly Baker   | 30000.00 | 390000.00 |
| Roberta Damstra | 25000.00 | 415000.00 |
+-----------------+----------+-----------+

Function CUME_DIST() and PERCENT_RANK()

SELECT s.store_state AS State, s.store_city AS City, SUM(e.salary) AS Salary
     , CUME_DIST() OVER (PARTITION BY State ORDER BY Salary) AS CumeDist
     , PERCENT_RANK() OVER (PARTITION BY State ORDER BY Salary) AS PctRank
  FROM employee AS e
  JOIN store AS s on s.store_id = e.store_id
 WHERE s.store_country = 'USA'
 GROUP BY s.store_name
 ORDER BY s.store_state, Salary DESC
;
+-------+---------------+-----------+--------------+--------------+
| State | City          | Salary    | CumeDist     | PctRank      |
+-------+---------------+-----------+--------------+--------------+
| CA    | Alameda       | 537000.00 | 1.0000000000 | 1.0000000000 |
| CA    | Los Angeles   | 221200.00 | 0.8000000000 | 0.7500000000 |
| CA    | San Diego     | 220200.00 | 0.6000000000 | 0.5000000000 |
| CA    | Beverly Hills | 191800.00 | 0.4000000000 | 0.2500000000 |
| CA    | San Francisco |  30520.00 | 0.2000000000 | 0.0000000000 |
| OR    | Salem         | 260220.00 | 1.0000000000 | 1.0000000000 |
| OR    | Portland      | 221200.00 | 0.5000000000 | 0.0000000000 |
| WA    | Tacoma        | 260220.00 | 1.0000000000 | 1.0000000000 |
| WA    | Spokane       | 223200.00 | 0.8571428571 | 0.8333333333 |
| WA    | Bremerton     | 221200.00 | 0.7142857143 | 0.6666666667 |
| WA    | Seattle       | 220200.00 | 0.5714285714 | 0.5000000000 |
| WA    | Yakima        |  74060.00 | 0.4285714286 | 0.3333333333 |
| WA    | Bellingham    |  23220.00 | 0.2857142857 | 0.1666666667 |
| WA    | Walla Walla   |  21320.00 | 0.1428571429 | 0.0000000000 |
+-------+---------------+-----------+--------------+--------------+

Function NTILE()

SELECT promotion_name, media_type
     , TO_DAYS(end_date)-TO_DAYS(start_date) AS Duration
     , NTILE(4) OVER (PARTITION BY promotion_name ORDER BY DURATION) AS quartile
     , NTILE(5) OVER (PARTITION BY promotion_name ORDER BY DURATION) AS quintile
     , NTILE(100) OVER (PARTITION BY promotion_name ORDER BY DURATION) AS precentile
  FROM promotion
 WHERE promotion_name = 'Weekend Markdown'
 LIMIT 10
;
+------------------+-------------------------+----------+----------+----------+------------+
| promotion_name   | media_type              | Duration | quartile | quintile | precentile |
+------------------+-------------------------+----------+----------+----------+------------+
| Weekend Markdown | In-Store Coupon         |        2 |        1 |        1 |          9 |
| Weekend Markdown | Daily Paper             |        3 |        3 |        4 |         29 |
| Weekend Markdown | Radio                   |        3 |        4 |        4 |         36 |
| Weekend Markdown | Daily Paper, Radio      |        2 |        2 |        2 |         13 |
| Weekend Markdown | Daily Paper, Radio, TV  |        2 |        2 |        3 |         20 |
| Weekend Markdown | TV                      |        2 |        3 |        3 |         26 |
| Weekend Markdown | Sunday Paper            |        3 |        3 |        4 |         28 |
| Weekend Markdown | Daily Paper, Radio, TV  |        3 |        3 |        4 |         34 |
| Weekend Markdown | Daily Paper             |        2 |        1 |        2 |         10 |
| Weekend Markdown | Street Handout          |        2 |        2 |        2 |         18 |
| Weekend Markdown | Bulk Mail               |        3 |        4 |        5 |         37 |
| Weekend Markdown | Cash Register Handout   |        2 |        2 |        2 |         14 |
| Weekend Markdown | Daily Paper, Radio, TV  |        3 |        3 |        4 |         31 |
| Weekend Markdown | Sunday Paper            |        2 |        3 |        3 |         27 |
| Weekend Markdown | Sunday Paper, Radio, TV |        1 |        1 |        1 |          4 |
+------------------+-------------------------+----------+----------+----------+------------+

by Shinguz at April 18, 2016 08:39 PM

MariaDB AB

MariaDB MaxScale Persistent Connections

martinbrampton

The MaxScale persistent connection feature has been available since version 1.3.0. It aims to reduce the load on the database servers in specific scenarios. Testing indicates benefits and feedback from live use has been positive.

A typical scenario where benefits will be obtained is a PHP web server application that uses a group of MariaDB databases to store information. HTTP is well known to be a connectionless protocol, and so each request from a browser is usually handled as a single invocation of a PHP program, which makes a fresh database connection and terminates it on completion. This is likely to happen in well under a second, many times per second. There are thus many instances of the relatively expensive operation of making a new database connection.

You might think that the obvious way to reduce this load is to use PHP persistent connections. But for a variety of technical reasons to do with PHP itself and web servers such as Apache, many experienced web developers strongly prefer to avoid this. It can lead to a build up of lost connections and wasted resources. Many people feel that PHP applications work best when invoked for a brief period with minimal linkage between successive operations.

So this is where the MaxScale persistent connection feature steps in. The feature is configured for each backend database server. The number of connections that can be allowed to persist is specified in the MaxScale configuration section for the server. Typically, where servers are in a group such as a master and some slaves, it will make sense to use the same number for each server, but MaxScale does not require this. An example of a server configuration section with persistent connections enabled is:

[server1]
type=server
address=10.131.24.62
port=3306
protocol=MySQLBackend
persistpoolmax=100
persistmaxtime=3600

Without persistent connections, whenever a client disconnects from MaxScale, all the backend database server connections that were made for that client are dropped. Now, for each server that has had the persistpoolmax configuration set to a non-zero value, when a client connects, a related connection to that database server will be considered for retention in the persistent connection pool. If there are already persistpoolmax connections in the pool, then the connection will be closed, but otherwise it will be added to the pool.

Conversely, when a new backend database connection is needed for a new client connection, the persistent pool for that database will be examined to see if there is a suitable connection already available. It is suitable provided it is for the same user and has not become stale by sitting in the pool for longer than the persistmaxtime specified for the server.

The effect is that a pool of connections can be constantly recycled, being used for a client connection, then placed in the pool, then reused for another client connection request. If the pool is large enough to cater for the maximum simultaneous number of requests, then new connections will rarely be needed.

There is one further check that can affect the handling of connections in the persistent pools. MaxScale continues to monitor the connection for any network messages. Although messages can be received correctly, in this situation MaxScale does not know what to do with them, not least because there is no related client. In fact, by far the most common reason for a message is the backend database disconnecting because of timeout. If any message arrives for a connection in the persistent pool, it is assumed that the connection is no longer valid, and it is removed from the pool.

So far at least, MaxScale persistent connections have been kept very simple, for maximum processing efficiency. You may have noticed that this means that there is a limitation. MaxScale assumes that the client can use an existing connection, irrespective of the state it is in. For example, the previous user may or may not have selected a default database. Or the previous user may have set a value. This limitation is not a problem for the target group of applications. For example, PHP will set any features of the database environment whenever a new connection is made.

Further functionality will be considered in future, although a balance between benefit and processing overhead will be a factor. Given the limitation, it is important to avoid using MaxScale persistent connections for applications that are not suited to it. In appropriate cases, though, valuable gains will be achieved.

About the Author

martinbrampton's picture

Martin Brampton is Principal Software Engineer at MariaDB

by martinbrampton at April 18, 2016 01:29 PM

MariaDB Foundation

MariaDB 10.2.0 Alpha now available

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.2.0. This is an alpha release. See the release notes and changelog for details on this release. Download MariaDB 10.2.0 Release Notes Changelog What is MariaDB 10.2? MariaDB APT and YUM Repository Configuration Generator Thanks, and enjoy MariaDB!

The post MariaDB 10.2.0 Alpha now available appeared first on MariaDB.org.

by Daniel Bartholomew at April 18, 2016 12:45 PM

April 17, 2016

Peter Zaitsev

How Percona XtraDB Cluster certification works

Percona XtraDB Cluster threading model
Percona XtraDB Cluster certificationIn this blog, we’ll  discuss how Percona XtraDB Cluster certification works. Percona XtraDB Cluster replicates actions executed on one node to all other nodes in the cluster and make it fast enough to appear as it if is synchronous (aka virtually synchronous).

Let’s understand all the things involved in the process (without losing data integrity).
There are two main types of actions: DDL and DML. DDL actions are executed using Total Order Isolation (let’s ignore Rolling Schema Upgrade for now) and DML using normal Galera replication protocol. This blog assumes the reader is aware of Total Order Isolation and MySQL replication protocol.
  • DML (Insert/Update/Delete) operations effectively change the state of the database, and all such operations are recorded in XtraDB by registering a unique object identifier (aka key) for each change (an update or a new addition). Let’s understand this key concept in a bit more detail.
    • A transaction can change “n” different data objects. Each such object change is recorded in XtraDB using a so-call append_key operation. The append_key operation registers the key of the data object that has undergone a change by the transaction. The key for rows can be represented in three parts as db_name, table_name, and pk_columns_for_table (if pk is absent, a hash of the complete row is calculated). In short there is quick and short meta information that this transaction has touched/modified following rows. This information is passed on as part of the write-set for certification to all the nodes of a cluster while the transaction is in the commit phase.
    • For a transaction to commit it has to pass XtraDB-Galera certification, ensuring that transactions don’t conflict with any other changes posted on the cluster group/channel. Certification will add the keys modified by given the transaction to its own central certification vector (CCV), represented by cert_index_ng. If the said key is already part of the vector, then conflict resolution checks are triggered.
    • Conflict resolution traces reference the transaction (that last modified this item in cluster group). If this reference transaction is from some other node, that suggests the same data was modified by the other node and changes of that node have been certified by the local node that is executing the check. In such cases, the transaction that arrived later fails to certify.
  • Changes made to DB objects are bin-logged. This is the same as how MySQL does it for replication with its Master-Slave eco-system, except that a packet of changes from a given transaction is created and named as a write-set.
  • Once the client/user issues a “COMMIT”, XtraDB Cluster will run a commit hook. Commit hooks ensure following:
    • Flush the binlogs.
    • Check if the transaction needs replication (not needed for read-only transactions like SELECT).
    • If a transaction needs a replication, then it invokes a pre_commit hook in the Galera eco-system. During this pre-commit hook, a write-set is written in the group channel by a “replicate” operation. All nodes (including the one that executed the transaction) subscribes to this group-channel and reads the write-set.
    • gcs_recv_thread is first to receive the packet, which is then processed through different action handlers.
    • Each packet read from the group-channel is assigned an “id”, which is a locally maintained counter by each node in sync with the group. When any new node joins the group/cluster, a seed-id for it is initialized to the current active id from group/cluster. (There is an inherent assumption/protocol enforcement that all nodes read the packet from a channel in same order, and that way even though each packet doesn’t carry “id” information it is inherently established using the local maintained “id” value).
      /* Common situation -
      * increment and assign act_id only for totally ordered actions
      * and only in PRIM (skip messages while in state exchange) */
      rcvd->id = ++group->act_id_;
      [This is an amazing way to solve the problem of the id co-ordination in multiple master
      system, otherwise a node will have to first get an id from central system or
      through a separate agreed protocol and then use it for the packet there-by
      doubling the round-trip time].
  • What happens if two nodes get ready with their packet at same time?
    • Both nodes will be allowed to put the packet on the channel. That means the channel will see packets from different nodes queued one-behind-another.
    • It is interesting to understand what happens if two nodes modify same set of rows. Let’s take an example:

     create -> insert (1,2,3,4)....nodes are in sync till this point.
     node-1: update i = i + 10;
     node-2: update i = i + 100;
Let's associate transaction-id (trx-id) for an update transaction that is executed on
node-1 and node-2 in parallel (The real algorithm is bit more involved (with uuid + seqno) but
conceptually the same so for ease I am using trx_id)
node-1:
   update action: trx-id=n1x
node-2:
   update action: trx-id=n2x
Both node packets are added to the channel but the transactions are conflicting. Let's see which
one succeeds. The protocol says: FIRST WRITE WINS. So in this case, whoever is first to write
to the channel will get certified. Let's say node-2 is first to write the packet
and then node-1 makes immediately after it.
NOTE: each node subscribes to all packages including its own package.
See below for details.
Node-2:
- Will see its own packet and will process it.
- Then it will see node-1 packet that it tries to certify but fails. (Will talk about
certification protocol in little while)
Node-1: 
- Will see node-2 packet and will process it. (Note: InnoDB allows isolation and so node-1
can process node-2 packets independent of node-1 transaction changes)
- Then it will see the node-1 packet that it tries to certify but fails. (Note even though the
packet originated from node-1 it will under-go certification to catch cases like thes. This
is beauty of listening to own events that make consistent processing path even if
events are locally generated)

  • Now let’s talk about the certification protocol using the example sighted above. As discussed above, the central certification vector (CCV) is updated to reflect reference transaction.

Node-2:
- node-2 sees its own packet for certification, adds it to its local CCV and performs
certification checks. Once these checks pass it updates the reference transaction by
setting it to "n2x"
- node-2 then gets node-1 packet for certification. Said key is already present in
CCV with a reference transaction set it to "n2x", whereas write-set proposes setting it to
"n1x". This causes a conflict, which in turn causes the node-1 originated transaction to
fail the certification test.
This helps point out a certification failure and the node-1 packet is rejected.
Node-1:
- node-1 sees node-2 packet for certification, which is then processed, the
local CCV is updated and the reference transaction is set to "n2x"
- Using the same case explained above, node-1 certification also rejects the node-1 packet.
Well this suggests that the node doesn't need to wait for certification to complete, but
just needs to ensure that the packet is written to the channel. The applier transaction will always
win and the local conflicting transaction will be rolled back.

  • What happens if one of the nodes has local changes that are not synced with group.

create (id primary key) -> insert (1), (2), (3), (4);
node-1: wsrep_on=0; insert (5); wsrep_on=1
node-2: insert(5).
insert(5) will generate a write-set that will then be replicated to node-1.
node-1 will try to apply it but will fail with duplicate-key-error, as 5 already exist.
XtraDB will flag this as an error, which would eventually cause node-1 to shutdown.

  • With all that in place, how is GTID incremented if all the packets are processed by all nodes (including ones that are rejected due to certification)? GTID is incremented only when the transaction passes certification and is ready for commit. That way errant-packets don’t cause GTID to increment. Also, don’t confuse the group packet “id” quoted above with GTID. Without errant-packets, you may end up seeing these two counters going hand-in-hand, but they are no way related.

by krunal.bauskar at April 17, 2016 11:40 PM

April 16, 2016

MariaDB AB

Downloading MariaDB MaxScale binaries without registration

rasmusjohansson

MariaDB MaxScale, the dynamic routing platform for MariaDB Server (and MySQL Server) had its first stable 1.0 GA release 15 months ago. Since then, the popularity of MariaDB MaxScale has grown exponentially. It has in many cases become a default piece of the architecture in clustered setups of MariaDB Server, in master-slave replication setups and in very large replication topologies making use of MariaDB MaxScale’s Binlog Server functionality.

MariaDB MaxScale has come far in a short time and it’s getting attention also from the point of view of how it’s being distributed. There has been several active community members pointing out that MariaDB MaxScale binaries (and not just the source code) should be made available to the broader user community in a similar fashion to MariaDB Server. We want to address this by making the community version available as easy as possible. The binaries have therefore been made available from downloads.mariadb.com/files/MaxScale without registration.

Please note that I wrote “the community version”. That currently equals the latest and greatest version of MariaDB MaxScale, version 1.4.1 as of writing this. What it also implies is that we plan to release an Enterprise version of MariaDB MaxScale that will include additional functionality on top of the community version. We are working on the Terms of Use for that purpose. Stay tuned.

It’s now straightforward to download the MariaDB MaxScale community binaries. Get them here!

Tags: 

About the Author

rasmusjohansson's picture

Rasmus has worked with MariaDB since 2010 and was appointed VP Engineering in 2013. As such, he takes overall responsibility for the architecting and development of MariaDB Server, MariaDB Galera Cluster and MariaDB Enterprise.

by rasmusjohansson at April 16, 2016 04:17 PM

Peter Zaitsev

The final three Database Performance Team characters are . . .

Full team banner

Database Performance TeamThe last email introduced two members of the Percona Database Performance Team: The Maven and The Specialist. Now we’re ready to reveal the identity of the final three team members.

The Database Performance Team is comprised of our services experts, who work tirelessly every day to guarantee the performance of your database. Percona’s support team is made up of superheroes that make sure your database is running at peak performance.

The third member is technical, possesses clairvoyant foresight, with the knowledge and statistics to account for all issues, and manages problems before they happen. Who is this champion?

Database Performance Team

The Clairvoyant
Percona Technical Account Manager
“Problems solved before they appear.”

The Clairvoyant predicts the future of technology and operations to head off major issues. Saves you before you even know there is a problem. With The Clairvoyant working with you, you know you’re going to be just fine.

 

 

 

The fourth member is remotely all-seeing, a director, good at multi-tasking, adapts-on-the-fly, and is cool in a crisis. Who is this champion?

Database Performance Team


The Maestro
Percona Remote DBA

“Just like that, optimized database.”

The Maestro is the head of the operation, a leader, a single-person think tank that controls all from their home base of operations. A cyborg, half-man half-machine. With the Maestro controlling your database, all your worries are through.

 

 

 

The fifth member is insanely strong, can’t be stopped, is hard to knock down, and the product of rigorous testing with unlimited endurance. Who is this champion?

Database Performance Team

 

The Powerhouse
Percona Software
“Performance Starts Here!”

Percona’s suite of MySQL and MongoDB software and toolkits are a powerhouse of performance, the backbone of the organization – they show unparalleled strength and dependability, with endurance to boot. As a product of the open source community, our software has been tested by fire and proven resilient.

 

 

Your Mission

Follow @Percona on Twitter and use hashtag “#DatabasePerformanceTeam #PerconaLive” and join us at The Percona Live Data Performance conference April 18-21for chances to win Database Performance Team member T-shirts! Collect them all! Stay tuned, as we will have more fun games for the Database Performance Team over the coming weeks!

Percona Live Update!

We know! We get it! It’s hard to plan with everything going on, and now you have to register for Percona Live at the last minute! Well, for once it pays off! The Percona Live Data Performance Conference will be April 18-21 at the Hyatt Regency Santa Clara & The Santa Clara Convention Center.

We have a special procrastination discount for everyone who waited until almost too late!Use discount code “procrastination” to get 15% off your full conference pass.

If you can only make it to the keynotes, BOFs, or Community Networking Reception, use discount code “KEY” for a $5 Expo Only pass.

And if you cannot make it this year, watch our blog following the conference and we’ll announce when and where the keynote recordings and breakout session slide decks can be found.

Let’s Get Social

Join the ranks of the Database Street Team! Fun games, cool prizes – more info is coming soon!

Connect with us on TwitterFacebook and LinkedIn for the latest news from Percona. And we invite you to report bugs, make suggestions or just get caught up on all of Percona’s projects on GitHub.

by Dave Avery at April 16, 2016 03:48 PM

Webinar Q & A for Introduction to Troubleshooting Performance: What Affects Query Execution?

Query Execution

Query ExecutionIn this blog, I will provide answers to the Webinar Q & A for Introduction to Troubleshooting Performance: What Affects Query Execution?

First, I want to thank you for attending the April, 7 webinar. This webinar is the third in the “MySQL Troubleshooting” webinar series and last introductory webinar in the series. The recording and slides for the webinar are available here. Here is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: If we had some MyISAM tables, could we use clustering in MariaDB?

A: Do you mean Galera Cluster? You can use it, but keep in mind what MyISAM support in Galera is still experimental and not recommended to use in production. You need to enable variable wsrep_replicate_myisam to enable MyISAM support.

Q: Is there a filesystem type that is better than another?

A: If you’re speaking about modern versus old file systems, modern is usually better. For example, ext3 or NTFS is certainly better than FAT32, which does not support files larger than 4GB. But that type of file system can be very helpful when you need to store data on a flash drive that can be read by any computer or mobile device. The same advice applies to ext2.

So a better answer to this question is it depends on your purpose. You can start from these Percona Live Slides by Ammon Sutherland, who describes the difference between file systems, their options and how they can improve or not MySQL performance. Then check this blog post by Dimitri Kravtchuk, and this one by Yves Trudeau.

Certainly NFS is not a good choice because it does not provide MySQL storage engines (particularly InnoDB) a reliable answer if data was really flushed to disk.

Q: Can I download this training somewhere on your website?

A: This webinar and all future webinars are available at the same place you registered. In this case, links to slides and recorded webinar are available here.

Q: What are good system level tools that are helpful?

A: In the webinar I presented a minimal set of tools that collect essential information, necessary for troubleshooting MySQL performance issues. Usually, you can start with them, then consult Brendan D. Gregg’s great picture. Regarding which tools which we like in Percona: a favorite is certainly perf.

Q: I am planning to use Percona Toolkit to optimize MySQL query performance.  When I tried to install Percona binaries, there is a conflict on MySQL binaries. Could you please help with how to install Percona binaries. Requesting for prerequisite?

A: A common reason for such conflicts is the client or shared libraries. You just need to replace them with Percona’s equivalent.

Q: How do you increase buffers for a high load case?

A: Buffers are linked to MySQL system variables. To increase them try

SET variable_name=NEW_VALUE
 first, then test in the same session. If you are not happy with the result, increase the global buffer:
SET GLOBAL variable_name=NEW_VALUE
, then test the performance of the whole database. If you are still not happy with the result, adjust the variable value in your configuration file. Of course, you don’t need to try to set a variable that has global scope. Try setting only in the session first. Sometimes you cannot change the variable value online. In this case, be especially careful: test first, be ready to rollback changes, choose a less busy time, etc.

Q: How do you handle deadlocks?

Q: Which are the best practices to fix performance caused by InnoDB deadlocks?

A: These two questions are about practically same thing. Deadlocks are not 100% avoidable in InnoDB, therefore the best solution is to code the application in such a way that it can simply re-run transactions that were rolled back. But if you see deadlocks too often, this is not always possible. In such cases, you need to investigate which rows are locked by each transaction, find out why this pattern repeats and fix your transactions or tables (sometimes if you search without indexes, a query can lock more rows than needed to resolve the query – adding an appropriate index can solve locking issue).

Q: Is it important in MySQL 5.7 to separate logs and data in differents disks or partitions?

A: By separating data and logs on different disk partitions you gain performance because you can write more data in parallel and it’s more stable. In the case of a data disk breaking, you will have log files untouched and can restore data from them. But this only applies to cases when partitions are on different physical disks.

Q: When are we going to see something like Datastax OpsCenter for MariaDB, with nice performance monitoring and support for easy partitioning?

A: There are many monitoring and administration tools for MySQL and MariaDB, which include VividCortex, SolarWinds, Webyog, MySQL Enterprise Monitor, MySQL Workbench, etc. Please specify in comments which feature in Datastax OpsCenter you miss in these products. I can probably answer if there is an alternative. I don’t know about any plans for cloning Datastax OpsCenter for MariaDB or MySQL.

Q: If you are storing queries in stored procedures, and you make changes to those SP’s, how long will it take for them to be cached? The next time they are run, or after x amount of times?

A: What do you mean queries would be cached? If you mean MySQL Query Cache: the call of the SP will be in cache, and the result will be reloaded next time the procedure is called. If you mean the data retrieved by these queries, if it is stored in the InnoDB buffer pool or not it is same: the next time when you call the SP, new data will be in the buffer pool.

Q: I have a very big table (up to 9GB data), and it is a very heavy read/write table. We actually store all our chat messages in that table: every record in the table is a row. What would be the best way to get out of this situation, NoSQL or partition? Will Percona be helpful for me in this situation?

A: This depends on your table definition and how you use it. It is hard to answer having only this description of your issue. Regarding Percona help, this looks like a case for our Support or Consulting.

Q: I want to archive data on a daily basis. I use INSERT INTO table_achive SELECT * FROM table. This takes about 45 minutes for 7.000.000 rows. Is that slow?

A: As I mentioned at the beginning of the webinar, there is no “yes” or “no” answer. It depends on the speed of your disk, how large the table is (retrieving 7,000,000 rows which contain only two integer columns would be certainly faster than retrieving 7,000,000 rows, each of them has maybe 42 columns). But what I can certainly say is that most likely this dataset does not fit into your memory, and this query requires you to create disk-based temporary tables. This query most likely sets too many locks and can slow down other queries on the “table”. If all this concerns you, consider copying data in chunks: for example 

INSERT INTO table_achive SELECT * FROM table WHERE Primary_Key BETWEEN start AND end
. You can use utility pt-online-schema-change as a guide.

 

by Sveta Smirnova at April 16, 2016 03:29 PM

April 15, 2016

Peter Zaitsev

Creating Geo-Enabled applications with MongoDB, GeoJSON and MySQL

Creating Geo-Enabled applications with MongoDB, GeoJSON and MySQL

Creating Geo-Enabled applications with MongoDB, GeoJSON and MySQLThis blog post will discuss creating geo-enabled applications with MongoDB, GeoJSON and MySQL.

Introduction

Recently I published a blog post about the new GIS features in MySQL 5.7. Today I’ve looked into how to use MongoDB (I’ve tested with 3.0 and 3.2, with 3.2 being much faster) for the same purpose. I will also talk about GIS in MySQL and MongoDB at Percona Live next week (together with my colleague Michael Benshoof).

MongoDB and GIS

MongoDB has a very useful feature called “geoNear.” There are other MongoDB spatial functions available to calculate the distance on a sphere (like the Earth), i.e.

$nearSphere
 ,
$centerSphere
,
$near
 – but all of them have restrictions. The most important one is that they do not support sharding. The 
geoNear
 command in MongodDB, on the other hand, supports sharding. I will use
geoNear
 in this post.

For this test, I exported Open Street Map data from MySQL to MongoDB (see the “Creating GEO-enabled applications with MySQL 5.6” post for more details on how to load this data to MySQL).

  1. Export the data to JSON. In MySQL 5.7, we can use JSON_OBJECT to generate the JSON file:
    mysql> SELECT JSON_OBJECT('name', replace(name, '"', ''),
           'other_tags', replace(other_tags, '"', ''), 'geometry', st_asgeojson(shape)) as j
           FROM `points`
           INTO OUTFILE '/var/lib/mysql-files/points.json';
    Query OK, 13660667 rows affected (4 min 1.35 sec)
  2. Use
    mongoimport
      to import JSON into MongoDB (I’m using 24 threads, -j 24, to use parallel import):
    mongoimport --db osm --collection points -j 24 --file /var/lib/mysql-files/points.json
    2016-04-11T22:38:10.029+0000    connected to: localhost
    2016-04-11T22:38:13.026+0000    [........................] osm.points  31.8 MB/2.2 GB (1.4%)
    2016-04-11T22:38:16.026+0000    [........................] osm.points  31.8 MB/2.2 GB (1.4%)
    2016-04-11T22:38:19.026+0000    [........................] osm.points  31.8 MB/2.2 GB (1.4%)
    …
    2016-04-11T23:12:13.447+0000    [########################] osm.points  2.2 GB/2.2 GB (100.0%)
    2016-04-11T23:12:15.614+0000    imported 13660667 documents
  3. Create a 2d index:
    mongo
    > use osm
    switched to db osm
    > db.points.createIndex({ geometry : "2dsphere" } )
    {
            "createdCollectionAutomatically" : false,
            "numIndexesBefore" : 1,
            "numIndexesAfter" : 2,
            "ok" : 1
    }

Another option would be using the osm2mongo Ruby script, which will convert the osm file and load it directly to MongoDB.

Now I can use the

geoNear
 command to find all the restaurants near my location:

> db.runCommand( { geoNear: "points",
                   near: { type: "Point" ,  coordinates: [ -78.9064543, 35.9975194 ]},
                   spherical: true,
... query: { name: { $exists: true, $ne:null},
                   "other_tags": { $in: [ /.*amenity=>restaurant.*/, /.*amenity=>cafe.*/ ] } },
                   "limit": 5, "maxDistance": 10000 } )
{
        "results" : [
                {
                        "dis" : 127.30183814835166,
                        "obj" : {
                                "_id" : ObjectId("570329164f45f7f0d66f8f13"),
                                "name" : "Pop's",
                                "geometry" : {
                                        "type" : "Point",
                                        "coordinates" : [
                                                -78.9071795,
                                                35.998501
                                        ]
                                },
                                "other_tags" : "addr:city=>Durham,addr:country=>US,addr:housenumber=>605,addr:street=>West Main Street,amenity=>restaurant,building=>yes"
                        }
                },
                {
                        "dis" : 240.82201047521244,
                        "obj" : {
                                "_id" : ObjectId("570329df4f45f7f0d68c16cb"),
                                "name" : "toast",
                                "geometry" : {
                                        "type" : "Point",
                                        "coordinates" : [
                                                -78.9039761,
                                                35.9967069
                                        ]
                                },
                                "other_tags" : "addr:full=>345 West Main Street, Durham, NC 27701, US,amenity=>restaurant,website=>http://toast-fivepoints.com/"
                        }
                },
...
}

MongoDB 3.0 vs 3.2 with geoNear

MongoDB 3.2 features Geospatial Optimization:

MongoDB 3.2 introduces version 3 of 2dsphere indexes, which index GeoJSON geometries at a finer gradation. The new version improves performance of 2dsphere index queries over smaller regions. In addition, for both 2d indexes and 2dsphere indexes, the performance of geoNear queries has been improved for dense datasets.

I’ve tested the performance of the above

geoNear
 query with MongoDB 3.0 and MongoDB 3.2 (both the old and new versions of 2dsphere index). All the results statistics are for a
"limit": 5
 and
"maxDistance": 10000
.

MongoDB 3.0, index version 2:

> db.points.getIndexes()
...
        {
                "v" : 1,
                "key" : {
                        "geometry" : "2dsphere"
                },
                "name" : "geometry_2dsphere",
                "ns" : "osm.points",
                "2dsphereIndexVersion" : 2
        }
]
"stats" : {
        "nscanned" : 1728,
        "objectsLoaded" : 1139,
        "avgDistance" : 235.76379903759667,
        "maxDistance" : 280.2681226202938,
        "time" : 12
},

MongoDB 3.2, index version 2:

> db.points.getIndexes()
[
...
        {
                "v" : 1,
                "key" : {
                        "geometry" : "2dsphere"
                },
                "name" : "geometry_2dsphere",
                "ns" : "osm.points",
                "2dsphereIndexVersion" : 2
        }
]
...
"stats" : {
          "nscanned" : 513,
          "objectsLoaded" : 535,
          "avgDistance" : 235.76379903759667,
          "maxDistance" : 280.2681226202938,
          "time" : 5
},

What is interesting here is that even with the

"2dsphereIndexVersion" : 2
, MongoDB 3.2 performs much faster and scans a much smaller number of documents.

MongoDB 3.2, index version 3:

> db.points.dropIndex("geometry_2dsphere")
{ "nIndexesWas" : 2, "ok" : 1 }
> db.points.createIndex({ geometry : "2dsphere" } )
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.points.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "osm.points"
        },
        {
                "v" : 1,
                "key" : {
                        "geometry" : "2dsphere"
                },
                "name" : "geometry_2dsphere",
                "ns" : "osm.points",
                "2dsphereIndexVersion" : 3
        }
]
"stats" : {
          "nscanned" : 144,
          "objectsLoaded" : 101,
          "avgDistance" : 235.76379903759667,
          "maxDistance" : 280.2681226202938,
          "time" : 1
        },

That is significantly faster, 1ms for five results!

MySQL and GeoJSON revisited

To compare to the performance of the above query, I’ve created a similar query in MySQL. First of all, we will need to use the good old bounding rectangle (envelope) trick to only include the points in the 10 miles radius (or so). If we don’t, MySQL will not be able to use spatial (RTREE) index. I’ve created the following function to generate the envelope:

DELIMITER //
CREATE DEFINER = current_user() FUNCTION create_envelope(lat decimal(20, 14), lon decimal(20, 14), dist int) RETURNS geometry DETERMINISTIC
begin
declare point_text varchar(255);
declare l varchar(255);
declare p geometry;
declare env geometry;
declare rlon1 double;
declare rlon2 double;
declare rlat1 double;
declare rlat2 double;
set point_text = concat('POINT(', lon, ' ', lat, ')');
set p = ST_GeomFromText(point_text, 1);
set rlon1 = lon-dist/abs(cos(radians(lat))*69);
set rlon2 = lon+dist/abs(cos(radians(lat))*69);
set rlat1 = lat-(dist/69);
set rlat2 = lat+(dist/69);
set l = concat('LineString(', rlon1, ' ', rlat1, ',', rlon2 , ' ', rlat2, ')');
set env= ST_Envelope(ST_GeomFromText(l, 1));
return env;
end //
DELIMITER ;
mysql> set @lat= 35.9974043;
Query OK, 0 rows affected (0.00 sec)
mysql> set @lon = -78.9045615;
Query OK, 0 rows affected (0.00 sec)
mysql> select st_astext(create_envelope(@lat, @lon, 10));
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| st_astext(create_envelope(@lat, @lon, 10)) |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| POLYGON((-79.08369589058249 35.852476764,-78.72542710941751 35.852476764,-78.72542710941751 36.142331836,-79.08369589058249 36.142331836,-79.08369589058249 35.852476764)) |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Then we can use the following query (an update of the GeoJSON query from my previous post):

set @lat= 35.9974043;
set @lon = -78.9045615;
set @p = ST_GeomFromText(concat('POINT(', @lon, ' ', @lat, ')'), 1);
set group_concat_max_len = 100000000;
SELECT CONCAT('{
  "type": "FeatureCollection",
  "features": [
  ',
   GROUP_CONCAT('{
   "type": "Feature",
      "geometry": ', ST_AsGeoJSON(shape), ',
      "properties": {"distance":', st_distance_sphere(shape, @p) , ', "name":"', name , '"}
   }' order by st_distance_sphere(shape, @p)),
  ']
}') as j
FROM points_new
WHERE st_within(shape, create_envelope(@lat, @lon, 10))
	  and (other_tags like '%"amenity"=>"cafe"%' or other_tags like '%"amenity"=>"restaurant"%')
	  and name is not null
	  and st_distance_sphere(shape, @p) < 1000;
...
1 row in set (0.04 sec)

The time is slower: 40ms in MySQL compared to 1ms – 12ms in MongoDB. The box is AWS EC2 t2.medium.

To recap the difference between MongoDB

geoNear
 and MySQL
st_distance_sphere
:

  • MongoDB
    geoNear
     uses 2dsphere index, so it is fast; however, it can’t just calculate the distance between two arbitrary points
  • MySQL st_distance_sphere is a helper function and will only calculate the distance between two points; it will not use an index – we will have to use the
    create_envelope
     function to restrict the search so MySQL will use an index

Time-wise, this is not an apples to apples comparison as the query is quite different and uses a different technique.

Visualizing the results

Results for GeoJSON for Google Maps API:

{
 "type": "FeatureCollection",
 "features": [
 {
  "type": "Feature",
     "geometry": {"type": "Point", "coordinates": [-78.9036457, 35.997125]},
     "properties": {"distance":87.67869122893659, "name":"Pizzeria Toro"}
  },{
  "type": "Feature",
     "geometry": {"type": "Point", "coordinates": [-78.9039761, 35.9967069]},
     "properties": {"distance":93.80064086575564, "name":"toast"}
  },{
  "type": "Feature",
     "geometry": {"type": "Point", "coordinates": [-78.9031929, 35.9962871]},
     "properties": {"distance":174.8300018385443, "name":"Dame's Chicken and Waffles"}
  },
...
}

Now we can add those on a map:

Back to MongoDB: pluses and minuses

MongoDB uses Google’s S2 library to perform GIS calculations. The

geoNear
 command is fast and easy to use for finding points of interests near you (which is the most common operation). However, full GIS support does not natively exist.

Another issue I came across when creating a 2dsphere index: MongoDB is very strict when checking the lines and polygons. For example:

> db.lines.createIndex({ geometry : "2dsphere" } )
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "errmsg" : "exception: Can't extract geo keys: { _id: ObjectId('570308864f45f7f0d6dfbed2'), name: "75 North", geometry: { type: "LineString", coordinates: [ [ -85.808852, 41.245582 ], [ -85.808852, 41.245582 ] ] }, other_tags: "tiger:cfcc=>A41,tiger:county=>Kosciusko, IN,tiger:name_base=>75,tiger:name_direction_suffix=>N,tiger:reviewed=>no" }  GeoJSON LineString must have at least 2 vertices: [ [ -85.808852, 41.245582 ], [ -85.808852, 41.245582 ] ]",
        "code" : 16755,
        "ok" : 0
}

MongoDB complains about this: type: “LineString”, coordinates: [ [ -85.808852, 41.245582 ], [ -85.808852, 41.245582 ] ]

This is a “bad” line string as the starting point and ending point are the same. I had to remove the bad data from my MongoDB imported dataset, which was tricky by itself. MongoDB (as opposed to MySQL) does not have a native way to compare the values inside the JSON, so I had to use $where construct – which is slow and acquires a global lock:

> db.lines.remove({"geometry.type": "LineString", "geometry.coordinates": {$size:2},
$where: "this.geometry.coordinates[0][0] ==  this.geometry.coordinates[1][0]
      && this.geometry.coordinates[0][1] == this.geometry.coordinates[1][1]" })
WriteResult({ "nRemoved" : 22 })

After that, I was able to add the 2dsphere index.

Conclusion

MongoDB looks good, is pretty fast and easy for geo-proximity search queries – until you go outside of the one function and need full GIS support (which does not natively exist). It may be trickier to implement other GIS functions like

st_contains or st_within
.

Update: as pointed out, MongoDB actually supports $geoWithin and $geoIntersects GIS functions.

Update 2: I was asked about MySQL and GeoJSON: why not to use the ST_MakeEnvelope function. One of the issues with

ST_MakeEnvelope
 is that it only works with SRID 0 (it requires point geometry arguments with an SRID of 0) and OSM data is stored with SRID 1. But also I will need to “add” 10 miles to my point. The only way to do that is to calculate the new point, 10 miles apart from “my” point/location. I have to use a custom function to manipulate the lat/lon pair.

The explain plan for the MySQL GeoJSON query shows that MySQL uses SHAPE (Spatial) index:

mysql> explain SELECT CONCAT('{
 "type": "FeatureCollection",
 "features": [
 ',
 GROUP_CONCAT('{
 "type": "Feature",
 "geometry": ', ST_AsGeoJSON(shape), ',
 "properties": {"distance":', st_distance_sphere(shape, @p) , ', "name":"', name , '"}
 }' order by st_distance_sphere(shape, @p)),
 ']
}') as j
FROM points_new
WHERE st_within(shape, create_envelope(@lat, @lon, 10))
 and (other_tags like '%"amenity"=>"cafe"%' or other_tags like '%"amenity"=>"restaurant"%')
 and name is not null
 and st_distance_sphere(shape, @p) < 1000;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: points_new
   partitions: NULL
         type: range
possible_keys: SHAPE
          key: SHAPE
      key_len: 34
          ref: NULL
         rows: 665
     filtered: 18.89
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

And if I remove “st_within(shape, create_envelope(@lat, @lon, 10))” from the query it will show the full table scan:

mysql> explain SELECT CONCAT('{
 "type": "FeatureCollection",
 "features": [
 ',
 GROUP_CONCAT('{
 "type": "Feature",
 "geometry": ', ST_AsGeoJSON(shape), ',
 "properties": {"distance":', st_distance_sphere(shape, @p) , ', "name":"', name , '"}
 }' order by st_distance_sphere(shape, @p)),
 ']
}') as j
FROM points_new
WHERE (other_tags like '%"amenity"=>"cafe"%' or other_tags like '%"amenity"=>"restaurant"%')
 and name is not null
 and st_distance_sphere(shape, @p) < 1000;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: points_new
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11368798
     filtered: 18.89
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

 

by Alexander Rubin at April 15, 2016 06:58 PM

MySQL Document Store Developments

MySQL Document Store

MySQL Document StoreThis blog will discuss some recent developments with MySQL document store.

Starting MySQL 5.7.12, MySQL can be used as a real document store. This is great news!

In this blog post, I am going to look into the history-making MySQL work better for “NoSQL” workloads and more of the details on what MySQL document store offers at this point.

First, the idea of using reliable and high-performance MySQL storage engines for storing or accessing non-relational data through SQL is not new.

Previous Efforts

MyCached (Memcache protocol support for MySQL) was published back in 2009. In 2010 we got HandlerSocket plugin, providing better performance and a more powerful interface. 2011 introduced both MySQL Cluster (NDB) support for MemcacheD protocol and MemcacheD access to InnoDB tables as part of MySQL 5.6.

Those efforts were good, but focused a rear-window view. They provided a basic (though high-performance) Key-Value interface, but many developers needed both the flexibility of unstructured data and the richness inherent in structured data (as seen in document store engines like MongoDB).

When the MySQL team understood the needs, MySQL 5.7 (the next GA after 5.6) shipped with excellent features like JSON documents support, allowing you to mix structured and unstructured data in the same applications. This support includes indexes on the JSON field as well as an easy reference “inside” the document fields from applications.

MariaDB 5.3 attempted to support JSON functionality with dynamic columns. More JSON functions were added in MariaDB 10.1, but both these implementations were not as well done or as integrated as in MySQL 5.7 – they have a rushed feel to them. The plan is for MariaDB 10.2 to catch up with MySQL 5.7.  

JSON in SQL databases is still a work in progress, and there is no official standard yet. As of right now different DBMSs implement it differently, and we’ve yet to see how a standard MySQL implementation will look.

MySQL as a Document Store

Just as we thought we would have wait for MySQL 5.8 for future “NoSQL” improvements, the MySQL team surprised us by releasing MySQL 5.7.12 with a new “X Plugin.” This plugin allows us to use MySQL as a document store and avoid using SQL when a different protocol would be a better fit.

Time will tell whether the stability and performance of this very new plugin are any good – but it’s definitely a step in the right direction!

Unlike Microsoft DocumentDB, the MySQL team choose not to support the MongoDB protocol at this time. Their protocol, however, looks substantially inspired by MongoDB and other document store databases. There are benefits and drawbacks to this approach. On the plus side, going with your own syntax and protocol allows you to support a wealth of built-in MySQL functions or transactions that are not part of the MongoDB protocol. On the other hand, it also means you can’t just point your MongoDB application to MySQL and have it work.  

In reality, protocol level compatibility at this level usually ends up working only for relatively simple applications. Complex applications often end up relying on not-well-documented side effects or specific performance properties, requiring some application changes anyway.

The great thing about MySQL document store is that it supports transactions from the session start. This is important for users who want to use document-based API, but don’t want to give up the safety of data consistency and ACID transactions.

The new MySQL 5.7 shell provides a convenient command line interface for working with document objects, and supports scripting with SQL, JavaScript and Python.

The overall upshot of this effort is that developers familiar with MySQL, who also need document store functionality, will be able to continue using MySQL instead of adding MongoDB (or some other document store database) to the mix in their environments.

Make no mistake though: this is an early effort in the MySQL ecosystem! MongoDB and other companies have had a head start of years! Their APIs are richer (in places), supported by more products and frameworks, better documented and more understood by the community in general,  and are generally more mature.

The big question is when will the MySQL team be able to focus their efforts on making document-based APIs a “first-class citizen” in the MySQL ecosystem? As an example, they need to ensure stable drivers exist for a wide variety of languages (currently, the choice is pretty limited).

It would also be great to see MySQL go further by taking on other areas that drive the adoption of NoSQL systems – such as the easy way they achieve high availability and scale. MySQL’s replication and manual sharding were great in the early 2000s, but is well behind modern ease-of-use and dynamic scalability requirements.

Want to learn more about this exciting new development in MySQL 5.7? Join us at Percona LiveJan Kneschke, Alfredo Kojima, Mike Frank will provide an overview of MySQL document store as well as share internal implementation details.

by Peter Zaitsev at April 15, 2016 05:04 PM

April 14, 2016

Peter Zaitsev

Percona Live featured talk with Álvaro Hernández Tortosa — ToroDB: Supercharging your RDBMS with MongoDB super powers

Percona Live Álvaro Hernández Tortosa

Percona Live featured talkWelcome to the last Percona Live featured talk with Percona Live Data Performance Conference 2016 speakers! In this series of blogs, we’ll highlight some of the speakers that will be at this year’s conference, as well as discuss the technologies and outlooks of the speakers themselves. Make sure to read to the end to get a special Percona Live registration bonus!

It’s been great getting to know some of the speakers, and learning about their talks. Everyone highlighted in this blog series can be found at Percona Live in Santa Clara next week (April 18-21). See you there!

In this Percona Live featured talk, we’ll meet Álvaro Hernández Tortosa, CTO of 8Kdata. His talk will be on ToroDB: Supercharging your RDBMS with MongoDB super powersToroDB is an open source project that turns your RDBMS into a MongoDB-compatible server, supporting the MongoDB query API, MongoDB’s replication, and both OLTP and OLAP/DW workloads. I had a chance to speak with Álvaro and learn a bit more about ToroDB:

Percona: Give me a brief history of yourself: how you got into database development, where you work, what you love about it.

Álvaro: I started using databases as a developer around the year 2000. I realized they were exactly what they advertise: a building block to help create applications that require persistence, concurrency, durability and advanced querying capabilities. A magical piece of software that lets you rely on powerful abstractions, and make developers’ lives easier, cheaper and less buggy.

At some point, and after facing some “leaks” over abstractions, I started researching the database space –  almost every direction and every feature. I haven’t stopped doing that.

Percona: Your talk is going to be on “ToroDB: Supercharging your RDBMS with MongoDB super powers.” It seems like you’re not taking sides in the NoSQL vs. MySQL Great Debate! Are there myths about the differences in approaches that you have to constantly correct?

Álvaro: Many! To name a few myths:

  • NoSQL is schema-less. There’s no such thing as a schema-less database. Even unstructured data has a “schema.” It just happens to be “attached” to the data, and this is what allows it to change from document to document. But there is always a schema, and even if it only varies slightly NoSQL repeats that schema in almost every document. I prefer to call this “schema-attached.”
  • SQL is slow, and indeed slower than NoSQL. That’s simply not true, especially when you compare “apples to apples.” Most SQL versus NoSQL benchmarks compare fully durable relational setups with reduced durability NoSQL configurations. Relational can be as fast, or even faster, than NoSQL. It’s just that they have different approaches to speed, reliability and durability.
  • You have to choose between either NoSQL or relational. Well, this is the main theme of the talk, so I’ll leave readers to attend the talk to resolve the myth. 😉

Percona: How does a mixed environment affect application performance? What are the performance advantages of combining the two approaches? How does ToroDB help?

Álvaro: It depends on the operations. Inserts are comparable in speed, sometimes slightly slower. Indexed queries are in the same order of magnitude. Filtered queries are usually significantly faster, since ToroDB automatically classifies (some sort of automatic “normalization”) the documents at insertion time, and data is automatically partitioned. Aggregate queries can be up to orders of magnitude faster, as SQL is terrific at this (compared to NoSQL, which not good). The latter is even more significant when combined with columnar stores and compression.

Percona: What do you see as an issue that we the database community needs to be on top of regarding NoSQL (and ToroDB) development? What keeps you up at night concerning the future of NoSQL (and SQL for that matter)?

Álvaro: ToroDB adds a much-wanted feature to MySQL that MongoDB users have with MongoDB: transactions. Transactions are a really powerful abstraction that significantly simplifies application development, and allows you to create resilient applications. However, cluster-wide transactions are a really challenging problem. I think distributed transactions and a single-system image of the database (as in a snapshot isolation over a cluster) are two of the most challenging and desirable properties of current database systems.

Percona: What are you most looking forward to at Percona Live Data Performance Conference 2016?

Álvaro: Foremost, feedback and interacting with other database colleagues. ToroDB started as a database that only supported PostgreSQL as a backend. We would like to have ToroDB running on MySQL/Percona too, and by listening to MySQL users, figure out how they could benefit from ToroDB. Engaging with architects and performance experts at the conference is invaluable to us.

You can read more about ToroDB at 8kdata’s website: www.8kdata.com.  You can also follow Álvaro on Twitter at @ahachete. Check out what is going at with 8kdata at this link, including their participation in Percona Live!

Want to find out more about Álvaro and ToroDBRegister for Percona Live Data Performance Conference 2016, and see his talk ToroDB: Supercharging your RDBMS with MongoDB super powers. Use the code “FeaturedTalk” and receive $100 off the current registration price!

Percona Live Data Performance Conference 2016 is the premier open source event for the data performance ecosystem. It is the place to be for the open source community as well as businesses that thrive in the MySQL, NoSQL, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.

The Percona Live Data Performance Conference will be April 18-21 at the Hyatt Regency Santa Clara & The Santa Clara Convention Center.

 

by Dave Avery at April 14, 2016 04:03 PM

MariaDB AB

Database Firewall Filter in MaxScale 1.4.1

markusmakela

New and Improved Functionality

The recently released 1.4.1 version of MariaDB MaxScale contains a bundle of great improvements to the Database Firewall Filter, dbfwfilter. This article starts by describing the dbfwfilter module and how it is used. Next we’ll find out what kinds of improvements were made to the filter in MaxScale 1.4.1 and we’ll finish by looking at a few use cases for it.

Here are the highlights of the new dbfwfilter functionality in the 1.4.1 release of MaxScale.

  • Configurable filter actions on rule match
    • Allow the query, block the query or ignore the match
  • Logging of matching and/or non-matching queries

With these new features, you can easily implement various types of configurations including a dry-run mode where no action is taken but all matching and non-matching queries are logged.

Later on we’ll introduce the new features and explore how we can better secure our database environment by using these new features.

What Is the Database Firewall Filter?

The database firewall filter, dbfwfilter, is a module which acts as a firewall between the clients and the backend cluster. Similar to the iptables software found in most Linux based distributions, this module either allows or denies SQL queries based on a set of rules.

A rules is defined by a small and simple syntax that can be used to describe the kind of content it matches. These rules can then be assigned to users to make sets of user and rule groups. For more details about the rule syntax, read the Database Firewall Filter Documentation.

The dbfwfilter module allows you to control what kinds of queries are allowed. Because the filter understands the content that passes through it, it can prevent malicious attempts to execute SQL which can compromise your data.

Here are a few examples how the dbfwfilter can help improve the security of your database cluster.

  • Block delete queries with no “WHERE” clause - preventing attacker from mass deleting data from tables and damage to customer data
  • Block select queries on certain table (such as user data, customer data) with no “WHERE” clause - preventing attacker from getting mass access to confidential user data
  • Only allow queries with certain columns on certain tables for a set of users. So these users will only have access to subset of columns and will not be able to access any other data

Configuring the Filter

The best way to understand how the dbfwfilter works is to configure it for use.We start by defining the rules for the filter. We’ll define a simple rule and apply it to all possible users. We already have MaxScale installed and configured for normal operation. For a good tutorial on setting up MaxScale, read the MaxScale Tutorial.

The rule we’ll create is a no_where_clause rule which matches if the query lacks a WHERE/HAVING clause. We’ll also add an optional on_queries part to the rule which allows us to limit the matching to update only.

rule protected_update deny no_where_clause on_queries update
users %@% match any rules protected_update

The first line defines the rule protected_update while the second line applies this rule to all users. The match any makes it so that any rule in the list will cause it to be considered a match. Since we only have one rule, the value of match is not very important. The matching type allows you to combine simpler rules into a more complex one.

The next step is to create a filter definition. The following filter definition uses the dbfwfilter module and defines the rules parameter, which tells us where the rules for this filter are. The rules we defined earlier have been saved to /usr/home/markusjm/rules. The action parameter tells the filter what it should do when a query matches a rule. We’ll set to to block so the filter blocks any query that matches a rule.

[firewall-filter]
type=filter
module=dbfwfilter
rules=/home/markusjm/rules
action=block

We will use the following service configuration.

[RW Split Router]
type=service
router=readwritesplit
servers=server1,server2,server3,server4
user=maxuser
passwd=maxpwd
filters=firewall-filter

Testing the Configuration

After we’ve configuration, we can start MaxScale and execute some queries. First we’ll create a table for our test and insert some values into it.

MySQL [test]> create table t1(id int);
Query OK, 0 rows affected (0.41 sec)
MySQL [test]> insert into test.t1 values (1), (2), (3);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

Next we’ll try to update the values without defining a where clause.

MySQL [test]> update t1 set id=0;
ERROR 1141 (HY000): Access denied for user 'maxuser'@'127.0.0.1' to database 'test': Required WHERE/HAVING clause is missing.

We can see that it was rejected because it matched the rule we defined. Let’s try an update with a where clause.

MySQL [test]> update t1 set id=0 where id=1;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

It works as expected. How about a select without a where clause?

MySQL [test]> select * from t1;
+------+
| id   |
+------+
|    0 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

So our simple rule matches and works as expected. Next we’ll show a simple use case where two dbfwfilter instances are combined to form a more complex rule set.

Combining block and allow

Once we have our basic setup we can expand it by creating a second set of rules and a second filter definition. We’ll then combine these two filters into one filter pipeline which blocks queries that match the rule we defined earlier and only allow rules that match our new rule set. We start by defining the new rules.

rule safe_columns deny columns name email
users %@% match any rules safe_columns

This rule matches when one of the name, address or salary columns are accessed. The rule is a simple one which allows us to restrict queries to a certain set of columns. We’ll save the configuration in /home/markusjm/rules-whitelist and continue to configure the new filter definition.

[whitelist-filter]
type=filter
module=dbfwfilter
rules=/home/markusjm/rules-whitelist
log_no_match=true
action=allow

The filter definition is similar to the one we defined before apart from the different action value and the new log_no_match parameter. The filter will only allow queries that match the rules to be executed. In addition to this, all non-matching queries will be logged so we’ll know when an unexpected query is blocked.

Once we’ve configured the second filter, we can combine them into a pipeline in the following way.

[RW Split Router]
type=service
router=readwritesplit
servers=server1,server2,server3,server4
user=maxuser
passwd=maxpwd
filters=whitelist-filter|firewall-filter

Now we can test how our new combined filters work. We’ll test using a simple table and one row of data.

MariaDB [(none)]> show create table test.t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `name` varchar(60) DEFAULT NULL,
  `address` varchar(60) DEFAULT NULL,
  `email` varchar(120) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [(none)]> select * from test.t1;
+----------+---------------+-------------------+
| name     | address       | email             |
+----------+---------------+-------------------+
| John Doe | Castle Hill 1 | johndoe@gmail.com |
+----------+---------------+-------------------+
1 row in set (0.00 sec)

Let’s try selecting name and email from the table.

MariaDB [(none)]> select name, email from test.t1;
+----------+-------------------+
| name     | email             |
+----------+-------------------+
| John Doe | johndoe@gmail.com |
+----------+-------------------+

As expected, the query is successful. We can try to select only address from the table but we will be denied access.

MySQL [(none)]> select address from test.t1;
ERROR 1141 (HY000): Access denied for user 'maxuser'@'127.0.0.1'

So only queries which target either the name or email column pass through the whitelist-filter we’ve configured. Next we can test if updates to name work.

MySQL [(none)]> update test.t1 set name="No Name";
ERROR 1141 (HY000): Access denied for user 'maxuser'@'127.0.0.1': Required WHERE/HAVING clause is missing.

MySQL [(none)]> update test.t1 set name="No Name" where name="John Doe";
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [(none)]> select name from test.t1;
+---------+
| name    |
+---------+
| No Name |
+---------+

As we can see, the filter we previously configured still works and by combining these two filters, we only allow queries that are in the set of allowed queries but not in the set of denied queries. This combining of rule sets allows us to create rich sets of rules that all the queries must conform to. Since we added the log_no_match parameter to the filter definition, we can see a log message with details about the non-matching query we executed.

2016-04-02 16:21:42   notice : [RW Split Router] Query for '%@%' by maxuser@127.0.0.1 was not matched: select address from test.t1

With this, we could implement a simple auditing mechanism at the cluster level which would allow us to detect unexpected queries and reveal information about the user who executed them.

What does the future hold?

In the near future, we’re aiming to implement smarter firewalling functionality into MaxScale. The smart firewall is aimed for minimal configuration and maximal ease of use by automating rule generation. The smart firewall is planned to be included in the next release of MaxScale.

If you have any questions, feedback or great ideas, join us on maxscale@googlegroups.com for discussion about MaxScale. We also have the #maxscale IRC channel on FreeNode.

About the Author

markusmakela's picture

Markus Mäkelä is a Software Engineer working on MariaDB MaxScale. He graduated from Metropolia University of Applied Sciences in Helsinki, Finland.

by markusmakela at April 14, 2016 08:27 AM

Colin Charles

MariaDB Berlin Meetup Notes & Slides

We had the first MariaDB Berlin Meetup on Tuesday 12.04.2016 at the Wikimedia Berlin offices at 7pm. More or less there were over 54 people that attended the event, a mix of MariaDB Corporation employees and community members. We competed with the entertainment at the AWS Summit Berlin which was apparently about 400m away! Food and drink were enjoyed by all, and most importantly there were many, many lightning talks (minimum 5 minutes, maximum 10 minutes – most were about 6-7 minutes long).

The bonus of all of this? Lots and lots of slides for you to see. Grab them from the Google Drive folder MariaDB Berlin meetup April 2016.

  1. Monty talked about improving the speed of connections to MariaDB Server, some work he’s just pushed fairly recently to the 10.2 tree.
  2. Dipti spoke about MariaDB ColumnStore and it is now clear we’ll see some source/binary drop by the end of May 2016.
  3. Sergei Petrunia and Vicentiu Ciorbaru spoke about the upcoming window functions that MariaDB Server 10.2.0 will see (yes, the alpha should be out real soon now).
  4. Jan spoke about InnoDB in 10.2.
  5. Lixun Peng spoke about a fairly interesting feature, the idea to flashback via mysqlbinlog and how you can have a “Time Machine”. I can’t wait for flashback/time machine to appear in 10.2. The demo for this is extremely good.
  6. Kolbe spoke about data at rest encryption using the MariaDB Amazon AWS KMS plugin.
  7. Sanja and Georg went up together to speak about 10.2 protocol enhancements as well as what you’ll see in Connector/C 3.0.
  8. Wlad gave us a good rundown on authenticating with GSSAPI, something you will notice is also available in MariaDB Server 10.1’s later releases.
  9. Johan Wikman gave us an introduction to MariaDB MaxScale, which started off the talks on MaxScale.
  10. Markus talked about the readwritesplit plugin.
  11. Massimiliano went into the Binlog server.
  12. Martin didn’t use slides but gave us an amazing talk titled “Rival concepts of SQL Proxy”; it was very well given and I’ve encouraged him to write a blog post about it.
  13. Community member Ben Kochie, an SRE at SoundCloud gave us a quick talk on Monitoring MySQL with Prometheus and how much they depend on the PERFORMANCE_SCHEMA.
  14. Diego Dupin spoke a little about the MariaDB Java Connector, and the idea was to do a demo but the projector via HDMI seemed to be a bit wonky (this was also true of using my Mac; the VGA output however worked fine). So it was just a quick talk without any deck.

We ended with a quick Q&A session with Monty dominating it. Lots of interesting questions around why the name Maria, licensing thoughts, ensuring all the software we have are in distributions, etc. Some ended up going for pizza while others ended up in a hotel bar at the Crowne Plaza Potsdamer Platz — and the chatter went on till at least 11pm.

Thanks again to Georg Richter who found us the venue and also did a lot of the legwork with Wikimedia Foundation.

by Colin Charles at April 14, 2016 06:53 AM

Peter Zaitsev

Orchestrator-agent: How to recover a MySQL database

orchestrator-agent

orchestrator-agentIn our previous post, we showed how Orchestrator can handle complex replication topologies. Today we will discuss how the Orchestrator-agent complements Orchestrator by monitoring our servers, and provides us a snapshot and recovery abilities if there are problems.

Please be aware that the following scripts and settings in this post are not production ready (missing error handling, etc.) –  this post is just a proof of concept.

What is Orchestrator-agent?

Orchestrator-agent is a sub-project of Orchestrator. It is a service that runs on the MySQL servers, and it gives us the seeding/deploying capability.

In this context “seeding” means copying MySQL data files from a donor server to the target machine. Afterwards, the MySQL can start on the target machine and use the new data files. 

Functionalities (list from Github):
  • Detection of the MySQL service, starting and stopping (start/stop/status commands provided via configuration)
  • Detection of MySQL port, data directory (assumes configuration is /etc/my.cnf)
  • Calculation of disk usage on data directory mount point
  • Tailing the error log file
  • Discovery (the mere existence of the orchestrator-agent service on a host may suggest the existence or need of existence of a MySQL service)
  • Detection of LVM snapshots on MySQL host (snapshots that are MySQL specific)
  • Creation of new snapshots
  • Mounting/umounting of LVM snapshots
  • Detection of DC-local and DC-agnostic snapshots available for a given cluster
  • Transmitting/receiving seed data

The following image shows us an overview of a specific host (click on an image to see a larger version):

Screen Shot 2016-03-28 at 12.55.43

How does it work?

The Orchestrator-agent runs on the MySQL server as a service, and it connects to Orchestrator through an HTTP API. Orchestrator-agent is controlled by Orchestrator. It uses and is based on LVM and LVM snapshots: without them it cannot work.

The agent requires external scripts/commands example:

  • Detect where in the local and remote DCs it can find an appropriate snapshot
  • Find said snapshot on server, mount it
  • Stop MySQL on target host, clear data on MySQL data directory
  • Initiate send/receive process
  • Cleanup data after copy

If these external commands are configured, a snapshot can be created through the Orchestrator web interface example. The agent gets the task through the HTTP API and will call an external script, which creates a consistent snapshot.

Orchestrator-agent configuration settings

There are many configuration options, some of which we’ll list here:

  • SnapshotMountPoint
     – Where should the agent mount the snapshot.
  • AgentsServer
      –  Where is the AgentServer example: “http://192.168.56.111:3001” .
  • CreateSnapshotCommand
      – Creating a consistent snapshot.
  • AvailableLocalSnapshotHostsCommand
      – Shows us the available snapshots on localhost.
  • AvailableSnapshotHostsCommand
      – Shows us the available snapshots on remote hosts.
  • SnapshotVolumesFilter
      – Free text which identifies MySQL data snapshots.
  • ReceiveSeedDataCommand
      – Command that receives the data.
  • SendSeedDataCommand
      – Command that sends the data.
  • PostCopyCommand
      – Command to be executed after the seed is complete.

Example external scripts

As we mentioned before, these scripts are not production ready.

"CreateSnapshotCommand": "/usr/local/orchestrator-agent/create-snapshot.sh",

#!/bin/bash
donorName='MySQL'
snapName='my-snapshot'
lvName=`lvdisplay | grep "LV Path" | awk '{print $3}'|grep $donorName`
size='500M'
dt=$(date '+%d_%m_%Y_%H_%M_%S');
mysql -e"STOP SLAVE; FLUSH TABLES WITH READ LOCK;SELECT SLEEP(10);" &>/dev/null &
lvcreate --size $size --snapshot --name orc-$snapName-$dt $lvName

This small script creates a consistent snapshot what agent can use later, but it is going to Lock all the tables for 10 seconds.  (Better solutions can be exists but this is a proof of concept script.)

"AvailableLocalSnapshotHostsCommand": "lvdisplay | grep "LV Path" | awk '{print $3}'|grep my-snapshot",

We can filter the available snapshots based on the “SnapshotVolumesFilter” string.

"AvailableSnapshotHostsCommand": "echo rep4",

You can define a command that can show where the available snapshots in your topology are, or you can use a dedicated slave. In our test, we easily used a dedicated server.

"SnapshotVolumesFilter": "-my-snapshot",

“-my-snapshot” is the filter here.

"ReceiveSeedDataCommand": "/usr/local/orchestrator-agent/receive.sh",

#!/bin/bash
directory=$1
SeedTransferPort=$2
echo "delete $directory"
rm -rf $directory/*
cd $directory/
echo "Start nc on port $SeedTransferPort"
`/bin/nc -l -q -1 -p $SeedTransferPort | tar xz`
rm -f $directory/auto.cnf
echo "run chmod on $directorty"
chown -R mysql:mysql $directory

The agent passes two parameters to the script, then it calls the script like this:

/usr/local/orchestrator-agent/recive.sh /var/lib/mysql/ 21234

The script cleans the folder (you can not start while mysqld is running; first you have to stop it on the web interface or command line), listens on the specified port and it waits for the compressed input. After it removes “auto.cnf”,  MySQL recreates a new UUID at start time. Finally, make sure every file has the right owner.

"SendSeedDataCommand": "/usr/local/orchestrator-agent/seed.sh",

#!/bin/bash
directory=$1
targetHostname=$2
SeedTransferPort=$3
cd $directory
echo "start nc"
`/bin/tar -czf - -C $directory . | /bin/nc $targetHostname $SeedTransferPort`

The agent passes three parameters to the script:

/usr/local/orchestrator-agent/seed.sh /tmp/MySQLSnapshot rep5 21234

The first parameter is the mount point of the snapshot, and the second one is the destination host and the port number. The script easily compresses the data and sends it through “nc”.

Job details

Screen Shot 2016-03-28 at 16.40.15

A detailed log can be found from every seed. These logs can be really helpful in discovering any problems during the seed.

Why do we need Orchestrator-agent?

If you have a larger MySQL topology where you frequently have to provide new servers, or if you have a dev/staging replica set where you want to easily go back to a previous production stage, Orchestrator-agent can be really helpful and save you a lot of time. Finally, you’ll have the time for other fun and awesome stuff!

Features requests

Orchestrator-agent does its job, but adding a few extra abilities could make it even better:

  • Adding XtraBackup support.
  • Adding Mysqlpump/Mysqldump/Mydumper support.
  • Implement some kind of scheduling.
  • Batch seeding (seeding to more than one server with one job.)

Summary

Shlomi did a great job again, just like with Orchestrator.

Orchestrator and Orchestrator-agent together give us a useful platform to manage our topology and deploy MySQL data files to the new servers, or re-sync old ones.

by Tibor Korocz at April 14, 2016 12:42 AM

April 13, 2016

Jean-Jerome Schmidt

Please take part in our open source database management survey

As members of the open source database users community, it’d be great if you could participate in our open source database deployment and management survey.

Your feedback will help us make our resources and tools for deploying, monitoring, managing and scaling databases of even better use to all. It will give us valuable insight into the challenges you face when operating databases.

Please fill out our survey today by providing your input below; this will take approx. 5 minutes of your time.

<<<<

We’ll share the results of the survey once we have compiled your responses.

Thank you!

by Severalnines at April 13, 2016 08:48 PM

Peter Zaitsev

Evaluating Database Compression Methods: Update

Database Compression Methods

Database Compression MethodsThis blog post is an update to our last post discussing database compression methods, and how they stack up against each other. 

When Vadim and I wrote about Evaluating Database Compression Methods last month, we claimed that evaluating database compression algorithms was easy these days because there are ready-to-use benchmark suites such as lzbench.

As easy as it was to do an evaluation with this tool, it turned out it was also easy to make a mistake. Due to a bug in the benchmark we got incorrect results for the LZ4 compression algorithm, and as such made some incorrect claims and observations in the original article. A big thank you to Yann Collet for reporting the issue!

In this post, we will restate and correct the important observations and recommendations that were incorrect in the last post. You can view the fully updated results in this document.

Compression Method

As you can see above, there was little change in compression performance. LZ4 is still the fastest, though not as fast after correcting the issue.

Compression Ratio

The compression ratio is where our results changed substantially. We reported LZ4 achieving a compression ratio of only 1.89 — by far lowest among compression engines we compared. In fact, after our correction, the ratio is 3.89 — better than Snappy and on par with QuickLZ (while also having much better performance).  

LZ4 is a superior engine in terms of the compression ratio achieved versus the CPU spent.

Compression vs Decompression

The compression versus decompression graph now shows LZ4 has the highest ratio between compression and decompression performance of the compression engines we looked at.

Compression Speed vs Block Size

The compression speed was not significantly affected by the LZ4 block size, which makes it great for compressing both large and small objects. The highest compression speed achieved was with a block size of 64KB — not the highest size, but not the smallest either among the sizes tested.

Compression Speed vs Block Size

We saw some positive impact on the compression ratio by increasing the block size, However, increasing the block size over 64K did not substantially improve the compression ratio, making 64K an excellent block for LZ4, where it had the best compression speed and about as-good-as-it-gets compression. A 64K block size works great for other data as well, though we can’t say how universal it is.

Scatterplot with compression speed vs compression ratio

Rplot05

 

Updated Recommendations

Most of our recommendations still stand after reviewing the updated results, with one important change. If you’re looking for a fast compression algorithm that has decent compression, consider LZ4.  It offers better performance as well as a better compression ratio, at least on the data sets we tested.

 

by Peter Zaitsev at April 13, 2016 08:12 PM

Colin Charles

Major post-GA features in the 5.7 release!

Interesting developments in the MySQL world – it can now be used as a document store and you can query the database using JavaScript instead of SQL (via the MySQL Shell). There is also a new X Plugin (see: mysql-5.7.12/rapid/) (which now makes use of protocol buffers (see: mysql-5.7.12/extra/protobuf/)). I will agree, this is more than just a maintenance release.

Do get started playing with MySQL Shell. If you’re using the yum repository, remember to ensure you have enabled the mysql-tools-preview in /etc/yum.repos.d/mysql-community.repo. And don’t forget to load the X Plugin in the server! I can’t wait for the rest of the blog posts in the series, and today just took a cursory look at all of this — kudos Team MySQL @ Oracle.

However, I’m concerned that the GA is getting what you would think of as more than just a maintenance release. We saw 5.7.11 get at rest data encryption for InnoDB, and now 5.7.12 getting even more changes. This is going to for example, ship in the next Ubuntu LTS, Xenial Xerus. Today it has 5.7.11, but presumably after release it will be upgrade to 5.7.12. I am not a huge fan of surprises in LTS releases (predictability over 5 years is a nice thing; this probably explains why I still have a 5.0.95 server running), but I guess this small band-aid is what we need to ensure this doesn’t happen going forward?

As for the other question I’ve seen via email from several folk so far: will MariaDB Server support this? I don’t see why not in the future, so why not file a Jira?

by Colin Charles at April 13, 2016 01:16 PM

Federico Razzoli

MaxScale binaries are now available

UPDATE: Short story: ignore the next update and read the post. Long story: the original post was a mistake, as explained in the next update. But then, MariaDB released free MaxScale binaries and everything I wrote in the post is now correct.

UPDATE 2016-04-14: It seems that I was mistaken. MaxScale download page is a bit different from MariaDB Enterprise page, and does not explicitly require us to accept terms of use before download. But we accept those terms while creating an account.

So, MaxScale binaries cannot be used in production without paying for MariaDB enterprise. Thanks to the persons who commented this post and pointed my mistake. My apologies to my readers.

I won’t delete this post because I don’t want the comments to disappear, as they express opinions of some community members.

My jestarday’s post Comments on MaxScale binaries followed up a post from Percona’s blog. It had much more visits than any other post I wrote before. It was linked by Peter Zaitsev and Oli Senhauser on social networks. No, this is not a self-advertisement, I’m just saying that the problem I’ve talked about is considered important by the community.

Today, MaxScale binaries are available! Not because of me (obviously), but because MariaDB must have found out that the community badly wants those binaries.

MaxScale 1.4.1 was released today, and it is available from the Database Downloads page on MariaDB.com. You can click on MaxScale and then you can select the version (1.4.1, 1.3.0, 1.2.1) and the system (Debian, Ubuntu, RHEL/CentOS, SLES, both available as deb/rpm or tarball). Registration is required for download, but this is acceptable, as long as the binaries are freely available.

There are no restrictive terms of use. Here is how the copyright note starts:

This source code is distributed as part of MariaDB Corporation MaxScale. It is free
software: you can redistribute it and/or modify it under the terms of the
GNU General Public License as published by the Free Software Foundation,
version 2.

The only problem is the lack for a repository – but now that the binaries are freely available, I expect most Linux distros to provide their packages.

I downloaded the Ubuntu 14.04 version on my Mint machine, and everything worked as expected:

fede-mint-0 ~ # dpkg -i /home/federico/Downloads/maxscale-1.4.1-1.ubuntu_trusty.x86_64.deb 
Selecting previously unselected package maxscale.
(Reading database ... 184280 files and directories currently installed.)
Preparing to unpack .../maxscale-1.4.1-1.ubuntu_trusty.x86_64.deb ...
Unpacking maxscale (1.4.1) ...
Setting up maxscale (1.4.1) ...
Processing triggers for man-db (2.6.7.1-1ubuntu1) ...
Processing triggers for libc-bin (2.19-0ubuntu6.7) ...
fede-mint-0 ~ # maxadmin -uadmin -pmariadb
MaxScale> show servers
Server 0x1b7a310 (server1)
	Server:                              127.0.0.1
	Status:                              Auth Error, Down
	Protocol:                    MySQLBackend
	Port:                                3306
	Node Id:                     -1
	Master Id:                   -1
	Slave Ids:                   
	Repl Depth:                  -1
	Number of connections:               0
	Current no. of conns:                0
	Current no. of operations:   0

So, thanks MariaDB! I love software projects that listen to their community needs. This should be a lesson for another company – we all know who I am talking about.

Federico


by Federico at April 13, 2016 10:33 AM

April 12, 2016

Peter Zaitsev

Percona Live featured talk with Ying Qiang Zhang — What’s new in AliSQL: Alibaba’s branch of MySQL

Percona Live featured talk

Percona Live featured talkWelcome to the next Percona Live featured talk with Percona Live Data Performance Conference 2016 speakers! In this series of blogs, we’ll highlight some of the speakers that will be at this year’s conference, as well as discuss the technologies and outlooks of the speakers themselves. Make sure to read to the end to get a special Percona Live registration bonus!

In this Percona Live featured talk, we’ll meet Ying Qiang Zhang, Database Kernel Expert for the Alibaba Group. His talk will be What’s new in AliSQL — Alibaba’s branch of MySQL. This session introduces the Alibaba Group’s branch of the Oracle MySQL — AliSQL. In this session, we will learn about how AliSQL can support 140,000 order creations per second.  I had a chance to speak with Ying and learn a bit more about AliSQL:

PerconaGive me a brief history of yourself: how you got into database development, where you work, what you love about it.

Ying: My first step in my MySQL journey began in my graduate student period. I participated in lots of projects using MySQL as storage. At that time, I thought MySQL was a masterpiece of computer science theory and a well-demonstrated engineering implementation of a real RDBMS. By referencing MySQL source code, I solved many problems in my project.

Before joining the Alibaba group, I was a MySQL Kernel developer at Baidu Co., Ltd. I joined the Alibaba group in 2014 as the developer and maintainer of AliSQL, a MySQL fork of Alibaba.

The dramatic growth of Alibaba’s E-Commerce business puts extremely harsh demands on our database system. AliSQL faces bigger and bigger challenges. I like the challenges, and have tried my best to make AliSQL faster, safer and more scalable – which in turn makes our OLTP system more efficient and smooth.

Percona: Your talk is going to be on “What’s new in AliSQL – Alibaba’s branch of MySQL” So this was a version of MySQL that was put together specifically for the Alibaba group’s online commerce? What prompted that need and why a special MySQL implementation?

Ying: AliSQL is a fork of MySQL integrated with Alibaba’s business characteristics and requirements (based on a community version). The primary incentive of maintaining this fork are:

  1. As the largest E-Commerce platform in the world, the throughput of Alibaba’s online transaction processing system is huge, especially on days like Alibaba Singles’ Day shopping festival (China’s version of “Cyber Monday” or “Black Friday”). The databases behind the OLTP system faces the challenge of high throughput, high concurrency and low latency at the same time (requirements the community version of MySQL cannot meet).
  2. Under the high-stress scenarios, we found some MySQL bugs impact system stability. We couldn’t wait for new releases of the community version to fix these bugs. Usually we will have to fix the bugs with very limited time, and then we will report the bugs as well as the patch to community.
  3. In Alibaba, the differences between the responsibilities of an application developer and database administrator are significant. We have a very professional DBA team, and DBAs are well aware of the database system and need more features to manipulate MySQL: flow control, changing/controlling execution plan, controlling the watermark, setting blacklist without the involvement of application developer, etc. And the community version of MySQL lacks these features. The private cloud user needs these features even more than a public cloud user.

Percona: Are there differences in the online processing experience in China that are different than other countries? Especially for the Singles’ Day event?

Ying: China has huge population base and huge netizen base. With the rapid growth of China’s economy, the purchasing power of Chinese netizen is stronger and stronger. According to published data, Alibaba’s sales during Singles’ Day shopping festival 2014 was 8.99 billion USD, which was almost five times more than Cyber Monday or Black Friday’s online sales in the United States for the same year. In 2015, the amount reached 14.3 billion USD. Alibaba’s E-Commerce platform sales were one billion RMB at the first 1 minute and 12 seconds of Singles’ Day 2015. Millions of people are trying to buy the same commodity at the same time usually on that day. This is a huge challenge for the Alibaba’s online transaction processing system, and of course, for the databases sitting in the backend.

Percona: What do you see as an issue that we the database community needs to be on top of with AliSQL? What keeps you up at night with regard to the future of MySQL?

Ying: In my opinion, as an open source project MySQL has been focused on single users or the public cloud users (the “M” of “LAMP”). But with the growth of MySQL, we need to pay more attention to enterprise and big private cloud users. Some features such as administration, scalable cluster solutions, and performance optimizations for extreme scenarios are essential to enterprise users.

Percona: What are you most looking forward to at Percona Live Data Performance Conference 2016?

Ying: I am looking forward to communicating with MySQL users from all over the world, to see if we can help the community to grow even bigger with my two little cents. I am also looking forward to making more friends in the MySQL world.

You can read more about AliSQL at Ying’s website: zhangyingqiang.com.

Want to find out more about Ying and AliSQL? Register for Percona Live Data Performance Conference 2016, and see his talk What’s new in AliSQL — Alibaba’s branch of MySQL. Use the code “FeaturedTalk” and receive $100 off the current registration price!

Percona Live Data Performance Conference 2016 is the premier open source event for the data performance ecosystem. It is the place to be for the open source community as well as businesses that thrive in the MySQL, NoSQL, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.

The Percona Live Data Performance Conference will be April 18-21 at the Hyatt Regency Santa Clara & The Santa Clara Convention Center.

by Dave Avery at April 12, 2016 08:04 PM

Jean-Jerome Schmidt

New Webinar Series: Become a MongoDB DBA (if you’re really a MySQL user)

So, maybe you’ve been working with MySQL for a while and are now being asked to also properly maintain one or more MongoDB instances. It is not uncommon that MySQL DBAs, developers, network/system administrators or DevOps folks with general backgrounds, find themselves in this situation at some point in time. In fact, with more organisations operating polyglot environments, it’s starting to become commonplace.

With that said, we’d like to introduce a new webinar series: ‘How to Become a MongoDB DBA’ to answer the question: ‘what does a MongoDB DBA do’?  

In the space of three webinars, we will walk you through the most important tasks a MongoDB DBA routinely goes through and provide you with options on how to best complete these tasks.

In this initial webinar of the series, we will go beyond the deployment phase and show you how you can automate tasks, how to monitor a cluster and how to manage MongoDB; whilst also automating and managing your MySQL and/or PostgreSQL installations.

Date, Time & Registration

Europe/MEA/APAC

Tuesday, April 26th at 09:00 BST / 10:00 CEST (Germany, France, Sweden)
Register Now

North America/LatAm

Tuesday, April 26th at 09:00 Pacific Time (US) / 12:00 Eastern Time (US)
Register Now

Agenda

  • Introduction to becoming a MongoDB DBA
  • Installing & configuring MongoDB
  • What to monitor and how
  • How to perform backups
  • Live Demo

Speaker

Art van Scheppingen is a Senior Support Engineer at Severalnines. He’s a pragmatic MySQL and Database expert with over 15 years experience in web development. He previously worked at Spil Games as Head of Database Engineering, where he kept a broad vision upon the whole database environment: from MySQL to Couchbase, Vertica to Hadoop and from Sphinx Search to SOLR. He regularly presents his work and projects at various conferences (Percona Live, FOSDEM) and related meetups.

We look forward to “seeing” you there!

This session is based upon the experience we have using MongoDB and implementing it for our database infrastructure management solution, ClusterControl. For more details, read through our ‘Become a ClusterControl DBA’ blog series.

by Severalnines at April 12, 2016 06:30 PM

Federico Razzoli

Comments on MaxScale binaries

I’m writing this post after reading Downloading MariaDB MaxScale binaries, from Percona’s MySQL Performance Blog.

I was already aware about the problem: MaxScale is open source, but the binaries are not free. You can download them and use them for testing, but if you want to use them in production, you’ll need to buy MariaDB Enterprise.

Note that MaxScale Docker images seem to have the same problem. I’ve tried some of them, but all those I’ve tried were running MariaDB Enterprise binaries, so using them in production is illegal (unless you pay).

The alternative is… compiling MaxScale. I had problems in doing so and couldn’t solve those problems myself. From the MaxScale Google Group, I see that Justin Swanhart had the same problems… so I don’t feel particularly stupid for that.

After some questions on that group, 2 posts were published on MariaDB blog:

When I tried them, the first one worked for me, while the former didn’t.

But in any case, even if you are able to compile MaxScale on your Linux/BSD of choice, updates will be a problem. You will need to compile all next releases of MaxScale, which is simply not a viable solution for many companies.

This prevents MaxScale from being as widely used as it could be. The lack of free binaries is a problem. I understand their commercial choice – it is legit, but I don’t agree. First, because open source shouldn’t work in this way. Second, because the lack of free binaries could bring some customers to them, but… most probably, it simply prevents lots of people from trying MaxScale at all.

This would be negative for any software project, in my opinion. But I think that it is particularly negative for MaxScale. Why? Because it is amazingly versatile and writing a new module is amazingly simple. This combination of characteristics would make it greatly benefit from being widely adopted – people could write new modules and distribute them.

Please, MariaDB, reconsider this choice.

Federico


by Federico at April 12, 2016 06:19 PM

MariaDB AB

MaxScale 1.4.1 GA is available for download

Johan

We are pleased to announce that MaxScale 1.4.1 GA is now available for download!

If MaxScale is new to you, we recommend reading this page first.

MaxScale 1.4 brings:

  1. The Firewall Filter has been extended and can now be used for either black-listing or white-listing queries. In addition it is capable of logging both queries that match and queries that do not match.
  2. Client-side SSL has been available in MaxScale for a while, but it has been somewhat unreliable. We now believe that client side SSL is fully functional and usable.

Additional improvements:

  • POSIX Extended Regular Expression Syntax can now be used in conjunction with qlafilter, topfilter and namedserverfilter.
  • Improved user grant detection.
  • Improved password encryption.
  • Compared to the earlier 1.4.0 Beta release, a number of bugs have been fixed.

    The release notes can be found here and the binaries can be downloaded from the portal.

    In case you want to build the binaries yourself, the source can be found at GitHub, tagged with 1.4.1.

    We hope you will download and use this stable release, and we encourage you to create a bug report in Jira for any bugs you might encounter.

    On behalf of the entire MaxScale team.

    Tags: 

    About the Author

    Johan's picture

    Johan Wikman is a senior developer working on MaxScale at MariaDB Corporation. 

    by Johan at April 12, 2016 04:06 PM

    Peter Zaitsev

    Is Adaptive Hash Index in InnoDB right for my workload?

    adaptive hash index in InnoDBThis blog post will discuss what the Adaptive Hash Index in InnoDB is used for, and whether it is a good fit for your workload.

    Adaptive Hash Index (AHI) is one of the least understood features in InnoDB. In theory, it magically determines when it is worth supplementing InnoDB B-Tree-based indexes with fast hash lookup tables and then builds them automatically without a prompt from the user.

    Since AHI is supposed to work “like magic,” it has very little configuration available. In the early versions there were no configuration options available at all. Later versions added

    innodb_adaptive_hash_index
      to disable AHI if required (by setting it to “0” or “OFF”). MySQL 5.7 added the ability to partition AHI by enabling
    innodb_adaptive_hash_index_parts.
      (FYI, this feature existed in Percona Server as
    innodb_adaptive_hash_index_partitions
     since version 5.5.)

    To understand AHI’s impact on performance, think about it as if it were a cache. If an AHI “Hit” happens, we have much better lookup performance; if it is an AHI “Miss,” then performance gets slightly worse (as checking a hash table for matches is fast, but not free).

    This is not the only part of the equation though. In addition to the cost of lookup, there is also the cost of AHI maintenance. We can compare maintenance costs – which can be seen in terms of rows added to and removed from AHI – to successful lookups. A high ratio means a lot of lookups sped up at the low cost. A low ratio means the opposite: we’re probably paying too much maintenance cost for little benefit.

    Finally there is also a cost for adding an extra contention. If your workload consists of lookups to a large number of indexes or tables, you can probably reduce the impact by setting 

    innodb_adaptive_hash_index_parts
      appropriately. If there is a hot index, however, AHI could become a bottleneck at high concurrency and might need to be disabled.

    To determine if AHI is likely to help my workload, we should verify that the AHI hit and successful lookups to maintenance operations ratios are as high as possible.

    Let’s investigate what really happens for some simple workloads. I will use a basic Sysbench Lookup by the primary key – the most simple workload possible. We’ll find that even in this case we’ll find a number of behaviors.

    For this test, I am using MySQL 5.7.11 with a 16GB buffer pool. The base command line for sysbench is:

    sysbench --test=/usr/share/doc/sysbench/tests/db/select.lua   --report-interval=1 --oltp-table-size=1 --max-time=0 --oltp-read-only=off --max-requests=0 --num-threads=1 --rand-type=uniform --db-driver=mysql --mysql-password=password --mysql-db=test_innodb  run

    Looking up a single row

    Notice

    oltp-table-size=1
      from above; this is a not a mistake, but tests how AHI behaves in a very basic case:

    oltp table size=1

    And it works perfectly: there is a 100% hit ratio with no AHI maintenance operations to speak of.

    10000 rows in the table

    When we change the OLTP table setting to

    oltp-table-size=10000
     , we get the following picture:

    oltp table size=10k

    oltp table size=10k-2

    Again, we see almost no overhead. There is a rare incident of 16 rows or so being added to AHI (probably due to an AHI hash collision). Otherwise, it’s almost perfect.

    10M rows in the table

    If we change the setting to

    oltp-table-size=10000000
    , we now have more data (but still much less than buffer pool size):

    oltp table size=10m

    oltp table size=10m-2

    In this case, there is clearly a warm-up period before we get close to the 100% hit ratio – and it never quite hits 100% (even after a longer run). In this case, maintenance operations appear to keep going without showing signs of asymptotically reaching zero. My take on this is that with 10M rows there is a higher chance of hash collisions – causing more AHI rebuilding.

    500M rows in the table, uniform distribution

    Let’s now set the OLTP table size as follows:

    oltp-table-size=500000000
    . This will push the data size beyond the Innodb buffer pool size.

    oltp table size=500m

    oltp table size=500m-2

    Here we see a lot of buffer pool misses, causing the a very poor AHI hit ratio (never reaching 1%).   We can also see a large overhead of tens of thousands of rows added/removed from AHI. Obviously, AHI is not adding any value in this case

    500M rows, Pareto distribution

    Finally, let’s use the setting

    oltp-table-size=500000000
    , and add
    --rand-type=pareto
    . The
    --rand-type=pareto
     setting enables a skewed distribution, a more typical scenario for many real life data access patterns.

    oltp table size=500m-Pareto

    oltp table size=500m-Pareto-2

    In this case we see the AHI hit ratio gradually improving, and reaching close to 50%. The  AHI maintenance overhead is going down, but never reaches anything that suggests it is worth it.

    It is important to note in both this and the previous case that AHI has not reached a “steady state” yet. A steady state condition shows the number of rows added and removed becoming close to equal.

    As you can see from the math in the workloads shown above, the Adaptive Hash Index in InnoDB “magic” doesn’t always happen! There are cases when AHI is indeed helpful, and then there are others when AHI adds a lot of data structure maintenance overhead and takes memory away from buffer pool – not to mention the contention overhead. In these cases, it’s better that AHI is disabled.

    Unfortunately, AHI does not seem to have the logic built-in to detect if there is too much “churn” going on to make maintaining AHI worthwhile.

    I suggest using these numbers as a general guide to decide whether AHI is likely to benefit your workload. Make sure to run a test/benchmark to be sure.

    Interested in learning more about other InnoDB Internals? Please join me for the Innodb Architecture and Performance Optimization Tutorial at Percona Live!

    by Peter Zaitsev at April 12, 2016 02:26 PM

    April 11, 2016

    Peter Zaitsev

    Dealing with Jumbo Chunks in MongoDB

    Jumbo Chunks in MongoDB

    Jumbo Chunks in MongoDBIn this blog post, we will discuss how to deal with jumbo chunks in MongoDB.

    You are a MongoDB DBA, and your first task of the day is to remove a shard from your cluster. It sounds scary at first, but you know it is pretty easy. You can do it with a simple command:

    db.runCommand( { removeShard: "server1_set6" } )

    MongoDB then does its magic. It finds the chunks and databases and balances them across all other servers. You can go to sleep without any worry.

    The next morning when you wake up, you check the status of that particular shard and you find the process is stuck:

    "msg" : "draining ongoing",
    "state" : "ongoing",
    "remaining" : {
    "chunks" : NumberLong(3),
    "dbs" : NumberLong(0)

    There are three chunks that for some reason haven’t been migrated, so the

    removeShard
     command is stalled! Now what do you do?

    Find chunks that cannot be moved

    We need to connect to mongos and check the catalog:

    mongos> use config
    switched to db config
    mongos> db.chunks.find({shard:"server1_set6"})

    The output will show three chunks, with minimum and maximum _id keys, along with the namespace where they belong. But the last part of the output is what we really need to check:

    {
    [...]
    "min" : {
    "_id" : "17zx3j9i60180"
    },
    "max" : {
    "_id" : "30td24p9sx9j0"
    },
    "shard" : "server1_set6",
    "jumbo" : true
    }

    So, the chunk is marked as “jumbo.” We have found the reason the balancer cannot move the chunk!

    Jumbo chunks and how to deal with them

    So, what is a “jumbo chunk”? It is a chunk whose size exceeds the maximum amount specified in the

    chunk size
     configuration parameter (which has a default value of 64 MB). When the value is greater than the limit, the balancer won’t move it.

    The way to remove the flag from that those chunks is to manually split them. There are two ways to do it:

    1. You can specify at what point to split the chunk, specifying the corresponding _id value. To do this, you really need to understand how your data is distributed and what the settings are for min and max in order to select a good splitting point.
    2. You can just tell MongoDB to split it by half, letting it decide which is the best possible _id. This is easier and less error prone.

    To do it manually, you need to use

    sh.splitAt()
    . For example:

    sh.splitAt("dbname", { _id: "19fr21z5sfg2j0" })

    In this command, you are telling MongoDB to split the chunk in two using that _id as the cut point.

    If you want MongoDB to find the best split point for you, use the 

    sh.splitFind()
     command. In this particular case, you only need to specify a key (any key) that is part of the chunk you want to split. MongoDB will use that key to find that particular chunk, and then divide it into two parts using the _id that sits in the middle of the list.

    sh.splitFind("dbname", { _id : "30td24p9sx9j0" })

    Once the three chunks have been split, the jumbo flag is removed and the balancer can move them to a different server.

    removeShard
     will complete the process and you can drink a well-deserved coffee.

    by Miguel Angel Nieto at April 11, 2016 10:39 PM

    Downloading MariaDB MaxScale binaries

    Downloading MariaDB MaxScale binaries

    Downloading MariaDB MaxScale binariesIn this blog post we’ll discuss a caveat when downloading MariaDB MaxScale binaries.

    Following the previous performance results in my last two posts on sysbench and primary keys (https://www.percona.com/blog/2016/04/07/mysql-5-7-sysbench-oltp-read-results-really-faster/ and https://www.percona.com/blog/2016/03/28/mysql-5-7-primary-key-lookup-results-is-it-really-faster/), I wanted to measure overhead from proxies servers like ProxySQL and MaxScale.

    Unfortunately, I found that MaxScale binaries are not available without registering on the MariaDB.com portal. That in itself isn’t a bad thing, but to complete the registration you need to agree to an Evaluation Agreement. The agreement requests you comply with MariaDB Enterprise Terms and Conditions (you can find the text of the agreement here: MariaDB_Enterprise_Subscription_Agreement_US_v14_0).

    Personally, I don’t agree with MariaDB’s “Evaluation Agreement” or the “MariaDB Enterprise Terms and Conditions,” so it left me without binaries!

    In general, I strongly advise you to carefully read both documents – or, even better, ask your legal team if you can accept MariaDB’s “Evaluation Agreement.”

    Fortunately, MaxScale’s source code is available from https://github.com/mariadb-corporation/MaxScale. I had to build binaries myself, which I will share with you in this post! You can get MaxScale 1.4.1 binaries here https://www.percona.com/downloads/TESTING/MaxScale/. No “Evaluation Agreement” needed!

    I will follow up in a future post with my proxies testing results.

    by Vadim Tkachenko at April 11, 2016 07:13 PM

    April 08, 2016

    Peter Zaitsev

    MySQL Data at Rest Encryption

    MySQL Data at Rest Encryption

    MySQL Data at Rest EncryptionThis blog post will discuss the issues and solutions for MySQL Data at Rest encryption.

    Data at Rest Encryption is not only a good-to-have feature, but it is also a requirement for HIPAA, PCI and other regulations.

    There are three major ways to solve data encryption at rest:

    1. Full-disk encryption
    2. Database-level (table) encryption
    3. Application-level encryption, where data is encrypted before being inserted into the database

    I consider full disk encryption to be the weakest method, as it only protects from someone physically removing the disks from the server. Application-level encryption, on the other hand, is the best: it is the most flexible method with almost no overhead, and it also solves data in-flight encryption. Unfortunately, it is not always possible to change the application code to support application-level encryption, so database-level encryption can be a valuable alternative.

    Sergei Golubchik, Chief Architect at MariaDB, outlined the pluses and minuses of database level encryption during his session at Percona Live Amsterdam:

    Pros

    • Full power of DBMS is available
    • Full power of DBMS is availableEasy to implement
    • Easy to implementOnly database can see the data
    • Only databases can see the dataPer-table encryption, per-table keys, performance
    • Per-table encryption, per-table keys, performanceCannot be done per-user

    Cons

    • Cannot be done per-user
    • Does not protect against malicious root user

    Data at Rest Encryption: Database-Level Options

    Currently, there are two options for data at rest encryption at the database level:

    MariaDB’s implementation is different from MySQL 5.7.11. MySQL 5.7.11 only encrypts InnoDB tablespace(s), while MariaDB has an option to encrypt undo/redo logs, binary logs/relay logs, etc. However, there are some limitations (especially together with Galera Cluster):

    • No key rotation in the open source plugin version (MySQL 5.7.11 has a key rotation)
    • mysqlbinlog does not work with encrypted binlogs (bug reported)
    • Percona XtraBackup does not work, so we are limited to RSYNC as SST method for Galera Cluster, which is a blocking method (one node will not be available for writes during the SST). The latest Percona XtraBackup works with MySQL 5.7.11 tablespace encryption
    • The following data is not encrypted (bug reported)
      • Galera gcache + Galera replication data
      • General log / slow query log

    Database level encryption also has its weakness:

    1. Root and MySQL users can read the keyring file, which defeats the purpose. However, it is possible to place a key on the mounted drive and unmount it when MySQL starts (that can be scripted). The downside of this is that if MySQL crashes, it will not be restarted automatically without human intervention.
    2. Both MariaDB version and MySQL version only encrypt data when writing to disk – data is not encrypted in RAM, so a root user can potentially attach to MySQL with gdb/strace or other tools and read the server memory. In addition, with gdb it is possible to change the root user password structure and then use mysqldump to copy data. Another potential method is to kill MySQL and start it with skip-grant-tables. However, if the key is unmounted (i.e., on USB drive), MySQL will either not start or will not be able to read the encrypted tablespace.

    MariaDB Encryption Example

    To enable the full level encryption we can add the following options to my.cnf:

    [mysqld]
    plugin-load-add=file_key_management.so
    file_key_management_filekey = FILE:/mount/keys/mysql.key
    file-key-management-filename = /mount/keys/mysql.enc
    innodb-encrypt-tables = ON
    innodb-encrypt-log = 1
    innodb-encryption-threads=1
    encrypt-tmp-disk-tables=1
    encrypt-tmp-files=1
    encrypt-binlog=1
    file_key_management_encryption_algorithm = AES_CTR

    After starting MariaDB with those settings, it will start encrypting the database in the background. The file_key_management plugin is used; unfortunately, it does not support key rotation. The actual keys are encrypted with:

    # openssl enc -aes-256-cbc -md sha1 -k <key> -in keys.txt -out mysql.enc

    The encryption <key> is placed in /mount/keys/mysql.key.

    After starting MySQL, we can unmount the “/mount/key” partition. In this case, the key will not be available and a potential hacker will not be able to restart MySQL with “–skip-grant-tables” option (without passwords). However, it also prevents normal restarts, especially SSTs (cluster full sync).

    Additional notes:

    1. Encryption will affect the compression ratio, especially for the physical backups (logical backups, i.e. mysqldump does not matter as the data retrieved is not encrypted). If your original compressed backup size was only 10% of the database size, it will not be the case for the encrypted tables.
    2. Data is not encrypted in flight and will not be encrypted on the replication slaves unless you enable the same options on the slaves. The encryption is also local to the server, so when encryption was just enabled on a server some tables may not be encrypted yet (but will be eventually)
    3. To check which tables are encrypted, use the Information Schema INNODB_TABLESPACES_ENCRYPTION table, which contains encryption information. To find all tables that are encrypted, use this query:
      select * from information_schema.INNODB_TABLESPACES_ENCRYPTION where ENCRYPTION_SCHEME=1

    MySQL 5.7 Encryption Example

    To enable encryption, add the following option to my.cnf:

    [mysqld]
    early-plugin-load=keyring_file.so
    keyring_file_data=/mount/mysql-keyring/keyring

    Again, after starting MySQL we can unmount the “/mount/mysql-keyring/” partition.

    To start encrypting the tables, we will need to run

    alter table table_name encryption='Y'
     , as MySQL will not encrypt tables by default.

    The latest Percona Xtrabackup also supports encryption, and can backup encrypted tables.

    To find all encrypted tablespaces in MySQL/Percona Server 5.7.11, we can use information_schema.INNODB_SYS_TABLESPACES and the flag field. For example, to find normally encrypted tables, use the following query:

    mysql> select * from information_schema.INNODB_SYS_TABLESPACES where flag = 8225G
    *************************** 1. row ***************************
             SPACE: 4688
              NAME: test/t1
              FLAG: 8225
       FILE_FORMAT: Barracuda
        ROW_FORMAT: Dynamic
         PAGE_SIZE: 16384
     ZIP_PAGE_SIZE: 0
        SPACE_TYPE: Single
     FS_BLOCK_SIZE: 4096
         FILE_SIZE: 98304
    ALLOCATED_SIZE: 98304
    *************************** 2. row ***************************
             SPACE: 4697
              NAME: sbtest/sbtest1_enc
              FLAG: 8225
       FILE_FORMAT: Barracuda
        ROW_FORMAT: Dynamic
         PAGE_SIZE: 16384
     ZIP_PAGE_SIZE: 0
        SPACE_TYPE: Single
     FS_BLOCK_SIZE: 4096
         FILE_SIZE: 255852544
    ALLOCATED_SIZE: 255856640
    2 rows in set (0.00 sec)

    You can also use this query instead:

    select * from information_schema.tables where CREATE_OPTIONS like '%ENCRYPTION="Y"%';
    .

    Performance overhead

    This is a debatable topic, especially for the MariaDB implementation when everything is configured to be encrypted. During my tests I’ve seen ~10% of overhead for the standalone MySQL instance, and ~20% with Galera Cluster.

    The MySQL 5.7/Percona Server 5.7 tablespace-level encryption shows an extremely low overhead, however, that needs to be tested in different conditions.

    Conclusion

    Even with all the above limitations, database-level encryption can be a better option than the filesystem-level encryption if the application can not be changed. However, it is a new feature (especially MySQL 5.7.11 version) and I expect a number of bugs here.

    by Alexander Rubin at April 08, 2016 07:29 PM

    The first two Database Performance Team characters are . . .

    Database Performance Team

    team-shadow-aAnd the first two Database Performance Team characters are . . .

    The last blog introduced the Database Performance Team. The Database Performance Team is comprised of our services experts, who work tirelessly every day to guarantee the performance of your database. Percona’s services team is made up of superheroes that make sure your database is running at peak performance. Now we’re ready to reveal the identity of the first two team members.

    The first is funny, friendly, quick-witted, supporting, fast and courteous – but still able to get the job done with amazing competence. Who is this champion?

    support-bio-imageThe Maven
    Percona Support
    “How can we save you?”

    A helpful voice in a stressful situation, The Maven support team member is your-person Friday, with an easy manner and a knowing wink. The Maven puts you at your ease while solving your immediate problems.

    She can rescue your database, and do it with a smile.

     

     

    The second has computer-like smarts, instant recall, and is a counselor that can understand a problem and the solution quickly. Who is this champion?

    consulting-bio-imageThe Specialist

    Percona Consulting
    “No mission refused!”

    An ultra-brain, with deep knowledge of all aspects of technology, The Specialist can access minute and arcane details at a moment’s notice. For any mission, she is the one who can sharp shoot the answer under the toughest of circumstances. Put the Specialist on the case, and your problems will disappear.

     

     

    Follow @Percona on Twitter and use the hashtag #DatabasePerformanceTeam to cast your guess on our remaining three mystery characters. Correctly guess any of their names or roles, and the lucky winner gets their choice of our mystery T-shirt in either men’s or women’s style. Stay tuned, as we reveal the other identities of the Database Performance Team over the coming weeks! Respond with your guesses the remaining team members.

    Join the ranks of the Database Street Team! Fun games, cool prizes – more info is coming soon!

    Percona Live Update!

    See Bill Nye the Science Guy at Percona Live Data Performance Conference, and help an excellent cause! Bill Nye’s keynote speech at Percona Live is “Bill Nye’s Objective – Change the World!” Through his talks, books, and day job as the CEO of The Planetary Society (the world’s largest non-governmental space interest organization), Bill wants to get people involved in the power of science.

    And now you can help change the world, just by attending the Percona Live Data Performance Conference! For a limited time, if you buy a Keynote or Expo pass to Percona Live using the promo code “NYE” you will get the pass for just $10, AND all the money from these registrations will be donated to The Planetary Society.

    Come see Bill Nye at Percona Live, where you can also pickup these two awesome Database Performance Team t-shirts! Stay tuned as we reveal more team members!

    by Dave Avery at April 08, 2016 05:55 PM

    Federico Razzoli

    MariaDB/MySQL missing features: View comments

    The COMMENT clause

    All database components (and database themselves) should have a COMMENT clause in their CREATE/ALTER statements, and a *_COMMENT column in the information_schema. For example:

    CREATE PROCEDURE do_nothing()
            COMMENT 'We''re lazy. Let''s do nothing!'
    BEGIN
            DO NULL;
    END;
    SELECT ROUTINE_COMMENT FROM information_schema.ROUTINES;

    In fact most database objects have those clauses in MySQL/MariaDB, but not all. Views are an exception.

    Comments in code

    MariaDB and MySQL have multiple syntaxes for comments. Including executable comments (commented code that is only executed on some MySQL/MariaDB versions).

    One can use comments in stored procedures and triggers, and those codes are preserved:

    CREATE PROCEDURE do_nothing()
    BEGIN
            -- We're lazy. Let's do nothing!
            DO NULL;
    END;

    But, there are a couple problems:

    • This doesn’t work for views.
    • mysql client strips comments away, unless it’s started with --comments parameter. So, by default, procedures created with mysql have no comments.

    So…

    Views have no comment. No comments in metadata, no comments in code.

    This prevents us to create self-documenting databases. Even if names are self-documenting, we may still need to add notes like “includes sold-out products”, or “very slow”.

    Criticism makes us better

    As a final note, let me say that this post is not an attack against MariaDB or MySQL. It is criticism, yes, because I like MariaDB (and MySQL). Criticism helps, keeps projects alive, encourages discussions.

    To explain what I mean, I’ll show you a negative example. Recently I’ve attended a public talk from a LibreOffice Italia’s guy. It shown us a chart demonstrating that, according a generic “independent American study”, LibreOffice has no bug, while MS Office is full of bugs. The guy seems to think that software lint-like can automatically search for bugs. First I wondered how can LibreOffice survive with a community that is totally unable to produce criticism. Then I realized why it is the most buggy piece of software I’ve ever tried.

    Hiding problems is the safest way to make those problems persist.

    I’m happy that my favorite DBMS’s get the necessary amount of criticism.

    Federico


    by Federico at April 08, 2016 03:54 PM

    Colin Charles

    FOSDEM 2016 notes

    While being on the committee for the FOSDEM MySQL & friends devroom, I didn’t speak at that devroom (instead I spoke at the distributions devroom). But when I had time to pop in, I did take some notes on sessions that were interesting to me, so here are the notes. I really did enjoy Yoshinori Matsunobu’s session (out of the devroom) on RocksDB and MyRocks and I highly recommend you to watch the video as the notes can’t be very complete without the great explanation available in the slide deck. Anyway there are videos from the MySQL and friends devroom.

    MySQL & Friends Devroom

    MySQL Group Replication or how good theory gets into better practice – Tiago Jorge

    • Multi-master update everywhere with built-in automatic distributed recovery, conflict detection and group membership
    • Group replication added 3 PERFORMANCE_SCHEMA tables
    • If a server leaves the group, the others will be automatically informed (either via a crash or if you execute STOP GROUP REPLICATION)
    • Cloud friendly, and it is self-healing. Integrated with server core via a well-defined API. GTIDs, row-based replication, PERFORMANCE_SCHEMA. Works with MySQL Router as well.
    • Multi-master update everywhere. Conflicts will be detected and dealt with, via the first committer wins rule. Any 2 transactions on different servers can write to the same tuple.
    • labs.mysql.com / mysqlhighavailability.com
    • Q: When a node leaves a group, will it still accept writes? A: If you leave voluntarily, it can still accept writes as a regular MySQL server (this needs to be checked)
    • Online DDL is not supported
    • Checkout the video

    ANALYZE for statements – Sergei Petrunia

    • a lot like EXPLAIN ANALYZE (in PostgreSQL) or PLAN_STATISTICS (in Oracle)
    • Looks like explain output with execution statistics
    • slides and video

    Preparse Query Rewrite Plugins – Sveta Smirnova / Martin Hansson

    • martin.hansson@oracle.com
    • Query rewwriting with a proxy might be too complex, so they thought of doing it inside the server. There is a pre-parse (string-to-string) and a post-parse (parse tree) API. Pre-parse: low overhead, but no structure. Post-parse: retains structure, but requires re-parsing (no destructive editing), need to traverse parse tree and will only work on select statements
    • Query rewrite API builds on top of teh Audit API, and then you’ve got the pre-parse/post-parse APIs on the top that call out to the plugins
    • video

    Fedora by the Numbers – Remy DeCausemaker

    MyRocks: RocksDB Storage Engine for MySQL (LSM Databases at Facebook) – Yoshinori Matsunobu

    • SSD/Flash is getting affordable but MLC Flash is still expensive. HDD has large capacity but limited IOPS (reducing rw IOPS is very important and reducing write is harder). SSD/Flash has great read iops but limited space and write endurance (reducing space here is higher priority)
    • Punch hole compression in 5.7, it is aligned to the sector size of your device. Flash device is basically 4KB. Not 512 bytes. So you’re basically wasting a lot of space and the compression is inefficient
    • LSM tends to have a read penalty compared to B-Tree, like InnoDB. So a good way to reduce the read penalty is to use a Bloom Filter (check key may exist or not without reading data, and skipping read i/o if it definitely does not exist)
    • Another penalty is for delete. It puts them into tombstones. So there is the workaround called SingleDelete.
    • LSMs are ideal for write heavy applications
    • Similar features as InnoDB, transactions: atomicity, MVCC/non-locking consistent read, read committed repeatable read (PostgreSQL-style), Crash safe slave and master. It also has online backup (logical backup by mysqldump and binary backup by myrocks_hotbackup).
    • Much smaller space and write amplification compared to InnoDB
    • Reverse order index (Reverse Column Family). SingleDelete. Prefix bloom filter. Mem-comparable keys when using case sensitive collations. Optimizer statistics for diving into pages.
    • RocksDB is great for scanning forward but ORDER BY DESC queries are slow, hence they use reverse column families to make descending scan a lot faster
    • watch the video

    by Colin Charles at April 08, 2016 10:07 AM

    (tweet) Summary of Percona Live 2015

    The problem with Twitter is that we talk about something and before you know it, people forget. (e.g. does WebScaleSQL have an async client library?) How many blog posts are there about Percona Live Santa Clara 2015? This time (2016), I’m going to endeavour to write more than to just tweet – I want to remember this stuff, and search archives (and also note the changes that happen in this ecosystem). And maybe you do too as well. So look forward to more blogs from Percona Live Data Performance Conference 2016. In the meantime, here’s tweets in chronological order from my Twitter search.

    • crowd filling up the keynote room for #perconalive
    • beginning shortly, we’ll see @peterzaitsev at #perconalive doing his keynote
    • #perconalive has over 1,200 attendees – oracle has 20 folk, with 22 folk from facebook
    • #perconalive is going to be in Amsterdam sept 21-22 2015 (not in London this year). And in 2015, April 18-21 2016!
    • We have @PeterZaitsev on stage now at #perconalive
    • 5 of the 5 top websites are powered by MySQL – an Oracle ad – alexa rankings? http://www.alexa.com/topsites #perconalive
    • now we have Harrison Fisk on ployglot persistence at facebook #perconalive
    • make it work / make it fast / make it efficient – the facebook hacker way #perconalive
    • a lot of FB innovation goes into having large data sizes with short query time response #perconalive
    • “small data” to facebook? 10’s of petabytes with <5ms response times. and yes, this all sits in mysql #perconalive
    • messages eventually lands in hbase for long term storage for disk #perconalive they like it for LSM
    • Harrison introduces @RocksDB to be fast for memory/flash/disk, and its also LSM based. Goto choice for 100’s of services @ FB #perconalive
    • Facebook Newsfeed is pulled from RocksDB. 9 billion QPS at peak! #perconalive
    • Presto works all in memory on a streaming basis, whereas Hive uses map/reduce. Queries are much faster in Presto #perconalive
    • Scuba isn’t opensource – real time analysis tool to debug/understand whats going on @ FB. https://research.facebook.com/publications/456106467831449/scuba-diving-into-data-at-facebook/ … #perconalive
    • InnoDB as a read-optimized store and RocksDB as a write-optimized store — so RocksDB as storage engine for MySQL #perconalive
    • Presto + MySQL shards is something else FB is focused on – in production @ FB #perconalive
    • loving the woz keynote @ #perconalive – wondering if like apple keynotes, we’ll see a “one more thing” after this ;)
    • “i’m only a genius at one thing: that’s making people think i’m a genius” — steve wozniak #perconalive
    • Happiness = Smiles – Frowns (H=S-F) & Happiness = Food, Fun, Friends (H=F³) Woz’s philosophy on being happy + having fun daily #perconalive
    • .@Percona has acquired @Tokutek in a move that provides some consolidation in the MySQL database market and takes..
    • MySQL Percona snaps up Tokutek to move onto MongoDB and NoSQL turf http://zd.net/1ct6PEI by @wolpe
    • One more thing – congrats @percona @peterzaitsev #perconalive Percona has acquired Tokutek with storage engines for MySQL & MongoDB – @PeterZaitsev #perconalive
    • Percona is now a player in the MongoDB space with TokuMX! #perconalive
    • The tokumx mongodb logo is a mongoose… #perconalive Percona will continue to support TokuDB/TokuMX to customers + new investments in it
    • @Percona “the company driving MySQL today” and “the brains behind MySQL”. New marketing angle? http://www.datanami.com/2015/04/14/mysql-leader-percona-takes-aim-at-mongodb/ …
    • We have Steaphan Greene from @facebook talk about @WebScaleSQL at #perconalive
    • what is @webscalesql? its a collaboration between Alibaba, Facebook, Google, LinkedIn, and Twitter to hack on mysql #perconalive
    • close collaboration with @mariadb @mysql @percona teams on @webscalesql. today? upstream 5.6.24 today #perconalive
    • whats new in @WebScaleSQL ? asynchronous mysql client, with support from within HHVM, from FB & LinkedIn #perconalive
    • smaller @webscalesql change (w/big difference) – lower innodb buffer pool memory footprint from FB & Google #perconalive
    • reduce double-write mode while still preserving safety. query throttling, server side statement timeouts, threadpooling #perconalive
    • logical readahead to make full table scans as much as 10x fast. @WebScaleSQL #perconalive
    • whats coming to @WebScaleSQL – online innodb defragmentation, DocStore (JSON style document database using mysql) #perconalive
    • MySQL & RocksDB coming to @WebScaleSQL thanks to facebook & @MariaDB #perconalive
    • So, @webscalesql will skip 5.7 – they will backport interesting features into the 5.6 branch! #perconalive
    • likely what will be next to @webscalesql ? will be mysql-5.8, but can’t push major changes upstream. so might not be an option #perconalive
    • Why only minor changes from @WebScaleSQL to @MySQL upstream? #perconalive
    • Only thing not solved with @webscalesql & upstream @mysql – the Contributor license agreement #perconalive
    • All @WebScaleSQL features under Apache CCLA if oracle can accept it. Same with @MariaDB @percona #perconalive
    • Steaphan Greene says tell Oracle you want @webscalesql features in @mysql. Pressure in public to use the Apache CLA! #perconalive
    • We now have Patrik Sallner CEO from @MariaDB doing the #perconalive keynote ==> 1+1 > 2 (the power of collaboration)
    • “contributors make mariadb” – patrik sallner #perconalive
    • Patrik Sallner tells the story about the CONNECT storage engine and how the retired Olivier Bertrand writes it #perconalive
    • Google contributes table/tablespace encryption to @MariaDB 10.1 #perconalive
    • Patrik talks about the threadpool – how #MariaDB made it, #Percona improved it, and all benefit from opensource development #perconalive
    • and now we have Tomas Ulin from @mysql @oracle for his #perconalive keynote
    • 20 years of MySQL. 10 years of Oracle stewardship of InnoDB. 5 years of Oracle stewardship of @MySQL #perconalive
    • Tomas Ulin on the @mysql 5.7 release candidate. It’s gonna be a great release. Congrats Team #MySQL #perconalive
    • MySQL 5.7 has new optimizer hint frameworks. New cost based optimiser. Generated (virtual) columns. EXPLAIN for running thread #perconalive
    • MySQL 5.7 comes with the query rewrite plugin (pre/post parse). Good for ORMs. “Eliminates many legacy use cases for proxies” #perconalive
    • MySQL 5.7 – native JSON datatypes, built-in JSON functions, JSON comparator, indexing of documents using generated columns #perconalive
    • InnoDB has native full-text search including full CJK support. Does anyone know how FTS compares to MyISAM in speed? #perconalive
    • MySQL 5.7 group replication is unlikely to make it into 5.7 GA. Designed as a plugin #perconalive
    • Robert Hodges believes more enterprises will use MySQL thanks to the encryption features (great news for @mariadb) #perconalive
    • Domas on FB Messenger powered by MySQL. Goals: response time, reliability, and consistency for mobile messaging #perconalive
    • FB Messenger: Iris (in-memory pub-sub service – like a queue with cache semantics). And MySQL as persistence layer #perconalive
    • FB focuses on tiered storage: minutes (in memory), days (flash) and longterm (on disks). #perconalive
    • Gotta keep I/O devices for 4-5 years, so don’t waste endurance capacity of device (so you don’t write as fast as a benchmark) #perconalive
    • Why MySQL+InnoDB? B-Tree: cheap overwrites, I/O has high perf on flash, its also quick and proven @ FB #perconalive
    • What did FB face as issues to address with MySQL? Write throughput. Asynchronous replication. and Failover time. #perconalive
    • HA at Facebook: <30s failover, <1s switchover, > 99.999% query success rate
    • Learning a lot about LSM databases at Facebook from Yoshinori Matsunobu – check out @rocksdb + MyRocks https://github.com/MySQLOnRocksDB/mysql-5.6 …
    • The #mysqlawards 2015 winners #PerconaLive
    • Percona has a Customer Advisory Board now – Rob Young #perconalive
    • craigslist: mysql for active, mongodb for archives. online alter took long. that’s why @mariadb has https://mariadb.com/kb/en/mariadb/progress-reporting/ … #perconalive
    • can’t quite believe @percona is using db-engines rankings in a keynote… le sigh #perconalive
    • “Innovation distinguishes between a leader and a follower” – Steve Jobs #perconalive
    • Percona TokuDB: “only alternative to MySQL + InnoDB” #perconalive
    • “Now that we have the rights to TokuDB, we can add all the cool features ontop of Percona XtraDB Cluster (PXC)” – Rob Young #perconalive
    • New Percona Cloud Tools. Try it out. Helps remote DBA/support too. Wonder what the folk at VividCortex are thinking about now #perconalive
    • So @MariaDB isn’t production ready FOSS? I guess 3/6 top sites on Alexa rank must disagree #perconalive
    • Enjoying Encrypting MySQL data at Google by @jeremycole & Jonas — you can try this in @mariadb 10.1.4 https://mariadb.com/kb/en/mariadb/mariadb-1014-release-notes/ … #perconalive
    • google encryption: mariadb uses the api to have a plugin to store the keys locally; but you really need a key management server #perconalive
    • Google encryption: temporary tables during query execution for the Aria storage engine in #MariaDB #perconalive
    • find out more about google mysql encryption — https://code.google.com/p/google-mysql/ or just use it at 10.1.4! https://downloads.mariadb.org/mariadb/10.1.4/ #perconalive
    • Encrypting MySQL data at Google – Percona Live 2015 #perconalive http://wp.me/p5WPkh-5F
    • The @WebScaleSQL goals are still just to provide access to the code, as opposed to supporting it or making releases #perconalive
    • There is a reason DocStore & Oracle/MySQL JSON 5.7 – they were designed together. But @WebScaleSQL goes forward with DocStore #perconalive
    • So @WebScaleSQL will skip 5.7, and backport things like live resize of the InnoDB buffer pool #perconalive
    • How to view @WebScaleSQL? Default GitHub branch is the active one. Ignore -clean branches, just reference for rebase #perconalive
    • All info you need should be in the commit messages @WebScaleSQL #perconalive
    • Phabricator is what @WebScaleSQL uses as a code review system. All diffs are public, anyone can follow reviews #perconalive
    • automated testing with jenkins/phabricator for @WebScaleSQL – run mtr on ever commit, proposed diffs, & every night #perconalive
    • There is feature documentation, and its a work in progress for @WebScaleSQL. Tells you where its included, etc. #perconalive
    • Checked out the new ANALYZE statement feature in #MariaDB to analyze JOINs? Sergei Petrunia tells all #perconalive https://mariadb.com/kb/en/mariadb/analyze-statement/ …

    by Colin Charles at April 08, 2016 09:44 AM

    April 07, 2016

    Peter Zaitsev

    MySQL 5.7 sysbench OLTP read-only results: is MySQL 5.7 really faster?

    MySQL Document Store

    MySQL 5.7 sysbench OLTP read-only resultsThis blog will look at MySQL 5.7 sysbench OLTP read-only results to determine if they are faster than previous versions.

    As promised in my previous post, I have checked MySQL 5.7 performance against previous versions in a different workload. This time, I will use sysbench OLTP read-only transactions (read-write transactions are part of future research, as there is more tuning required to get the best performance in write workloads).

    One important thing to mention is that MySQL 5.6 and 5.7 have special optimizations for READ-ONLY transactions. In MySQL 5.6, however,  you need to start a transaction with "START TRANSACTION READ ONLY" to get the optimization benefit. MySQL 5.7 automatically detects read-only transactions.

    I’ve modified the sysbench oltp.lua script to use "START TRANSACTION READ ONLY" for MySQL 5.6. This optimization is not available in MySQL 5.5.

    I also tried two different setups:

    • Local connections: the client (sysbench) and the server (mysqld) are running on the same server
    • Network connection: the client and server are connected by a 10GB network

    Other details

    • CPU: 56 logical CPU threads servers, Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
    • sysbench 10 tables x 10 million rows, Pareto distribution
    • OS: Ubuntu 15.10 (Wily Werewolf)
    • Kernel 4.2.0-30-generic

    More details with scripts and config files are available on our github.

    Summary results can also be found here:

    This post covers the most interesting highlights. First, the results on the local connections:

    Looking at these results, I was as surprised as you probably are. On a high number of threads and by a significantly visible margin, MySQL 5.7 is actually slower than MySQL 5.6.

    Let me show you the relative performance of MySQL 5.5 and MySQL 5.6 (having MySQL 5.7 as a baseline = 1.0):

    With a lower number of threads, MySQL 5.5 outperforms MySQL 5.7 by 8-15%, and on a higher number of threads MySQL 5.6 is better by 6-7%.

    To validate these findings, we can check the results on a remote connection. Here is a chart:

    This gives us a similar picture, with a couple of key differences. MySQL 5.6 encounters scalability problems sooner, and the throughput declines. The fix for that is using

    innodb-thread-concurrency=64
    .

    Here are the results:

    In this round, I did not test scenarios over 1000 threads. But gauging from the results above it seems that MySQL 5.7 has problems. It is interesting to consider how it will affect replication performance – and I will test this after my read-write benchmarks.

     

    by Vadim Tkachenko at April 07, 2016 02:26 PM

    April 06, 2016

    Peter Zaitsev

    11 Days Until Percona Live: Justification, Julian Cash, Sponsor List

    11 Days Until Percona LiveOnly 11 days until Percona Live! Are you registered?

    It’s getting close to the Percona Live Data Performance Conference 2016! The conference starts Monday, April 18th. We have some quick updates and pieces of information to pass on to you, so keep reading to find out the details.

    Need Help Justifying Your Attendance?

    Haven’t been able to justify going to Percona Live to your boss? Here is a link that will help you with that.

    Julian Cash X-RAY Light Painting Studio

    Don’t forget that Julian Cash will be setting up an X-RAY Light Painting Studio in the Exhibition Hall for your amazement and amusement. Light Painting Portraits are a rare and incredible art form that Julian has pioneered. His interactive artwork at Percona Live is an example of Julian’s vision, which also was featured on America’s Got Talent.

    He’s running a campaign to take Light Painting portraits in places where it would otherwise be impossible.  With your help, the studio will be equipped with the best technology imaginable, which will make for countless magical and fantastical images. Check it out!

     

    This Year’s Sponsors

    Our sponsors for Percona Live Data Performance Conference are set, and we want to thank them for helping us to put on this event. Below, you can see who sponsored Percona Live this year:

    • Diamond Plus Sponsor
      • Deep Information Science
      • RocksDB (Facebook)
    • Platinum
      • Rackspace
      • VividCortex
    • Gold
      • AWS
    • Silver
      • Yelp
      • Shopify
    • Exhibition Hall
      • Codership
      • Blackmesh
      • University of Michigan (DBSeer)
      • Vertabelo
      • Raintank (Grafana.net)
      • Red Hat
      • ScaleArc
      • SolarWinds
      • Pythian
      • AgilData
      • Box
      • Clustrix
      • MaxGauge
      • HGST
      • Severalnines
      • VMware
      • Eventbrite
      • MemSQL
    • Coffee Breaks
      • Mailchimp
    • Badge Lanyards and Conference Bags
      • Google
    • 50 Minute Breakout
      • Rackspace
      • Clustrix
    • Thirty-Minute Demo
      • Vertabelo
    • Data in the Cloud Track
      • Red Hat
      • Intel
    • Signage Sponsor
      • MONyog (Webyog)

    Thanks again to all of our sponsors, and all of our attendees. If you haven’t registered yet, do it now! There are only 11 days left until the conference!

    by Kortney Runyan at April 06, 2016 05:59 PM

    Percona Live: Advanced Percona XtraDB Cluster in a Nutshell, La Suite

    12 Days Until Percona Live

    PL16-Logo-Vert-Full-Opt2This blog post will discuss what is necessary of the Percona Live  Advanced Percona XtraDB Cluster tutorial.

    Percona Live 2016 is happening in April! If you are attending, and you are registered to the Percona XtraDB Cluster (Galera) tutorial presented by Kenny and myself, please make sure that you:

    • Bring your laptop, this is a hands-on tutorial
    • Have Virtual Box 5 installed
    • Bring a machine that supports 64bit VMs
    • Have at least 5GB of free disk space

    This advanced tutorial is a continuation of the beginners’ tutorial, so some basic experience with Percona XtraDB Cluster and Galera is required.

    See you soon!

    Description of the Percona Live Advanced Percona XtraDB Cluster Talk

    Percona XtraDB Cluster is a high availability and high scalability solution for MySQL clustering. Percona XtraDB Cluster integrates Percona Server with the Galera synchronous replication library in a single product package, which enables you to create a cost-effective MySQL cluster. For three years at Percona Live, we’ve introduced people to this technology – but what’s next?

    This tutorial continues your education and targets users that already have experience with Percona XtraDB Cluster and want to go further. This tutorial will cover the following topics:

    • Bootstrapping in details
    • Certification errors, understanding and preventing them
    • Replication failures, how to deal with them
    • Secrets of Galera Cache – Mastering flow control
    • Understanding and verifying replication throughput
    • How to use WAN replication
    • Implications of consistent reads
    • Backups
    • Load balancers and proxy protocol

    Register for Percona Live now!

    by Frederic Descamps at April 06, 2016 04:30 PM

    EXPLAIN FORMAT=JSON wrap-up

    EXPLAIN FORMAT=JSON

    EXPLAIN FORMAT=JSON wrap-upThis blog is an EXPLAIN FORMAT=JSON wrap-up for the series of posts I’ve done in the last few months.

    In this series, we’ve discussed everything unique to

    EXPLAIN FORMAT=JSON
    . I intentionally skipped a description of members such as
    table_name
    ,
    access_type
      or
    select_id
    , which are not unique.

    In this series, I only mentioned in passing members that replace information from the

    Extra
     column in the regular
    EXPLAIN
     output, such as
    using_join_buffer
     ,
    partitions
    ,
    using_temporary_table
      or simply
    message
    . You can see these in queries like the following:

    mysql> explain format=json select rand() from dual
    *************************** 1. row ***************************
    EXPLAIN: {
      "query_block": {
        "select_id": 1,
        "message": "No tables used"
      }
    }
    1 row in set, 1 warning (0.00 sec)

    Or

    mysql> explain format=json select emp_no from titles where 'Senior Engineer' = 'Senior Cat'
    *************************** 1. row ***************************
    EXPLAIN: {
      "query_block": {
        "select_id": 1,
        "message": "Impossible WHERE"
      }
    }
    1 row in set, 1 warning (0.01 sec)

    Their use is fairly intuitive, similar to regular

    EXPLAIN
    , and I don’t think one can achieve anything from reading a blog post about each of them.

    The only thing left to list is a Table of Contents for the series:

    attached_condition: How EXPLAIN FORMAT=JSON can spell-check your queries

    rows_examined_per_scan, rows_produced_per_join: EXPLAIN FORMAT=JSON answers on question “What number of filtered rows mean?”

    used_columns: EXPLAIN FORMAT=JSON tells when you should use covered indexes

    used_key_parts: EXPLAIN FORMAT=JSON provides insight into which part of multiple-column key is used

    EXPLAIN FORMAT=JSON: everything about attached_subqueries, optimized_away_subqueries, materialized_from_subquery

    EXPLAIN FORMAT=JSON provides insights on optimizer_switch effectiveness

    EXPLAIN FORMAT=JSON: order_by_subqueries, group_by_subqueries details on subqueries in ORDER BY and GROUP BY

    grouping_operation, duplicates_removal: EXPLAIN FORMAT=JSON has all details about GROUP BY

    EXPLAIN FORMAT=JSON has details for subqueries in HAVING, nested selects and subqueries that update values

    ordering_operation: EXPLAIN FORMAT=JSON knows everything about ORDER BY processing

    EXPLAIN FORMAT=JSON knows everything about UNIONs: union_result and query_specifications

    EXPLAIN FORMAT=JSON: buffer_result is not hidden!

    EXPLAIN FORMAT=JSON: cost_info knows why optimizer prefers one index to another

    EXPLAIN FORMAT=JSON: nested_loop makes JOIN hierarchy transparent

    Thanks for following the series!

    by Sveta Smirnova at April 06, 2016 02:23 PM

    Webinar April 7, 10am PDT – Introduction to Troubleshooting Performance: What Affects Query Execution?

    Query Execution

    Query ExecutionJoin us for our latest webinar on Thursday, April 7, at 10 am PDT (UTC-7) on Introduction to Troubleshooting Performance: What Affects Query Execution?

    MySQL installations experience a multitude of issues: server hangs, wrong data stored in the database, slow running queries, stopped replications, poor user connections and many others. It’s often difficult not only to troubleshoot these issues, but to even know which tools to use.

    Slow running queries, threads stacking for ages during peak times, application performance suddenly lagging: these are some of the things on a long list of possible database performance issues. How can you figure out why your MySQL installation isn’t running as fast as you’d like?

    In this introductory webinar, we will concentrate on the three main reasons for performance slowdown:

    • Poorly optimized queries
    • Concurrency issues
    • Effects of hardware and other system factors

    This webinar will teach you how to identify and fix these issues. Register now.

    If you can’t attend this webinar live, register anyway and we’ll send you a link to the recording.

    Sveta Smirnova, Principal Technical Services Engineer.

    Sveta joined Percona in 2015. Her main professional interests are problem-solving, working with tricky issues, bugs, finding patterns which can solve typical issues quicker, teaching others how to deal with MySQL issues, bugs and gotchas effectively. Before joining Percona Sveta worked as Support Engineer in MySQL Bugs Analysis Support Group in MySQL AB-Sun-Oracle. She is the author of the book “MySQL Troubleshooting” and JSON UDF functions for MySQL.

    by Dave Avery at April 06, 2016 12:49 AM

    April 05, 2016

    Jean-Jerome Schmidt

    Still Time for an Early Look at NinesControl (beta) for MySQL & MongoDB

    Your Database, Any Cloud - NinesControl (beta)

    As you may have seen, we recently announced NinesControl, a developer friendly service to deploy and manage MySQL and MongoDB clusters using your preferred Cloud Provider.

    There is still time for you to join and get an early look at NinesControl - just provide us with your email address, and we will be in touch to give you early access to the service.

    NinesControl users will be able to benefit from the following key features:

    • Provision a database on any cloud
    • Real-time unified monitoring
    • Automated database management and availability

    Sign up up to stay informed and apply for early access

    And see further details on NinesControl below in this blog.

    What is NinesControl?

    NinesControl is a new online service for developers. With a couple of simple steps, you can deploy and manage MySQL and MongoDB clusters on your prefered public cloud.

    Sign up up to stay informed and apply for early access

    Who is it for?

    NinesControl is specifically designed with developers in mind. It is currently in beta for DigitalOcean users, before we expand the service to other public cloud providers.

    How does NinesControl work?

    NinesControl is an online service that is fully integrated with DigitalOcean. Once you register for the service and provide your DigitalOcean “access key”, the service will launch droplets in your region of choice and provision database nodes on them.

    Sign up up to stay informed and apply for early access

    by Severalnines at April 05, 2016 08:42 PM

    Peter Zaitsev

    Percona Live featured talk with Anastasia Ailamaki — RAW: Fast queries on JIT databases

    Percona Live featured talk

    Percona Live featured talkWelcome to the next Percona Live featured talk with Percona Live Data Performance Conference 2016 speakers! In this series of blogs, we’ll highlight some of the speakers that will be at this year’s conference, as well as discuss the technologies and outlooks of the speakers themselves. Make sure to read to the end to get a special Percona Live registration bonus!

    In this Percona Live featured talk, we’ll meet Anastasia Ailamaki, Professor and CEO, EPFL and RAW Labs. Her talk will be RAW: Fast queries on JIT databases. RAW is a query engine that reads data in its raw format and processes queries using adaptive, just-in-time operators. The key insight is its use of virtualization and dynamic generation of operators. I had a chance to speak with Anastasia and learn a bit more about RAW and JIT databases:

    Percona: Give me a brief history of yourself: how you got into database development, where you work, what you love about it.

    Anastasia: I am a computer engineer and initially trained on networks. I came across databases in the midst of the object-oriented hype — and was totally smitten by both the power of data models and the wealth of problems one had to solve to create a functioning and performant database system. In the following years, I built several systems as a student and (later) as a coder. At some point, however, I needed to learn more about the machine. I decided to do a Masters in computer architecture, which led to a Ph.D. in databases and microarchitecture. I became a professor at CMU, where for eight years I guided students as they built their ideas into real systems that assessed their ideas potential and value. During my sabbatical at EPFL, I was fascinated by the talent and opportunities in Switzerland — I decided to stay and, seven years later, co-founded RAW Labs.

    Percona: Your talk is going to be on “RAW: Fast queries on JIT databases.” Would you say you’re an advocate of abandoning (or at least not relying on) the traditional “big structured database accessed by queries” model that have existed for most of computing? Why?

    Anastasia: The classical usage paradigm for databases has been “create a database, then ask queries.” Traditionally, “creating a database” means creating a structured copy of the entire dataset. This is now passé for the simple reason that data is growing too fast, and loading overhead grows with data size. What’s more, we typically use only a small fraction of the data available, and investing in the mass of owned data is a waste of resources — people have to wait too long from the time they receive a dataset until they can ask a query. And it doesn’t stop there: the users are asked to pick a database engine based on the format and intended use of the data. We associate row stores to transactions, NoSQL to JSON, and column stores to analytics, but true insight comes from combining all of the data semantically as opposed to structurally. With each engine optimizing for specific kinds of queries and data formats, analysts subconsciously factor in limitations when piecing together their infrastructure. We only know the best way to structure data when we see the queries, so loading data and developing query processing operators before knowing the queries is premature.

    Percona: What are the conditions that make JIT databases in general (and RAW specifically) the optimum solution?

    Anastasia: JIT databases push functionality to the last minute, and execute it right when it’s actually needed. Several systems perform JIT compilation of queries, which offer great performance benefits (an example is Hyper, a system recently acquired by Tableau). RAW is JIT on steroids: it leaves data at its source and only reads it or asks for any system resources when they’re actually required. You may have 10000 files, and a file will only be read when you ask a query that needs the data in it. With RAW, when the user asks a query the RAW code-generates raw source data adaptors and the entire query engine needed to run the query. It stores all useful information about the accessed data, as well as popular operators generated in the past, and uses them to accelerate future queries. It adapts to system resources on the fly and only asks for them when needed. RAW is an interface to raw data and operational databases, and uses them to accelerate future queries. It adapts to system resources on the fly and only asks for them when needed. In addition, the RAW query language is incredibly rich; it is a superset of SQL which allows navigation on hierarchical data and tables at the same time, with support for variable assignments, regular expressions, and more for log processing — while staying in declarative land. Therefore, the analysts only need to describe the desired result in SQL, without thinking of data format.

    Percona: What would you say in the next step for JIT and RAW? What keeps you up at night concerning the future of this approach?

    Anastasia: The next step for RAW is to reach out to as many people as possible — especially users with complex operational data pipelines — and reduce cost and eliminate pipeline stages, unneeded data copies, and extensive scripting. RAW is a new approach that can work with existing infrastructures in a non-intrusive way. We are well on our way with several proof-of-concept projects that create verticals for RAW, and demonstrate its usefulness for different applications.

    Percona: What are you most looking forward to at Percona Live Data Performance Conference 2016?

    Anastasia: I am looking forward to meeting as many users and developers as possible, hearing their feedback on RAW and our ideas, and learning from their experiences.

    You can read more about RAW and JIT databases at Anastasia’s academic group’s website: dias.epfl.ch.

    Want to find out more about Anastasia and RAW? Register for Percona Live Data Performance Conference 2016, and see her talk RAW: Fast queries on JIT databases. Use the code “FeaturedTalk” and receive $100 off the current registration price!

    Percona Live Data Performance Conference 2016 is the premier open source event for the data performance ecosystem. It is the place to be for the open source community as well as businesses that thrive in the MySQL, NoSQL, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.

    The Percona Live Data Performance Conference will be April 18-21 at the Hyatt Regency Santa Clara & The Santa Clara Convention Center.

    by Dave Avery at April 05, 2016 04:07 PM

    Data in the Cloud track at Percona Live with Brent Compton and Ross Turk: The Data Performance Cloud

    12 Days Until Percona Live

    Data in the Cloud Track at Percona LiveIn this blog, we’ll discuss the Data in the Cloud track at Percona Live with Red Hat’s Brent Compton and Ross Turk.

    Welcome to another interview with the Percona Live Data Performance Conference speakers and presenters. This series of blog posts will highlight some of the talks and presentations available at Percona Live Data Performance Conference April 18-21 in Santa Clara. Read through to the end for a discounts for Percona Live registration.

    (A webinar sneak preview of their “MySQL on Ceph” cloud storage talk is happening on Wednesday, April 6th at 2 pm EDT. You can register for it here – all attendees will receive a special $200 discount code for Percona Live registration after the webinar! See the end of this blog post for more details!)

    First, we need to establish some context. Data storage has traditionally, and for most of its existence, pretty much followed a consistent model: stable and fairly static big box devices that were purpose-built to house data. Needing more storage space meant obtaining more (or bigger) boxes. Classic scale-up storage. Need more, go to the data storage vendor and order a bigger box.

    The problem is that data is exploding, and has been exponentially for the last decade. Some estimates put the amount of data being generated worldwide increasing at a rate of 40%-60% per year. That kind of increase, and at that speed, doesn’t leave a lot of ramp up time to make long term big box hardware investments. Things are changing too fast.

    The immediate trend – evident by declining revenues of class storage boxes – is placing data in a cloud of scale-out storage. What is the cloud? Since that question has whole books devoted to it, let’s try to simplify it a bit.

    Cloud computing benefits include scalability, instantaneous configuration, virtualized consumables and the ability to quickly expand base specifications. Moving workloads to the cloud brings with it numerous business benefits, including agility, focus and cost:

    • Agility. The cloud enables businesses to react to changing needs. As the workload grows or spikes, just add compute cycles, storage, and bandwidth with the click of a mouse.
    • Focus. Deploying workloads to the cloud enables companies to focus more resources on business-critical activities, rather than system administration.
    • Cost. Businesses can pay as they go for the services level they need. Planning and sinking money into long-term plans that may or may not pan out is not as big a problem.

    When it comes to moving workloads into the cloud, the low throughput applications were the obvious first choice: email, non-critical business functions, team collaboration assistance. These generally are neither mission critical, nor require high levels of security. As applications driven services became more and more prevalent (think Netflix, Facebook, Instagram), more throughput intensive services were moved to the cloud – mainly for flexibility during service spikes and to accommodate increased users. But tried and true high-performance workloads like databases and other corporate kingdoms that have perceived higher security requirements have traditionally remained stuck in the old infrastructures that have served well – until now.

    So what is this all leading to? Well, according to Brent and Ross, ALL data will eventually be going to the cloud, and the old models of storage infrastructure are falling by the wayside. Between the lack of elasticity and scalability of purpose-built hardware, and the oncoming storage crisis, database storage is headed for cloud services solutions.

    I had some time to talk with Brent and Ross about data in the cloud, and what we can expect regarding a new data performance cloud model.

    Percona: There is always a lot of talk about public versus private paradigms when it comes to cloud discussions. To you, this is fairly inconsequential. How do see “the cloud?” How would you define it terms of infrastructure for workloads?

    RHT: Red Hat has long provided software for hybrid clouds, with the understanding that most companies will use a mix of public cloud and private cloud infrastructure for their workloads. This means that Red Hat software is supported both on popular public cloud platforms (such as AWS, Azure, and GCE) as well as on-premise platforms (such as OpenStack private clouds). Our work with Percona in providing a reference architecture for MySQL running on Ceph is all about giving app developers a comparable, deterministic experience when running their MySQL-based apps on a Ceph private storage cloud v. running them in the public cloud.

    Percona: So, your contention is that ALL data is headed to the cloud. What are the factors that are going ramp up this trend? What level of information storage will cement this as inevitable?

    RHT:  We’d probably restate this to “most data is headed to A cloud.” Two distinctions being made in this statement. The first is “most” versus “all” data.  For years to come, there will be late adopters with on-premise data NOT being served through a private cloud infrastructure. The second distinction is “a” cloud versus “the” cloud.  “A” cloud means either a public cloud or a private cloud (or some hybrid of the two). Private clouds are being constructed by the world’s most advanced companies within their own data centers to provide a similar type of elastic infrastructure with dynamic provisioning and lower CAPEX/OPEX costs (as is found in public clouds).

    Percona: What are the concerns you see with moving all workloads to the cloud, and how would you address those concerns?

    RHT:  The distinctions laid out in the previous answer address this. For myriad reasons, some data and workloads will reside on-premise within private clouds for a very long time. In fact, as the technology matures for building private clouds (as we’re seeing with OpenStack and Ceph), and can offer many of the same benefits as public clouds, we see the market reaching an equilibrium of sorts. In this equilibrium many of the agility, flexibility, and cost benefits once available only through public cloud services will be matched by private cloud installations. This will re-base the public versus private cloud discussion to fewer, simpler trade-offs – such as which data must reside on-premises to meet an enterprise’s data governance and control requirements.

    Percona: So you mentioned the “Data Performance Cloud”? How would you describe that that is, and how it affects enterprises?

    RHT:  For many enterprises, data performance workloads have been the last category of workloads to move a cloud, whether public or private. Public cloud services, such as AWS Relational Database Service with Provisioned-IOPS storage, have illustrated improved data performance for many workloads once relegated to the cloud sidelines. Now, with guidelines in the reference architecture being produced by Percona and the Red Hat Ceph team, customers can achieve comparable data performance on their private Ceph storage clouds as they do with high-performance public cloud services.

    Percona: What can people expect to get out of the Data in the Cloud track at Percona Live this year?

    RHT: Architecture guidelines for building and optimizing MySQL databases on a Ceph private storage cloud.   These architectures will include public cloud benefits along with private cloud control and governance.

    Want to find out more about MySQL, Ceph, and Data in the Cloud? Register for Percona Live Data Performance Conference 2016, and see Red Hat’s sponsored Data in the Cloud Keynote Panel: Cloudy with a chance of running out of disk space? Or Sunny times ahead? Use the code “FeaturedTalk” and receive $100 off the current registration price!

    The Percona Live Data Performance Conference is the premier open source event for the data performance ecosystem. It is the place to be for the open source community as well as businesses that thrive in the MySQL, NoSQL, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.

    The Percona Live Data Performance Conference will be April 18-21 at the Hyatt Regency Santa Clara & The Santa Clara Convention Center.

    MySQL and Ceph: Database-as-a-Service sneak preview

    Businesses are familiar with running a Database-as-a-Service (DBaaS) in the public cloud. They enjoy the benefits of on-demand infrastructure for spinning-up lots of MySQL instances with predictable performance, without the headaches of managing them on specific, bare-metal highly available clusters.

    This webinar lays the foundation for building a DBaaS on your own private cloud, enabled by Red Hat® Ceph Storage. Join senior architects from Red Hat and Percona for reference architecture tips and head-to-head performance results of MySQL on Ceph versus MySQL on AWS.

    This is a sneak preview of the labs and talks to be given in April 2016 at the Percona Live Data
    Performance Conference
    . Attendees received a discount code for $200 off Percona Live registration!

    Speakers:

    • Brent Compton, director, Storage Solution Architectures, Red Hat
    • Kyle Bader, senior solutions architect, Red Hat
    • Yves Trudeau, principal consultant, Percona

    Join the live event:

    Wednesday, April 6, 2016 | 2 p.m. ET | 11 a.m. PT

    Time zone converter

    by Dave Avery at April 05, 2016 12:03 AM