Android Question RDC2_Handler error

[marcick]

I often register this error in sub Handle of RDCHandler

2017-06-09 10:47:44 - RDCHandler_Handle error, (TimeoutException) com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@15975490 -- timeout at awaitAvailable()

This is the sub

Sub Handle(req As ServletRequest, resp As ServletResponse)
    Start = DateTime.Now
    Dim q As String     'ignore
    Dim in As InputStream = req.InputStream
    Dim method As String = req.GetParameter("method")
    Dim con As SQL
    Try
        con = Main.rdcConnector1.GetConnection
        If method = "query2" Then
            q = ExecuteQuery2(con, in, resp)
#if VERSION1
        Else if method = "query" Then   
            in = cs.WrapInputStream(in, "gzip")
            q = ExecuteQuery(con, in, resp)
            'Log($"Command: ${q}, tool: ${DateTime.Now - Start}ms, client=${req.RemoteAddress}"$)
        Else if method = "batch" Then
            in = cs.WrapInputStream(in, "gzip")
            q = ExecuteBatch(con, in, resp)
            'Log($"Command: ${q}, tool: ${DateTime.Now - Start}ms, client=${req.RemoteAddress}"$)
#end if
        Else if method = "batch2" Then
            q = ExecuteBatch2(con, in, resp)
        Else
            Log("Unknown method: " & method)
            resp.SendError(500, "unknown method")
        End If
    Catch
        Log("RDCHandler_Handle error, " & LastException)
        resp.SendError(500, LastException.Message)
        If LastException.Message.Contains("0000-00-00 00:00:00") Then PurgeBadRecord   
        Dim TextWriter1 As TextWriter
        TextWriter1.Initialize(File.OpenOutput(File.DirApp, "Restart.log",True))
        TextWriter1.Write(DateTime.date(DateTime.Now) & " - RDCHandler_Handle error, " & LastException)
        TextWriter1.Close
        Main.robot.relaunchSelfbatch("jrdc.bat")
    End Try
    If con <> Null And con.IsInitialized Then con.Close   
End Sub

Any idea ?

 

[Erel]

It means that there was no SQL connection available. You can configure the connection pool and increase the limits.

 

[marcick]

Which is the default max pool size, so I can try to double it ?

 

[Erel]

The default maximum pool size is 15. How many clients are connected at the same time? 15 should be enough in most cases.

 

[marcick]

Difficult to evaluate for me.
Let's say 100 client, each one make a request every 5 seconds.

 

[Erel]

Unless the queries are very slow (few seconds) then it shouldn't be a problem.

Do the RDC server and DB server run on the same computer?

 

[marcick]

Yes, they're on the same computer.
Does this screenshot give some idea of the traffic ?

https://1drv.ms/i/s!Ak1xBLnncsa5jc9GGOgRJkN5bNUYlA

Sometimes the queries are much slower, some seconds.

 

[Erel]

I think that the first step should be to find the slow queries and see how you can optimize them. Maybe you can add an index and make the queries 100 times faster.

 

[marcick]

This query for example (tested with PhpMyAdmin on the server where is located the MySql db) requires 12 seconds to give the result (1000 rows)

SELECT `Index`, `ID`, `MS`, DATE_FORMAT(`StoTime`, '%Y-%m-%d %T') AS `StoTime`, DATE_FORMAT(`GpsTime`, '%Y-%m-%d %T') AS `GpsTime`, `Body`, `Pwd`, `Sender`, `Device`, `Crc` FROM `records` ORDER BY `StoTime` LIMIT 1000

I don't have normally 1000 rows as result, but may happen the client remain offline for a long time and then yes.

 

[marcick]

I see now that the problem is in "ORDER BY StoTime" . It has a massive slowing effect on the query.
Removing that clause I obtain 0,008 sec instead of 10 .....
But I need that sorting, How to optimize it ?
I understand, this question is not B4A related but ....

 

[DonManfred]

But I need that sorting, How to optimize it ?

Add index(es) which matches the right values.... n this case it should be ok to set a index to StoTime

 

[marcick]

Yes, I have already tried it but no improvements.

CREATE INDEX StoTime_ind ON records (`StoTime`)

I'm reading that In some cases, MySQL cannot use indexes to resolve the ORDER BY

 

[DonManfred]

try "explain "your query"" in phpmyadmin... It should list of used indexes

Edit: See https://dev.mysql.com/doc/refman/5.7/en/using-explain.html

 

[marcick]

confirmed: the full query above does not use the existing index StoTime, but filesort instead.

 

[DonManfred]

SELECT `Index`, `ID`, `MS`, DATE_FORMAT(`StoTime`, '%Y-%m-%d %T') AS `StoTime`, DATE_FORMAT(`GpsTime`, '%Y-%m-%d %T') AS `GpsTime`, `Body`, `Pwd`, `Sender`, `Device`, `Crc` FROM `records` ORDER BY `StoTime` LIMIT 1000

You are casting StoTime to a STRING with DATE_FORMAT.

Give the resultning value another name. Use the original field to sort (ORDER).

something like

SELECT `Index`, `ID`, `MS`, DATE_FORMAT(`StoTime`, '%Y-%m-%d %T') AS `StrStoTime`, DATE_FORMAT(`GpsTime`, '%Y-%m-%d %T') AS `StrGpsTime`, `Body`, `Pwd`, `Sender`, `Device`, `Crc` FROM `records` ORDER BY `StoTime` LIMIT 1000

 

[marcick]

Wow, yes, it's 1000 times faster now.
The column names of the result are not what is expected by the client now, but I can manage a new RDC command used by a new version of the client app.
Thank you very much !

 

[marcick]

I don't know what's happened but looks like the index no longer works.

I have tried also with a very basic query

https://1drv.ms/i/s!Ak1xBLnncsa5jc9LsO-3Rjx7HdJ7tA

 

[DonManfred]

What do you expect? Using * will get ALL Results. So filesort is used always. Add a where and the index should work.

PD: Please dont upload filess on drive. Upload the picture directly here. Just drop the picture on the editor to upload....

 

[marcick]

I'm probably out of service ...
Need some rest

 

[marcick]

Ok, everything works.
My server was close to the collapse, several seconds to solve the queries and 100% of CPU usage.
With 3 indexes in the db all works perfect in a few mS for each query.
Once again: thank you so much for the incredible, precise and effective support of Erel, DonManfred and many other experts that I always find here.
Yeah.