nl2_query_results table gets huge

[Derkades]

hundreds of megabytes (grows infinitely?)

IMPORTANT also causes other issues: #2179 (comment)

[tadhgboyle]

I feel like this could be handled similar to update check (cache last update check time, check if last time was more than x minutes ago, then check again).

We need to decide how many rows to keep max, and then delete oldest rows every x number of minutes

[partydragen]

well cannot be rows as its depends on how many servers you have and interval, should be a own option that delete older than interval, 0 to disable for them who wish to keep the data

[partydragen]

its not really a bug either as all data was to be keeped, also used to be a graph where you could go back in time

[Derkades]

EDIT: THESE ARE NOT INSTRUCTIONS. Do not do this if you are a NamelessMC user

• Remove "extra" column. If data from this becomes useful in the future, add a specific column for only that value.
• Remove "groups" column, create nl2_server_groups where a list of groups is stored by server id. Group history is not necessary
• Delete everything with timestamp < (now - 7d). This is a slow O(n) operation because the timestamp column is not indexed, don't run very often (how? does namelessmc have a "jobs" system?)

This will reduce a row from ~1KB to ~20 bytes. 1MB per server max instead of +60MB per server, per week, infinitely (for 5 servers 1GB/mo!)

[partydragen]

well was never designed for every 10 sec, but every 10 mins++

Also the player stats graphs, now for every 10 sec it will need to load ALLOOOT of data

[Derkades]

The website is free to only store a new row if the timestamp of the old row < (now - 1h) for example. Fast updates are convenient for group sync but storing every ping is not necessary.

[Derkades]

Removing the extra column is also important because it would lift the limit on the request sent by the plugin. Currently rank sync does not work for the majority of users because the column is too small. (it already breaks with more than a single player online most of the time)

[samerton]

Extra column removed in 6dcecef

Further improvements will be added in a future release

[tadhgboyle]

Do we want/need to keep the player_count column - it's not used by anything internally anymore?

If not, we may as well move the MC group storage to a different table as Derkades mentioned, and dump the query_results table:

server_id (FK)	groups
1	{"admin", "mod", "member"}
2	{"admin", "mod", "member", "helper"}

[partydragen]

Player graph etc will still be added back at somepoint

[tadhgboyle]

Okay, we can maintain this table layout, but instead we can compare the values of the groups and player_count rows and only insert new data if they are different. How does this sound?

[Derkades]

When players graphs are added back eventually ™️ you could always create a new table to keep track of player count. I like the idea of removing nl2_query_results and creating a new table with just groups by server id instead.

I'm not sure if the difference check is necessary, I'd use a query like the following:

With server_id marked as UNIQUE:

INSERT INTO nl2_server_groups (server_id, groups) VALUES (?, ?) ON DUPLICATE KEY UPDATE groups = ?

[tadhgboyle]

Ah yeah you’re right on that, update would be fine

…

On Thu, Jun 9, 2022 at 15:22 Robin ***@***.***> wrote: When players graphs are added back eventually ™️ you could always create a new table to keep track of player count. I like the idea of removing nl2_query_results and creating a new table with just groups by server id instead. I'm not sure if the difference check is necessary, just always sending an UPDATE query should be fine. Wouldn't you need an additional query to check the current value, or am I missing something? — Reply to this email directly, view it on GitHub <#2179 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AGG43DDBJEJ44QBVNSYFIXLVOJOBJANCNFSM4YHBROBQ> . You are receiving this because you commented.Message ID: ***@***.***>

[partydragen]

The table should stay keeping data is always good atleast the playercount and the player graph have been added and removed multiple times xD anyways can be used for alot more than just graph

Just make that new table instead and keep this

[tadhgboyle]

INSERT INTO nl2_server_groups (server_id, groups) VALUES (?, ?) ON DUPLICATE KEY UPDATE groups = ?

Realizing this now, how will this work for displaying group sync options?
Should it only get groups for the default server, or should it collect all of the groups and display all of them as options in the dropdown?

[tadhgboyle]

Continuing the push back of this issue 😛