War2 Analytics

[BusyOrc]

Here's a project I've been working on

Server Analytics

http://analytics.war2.co/

User Analytics

http://analytics.war2.co/user.php


Game Search (still under construction, only works for search 1-2 players so far and has lots of bugs still)

http://analytics.war2.co/game_search.php


Screenshot generator

http://analytics.war2.co/game_report.php?id=119150


Its not fully baked in some areas but I'm getting bored with working on it for the last few months so figure I will let you guys have some fun with it.

[Certified MENSA Genius Brain (smart)]

1st   Garden of War.pud   31,530
2nd   Garden of war BNE.pud   8,507
3rd   Chop Farms [PROPER].pud   5,890
4th   Mini Chop Farms [[PROPER]].pud   3,923
5th   Mini Chop Farms [PROPER].pud   3,281
6th   Chop Farms (PROPER) CORRECT.pud   2,223
7th   Nowhere to run or hide.pud   1,720
8th   Mini Chop Farm [PROPER].pud   1,442
9th   Chop Farms {PROPER} CORRECT.pud   1,203

These dummies need to make up their minds and figure out which version it is they want to play

[GaNzTheLegend]

never knew people played that much NWTR still.

[BusyOrc]

NWTR must be all Unu lol

[Certified MENSA Genius Brain (smart)]

Yeah Unu hosts nothing but nwtr and if I'm online and it's the only game hosted I'll join it because nwtr is pretty good.

[{Lance}]

If you are not using caching for this,  I highly suggest it so that it does not overload iL's server resources.  His servers are not that good hardware wise.  Cache results in your own DB and have the search request from your page check your own DB first, then if nothing exists or it's been awhile since the last update for that particular query,  check iL's servers as sparingly as possible.  Especially for the "user search" feature which I am assuming abuses the ladder stats page or the game reports,  both of which are quite resource intensive when given large search parameters.  It seems like you already have caching for the "stats" portion,  but I'm not so sure about the search features because they take a conspicuously long time to complete,  I assume because it's reading from iL's servers rather than your own DB.

The UI you wrap around the items is pretty nice though.  I do like the look you have for the game reports with a clone of the war2 end screen and such.  Nice front end design work.

[BusyOrc]

Thanks Lance,

As for effecting server resources, I do not want to cause any problems! I am trying to have as small of a footprint as possible here.

All the queries are performed against my own DB.

I wrote a python script that runs every few minutes, downloads the list of game reports from reportb.war2.ru, checks to see which game reports its already done before, then it parses the new game reports that it hasnt seen.

Also another python script downloads the server status page every few minutes and saves info to my DB about antihack status, and country of users, and records session times.

I used to run a python script in the middle of the night that would log into the server and /finger every profile, parse and add to the DB, but it was getting me IP banned despite efforts to delay each command by 5+ seconds, so I stopped doing that, and it was probably a little too resource intensive anyways, although it didnt seem to cause any problems.


So now all I'm doing is opening reportb.war2.ru and server.war2.ru/status every few minutes.

[{Lance}]

Nice,  the slow down I noticed was really in the search queries.  They do seem to take a very long time and with a proper DB setup, they should take no time at all.  Are those queries (such as a username query) coming from your DB?  If so,  might want to make sure you are using indexing.  That could speed up the results A LOT because to me it seemed so slow that I thought it was querying il"s server on the fly and that wouldnt be good for the health of his servers  

[EviL~Ryu]

See what happens when you use your powers for good and not evil [emoji14]

Let me know if you wanna collaborate on some trivia reporting projects I have in mind.


Sent from my Motorola DynaTAC 8000X using Tapatalk

[BusyOrc]

Lance, I could definitely use some help with speeding this site up.

The server is 8gb RAM, 500GB sata hdd, 2 Xeon CPUs, there's no reason it should be so slow I would think.

backend: Linux/CentOS, Apache, MySQL, PHP, Python (for scraping all the data and adding to my DB)

frontend: Javascript, JQuery, and Google Charts API. The "Download SS" is actually a cool HTML5 Canvas script that turns the screenshot table into a PNG


