EmergencyEMERGENCY? Get 24/7 Help Now!

Percona Server 5.7.10-2 second RC available

Percona is glad to announce the second release candidate of Percona Server 5.7.10-2 on February 8, 2016. Download the latest version from the Percona web site or from the Percona Software Repositories.

New Features:

  • Complete list of changes between Percona Server 5.6 and 5.7 can be seen in Changed in Percona Server 5.7.
  • 5.7 binlog group commit algorithm is now supported in TokuDB as well.
  • New TokuDB index statistics reporting has been implemented to be compatible with the changes implemented in upstream 5.7. Following the InnoDB example, the default value for tokudb_cardinality_scale_percent has been changed from 50% to 100%. Implementing this also addresses a server crash deep in the optimizer code.

Known Issues:

  • In Percona Server 5.7 super_read_only feature has been replaced with the upstream implementation. There are currently two known issues compared to Percona Server 5.6 implementation:
    • Bug #78963, super_read_only aborts STOP SLAVE if variable relay_log_info_repository is set to TABLE which could lead to a server crash in Debug builds.
    • Bug #79328, super_read_only set as a server option has no effect.
  • InnoDB crash recovery might fail if innodb_flush_method is set to ALL_O_DIRECT. The workaround is to set this variable to a different value before starting up the crashed instance (bug #1529885).

Bugs Fixed:

  • Clustering secondary index could not be created on a partitioned TokuDB table. Bug fixed #1527730 (#720).
  • Percona TokuBackup was failing to compile with Percona Server 5.7. Bug fixed #123.
  • Granting privileges to a user authenticating with PAM Authentication Plugin could lead to a server crash. Bug fixed #1521474.
  • TokuDB status variables were missing from Percona Server 5.7.10-1. Bug fixed #1527364 (#923).
  • Attempting to rotate the audit log file would result in audit log file name foo.log.%u (literally) instead of a numeric suffix. Bug fixed #1528603.
  • Adding an index to an InnoDB temporary table while expand_fast_index_creation was enabled could lead to server assertion. Bug fixed #1529555.
  • TokuDB would not be upgraded on Debian/Ubuntu distributions while performing an upgrade from Percona Server 5.6 to Percona Server 5.7 even if explicitly requested. Bug fixed #1533580.
  • Server would assert when both TokuDB and InnoDB tables were used within one transaction on a replication slave which has binary log enabled and slave updates logging disabled. Bug fixed #1534249 (upstream bug #80053).
  • MeCab Full-Text Parser Plugin has not been included in the previous release. Bug fixed #1534617.
  • Fixed server assertion caused by Performance Schema memory key mix-up in SET STATEMENT ... FOR ... statements. Bug fixed #1534874.
  • Setting the innodb_sched_priority_purge (available only in debug builds) while purge threads were stopped would cause a server crash. Bug fixed #1368552.
  • Enabling TokuDB with ps_tokudb_admin script inside the Docker container would cause an error due to insufficient privileges even when running as root. In order for this script to be used inside docker containers this error has been changed to a warning that a check is impossible. Bug fixed #1520890.
  • Write-heavy workload with a small buffer pool could lead to a deadlock when free buffers are exhausted. Bug fixed #1521905.
  • InnoDB status will start printing negative values for spin rounds per wait, if the wait number, even though being accounted as a signed 64-bit integer, will not fit into a signed 32-bit integer. Bug fixed #1527160 (upstream #79703).
  • Percona Server 5.7 couldn’t be restarted after TokuDB has been installed with ps_tokudb_admin script. Bug fixed #1527535.
  • Fixed memory leak when utility_user is enabled. Bug fixed #1530918.
  • Page cleaner worker threads were not instrumented for Performance Schema. Bug fixed #1532747 (upstream bug #79894).
  • Busy server was preferring LRU flushing over flush list flushing too strongly which could lead to performance degradation. Bug fixed #1534114.
  • libjemalloc.so.1 was missing from a binary tarball. Bug fixed #1537129.
  • When cmake/make/make_binary_distribution workflow was used to produce binary tarballs it would produce tarballs with mysql-... naming instead of percona-server-.... Bug fixed #1540385.
  • Added proper memory cleanup if for some reason a table is unable to be opened from a dead closed state. This prevents an assertion from happening the next time the table is attempted to be opened. Bug fixed #917.
  • Variable tokudb_support_xa has been modified to prevent setting it to anything but ON/ENABLED and to print a SQL warning anytime an attempt is made to change it, just like innodb_support_xa. Bug fixed #928.

Other bugs fixed: #1179451, #1534246, #1524763, #1525109 (upstream #79569), #1530102, #897, #898, #899, #900, #901, #902, #903, #905, #906, #907, #908, #909, #910, #911, #912, #913, #915, #919, and #904.

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

Percona XtraBackup 2.4.0-rc1 is now available

Percona is glad to announce the first release candidate of Percona XtraBackup 2.4.0-rc1 on February 8th 2016. Downloads are available from our download site and from apt and yum repositories.

This is a Release Candidate quality release and it is not intended for production. If you want a high quality, Generally Available release, the current Stable version should be used (currently 2.3.3 in the 2.3 series at the time of writing).

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

This release contains all of the features and bug fixes in Percona XtraBackup 2.3.3, plus the following:

New Features:

  • Percona XtraBackup has implemented basic support for MySQL 5.7 and Percona Server 5.7.

Known Issues:

  • Backed-up table data could not be recovered if backup was taken while running OPTIMIZE TABLE (bug #1541763) or ALTER TABLE ... TABLESPACE (bug #1532878) on that table.
  • Compact Backups currently don’t work due to bug #1192834.

Release notes with all the bugfixes for Percona XtraBackup 2.4.0-rc1 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

Attribute (innodb_version) does not pass the type constraint...

Lastest Forum Posts - 3 hours 47 min ago
"Attribute (innodb_version) does not pass the type constraint because: Validation failed for 'Str' with value undef at /usr/bin/pt-table-sync line 1432."

I'm using pt-table-sync to sync one table from a MySQL 5.5 server to a MySQL 5.7 server. Seems like there should be a simple fix to this error. It just throws this error and quits.

Should I worry about "Some authentication plugins will not work." note?

Lastest Forum Posts - 3 hours 57 min ago
Hello to all

I am trying to set up TLS in percona-server 5.6 (on debian jessie).

I have created my root CA and key pair and set it up in my.cnf (under the 'mysqld' section)

ssl-ca=/etc/ssl/certs/ca-certificates.crt
ssl-cert=/etc/mysql/certs/db.investme.com.crt
ssl-key=/etc/mysql/certs/db.investme.com.key

I gave mysql user and group permissions to them:

chown -R mysql:mysql /etc/mysql/certs/

But I still get this in the error log:

2016-02-08 08:47:27 12930 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2016-02-08 08:47:27 12930 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.

I tried to create symlinks in /var/lib/mysql but then the startup mysql script would freeze.

Any idea? Is this something I should worry about?

Max number of collections / indexes with PerconaFT

Lastest Forum Posts - 5 hours 38 min ago
I've tried to find the information myself but I couldn't, so here it goes:

What is the max number of collections+indexes a single node can have when using PerconaFT? WiredTiger uses file per collection + file per index, which effectively limits the amount substantially. RocksDB works well for huge numbers (N millions), and I would like to confirm PerconaFT engine's technical limits on this.


MySQL 5.7: Introduction for Operational DBAs

Join us Tuesday, February 16, 2016 9:00am PST (UTC-8) for a webinar on the operational and monitoring features of MySQL 5.7 with Percona CEO Peter Zaitsev.

MySQL 5.7 is a great release, providing valuable features for both daily operations and ongoing development. In this, part two of our ongoing 5.7 webinar series, we will look into the new MySQL 5.7 features and enhancements that improve operations, with a specific focus on monitoring. These include:

  • An improved optimizer, including updates to EXPLAIN and enhanced JSON support
  • Performance and scalability improvements for the InnoDB storage engine, including temp tables, spatial types and full text search parsing
  • Security improvements, such as a password expiration policy
  • Performance and sys schema improvements, such as memory usage, metadata locks, monitoring capabilities and reduced footprint/overhead
  • Better online server management
  • Improved replication functions, including new statements for multi-source replication and better monitoring

MySQL 5.7 promises to be faster, more robust, and more secure. We look forward to seeing you at the webinar!

Peter Zaitsev co-founded Percona in 2006, assuming the role of CEO. Percona helps companies of all sizes maximize their success with MySQL. Percona was named to the Inc. 5000 in 2013. Peter was an early employee at MySQL AB, eventually leading the company’s High Performance Group. A serial entrepreneur, Peter co-founded his first startup while attending Moscow State University where he majored in Computer Science. As CEO of Percona, Peter enjoys mixing business leadership with hands on technical expertise. Peter is co-author of High Performance MySQL published by O’Reilly, one of the most popular books on MySQL performance. Peter blogs regularly on MySQLPerformanceBlog.com and speaks frequently at conferences. Peter lives in North Carolina with his wife and two children. In his spare time, Peter enjoys travel and spending time outdoors.

Starting TokuDB with missing recovery log

Lastest Forum Posts - 10 hours 29 min ago
Hello.
I've lost TokuDB recovery log(HDD has died) and TokuDB won't even start complaining about missing recovery log:

Code: /var/log/mysql/error.log: ... [ERROR] TokuDB: Recovery log is missing (persistent environment information is present) [ERROR] TokuDB unknown error 2 [ERROR] Plugin 'TokuDB' init function returned error. [ERROR] Plugin 'TokuDB' registration as a STORAGE ENGINE failed. ... According to documentation:
The TokuDB recovery log stores the transactional state of the active transactions and is used to recover from crashes By the time of the crash the database was in read-only mode for about a month, so, I guess, there was nothing important in the recovery log.

I have a backup but loading it into a new database and building indexes will take days if not weeks.

Is there any way to make TokuDB work in this situation / extract data without starting / etc?

pt-online-schema-change impossible to create triggers

Lastest Forum Posts - 13 hours 2 min ago
Hi,

I'm trying to alter a table with about ~50m rows, but keeps crashing every time I try to run the command. I've used the tool successfully in other alterations of the same database, so I'm not sure what's going on.

Code: # pt-online-schema-change --critical-load Threads_running=100 --alter "ADD COLUMN checked_at INT(11) UNSIGNED NOT NULL DEFAULT '0'" u=root,p=XXX,h=10.0.0.101,D=YYY,t=objects --execute No slaves found. See --recursion-method if host 10.0.0.101 has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `YYY`.`objects`... Creating new table... Created new table YYY._objects_new OK. Altering new table... Altered `YYY`.`_objects_new` OK. 2016-02-08T07:36:03 Creating triggers... 2016-02-08T07:46:14 Dropping triggers... 2016-02-08T07:46:14 Dropped triggers OK. 2016-02-08T07:46:14 Dropping new table... 2016-02-08T07:46:14 Dropped new table OK. `YYY`.`objects` was not altered. Error creating triggers: 2016-02-08T07:46:14 DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement "CREATE TRIGGER `pt_osc_yyy_objects_del` AFTER DELETE ON `yyy`.`objects` FOR EACH ROW DELETE IGNORE FROM `yyy`.`_objects_new` WHERE `yyy`.`_objects_new`.`objectid` <=> OLD.`objectid`"] at /usr/bin/pt-online-schema-change line 10583. Versions:
Code: # pt-online-schema-change --version pt-online-schema-change 2.2.16 Code: mysql> SHOW VARIABLES LIKE "%version%"; -------------- SHOW VARIABLES LIKE "%version%" -------------- +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.6.28 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.28-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------+ 7 rows in set (0.02 sec) Any possible work around for this?

Thanks!

max_seeks_for_key default value

Lastest Forum Posts - February 7, 2016 - 11:01pm
Why is the max_seeks_for_key not defaulted to a lower value if it can help reduce table scans.

pt-online-schema-change and foreign key constraints

Lastest Forum Posts - February 7, 2016 - 10:08am
This is a great tool. I'm using it with --alter-foreign-keys-method=rebuild_constraints, because my reading of the help is that this is the only really safe option (please correct me if this is wrong).

However this can cause a rebuild of other tables - which can on occasion be a worse problem for availability than not using pt-online-schema-change at all, if the second table is large, or if there are multiple tables to rebuild.

I was wondering if it is possible for this tool to work in a way which also rebuilds those other tables in a nicely available way?

Measuring Percona Server Docker CPU/network overhead

Latest MySQL Performance Blog posts - February 5, 2016 - 10:55am

Now that we have our Percona Server Docker images, I wanted to measure the performance overhead when we run the database in the container. Since Docker promises to use a lightweight container, in theory there should be very light overhead. We need to verify that claim, however. In this post I will show the numbers for CPU and network intensive workloads, and later I will take a look at IO.

For the CPU-bound load, I will use a sysbench OLTP read-only workload with data fitting into memory (so there is no IO performed, and the execution path only goes through the network and CPU).

My server is 24 cores (including hyper-threads), with Intel(R) Xeon(R) CPU E5-2643 v2 @ 3.50GHz CPUs, RAM: 256GB, OS: Ubuntu 14.04. The Docker version is the latest on the day of publishing, which is 1.9.1.

First, I measured the throughput on a bare server, without containers – this will be the baseline. For reference, the command I used is the following:

/opt/sysbench/sysbench --test=/opt/tests/db/oltp.lua --oltp_tables_count=8 --oltp_table_size=10000000 --num-threads=16 --mysql-host=172.18.0.2 --mysql-user=root --oltp-read-only=on --max-time=1800 --max-requests=0 --report-interval=10 run

On the bare metal system, the throughput is 7100 transactions per second (tps).

In the next experiment, I started Percona Server in a Docker container and connect to it from the host:

docker run -e MYSQL_ALLOW_EMPTY_PASSWORD=1 --name ps13 -p 3306:3306 -v /data/flash/d1/:/var/lib/mysql -v /data/flash/my.cnf:/etc/my.cnf --net=host percona/percona-server:5.6.28

In this case, the container exposed port 3306 to the host, and we used that as an access point in sysbench.

The throughput in this scenario is 2200 tps!!! That is a significant overhead. I suspect it comes from the Docker gateway, which is added to the execution path when we connect through port forwarding.

So to avoid the Docker gateway, in the next run I used the host network by running the container with --net=host:

docker run -e MYSQL_ALLOW_EMPTY_PASSWORD=1 --name ps13 -v /data/flash/d1/:/var/lib/mysql -v /data/flash/my.cnf:/etc/my.cnf --net=host percona/percona-server:5.6.28

In this case the container ran directly in the host network stack, so this should exclude any Docker network overhead. In this case, the throughput is basically back to 7100 tps.

From these tests, I can make an important conclusion. There is NO measurable CPU overhead when running Percona Server in a Docker container. But the network path raises some questions.

So in the next experiment I ran both sysbench and MySQL in two different containers, connected over the Docker network bridge.

I created a sysbench container, which you can get from:

 https://hub.docker.com/r/percona/sysbench/

To run sysbench:

docker run --name sb -t percona/sysbench

Just for the reference, I created a Docker network:

docker network create sysbenchnet

and connected both containers to the same network:

docker network connect sysbenchnet ps13; docker network connect sysbenchnet sb;

In this configuration, the throughput I’ve observed is 6300 tps.

So there is still some network overhead, but not as significant as with the port gateway case.

For the last example, I again excluded the network path and ran the sysbench container inside the MySQL container network stack using the following command:

docker run --name sb --net container:ps13 -t percona/sysbench

The throughput in this configuration is back to 7100 tps. 

And the conclusion, again, is that there is no CPU overhead even if we run both client and server inside containers, but there is some network overhead – even when running on the same host. It will be interesting to measure the network overhead when the containers are on different physical hosts.

The following chart summarizes the results:

Next time I will try to measure IO overhead in Docker containers.

Can I use Xtrabackup to clone an existing replica?

Lastest Forum Posts - February 4, 2016 - 2:24pm
Suppose I have a master and a replica already in production, and I want to create a second replica of the master (not chained). I'd like to do this during production hours, so running a backup against the master instance is off the table. I can currently do this by shutting down the replica and copying all the data files over to the new replica, then using the info in the original replica's master.info file to start replication on the new replica. If I want to do this without shutting down the first replica, is there a way I can clone it using Xtrabackup and then somehow retrieve the required start positions to start the I/O thread on the new replica without duplicating or losing any transactions?

Thanks!

Newbie question on Percona server and Xtrabackup

Lastest Forum Posts - February 4, 2016 - 10:39am
Hello,
I just heard about Percona, and saw it's benchmarks against Mysql, and i'm very impressed.
Following are the things i'd like to do to my VPS -

1.) Upgrade Mysql to Percona server
2.) Use Percona XtraBackup to perform Hot Backups

