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

[ImportBot]

Originally Posted by ColColonCleaner*:

 

I will be thinking about this...

I am still a little puzzled while adding up the numbers and comparing costs and benefits. If fetching 20 rows takes 4 times longer, it makes me wonder if the hassle and inconvenience (to both me and the user) of overhauling the pagination design is worth it for very little comparable gain. What is a 20 second load time compared to 25 second load time? Both are awful numbers and feel awful for the user. Is it worth hampering the functionality for the user who will be less than impressed by the load time either way?

The thing is...other web services who offer chat logging have almost instant response for searches using substrings of messages. So what are they doing differently?

 

Perhaps a rethink of how this is working from a design standpoint would help.

[ImportBot]

Originally Posted by Jamesonp*:

 

I really appreciate your work and it looks great but there are some fatal flaws with the latest version. We're going to be working on it on our end at some point to try and make it efficient.

 

An example of a fatal flaw - I've caused my MySQL instance to run out of memory using over 8GB of RAM and start swapping which causes MySQL to become defunct and fail when clicking links quickly.

 

I'm sure I'm not the only one with a database that's super large and won't be the last. I'm just not willing to give up stats and trim my database that we use on the administration side for player management.

[ImportBot]

Originally Posted by ColColonCleaner*:

 

There has to be a better way of storing these.

 

The tblchatlog table is stored using InnoDB, which has terrible performance for parsing full text messages, especially in large quantity.

 

MyISAM, although less functional, provides much higher performance when searching full text messages, so player names and their chat logs.

 

Has anyone tried converting their chatlog tables over to see if any performance benefits can be found?

 

EDIT: I know foreign key constraints do not work with MyISAM. They can be added, but they will not be enforced, so this shouldn't cause any issues as long as all the interacting scripts handle their inserts correctly.

[ImportBot]

Originally Posted by Prophet731*:

 

There has to be a better way of storing these.

 

The tblchatlog table is stored using InnoDB, which has terrible performance for parsing full text messages, especially in large quantity.

 

MyISAM, although less functional, provides much higher performance when searching full text messages, so player names and their chat logs.

 

Has anyone tried converting their chatlog tables over to see if any performance benefits can be found?

 

EDIT: I know foreign key constraints do not work with MyISAM. They can be added, but they will not be enforced, so this shouldn't cause any issues as long as all the interacting scripts handle their inserts correctly.

Remember the read and write locks with MyISAM. If the chatlog table is being written too you cant do a select on it at the same time so it will add delay to that. With multiple servers writing to it a lot it will cause delays in the query.

[ImportBot]

Originally Posted by Prophet731*:

 

I will be thinking about this...

I am still a little puzzled while adding up the numbers and comparing costs and benefits. If fetching 20 rows takes 4 times longer, it makes me wonder if the hassle and inconvenience (to both me and the user) of overhauling the pagination design is worth it for very little comparable gain. What is a 20 second load time compared to 25 second load time? Both are awful numbers and feel awful for the user. Is it worth hampering the functionality for the user who will be less than impressed by the load time either way?

Try a simple pagination with just Previous and Next options. That's how I do it for my chatlog searching thanks to laravels pagination simple function.

 

This is my controller which handles the chat log searching page. I need to overhaul it and make it to where it's not server specific and clean it up. I wrote it very messy.

[ImportBot]

Originally Posted by Jamesonp*:

 

There has to be a better way of storing these.

 

The tblchatlog table is stored using InnoDB, which has terrible performance for parsing full text messages, especially in large quantity.

I wonder if altering the column to use VARCHAR(100) would be a more viable option. I've seen mixed statements on whether text and varchar are treated the same with INNODB.

 

I doubt MyISAM would be a good choice for this table due to the lock limitations.

[ImportBot]

Originally Posted by Prophet731*:

 

I wonder if altering the column to use VARCHAR(100) would be a more viable option. I've seen mixed statements on whether text and varchar are treated the same with INNODB.

 

I doubt MyISAM would be a good choice for this table due to the lock limitations.

Altering the column wouldn't do anything. It's just the indexing that's the problem otherwise it would better with search actual messages.

[ImportBot]

Originally Posted by Jamesonp*:

 

Altering the column wouldn't do anything. It's just the indexing that's the problem otherwise it would better with search actual messages.

Well the only reason why I brought that up was because with MyISAM a text column is scanned each time due to not being allowed in memory vs varchar which is. I'm just not 100% sure that's the case with InnoDB.

[ImportBot]

Originally Posted by Prophet731*:

 

Well the only reason why I brought that up was because with MyISAM a text column is scanned each time due to not being allowed in memory vs varchar which is. I'm just not 100% sure that's the case with InnoDB.

Something like Elasticsearch would be awesome to implement. Problem is it more difficult to setup and configure and what not. I wanted to set this up for ADK to improve the chat log search ability but it requires a bit more then just an slap slap connect and go. I might find a use for it someday.

[ImportBot]

Originally Posted by Jamesonp*:

 

