Welcome to Alltid Hattrick!

New season, some fixes

HT-Weird_ed | 17.08.10

Another season has started, and I’ve taken the opportunity to fix a couple of problems:

• No more “unable to load team” on teams which are in the database.
• Missing/wrong team names should exist and be correct.
• Added a couple of missing league levels (Italy XI and Hungary VIII).
• The missing player names should be fixed on the next import.

Good luck to everyone in the new season!

Comment [11]

That was a bad idea... But databases are fun, right?

HT-Weird_ed | 19.07.10

Update: Stats are back.

I’ve disabled the stats temporarily, and will import the missing data (player data) into the Alltid databases before the site opens again. It’ll probably take the afternoon. Sorry about that.

And now for an explanation of what’s been going on this weekend.

We’ve had all the data we needed for Alltid in the Hattrick databases for quite a while. The data was never updated “live” during the weekend before though. I enabled it this weekend, and switched over Alltid to use the Hattrick database. Unfortunately, that didn’t go as well as I hoped:

1. I was very unlucky and encountered a database error which delayed everything for 12 hours.
2. The data processing in the Hattrick database is way slower than expected on larger leagues, which made everything queue up or fail.
3. There are lots and lots of different ways of accessing the Alltid data, and the data set is huge. Seems like we need to do some changes to make the server able to handle all variations of stats without slowing down.

Since there’s no stats to look at right now, I’ll take the opportunity to geek out and talk a bit about the challenges regarding database design and the huge amount of data we are looking at. I’ll say what I just said, but in a lot more words. You’ve been warned.

Simple Explanation of Databases
When you create a database there’s always a fine line between 1) how fast you can read from it, 2) how fast you can write to it, and 3) how easy the data will be to maintain.

To read from a database, you need indexes (like the index in your phonebook) so the server can look up the data quickly. But generating an index takes time, and it takes more time the more data you a) have in your database, and b) want to add to the database.

So keep in mind:
No indexes = fast writes, slow reads
Lots of indexes = slow writes, fast reads

The Original Alltid Database
The Alltid database runs on very modest hardware, because I never had much money to spend on it. In fact, I bet the computer you are using right now is faster than the Alltid DB servers.

The old Alltid database completely ignored maintainability and went straight for writes as the main priority with reads as a close second. I accomplished this by separating the data into lots and lots of small independent chunks in the database. It’s what we call sharding). This means that whenever a huge amount of data is added to the database, there’s very little or no existing data in the shard it’s writing to. It also means we can spread data easily across servers.

So the imports were very very fast.

But what about reading from the database? Well, as long as the data you wanted was within the same chunk of data (shard), this was incredibly fast. But as soon as you went outside, it was very slow, since there was no way to easily look up data between shards.

It was a reasonable tradeoff, and something which the database software (MySQL w/MyISAM tables) excelled at.

The New Hattrick Stats Database

Hattrick has an insanely huge server for the Hattrick Stats database. They’re also a serious business. That’s why maintainability has to be a priority, and thanks to the hardware it can be a priority. And, of course, Hattrick uses a real database server software (Microsoft SQL Server), compared to the old MySQL (w/MyISAM) server software.

Luckily MSSQL has a lot of built in options for achieving the same things I had to do manually on the old servers, so the data is very very easy to maintain and understand.

The database design is a bit different though. While we’re still using “shards” (partitions), but every shard holds a lot more data. So it’s basically a bit like having everything in a couple of huge buckets instead of thousands of small cups.

This is potentially a lot faster to read from when you want a broad set of data, but requires more indexes. Which in turn makes slow writes even slower. The hardware should be able to handle it though, we just need to find a good balance.

So with all this real hardware and software, why are we experiencing these problems?

The main reason is that we haven’t yet tested it in a real world situation. Because of all the delays in getting things ready, I got eager, and saw an opportunity to use the new data. But it was too early, and it’s obvious that our balance between write/read speed isn’t as good as it should be.

The bad news is that by having the data in the big buckets, every little operation on the database (like tweaking indexes, changing layout) takes forever and ever. On the main table it now takes about 24 hours to add an index. So it’s incredibly time consuming to do changes and tweak things until they are perfect. Even if we experiment on a subset of the data.

In the future, I will do a more gradual switch to reading from the new database. I’ll move one statistic at a time, and look at how the performance is.

I have good faith that all the database work should be complete before next season though.

I’ll talk more about the plans for Alltid/Hattrick/Integration once that work is done.

Comment [25]

Ok, let's try this...

HT-Weird_ed | 18.07.10

Sorry about the problems this weekend. I switched most of the stats to use the Hattrick database, which should give you better data with faster updates (eventually). Unfortunately I was unlucky with my timing, as the part of the Hattrick database Alltid uses decided to act up. It’s under control now though, but the imports have been queueing up more than I expected. Sorry about that.

I’ll talk more about the new database later. But for now, there are some issues I know about:

• Compare/groups/feds might not work as expected, but please report if you see problems.
• Those of you with “team does not exist” errors will still get these errors, but we do have your data. It’ll probably not work correctly until next season though.
• The dream team auto-setting seems to choose the worst option instead of the best. I’ll look into this.

If you see any other problems, please report them in the comments.

Comment [37]

Some changes to player data

HT-Weird_ed | 1.07.10

This week I’ve made some changes to top players and dream teams. The result should be more accurate data, and a lot faster data retrieval. I’m sure you’ll notice during the weekend when the site is usually very slow.

Unfortunately this has had some bad effects on the groups, federations and team compare data for those stats. It should be solved in time for the weekend updates though.

I’d also like to thank everyone who posted on the last news. It feels great to get that kind of support even though things haven’t been as they should with the site lately.

Comment [20]

Previous