Welcome to Open Carnage

A resource for Halo Custom Edition and MCC modding, with unique means of rewarding content creation and support. Have a wander to see why we're worth the time! - EST. 2012

mouseboyx

Webserver Statistics Logging

24 posts in this topic

I think you're on the right track. I'd argue for keeping only the last completed game for each player. Everything can be tabulated into your player stats tables before it is deleted. This way you can dramatically eliminate the number of rows and joins you'll be doing.

Enclusion likes this

Kavawuvi: one of these days these glutes are gonna squawk all over you

Share this post


Link to post
Share on other sites

Tiddy-bits:

1 hour ago, Sunstriker7 said:

I think you're on the right track. I'd argue for keeping only the last completed game for each player. Everything can be tabulated into your player stats tables before it is deleted. This way you can dramatically eliminate the number of rows and joins you'll be doing.

Should this be done so that instead of a row for each game simplify it into a row for each player killing another player regardless of which game or possibly server it was played on?  I'm sort of learning while I go with this stuff I've done other things like this in the past but none of it was made to last. 

 

It could be that a game history shouldn't be kept at all except for the last and current games being played.  That would work I think the table with all the who killed who data could be trimmed to only last and current games, with the remaining data put into rows only based on what server they were done on, or possibly server wide.  Then there are 2 other tables that only keep track of kills and deaths on a game by game basis.  That way it would still have a game history but not a detailed game history. 

 

I was also trying to see what number of rows things would slow down, I added something like 35k dummy data rows and it still seemed fine, but I know it could get a lot bigger than that, like a years worth of games would be many many rows.

 

Do you have experience with database type work?  I would really appreciate having some help from at least one other person to get this thing more acceptable.

Enclusion and Sunstriker7 like this

Share this post


Link to post
Share on other sites

I'm working on a computer science degree and I've done a class on relational database systems and I'm currently working on building a database system to keep records of and schedule construction projects at work. I'm no expert yet but I know enough to get by.

 

I've also done research on how many rows is too many and according to the fine folks at stack exchange, you really don't need to worry about it until you start getting into the billions. Even then, the consensus is that it's often better to have fewer massive tables than many smaller tables you're constantly running complex operations on.

 

16 minutes ago, mouseboyx said:

instead of a row for each game simplify it into a row for each player killing another player regardless of which game or possibly server it was played on?

 

Yes, that is what I personally would do. I don't really see the need to keep a permanent record of what happened in individual games or even servers. You can still keep the info of who killed who in another table linked to your player table. This would allow it to grow indefinitely, but instead of adding a new row for each kill, give the combination of the playerID, killerID and weaponID columns the UNIQUE constraint (which you can also use as a composite primary key for that table). The last column would simply be the running total. Your current game table can be added to this table at the end of every match.

 

Also I'm on the OC discord

Enclusion, Tucker933 and mouseboyx like this

Kavawuvi: one of these days these glutes are gonna squawk all over you

Share this post


Link to post
Share on other sites
45 minutes ago, Sunstriker7 said:

I'm working on a computer science degree and I've done a class on relational database systems and I'm currently working on building a database system to keep records of and schedule construction projects at work. I'm no expert yet but I know enough to get by.

 

I've also done research on how many rows is too many and according to the fine folks at stack exchange, you really don't need to worry about it until you start getting into the billions. Even then, the consensus is that it's often better to have fewer massive tables than many smaller tables you're constantly running complex operations on.

 

 

Yes, that is what I personally would do. I don't really see the need to keep a permanent record of what happened in individual games or even servers. You can still keep the info of who killed who in another table linked to your player table. This would allow it to grow indefinitely, but instead of adding a new row for each kill, give the combination of the playerID, killerID and weaponID columns the UNIQUE constraint (which you can also use as a composite primary key for that table). The last column would simply be the running total. Your current game table can be added to this table at the end of every match.

 

Also I'm on the OC discord

Thanks that really summarizes well what should be done, I'm hoping to keep my existing data so I'll probably wait until nobody is on the servers then add up the existing rows, except for the last gameID's that have already started, they will be able to add their data after their games have finished.  I have an associates degree in web development, and the database course was about the same amount of learning as a 2 day seminar, so I've had to teach myself most of it.  In addition I didn't write out or plan anything I just had it all in my head how it should work, which I'm a bit embarrassed at how sloppy it turned out.  But that's why it's all a test, and can be improved upon :)

Enclusion and Sunstriker7 like this

Share this post


Link to post
Share on other sites
7 hours ago, Sunstriker7 said:

I've been using https://dbdiagram.io/home

 

You can design your tables and relations with a pseudocode and then export it into whatever systems you use.

