600208 - Send 503 + Retry-After when the DB queries are timing out

Status: RESOLVED INVALID

(Cloud Services :: Server: Other, defect)

Description

[Tarek Ziadé (:tarek)]

We should set the sql connector to wait at most 10mn for a query, and return a X-Weave-Backoff if the DB does not respond. (using PDO::ATTR_TIMEOUT() in PHP)

This will inform the client that the node is melting down,

Comment 1

[Tarek Ziadé (:tarek)]

mconnor suggested a Retry-After instead of X-Weave-Backoff in that case.

Comment 2

[Toby Elliott [:telliott]]

I don't think this'll help us much. Apache will probably have timed out on the frontend long before this, and nobody will get the response.

Comment 3

[Toby Elliott [:telliott]]

The approach we probably need to take is some combination of monitoring and Bug 599018 - where we manually or automatically trigger a backoff based on the length certain queries are taking. 

Would also be useful to have the queries that really cause us pain logged somewhere.

Comment 4

[Tarek Ziadé (:tarek)]

(In reply to comment #2)
> I don't think this'll help us much. Apache will probably have timed out on the
> frontend long before this, and nobody will get the response.

As long as the PHP thread is killed when Apache is timing out, that seems fine.

Comment 5

[Toby Elliott [:telliott]]

Yes, but killing php may not kill the mysql thread.

Comment 6

[Tarek Ziadé (:tarek)]

Oh I though PDO was taking care of that when the thread receives the sigkill.

So what about setting timeouts this way (with 1 second between each timeout):

  apache timeout > php timeout >  mysql timeout

This way, PHP/PDO can handle queries timeout and we're sure there's no orphan process running on the server.

Comment 8

[:Atoll]

the client gives up at 5 minutes from the beginning of the request.  we need to either set a global timer from the start of the request and abort at 4m30s total time spent, or we need to set no more than three or four timers at 1m00s each, so that we always return a temporary error and a backoff/retry-after to the client.

Comment 9

[Tarek Ziadé (:tarek)]

Making my previous comment clearer, as it seemed unclear.

By setting the Apache timeout to, let's say 30 s, the PHP timeout to 25 s and the DB connector timeout to 20s (on MySQL side too), we can return a timeout error and make sure we don't leave long-running processes on the server. Same thing apply for LDAP.

Comment 10

[Tarek Ziadé (:tarek)]

I am checking on my side that the Python server behaves properly on slow LDAP or SQL servers. 

tc was a pain to use depending on your kernel options/OS, so I have created a small port forwarder script I am using to add delays. It add a bigger delay on each call until it reaches a max delay, then reduces it to no delay, and starts back.

  http://bitbucket.org/tarek/sync-server/src/tip/tests/delay/delay.py

If you want to use it for the PHP app, install twisted and run it like this:

$ sudo python delay.py 390 localhost 389
Forwarding from 390 to localhost:389 with delays

This will add a delay to every call on localhost:390 then forward to the ldap server.

Comment 12

[Philipp von Weitershausen [:philikon]]

As described in bug 616393, last night's incident has revealed that timing out MySQL queries will in some cases have the web head return a 200 response with an invalid JSON body. This will either confuse the client into wiping + reuploading or just surface an Unknown Error. Both aren't acceptable. Web heads should return a 503 + Retry-After as soon as there's a noticeable delay in database response time. This will tell the client to back off and show the right kind of notification to the user.

Comment 13

[matthew zeier [:mrz]]

Attachment: error_file

Here's how I fixed it:

* uploaded as an extra file
** Catalogs | Extra Files | Miscellaneous Files

* changed error_file to sync-500-to-503.html
** Services | Virtual Servers | sync01 | Connection Management | Connection Error Settings


To revert, change error_file to Default.

Comment 14

[:Atoll]

i do not agree that 616393 is a duplicate of 600208..  attachment 495152 [details] is good for when we actually get as far as returning a 500 error to the client (per bug 616393) but is not sufficient interrupting an active request at 30 seconds to send back a 503 to the client (per bug 600208, this one).

Comment 15

[Tarek Ziadé (:tarek)]

probably outdated