That's the first I've ever heard of Elasticsearch and it looks pretty amazing. I was just thinking of using something like memcached to see what that'd do for me.

[ImportBot]

Originally Posted by Prophet731*:

 

That's the first I've ever heard of Elasticsearch and it looks pretty amazing. I was just thinking of using something like memcached to see what that'd do for me.

Memcached wouldn't really be used for something like that. Run out of memory pretty quickly on larger DB I would assume.

[ImportBot]

Originally Posted by Jamesonp*:

 

Memcached wouldn't really be used for something like that. Run out of memory pretty quickly on larger DB I would assume.

You're probably right. Still might be worth a try. Maybe I'll spin up a Google Compute Engine instance and see.

[ImportBot]

Originally Posted by ty_ger07*:

 

I really appreciate your work and it looks great but there are some fatal flaws with the latest version. We're going to be working on it on our end at some point to try and make it efficient.

 

An example of a fatal flaw - I've caused my MySQL instance to run out of memory using over 8GB of RAM and start swapping which causes MySQL to become defunct and fail when clicking links quickly.

 

I'm sure I'm not the only one with a database that's super large and won't be the last. I'm just not willing to give up stats and trim my database that we use on the administration side for player management.

What do you want me to say?

 

Before:

https://github.com/tyger07/BF4-Serve...ommon/chat.php

 

After:

https://github.com/tyger07/BF4-Serve...ommon/chat.php

[ImportBot]

Originally Posted by ty_ger07*:

 

Try a simple pagination with just Previous and Next options. That's how I do it for my chatlog searching thanks to laravels pagination simple function.

 

This is my controller which handles the chat log searching page. I need to overhaul it and make it to where it's not server specific and clean it up. I wrote it very messy.

Jamesonp suggested the same.

 

The problem though is that the pagination code wasn't his issue. His issue was with selecting 20 rows of data from any location. How can I fix that issue other than try to help him ban the 40 bots from his site which were overloading his database? Did you ever take a look at how many users were viewing his stats pages at any one time? It was insane. I am assuming that the text box and easier navigation were a bots dream and they were going ballistic with it.

 

I have not changed the pagination code itself ever; the only thing which changed was its appearance. So for Jamesonp to make such a claim... well whatever.

 

