default idle db connections

Post here your questions about SFS2X. Here we discuss all server-side matters. For client API questions see the dedicated forums.

Moderators: Lapo, Bax

Mrm83
Posts: 132
Joined: 17 Dec 2017, 04:02

default idle db connections

Postby Mrm83 » 30 Sep 2022, 17:45

Is there a way to increase the default idle database connections on server start?

The zone is configured to have 20 idle connections but it always boots up with 2.
User avatar
Lapo
Site Admin
Posts: 22558
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: default idle db connections

Postby Lapo » 01 Oct 2022, 09:48

Hi,
the answer is no.
I am not sure what would be the advantage of creating many unused connections when they're not needed. It seems to defeat the purpose of the Connection pooling itself, where connections are created only when needed and prioritizing reuse.

Cheers
Lapo
--
gotoAndPlay()
...addicted to flash games
Mrm83
Posts: 132
Joined: 17 Dec 2017, 04:02

Re: default idle db connections

Postby Mrm83 » 01 Oct 2022, 11:28

I am stress testing the server right now and new connections ARE NOT being created. It is causing connection delays up to 10 seconds and that is ridiculous. I am spamming the server every 100 ms with up to 100 CCU doing DB requests and each DB request connects to a remote db with 300ms ping. For 10-15 minutes, there were only 2 active connections. The response time should be 500ms tops, but I am getting responses that range between 2 - 10 seconds.

Connections are created eventually, after like 10-15 minutes into the test, but that is too late.

I don''t understand why connections takes so long to create when the server is being hammered. Not good at all.
User avatar
Lapo
Site Admin
Posts: 22558
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: default idle db connections

Postby Lapo » 01 Oct 2022, 14:52

For starters it would to know the conditions under which the test is performed.
If you are using a remote DB with 300ms of lag from SFS2X that is already a significant problem, as every client request will incur in that much latency plus everything else.

As regards the number of connections being only 2 while the server is being spammed, that doesn't sound right.
Where exactly are you getting the number of connections from?
And what is the configuration of the DBManager on the SFS2X side?

The response time should be 500ms tops, but I am getting responses that range between 2 - 10 seconds.

That sounds more like a problem with the database itself than anything else.
By the way what database is this exactly? Where is it hosted?

Thanks
Lapo

--

gotoAndPlay()

...addicted to flash games
Mrm83
Posts: 132
Joined: 17 Dec 2017, 04:02

Re: default idle db connections

Postby Mrm83 » 02 Oct 2022, 12:19

For starters it would to know the conditions under which the test is performed.
If you are using a remote DB with 300ms of lag from SFS2X that is already a significant problem, as every client request will incur in that much latency plus everything else.

That is not the problem. It is expected.

As regards the number of connections being only 2 while the server is being spammed, that doesn't sound right.
Where exactly are you getting the number of connections from?
And what is the configuration of the DBManager on the SFS2X side?

This is from runtime zone settings. 80 Active, 20 Idle, GROW, 3000 block.

The response time should be 500ms tops, but I am getting responses that range between 2 - 10 seconds.
That sounds more like a problem with the database itself than anything else.
By the way what database is this exactly? Where is it hosted?

No, it is a problem because there are only "2 ACTIVE" connections. Once the active connections bumps up (highest I saw was 10), everything is normal where the expected response time is actual. ECS DB, where server and db are hosted in different regions.

##Update
So I am able to get my idle connections up by not closing the db connections until all are created. I am now noticing the SAME problem.
FREE connections are not being used and it keeps on waiting for the same active connection causing huge response times!
I have 20 idle db connections before test start, once the test runs, I see
Active : 1-3
Idle: 16+
Again, it takes a few minutes before I see active connections go to 15-20.
What is going on? Why is this happening?
User avatar
Lapo
Site Admin
Posts: 22558
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: default idle db connections

Postby Lapo » 02 Oct 2022, 13:52

This is from runtime zone settings. 80 Active, 20 Idle, GROW, 3000 block.

Thanks. I was also asking where are you getting the number of connections from?
Is the SFS2X AdminTool or the DB itself?

