/**
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Lesser General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
* <p>
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
* <p>
* You should have received a copy of the GNU Lesser General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*
* @author Nuno Oliveira, GeoSolutions S.A.S., Copyright 2016
*/
package org.geowebcache.sqlite;
import org.apache.commons.io.FileUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.geowebcache.storage.BlobStore;
import org.geowebcache.storage.TileObject;
import org.geowebcache.storage.blobstore.file.FileBlobStore;
import java.io.File;
import java.nio.file.Files;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Random;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
/**
* Measures the performance of the {@link SqliteConnectionManager}
* and the {@link MbtilesBlobStore} against using a raw query.
*/
final class SqlitlePerf {
private static Log LOGGER = LogFactory.getLog(SqlitlePerf.class);
// number of workers that will be used to perform the selects
final static int WORKERS = 10;
// number of tiles to store and retrieve
final static int TILES = 1000000;
public static void main(String[] args) throws Exception {
// initiate sqlite drive
Class.forName("org.sqlite.JDBC");
// create the directory that will contain all created files
File rootDirectory = Files.createTempDirectory("gwc-").toFile();
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Root directory '%s'.", rootDirectory));
}
// seeding file system
long[][] tiles = new long[TILES][3];
File seedDirectory = seedFileSystem(rootDirectory, tiles);
fileStore(seedDirectory, tiles);
FileUtils.deleteDirectory(seedDirectory);
// seeding a database with some random tiles
File seedFile = createSeedFile(rootDirectory, tiles);
// select tiles using a raw connection
rawSqlitle(rootDirectory, seedFile, tiles);
// select tiles using the connection manager
pooledSqlitle(rootDirectory, seedFile, tiles);
// select tiles using the mbtiles blobstore
mbtilesStore(rootDirectory, seedFile, tiles);
// cleaning everything
FileUtils.deleteDirectory(rootDirectory);
}
/**
* Select the created tiles using a raw connection.
*/
private static void rawSqlitle(File rootDirectory, File seedFile, long[][] tiles) throws Exception {
// creating a new database by copying the seeded one
File databaseFile = new File(rootDirectory, "raw_perf_test.sqlite");
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Start raw select from file '%s'.", databaseFile));
}
FileUtils.copyFile(seedFile, databaseFile);
// submitting the select tasks
ExecutorService executor = Executors.newFixedThreadPool(WORKERS);
long startTime = System.currentTimeMillis();
Connection connection = DriverManager.getConnection("jdbc:sqlite:" + seedFile.getPath());
for (int i = 0; i < tiles.length; i++) {
long[] tile = tiles[i];
executor.submit((Runnable) () -> getTile(connection, tile));
if (i != 0 && i % 10000 == 0) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(String.format("Submitted %d select tasks.", i));
}
}
}
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(String.format("Submitted %d select tasks.", TILES));
}
// lets wait for the workers to finish
executor.shutdown();
executor.awaitTermination(5, TimeUnit.MINUTES);
// computing some stats
long endTime = System.currentTimeMillis();
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Tiles raw select time '%d'.", endTime - startTime));
}
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Tiles raw selected per second '%f'.", TILES / (float) (endTime - startTime) * 1000));
}
// clean everything
connection.close();
FileUtils.deleteQuietly(databaseFile);
}
/**
* Select the created tiles using a connection provide by the connection manager.
*/
private static void pooledSqlitle(File rootDirectory, File seedFile, long[][] tiles) throws Exception {
// creating a new database by copying the seeded one
File databaseFile = new File(rootDirectory, "pooled_perf_test.sqlite");
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Start pooled select from file '%s'.", databaseFile));
}
FileUtils.copyFile(seedFile, databaseFile);
// submitting the select tasks
ExecutorService executor = Executors.newFixedThreadPool(WORKERS);
SqliteConnectionManager connectionManager = new SqliteConnectionManager(10, 2000);
long startTime = System.currentTimeMillis();
for (int i = 0; i < tiles.length; i++) {
long[] tile = tiles[i];
executor.submit((Runnable) () -> connectionManager.doWork(databaseFile, true, connection -> {
getTile(connection, tile);
}));
if (i != 0 && i % 10000 == 0) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(String.format("Submitted %d select tasks.", i));
}
}
}
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(String.format("Submitted %d select tasks.", TILES));
}
// lets wait for the workers to finish
executor.shutdown();
executor.awaitTermination(5, TimeUnit.MINUTES);
// computing some stats
long endTime = System.currentTimeMillis();
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Tiles pooled select time '%d'.", endTime - startTime));
}
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Tiles pooled selected per second '%f'.", TILES / (float) (endTime - startTime) * 1000));
}
// clean everything
connectionManager.reapAllConnections();
connectionManager.stopPoolReaper();
FileUtils.deleteQuietly(databaseFile);
}
/**
* Retrieve the created tiles using the mbtiles blobstore.
*/
private static void mbtilesStore(File rootDirectory, File seedFile, long[][] tiles) throws Exception {
// creating a new database by copying the seeded one
File databaseFile = new File(rootDirectory, Utils.buildPath("grid", "layer", "image_png", "mbtiles_perf_test.sqlite"));
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Start mbtiles select from file '%s'.", databaseFile));
}
FileUtils.copyFile(seedFile, databaseFile);
// submitting the select tasks
ExecutorService executor = Executors.newFixedThreadPool(WORKERS);
long startTime = System.currentTimeMillis();
// mbtiles store configuration
MbtilesConfiguration configuration = new MbtilesConfiguration();
configuration.setRootDirectory(rootDirectory.getPath());
configuration.setTemplatePath(Utils.buildPath("{grid}", "{layer}", "{format}", "mbtiles_perf_test.sqlite"));
configuration.setUseCreateTime(false);
// instantiate the mbtiles blobstore
SqliteConnectionManager connectionManager = new SqliteConnectionManager(10, 2000);
MbtilesBlobStore mbtilesBlobStore = new MbtilesBlobStore(configuration, connectionManager);
for (int i = 0; i < tiles.length; i++) {
long[] tile = tiles[i];
executor.submit((Runnable) () -> {
TileObject mbtile = TileObject.createQueryTileObject("layer", tile, "grid", "image/png", null);
try {
mbtilesBlobStore.get(mbtile);
} catch (Exception exception) {
throw Utils.exception(exception, "Error retrieving tile '%s'.", mbtile);
}
});
if (i != 0 && i % 10000 == 0) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(String.format("Submitted %d select tasks.", i));
}
}
}
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(String.format("Submitted %d select tasks.", TILES));
}
// lets wait for the workers to finish
executor.shutdown();
executor.awaitTermination(5, TimeUnit.MINUTES);
// computing some stats
long endTime = System.currentTimeMillis();
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Tiles mbtiles blobstore select time '%d'.", endTime - startTime));
}
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Tiles mbtiles blobstore selected per second '%f'.", TILES / (float) (endTime - startTime) * 1000));
}
// clean everything
connectionManager.reapAllConnections();
connectionManager.stopPoolReaper();
FileUtils.deleteQuietly(databaseFile);
}
/**
* Retrieve the created tiles using the file blobstore.
*/
private static void fileStore(File seedDirectory, long[][] tiles) throws Exception {
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Start reading from directory'%s'.", seedDirectory));
}
// submitting the read tasks
ExecutorService executor = Executors.newFixedThreadPool(WORKERS);
long startTime = System.currentTimeMillis();
// instantiate the file blobstore
BlobStore fileBlobStore = new FileBlobStore(seedDirectory.getPath());
for (int i = 0; i < tiles.length; i++) {
long[] tile = tiles[i];
executor.submit((Runnable) () -> {
TileObject mbtile = TileObject.createQueryTileObject("layer", tile, "grid", "image/png", null);
try {
fileBlobStore.get(mbtile);
} catch (Exception exception) {
throw Utils.exception(exception, "Error retrieving tile '%s'.", mbtile);
}
});
if (i != 0 && i % 10000 == 0) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(String.format("Submitted %d read tasks.", i));
}
}
}
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(String.format("Submitted %d read tasks.", TILES));
}
// lets wait for the workers to finish
executor.shutdown();
executor.awaitTermination(5, TimeUnit.MINUTES);
// computing some stats
long endTime = System.currentTimeMillis();
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Tiles file blobstore read time '%d'.", endTime - startTime));
}
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Tiles file blobstore reads per second '%f'.", TILES / (float) (endTime - startTime) * 1000));
}
}
/**
* Store random tiles in the filesystem.
*/
private static File seedFileSystem(File rootDirectory, long[][] tiles) throws Exception {
// creating the root directory where tiles will be saved
File seedDirectory = new File(rootDirectory, "tiles");
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Start seeding file system '%s'.", seedDirectory));
}
BlobStore fileBlobStore = new FileBlobStore(seedDirectory.getPath());
// start seeding the tiles
long startTime = System.currentTimeMillis();
for (int i = 0; i < TILES; i++) {
Tile tile = Tile.random();
tiles[i][0] = tile.x;
tiles[i][1] = tile.y;
tiles[i][2] = tile.z;
fileBlobStore.put(TileObject.createCompleteTileObject("layer",
new long[]{tile.x, tile.y, tile.z},
"epsg:4326",
"image/png",
null,
Utils.byteArrayToResource(tile.data)));
if (i != 0 && i % 10000 == 0) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(String.format("Stored %d tiles.", i));
}
}
}
long endTime = System.currentTimeMillis();
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Insert time '%d' (batch mode).", endTime - startTime));
}
return seedDirectory;
}
/**
* Seeding a file with random tiles.
*/
private static File createSeedFile(File rootDirectory, long[][] tiles) throws Exception {
// creating the database that will be seeded
File seedFile = new File(rootDirectory, "seed_perf_test.sqlite");
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Start seeding file '%s'.", seedFile));
}
Connection connection = DriverManager.getConnection("jdbc:sqlite:" + seedFile.getPath());
String createTableSql = "CREATE TABLE IF NOT EXISTS tiles (zoom_level integer, tile_column integer, " +
"tile_row integer, tile_data blob, CONSTRAINT pk_tiles PRIMARY KEY(zoom_level, tile_column,tile_row));";
executeSql(connection, createTableSql);
// start seeding wrapped in a transaction (improves performance)
long startTime = System.currentTimeMillis();
executeSql(connection, "BEGIN TRANSACTION;");
String sql = "INSERT OR REPLACE INTO tiles VALUES(?, ?, ?, ?);";
// insert the tiles in batches
try (PreparedStatement statement = connection.prepareStatement(sql)) {
for (int i = 0; i < TILES; i++) {
Tile tile = Tile.random();
tiles[i][0] = tile.x;
tiles[i][1] = tile.y;
tiles[i][2] = tile.z;
statement.setLong(1, tile.z);
statement.setLong(2, tile.x);
statement.setLong(3, tile.y);
statement.setBytes(4, tile.data);
statement.addBatch();
if (i != 0 && i % 10000 == 0) {
statement.executeBatch();
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(String.format("Inserted batch %d.", i));
}
}
}
statement.executeBatch();
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(String.format("Inserted batch %d.", TILES));
}
} catch (Exception exception) {
throw Utils.exception(exception, "Error executing SQL '%s'.", sql);
}
// clean everything
executeSql(connection, "END TRANSACTION;");
long endTime = System.currentTimeMillis();
if (LOGGER.isInfoEnabled()) {
LOGGER.info(String.format("Insert time '%d' (batch mode).", endTime - startTime));
}
connection.close();
return seedFile;
}
/**
* Helper method that fetches a tile form the database using the provided connection.
*/
private static byte[] getTile(Connection connection, long[] xyz) {
String sql = "SELECT tile_data FROM tiles WHERE zoom_level = ? AND tile_column = ? AND tile_row = ?;";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setLong(1, xyz[2]);
statement.setLong(2, xyz[0]);
statement.setLong(3, xyz[1]);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next()) {
// the tile exists
byte[] data = resultSet.getBytes(1);
if (data.length != 2024) {
// the data doesn't have the expected size
if (LOGGER.isErrorEnabled()) {
LOGGER.error(String.format("Tile %d-%d-%d data is not valid.", xyz[2], xyz[0], xyz[1]));
}
}
// the tile data looks good
return data;
} else {
// the tile was not found
if (LOGGER.isErrorEnabled()) {
LOGGER.error(String.format("Failed to load tile %d-%d-%d.", xyz[2], xyz[0], xyz[1]));
}
return null;
}
} catch (Exception exception) {
throw Utils.exception(exception, "Error executing SQL '%s'.", sql);
}
}
/**
* Helper method that executes an SQL statement using the provided connection.
*/
private static void executeSql(Connection connection, String sql) {
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.execute();
} catch (Exception exception) {
throw Utils.exception(exception, "Error executing SQL '%s'.", sql);
}
}
/**
* Helper class that stores a tile information.
*/
private final static class Tile {
private final static Random random = new Random();
final long x;
final long y;
final long z;
final byte[] data;
private Tile(long x, long y, long z, byte[] data) {
this.x = x;
this.y = y;
this.z = z;
this.data = data;
}
/**
* Creates a random tile.
*/
static Tile random() {
byte[] data = new byte[2024];
random.nextBytes(data);
return new Tile(random.nextInt(1000000),
random.nextInt(1000000),
random.nextInt(10),
data);
}
}
}