I totally agree that I could optimize it by making the pagination more simple. But the loss in usability seems like a steep cost in favor of a page which loads in 4 seconds instead of 5 seconds (in Jamesonp's case). Both are slow load times and the pagination and query are obviously not the root causes.

 

Assuming that bots were the root cause since nothing else changed, I would have to assume that increasing the text length required before a jquery is fired off and adding a delay between text input and jquery refresh would be a big performance boost. As it is, it will fire off a new query as each letter is entered (to filter down the results real-time) which causes a rapid-fire succession of queries.

[ImportBot]

Originally Posted by Prophet731*:

 

Jamesonp suggested the same.

 

The problem though is that the pagination code wasn't his issue. His issue was with selecting 20 rows of data from any location. How can I fix that issue other than try to help him ban the 40 bots from his site which were overloading his database? Did you ever take a look at how many users were viewing his stats pages at any one time? It was insane. I am assuming that the text box and easier navigation were a bots dream and they were going ballistic with it.

 

I have not changed the pagination code itself ever; the only thing which changed was its appearance. So for Jamesonp to make such a claim... well whatever.

Are the bots ones by search engines or people hitting his site in an attempt to bottleneck it?

 

I was also suggesting the simple paginate as it would make the SQL query a little quicker without having to generating the page numbers on large searches.

 

You could implement a throttle limit so like most forums have on the search engine. I need to do this for player searching and a few other places in my code.

[ImportBot]

Originally Posted by ty_ger07*:

 

Are the bots ones by search engines or people hitting his site in an attempt to bottleneck it?

 

I was also suggesting the simple paginate as it would make the SQL query a little quicker without having to generating the page numbers on large searches.

 

You could implement a throttle limit so like most forums have on the search engine. I need to do this for player searching and a few other places in my code.

I don't know which bots he had searching through the stats pages. But it seemed obvious that he must have some bots from somewhere. It just isn't very likely that he had such a demand for stats from his players to cause there to always be 40 to 50 players searching his stats pages. Usually bots are from search engines; but it could also be nefarious attempts to find security flaws, misguided brute-force attacks (misguided since there is nothing to gain), or DDoS attempts.

 

Taken from edit above:

 

"Assuming that bots were the root cause since nothing else changed, I would have to assume that increasing the text length required before a jquery is fired off and adding a delay between text input and jquery refresh would be a big performance boost. As it is, it will fire off a new query as each letter is entered (to filter down the results real-time) which causes a rapid-fire succession of queries."

 

Perhaps I should just remove the nice-looking real-time jquery search completely. Or, like you said, make a limit per user of X number of searches in Y number of seconds.

 

Whatever the source of the load, it seems to me that the excessive load on the database is what caused his performance issues rather than the queries themselves.

[ImportBot]

Originally Posted by TMiland*:

 

Is it possible to hide the inactive servers on the index page?

 

In tbl_server connectionState is set to off, maybe use that as a way to hide them?

[ImportBot]

Originally Posted by Prophet731*:

 

Is it possible to hide the inactive servers on the index page?

 

In tbl_server connectionState is set to off, maybe use that as a way to hide them?

Well I change those values. The plugin, as far as I know, doesn't touch that column. Ty would need to add a interface to change that column.

[ImportBot]

Originally Posted by ColColonCleaner*:

 

Well I change those values. The plugin, as far as I know, doesn't touch that column. Ty would need to add a interface to change that column.

Or we could add a last-online column that's automatically updated on row change, and use that to manage offline servers.

[ImportBot]

Originally Posted by Prophet731*:

 

Or we could add a last-online column that's automatically updated on row change, and use that to manage offline servers.

So what if procon goes offline but the server is still running?

[ImportBot]

Originally Posted by TMiland*:

 

Well I change those values. The plugin, as far as I know, doesn't touch that column. Ty would need to add a interface to change that column.

I was just thinking, that if it was set to offline, hide it from the index here: http://nbfc.no/bf4stats/

 

I have only 2 active servers now...

[ImportBot]

Originally Posted by ty_ger07*:

 

Disregard

[ImportBot]

Originally Posted by ty_ger07*:

 

I was just thinking, that if it was set to offline, hide it from the index here: http://nbfc.no/bf4stats/

 

I have only 2 active servers now...

I can add that filter. But the user would need to be responsible for flipping that switch in their database.

[ImportBot]

Originally Posted by TMiland*:

 

I can add that filter. But the user would need to be responsible for flipping that switch in their database.

That was the idea, that's great.

[ImportBot]

Originally Posted by ty_ger07*:

 

Update released!

 

Changes:

- Block common bots

- Faster combined player counts on a few pages

- Added filter to not display servers if there `ConnectionState` is NOT NULL in the `tbl_server` table

[ImportBot]

Originally Posted by ty_ger07*:

 

That was the idea, that's great.

Hmm.

 

My update above was too hurried. On the surface, it does exactly what you want. The issue is that most of the data from the other servers will still be counted in the combined server stats pages.

 

I will need to add conditions like I did on the chat and maps page such as "WHERE `ServerID` in ({$ids})".

[ImportBot]

Originally Posted by TMiland*:

 

Hmm.

 

My update above was too hurried. On the surface, it does exactly what you want. The issue is that most of the data from the other servers will still be counted in the combined server stats pages.

 

I will need to add conditions like I did on the chat and maps page such as "WHERE `ServerID` in ({$ids})".

Did a git pull now, and i get "No 'BF4' servers were found in this database! Please notify this website's administrator."

 

Edit:

You need to do: Code:

AND `ConnectionState` = 'on'

Works: nbfc.no/bf4stats/

[ImportBot]

Originally Posted by Jamesonp*:

 

Jamesonp suggested the same.

 

The problem though is that the pagination code wasn't his issue. His issue was with selecting 20 rows of data from any location. How can I fix that issue other than try to help him ban the 40 bots from his site which were overloading his database? Did you ever take a look at how many users were viewing his stats pages at any one time? It was insane. I am assuming that the text box and easier navigation were a bots dream and they were going ballistic with it.

 

I have not changed the pagination code itself ever; the only thing which changed was its appearance. So for Jamesonp to make such a claim... well whatever.

 

I totally agree that I could optimize it by making the pagination more simple. But the loss in usability seems like a steep cost in favor of a page which loads in 4 seconds instead of 5 seconds (in Jamesonp's case). Both are slow load times and the pagination and query are obviously not the root causes.

 

Assuming that bots were the root cause since nothing else changed, I would have to assume that increasing the text length required before a jquery is fired off and adding a delay between text input and jquery refresh would be a big performance boost. As it is, it will fire off a new query as each letter is entered (to filter down the results real-time) which causes a rapid-fire succession of queries.

Yea they weren't bots, the user I use for the stats pages is strictly set to select only. Those page view stats are completely wrong and from when I first setup the stats logging page.

 

Literally when I watched top on my VDS I could tell when someone was browsing the stats page because the MySQL process would pop to the top with 100% CPU usage and then fall back down when the page was done being generated.

[ImportBot]

Originally Posted by ty_ger07*:

 

Did a git pull now, and i get "No 'BF4' servers were found in this database! Please notify this website's administrator."

What are the ConnectStates for your server is tbl_server? My database shows the defult connection state is NULL, so I filtered it to only find servers with that default state. Is your default state different or have you changed the connection state values for your servers?

[ImportBot]

Originally Posted by TMiland*:

 

What are the ConnectStates for your server is tbl_server? My database shows the defult connection state is NULL, so I filtered it to only find servers with that default state. Is your default state different or have you changed the connection state values for your servers?

They are changed by the adkats webadmin panel, either off or on. Looks like this: