Frontpage is slow

[berekuk]

I occasionally get "over 10 seconds" timeout errors from the frontpage. Fetching the frontpage data is the culprit and it's pretty bad currently:

$ ab -n 10 https://metaforecast.org/api/frontpage
This is ApacheBench, Version 2.3 <$Revision: 1879490 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking metaforecast.org (be patient).....done


Server Software:        Netlify
Server Hostname:        metaforecast.org
Server Port:            443
SSL/TLS Protocol:       TLSv1.2,ECDHE-ECDSA-CHACHA20-POLY1305,256,256
Server Temp Key:        ECDH X25519 253 bits
TLS Server Name:        metaforecast.org

Document Path:          /api/frontpage
Document Length:        115 bytes

Concurrency Level:      1
Time taken for tests:   33.563 seconds
Complete requests:      10
Failed requests:        9
   (Connect: 0, Receive: 0, Length: 9, Exceptions: 0)
Non-2xx responses:      1
Total transferred:      1103648 bytes
HTML transferred:       1100869 bytes
Requests per second:    0.30 [#/sec] (mean)
Time per request:       3356.261 [ms] (mean)
Time per request:       3356.261 [ms] (mean, across all concurrent requests)
Transfer rate:          32.11 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:      575  581   4.4    581     590
Processing:  1812 2775 2648.0   1883   10284
Waiting:     1229 2246 2833.0   1308   10284
Total:       2393 3356 2646.9   2469   10862

Percentage of the requests served within a certain time (ms)
  50%   2469
  66%   2485
  75%   2539
  80%   3149
  90%  10862
  95%  10862
  98%  10862
  99%  10862
 100%  10862 (longest request)

Not sure yet what's causing this. Selecting a single value from the DB, even a large one, shouldn't take that long.

Asking for frontpage_sliced from Heroku is ok:

defaultdb=> \timing
Timing is on.
defaultdb=> select frontpage_sliced from frontpage order by id desc limit 1;
Time: 31.995 ms

This might be related to the ping distance/latency (Netlify <-> DO) again. But then /api/dashboard-by-id would be even slower since it does multiple queries while /api/frontpage does a single one. (/api/dashboard-by-id it not great either, its median time is over 1s, but it's still fater). Need to investigate further.

[berekuk]

Ok, my fault, I should've been more careful with pg connections and call client.release() properly. Most of this issue is fixed in afeca1e now, though I still believe that colocating frontend/api and the database might be necessary later on.