I found this amazing article on digitalocean to upgrade to Percona - https://www.digitalocean.com/communi...-replace-mysql
I'm a little confused in using Xtrabackup on a percona server ... are there any tutorials specific to perform Xtrabackup on Percona server, rather than a MySql server?

Your help is greatly appreciated, thanks!

MySQL password expiration features to help you comply with PCI-DSS

Latest MySQL Performance Blog posts - February 4, 2016 - 7:12am

PCI Compliance (section 8.2.4) requires users to change password every 90 days. Until MySQL 5.6.6 there wasn’t a built-in way to comply with this requirement.

Since MySQL version 5.6.6 there’s a password_expired feature which allows to set a user’s password as expired.
This has been added to the mysql.user table and its default value it’s “N.” You can change it to “Y” using the ALTER USER statement.

Here’s an quick example on how to set expiration date for a MySQL user account:

mysql> ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE;

Once this is set to “Y” the username will still be able to login to the MySQL server, but it will not be able to run any queries before setting the new password. You will instead get an ERROR 1820 message:

mysql> SHOW DATABASES; ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

Keep in mind that this does not affect any current connections the account has open.

After setting a new password, all operations performed using the account will be allowed (according to the account privileges):

mysql> SET PASSWORD=PASSWORD('mechipoderranen'); Query OK, 0 rows affected (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | data | | logs | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) mysql>