I've seen organizers similar to that before although it was probably more graphical rather than writing psuedo code.  I'm honestly going to have to take a bit of a break from working on this, I keep obsessing over how it should work and the features it should have.  I was thinking that the player stats could be inserted directly into a table that wasn't sorted or id'ed by game or server, when each kill/death request is made, because there are already tables that keep track of only kills and deaths per game id.  There's not much use in keeping that data for a short amount of time, unless there's a performance hit in inserting that data because of finding where it already exists and updating or incrementing the counter, but it would still have to be done on each new game anyway, and the logic for it to work would have to be done at some point why not make it automatically like that.  Probably the biggest mistake someone just starting out database work is to put everything in one huge table, I probably made the mistake of wanting all the information, like I want to know what game it happened on and what server it happened on in addition to every other detail, which isn't that bad I guess but not very practical.  The end cumulative totals are actually what's more interesting in the long run rather than what was the most used weapon in any particular game, or who got the most headshots in a particular game.  But organizing it by server seems more logical because a player might love playing on X server but hate Y server and never play on it because of the game type or mods.  But that would create more rows.  Probably only a separate table to keep track of which players were seen on which servers, but not categorizing the actual data into separate rows based on server would be the way to go.  Well probably after a week or two and a break so I can come back with fresh eyes, I'll have something new. 

Enclusion likes this

Share this post


Link to post
Share on other sites
mysql> describe killed_by_player;
+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | bigint(20) | NO   | PRI | NULL    | auto_increment |
| killer       | bigint(20) | YES  |     | NULL    |                |
| victim       | bigint(20) | YES  |     | NULL    |                |
| weapon_id    | bigint(20) | YES  |     | NULL    |                |
| hitstring_id | bigint(20) | YES  |     | NULL    |                |
| backtap      | int(11)    | YES  |     | NULL    |                |
| times        | bigint(20) | YES  |     | NULL    |                |
+--------------+------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> select * from killed_by_player where id>50 limit 10;
+----+--------+--------+-----------+--------------+---------+-------+
| id | killer | victim | weapon_id | hitstring_id | backtap | times |
+----+--------+--------+-----------+--------------+---------+-------+
| 51 |      8 |      9 |         8 |            2 |       0 |     5 |
| 52 |      9 |      8 |         5 |            2 |       0 |     1 |
| 53 |      8 |      9 |         8 |            1 |       0 |     2 |
| 54 |      9 |      8 |        15 |            2 |       0 |     3 |
| 55 |      9 |     10 |         9 |            2 |       0 |     3 |
| 56 |      8 |     10 |        15 |            2 |       0 |     1 |
| 57 |      8 |      9 |         6 |            2 |       0 |     2 |
| 58 |      8 |     10 |         9 |            2 |       0 |     1 |
| 59 |      8 |     10 |         8 |            2 |       0 |     1 |
| 60 |      8 |     11 |         4 |            2 |       0 |     5 |
+----+--------+--------+-----------+--------------+---------+-------+
10 rows in set (0.00 sec)

This is the new format I came up with it's still tracking a lot of information, but no longer does a game by game or server by server track, it's only player by player.  It's all integers so it should speed up lookup times.  I was noticing before I dropped my database that the original method was taking quite a long time to load in terms of a web page, anything over 750ms is probably a long time.   Also the original code before this change had a query error where counts would be updated for all rows of a particular key rather than only the row that had a specific key and hitstring.  I was also trying to figure out a worst case scenario for number of rows that could in theory be added:

 

( ((AllPlayers-1)*AllPlayers)*NumberOfWeapons*PossibleHitstrings*BacktapBool(1))+(BacktapBool(1)*MeleeWeapon*((AllPlayers-1)*AllPlayers) )

 

All Players is ever changing, most likely not every player will kill every other player.

Number Of Weapons is 39 different halo tag paths with the default maps (not including custom maps)

Number of Hitstrings 'head','body','legs','ShieldGenerator' 4

Backtap is either on or off so 2, basically 1 for these purposes

Melee Weapon is any weapon capable of melee including flags and balls about 12 in classic maps

For 100 players if they all kill each other with every weapon and all get backtaps with every weapon and they all kill each other in all 4 different hitstrings.

 

(((100-1)*100)*39*4*1)+(1*12*((100-1)*100))=1,663,200

 

The real world scenario is probably not that many though, but I haven't tested whether backtaps can be applied to body, head, or legs, this is assuming only body.  Otherwise multiplying the second string of multipliers by 4 would work. Some players only play at certain times of day with others that do the same, so I'm guessing this many rows won't be added for 100 players, or even 300, and some servers never have flags or balls, some only have rockets etc..  I hope my math is correct for this analysis. 

 