I just barely started to get into programming and databases last spring, I didn't even know what a "framework" like Laravel was until after I started this site, so its just a mess of different PHP files strung together with "include"s.

Python scripts run on a 3min cronjob for collecting game reports and server status info and adding to my own DB.

I've made some changes in MySQL because it is so slow I've been trying to make it better, I increased some table size memory or something that helped alot for when I do subqueries that select huge tables "AS table_1" for example.

Also I changed to "innodb" just because I read its better but like I said I dont know much like how to cache, but Ill look into it



I need help writing the game search also.

Figuring out the query to write was very complex, joining multiple databases, but I have it figured out now. The problem is the number of possible combinations of inputs is insane.

Do I literally need to have an ifelse statement for every possible combination of inputs? If so I will literally have to write thousands of ifelse statements to expand to 8 players

[BusyOrc]

See what happens when you use your powers for good and not evil [emoji14]

Let me know if you wanna collaborate on some trivia reporting projects I have in mind.


Sent from my Motorola DynaTAC 8000X using Tapatalk

Yeah I think I'm going to change my name to GooD~Archer lol. But yeah I'm trying to contribute, hope some of the code I wrote can actually be useful for the community
Sure

[{Lance}]

Innodb actually isnt all that it's cracked up to be and will cause you more problems than it solves.  It generates 1 BIG ass file and rather than deleting from it,  it just keeps piling more info into it.  Example,  if you "Delete" a row in the table,  it does NOT actually delete anything.  All it does is add a "delete" query to the big ass file which then denotes that a row has been deleted and it just "appears" like it was deleted when it really wasnt.  It was just marked as deleted.  This is called journaling and is only useful if you have to "rollback" data at all.  You can use the journal to go backwards in time.  Similar to version control for source code files (like git).  It does have some speed enhancements that affects input,  but as far as actual querying,  no, there is not much difference.  Certainly not enough difference to ignore the fact that your innodb file is going to be TERABYTES in size in no time flat and the only way to "fix" that is to completely start over.  Do a mysqldump of the DB,  drop the DB, wipe the innodb file manually, and then re-import it.  There are ways to split that innofb file into multiple pieces, but it's a very advanced thing and very likely way above anything you could handle.  Stick with MyISAM for your tables.  Your diskIO and disk space will thank you for it.

The key for joining tables is to make sure that you are using "WHERE" ONLY on fields that have an "index".  If even 1 field does NOT have an index,  then your query can only run as fast as the slowest field and that can be a real bummer.  Thats usually the single quickest method to magically speed up any query.

[BusyOrc]

Interesting... I have alot of reading to do to get this site optimized.

The server stats charts are really cool. It was one of the hardest parts of programming.

Isn't anyone surprised to see how the server has actually been growing somewhat from Aug - December, increasing from 11k games per month to 15k games in December.

January's numbers are skewed because of a gap of game reports that were missing from RU for some reason (i think server migration?)


ALSO IL/LANCE:

The game report texts can be pretty shitty. They have problems with unicode characters so my game parser only works if the person uses an English war2 client, because otherwise the report gets all garbled and theres no way to parse it. I dont know if you can normalize them to be english some how, but it would help alot.

Also the times in the game report, it would be awesome if they could be normalized to UTC. It was very difficult to figure out how I could convert the times to UTC, and I ended up going off of one of the times that is always in Eastern timezone (because the server time is eastern I guess) and I was able to convert that to UTC and use it as the game time

[Certified MENSA Genius Brain (smart)]

See what happens when you use your powers for good and not evil [emoji14]

Let me know if you wanna collaborate on some trivia reporting projects I have in mind.


Sent from my Motorola DynaTAC 8000X using Tapatalk

Yeah I think I'm going to change my name to GooD~Archer lol. But yeah I'm trying to contribute, hope some of the code I wrote can actually be useful for the community
Sure

[EviL~Ryu]

Lmao, this picture again.


Sent from my Motorola DynaTAC 8000X using Tapatalk