B4J Question SQL Background Thread

[aaronk]

Hi,

Just wondering if I run the following code, is this running on the main thread or on a background thread ?

I am guessing the following is running on the main thread ?

Sub SetValue(location As String, value As String) As String

    Dim sql As SQL = pool.GetConnection
    Try
        'work with sql
        sql.ExecNonQuery2("INSERT INTO Status (Location, Value) VALUES (?,?) ON DUPLICATE KEY UPDATE Value=?",Array (location,value,value))
    Catch
        'handle failure
        Log(LastException.Message)
    End Try
    sql.Close
End Sub

Since I am wanting to send the request to the MySQL database on a background thread (as I don't want the rest of my B4J app to lock while it's sending the request, in case it takes longer to send the request), would I be correct in saying the following code will run the SQL query on a background thread, and is it the correct way in doing it ?

Sub SetDeviceStatus(location As String, value As String) As String

    pool.GetConnectionAsync("SQL1")
    wait for SQL1_ConnectionReady (Success As Boolean, sql As SQL)
    'work with sql
    sql.ExecNonQuery2("INSERT INTO Status (Location, Value) VALUES (?,?) ON DUPLICATE KEY UPDATE Value=?",Array (location,value,value))
    Catch
    'handle failure
    Log(LastException.Message)
    End Try

End Sub

 

[Erel]

Not correct.

You should use the async methods.
[B4X] SQL with Wait For

 

[OliverA]

Look at my answer to your other question: https://www.b4x.com/android/forum/threads/filter-running-slow.115410/post-726629

 

[aaronk]

I am doing it like the following:

Sub Process_Globals
    Dim pool As ConnectionPool
    Dim con As Int = 0
End Sub

Sub GetDeviceStatus(Account As String, device As String) As ResumableSub
   
    Dim returnValue As String
    pool.GetConnectionAsync("SQL1")

    con = con + 1

    wait for SQL1_ConnectionReady (Success As Boolean, sql As SQL)
   
    If Success Then
        Try
            'work with sql
            Dim sf As Object = sql.ExecQueryAsync("SQL", "SELECT Value FROM `StatusV2` WHERE `Location` = ?", Array As Object (Account & "_" & device))
            Wait For (sf) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
            If Success Then
                Log("ExecQueryAsync Success = " & Success)
                If rs.NextRow Then
                    returnValue = rs.GetString("Value")
                End If
            End If
        Catch
            Log(LastException.Message)
        End Try
    End If
   
    sql.Close
    rs.Close
   
    con = con - 1
    Log("con = " & con)
   
    ' will return value
    Return returnValue
   
End Sub

I am logging how many connections are open (well I think that is one way in detecting how many connections are open) based on the value of con.

When the connection is being made it adds one to the con int value, and once it closes the connection it will minus one from the con int value.

This keeps increasing and does't seem to ever decrease.

It was locking up and showing an error. The value of 'con' was 15:

java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
at anywheresoftware.b4j.object.ConnectionPool.GetConnection(ConnectionPool.java:45)
at anywheresoftware.b4j.object.ConnectionPool$1.call(ConnectionPool.java:57)
at anywheresoftware.b4j.object.ConnectionPool$1.call(ConnectionPool.java:1)
at anywheresoftware.b4a.BA$4.run(BA.java:272)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@721e0f4f -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
... 10 more
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
at anywheresoftware.b4j.object.ConnectionPool.GetConnection(ConnectionPool.java:45)
at anywheresoftware.b4j.object.ConnectionPool$1.call(ConnectionPool.java:57)
at anywheresoftware.b4j.object.ConnectionPool$1.call(ConnectionPool.java:1)
at anywheresoftware.b4a.BA$4.run(BA.java:272)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@721e0f4f -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
... 10 more

I then increased the max pool size after I initialise the the pool like:

pool.Initialize("com.mysql.jdbc.Driver","jdbc:mysql://mysql_ip_address_here/cloudserver?characterEncoding=utf8","username","password")
   
    Dim jo As JavaObject = pool
    jo.RunMethod("setMaxPoolSize", Array(5000))

Be increasing the connection pool size, it seems to fix the issue where it locked up after it got to 15, but it keeps increasing, and I assume it will crash again once it gets to 5000.

I tried in debug mode and release mode, but both has the same result.

I am now doing this correct or am I still doing it wrong ?

 

