[BF4] Stats webpage for XpKiller's Stats Logger Plugin

[ImportBot]

Originally Posted by ty_ger07*:

 

I love your commit message lol

 

Also, you should look into angularjs. You could probably clear out all the cluttered html in the PHP scripts and return json for single page app. Wouldn't be a bad improvement. I got lost trying to see if I can setup a angualrjs version of your app. I gave up after an hour.

Apparently, I must lack the vision. What does angularjs provide other than hours of rewriting for the same user experience?

[ImportBot]

Originally Posted by Prophet731*:

 

Umm...

 

The purpose of the chat log is to be able to see the chat messages. So, that isn't a very viable option, is it?

I meant take it out of the WHERE clause. You can still keep it in the SELECT. Also to make it speedier you could have a checkbox where the user can choose to filter those specific messages out or not.

 

Apparently, I must lack the vision. What does angularjs provide other than hours of rewriting for the same user experience?

AngularJS is just a framework for the front end so you could put most of your logic to show or hide html into the user side therefor cleaning up your code server side for easier management. Re-writing it would take some time, not disputing that, but it would just benefit code maintainability and you could probably write less in a few areas. Again its just a suggestion to make it more fancy in some areas.

[ImportBot]

Originally Posted by ty_ger07*:

 

Update released:

 

- Optimizations in load time

- Missing weapon images corrected

 

 

Note: The chat log will no longer filter out server spam, commo rose, etc for load time reasons. I will leave that up to the server owner to keep that stuff out of their chat log, if they wish, using regex in XpKiller's plugin and/or by removing it from their database from time to time.

[ImportBot]

Originally Posted by ty_ger07*:

 

Caching Top 20 would be the next big performance improvement.

[ImportBot]

Originally Posted by dyn*:

 

I don't have before and after exact numbers, but the whole stats package just feels faster. I was routinely seeing +15 seconds for page generation (as stated by bottom of page) and now we're down to under 2 seconds for the main page and then under 5 seconds just browsing around.

 

Nicely done! Can't wait to see any further improvements. Thank you.

[ImportBot]

Originally Posted by ty_ger07*:

 

Thank you dyn!

 

6195 bots have been denied access

http://nbfc.no/bf4stats/

 

Wow! I am sure that performance savings adds up quickly. It might be a bad thing as far as server publicity is concered in search engine results. But good for every other reason.

[ImportBot]

Originally Posted by TMiland*:

 

http://nbfc.no/bf4stats/

 

Wow! I am sure that performance savings adds up quickly. It might be a bad thing as far as server publicity is concered in search engine results. But good for every other reason.

Yeah, it's much faster already, great job!

[ImportBot]

Originally Posted by Chelrid*:

 

** Deutsch **

Hi,

 

folgender Sachverhalt:

 

2 Gameserver, bei 2 verschiedenen Hostern. Für jeden Server eine eigene Datenbank. Beide Hoster ermöglichen keinen Kontakt zum jeweils anderen Hoster.

 

Somit müsste ich für beide Server die Stats Seite 2x erstellen. Oder kann ich auch beide Datenbanken mit einer Config abfragen?

 

**Google translated English**

Hi,

 

the following facts:

 

2 game servers in 2 different hosters. For each server its own database. Both Hoster allow no contact with each other hosters.

 

So I would create for both the Server Stats Page 2x. Or can I check both databases with a config?

[ImportBot]

Originally Posted by ty_ger07*:

 

You would need to create two separate stats pages -- one for each server -- or customize config/config.php and common/connect.php to connect to two different databases.

[ImportBot]

Originally Posted by Chelrid*:

 

You would need to create two separate stats pages -- one for each server -- or customize config/config.php and common/connect.php to connect to two different databases.

I can't php.

[ImportBot]

Originally Posted by ty_ger07*:

 

I can't php.

Use two separate pages in different folders of your server.

[ImportBot]

Originally Posted by ty_ger07*:

 

I think I have the top 20 caching working well. I am just doing some testing first.

[ImportBot]

Originally Posted by ty_ger07*:

 

Update released!

http://tyger07.github.io/BF4-Server-Stats

 

Demo:

