ye i agree if the system has way too much data going through it , just keep pug stats and league match stats , surely this will dramatically reduce the amount of data ?
Hey - turns out IRC is out and something a little more modern has taken it's place... A little thing called Discord!
Join our community @ https://discord.gg/JuaSzXBZrk for a pick-up game, or just to rekindle with fellow community members.
how easy/difficult (granted its more likely to be difficult) is it going to be to re-hash how the stats are accumulated? I would be happy to dedicate some time to trying to re-hash it in the name of learning new stuff which in some way may be useful to me later and obv of some use to the community.
[19:21] [@]doh> gah, fucking yahoo crawler is indexing utstats
[19:22] [@]doh> generating some huge queires
[19:44] [+]Timo_Maas> :/
[19:46] [@]doh> | 83710 | league | localhost | league_stats | Query | 44 | Copying to tmp table | SELECT pi.name AS name, pi.country AS country, p.pid, COUNT(p.id) AS games, SUM(p.gamescore) as game |
[19:46] [@]doh> 44 secs.. useless
[19:47] [+]Timo_Maas> hm, that's not one of the usual queries
[19:47] [@]doh> well it was doing something like writing sort index before
[19:47] [&]FragMe> 44 sec o_O
[19:48] [&]FragMe> EXPLAIN on that sucker
[19:48] [@]doh> so i guess that yahoo bot is doing stuff
[19:48] [@]doh> that utstats is nasty shit
[19:52] [@]doh> | 86840 | league | localhost | league_stats | Query | 111 | Creating sort index | SELECT g.id AS gid, g.name AS gamename, SUM(p.frags) AS frags, SUM(p.kills) AS kills, SUM(p.suicides |
[19:52] [@]doh> mmm nice
[19:52] [+]Timo_Maas> i'm adding some script_name columns into those queries to show where they're coming from
[19:52] [@]doh> 130 secs and counting
[19:53] [@]doh> theres that sort index again
[19:53] [@]doh> takes ages
[19:53] [+]Timo_Maas> paste
[19:53] [@]doh> above
[19:53] [@]doh> took like 140 secs
[19:57] [@]doh> # Query_time: 143 Lock_time: 0 Rows_sent: 16 Rows_examined: 11017280
[19:57] [@]doh> use league_stats;
[19:57] [@]doh> SELECT g.id AS gid, g.name AS gamename, SUM(p.frags) AS frags, SUM(p.kills) AS kills, SUM(p.suicides) AS suicides, SU$
[19:57] [@]doh> FROM uts_player AS p, uts_games AS g WHERE p.gid = g.id GROUP BY gamename ORDER BY gamename ASC;
[19:57] [@]doh> thats the full one
[19:57] [@]doh> oops cut off
[19:58] [@]doh> # Query_time: 143 Lock_time: 0 Rows_sent: 16 Rows_examined: 11017280
[19:58] [@]doh> use league_stats;
[19:58] [@]doh> SELECT g.id AS gid, g.name AS gamename, SUM(p.frags) AS frags, SUM(p.kills) AS kills, SUM(p.suicides) AS suicides, SUM(p.teamkills) AS teamkills, COUNT(DISTINCT p.matchid) AS matchcount
[19:58] [@]doh> FROM uts_player AS p, uts_games AS g WHERE p.gid = g.id GROUP BY gamename ORDER BY gamename ASC;
[20:52] [+]Timo_Maas> <!-- loadavg: 3.54 2.58 1.87 1/141 5744
[20:52] [@]doh> its 5/10/15 mins avg
[20:52] [@]doh> and we just need the 5 min avg
[20:52] [@]doh> dunno what the last is tbh
[20:52] [@]doh> do you know FragMe ?
[20:53] [@]doh> /proc/loadavg
[20:53] [@]doh> lol the last one is the number of times the file was queried
[20:54] [+]Timo_Maas> what am i capping it on?
[20:54] [+]Timo_Maas> 3?
[20:54] [+]Timo_Maas> 4?
[20:54] [@]doh> 5
[20:54] [+]Timo_Maas> k
[20:54] [@]doh> i think forums are a bit higher then that, so utstats stop before forums do
[20:54] [@]doh> yeah
[20:54] [+]Timo_Maas> be funny if this renders utstats inoperable
[20:54] [+]Timo_Maas> :P
[20:55] [@]doh> well a loadavg of 5 is pretty high
[20:55] [@]doh> it only goes above that when all processors are busy
[20:55] [@]doh> which is bad
[20:55] [&]FragMe> some heavy disk IO can cause a 6 pretty easy
[20:55] [@]doh> yeah
[20:56] [@]doh> but it never happens on this box
[20:56] [+]Timo_Maas> right, done
[20:56] [@]doh> mostly mysql that uses the disk
[20:56] [+]Timo_Maas> we now have loadavg checking in utstats :P
[20:56] [@]doh> :D
[20:56] [@]doh> i can sleep a bit more safe then
[20:56] [@]doh> should probably test it
[20:57] [+]Timo_Maas> yeh that's what i'm trying now
[20:57] [@]doh> well i'm peaking it now
[20:57] [@]doh> just leave it at 5
[20:58] [@]doh> look in top where its going
[20:58] [@]doh> hitting 4 now
[20:58] [@]doh> its closed now
[20:58] [@]doh> it seems
[20:58] [@]doh> before 5
The stats system torp mentioned for CTF stats doesn't seem to suffer from such massive load times though.. you sure theres no some server side mod which can filter out the crap before it is sent to the stats server? - guess this will put strain on the game server then though
It's OK - I snipped out the good bitsprivate luv for timo going public!
They also seemed to have logged less than 1000 matches? Bit of a difference from over a million rounds (this was archived down to half a million when taking away data older than 2007). /edit: What MyM said ^
The log data isn't an issue as it takes fractions of a second to discard what we don't need.
Think the general concencus as per my earlier post is that we'll ditch public stats, or better still, just keep high level info (number of maps, players, kills per server) for publics, and detailed info + full stats for matches.
Still... Need to find some time to get stuck into this as it's tough to just spend 5 minutes here and there without re-thinking it each time.
It's OK - I snipped out the good bits