This is an automated email from the ASF dual-hosted git repository.
bchapuis pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/incubator-baremaps.git
The following commit(s) were added to refs/heads/main by this push:
new 3ad2fe3a Optimize vector tile export and log slow queries (#753)
3ad2fe3a is described below
commit 3ad2fe3a0d183204ffdc48b286039632fd09bd36
Author: Bertil Chapuis <[email protected]>
AuthorDate: Thu Aug 24 11:19:23 2023 +0200
Optimize vector tile export and log slow queries (#753)
* Simplify the TileStore interface
* Cache the AST of the SQL queries
* Log queries taking more than 10sec to execute
* Remove boundaries at low zoom levels
* Use simplified waterway
---
.run/basemap-mbtiles.run.xml | 7 ++++-
.../baremaps/benchmarks/MBTilesBenchmark.java | 23 -----------------
.../org/apache/baremaps/tilestore/TileStore.java | 30 ----------------------
.../baremaps/tilestore/mbtiles/MBTilesStore.java | 21 ---------------
.../baremaps/tilestore/postgres/PostgresQuery.java | 4 ++-
.../tilestore/postgres/PostgresTileStore.java | 10 +++++++-
.../org/apache/baremaps/utils/PostgresUtils.java | 13 +++++++---
.../org/apache/baremaps/utils/SqliteUtils.java | 5 ++--
.../apache/baremaps/workflow/WorkflowContext.java | 2 +-
.../baremaps/workflow/tasks/ExportVectorTiles.java | 2 +-
basemap/layers/boundary/tileset.js | 2 +-
basemap/layers/waterway/tileset.js | 4 +--
12 files changed, 34 insertions(+), 89 deletions(-)
diff --git a/.run/basemap-mbtiles.run.xml b/.run/basemap-mbtiles.run.xml
index 6a57530d..fd32451f 100644
--- a/.run/basemap-mbtiles.run.xml
+++ b/.run/basemap-mbtiles.run.xml
@@ -2,7 +2,7 @@
<configuration default="false" name="basemap-mbtiles" type="Application"
factoryName="Application">
<option name="MAIN_CLASS_NAME" value="org.apache.baremaps.cli.Baremaps" />
<module name="baremaps-cli" />
- <option name="PROGRAM_PARAMETERS" value="map mbtiles --mbtiles
$USER_HOME$/Datasets/Baremaps/tiles.mbtiles --tilejson tileset.js --style
style.js" />
+ <option name="PROGRAM_PARAMETERS" value="map mbtiles --mbtiles
tiles.mbtiles --tilejson tileset.js --style style.js" />
<option name="WORKING_DIRECTORY" value="$PROJECT_DIR$/basemap" />
<extension name="coverage">
<pattern>
@@ -10,6 +10,11 @@
<option name="ENABLED" value="true" />
</pattern>
</extension>
+ <extension
name="software.aws.toolkits.jetbrains.core.execution.JavaAwsConnectionExtension">
+ <option name="credential" />
+ <option name="region" />
+ <option name="useCurrentConnection" value="false" />
+ </extension>
<method v="2">
<option name="Make" enabled="true" />
</method>
diff --git
a/baremaps-benchmark/src/main/java/org/apache/baremaps/benchmarks/MBTilesBenchmark.java
b/baremaps-benchmark/src/main/java/org/apache/baremaps/benchmarks/MBTilesBenchmark.java
index 8d170729..18435fb3 100644
---
a/baremaps-benchmark/src/main/java/org/apache/baremaps/benchmarks/MBTilesBenchmark.java
+++
b/baremaps-benchmark/src/main/java/org/apache/baremaps/benchmarks/MBTilesBenchmark.java
@@ -18,7 +18,6 @@ import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.security.SecureRandom;
-import java.util.ArrayList;
import java.util.concurrent.TimeUnit;
import org.apache.baremaps.tilestore.TileCoord;
import org.apache.baremaps.tilestore.TileStoreException;
@@ -66,28 +65,6 @@ public class MBTilesBenchmark {
}
}
- @Benchmark
- @BenchmarkMode(Mode.SingleShotTime)
- public void writeMBTilesBatch(MBTilesBenchmark benchmark) throws
TileStoreException {
- var coords = new ArrayList<TileCoord>();
- var buffers = new ArrayList<ByteBuffer>();
- for (int i = 0; i < benchmark.iterations; i++) {
- var bytes = new byte[1 << 16];
- random.nextBytes(bytes);
- coords.add(new TileCoord(0, 0, i));
- buffers.add(ByteBuffer.wrap(bytes));
- if (coords.size() == 100) {
- random.nextBytes(bytes);
- mbTilesStore.write(coords, buffers);
- coords.clear();
- buffers.clear();
- }
- }
- mbTilesStore.write(coords, buffers);
- coords.clear();
- buffers.clear();
- }
-
public static void main(String[] args) throws RunnerException {
Options opt =
new
OptionsBuilder().include(MBTilesBenchmark.class.getSimpleName()).forks(1).build();
diff --git
a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/TileStore.java
b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/TileStore.java
index 926fe088..c7288383 100644
--- a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/TileStore.java
+++ b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/TileStore.java
@@ -15,8 +15,6 @@ package org.apache.baremaps.tilestore;
import java.nio.ByteBuffer;
-import java.util.ArrayList;
-import java.util.List;
/** Represents a store for tiles. */
public interface TileStore {
@@ -30,21 +28,6 @@ public interface TileStore {
*/
ByteBuffer read(TileCoord tileCoord) throws TileStoreException;
- /**
- * Reads the content of several tiles.
- *
- * @param tileCoords the tile coordinates
- * @return the content of the tiles
- * @throws TileStoreException
- */
- default List<ByteBuffer> read(List<TileCoord> tileCoords) throws
TileStoreException {
- var blobs = new ArrayList<ByteBuffer>(tileCoords.size());
- for (var tileCoord : tileCoords) {
- blobs.add(read(tileCoord));
- }
- return blobs;
- }
-
/**
* Writes the content of a tile.
*
@@ -54,19 +37,6 @@ public interface TileStore {
*/
void write(TileCoord tileCoord, ByteBuffer blob) throws TileStoreException;
- /**
- * Writes the content of several tiles.
- *
- * @param tileCoords the tile coordinates
- * @param blobs the content of the tiles
- * @throws TileStoreException
- */
- default void write(List<TileCoord> tileCoords, List<ByteBuffer> blobs)
throws TileStoreException {
- for (int i = 0; i < tileCoords.size(); i++) {
- write(tileCoords.get(i), blobs.get(i));
- }
- }
-
/**
* Deletes the content of a tile.
*
diff --git
a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/mbtiles/MBTilesStore.java
b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/mbtiles/MBTilesStore.java
index 847a2c9a..a5cb03e0 100644
---
a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/mbtiles/MBTilesStore.java
+++
b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/mbtiles/MBTilesStore.java
@@ -22,7 +22,6 @@ import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
-import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.baremaps.tilestore.TileCoord;
@@ -106,26 +105,6 @@ public class MBTilesStore implements TileStore {
}
}
- /** {@inheritDoc} */
- @Override
- public void write(List<TileCoord> tileCoords, List<ByteBuffer> blobs) throws
TileStoreException {
- try (Connection connection = dataSource.getConnection();
- PreparedStatement statement =
connection.prepareStatement(INSERT_TILE)) {
- for (int i = 0; i < tileCoords.size(); i++) {
- TileCoord tileCoord = tileCoords.get(i);
- ByteBuffer blob = blobs.get(i);
- statement.setInt(1, tileCoord.z());
- statement.setInt(2, tileCoord.x());
- statement.setInt(3, reverseY(tileCoord.y(), tileCoord.z()));
- statement.setBytes(4, blob.array());
- statement.addBatch();
- }
- statement.executeBatch();
- } catch (SQLException e) {
- throw new TileStoreException(e);
- }
- }
-
/** {@inheritDoc} */
@Override
public void delete(TileCoord tileCoord) throws TileStoreException {
diff --git
a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresQuery.java
b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresQuery.java
index 5047ace8..542ca854 100644
---
a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresQuery.java
+++
b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresQuery.java
@@ -29,6 +29,7 @@ public class PostgresQuery {
private final Integer minzoom;
private final Integer maxzoom;
private final String sql;
+ private final PlainSelect ast;
/**
* Constructs a {@code PostgresQuery}.
@@ -43,6 +44,7 @@ public class PostgresQuery {
this.minzoom = minzoom;
this.maxzoom = maxzoom;
this.sql = sql;
+ this.ast = parse(sql);
}
/**
@@ -87,7 +89,7 @@ public class PostgresQuery {
* @return the AST
*/
public PlainSelect getAst() {
- return parse(sql);
+ return ast;
}
private PlainSelect parse(String query) {
diff --git
a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java
b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java
index 8f531921..7b4f75fb 100644
---
a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java
+++
b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java
@@ -100,14 +100,15 @@ public class PostgresTileStore implements TileStore {
/** {@inheritDoc} */
@Override
public ByteBuffer read(TileCoord tileCoord) throws TileStoreException {
+ String sql = withQuery(tileCoord);
try (Connection connection = datasource.getConnection();
Statement statement = connection.createStatement();
ByteArrayOutputStream data = new ByteArrayOutputStream()) {
int length = 0;
if (queries.stream().anyMatch(query -> zoomPredicate(query,
tileCoord.z()))) {
- String sql = withQuery(tileCoord);
logger.debug("Executing query: {}", sql);
+ long start = System.currentTimeMillis();
try (GZIPOutputStream gzip = new GZIPOutputStream(data);
ResultSet resultSet = statement.executeQuery(sql)) {
while (resultSet.next()) {
@@ -116,6 +117,13 @@ public class PostgresTileStore implements TileStore {
gzip.write(bytes);
}
}
+ long stop = System.currentTimeMillis();
+ long duration = stop - start;
+
+ // Log slow queries (> 10s)
+ if (duration > 10_000) {
+ logger.warn("Executed query for tile {} in {} ms: {}", tileCoord,
duration, sql);
+ }
}
if (length > 0) {
diff --git
a/baremaps-core/src/main/java/org/apache/baremaps/utils/PostgresUtils.java
b/baremaps-core/src/main/java/org/apache/baremaps/utils/PostgresUtils.java
index ab5cf6e3..e1768a15 100644
--- a/baremaps-core/src/main/java/org/apache/baremaps/utils/PostgresUtils.java
+++ b/baremaps-core/src/main/java/org/apache/baremaps/utils/PostgresUtils.java
@@ -23,13 +23,18 @@ import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
+import javax.sql.DataSource;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
/** A helper class for creating data sources and executing queries. */
public final class PostgresUtils {
+ private static final Logger logger =
LoggerFactory.getLogger(PostgresUtils.class);
+
private PostgresUtils() {}
- public static HikariDataSource createDataSource(String host, Integer port,
String database,
+ public static DataSource createDataSource(String host, Integer port, String
database,
String username, String password) {
return createDataSource(
String.format("jdbc:postgresql://%s:%s/%s?&user=%s&password=%s", host,
port,
@@ -43,8 +48,8 @@ public final class PostgresUtils {
* @param url the JDBC url
* @return the data source
*/
- public static HikariDataSource createDataSource(String url) {
- return createDataSource(url, Runtime.getRuntime().availableProcessors());
+ public static DataSource createDataSource(String url) {
+ return createDataSource(url, Runtime.getRuntime().availableProcessors() *
2);
}
/**
@@ -54,7 +59,7 @@ public final class PostgresUtils {
* @param poolSize the pool size
* @return the data source
*/
- public static HikariDataSource createDataSource(String url, int poolSize) {
+ public static DataSource createDataSource(String url, int poolSize) {
if (poolSize < 1) {
throw new IllegalArgumentException("PoolSize cannot be inferior to 1");
}
diff --git
a/baremaps-core/src/main/java/org/apache/baremaps/utils/SqliteUtils.java
b/baremaps-core/src/main/java/org/apache/baremaps/utils/SqliteUtils.java
index 35e980ae..d2403146 100644
--- a/baremaps-core/src/main/java/org/apache/baremaps/utils/SqliteUtils.java
+++ b/baremaps-core/src/main/java/org/apache/baremaps/utils/SqliteUtils.java
@@ -25,6 +25,7 @@ import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
+import javax.sql.DataSource;
import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteConfig.JournalMode;
import org.sqlite.SQLiteConfig.LockingMode;
@@ -43,7 +44,7 @@ public final class SqliteUtils {
* @param path the path to the SQLite database
* @return the SQLite data source
*/
- public static HikariDataSource createDataSource(Path path, boolean readOnly)
{
+ public static DataSource createDataSource(Path path, boolean readOnly) {
var sqliteConfig = new SQLiteConfig();
sqliteConfig.setReadOnly(readOnly);
sqliteConfig.setCacheSize(1000000);
@@ -57,8 +58,6 @@ public final class SqliteUtils {
sqliteDataSource.setConfig(sqliteConfig);
sqliteDataSource.setUrl("jdbc:sqlite:" + path.toAbsolutePath());
- System.out.println(path.toAbsolutePath());
-
var hikariConfig = new HikariConfig();
hikariConfig.setDataSource(sqliteDataSource);
hikariConfig.setMaximumPoolSize(readOnly ?
Runtime.getRuntime().availableProcessors() : 1);
diff --git
a/baremaps-core/src/main/java/org/apache/baremaps/workflow/WorkflowContext.java
b/baremaps-core/src/main/java/org/apache/baremaps/workflow/WorkflowContext.java
index eff85f26..b03060ca 100644
---
a/baremaps-core/src/main/java/org/apache/baremaps/workflow/WorkflowContext.java
+++
b/baremaps-core/src/main/java/org/apache/baremaps/workflow/WorkflowContext.java
@@ -33,7 +33,7 @@ public class WorkflowContext {
* @return the data source
*/
public DataSource getDataSource(String database) {
- return dataSources.computeIfAbsent(database, d ->
PostgresUtils.createDataSource(d));
+ return dataSources.computeIfAbsent(database,
PostgresUtils::createDataSource);
}
}
diff --git
a/baremaps-core/src/main/java/org/apache/baremaps/workflow/tasks/ExportVectorTiles.java
b/baremaps-core/src/main/java/org/apache/baremaps/workflow/tasks/ExportVectorTiles.java
index 07c2a9ae..5bced5a8 100644
---
a/baremaps-core/src/main/java/org/apache/baremaps/workflow/tasks/ExportVectorTiles.java
+++
b/baremaps-core/src/main/java/org/apache/baremaps/workflow/tasks/ExportVectorTiles.java
@@ -74,7 +74,7 @@ public record ExportVectorTiles(
StreamUtils.stream(TileCoord.iterator(envelope, tileset.getMinzoom(),
tileset.getMaxzoom()))
.peek(new ProgressLogger<>(count, 5000));
- StreamUtils.batch(stream, 10).forEach(new TileChannel(sourceTileStore,
targetTileStore));
+ StreamUtils.batch(stream).forEach(new TileChannel(sourceTileStore,
targetTileStore));
}
private TileStore sourceTileStore(Tileset tileset, DataSource datasource) {
diff --git a/basemap/layers/boundary/tileset.js
b/basemap/layers/boundary/tileset.js
index 75525332..8529b222 100644
--- a/basemap/layers/boundary/tileset.js
+++ b/basemap/layers/boundary/tileset.js
@@ -13,7 +13,7 @@ export default {
id: 'boundary',
queries: [
{
- minzoom: 10,
+ minzoom: 13,
maxzoom: 20,
sql:
"SELECT id, tags, geom FROM osm_ways WHERE tags ? 'boundary'",
diff --git a/basemap/layers/waterway/tileset.js
b/basemap/layers/waterway/tileset.js
index 177c4a37..81940686 100644
--- a/basemap/layers/waterway/tileset.js
+++ b/basemap/layers/waterway/tileset.js
@@ -19,11 +19,11 @@ export default {
},
{
"minzoom": 10,
- "maxzoom": 12,
+ "maxzoom": 13,
"sql": "SELECT id, tags, geom FROM osm_waterway_z$zoom WHERE tags
->> 'waterway' IN ('river', 'stream')"
},
{
- "minzoom": 12,
+ "minzoom": 13,
"maxzoom": 20,
"sql": "SELECT id, tags, geom FROM osm_ways WHERE tags ?
'waterway'"
}