http://open-web-community.com/bf4sta...tats/index.php

 

Change:

- Top 20 players are cached for performance reasons

 

 

 

NOTE: For those who are curious, these are the caching procedures:

 

Player Rank in individual servers (score, kills, kdr, hsr): recalculated and cache updated after 12 hours. I didn't use XpKiller's default score and kill rank and instead recalculate it simply because XpKiller's rank doesn't have a second sort-by which causes people with the same kills or same score to randomly swap ranks. Mine sorts by player name as secondary sort in case their kills or score are the same between two players which makes everything jive instead of appearing slightly random.

 

Player Rank in Combined Servers (score rank in signature image): recalculated and cache updated after 12 hours

 

Combined Server total players (used in server list, server stats, leaderboard pagination, global rank [out of # players]): recalculated and cache updated after 12 hours

 

Total Chat messages (pagination to find last page): recalculated and cache updated after 1 hour

 

Top 20 Leaderboard (only the first 20 to make default pages load fast): recalculated and cache updated after 12 hours

 

 

Additional Notes

Caching means that the cached data will not be completely correct 100% of the time like it used to be prior to caching. But it is very unlikely that anyone would notice a small statistical difference.

 

If you are the first user to view a page before it is cached or the first person to view a page when it comes due to be recached, you will experience the old slow loading experience due to everything being recalculated.

[ImportBot]

Originally Posted by ty_ger07*:

 

Before I commit to this work, I need to know if there is interest.

 

Features of proposed stats database cleanup script (with options to include or exclude each of the following actions when you run the script):

Delete blank chat log rows

Delete commo rose messages from chat log

Delete server spam from chat log

Delete chat messages which start with !, @, or /

Delete chat messages which are over X number of days/weeks/months old

Delete player data if players have no score, kills, or deaths in their stats record

Delete chat messages from players with no score, kills, or deaths

Recalculate ranks and total players in server stats after deleting the above players from the database

 

Thank you!

[ImportBot]

Originally Posted by Prophet731*:

 

Before I commit to this work, I need to know if there is interest.

 

Features of proposed stats database cleanup script:

Delete blank chat log rows

Delete commo rose messages from chat lot

Delete server spam from chat log

Delete chat messages which start with !, @, or /.

Delete player data if players have no score, kills, or deaths in their stats record.

Delete chat messages from players with no score, kills, or deaths.

Recalculate total players after deleting the above players from the database.

 

Thank you!

You could just provide the scripts to do this and have the user run them if they so desire. Doing a this query shouldn't take very long. Code:

SELECT COUNT(PlayerID) AS Total FROM tbl_playerdata;

[ImportBot]

Originally Posted by ty_ger07*:

 

You could just provide the scripts to do this and have the user run them if they so desire. Doing a this query shouldn't take very long. Code:

SELECT COUNT(PlayerID) AS Total FROM tbl_playerdata;

We are all capable of doing anything we set our minds to, but not all are interested in doing the messy work. I don't think the majority of users know how to log into their database or execute queries.

 

Additionally, some of the above proposed functions cannot be executed in a single query. They will either require multiple queries of different tables with different steps taken depending on circumstances and/or require multiple queries repeated in steps due to processing time incurred if it were attempted in mass which would run up against database processing time restrictions resulting in a timeout of the query at some random point part-way through.

 

It should also be noted that your query will not work (at least not in a useful way) since it does not take into account GameID or ServerID.

[ImportBot]

Originally Posted by dyn*:

 

I am very impressed how your project has developed over the past year. It went from having to manually edit a config file for each server & longer load times to now simply one config file which can be moved from install to install and caching enabled!

 

We're now at:

server compiled page in 1.658 seconds

 

And loading from page to page feels pretty snappy!

 

To answer your question, I'm not sure about the best way to go about cleaning the database. It would be very convenient to periodically clear out the really old data and also the spam; just don't know the best way to accomplish there. Not sure if having it be a part of your stats page would be best or perhaps if perhaps an add on or it's own 'thing'? I would worry about having a user / admin accidentally change the settings and delete data that should not be deleted. But would tools like you mentioned be useful? I do think so.

[ImportBot]

Originally Posted by ty_ger07*:

 

I would definitely include a password (whatever your database password is) with brute force hack protection (cool-down period after incorrect password entry). I was thinking it would be another file in another folder. But maybe it should be its own thing.

[ImportBot]

Originally Posted by Prophet731*:

 

We are all capable of doing anything we set our minds to, but not all are interested in doing the messy work. I don't think the majority of users know how to log into their database or execute queries.

 

Additionally, some of the above proposed functions cannot be executed in a single query. They will either require multiple queries of different tables with different steps taken depending on circumstances and/or require multiple queries repeated in steps due to processing time incurred if it were attempted in mass which would run up against database processing time restrictions resulting in a timeout of the query at some random point part-way through.

 

It should also be noted that your query will not work (at least not in a useful way) since it does not take into account GameID or ServerID.

Well I was saying just create the SQL files that perform these functions and not make it something part of the software. That way it can just be ran as standalone. You could add in some sort of admin side to perform maintenance tasks that the user can start. Then you can just call file_get_contents() and execute that SQL right away with whatever function mysqli uses for raw queries. Probably the one that will take the most time would be the chatlog cleanup. I know some users don't exactly know how to log into their database but im sure they can find out how by going to their hosting companies faq on how to access it.

 

As for the queries the chatlog one would be simple as you can do something like this. On larger databases this could take a long time to finish. Took 15 minutes to add a column to the table with our 4.5GB worth of chatlogs.

 

Code:

DELETE FROM `tbl_chatlog` 
WHERE
    `logMessage` = ''
    OR `logMessage` LIKE 'ID_CHAT_%'
    OR `logMessage` LIKE '!%'
    OR `logMessage` LIKE '@%'
    OR `logMessage` LIKE '/%'
    OR `logSoldierName` = 'Server';

[ImportBot]

Originally Posted by ty_ger07*:

 

Well I was saying just create the SQL files that perform these functions and not make it something part of the software. That way it can just be ran as standalone. You could add in some sort of admin side to perform maintenance tasks that the user can start. Then you can just call file_get_contents() and execute that SQL right away with whatever function mysqli uses for raw queries. Probably the one that will take the most time would be the chatlog cleanup. I know some users don't exactly know how to log into their database but im sure they can find out how by going to their hosting companies faq on how to access it.

 

As for the queries the chatlog one would be simple as you can do something like this. On larger databases this could take a long time to finish. Took 15 minutes to add a column to the table with our 4.5GB worth of chatlogs.

 

Code:

DELETE FROM `tbl_chatlog` 
WHERE
    `logMessage` = ''
    OR `logMessage` LIKE 'ID_CHAT_%'
    OR `logMessage` LIKE '!%'
    OR `logMessage` LIKE '@%'
    OR `logMessage` LIKE '/%'
    OR `logSoldierName` = 'Server';

I don't like file_get_contents. But that's a different subject.

 

I have never had large databases to test with, but in my testing, it takes a lot longer to check if players have no stats in any servers and, if so, delete their player data and stats data. Just my donated 300 MB database took about 30 minutes to perform that function in 5000 row chunks. It really depends on the server of course. Who can possibly predict someone elses' server performance and database size? It might be biting off more than I can chew.

[ImportBot]

Originally Posted by Prophet731*:

 

I have always been afraid of file_get_contents and am afraid of requiring others to set up their server to allow the use of something I don't think is wise. But that's a different subject.

 

I have never had large databases to test with, but in my testing, it takes a lot longer to check if players have no stats in any servers and, if so, delete their player data and stats data. Just my donated 300 MB database took about 30 minutes to perform that function in 5000 row chunks. It really depends on the server of course. Who can possibly predict someone elses' server performance and database size? It might be biting off more than I can chew.

file_get_contents is pretty standard. In my app I have some custom sql files that are stored in a folder. All I need to do to run them is reference a path and load the sql file. PHP has changed a lot of the past few years for the better of course.

 

Code:

$results = DB::statement( file_get_contents( storage_path() . '/sql/some_sql_code.sql' ) );

What software are you using for your DB testing? XAMPP or WAMP? I'm assuming you're testing locally. In my experience i've noticed that the mysql setup isn't always the best with those software so it could be mis configuration causing performance problems then the actual sql being ran. I could throw that DB you have on my VPS and give you some creds to it to perform some testing on. DB is configured with plenty of RAM and CPU power.

 

This querys ran for ~2 seconds and return the player count for each server and each game.

 

Code:

SELECT 
    tbl_server.ServerName,
    tbl_games.Name AS Game,
    COUNT(tbl_playerdata.PlayerID) AS total
FROM
    tbl_playerdata
        INNER JOIN
    tbl_server_player ON tbl_playerdata.PlayerID = tbl_server_player.PlayerID
        INNER JOIN
    tbl_server ON tbl_server_player.ServerID = tbl_server.ServerID
        INNER JOIN
    tbl_games ON tbl_server.GameID = tbl_games.GameID
GROUP BY tbl_server.GameID , tbl_server_player.ServerID;


SELECT 
    tbl_games.Name AS Game,
    COUNT(tbl_playerdata.PlayerID) AS total
FROM
    tbl_playerdata
        INNER JOIN
    tbl_games ON tbl_playerdata.GameID = tbl_games.GameID
GROUP BY tbl_playerdata.GameID

[ImportBot]

Originally Posted by ty_ger07*:

 

Those queries took 0.3 to 0.4 seconds on my database. But "my" borrowed copy of someone elses' database is only 300 MB with "80316" (players according to your query); so faster than your GBs and GBs of data.

 

Interestingly, there are actually 77839 combined players (not 80316) because 2477 of them are more than one server and counted as duplicates. I find this query to be accurate, although 3 times slower.

 

Code:

SELECT COUNT(DISTINCT tpd.`PlayerID`) AS count, tg.`Name` AS game
FROM  `tbl_playerdata` tpd
INNER JOIN `tbl_server_player` tsp ON tsp.`PlayerID` = tpd.`PlayerID`
INNER JOIN `tbl_playerstats` tps ON tps.`StatsID` = tsp.`StatsID`
INNER JOIN `tbl_games` tg ON tg.`GameID` = tpd.`GameID`
GROUP BY tg.`GameID`

I am using GoDaddy Shared Linux Hosting.

http://toolbar.netcraft.com/site_rep...-community.com

[ImportBot]

Originally Posted by Prophet731*:

 

Those queries took 0.3 to 0.4 seconds on my database. But "my" borrowed copy of someone elses' database is only 300 MB with "80316" (players according to your query); so faster than your GBs and GBs of data.

 

Interestingly, there are actually 77839 combined players (not 80316) because 2477 of them are more than one server and counted as duplicates. I find this query to be accurate, although 3 times slower.

 

Code:

SELECT COUNT(DISTINCT tpd.`PlayerID`) AS count, tg.`Name` AS game
FROM  `tbl_playerdata` tpd
INNER JOIN `tbl_server_player` tsp ON tsp.`PlayerID` = tpd.`PlayerID`
INNER JOIN `tbl_playerstats` tps ON tps.`StatsID` = tsp.`StatsID`
INNER JOIN `tbl_games` tg ON tg.`GameID` = tpd.`GameID`
GROUP BY tg.`GameID`

I am using GoDaddy Shared Linux Hosting.

http://toolbar.netcraft.com/site_rep...-community.com

Each player id is unique. The reason why yours is slower is because of the join on tbl_server_player. Essentially, you looking for players who have stats while my is looking for how many players there are for each game. So your query is right for returning how many players actually have stats while mine is returning how many players (with/without stats) there are. Your query took 9.11 seconds to run on our database.

 

The stats logger and adkats add players to the tbl_playerdata table regardless of stats because it also contains the PB and EA GUID.

 

Screenshot of the results showing yours vs mine.

 

[ImportBot]

Originally Posted by ty_ger07*:

 

I should add a player search tab to the leaderboard where you can enter the player's name and it will find their place on the leaderboard and stats without searching for that player's full stats page/profile.

[ImportBot]

Originally Posted by ty_ger07*:

 

Each player id is unique. The reason why yours is slower is because of the join on tbl_server_player. Essentially, you looking for players who have stats while my is looking for how many players there are for each game. So your query is right for returning how many players actually have stats while mine is returning how many players (with/without stats) there are. Your query took 9.11 seconds to run on our database.

 

The stats logger and adkats add players to the tbl_playerdata table regardless of stats because it also contains the PB and EA GUID.

 

Screenshot of the results showing yours vs mine.

 

That does not seem to be true. Look at my combined server stats leaderboard and paginate to the last pages. You will find thousands of players have no stats values but are still counted. There is something about the playerid to statsid conversion which makes your count incorrect by excluding statsid. I used to find total players by your method, but it didn't work because the last tens of pagination pages returned no results.

 

In a perfect world, all playerids should have a statsid because they joined a server in order to have their playerdata recorded and therefore the table_server should have a statsid conversion to a 0 stats row. Unfortunately, there are thousands of players who do not fit that scenario.

 

Because the query is slow, I cache the result for 12 hours. Since the leaderboard is top to bottom by default and that is all anyone cares about, it doesn't matter that the bottom players with 0's aren't shown due to cache count and live count mis-match.

[ImportBot]

Originally Posted by Prophet731*:

 

That does not seem to be true. Look at my combined server stats leaderboard and paginate to the last pages. You will find thousands of players have no stats but are still counted. There is something about the playerid to statsid conversion which makes your count incorrect. I used to find total players by your method, but it didn't work because the last tens of pagination pages returned no results.

Some players won't be added to the tbl_server_player unless they have stats to be assigned a statsid. This would happen if the player joins but then leaves very quickly (game crashes, internet connection loss, etc..).

[ImportBot]

Originally Posted by ty_ger07*:

 

Some players won't be added to the tbl_server_player unless they have stats to be assigned a statsid. This would happen if the player joins but then leaves very quickly (game crashes, internet connection loss, etc..).

Well, regardless, I can only count players who have both a playerid AND a statsid otherwise the pagination doesn't work and error messages are displayed. So, unfortunately, I need to use the slow query and cache it. One of my recent updates made sure all queries took into account game id, server id, and stats id. In the update notes, it stated that stats are now more accurate but queries are slower. I needed to do this in order to calculate the correct ranks and not see error messages on the last pagination pages, stats signature images, and player stats pages.

[ImportBot]

Originally Posted by Prophet731*:

 

Well, regardless, I can only count players who have both a playerid AND a statsid otherwise the pagination doesn't work and error messages are displayed. So, unfortunately, I need to use the slow query and cache it. One of my recent updates made sure all queries took into account game id, server id, and stats id. In the update notes, it stated that stats are now more accurate but queries are slower. I needed to do this in order to calculate the correct ranks and not see error messages on the last pagination pages, stats signature images, and player stats pages.

Understandable. Pagination is always a pain.

[ImportBot]

Originally Posted by Hutchew*:

 

Couldn't you just exclude players with stats less than x ( perhaps total score less than 25000)? That would reduce the total # of pages returned. Then if they looked for themselves, give them a generic " you don't exist. Play more to get stats".

 

Lol, just thinking out loud, thought I would share my ramblings.

 

 

 

Hutchew

[ImportBot]

Originally Posted by ty_ger07*:

 

Couldn't you just exclude players with stats less than x ( perhaps total score less than 25000)? That would reduce the total # of pages returned. Then if they looked for themselves, give them a generic " you don't exist. Play more to get stats".

 

Lol, just thinking out loud, thought I would share my ramblings.

 

 

 

Hutchew

Performance wise, it would make no difference; maybe even slower (haven't tested). Your suggestion still requires checking their stats id in order to check their score and that is where the performance bottleneck is. Bigger databases will be slower. Working on an acceptable way to remove players and information from the database -- which you guys agree should be removed -- would be a performance booster to some degree depending on the ratio of removed versus not removed. The leaner the database is, the faster it will be. But, you may not want to remove too much for various reasons which will decrease the performance benefit of the removal function.

 

If I knew that 100% of user's databases have removed player ids which have no stats id, I could use the query which is 3 to 5 times faster. Unfortunately, even if I release the script which removes those worthless dead weights, I can't be sure that the script was run and am stuck using the slow accurate script.