OpenSubtitles is BLEEDING to death!

[oss]

ok, danger recompiled varnish with fix posted by developers, it seems it helps. So lets see, how it will perform now. Anyway, handle 5000-6000 users online is not fun anymore. We need one more webserver it seems.

[eduo]

Are there any plans to revise the API and the methods before the big rewrite? Some of that might help.

What exactly is the most expensive(*) action in OpenSubtitles? Is it search? Is it a web query? Is it an API method?

(*)Expensive as in "the one that slows everything the most" or "the one that takes most resources".

[oss]

there are some issues:

1. when website doesnt respond at all, it is down for long time - thats some big problem, like we had now recently. Now it is fixed (at least workaround, I still think there is something more behind that)

2. when website doesnt respond for short time - usually something is taking resources in mysql. Long query needed for statistics, for example I disabled to show downloaded subtitles per user. I got it database, but it takes long time to filter all downloads from HUGE download table(s)

3. connection error...

[eduo]

there are some issues:
2. when website doesnt respond for short time - usually something is taking resources in mysql. Long query needed for statistics, for example I disabled to show downloaded subtitles per user. I got it database, but it takes long time to filter all downloads from HUGE download table(s)

Consider running overnight job for this. And doing the grouping and summarising in PHP, not MySQL. I have several similar statistics jobs at the office and what I do is generate an overnight larger table and process it separately, filling out a "stats" table.

Users know the data there is refreshed overnight, so that's OK. Also, I don't include anything below a certain threshold. That eliminates more than 80% of users who don't know nor care about statistics. There's a "minimum" to appear in the statistics ladder. Improves competition and lowers processing needs.

[Sorku]

2. when website doesnt respond for short time - usually something is taking resources in mysql. Long query needed for statistics, for example I disabled to show downloaded subtitles per user. I got it database, but it takes long time to filter all downloads from HUGE download table(s)

Like eduo said, caching is the way to resolve all heavy queries. There are quite a few free frameworks for caching available in PHP out there, many of them are quite easy to implement if opensubtitles is object oriented

[eduo]

2. when website doesnt respond for short time - usually something is taking resources in mysql. Long query needed for statistics, for example I disabled to show downloaded subtitles per user. I got it database, but it takes long time to filter all downloads from HUGE download table(s)

Like eduo said, caching is the way to resolve all heavy queries. There are quite a few free frameworks for caching available in PHP out there, many of them are quite easy to implement if opensubtitles is object oriented

OS uses a lot of caching already. This is more than caching, it's changing the way some queries are made so they run on a separate "thread" than the site's requests.

[Sorku]

OS uses a lot of caching already. This is more than caching, it's changing the way some queries are made so they run on a separate "thread" than the site's requests.

I'm aware of that, but last time I checked the user statistics it was calculated on the fly, this was a couple of months ago though...

[mataka80]

I just was wondering also that statistic calculation. One possibility would be to take kind of data warehousing approach and keep all older statistic in fact tables.

In other words e.g. last month would be in product db but older data would be in "DW". Containing only well defined tables for those statistic purposes. E.g. tables like UserDownloadsFact (e.g. user, month, amount), SubtitleDownloadsFact (e.g. subtitle, mont, amount) having sums of downloads and those have time dimension on day or month level. Ok there will be lost connection from which user downloaded which subtitle and exactly on what time but it is always good to ask is that information really important for history data to take space and slowing down queries. (Of course if space is not an issue and that is still wanted to keep there it can be kept in another table. main idea is to get that old data away from product tables and have well defined summary facts for data that is needed on reports.)

Data from product environment can be moved to summarized fact tables once per day/week or whatever would be good interval.

Anyway this would make also possible to calculate "current" information by using both product and DW because product table would have lot less data and queries would be quite fast for it.

Of course this needs more work that approach that eduo proposed.

[eduo]

Or, even better, disable the godforsaken stats page and run it as a batch job overnight and leave it mostly static.

Without knowing the architecture of the site and where the loads are happening there isn't much we can do to help other than blindly offering suggestions.

One hint: Caching is a band-aid. Caching doesn't replace good coding practices. We can help with the latter while caching holds up.

[eduo]

Incidentally: Users are dropping like flies from the clients using the API. We can't tell them much because it's all a gray area. I don't know if the API is officially down or if it comes up from time to time.

One idea: To make a method (or use the serverinfo one) to report the status of the API. The XML could be static and it could be anonymous ( http://api.opensubtitles.org/apistatus.xml for example) which reports the overall status of the API and the methods that are working.

That way we could code the clients to let the users know the API is not fully functional. Representing this in the blog, forum and main pages would be an added bonus, similar to http://status.twitter.com/ .

[eduo]

Suddenly this whole thread feels like Deja Vu.

I guess OS takes holidays on the same date always

As in the past, I offer my help in sharing the load of serving the files and/or the database.

As has been stated in the past, a mirror wouldn't be amiss. If at least to cover for them vacation time comes.

[SimplyTheBOSS]

Info:
viewtopic.php?t=2519

[oss]

"opensubtitles fucked up BSplayer"

if you can, please explain this, it sounds quite offensive.

[danger]

should be fixed now.

[farslegender]

Thanks Boss!
From what I can see the site is normal again.