B4J Question Congestion problems with old MySQL 4.0 and connection pooling.

[vfafou]

Hello!
The following refers to changed Custom Web Socket Framework.
Is it possible to place in a queue the Events called from clients?
I have a database congestion problem when many concurrent clients are trying to login, because I do some queries in order to return to the clients their current status.
The number of concurrent clients is between 50 and 120.
The MySQL version is - unfortunately - 4.0 and it hasn't very good connection pooling support. I'm planning to upgrade it to MariaDB 10.0 or 10.1, within 1 month but I need to temporarily solve the problem because the system is on production.
Thank you in advance!

 

[Erel]

The database server should handle 120 connections. The connection pool manages a queue. You can change its configuration. For example:
https://www.b4x.com/android/forum/t...ith-connectionpool-library.42647/#post-261085

I think that the first step is to try to optimize the queries. Maybe you can add an index and make the queries much faster.

 

[vfafou]

Hi Erel!
Thank you for your response!
I have already done the configuration of the connection pool.
A few hours after my previous post, I finally found where my problem was:
I had created the two tables of the messages with XtraDB (InnoDB) storage engine.
When the congestion occured, I simply changed the storage engine from XtraDB to Aria (MyISAM) and the congestion disapeared immediately!
The database is MariaDB 10.0.14.
The strange case is that I have optimized the database for InnoDB engine.
It seems that the data flow is so fast that the transactional mode cannot handle it!

P.S. I've just noticed that I have confused all the readers of this post. I forgot to mention that the old 4.0 MySQL version is the one that holds all my central application's data, and all requests from the websocket server are ended up there!
The new MariaDB instance is the database of the websocket server.
I was supposing that the problem comes from the old instance but finally was the new instance that was having a very big delay to deliver the answers from the old system!