engine room report

[b]*** engine room report October 27th 2014 ***[/b]

Hey there again, here's the news:
Spent another week looking at possible causes of the so called "1am Berlin Time slow down" effect, with quite some criminalistic investigation on what might be the cause. Since server support has not been assisting, I had to sit and think myself -

think of yourself wondering why your car suddenly only goes 25mph max:
You may look at the engine in despair, check the fuel tank, check wether the brakes got stuck or you got a flat tire, and if all of that fails, maybe you'll find a family of elephants on the roof of the car that may explain the effect as well...

In our case here, I finally found out what is happening at 1am, and it has not to do with too many visitors at that time of the day, nor with too little bandwidth available, nor with the database getting overly busy.

Turns out it is the hosters dayly statistic generation - a tool that calculates the used webspace & traffic and propably does some backup routine at the same time.

Obviously, this is so load intensive on the harddrive that we are experiencing the slow down while the statistics are rendered. It would have been nice if the server guys had told me right away, well...

After I came to this rather unexpected conclusion, here's what I did:
To speed up the backup process, I went out and deleted a whole lot of outdated and duplicate files on the server, including a backup of the audio files.

Next, I realised that the concept of storing search engine pages as single files (done since the 6.2 update to save reoccuring database queries) might be the cause of the statistic tool taking so long:
Since there is quite a lot of possible search engine result pages on wikiloops, I was looking at about 120.000 single files per subdomain which needed counting and backup every night...

So I went out to change that concept all over again, this time following an approach hurzel suggested at the Urft meeting:
Instead of storing the search results page of p.e. "Blues without Bass" as a single file, I focused on the question: "What is so load intensive about that page creation that it shouldn't be done on any visit?" again.

Turns out, theres three things that will let this take long:
1. counting thru the complete track database to find out how many tracks match a search,
2. checking the complete trackbase again to find out which additional filtering choices should be available (engine tries to offer only available instruments to avoid "0 matches" results) and
3. getting the ten tracks to show on the page (which funnily is the least load intensive of the three).

So, instead of saving a file to preserve those things for some time, the new approach had to be:
save those three bits of information into a database entry and use those entries to present the search results, while the page displayed is created (and never saved) on the fly. Yes, this is indeed causing some CPU and a little Database load again, but also allowed me to delete about 50GB of stored search pages(!) on the four subdomains of wikiloops.

Since the search pages have different names/translated instruments on each subdomain, it used to be necessary to keep seperate copies on each domain, and once at it, I had to get rid of this duplication issue as well.

The three bits of information listed above are in no way related to the language they will be displayed by - the count of files is the same, the available filtering options are the same and so are the tracks to display, the only difference is the output of the page, so what was needed here was some a way to let the server know the query for "pistes sans batterie" is the same as one for "tracks without drums".

This is done by "encoding" any possible filtering setting into a non-lingual index number, and after thinking hard and experimenting around a little, every preserved search result is now identified by a 25(!) digit ID that can be accessed from any language domain.

So, while everything pretty much looks the same on the loops, the backend engine has gone thru a complete concept change within that last week once more.
Yes, this is nerd stuff, but looking at the financial consequences I'd say it was a week well spent.

After all of the above (and a lot even nerdier stuff I wont bore you with) has been applied, wikiloops is in total 300GB "slimmer" than before, and the number of files to backup every night should be reduced by half a million.

My own statistical tools and googles webmaster tools both confirm the average server response time has dropped by about 25%, which is the effect of my overall load reduction efforts.
All of this while the dayly visitors count crossed the 1600-mark several times last week.

When comparing the hosters stats record (sadly I cant turn it off) of September and October, the amount of requested pages/files has dropped by 40% - mostly because of moving the images on the CDN network and slowing down the "live" refresh rates of the shoutbox.

So much said about the "you are causing too much load, get a bigger server" statement by our hosters support.

Sadly, the 1am slow down may still be witnessed, but it does take a lot less long as far as I could tell - last impression I had were two stretches of very slow responses per night, both taking about two minutes.
If thats what we'll have to live with to save a lot of budget for some more time to come, I'd say lets just live with it for now - even tho there is a slight risk of getting down-ranked by google in case googlebot notices the slow responses.

With Lutz words in the back of my head, I'll try to keep focused on the budget issue for some time to come - one new "eye-catcher" that non-supporters have to pass on every log in now looks like this:
[img]http://www.wikiloops.com/forum/images/logon_screenshot.png[/img]


I'll continue with fixing some minor bugs (bpm & meter values are still messing up on new uploads... thanks for reporting those issues again!), preparing some sponsor spotting and hope to be back with some good news soon!

Stats of the day:
17.828 members
22.582 tracks up
1554 average dayly visitors last week