This allows administrators to perform password expiration by scheduling the ALTER USER via cron.

Since MySQL 5.7.4, this has been improved and there’s a new feature to set a policy for password expiration, that provides more control through a global variable, default_password_lifetime which allows to set a global automatic password expiration policy.

Example usage:

Setting a default value on our configuration file. This will set all account passwords to expire every 90 days, and will start counting from the day this variable was set effective on your MySQL server:

[mysqld] default_password_lifetime=90

Setting a global policy for the passwords to never expire. Note this is the default value (so it is not strictly necessary to declare in the configuration file):

[mysqld] default_password_lifetime=0

This variable can also be changed at runtime if the user has SUPER privileges granted:

mysql> SET GLOBAL default_password_lifetime = 90; Query OK, 0 rows affected (0.00 sec)

You can also set specific values for each user account using ALTER USER. This will override the global password expiration policy. Please note that ALTER USER only understands INTERVAL expressed in DAY:

ALTER USER ‘testuser’@‘localhost' PASSWORD EXPIRE INTERVAL 30 DAY;

Disable password expiration:

ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE NEVER;

Set to default value, which is the current value of default_password_lifetime:

ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE DEFAULT;

Since MySQL 5.7.6, you can use the ALTER USER to change the user’s password:

mysql> ALTER USER USER() IDENTIFIED BY '637h1m27h36r33K'; Query OK, 0 rows affected (0.00 sec)

