Account System Problem on High Player Numbers

[Query]

Hi, I have a Roleplay server over 200 players per day.
But we have a problem with the "Account" issue.
6.000 accounts, 14,000 characters available in SQL.
When we reach the high player numbers, there is a function in "setElementData" that treats the data that SQL has drawn when entering each character.
This function goes to CPUs that reach 100% - 200% and sometimes 300% when we check it in "ipb" script.
How can we fix this? Is there any way that I and my team can help with this?

[Pirulax]

dont use element data maybe?
Because the way it works is that every element data is synced to everyone.
Even the ones set at the client I think.
So, that's the problem.

Edited July 30, 2018 by Pirulax

[Query]

Sample:
 

 local characterData = false
       
        if theAdmin then
                accountID = remoteAccountID
                characterData = mysql:query_fetch_assoc("SELECT * FROM `characters` LEFT JOIN `jobs` ON `characters`.`id` = `jobs`.`jobCharID` AND `characters`.`job` = `jobs`.`jobID` WHERE `id`='" .. tostring(characterID) .. "' AND `account`='" .. tostring(accountID) .. "'")
        else
                characterData = mysql:query_fetch_assoc("SELECT * FROM `characters` LEFT JOIN `jobs` ON `characters`.`id` = `jobs`.`jobCharID` AND `characters`.`job` = `jobs`.`jobID` WHERE `id`='" .. tostring(characterID) .. "' AND `account`='" .. tostring(accountID) .. "' AND `cked`=0")
        end

setElementDataEx(client, "maxvehicles", tonumber(characterData["maxvehicles"]))

 

the last "setElementData" slaves as each player leaves the game.
There's no problem here, it's not lagging.

The problem occurs when retrieving repetitive data from SQL while doing "setElementData".

When the player exits the server, we have no problems with SQL operations. No LAG.
When logging into the server, when logging into the character, when pouring the data in SQL into "setElementData", the CPU usage increases considerably. We are experiencing a delay in high player numbers.

We exported the entire SQL structure to the table, and we took the "setElementData" by pulling the table with the table. The problem is the same again.
Even when the CPU usage was made with the tabulation structure, it increased even more.

What kind of solution can you offer us?
How did the big servers solve this problem?

[itHyperoX]

try to use dbQuery, check if is that better, if not, then try to use less elementData

[Query]

We tried dbQuery. The problem did not change. We can not reduce the number of elements at this time, the server is an active server. So I do not think we can drop it. We need a different solution.

and

plus a total of 90 elements.
When you enter the character 90 elements are working.
It's impossible to reduce it.

Edited July 30, 2018 by Query

[SycroX]

do you try to replace the elementData with tables ? 

 

[Query]

When the script runs, we have turned all SQL into a table.
Later in the Quit process we updated the table.

However, in the same function, each player has detected the username of the table on input and updated it with "setElementData".

But according to SQL the table used more CPU.

This was not a solution. We did the tests in this way.

[Pirulax]

That SQL query you just posted is totally inject safe, you should know that.
Nah, back to the thing: buy a better VPS, or actually, a server.
The problem is that(mostly) setElementData causes a big bottleneck, since it's synced to everyone who joins to the server.
Try optimizing ur vehicle-system, since a lot of elements can cause big CPU usage too.
Try reducing server-side elements, like create maps client side instead of server-side.

 

[Query]

Yes, you're right, I need to get a better server. (lul)

I'm talking about a server that can see 300 players in total. You're telling me to buy a new server, it's funny.

[IIYAMA]

Are your queries limiting the search result? (Just check it if you haven't done it yet, because not doing it can improve the performance with 0 t/m 1000% depending on the situation)

1. Limit search results

 

Your situation: queries are taking too long to complete and your lua memory is waiting for the results.(because you didn't use callbacks)

 

2. Use callbacks instead of pause the lua memory.

2.1 See also coroutines: https://www.lua.org/pil/9.1.html

 

 

3. Last resort

You have to queue your queries and let it better work with your internal lua memory.

[Query]

Thank you for your valuable comment. We will test with our developer team friends and re-comment.
 

[Pirulax]

I'm sure that you have a professional team.(sarcasm)
They didn't used callbacks.
Neither LIMIT 1 in sql queries.
Neither '?' but instead they simply concated string which leads to SQL injection....

 

On 7/31/2018 at 00:52, Query said:

Yes, you're right, I need to get a better server. (lul)

I'm talking about a server that can see 300 players in total. You're telling me to buy a new server, it's funny.

Dont insult me, alright?

[Query]

You do not need to talk in a sarcastic way.
'IIYAMA's recommendation was our job. We solved the problem by 75%.
Good people in the field of developers in my team. At least I have the best first four in Turkey.

 

In the meantime I did not insult you, I do not want to be misunderstood.
Rather; I did not insult anyone.

Edited August 3, 2018 by Query

[Query]

We are also open to different suggestions.
 

[Pirulax]

@Query
If they are the top 4 in turkey, then turkey is a big shame.
Nah, back on the topic:
Maybe try using 

batch=1

In dbConnect's option list.