Site news

Latest comments

Forum posts

New bug reports

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.


Please let us know what you think about this by adding your comment below.

good luck!

love your site, i visit here every week!

Aaron
Virginia Cavaliers

moodcode (Jul 19, 04:06 PM)

HT-Weird_ed wrote:
«The main reason is that we haven’t yet tested it in a real world situation.»

Welcome to the (M)hattrick(X).

Aristodemos (Jul 19, 04:53 PM)

I don’t know very much about MSSQL, but I know that in other environments (Oracle) there is an algorithm that chooses the right path in order to get the data with the quickest/lower cost.
Indexes are good as you say in order to have faster reads, but if you have too much indexes it will have a penalty in other areas, so sometimes is better to have full scans of tables (partitions) rather than having too many indexes.
Also, the algorithm needs some recently updated stadistics of the tables/schema/database, since it uses them to choose the right path.
Well, good luck, enjoy it, and have much patience, it’s always needed with databases. :)

greengroucer (Jul 19, 05:02 PM)

hey Weird, glad you keep us posted again. :)
Your work is highly appreciated, I hope you realize that.

Move your ass and make a team for Fantasia Premier League. :P
Alltid Hattrick (fed id 74507)

Luker Supporter (Jul 19, 05:19 PM)

“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. ”
Come on man!! Why don’t you advertise on this site? Taking into account the number of daily visit, you could make a fortune.
BTW thx for the great job.
All the best,

Matb12 (Jul 19, 07:31 PM)

Thanks for keeping us up to date. And don’t worry about the geekiness, it was an interesting bit to read. :)

EmperorAtahualpa (Jul 19, 09:03 PM)

Don’t Give Up! Everyone likes this site.
Only one thing. I think the dream teams are still not working properly.

shawshank (Jul 19, 09:22 PM)

I agree.

Everyone likes this site!

But the dream teams are still not working properly.

Trenta (Jul 19, 09:46 PM)

Dunno if this is also related to some read/write-issue, but it appears that some parts have not been updated still. I’m talking about salaries and form. Also the ‘ratings per line’-graph remains blank…

Hang in there, Ed! :)

Jeroen_Flitsboem (Jul 19, 10:19 PM)

I do need to comment this and i hope you have the chance/time to read it.
It is unbelivable the amount of time you speend on writing the explanation of what you are doind, thinking on how to solve problems and coding every thing.

I can only say “thant you!!!” and that i do understand wend sometings go wrong, i just hope you keep trying because if you don’t i don’t know were else will we go!!!

torresmosboy (Jul 19, 11:46 PM)

Now it’s cool, tnx! Except for one little persistant error of not showing the name of my top 10 player Paco Núñez. This has been the case since November 2009 or so, however, it did show up briefly yesterday :)

Jeroen_Flitsboem (Jul 20, 11:53 AM)

Big DB’s are cool, and a lot of fun;)
If I am not mistaken, you do’nt have to create idx’s within it,
just create your own “DB’tje”(tje=dull/stange Dutch for smaller;)
You do’nt need that much data to keep Alltid up and running fast!
Just keep the Alltid personal groups in “your” space, not within HT.
Track players tsi, salary and form, most other things are static.
And please…could you make a difference between central- and wing-
defence, they are all the same now???
(ps db is also an asm mnemonic for Define Byte, the very first
entry for defining everything else…;)

MadMeu68 (Jul 20, 06:33 PM)

Thanx for fixing it and doing such a great job.

keeefer (Jul 21, 04:19 AM)

Good job on a very nice site.
I just noticed something strange. My goalkeeper is actually not among the players, even though he has played all league matches. He is simply gone.

Don’t know if this is a known “bug”.

torsen (Jul 21, 12:37 PM)

You’re cool and weird, my dear Ed! Keep up the terrific work, you’re great, we all appreciate it a lot!

_MiKy_ (Jul 22, 11:07 AM)

hmmm….
“In fact, I bet the computer you are using right now is faster than the Alltid DB servers.”
Athlon XP 3k with 2Gb of DDR1 @333Mhz ? hm…

Thanks for all the geekiness, I love to pretend I’m able to understand and learn from my favourite Websites! Its great that you share whats going on with us, even the mistakes of being to eager. thanks for taking this time, and keep up the terrific work.

Samstroem (Jul 23, 12:59 PM)

BUG:

http://alltid.org/league/122967/index/

I still can’t see my team in the database

!!!! please fix this, this has been an epic problem with this site for a long time now

Ukityatin (Jul 25, 02:38 PM)

Hey, WHEN we’ll be able to see the friendlies stats?

Ricardoninty (Jul 29, 05:08 PM)

* Error: Unable to load team 1970162

* Error: Unable to load configuration for stats. second hattrick year gone but still i have this problem

please fix it :)

turkom333 (Jul 29, 06:10 PM)

I can see this for months now:

* Error: Unable to load team 1997784 * Error: Unable to load configuration for stats.

Can you please fix it? Thanks.

domper (Aug 4, 02:09 PM)

Looks like a great site I’m pretty new to the whole hat trick thing and the stats on this site blew me away unfortunately my own team doesn’t appear to be very accurate and still has an old name and players I don’t recognise. Strange thing is it does have some players I’ve bought myself.

Can appreciate with all the bugs from the database error and the stat updating disabled it’s going to be a hard job and take time and a lot of work so best of luck and hope I eventually see the site in full glory.

teknikal (Aug 4, 07:11 PM)

@teknikal
Did you try changing the round to the most recent one, on the top left?

Jeroen_Flitsboem (Aug 5, 09:12 PM)

* Error: Unable to load team 1970162

* Error: Unable to load configuration for stats.

pls fix it

turkom333 (Aug 5, 10:57 PM)

@Jeroen_Flitsboem
Thanks that helped with the players and the whole layout makes more sense to me now although the Team still has a completely different name and even a different manager listed. I’m pretty sure it’s showing that from before I took it over though if your curious Team ID is 484754 .

teknikal (Aug 9, 04:16 PM)

You’re welcome, Teknikal. And you’re right, you’re team (Instinct) is not listed in Alltid, as is your username. But I believe I had the same problems in the beginning and they were solved from one day to the other, so I hope you’ll have the same luck eventually :)

Jeroen_Flitsboem (Aug 9, 11:03 PM)

commenting closed for this article