For more information on this variable, please refer to the documentation page: https://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html

Bonus post:

Another new feature in MySQL 5.7.8 related to user management is locking/unlocking user accounts when CREATE USER, or at a later time running the ALTER USER statement.

In this example, we will first create a username with the ACCOUNT LOCK:

mysql> CREATE USER 'furrywall'@'localhost' IDENTIFIED BY '71m32ch4n6317' ACCOUNT LOCK; Query OK, 0 rows affected (0.00 sec)

As you can see below, the newly created user gets an ERROR 3118 message while trying to login:

$ mysql -ufurrywall -p Enter password: ERROR 3118 (HY000): Access denied for user 'furrywall'@'localhost'. Account is locked.

We can unlock the account using the ALTER USER ... ACCOUNT UNLOCK; statement:

mysql>ALTER USER 'furrywall'@'localhost' ACCOUNT UNLOCK; Query OK, 0 rows affected (0.00 sec)

Now the user account is unlocked and accessible:

$ mysql -ufurrywall -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 17 Server version: 5.7.8-rc MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>

If necessary, you can lock it again:

mysql> ALTER USER 'furrywall'@'localhost' ACCOUNT LOCK; Query OK, 0 rows affected (0.00 sec)

Please check this following documentation for more details: https://dev.mysql.com/doc/refman/5.7/en/account-locking.html

Backup MySQL database remotely

Lastest Forum Posts - February 3, 2016 - 8:48pm
Hi,

Currently, we have 3 windows server with MySQL servers and planning to use XtraBackup to run the incremental database backup.

Would like to setup this kind of scenario;

1. Install linux in HyperV.
2. Install Extrabackup in Linux.
3. Run Extrabackup and configure incremental backup database remotely from 3 windows server.
4. And put the database backup file in network drive.

Anyone already did this?

New GIS Features in MySQL 5.7

Latest MySQL Performance Blog posts - February 3, 2016 - 7:25am

MySQL 5.7 has been released, and there are some exciting new features now available that I’m going to discuss in this blog — specifically around geographic information system (GIS).

I’ve used GIS features in MySQL for a long time. In my previous blog entries I’ve shown how to create geo-enabled applications with MySQL 5.6 and use MySQL 5.6 geo-spatial functions. In this blog post, I’ll look into what is new in MySQL 5.7 and how we can use those new features in practice for geo-enabled applications.

New in MySQL 5.7

