Improve SQL queries for UUIDs / "Display all" about banmanager-webui HOT 20 CLOSED

@FrozenBeard Since version 5 of the Java plugin, the database structure has changed a lot and we rewrote a lot of the functions to make it work again. One downside was a pretty hacky "display all" function which is very SQL heavy. So if you have a pretty big database and/or not that quite fast I/O, it could take a while to process the query and return the data. A solution for your error. It actually already tell you what to do in the error message: "Fatal error: Maximum execution time of 30 seconds exceeded". Find your php.ini and increase max_execution_time. However thats only a temporary solution to fix the symptom and not the root cause. I try to find a more performant way to display all players.

from banmanager-webui.

Yeah, I'm aware I could do that, but this really isn't a fix! Build in some
proper pagination instead of pulling everything, that's the most basic way
to handle large amounts of data, you could easily use Ajax to also do this
page by page, there are even library's and classes available to handle
large SQL queries.
On 29 Mar 2015 1:54 pm, "Jonas Friedmann" [email protected] wrote:

@FrozenBeard https://github.com/FrozenBeard Since version 5 of the Java
plugin, the database structure has changed a lot and we rewrote a lot of
the functions to make it work again. One downside was a pretty hacky
"display all" function which is very SQL heavy. So if you have a pretty big
database and/or not that quite fast I/O, it could take a while to process
the query and return the data. A solution for your error. It actually
already tell you what to do in the error message: "Fatal error: Maximum
execution time of 30 seconds exceeded". Find your php.ini and increase
max_execution_time. However thats only a temporary solution to fix the
symptom and not the root cause. I try to find a more performant way to fix
this.

—
Reply to this email directly or view it on GitHub
#46 (comment)
.

from banmanager-webui.

Feel free to PR if you think thats so easy for you. ;)

I am not a developer, actually sys admin in full time. And then again, the new database structue (binary/hex data types for UUIDs) doesn't allow searching for specific names (LIKE %$query% in SQL):

https://github.com/BanManagement/BanManager-WebUI/blob/master/index.php#L409-L422

from banmanager-webui.

I don't know the backend at all, It would take me a long while to read through everything to see how it was built, So this is a request to the developer of the web UI, Who does build this so we can ask them?

bm_players Lists the players name though? This could be used to search.

from banmanager-webui.

@FrozenBeard The code (backend) is actually quite small. :)

I am the new maintainer since I rewrote the web UI for version 5 for my personal server and made it available on GitHub. Then @confuser asked me to take over the official repository so he can focus on the Java part of this. So basically, I do build this.

I use the bm_players already: https://github.com/BanManagement/BanManager-WebUI/blob/master/index.php#L393-L407.

from banmanager-webui.

We don't use this web interface on Frostcast any more, which is one of the reasons why development was taken over by others, most notably @frdmn who has spent quite some time and effort in maintaining this project. Overall, it is pretty much community driven.

That being said, a timeout like you've reported certainly should not be happening, especially in production. I'll investigate the cause and modify the query accordingly.

Ideally it should be using a join with the player table for LIKE $search% queries to make use of the player name index.

from banmanager-webui.

Whoever can improve the performance or the project in general is very welcome to do so. :)

Thanks @confuser for taking a look into this. I will concentrate on the admin function branch for now.

from banmanager-webui.

Superb, What fast replies! @confuser are there a few web UI's then? Or is the Frostcast version just for Frostcast?
This would be superb if you could improve the SQL query, my PHP is quite sloppy!
Its great to know this is still being developed, Im currently building a statistics web UI http://stats.piratemc.com that I was planning on adding Banmanager support into player profiles, But I haven't got around to sorting out some of my bad timings yet (Slow API calls!).

from banmanager-webui.

Any chance of this being fixed? The version 4 keeps corrupting my bm_ip_records table and spamming the console with errors, but I cant update to 5+ until the web UI is in working order, thank you.

from banmanager-webui.

Yes but not for a couple of weeks, dissertation and exams are taking priority at the moment.

from banmanager-webui.

Alright, thanks, I didn't want to open a ticket about the table corruption since there is a newer build available, maybe I will disable the web UI until its working.

from banmanager-webui.

See #71.

from banmanager-webui.

I manually implemented this fix and it did not solve the +30 seconds timeout.

from banmanager-webui.

I haven't had any time to look into this yet. I'll make it a priority this weekend.

from banmanager-webui.

I have reason to believe this has something to do with names being in the table and not UUIDs or something along those lines. I'm not entirely sure how to help figure that out though, when I try UNHEX(player_id) it returns NULL.

@FrozenBeard Add set_time_limit(300); somewhere in index.php, I have mine right above session_name("BanManagement");. Only as a temporary solution!

from banmanager-webui.

The solution to this is most likely creating a new function to handle View All, i.e. % or a new query parameter in the url, and perform a SELECT query on the bans table, with a RIGHT JOIN on the player table. This avoids any where checks and should solve the issue.

In fact the entire search could be refactored using a join

psuedo

SELECT * FROM bm_player_bans b RIGHT JOIN bm_players p ON b.player_id = p.player_id WHERE p.name LIKE %$name%

from banmanager-webui.

You should all see a massive performance boost, apologies it took so long

from banmanager-webui.

Mother of god, it works smooth as butter! Takes milliseconds to load!

from banmanager-webui.

@confuser query at lines 19, 60, and 100 of actions/home.php have a hardcoded bm_players table name.

from banmanager-webui.

Sorry about that @SupaHam it has been awhile since I've touched this code base as @frdmn is doing a great job of managing it. I've just pushed a commit which fixes the hard coded table names.

from banmanager-webui.