[DonManfred]

based on the value of con

but you are always incrementing. Even if not reaching the ConnectionReady?

 

[aaronk]

but you are always incrementing. Even if not reaching the ConnectionReady?

Thanks for pointing that out.

I have it as the following. Now it shows it as 0 each time, but it still locks up and shows the same error.

Sub GetDeviceStatus(Account As String, device As String) As ResumableSub
    
    Dim returnValue As String
    pool.GetConnectionAsync("SQL1")

    wait for SQL1_ConnectionReady (Success As Boolean, sql As SQL)
    con = con + 1
    If Success Then
        Try
            'work with sql
            Dim sf As Object = sql.ExecQueryAsync("SQL", "SELECT Value FROM `StatusV2` WHERE `Location` = ?", Array As Object (Account & "_" & device))
            Wait For (sf) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
            If Success Then
                Log("ExecQueryAsync Success = " & Success)
                If rs.NextRow Then
                    returnValue = rs.GetString("Value")
                End If
            End If
        Catch
            Log(LastException.Message)
        End Try
    End If
    
    sql.Close
    rs.Close
    
    con = con - 1
    Log("con = " & con)
    
    ' will return value
    Return returnValue
    
End Sub

 

[Erel]

You should close the ResultSet before you close the connection.

As @DonManfred wrote you are not counting open connections.

You have made many posts and still haven't done the correct thing, which is to find the bottleneck. Measure the various steps and find which step is too slow. Nothing will help if the rate of incoming requests is faster than the rate that your server can handle.

 

[aaronk]

You should close the ResultSet before you close the connection.

Ok, I have now changed it around so it closes the RS and then the SQL connection.

Measure the various steps and find which step is too slow. Nothing will help if the rate of incoming requests is faster than the rate that your server can handle.

See attached txt file.
Nothing seems to be running slow from what I can see.
The only thing I notice is the connection drops, since SQL1_ConnectionReady stops after approximately 15-20 seconds? Would I be correct in saying that ?

Sub GetDeviceStatus(Account As String, device As String) As ResumableSub
    Dim n As Long = DateTime.Now
 
    Dim returnValue As String
    pool.GetConnectionAsync("SQL1")
    Log("1 = " & (DateTime.Now - n))

    wait for SQL1_ConnectionReady (Success As Boolean, sql As SQL)
    Log("2 = " & (DateTime.Now - n))
    If Success Then
        Try
            'work with sql
            Dim sf As Object = sql.ExecQueryAsync("SQL", "SELECT Value FROM `StatusV2` WHERE `Location` = ?", Array As Object (Account & "_" & device))
            Wait For (sf) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
            Log("3 = " & (DateTime.Now - n))
            If Success Then
                Log("ExecQueryAsync Success = " & Success)
                If rs.NextRow Then
                    returnValue = rs.GetString("Value")
                End If
            End If
            Log("4 = " & (DateTime.Now - n))
        Catch
            Log(LastException.Message)
        End Try
    End If
 
    rs.Close
    sql.Close
 
    Log("5 = " & (DateTime.Now - n))
    Log(" ")
    ' will return value
    Return returnValue
 
End Sub

 

[Erel]

How many requests per second are you handling? Note that rs.Close and SQL.Close should only be called when Success = True.

What happens after it fails to get a connection? Do further requests also fail?

 

[aaronk]

How many requests per second are you handling?

There is currently approx 3-4 requests per second.

Note that rs.Close and SQL.Close should only be called when Success = True.

Ok, I have move that code so it is only triggered when success = True.

What happens after it fails to get a connection? Do further requests also fail?

Yes they do.

 

[Erel]

It looks like the connections are not returned properly to the connection pool. Does this error also happen if you use GetConnection instead of the async method?

 

[aaronk]

If I run it like the following, then it works fine except it makes my B4J app run slow while it processors the messages.

Everything seems to work and doesn't timeout.
Also see attached of what it logs.

Sub GetDeviceStatus(Account As String, device As String) As String
    Dim name As String

    Dim n As Long = DateTime.Now
    Dim sql As SQL = pool.GetConnection
    Log("1 = " & (DateTime.Now - n))
  
    Try
        name = sql.ExecQuerySingleResult2("SELECT Value FROM `StatusV2` WHERE `Location` = ?",Array (Account & "_" & device))
        Log("2 = " & (DateTime.Now - n))
    Catch
        'handle failure
        Log(LastException.Message)
    End Try
    sql.Close
  
    Log("3 = " & (DateTime.Now - n))
    Log(" ")
  
    Return name