MySQL 5.7 introduces the following major improvements and features for GIS:

  1. Spatial indexes for InnoDB. Finally it is here! This was a long overdue feature, which also prevented many companies from converting all tables to InnoDB.
  2. st_distance_sphere: native function to calculate a distance between two points on earth. Finally it is here as well! Like many others, I’ve created my stored procedure to calculate the distance between points on earth using haversine formula. The native function is ~20x faster than the stored procedure (in an artificial benchmark, see below). This is not surprising, as stored procedures are slow computationally – especially for trigonometrical functions.
  3. New functions: GeoHash and GeoJSON. With GeoJSON we can generate the results that are ready for visualizing on Google Maps.
  4. New GIS implementation based on Boost.Geometry library. This is great news, as originally GIS was implemented independently from scratch with a very limited set of features. Manyi Lu from MySQL server team provides more reasoning behind the choice of Boost.Geometry.

This is the great news. The bad news is that except for the st_distance_sphere, all other functions use planar geometry (no change since MySQL 5.6) and do not support Spatial Reference System Identifier (SRID). That means that if I want to calculate the distance of my favorite bike path in miles or kilometers, I’ll still have to use a stored function (see below for an example) or write an application code for that. Native function st_distance will ignore SRID for now and return a value which represents a distance on a planar – not very useful for our purposes (may be useful for order by / compare).

Distance on Sphere

MySQL 5.7 introduces the function st_distance_sphere, which uses a haversine formula to calculate distance. He is the example:

mysql> select st_distance_sphere(point(-78.7698947, 35.890334), point(-122.38657, 37.60954)); +--------------------------------------------------------------------------------+ | st_distance_sphere(point(-78.7698947, 35.890334), point(-122.38657, 37.60954)) | +--------------------------------------------------------------------------------+ | 3855600.7928957273 | +--------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

The distance is in meters by default (you can also change the radius of the earth to meters using the 3rd optional parameter, default: 6,370,986). Although our earth is represented as an oblate spheroid, all practical applications use the distance on a sphere. The difference between the haversine formula and more precise (and much slower) functions is negligible for our purposes.

The st_distance_sphere is much faster than using stored routines. Here is the artificial benchmark:

mysql> select benchmark(1000000, haversine_distance_sp(37.60954, -122.38657, 35.890334, -78.7698947)); +-----------------------------------------------------------------------------------------+ | benchmark(1000000, haversine_distance_sp(37.60954, -122.38657, 35.890334, -78.7698947)) | +-----------------------------------------------------------------------------------------+ | 0 | +-----------------------------------------------------------------------------------------+ 1 row in set (22.55 sec) mysql> select benchmark(1000000, st_distance_sphere(point(-78.7698947, 35.890334), point(-122.38657, 37.60954))); +----------------------------------------------------------------------------------------------------+ | benchmark(1000000, st_distance_sphere(point(-78.7698947, 35.890334), point(-122.38657, 37.60954))) | +----------------------------------------------------------------------------------------------------+ | 0 | +----------------------------------------------------------------------------------------------------+ 1 row in set (0.77 sec)

haversine_distance_sp is a stored routine implementation of the same algorithm.

InnoDB GIS example: find 10 restaurants near me 

In my previous blog post I’ve demonstrated how to use st_within function to find restaurants inside my zipcode (US postal code) and sort by distance. In MySQL 5.7 there will be 2 changes:

  1. We can use InnoDB table
  2. We can use st_distance_sphere function

For this example, I’ve converted Open Street Map data to MySQL and then created a new InnoDB table:

CREATE TABLE `points_new` ( `OGR_FID` int(11) NOT NULL AUTO_INCREMENT, `SHAPE` geometry NOT NULL, `osm_id` text, `name` text, `barrier` text, `highway` text, `ref` text, `address` text, `is_in` text, `place` text, `man_made` text, `other_tags` text, UNIQUE KEY `OGR_FID` (`OGR_FID`), SPATIAL KEY `SHAPE` (`SHAPE`) ) ENGINE=InnoDB AUTO_INCREMENT=13660668 DEFAULT CHARSET=latin1

SHAPE is declared as geometry (and stores points in this table). We also have SPATIAL KEY SHAPE in the InnoDB table.

The following query will find all cafe or restaurants in Durham, NC (zipcode: 27701):

SELECT osm_id, name, round(st_distance_sphere(shape, st_geomfromtext('POINT (-78.9064543 35.9975194)', 1) ), 2) as dist FROM points_new WHERE st_within(shape, (select shape from zcta.tl_2013_us_zcta510 where zcta5ce10='27701') ) and (other_tags like '%"amenity"=>"cafe"%' or other_tags like '%"amenity"=>"restaurant"%') and name is not null ORDER BY dist asc LIMIT 10;

Table tl_2013_us_zcta510 stores the shapes of polygons for all US zipcodes. (It needs to be converted to MySQL.) In this example I’m using st_within to filter only the POIs I need, and st_distance_sphere to get the distance from my location (-78.9064543 35.9975194 are the coordinates of Percona’s office in Durham) to the restaurants.

Explain plan:

mysql> EXPLAIN -> SELECT osm_id, name, round(st_distance_sphere(shape, st_geomfromtext('POINT (-78.9064543 35.9975194)', 1) ), 2) as dist -> FROM points_new -> WHERE st_within(shape, -> (select shape from zcta.tl_2013_us_zcta510 where zcta5ce10='27701') ) -> and (other_tags like '%"amenity"=>"cafe"%' or other_tags like '%"amenity"=>"restaurant"%') -> and name is not null -> ORDER BY dist asc LIMIT 10G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: points_new partitions: NULL type: range possible_keys: SHAPE key: SHAPE key_len: 34 ref: NULL rows: 21 filtered: 18.89 Extra: Using where; Using filesort *************************** 2. row *************************** id: 2 select_type: SUBQUERY table: tl_2013_us_zcta510 partitions: NULL type: ref possible_keys: zcta5ce10 key: zcta5ce10 key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL 2 rows in set, 1 warning (0.00 sec)

