commit efa490efdfd8669b7ca246f142e9c8170319753c Author: Karsten Loesing <karsten.loesing@gmx.net> Date: Mon Jan 27 21:12:21 2020 +0100 Extract directory authority bytes per day. Implements #33065. --- CHANGELOG.md | 1 + .../stats/bwhist/RelayDescriptorDatabaseImporter.java | 6 ++++-- src/main/sql/bwhist/tordir.sql | 18 +++++++++++++----- 3 files changed, 18 insertions(+), 7 deletions(-) diff --git a/CHANGELOG.md b/CHANGELOG.md index 5731bad..43b7e75 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -4,6 +4,7 @@ - Improve runtime performance of the hidserv module by storing extrapolated statistics even if computed network fractions are zero, to avoid re-processing these statistics over and over. + - Extract directory authority bytes per day in the bwhist module. * Minor changes - Make Jetty host configurable. diff --git a/src/main/java/org/torproject/metrics/stats/bwhist/RelayDescriptorDatabaseImporter.java b/src/main/java/org/torproject/metrics/stats/bwhist/RelayDescriptorDatabaseImporter.java index 91cd559..7b08f77 100644 --- a/src/main/java/org/torproject/metrics/stats/bwhist/RelayDescriptorDatabaseImporter.java +++ b/src/main/java/org/torproject/metrics/stats/bwhist/RelayDescriptorDatabaseImporter.java @@ -583,13 +583,13 @@ public final class RelayDescriptorDatabaseImporter { List<String[]> queryBandwidth() throws SQLException { List<String[]> statistics = new ArrayList<>(); String columns = "date, isexit, isguard, bwread, bwwrite, dirread, " - + "dirwrite"; + + "dirwrite, dirauthread, dirauthwrite"; statistics.add(columns.split(", ")); Statement st = this.conn.createStatement(); String queryString = "SELECT " + columns + " FROM stats_bandwidth"; try (ResultSet rs = st.executeQuery(queryString)) { while (rs.next()) { - String[] outputLine = new String[7]; + String[] outputLine = new String[9]; outputLine[0] = rs.getDate("date").toLocalDate().toString(); outputLine[1] = getBooleanFromResultSet(rs, "isexit"); outputLine[2] = getBooleanFromResultSet(rs, "isguard"); @@ -597,6 +597,8 @@ public final class RelayDescriptorDatabaseImporter { outputLine[4] = getLongFromResultSet(rs, "bwwrite"); outputLine[5] = getLongFromResultSet(rs, "dirread"); outputLine[6] = getLongFromResultSet(rs, "dirwrite"); + outputLine[7] = getLongFromResultSet(rs, "dirauthread"); + outputLine[8] = getLongFromResultSet(rs, "dirauthwrite"); statistics.add(outputLine); } } diff --git a/src/main/sql/bwhist/tordir.sql b/src/main/sql/bwhist/tordir.sql index a2c3b65..fad5d2f 100644 --- a/src/main/sql/bwhist/tordir.sql +++ b/src/main/sql/bwhist/tordir.sql @@ -66,7 +66,9 @@ CREATE TABLE bwhist_flags ( CREATE TABLE user_stats ( date DATE NOT NULL, dw BIGINT, - dr BIGINT + dr BIGINT, + daw BIGINT, + dar BIGINT ); -- Dates to be included in the next refresh run. @@ -199,13 +201,17 @@ CREATE OR REPLACE FUNCTION refresh_user_stats() RETURNS INTEGER AS $$ DELETE FROM user_stats WHERE date IN (SELECT date FROM updates); -- Now insert new user statistics. EXECUTE ' - INSERT INTO user_stats (date, dw, dr) + INSERT INTO user_stats (date, dw, dr, daw, dar) SELECT bwhist_by_relay.date AS date, SUM(CASE WHEN authority IS NOT NULL THEN NULL ELSE dirwritten END) AS dw, SUM(CASE WHEN authority IS NOT NULL - THEN NULL ELSE dirread END) AS dr + THEN NULL ELSE dirread END) AS dr, + SUM(CASE WHEN authority IS NULL + THEN NULL ELSE dirwritten END) AS daw, + SUM(CASE WHEN authority IS NULL + THEN NULL ELSE dirread END) AS dar FROM ( -- Retrieve aggregate bandwidth histories of all relays in the given -- time frame. @@ -263,14 +269,16 @@ CREATE VIEW stats_bandwidth AS (SELECT date, isexit, isguard, read / 86400 AS bwread, written / 86400 AS bwwrite, - NULL AS dirread, NULL AS dirwrite + NULL AS dirread, NULL AS dirwrite, NULL AS dirauthread, NULL AS dirauthwrite FROM bwhist_flags WHERE date < current_date - 2) UNION ALL (SELECT date, NULL AS isexit, NULL AS isguard, NULL AS bwread, NULL AS bwwrite, FLOOR(CAST(dr AS NUMERIC) / CAST(86400 AS NUMERIC)) AS dirread, - FLOOR(CAST(dw AS NUMERIC) / CAST(86400 AS NUMERIC)) AS dirwrite + FLOOR(CAST(dw AS NUMERIC) / CAST(86400 AS NUMERIC)) AS dirwrite, + FLOOR(CAST(dar AS NUMERIC) / CAST(86400 AS NUMERIC)) AS dirauthread, + FLOOR(CAST(daw AS NUMERIC) / CAST(86400 AS NUMERIC)) AS dirauthwrite FROM user_stats WHERE date < current_date - 2) ORDER BY date, isexit, isguard;