B4J Question Best way to get closest devices to a known GeoPoint?

[vfafou]

Hello!
I have a web socket push framework with automatic tablet coordinates update. I'm using MariaDB 10.1.
I' ve noticed that if randomly ~200+ concurrent devices try to update their coords, I have an extended table wait lock which by condition would have a very long duration. The result is a very slow performance of the web socket server.
After this experience, I'm looking for another way to store coordinates and retrieve the closest to a point devices.
Is the map or list object proper to do that?
Is a new class with Longitude and Latitude properties (and any other property as filter) a better object for my case?
Is there any fast and reliable algorithm, to get the closest to a point devices?
Thank you in advance!

 

[Erel]

Have you enabled concurrent inserts? https://mariadb.com/kb/en/mariadb/concurrent-inserts/

 

[vfafou]

Hello, Erel!
Yes, I have enabled concurrent inserts!

 

[Erel]

Is it still slow?

 

[vfafou]

I've found that I had the setMaxPoolSize parameter and the max_connections of the database set to 600. When I set the values to 800, I noticed that the number of connections to the database could be ~750. Now, I have the value increased to 1000 and I don't see any problem.
My only issue is: how can 450 devices open 750 connections to the database? I ask because every connection I open from the web socket server, I close it when transaction is done!

 

[Erel]

Doesn't look correct to me. The pool size should be much smaller.

Why have you increased the maximum size?

Make sure that you are not leaking any connection as it will easily "kill" the database.

 

[vfafou]

Related to this post:
https://www.b4x.com/android/forum/t...ections-with-mysql-mariadb.61746/#post-389718
I think that the problem caused from this behaviour of the connection pooling.
If I let it handle the connections, the result is opening a new connection with every SQL = pool.GetConnection
If this is not the normal behavior, then I've done something wrong.
I have a global SQL object for every web socket connection thread. In every procedure I need to open the database, I do:

SQL = pool.GetConnection
'Queries, Updates, etc...
SQL.Close

If there is a procedure called inside another procedure and uses SQL object, then I do:

SQL = pool.GetConnection
SQL.ExecQuery(...)
updOtherTable(SQL,...)
SQL.Close

Where updOtherTable(SQL,...) is:

Sub updOtherTable(TSQL As SQL, TabName As String)
   TSQL.ExecNonQuery("Update " & TabName & " Set C1 = 0")
   'I don't give a TSQL.Close because I think that it will close the global SQL object! Is that true?
End Sub

Thank you in advance!

 

[Erel]

'I don't give a TSQL.Close because I think that it will close the global SQL object! Is that true?

Yes.

How do you see that a new connection is created each time?

What is the actual problem? Is the database response slow?

 

[vfafou]

Hello Erel!
Thank you for your reply.
All devices connected to the websocket server, do a ping every 10 seconds. With every ping, I do an update of the coordinates of the devices.
I have ~450 concurrent devices connected. I suppose that every device must make one connection to the database.
Watching the DB open processes from a front-end tool like SQLyog or HeidiSQL, some times the processes are more than the devices.
I've seen ~750 processes for ~450 devices. This is the reason I've increased the max_connections of the DB.
Before increasing, the websocket server hanged the pool and closed all connections. The server worked correctly again, only when I stopped and started the application. This is the actual problem.
Now, I have changed temporarily my code to do:

If not(SQL.IsInitialized) Then SQL = pool.GetConnection

and I haven't noticed any problem! It does not create more connections than the number of the devices.
The wait_timeout variable of the DB has the value 45 (seconds).

 

[vfafou]

Any suggestion, please???

 

[Erel]

If it works for you then all is good.

 

[vfafou]

Hello Erel!
Thank you for your reply. It's the 6th contiguous day the server works without any stop/start. So I think that there it's a MariaDB pool managing issue, or something is wrong with my settings!