That looks pretty good: MySQL is using and index on the SHAPE field (even with the subquery, btw).

Results:

mysql> SELECT osm_id, name, round(st_distance_sphere(shape, st_geomfromtext('POINT (-78.9064543 35.9975194)', 1) ), 2) as dist, st_astext(shape) -> FROM points_new -> WHERE st_within(shape, -> (select shape from zcta.tl_2013_us_zcta510 where zcta5ce10='27701') ) -> and (other_tags like '%"amenity"=>"cafe"%' or other_tags like '%"amenity"=>"restaurant"%') -> and name is not null -> ORDER BY dist asc LIMIT 10; +------------+----------------------------+--------+--------------------------------------+ | osm_id | name | dist | st_astext(shape) | +------------+----------------------------+--------+--------------------------------------+ | 880747417 | Pop's | 127.16 | POINT(-78.9071795 35.998501) | | 1520441350 | toast | 240.55 | POINT(-78.9039761 35.9967069) | | 2012463902 | Pizzeria Toro | 256.44 | POINT(-78.9036457 35.997125) | | 398941519 | Parker & Otis | 273.39 | POINT(-78.9088833 35.998997) | | 881029843 | Torero's | 279.96 | POINT(-78.90829140000001 35.9995516) | | 299540833 | Fishmonger's | 300.01 | POINT(-78.90850250000001 35.9996487) | | 1801595418 | Lilly's Pizza | 319.83 | POINT(-78.9094462 35.9990732) | | 1598401100 | Dame's Chicken and Waffles | 323.82 | POINT(-78.9031929 35.9962871) | | 685493947 | El Rodeo | 379.18 | POINT(-78.909865 35.999523) | | 685504784 | Piazza Italia | 389.06 | POINT(-78.9096472 35.9998794) | +------------+----------------------------+--------+--------------------------------------+ 10 rows in set (0.13 sec)

0.13 seconds response time on AWS t2.medium box sounds reasonable to me. The same query on the MyISAM table shows ~same response time: 0.14 seconds.

GeoJSON feature and Google Maps

Another nice feature of MySQL 5.7 GIS is GeoJSON function: you can convert your result set to GeoJSON, which can be used with other applications (for example Google Maps API).

Let’s say I want to visualize the above result set on Google Map. As the API requires a specific format, I can use concat / group_concat to apply the format inside the SQL:

SELECT CONCAT('{ "type": "FeatureCollection", "features": [ ', GROUP_CONCAT('{ "type": "Feature", "geometry": ', ST_AsGeoJSON(shape), ', "properties": {} }'), '] }') as j FROM points_new WHERE st_within(shape, (select shape from zcta.tl_2013_us_zcta510 where zcta5ce10='27701') ) and (other_tags like '%"amenity"=>"cafe"%' or other_tags like '%"amenity"=>"restaurant"%') and name is not null

I will get all the restaurants and cafes in zipcode 27701. Here I’m using ST_AsGeoJSON(shape) to convert to GeoJSON, and concat/group_concat to “nest” the whole result into the format suitable for Google Maps.

Result:

mysql> set group_concat_max_len = 1000000; Query OK, 0 rows affected (0.00 sec) mysql> SELECT CONCAT('{ '> "type": "FeatureCollection", '> "features": [ '> ', -> GROUP_CONCAT('{ '> "type": "Feature", '> "geometry": ', ST_AsGeoJSON(shape), ', '> "properties": {} '> }'), -> '] '> }') as j -> FROM points_new -> WHERE st_within(shape, -> (select shape from zcta.tl_2013_us_zcta510 where zcta5ce10='27701') ) -> and (other_tags like '%"amenity"=>"cafe"%' or other_tags like '%"amenity"=>"restaurant"%') -> and name is not null *************************** 1. row *************************** j: { "type": "FeatureCollection", "features": [ { "type": "Feature", "geometry": {"type": "Point", "coordinates": [-78.890852, 35.9903403]}, "properties": {} },{ "type": "Feature", "geometry": {"type": "Point", "coordinates": [-78.8980807, 35.9933562]}, "properties": {} },{ "type": "Feature", "geometry": {"type": "Point", "coordinates": [-78.89972490000001, 35.995879]}, "properties": {} } ... ,{ "type": "Feature", "geometry": {"type": "Point", "coordinates": [-78.9103211, 35.9998494]}, "properties": {} },{ "type": "Feature", "geometry": {"type": "Point", "coordinates": [-78.9158326, 35.9967114]}, "properties": {} }] } 1 row in set (0.14 sec)

I did not include the full result set for the lack of space; I also had to change the group concat max length, otherwise MySQL will cut the result of the group_concat function.

Now I can visualize it:

 

 

 

 

 

 

 

 

 

 

Example: Find the longest bike path

MySQL 5.7 (as well as the older versions) supports st_length function to calculate a length of a linestring. However, even in MySQL 5.7 st_length can’t calculate the distance on earth. To find the distance of a linestring I’ve created a very simple stored procedure:

