Skip to content

Instantly share code, notes, and snippets.

@paolobueno

paolobueno/benchmarks.sql

Created March 14, 2018 12:40
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save paolobueno/1488f597793937d2f8576c6bd19e8042 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save paolobueno/1488f597793937d2f8576c6bd19e8042 to your computer and use it in GitHub Desktop.
Download ZIP
app metrics benchmarks
Raw
benchmarks.sql
/*
Tested with data generated by
autocannon -a 150000 -m POST -I -b '{"clientId":"[<id>]","data":{"app":{"appId":"com.example.someApp","sdkVersion":"2.4.6","appVersion":"256"},"device":{"platform":"android","platformVersion":"27"}}}' http://localhost:3000/metrics
Thus creating 150k records.
This uses generated clientIds and the rest of the data is constant, thus it doesn't match very well the expected cardinality,
I'd like to redo these tests with better random data.
For example we wouldn't have unique clientIds per record, and have some variance in device platform, and some less in app info, and also a much bigger spread in event_times.
This probably leads to some skew both in the queries and in the PK/index sizes for now.
Here are the insert performance data:
jsonb with PK:
Stat Avg Stdev Max
Latency (ms) 3.58 5.39 240.86
Req/Sec 2381.08 909.05 4146
Bytes/Sec 153 kB 58.2 kB 265 kB
jsonb without PK:
Stat Avg Stdev Max
Latency (ms) 3.74 5.81 302.88
Req/Sec 2307.85 929.5 4026
Bytes/Sec 148 kB 59.5 kB 258 kB
relational with PKs:
Stat Avg Stdev Max
Latency (ms) 3.42 2.94 210.82
Req/Sec 2500.27 618.5 3186
Bytes/Sec 160 kB 39.7 kB 204 kB
It seems that the bottleneck is in the CPU, so running autocannon with -a <n_of_records> is much faster than -c <concurrency> -d <seconds>.
Also with -a there are no non-200 responses on all implementations.
I wanna look at insertion speeds with a round robin load balancer and multiple instances of the golang server.
*/
-- table size query from https://wiki.postgresql.org/wiki/Disk_Usage
SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r' and nspname = 'public'
) a
) a;
/*
+-------+----------------+------------------------+----------------+---------------+---------------+---------------+---------------+---------+---------+---------+---------+
| oid | table_schema | table_name | row_estimate | total_bytes | index_bytes | toast_bytes | table_bytes | total | index | toast | table |
|-------+----------------+------------------------+----------------+---------------+---------------+---------------+---------------+---------+---------+---------+---------|
| 16386 | public | mobilemetrics_app | 150000.0 | 70115328 | 27303936 | <null> | 42811392 | 67 MB | 26 MB | <null> | 41 MB |
| 16395 | public | mobilemetrics_device | 150000.0 | 62636032 | 25862144 | <null> | 36773888 | 60 MB | 25 MB | <null> | 35 MB |
| 16404 | public | mobilemetrics_security | 0.0 | 81920 | 81920 | <null> | 0 | 80 kB | 80 kB | <null> | 0 bytes |
| 16444 | public | mobileappmetrics | 126079.0 | 73785344 | 18161664 | 16384 | 55607296 | 70 MB | 17 MB | 16 kB | 53 MB |
+-------+----------------+------------------------+----------------+---------------+---------------+---------------+---------------+---------+---------+---------+---------+
*/
-- Original query by Ali
EXPLAIN ANALYZE SELECT
count(distinct latestEntryInTimeRange.clientId)
FROM
(select entry.clientId, entry.event_time, entry.data
from mobileAppMetrics entry
inner join
(select clientId, data->'app'->>'appId' as appId, max(event_time) as latestEntryTime
from mobileAppMetrics
where event_time between '2018-03-14 08:00:00+00' AND '2018-03-14 20:10:00+00'
and data->'app'->>'appId' like 'com.example.someApp'
and data->'device'->>'platform' like 'android'
group by clientId, appId
)
latestEntry
on entry.clientId = latestEntry.clientId and entry.data->'app'->>'appId' = latestEntry.appId and entry.event_time = latestEntryTime
) latestEntryInTimeRange
where latestEntryInTimeRange.data->'app' ? 'appId';
/*
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| QUERY PLAN
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Aggregate (cost=23419.91..23419.92 rows=1 width=8) (actual time=11780.281..11780.281 rows=1 loops=1)
| -> Nested Loop (cost=23386.34..23419.91 rows=1 width=81) (actual time=4345.876..7627.462 rows=150000 loops=1)
| -> GroupAggregate (cost=23385.92..23386.02 rows=4 width=121) (actual time=4345.802..4454.237 rows=150000 loops=1)
| Group Key: mobileappmetrics.clientid, (((mobileappmetrics.data -> 'app'::text) ->> 'appId'::text))
| -> Sort (cost=23385.92..23385.93 rows=4 width=121) (actual time=4345.794..4376.307 rows=150000 loops=1)
| Sort Key: mobileappmetrics.clientid, (((mobileappmetrics.data -> 'app'::text) ->> 'appId'::text))
| Sort Method: external sort Disk: 17896kB
| -> Seq Scan on mobileappmetrics (cost=0.00..23385.88 rows=4 width=121) (actual time=0.076..204.847 rows=150000 loops=1)
| Filter: ((event_time >= '2018-03-14 08:00:00+00'::timestamp with time zone) AND (event_time <= '2018-03-14 20:10:00+00'::timestamp with time zone) AND (((data -> 'app'::text) ->> 'appId'::text) ~~ 'com.example.someApp'::text) AND (((data -> 'dev
| -> Index Scan using mobileappmetrics_pkey on mobileappmetrics entry (cost=0.42..8.45 rows=1 width=829) (actual time=0.020..0.021 rows=1 loops=150000)
| Index Cond: ((clientid = mobileappmetrics.clientid) AND (event_time = (max(mobileappmetrics.event_time))))
| Filter: (((data -> 'app'::text) ? 'appId'::text) AND ((((mobileappmetrics.data -> 'app'::text) ->> 'appId'::text)) = ((data -> 'app'::text) ->> 'appId'::text)))
| Planning time: 0.470 ms
| Execution time: 11782.979 ms
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/
-- CTE Query
EXPLAIN ANALYZE WITH a as (
select clientId, event_time
FROM mobileAppMetrics
WHERE data->'app'->>'appId' like 'com.example.someApp'
and data->'device'->>'platform' like 'android'
)
SELECT count(distinct a.clientId)
FROM a
INNER JOIN (select clientId, max(event_time) as ltime FROM a where event_time between '2018-03-14 08:00:00+00' AND '2018-03-14 20:10:00+00' GROUP BY clientId) as latest
ON a.clientId = latest.clientId
AND a.event_time = latest.ltime
/*
With PRIMARY KEY(clientid, event_time)
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Aggregate (cost=22635.81..22635.82 rows=1 width=8) (actual time=3560.881..3560.881 rows=1 loops=1) |
| CTE a |
| -> Seq Scan on mobileappmetrics (cost=0.00..22635.55 rows=4 width=89) (actual time=0.061..150.962 rows=150000 loops=1) |
| Filter: ((((data -> 'app'::text) ->> 'appId'::text) ~~ 'com.example.someApp'::text) AND (((data -> 'device'::text) ->> 'platform'::text) ~~ 'android'::text)) |
| -> Hash Join (cost=0.14..0.26 rows=1 width=324) (actual time=378.267..516.187 rows=150000 loops=1) |
| Hash Cond: ((a.clientid = a_1.clientid) AND (a.event_time = (max(a_1.event_time)))) |
| -> CTE Scan on a (cost=0.00..0.08 rows=4 width=332) (actual time=0.065..15.654 rows=150000 loops=1) |
| -> Hash (cost=0.12..0.12 rows=1 width=332) (actual time=378.184..378.184 rows=150000 loops=1) |
| Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB |
| -> HashAggregate (cost=0.11..0.12 rows=1 width=332) (actual time=291.319..327.576 rows=150000 loops=1) |
| Group Key: a_1.clientid |
| -> CTE Scan on a a_1 (cost=0.00..0.10 rows=1 width=332) (actual time=0.004..221.413 rows=150000 loops=1) |
| Filter: ((event_time >= '2018-03-14 08:00:00+00'::timestamp with time zone) AND (event_time <= '2018-03-14 20:10:00+00'::timestamp with time zone)) |
| Planning time: 0.348 ms |
| Execution time: 3563.164 ms |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Without the PK the query takes forever to run because of a n^2 loop
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Aggregate (cost=40766.71..40766.72 rows=1 width=8) |
| CTE a |
| -> Seq Scan on mobileappmetrics (cost=0.00..40766.50 rows=3 width=89) |
| Filter: ((((data -> 'app'::text) ->> 'appId'::text) ~~ 'com.example.someApp'::text) AND (((data -> 'device'::text) ->> 'platform'::text) ~~ 'android'::text)) |
| -> Nested Loop (cost=0.08..0.21 rows=1 width=324) |
| Join Filter: ((a.clientid = a_1.clientid) AND (a.event_time = (max(a_1.event_time)))) |
| -> HashAggregate (cost=0.08..0.09 rows=1 width=332) |
| Group Key: a_1.clientid |
| -> CTE Scan on a a_1 (cost=0.00..0.08 rows=1 width=332) |
| Filter: ((event_time >= '2018-03-14 08:00:00+00'::timestamp with time zone) AND (event_time <= '2018-03-14 20:10:00+00'::timestamp with time zone)) |
| -> CTE Scan on a (cost=0.00..0.06 rows=3 width=332) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
*/
-- Using @> instead of -> + ->>
EXPLAIN WITH a as (
select clientId, event_time
FROM mobileAppMetrics
WHERE data @> '{"app":{"appId": "com.example.someApp"}}'
and data @>'{"device":{"platform": "android"}}'
)
SELECT count(distinct a.clientId)
FROM a
INNER JOIN (select clientId, max(event_time) as ltime FROM a where event_time between '2018-03-14 08:00:00+00' AND '2018-03-14 20:10:00+00' GROUP BY clientId) as latest
ON a.clientId = latest.clientId
AND a.event_time = latest.ltime
/*
^ Takes forever to run, doesn't use index either
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Aggregate (cost=21135.02..21135.03 rows=1 width=8) |
| CTE a |
| -> Seq Scan on mobileappmetrics (cost=0.00..21134.93 rows=1 width=89) |
| Filter: ((data @> '{"app": {"appId": "com.example.someApp"}}'::jsonb) AND (data @> '{"device": {"platform": "android"}}'::jsonb)) |
| -> Nested Loop (cost=0.03..0.09 rows=1 width=324) |
| Join Filter: ((a.clientid = a_1.clientid) AND (a.event_time = (max(a_1.event_time)))) |
| -> CTE Scan on a (cost=0.00..0.02 rows=1 width=332) |
| -> HashAggregate (cost=0.03..0.04 rows=1 width=332) |
| Group Key: a_1.clientid |
| -> CTE Scan on a a_1 (cost=0.00..0.03 rows=1 width=332) |
| Filter: ((event_time >= '2018-03-14 08:00:00+00'::timestamp with time zone) AND (event_time <= '2018-03-14 20:10:00+00'::timestamp with time zone)) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
*/
-- CTE query on relational tables
EXPLAIN ANALYZE WITH a as (
select app.clientId, app.event_time
FROM mobilemetrics_app as app
INNER JOIN mobilemetrics_device as device ON app.clientId = device.clientId
WHERE app.app_id = 'com.example.someApp'
AND device.platform = 'android'
)
SELECT count(distinct a.clientId)
FROM a
INNER JOIN (select clientId, max(event_time) as ltime FROM a where event_time between '2018-03-14 08:00:00+00' AND '2018-03-14 20:10:00+00' GROUP BY clientId) as latest
ON a.clientId = latest.clientId
AND a.event_time = latest.ltime
/*
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Aggregate (cost=42207.46..42207.47 rows=1 width=8) (actual time=3586.511..3586.511 rows=1 loops=1) |
| CTE a |
| -> Hash Join (cost=10522.00..27194.09 rows=149970 width=89) (actual time=72.553..226.526 rows=150000 loops=1) |
| Hash Cond: (app.clientid = device.clientid) |
| -> Seq Scan on mobilemetrics_app app (cost=0.00..8165.00 rows=149970 width=89) (actual time=0.010..50.066 rows=150000 loops=1) |
| Filter: ((event_time >= '2018-03-14 08:00:00+00'::timestamp with time zone) AND (event_time <= '2018-03-14 20:10:00+00'::timestamp with time zone) AND (app_id = 'com.example.someApp'::bpchar)) |
| -> Hash (cost=6596.00..6596.00 rows=150000 width=81) (actual time=72.401..72.401 rows=150000 loops=1) |
| Buckets: 32768 Batches: 8 Memory Usage: 2313kB |
| -> Seq Scan on mobilemetrics_device device (cost=0.00..6596.00 rows=150000 width=81) (actual time=0.005..35.787 rows=150000 loops=1) |
| Filter: (platform = 'android'::bpchar) |
| -> Hash Join (cost=3756.25..15011.50 rows=750 width=324) (actual time=414.280..548.130 rows=150000 loops=1) |
| Hash Cond: ((a.clientid = a_1.clientid) AND (a.event_time = (max(a_1.event_time)))) |
| -> CTE Scan on a (cost=0.00..2999.40 rows=149970 width=332) (actual time=72.555..88.043 rows=150000 loops=1) |
| -> Hash (cost=3753.25..3753.25 rows=200 width=332) (actual time=341.593..341.593 rows=150000 loops=1) |
| Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB |
| -> HashAggregate (cost=3749.25..3751.25 rows=200 width=332) (actual time=264.326..294.075 rows=150000 loops=1) |
| Group Key: a_1.clientid |
| -> CTE Scan on a a_1 (cost=0.00..2999.40 rows=149970 width=332) (actual time=0.001..199.939 rows=150000 loops=1) |
| Planning time: 0.314 ms |
| Execution time: 3589.985 ms |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Adding hash indexes on device.platform and app.appId made the query planner think it would be faster but was slower for some reason
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Aggregate (cost=2249.03..2249.04 rows=1 width=8) (actual time=6566.892..6566.893 rows=1 loops=1) |
| CTE a |
| -> Hash Join (cost=1101.74..2218.79 rows=573 width=332) (actual time=137.955..328.806 rows=150000 loops=1) |
| Hash Cond: (device.clientid = app.clientid) |
| -> Bitmap Heap Scan on mobilemetrics_device device (cost=22.74..1119.02 rows=354 width=324) (actual time=13.083..74.084 rows=150000 loops=1) |
| Recheck Cond: (platform = 'android'::bpchar) |
| Heap Blocks: exact=4716 |
| -> Bitmap Index Scan on idx_device_platform (cost=0.00..22.66 rows=354 width=0) (actual time=12.557..12.557 rows=150000 loops=1) |
| Index Cond: (platform = 'android'::bpchar) |
| -> Hash (cost=1074.94..1074.94 rows=324 width=332) (actual time=124.760..124.760 rows=150000 loops=1) |
| Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB |
| -> Bitmap Heap Scan on mobilemetrics_app app (cost=26.51..1074.94 rows=324 width=332) (actual time=9.685..84.460 rows=150000 loops=1) |
| Recheck Cond: (app_id = 'com.example.someApp'::bpchar) |
| Heap Blocks: exact=5888 |
| -> Bitmap Index Scan on idx_app_app_id (cost=0.00..26.43 rows=324 width=0) (actual time=9.046..9.046 rows=150000 loops=1) |
| Index Cond: (app_id = 'com.example.someApp'::bpchar) |
| -> Hash Join (cost=14.48..30.24 rows=1 width=324) (actual time=3461.678..3592.076 rows=150000 loops=1) |
| Hash Cond: ((a.clientid = a_1.clientid) AND (a.event_time = (max(a_1.event_time)))) |
| -> CTE Scan on a (cost=0.00..11.46 rows=573 width=332) (actual time=137.958..153.073 rows=150000 loops=1) |
| -> Hash (cost=14.43..14.43 rows=3 width=332) (actual time=3323.597..3323.597 rows=150000 loops=1) |
| Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB |
| -> GroupAggregate (cost=14.35..14.40 rows=3 width=332) (actual time=2583.480..3277.213 rows=150000 loops=1) |
| Group Key: a_1.clientid |
| -> Sort (cost=14.35..14.36 rows=3 width=332) (actual time=2583.468..3231.360 rows=150000 loops=1) |
| Sort Key: a_1.clientid |
| Sort Method: external merge Disk: 15496kB |
| -> CTE Scan on a a_1 (cost=0.00..14.33 rows=3 width=332) (actual time=0.004..251.680 rows=150000 loops=1) |
| Filter: ((event_time >= '2018-03-14 08:00:00+00'::timestamp with time zone) AND (event_time <= '2018-03-14 20:10:00+00'::timestamp with time zone)) |
| Planning time: 0.512 ms |
| Execution time: 6571.462 ms |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
After re-ANALYZE-ing the indexed tables it reverted to a similar plan to the non-indexed one
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Aggregate (cost=34689.21..34689.22 rows=1 width=8) (actual time=3516.218..3516.218 rows=1 loops=1) |
| CTE a |
| -> Hash Join (cost=10522.00..26794.50 rows=150000 width=89) (actual time=68.865..215.899 rows=150000 loops=1) |
| Hash Cond: (app.clientid = device.clientid) |
| -> Seq Scan on mobilemetrics_app app (cost=0.00..7763.00 rows=150000 width=89) (actual time=0.016..40.896 rows=150000 loops=1) |
| Filter: (app_id = 'com.example.someApp'::bpchar) |
| -> Hash (cost=6596.00..6596.00 rows=150000 width=81) (actual time=68.705..68.705 rows=150000 loops=1) |
| Buckets: 32768 Batches: 8 Memory Usage: 2314kB |
| -> Seq Scan on mobilemetrics_device device (cost=0.00..6596.00 rows=150000 width=81) (actual time=0.009..30.588 rows=150000 loops=1) |
| Filter: (platform = 'android'::bpchar) |
| -> Hash Join (cost=3760.57..7892.89 rows=731 width=324) (actual time=416.927..547.799 rows=150000 loops=1) |
| Hash Cond: ((a.clientid = a_1.clientid) AND (a.event_time = (max(a_1.event_time)))) |
| -> CTE Scan on a (cost=0.00..3000.00 rows=150000 width=332) (actual time=68.868..83.907 rows=150000 loops=1) |
| -> Hash (cost=3757.65..3757.65 rows=195 width=332) (actual time=347.977..347.977 rows=150000 loops=1) |
| Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB |
| -> HashAggregate (cost=3753.75..3755.70 rows=195 width=332) (actual time=269.337..299.492 rows=150000 loops=1) |
| Group Key: a_1.clientid |
| -> CTE Scan on a a_1 (cost=0.00..3750.00 rows=750 width=332) (actual time=0.004..206.685 rows=150000 loops=1) |
| Filter: ((event_time >= '2018-03-14 08:00:00+00'::timestamp with time zone) AND (event_time <= '2018-03-14 20:10:00+00'::timestamp with time zone)) |
| Planning time: 1.060 ms |
| Execution time: 3520.005 ms |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment