Why is the database API so slow?

When I try to “get” the summary data on past labs, it takes about 15 seconds.

http://eterna.cmu.edu/web/script/3448…

function getPastLabs(n)
{
n = n || 10000;
var params = {
type: “past_labs”,
skip: 0,
size: n // Presumes no more than N labs (default 10000)
}

res = AjaxManager.querySync(“GET”, Application.GET_URI, params);
return res.data.labs;
}

Why so slow?

The comment in the full script says

// It is sometimes useful to have a list of past labs, or their nids.
// These functions do API queries to return the labs or lab ids (nids).
// Note that it can take 15-20 seconds to return each answer, so be
// patient and increase the script timeout and cache the result if
// needed more than once.

But you don’t mean that making one query of all past labs takes 15 seconds per lab returned, do you? That wouldn’t match my own experience with the script that generates the list of past labs for the wiki. It takes about 15 seconds to run.

I’m guessing that the script comment refers to the situation where you are forced to put a database query inside a loop because no query exists that will get it all at once, as we discussed with Justin in the last dev chat.

I wonder if Eterna uses a SQL database for storage, and the RESTful query API has just been created as needed to support the game UI. If so, with multiple player-developers creating new capabilities, maybe we should ask Justin for something more like a general SQL query capability, rather than the smaller solution we arrived at in the dev chat that would only help with lab queries. That would certainly help the devs from being bombarded with back end requests in the same way they are now with UI requests.

I wrote the script. I put the comment there. I would like to understand why it takes so long.

I’m used to GET taking 1-2 seconds for some queries, but 15 seconds seems excessive. And that is *one* query, not something running in a Javascript loop (at least not on the client side).

But if I want the final results that I’m looking for, I *will* need to run a loop and an at least one additional query on each lab “nid” returned… so if there is something we can do to speed up the API it would be appreciated.

I decided to look into. Using wireshark, I monitored the TCP/IP conversation. It turns out the the TCP/IP software called from the in-game scripting engine is not closing the TCP/IP connection properly. To be specific, when it gets the FIN from the server, it returns an ACK, but it doesn’t also set the FIN bit, which it should. As a result, the server doesn’t close it’s end of the connection. Then, typically about 9 seconds later, the client send a followup packet with both FIN and ACK set, the server ACKs that, and both sides agree they are done. Only then did the UI display the results.

But then, while I was watching this to confirm it was consistent, things changed! The UI started reporting the results as soon as the client ACKed the server’s FIN, which eliminated the 9+ second delay. My guess is that Justin or one of the other devs read your post, looked into, and implemented a quick and quite reasonable workaround. But maybe things are intermittent. I know that I have occasionally gotten a running time for my script that is closer to 7 than 17 seconds. Why don’t you go ahead and try it and see whether it seems solidly fixed.

Hmm. Interesting Wireshark observations. It is still taking over 15 seconds for me.

Don’t know what to make of that. I’m still getting sub-ten second results that match up with the time between the opening of the connection and the receipt of the first FIN. I’m running Windows Vista. If I recall correctly, you prefer Linux. Is that what you’re using now? And what browser? Not that I know that the OS or browser is relevant, but it might be. I’ve now tried it with both Chrome and Firefox, and am qetting the quick times in both.

I’ll write up a bug report on github, and reference this forum thread, so if you find anything new of interest, post it here.

I’m using Chrome on Windows 7 for most of my EteRNA script work.

I think the webserver is having issues. Vinnie never had trouble getting puzzle data or submitting solutions (when it finds any). These past couple days, I’m witnessing random long delays, and sometimes queries stay simply unanswered…