DELIMITER // CREATE DEFINER=CURRENT_USER() FUNCTION `ls_distance_sphere`(ls GEOMETRY) RETURNS DECIMAL(20,8) DETERMINISTIC BEGIN DECLARE i, n INT DEFAULT 0; DECLARE len DECIMAL(20,8) DEFAULT 0; SET i = 1; SET n = ST_NumPoints(ls); WHILE i < n DO SET len = len + st_distance_sphere(st_pointN(ls, i), st_pointN(ls, i+1)); SET i = i + 2; END WHILE; RETURN len; END // DELIMITER ;

As the Open Street Map data has the information about roads in North America, we can use this function to calculate the length (in meters) for every road it stores:

mysql> select name, ls_distance_sphere(shape) from lines_new where highway = 'cycleway' and name is not null limit 10; +---------------------------------------+---------------------------+ | name | ls_distance_sphere(shape) | +---------------------------------------+---------------------------+ | Highbury Park Drive Bypass | 0.97386664 | | Ygnacio Canal Trail | 0.86093199 | | South Marion Parkway | 1.06723424 | | New River Greenway | 1.65705401 | | Northern Diversion Trail | 2.08269808 | | Gary L. Haller Trail;Mill Creek Trail | 2.09988209 | | Loop 1 | 2.05297129 | | Bay Farm Island Bicycle Bridge | 2.51141623 | | Burrard Street | 1.84810259 | | West 8th Avenue | 1.76338236 | +---------------------------------------+---------------------------+ 10 rows in set (0.00 sec)

Index the polygon/area distance using MySQL 5.7 virtual fields

To really answer the question “what is the longest bikepath (cyclepath) in North America?” we will have to order by stored function result. This will cause a full table scan and a filestort, which will be extremely slow for 30 millions of rows. The standard way to fix this is to materialize this road distance: add an additional field to the table and store the distance there.

In MySQL 5.7 we can actually use Generated (Virtual) Columns feature:

CREATE TABLE `lines_new` ( `OGR_FID` int(11) NOT NULL AUTO_INCREMENT, `SHAPE` geometry NOT NULL, `osm_id` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `highway` varchar(60) DEFAULT NULL, `waterway` text, `aerialway` text, `barrier` text, `man_made` text, `other_tags` text, `linestring_length` decimal(15,8) GENERATED ALWAYS AS (st_length(shape)) VIRTUAL, PRIMARY KEY (`OGR_FID`), SPATIAL KEY `SHAPE` (`SHAPE`), KEY `linestring_length` (`linestring_length`), KEY `highway_len` (`highway`,`linestring_length`) ) ENGINE=InnoDB AUTO_INCREMENT=27077492 DEFAULT CHARSET=latin1

Unfortunately, MySQL 5.7 does not support non-native functions (stored procedures or UDF) in generated columns, so I have to use st_length in this example. Ordering by value of st_length may be OK though:

mysql> select name, ls_distance_sphere(shape) from lines_new where highway = 'cycleway' and name is not null order by linestring_length desc limit 10; +-----------------------------+---------------------------+ | name | ls_distance_sphere(shape) | +-----------------------------+---------------------------+ | Confederation Trail | 55086.92572725 | | Cowboy Trail | 43432.06768706 | | Down East Sunrise Trail | 42347.39791330 | | Confederation Trail | 29844.91038542 | | Confederation Trail | 26141.04655981 | | Longleaf Trace | 29527.66063726 | | Cardinal Greenway | 30613.24487294 | | Lincoln Prairie Grass Trail | 19648.26787218 | | Ghost Town Trail | 25610.52158647 | | Confederation Trail | 27086.54829531 | +-----------------------------+---------------------------+ 10 rows in set (0.02 sec)

The query is very fast as it uses an index on both highway and linestring:

mysql> explain select name, ls_distance_sphere(shape) from lines_new where highway = 'cycleway' and name is not null order by linestring_length desc limit 10G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: lines_new partitions: NULL type: ref possible_keys: highway_len key: highway_len key_len: 63 ref: const rows: 119392 filtered: 90.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)

Conclusion

MySQL 5.7 contains a great set of features to work with geospatial data. Finally, spatial indexes are supported in InnoDB; st_distance_sphere as a native function is very useful. Unfortunately, other spatial functions only work with planar coordinates and do not support SRID. I hope this will be fixed in the new releases.

Percona Live Crash Courses: for MySQL and MongoDB!

Latest MySQL Performance Blog posts - February 2, 2016 - 11:45am

Percona Live Crash Courses for MySQL and MongoDB

The database community constantly tells us how hard it is to find someone with MySQL and MongoDB DBA skills who can help with the day-to-day management of their databases. This is especially difficult when companies don’t have a full-time requirement for a DBA. Developers, system administrators and IT staff spend too much time trying to solve basic database problems that keep them from doing their day job. Eventually the little problems or performance inefficiencies that start to pile up  lead to big problems.  

In answer to this growing need, Percona Live is once again hosting Crash Courses for developers, systems administrators, and other technical resources. This year, we’ve compacted the training into a single day, and are offering two options: MySQL 101 and MongoDB 101!

Don’t let the name fool you: these courses are led by Percona MySQL experts who will show you the fundamentals of MySQL or MongoDB tools and techniques.  

And it’s not just for DBAs: developers are encouraged to attend to hone their database skills. Developers who create code that can scale to match the demands of the online community are both a resource and and an investment.

Below are a list of the topics covered for each course:

MySQL 101 Topics

MongoDB 101 Topics

  • Schema Review 101: How and What You Should Be Looking at…
  • Choosing a MySQL High Availability Solution Today
  • MySQL Performance Troubleshooting Best Practices
  • Comparing Synchronous Replication Solutions in the Cloud
  • Cost Optimizations Through MySQL Performance Optimizations
  • SQL with MySQL or NoSQL with MongoDB?
  • MongoDB for MySQL DBA’s
  • MongoDB Storage Engine Comparison
  • MongoDB 3.2: New Features Overview

 

