Karbowiak
Superior Mass
67
|
Posted - 2012.09.13 22:43:00 -
[1] - Quote
Steve Ronuken wrote:You really want to institute caching.
Just check to see if it's cached when someone wants to look at a killmail, and if it's not, get the data from the database, then cache it. That way, people sharing kill links adds a minimal load.
memcache is nigh perfect for this. The first hit on a killmail will be relatively expensive. Every one after that will be cheap until it ages out.
As for how well a SQL based kill board would be, umm, 14 million entries isn't really that much. You just need to have appropriate partitioning and indexing.
Before deciding how to store the data, build your user stories. How are people going to want to retrieve the data. Every kill on a person? Every kill someone was part of? and so on.
Don't just cram everything into a single table. Something like:
Kill table: Kill id, victim id, kill date, ship id, location, kill value.
Drop table: pk, Kill id (fk), drop list as a json blob (unless you want to search on what dropped.)
Killer table. pk, Kill id (fk), killer id, ship id, weapon id, damage, final blow boolean.
Stay away from MyISAM and you're probably fine. (MyISAM does badly on locking, locking the entire table when it needs to do something other than select).
You do realize this is what EDK does ? inv_all / inv_crp holds kills where alliance and corporations are involved inv_detail holds both inv_all and inv_crp stuff, plus some more data. kills holds the victim data and is the main lookup table items_dropped/items_destroyed holds the items
the problem is that when you get alot of entries, those tables grow large, and EDK constantly joins those tables, tables that can easily grow to being +10GB in size, each.
Obv. some of the performance can be negated by partitioning it, but even then, one false move in a query and it does a full table scan.
This said, EDK does all of this very horribly default, and im very much aware that 10GB table size isn't that large compared to what enterprise stuff must be doing. But they also have more beefy hardware at hand than we have :P
As for the table, we're using innodb (xtradb actually) on Percona's version of MySQL, but i'm contemplating switching to MariaDB seeing as it has some more optimizations for joins. Anyway, Kingboard is fast even with millions of killmails, and that's on a relatively underpowered virtualmachine. Once Kingboard is finally done, and EVE-KILL can begin using it, we'll throw some beefy hardware at it, no worries! :)
Forgot to mention we also use varnish and memcache on eve-kill.. |