An idea to increase server performance

[Nik]

Well, lately i've been having some issues, server freezing for 4-5 seconds every 5 minutes, when many players are online. I tried to investigate it... and from the results i saw, i thought that its caused by the character updates, no wonder, it opens a new connection and executes a query ~900 times, lol, no wonder why.
Making a new connection, sending a query so many times at once, this is major performance loss. So my idea is to generate 1 major string, containing updates of every character online. This will MAJORLY increase the performance, because the server will open 1 connection and execute 1 query, instead of oppening multiple connections and executing multiple queries.
And this idea will be suitable for every simlar thing, where you have to execute multiple queries.

What do you think about this guys?

[JIV]

default is 15min and its not executed for all chars at same time.

[Vapulabe]

What takes the most time is not opening the connection (connection pooling and such) nor sending the SQL request...

What takes the most time is the execution of the SQL request on the SQL server...

Creating a bigger request will only concentrate the time needed... what could help is the opposite...

Try to optimize your SQL server... Or, if you feel good enough, you may also try to switch to PostgreSQL with full request optimisation (most of the requests are similar so the "genetic engine" will be able to get quite accurate).

[MELERIX]

Well, lately i've been having some issues, server freezing for 4-5 seconds every 5 minutes, when many players are online. I tried to investigate it... and from the results i saw, i thought that its caused by the character updates, no wonder, it opens a new connection and executes a query ~900 times, lol, no wonder why.
Making a new connection, sending a query so many times at once, this is major performance loss. So my idea is to generate 1 major string, containing updates of every character online. This will MAJORLY increase the performance, because the server will open 1 connection and execute 1 query, instead of oppening multiple connections and executing multiple queries.
And this idea will be suitable for every simlar thing, where you have to execute multiple queries.

What do you think about this guys?

viewtopic.php?f=80&t=15414

[Nik]

Ehh, its my fault that i was too hasty for posting this thing, without looking the sourcecode a bit to see that it doesnt update all chars at once... the reason i thought that it could be the char store thingie is because the problem occurs every 5 min, and the update interval is 5 min aswell.

Creating a bigger request will only concentrate the time needed... what could help is the opposite...

Create an SQL file that adds 500 NPCs for example. First time, try to make every NPC to have its own query, the second time, make all the NPCs to be in 1 query, and look what will be executed faster

Well, the SQL server is optimized, but maybe not good enough

[janiii]

Ehh, its my fault that i was too hasty for posting this thing, without looking the sourcecode a bit to see that it doesnt update all chars at once... the reason i thought that it could be the char store thingie is because the problem occurs every 5 min, and the update interval is 5 min aswell.

Creating a bigger request will only concentrate the time needed... what could help is the opposite...

Create an SQL file that adds 500 NPCs for example. First time, try to make every NPC to have its own query, the second time, make all the NPCs to be in 1 query, and look what will be executed faster

Well, the SQL server is optimized, but maybe not good enough

check java PreparedStatement . is for batch sql statements.

[Vapulabe]

Creating a bigger request will only concentrate the time needed... what could help is the opposite...

Create an SQL file that adds 500 NPCs for example. First time, try to make every NPC to have its own query, the second time, make all the NPCs to be in 1 query, and look what will be executed faster

Now, try to do some update and the situation change... Because updating a table means file write, index update, ...

You are talking about the time when the server updates... this is not a SELECT...

Also, doing select on player data is only done one account at a time, when the player connects on that player... It's a rather small and quick request...

Big DB hogs are
- Spawn load from SQL (once at startup ?)
- Mass UPDATE on the DB (well, SELECT/UPDATE/INSERT way of doing it is even worse)

[Szponiasty]

Not to mention that default databases are not optimized too well. And you can easyly make for example few indexes and db power utilisation drops down greately.