Attendees will return ready to quickly and correctly take care of the day-to-day and week-to-week management of your MySQL or MongoDB environment.

The schedule and non-conference cost for the 101 courses are:

  • MySQL 101: Tuesday April 19th ($400)
  • MongoDB 101: Wednesday April 20th ($400)
  • Both MySQL and MongoDB 101 sessions ($700)

(Tickets to the 101 sessions do not grant access to the main Percona Live breakout sessions. Full Percona Live conferences passes will grant admission to the 101 sessions. 101 Crash Course attendees will have full access to Percona Live keynote speakers the exhibit hall and receptions.)

As a special promo, the first 101 people to purchase the 101 talks receive a $299.00 discount off the ticket price! Each session only costs $101! Get both sessions for a mere $202! Register now, and use the following codes for your first 101 discount:

  • Single101= $299 off of either the MySQL or MongoDB tickets
  • Double101= $498 off of the combined MySQL/MongoDB ticket

Sign up now for special track pricing. Click here to register.

Birds of a Feather

Birds of a Feather (BOF) sessions enable attendees with interests in the same project or topic to enjoy some quality face time. BOFs can be organized for individual projects or broader topics (e.g., best practices, open data, standards). Any attendee or conference speaker can propose and moderate an engaging BOF. Percona will post the selected topics and moderators online and provide a meeting space and time. The BOF sessions will be held Tuesday, April 19, 2016 at 6:00 p.m. The deadline for BOF submissions is February 7.

Lightning Talks

Lightning Talks provide an opportunity for attendees to propose, explain, exhort, or rant on any MySQL, NoSQL or Data in the Cloud-related topic for five minutes. Topics might include a new idea, successful project, cautionary story, quick tip, or demonstration. All submissions will be reviewed, and the top 10 will be selected to present during one of the scheduled breakout sessions during the week. Lighthearted, fun or otherwise entertaining submissions are highly welcome. The deadline for submitting a Lightning Talk topic is February 7, 2016.

Experimental Percona Docker images for Percona Server

Latest MySQL Performance Blog posts - February 2, 2016 - 9:02am

Docker is incredibly popular tool for deploying software, so we decided to provide a Percona Docker image for both Percona Server MySQL and Percona Server for MongoDB.

We want to create an easy way to try our products.

There are actually some images available from https://hub.docker.com/_/percona/, but these images are provided by Docker itself, not from Percona.

In our images, we provide all the varieties of storage engines available in Percona Server (MySQL/MongoDB).

Our images are available from https://hub.docker.com/r/percona/.

The simplest way to get going is to run the following:

docker run --name ps -e MYSQL_ROOT_PASSWORD=secret -d percona/percona-server:latest

for Percona Server/MySQL, and:

docker run --name psmdb -d percona/percona-server-mongodb:latest

for Percona Server/MongoDB.

It is very easy to try the different storage engines that comes with Percona Server for MongoDB. For example, to use RocksDB, run:

docker run --name psmdbrocks -d percona/percona-server-mongodb:latest --storageEngine=RocksDB

or PerconaFT:

docker run --name psmdbperconaft -d percona/percona-server-mongodb:latest --storageEngine=PerconaFT

We are looking for any feedback  you’d like to provide: if this is useful, and what improvements we could make.

Deadlock Encountered when using pt-online-schema-change

Lastest Forum Posts - February 2, 2016 - 7:54am
I have a shell script looping 1000 times, doing inserts into a database table.
I am running pt-online-schema-change, creating a unique index on a column in that table.
pt-online-schema-change --alter-foreign-keys-method=auto --alter="ADD UNIQUE INDEX UQ_E_ID (E_ID)" --execute h=localhost,u=xx,p=**,D=db,t=t1

My shell script reports
./do_sql.sh
ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try restarting transaction


The deadlock reported is
pt-deadlock-logger h=localhost,u=xx,p=**,D=db,t=t1
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
localhost 2016-02-02T10:40:06 13616 0 0 root localhost db t1 PRIMARY RECORD S w 0 INSERT LOW_PRIORITY IGNORE INTO `db.`_t1_new` (`uid`, `name`, `accountid`, `deleted`, `e_id`, `parent_id`, `classification`) SELECT `uid`, `name`, `accountid`, `deleted`, `e_id`, `parent_id`, `classification` FROM `db`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 23347 copy table*/
localhost 2016-02-02T10:40:06 13847 0 0 root localhost db _t1_new TABLE AUTO-INC w 1 REPLACE INTO `db`.`_t1_new` (`uid`, `name`, `accountid`, `deleted`, `e_id`, `parent_id`, `classification`) VALUES (NEW.`uid`, NEW.`name`, NEW.`accountid`, NEW.`deleted`, NEW.`e_id`, NEW.`parent_id`, NEW.`classification`)


We are seriously considering using this tool to prevent downtime in our application when we need to modify our database schema, but am quite concerned by this finding. Can anyone suggest work-arounds or problems with my commands.
Thanks in advance!

Older versions of XtraDB Cluster via apt-get

Lastest Forum Posts - February 1, 2016 - 10:33am
Hello,

As the title suggests, I am trying to get an older version of XtraDB Cluster using apt-get but I am not having any luck using sudo apt-get install percona-xtradb-cluster-56=5.6.27-25.13.wheezy . Is this version no longer available via apt-get or am I missing something here?

Thanks for any help.


General Inquiries

For general inquiries, please send us your question and someone will contact you.