search

liqd / a4-meinberlin

The central participation platform of the city of Berlin, Germany
https://mein.berlin.de
GNU Affero General Public License v3.0
40 stars 6 forks source link

project overview is slow #2030

Closed rmader closed 4 years ago

rmader commented 5 years ago

To many sql queries...

fuzzylogic2000 commented 5 years ago

But @MagdaN made it a a lot faster! So, can we close this issue?

rmader commented 5 years ago

Jup :)

CarolingerSeilchenspringer commented 5 years ago

@rmader @MagdaN @fuzzylogic2000: the project overview is still (or again?) quite slow so I reopen this issue. You have any idea how to fix this?

rmader commented 4 years ago

Ok so there's more to this. Apparently some projects have somewhat "broken" data, slowing us down tremendously. #2646 makes this much easier to debug, but does not solve the issue by itself.

Here is what I got so far. With #2646 applied, there are two API calls that heavily stand out:

/api/containers/ is slow because of /projects/radverkehrsversuch-tempelhofer-damm/, which for some reason renders a point several thousand chars long. Interestingly this is not heavy on the server, but on the browser. Both Firefox and Chrome spend a long time with maxed out CPU during rendering this, but django debug bar does not report it as being slow:

"point": "\"\\\"\\\\\\\"\\\\\\\\\\\\\\\"\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\"\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ ....

Deactivating this project makes the api as fast as expected.

/api/extprojects/ is much worse, but I haven't figured out what exactly causes it. It alone creates 2808 sql queries on the production database and rendering it in the browser took my mashine five minutes (just loading it on /projekte is faster, but still the main bottleneck. To be continued.

rmader commented 4 years ago

So removing the faulty point from https://mein.berlin.de/projects/radverkehrsversuch-tempelhofer-damm/ brought us back to acceptable / expected speed. Further more, google page speed does accept our requests again, it simply aborted previously (and rightly so).

Still the projekte site is pretty bad, around 5 secs on desktop, 11 secs on mobile: https://developers.google.com/speed/pagespeed/insights/?hl=de&url=http%3A%2F%2Fmein.berlin.de%2Fprojekte

rmader commented 4 years ago

According to pagespeed our server response time on prod (without #2646) is now between 1.3 - 1.7 seconds. That's not great but probably acceptable. The biggest bottlenecks are now images (we should load them on demand when they are visible, especially on mobile) and excessive js, with mapbox being the biggest offender here. I have an experiment for reducing our js code by about half pending in https://github.com/liqd/adhocracy-plus/pull/17, but that needs further testing.

Will continue investigating loading images on demand next.

Note: sometimes images are also unnecessarily big. The project Stadtteilkasse Wedding Zentrum used an .png instead .jpg for an image that really should have been .jpg. Converting and reuploading their image reduced the size from ~400kb to 67kb thumbnail size without visibly loosing quality. We should consider only allowing .jpg or automatically converting to both formats, using the smaller result then. For A+ we could even switch to .webp, as all major browsers support it now (only IE11 doesn't).

rmader commented 4 years ago

We should consider forking and reviewing https://github.com/dan-gamble/django-lazy-image, which appears to fit our needs quite nicely.