Cartodb: Some endpoints are extremely slow

[saleiva]

Hard to reproduce since it's not happening always, but looks like some endpoints are very slow.
Attaching one when trying to add a widget

cc/ @javisantana

[juanignaciosl]

@saleiva ok, we'll take a look. If it happens with other endpoints please let us know.

[javisantana]

probably related to index creation @javitonino ?

[juanignaciosl]

We've already talked about that, but index creation is enqueued, it can't affect. We're getting some slow (>40s) updates on some tables, including widgets, we should research that.

[saleiva]

With styles it also happens

2016-06-14 14:15 GMT+02:00 Juan Ignacio Sánchez Lara <
[email protected]>:

@saleiva https://github.com/saleiva ok, we'll take a look. If it
happens with other endpoints please let us know.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/CartoDB/cartodb/issues/7839#issuecomment-225862944,
or mute the thread
https://github.com/notifications/unsubscribe/AAIENSAKFHTFKch_rwBtxhYkPUCge-7Uks5qLptngaJpZM4I1PFV
.

Sergio Alvarez Leiva

[juanignaciosl]

Ok, thanks, we're pretty sure that it's related to database random slowness.

[javitonino]

After looking at logs until my eyes bled, I'm pretty sure this is related to named maps updates, with some aggravating factors. I have seen named maps updates to take up to 45 seconds without apparent reason, and we do these updates synchronously from the UI worker, so this is what delays the update. The new named map implementation has a 5 second timeout, which should help with that, but maybe at the cost of losing updates.

Additionally, we update the named maps and invalidate caches inside the DB transaction, so if a worker gets stuck on a named map update, the row gets exclusively locked for all competing transactions and everything crawls to a halt. That's why we are seeing long query times as well.

Finally, some UI operations launch a lot of requests. The biggest offender I found is widget reordering, which launches two request for each widget (one for the widget, one for the layer). If any of the requests get stuck updating named maps, we block all other until that finishes.

Proposed actions, to discuss:

• Investigate slow named maps updates in the tiler @rochoa
• Extract costly operations outside of the DB transaction. This should be slightly better with the named maps refactor, which updates named maps after commit.
• Reduce number of calls to named maps api #6901
• Batch operations from the UI: @xavijam
  
  
  
  • We have a batch update endpoint for layers, we should do the same for widgets. This way, layer reordering should just call a batch update for widgets and another for layers (2 requests instead of 2n)
  
  
  • Delayed updates when changing slider values may also be a good idea if it is easy to implement.
  
  

[rochoa]

I will try to investigate why those endpoints get stuck sometimes, as they should be really fast operations, doing some validations and interacting just with redis server.

One idea we discussed some time ago was to change the semantics of named maps rest service, so PUT endpoint could accept updates and new items, that way we would remove half of the requests from the editor: IIRC we do a GET to know if the named map already exists and then a POST or PUT based on existence.

[xavijam]

cc @gfiorav

[javitonino]

We merged some changes to reduce the number of named maps calls and execute them after the transaction. This appears to be helping a bit, we still have a slow update from time to time, but not the same peaks we had before.

[javitonino]

Closing since this doesn't seem to be an issue anymore since we updated redis timeouts.