Feb
05
2016
--

ZIRX Drops Its Consumer Valet Service To Focus On The Enterprise

ZIRX-agent-customer ZIRX, an on-demand valet startup, is shutting down its consumer service at the end of the month, according to an email that the company sent out to ZIRX members today. The company confirmed the move in a blog post. However, the company isn’t shutting down. ZIRX is shifting its focus to its enterprise service while shuttering its consumer valet service on February 29. In short,… Read More

Feb
05
2016
--

Measuring Percona Server Docker CPU/network overhead

Docker

Precona Server DockerNow 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:

dockeroverhead

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

Feb
04
2016
--

Box KeySafe Aims To Simplify Encryption Key Management For SMBs

Set of keys on an old ring sitting on a warn wooden table. Box announced a new product today called Box KeySafe that should help simplify encryption key management, putting it in reach of small to medium sized businesses (SMBs) who require encryption, but want to avoid the complexities associated with Box’s higher end Enterprise Key Management product. Many highly regulated industries have wanted to move to the cloud, but security and… Read More

Feb
04
2016
--

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

MySQL passwordPCI 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

Feb
04
2016
--

Sonatype Snares $30 Million Investment Led By Goldman Sachs

Business man carrying an umbrella to protect him from digital rain. Sonatype, a company that helps customers create automated, policy-driven software component security, announced a $30 million round today led by Goldman Sachs. The investment was a mix of debt and equity financing and was handled by Goldman’s Principal Strategic Investments Group, rather than Goldman’s venture capital arm, Sonatype CEO Wayne Jackson explained. Although this… Read More

Feb
03
2016
--

Cisco To Buy Jasper Technologies For $1.4 Billion

Cisco Headquarters Cisco announced today it was buying Jasper Technologies, developers of an Internet of Things cloud platform for $1.4 billion. With Jasper, Cisco gets a company that understands the burgeoning Internet of Things market. While the IoT term gets bandied about quite a bit, it simply means connected machines talking to one another over the internet. This could be industrial automation equipment on… Read More

Feb
03
2016
--

New GIS Features in MySQL 5.7

MySQL 5.7MySQL 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.

Feb
02
2016
--

Marc Benioff Offers Some Insight Into Thinking Behind SteelBrick Purchase

Salesforce CEO Marc Benioff speaking at a presentation today. When Salesforce announced it was buying quote-to-cash vendor SteelBrick for $360 million at the end of last year, it came as a surprise. CEO Marc Benioff offered some insight into why he made the purchase at today’s Salesforce Lightning CRM launch event.
The move has to be seen in the context of comments made at a Dreamforce press event last fall with Keith Block, who was named COO today. Read More

Feb
02
2016
--

Salesforce Announces Latest Lightning CRM Release With SteelBrick Integration

Salesforce logo on satellite. This afternoon at a big presentation in San Francisco, Salesforce announced a new version of the Lightning CRM platform that includes built-in Wave analytics, intelligent email from its RelateIQ purchase in July, 2014 and configure-price-quote features from its SteelBrick acquisition at the end of last year. In addition, the company announced a new built-in phone tool which allows sales people… Read More

Feb
02
2016
--

Percona Live Crash Courses: for MySQL and MongoDB!

Percona Live

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.

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