Near a year ago we looked for a geolocation solution to complement the functionality of one of our projects. We expected to deal with hundreds of thousands of records at the beginning and to grow up to 10-15 million records. Finally, we’ve picked up PostgreSQL and its PostGIS extension.

We’ve decided to set amount of records to 100M, so to make it worth of telling about here. The key goal is to compare both DBs spatial indexes possibilities and speed.

Possible solutions

You’ll probably end up with some popular solutions like:
– PostgreSQL & PostGIS
– MySQL
– solr
– MongoDB

Each of them has its own pros and cons, but for a several reasons we decided to choose finally between PostgreSQL and MySQL.

Server configuration

Our benchmark ran on virtual server having power of all cores of physical CPU (Intel Xeon E31275 @ 3.40GHz) and with 4 Gb of RAM. Hard disks maintained by LSI MegaSAS 9260 controller.

Software versions & configuration details

Software versions we used in our benchmarking:
PostgreSQL: 9.1.2
PostGIS: 1.5.3
MySQL: 5.5.17

MySQL configuration adjustments are quite brief:

key_buffer = 2GB
sort_buffer_size = 7M
read_buffer_size = 7M
read_rnd_buffer_size = 7M
myisam_sort_buffer_size = 8MB
myisam_max_sort_file_size = 128MB
thread_concurrency = 16


PostgreSQL changes are compact too:

shared_buffers = 2GB
temp_buffers = 8MB
work_mem = 7MB
effective_io_concurrency = 8
effective_cache_size = 2GB


DBs tables setup and notes

MySQL’s create table statement:

CREATE TABLE locations (
id serial,
name varchar(255),
latitude decimal(12, 8),
longitude decimal(12, 8),
location Point NOT NULL
) ENGINE=MyISAM;


As you may see engine type here is MyISAM. That’s because only this type of storage engine supports spatial indexing.

Spatial index creating statement:
CREATE SPATIAL INDEX idx_locations ON locations(location);

PostgreSQL create table statement:
CREATE TABLE locations (
id serial UNIQUE,
name varchar(255),
latitude decimal(12, 8),
longitude decimal(12, 8),
location GEOGRAPHY(POINT,4326)
);


Spatial index creating statement:
CREATE INDEX idx_location ON locations USING GIST(location);

Note: some table columns were skipped

Benchmarking methodology

Our business needs included searching for locations within some radius having coordinates of the center. While benchmarking, we’re taking 1000 locations and searching for other locations using coordinates of each selected row within 1000 meters around it. This gives us 1000 lookups. Every hundred of lookups we’ve printed time to see a progress.

Also, we measured performance producing 20 parallel requests, each with 30 points to look around.

Our set consists of 100M records placed randomly within -20..20 degrees both longitude and latitude.

In order to have benefits from spatial indexing in MySQL you’ll have to issue several queries (they may be packed in stored procedure):

set @lng1 = ${lng} - 1000/abs(cos(radians($lat)) * 111133.00000000001);
set @lng2 = ${lng} + 1000/abs(cos(radians($lat)) * 111133.00000000001);
set @lat1 = ${lat} - (1000/111133.00000000001);
set @lat2 = ${lat} + (1000/111133.00000000001);
set @search_area = PolyFromText(CONCAT('Polygon((', @lng1, ' ', @lat1, ',', @lng1, ' ', @lat2, ',', @lng2, ' ', @lat2, ',', @lng2, ' ', @lat1, ',', @lng1, ' ', @lat1, '))'));
SELECT id, name, latitude, longitude FROM businesses WHERE MBRWithin(location, @search_area) ORDER BY id ASC;


Those statements will give us a square with the given coordinates in the point of its diagonals intersection. The distance from this “center” point to all vertexes will be 1000 meters (the distance we’re searching within in our benchmark).

PostGIS query is much more compact than MySQL one:
SELECT id,name, latitude, longitude from businesses where ST_DWithin(location, ST_PointFromText('POINT($lng $lat)', 4326), 1000) ORDER BY id ASC;

Shell variables $lng and $lat are substituted with coordinates of the points we’re going to search around during queries generation.

Benchmarking results

Note: while MySQL spatial extension is built-in and did not require any special setup, PostGIS is a separate one and its setup documentation suggests performing index clustering (cluster index statement), without this step performance of this extension is simply awful (3-4 times slower than results below you’ll see).

Before taking in count any results, we’ve ran several warm-up cycles. After that we’ve taken 3 runs for each DB. Each run used new set of 1000 points to search. This was done manually by changing offset for initial select query.

So finally for 100M of records next benchmarking results were received:

Consecutive mode

Parallel mode (20 processes x 30 searches)

Averaging these results we have next ones:
Consecutive run (100M of records):

Parallel run (100M of records):

As a result, PostgreSQL/PostGIS appears to be 9.6% faster than MySQL in consecutive mode and 48.6% faster in parallel mode we’ve used for benchmarking.

Further more there’s one more benchmark to prove one important thing: spatial indexes doesn’t depend much on records amount in a table.

So let’s benchmark the same lookups but with 50M of records seeded with higher density (square with -10..10 for both latitude and longitude).

Consecutive mode

Parallel mode (20 processes x 30 searches)

Averaging these results we have next ones:
Consecutive run (50M of records):

Parallel run (50M of records):

Comparing to less dense 100M set you may see that PostgreSQL/PostGIS performed almost at the same rate in consecutive mode, while MySQL was significantly slower increasing the gap up to 25.2% between DBs.

As for parallel mode MySQL retained its performance while PostgreSQL/PostGIS introduced some decrease in performance while still performing faster, now by 27.5%.

Slightly higher density of locations influenced timings comparing to less dense 100M set.

Conclusions

Although it was rather rough comparison because of the way both DBs produced result, I think, it was interesting to see how MySQL and PostgreSQL performed during this benchmark.

Spatial indexes proved to be fast no matter how large is your dataset. Both 50M and 100M datasets seemed to be the same deal for engines and indexes. Both DBs using R-Tree scheme for spatial indexes (PostgreSQL has a special type for it called GIST). What influenced results more is locations density, slowing down overall performance on a smaller dataset.

One more note not to be missed about PostGIS – it allows to have more accurate search results, because it has an option to calculate over spheroid surface rather than simple sphere. This effect will be more noticeable when, suppose, you want to have also a distance to the center of searching radius for all locations.

The lag will be up to several meters if some location nearly fits within 1000 meters radius lookup compared to calculations assuming Earth as an ideal sphere. The bigger radius, the more pronounced effect will be. MySQL doesn’t offers that option. Also, while using MySQL you’ll probably want to put calculating queries into stored procedure to have cleaner look of interaction with DB.

Both DBs will give good results, but it seems that PostgreSQL/PostGIS for the moment might be better, faster and more versatile solution, on the other hand, it has more dependencies to be installed and is a little bit tricky during table’s index creation.

PS

Script used for benchmarking can be found here: https://gist.github.com/2216469