End Sub

 

[Erel]

This is not what I asked you to try. Only make the first step synchronous. What happens?

 

[aaronk]

This is not what I asked you to try. Only make the first step synchronous.

Oh, I thought that is what you were meaning.

I am hoping this is what you are meaning..

Sub GetDeviceStatus(Account As String, device As String) As ResumableSub
    Dim n As Long = DateTime.Now
   
    Dim returnValue As String
    Dim sql As SQL = pool.GetConnection
    Log("1 = " & (DateTime.Now - n))
   
        Try
            'work with sql
            Dim sf As Object = sql.ExecQueryAsync("SQL","SELECT Value FROM `StatusV2` WHERE `Location` = ?", Array As Object (Account & "_" & device))
            Log("2 = " & (DateTime.Now - n))
            Wait For (sf) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
            Log("3 = " & (DateTime.Now - n))
            If Success Then
               
                If rs.NextRow Then
                    returnValue = rs.GetString("Value")
                End If
               
            End If
            Log("4 = " & (DateTime.Now - n))
        Catch
            Log(LastException.Message)
        End Try
       
        rs.Close
        sql.Close
   
    Log("5 = " & (DateTime.Now - n))
    Log(" ")
    ' will return value
    Return returnValue
   
End Sub

This seems to work and doesn't seem to crash.

It logs the following.

1 = 22
2 = 26
1 = 22
2 = 22
3 = 61
4 = 61
5 = 62

1 = 23
2 = 23
3 = 73
4 = 73
5 = 73

3 = 46
4 = 46
5 = 47

1 = 19
2 = 19
3 = 42
4 = 42
5 = 42

1 = 21
2 = 22
3 = 44
4 = 45
5 = 45

1 = 25
2 = 25
1 = 21
2 = 21
3 = 57
4 = 57
5 = 58

3 = 47
4 = 47
5 = 48

1 = 23
2 = 23
3 = 46
4 = 46
5 = 46

1 = 21
2 = 22
3 = 44
4 = 45
5 = 45

1 = 22
2 = 22
3 = 45
4 = 45
5 = 46

1 = 22
2 = 22
3 = 47
4 = 47
5 = 47

1 = 21
2 = 21
3 = 45
4 = 45
5 = 45

1 = 22
2 = 22
3 = 48
4 = 48
5 = 48

1 = 24
2 = 24
3 = 47
4 = 47
5 = 47

1 = 21
2 = 21
3 = 45
4 = 45
5 = 45

1 = 30
2 = 30
3 = 55
4 = 55
5 = 55

1 = 22
2 = 22
3 = 44
4 = 44
5 = 44

1 = 21
2 = 21
3 = 44
4 = 44
5 = 44

1 = 22
2 = 22
1 = 24
2 = 24
1 = 22
2 = 22
3 = 76
4 = 76
5 = 77

3 = 64
4 = 64
5 = 64

3 = 46
4 = 46
5 = 46

1 = 21
2 = 22
3 = 45
4 = 46
5 = 46

1 = 24
2 = 24
3 = 48
4 = 49
5 = 49

 

[aaronk]

What happens?

This seems to work and doesn't seem to crash.

Looks like it does crash.
Thought at first it didn't, but after running it for approximately 1-2 minutes it stops and displays the error java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.

 

[OliverA]

Then you are keeping connections longer open then what is necessary for the demand of newer connections. We need to be able to reproduce this with your help. We need a minimalistic application that can reproduce this issue posted here. Including the table setup used. Unless you have a super amount of traffic hitting tour server or your sql queries are taking an unusual amount of time, the default pool size hardly needs to be touched. Also, ensure you’re not hammering your server in debug mode (it does introduce processing overhead).

 

[aaronk]

Including the table setup used

My Table (in MySQL) looks like the following:

I called the table StatusV2

We need a minimalistic application that can reproduce this issue posted here

I am using the code from Post #14.

 

[OliverA]

What jdbc driver and version? What java version?

 

[OliverA]

It still would be a lot simpler if you would post a small, complete runnable app (except for running MySQL server) that reproduces this issue on your end. MySQL version? What platform is it running on?