Also what I am not sure what an ECS database is... I suppose it's running in AWS elastic container? But what actual database is it? MySQL? MSSQL?

I would recommend taking a look at the SFS2X AdminTool's dashboard while you're running the test, and in particular in the "Message Queue Status" tab. Make sure the Extension queue is not overly busy. Ideally it should only have a few, infrequent spikes.

Thanks

p.s. = You can also bypass the DBManager entirely if that bothers you and connect to the DB via the standard JDBC API (which the DBManager uses anyways).
Lapo

--

gotoAndPlay()

...addicted to flash games
Mrm83
Posts: 132
Joined: 17 Dec 2017, 04:02

Re: default idle db connections

Postby Mrm83 » 02 Oct 2022, 14:00

Thanks. I was also asking where are you getting the number of connections from?
Is the SFS2X AdminTool or the DB itself?

Admin tool
Also what I am not sure what an ECS database is... I suppose it's running in AWS elastic container? But what actual database is it? MySQL? MSSQL?

Amazon MySQL

#update
ok, i found the problem. this is not entirely the db connection fault.
This is because ext thread is 4, and each thread is waiting for sql to complete, meaning it is the ext thread blocking.
once more ext threads were created (20), all 20 db connections were used and i am no longer see the crazy 10 second response time.

now i did read a post before where it is recommended to leave default pool settings..
I am wanting to increase ext core thread from 4 to 20, is this bad?
what risks, issues am i facing for increasing it 20?
User avatar
Lapo
Site Admin
Posts: 22558
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: default idle db connections

Postby Lapo » 02 Oct 2022, 14:37

No, it's not bad to change the thread settings, especially in a situation like this, where you have a laggy database.
I would actually set the the core thread count to 40-50.
The Extension thread pool can auto-resize itself if it detects a lack of available threads but it's not real time as it needs to monitor a certain time interval before it triggers a resize. So starting with a larger pool size is definitely better in this case.

Keep in mind that a laggy DB server is not ideal and will cause scalability issues at some point, due to the slow I/O. If you can optimize the location of the DB and bring it more close to the SFS2X server it will make a significant difference.

More on the auto-resizing thread pools here:
http://docs2x.smartfoxserver.com/Extens ... oadBalance

Cheers
Lapo

--

gotoAndPlay()

...addicted to flash games
Mrm83
Posts: 132
Joined: 17 Dec 2017, 04:02

Re: default idle db connections

Postby Mrm83 » 02 Oct 2022, 14:49

No, it's not bad to change the thread settings, especially in a situation like this, where you have a laggy database.
I would actually set the the core thread count to 40-50.
The Extension thread pool can auto-resize itself if it detects a lack of available threads but it's not real time as it needs to monitor a certain time interval before it triggers a resize. So starting with a larger pool size is definitely better in this case.

Keep in mind that a laggy DB server is not ideal and will cause scalability issues at some point, due to the slow I/O. If you can optimize the location of the DB and bring it more close to the SFS2X server it will make a significant difference.

More on the auto-resizing thread pools here:
http://docs2x.smartfoxserver.com/Extens ... oadBalance


Yes, I understand that high latency of db is bad, but this game setup is to support multi regional servers which uses the same db. ive thought about making a slave db locally for each region, but that isn't going to work. so right now, the code is optimized to only read/write to the db on login and logout so effectively, it is only going to be bad during the login and logouts of a session (which will be treated as "loading").

I will see if there is another location where i can move it to so it is a better sweet spot for both us/as region.

thanks!
User avatar
Lapo
Site Admin
Posts: 22558
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: default idle db connections

Postby Lapo » 03 Oct 2022, 07:43

Hi,
it's not actually bad, it's just not ideal, especially if you could bring the database closer. In your case it's not that trivial to do so, and it's also not the end of the world :)

You can mitigate the latency issue with a larger System/Extension thread pool so that you always have enough threads to keep up with the slow I/O. SFS2X is already designed to deal with those scenarios and it can help with the auto load-balancing pools.

Cheers
Lapo

--

gotoAndPlay()

...addicted to flash games

Return to “SFS2X Questions”

Who is online

Users browsing this forum: No registered users and 38 guests