This is the current ratio of number of players to rows in the stats table, of course this should and could grow even if the player count remained the same, due to killing by different means or body parts.

mysql> select count(*) from players;
+----------+
| count(*) |
+----------+
|      291 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from killed_by_player;
+----------+
| count(*) |
+----------+
|     4336 |
+----------+
1 row in set (0.00 sec)

There's also the fact that a new player is added for each new ip address/player name combination, so someone could keep joining under different names, and get a kill or death then that would update a new row for the players table.

 

This could be refined further so that there is no victim attached to the methods of death, and only kller,weapon, hitstring, and backtap are stored along with the total.  Then maybe a separate table to store the killer and victim data with a kill counter, but doing so would deprive the ability to see what weapons and body parts any player killed another player with.  I don't know if this is too ambitious currently having all the information stored per victim and killer, but at least it is persistent across servers and new games. 

 

The maximum rows in this new method is largely dependent on how many different players another player kills, but if they kill the same ones all the time using the same method the table will not grow, only the totals will.

Edited by mouseboyx

Share this post


Link to post
Share on other sites

I have some suggestions. I'll explain my reasoning on them all and then you can decide if they'll work for you.

 

The first is pretty straightforward. I would simply change your backtap column to be data type bool because it can only have two possible values. I would also either give it the NOT NULL flag or DEFAULT it to 0.

 

The next point I have is your choice of using bigint. It's not really a big deal but the maximum size of a bigint is 9,223,372,036,854,775,807. A regular int maxes out at 2.14 billion and seems more than adequate for your needs.

 

Do you need all that hitstring data or are you only interested in headshots? If all you care about is headshots you might simply make it a bool. This would reduce a lot of needless complexity.

 

You mentioned melee weapon. Is that a weapon id you have set up to track melee kills? If so, now that I think about it, you could make a special weapon in your weapons table that is a backtap and therefore eliminate it as it's own, hardly used column.

Enclusion, mouseboyx and Tucker933 like this

Kavawuvi: one of these days these glutes are gonna squawk all over you

Share this post


Link to post
Share on other sites
10 hours ago, Sunstriker7 said:

I have some suggestions. I'll explain my reasoning on them all and then you can decide if they'll work for you.

 

The first is pretty straightforward. I would simply change your backtap column to be data type bool because it can only have two possible values. I would also either give it the NOT NULL flag or DEFAULT it to 0.

 

The next point I have is your choice of using bigint. It's not really a big deal but the maximum size of a bigint is 9,223,372,036,854,775,807. A regular int maxes out at 2.14 billion and seems more than adequate for your needs.

 

Do you need all that hitstring data or are you only interested in headshots? If all you care about is headshots you might simply make it a bool. This would reduce a lot of needless complexity.

 

You mentioned melee weapon. Is that a weapon id you have set up to track melee kills? If so, now that I think about it, you could make a special weapon in your weapons table that is a backtap and therefore eliminate it as it's own, hardly used column.

I'm probably lazy about the choices of data types like bigint, I made it a bigint so I wouldn't have to think about how big things could get.  Yeah you're right about changing the data types to things that they actually should be representing.

 

I like the idea of a pre-inserted row for backtaps it makes total sense and it could be used in either the weapons or hitstrings table.  I don't care too much about tracking every body part, but after a while that information is interesting, like the ratio of each body part to each other one can be revealing about a player.  Something else with all these changes being made it could be a good idea to include these as potential configurable options.  In the installation when you have a blank slate and install it on your web server you have to option to leave everything default, which would probably be configured to be a minimal amount of data stored.  Then give options to track lots more information if someone would want to.  But that would add needless complexity to the whole thing, maybe it could be hidden away so it could be changed only if someone really cared enough to do it.  But one thing that would take a lot of time to implement would be the ability to configure that after the service was installed, then you have historic data that doesn't conform to the new structure, and someone would ask why don't the totals of these things actually add up? 

 

I haven't yet committed the changes to the github repo though, but it's running nicely in the testing server.  I'm not really a perfectionist when it comes to this stuff I get excited when things are working and don't really consider much beyond that.

Enclusion and Sunstriker7 like this

Share this post


Link to post
Share on other sites

What you have will totally work though and it's really neat you've taken the time to do all of this. I'm excited to see where this goes.

 

One thing that would really cool is if you collected other stats based on gametype. In your OP you mentioned you'd like to make your script public but you would need to preserve the data integrity somehow. If you could get that working I think it would be badass. I'll do some thinking about it too.

Enclusion likes this

Kavawuvi: one of these days these glutes are gonna squawk all over you

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.