B4J Question [SOLVED] Hostgator - too many requests to database

[jroriz]

Hi everyone.
I developed an application that accesses MySql remotely.
The provider (Hostgator) sent me an alert saying that I am sending too many requests to the database.
The log of requests they sent me does not help much:

Running Queries:
1. row
USER: appfoodc_admin
DB: appfoodc_byappfood
STATE:
TIME: 22
COMMAND: Sleep
INFO:
2. row
USER: appfoodc_admin
DB: appfoodc_byappfood
STATE:
TIME: 87
COMMAND: Sleep
INFO:
...

I'm guessing that may be the way I'm accessing the database.

Is everything correct with my code?

Sub Process_Globals
    Dim pool As ConnectionPool, Hostgator As SQL
end sub

Sub AppStart (Form1 As Form, Args() As String)

    server = "jdbc:mysql://mydomain.com.br/myfolder"
    
    Try
        pool.Initialize("com.mysql.cj.jdbc.Driver", server, "user", "password")
    Catch
        log("Connection error")
        ExitApplication       
    End Try
end sub

Sub Write(query As String) As Boolean

    Try
        Hostgator = pool.GetConnection

        Hostgator.BeginTransaction
        Hostgator.ExecNonQuery(query)
        Hostgator.TransactionSuccessful
        Hostgator.Close
        Return True
    Catch
        Log(LastException)

        '''''''''''''''''''''''''
        ' hostgator.close here? '
        '''''''''''''''''''''''''

        Return False
        
    End Try
    
End Sub

Sub Read(q As String) As Object

    Try
        Hostgator = pool.GetConnection
        Dim tmp As Object
        tmp = Hostgator.ExecQuerySingleResult(q)
        Hostgator.Close
    Catch
        Log(LastException)

        '''''''''''''''''''''''''
        ' hostgator.close here? '
        '''''''''''''''''''''''''

        Return Null
    End Try

    Return tmp
End Sub

 

[KMatle]

So: How many requests do you send in which time? How many clients? Is your plan limited? Did you check if your queries must be optimized/running too long?

 

[jroriz]

Thanks for the reply.

Requests are random.
Few customers.
The plan has limits, but they are far from being hit.
The queries are EXTREMELY simple.

What about my code? Is he correct?

 

[Erel]

The correct pattern to work with a connection is:

Dim sql As SQL = Pool.GetConnection
Try
 '..work
Catch
 Log(LastException)
End Try
sql.Close

This is probably not related to the usage issue.

Try to lower the pool maximum size. Test it with a size of 1:
https://www.b4x.com/android/forum/threads/database-connection-pool-question.39816/ (posts #2 and #4).

 

[jroriz]

Thank you.
I will try pool maximum size.

 

[jroriz]

The correct pattern to work with a connection is:

Dim sql As SQL = Pool.GetConnection
Try
 '..work
Catch
 Log(LastException)
End Try
sql.Close

This is probably not related to the usage issue.

Try to lower the pool maximum size. Test it with a size of 1:
https://www.b4x.com/android/forum/threads/database-connection-pool-question.39816/ (posts #2 and #4).

In the appstart sub I initialize the pool object, which is global.

The user will terminate the application by closing the form.

Will the session remain open? Because the pool.closepool command was not executed.

 

[Erel]

It is better to close it in MainForm_Closed event.

 

[EnriqueGonzalez]

In the appstart sub I initialize the pool object, which is global.

The user will terminate the application by closing the form.

Will the session remain open? Because the pool.closepool command was not executed.

Depending on the quality of the internet connection you may want to keep it open for a long time (close when form is closed) or if connection is poor. Open... Do stuff ... Close it. Then you will not have to check every time if the connection is already closed.

 

[jroriz]

It is better to close it in MainForm_Closed event.

"It is better" means it is not mandatory?

What happens to the connection when an application closes for an unplanned error?

What if I open the connection EVERY time, run the command and close it, as Enrique Gonzalez suggested?

Sub RunQuery(query As String)
   
    Dim pool As ConnectionPool
    Dim hostgator as sql

    Try
        pool.Initialize("com.mysql.cj.jdbc.Driver", "jdbc:mysql://mydomain.com.br/mydatabase", "user", "password")
        Dim jo As JavaObject = pool
        jo.RunMethod("setMaxPoolSize", Array As Object(3))
    Catch
        Log(LastException)
        pool.ClosePool
        ExitApplication
    End Try

    Try
        Hostgator = pool.GetConnection

        Hostgator.BeginTransaction
        Hostgator.ExecNonQuery(query)
        Hostgator.TransactionSuccessful
    Catch
        Log(LastException)
    End Try
   
    Hostgator.Close
    pool.ClosePool
End Sub

 

[OliverA]

You are now on the wrong path. The connection pool is global, just as you had it. The SQL object is local to the method/sub-routine, just as @Erel shows in his sample code. You asked about closing the pool and you were given the option to do that upon closing the Main form (ending the application). That's a one time thing.

 

[OliverA]

BTW, if you application accesses the DB infrequently, you could just do away with the connection pool and just use the SQL object. It all depends on what the application does and how much you access the DB and how many clients will do this at the same time.

Sub RunQuery(query As String)
    
    Dim Hostgator As SQL
    
    Try
        Hostgator.Initialize2("com.mysql.cj.jdbc.Driver", "jdbc:mysql://mydomain.com.br/mydatabase", "user", "password")
    Catch
        Log(LastException)
        Hostgator.Close
        ExitApplication
    End Try

    Try
        Hostgator.BeginTransaction
        Hostgator.ExecNonQuery(query)
        Hostgator.TransactionSuccessful
    Catch
        Log(LastException)
    End Try
    
    Hostgator.Close

End Sub

Note: untested code, just yours w/slight modifications.

 

[jroriz]

You are now on the wrong path. The connection pool is global, just as you had it. The SQL object is local to the method/sub-routine, just as @Erel shows in his sample code. You asked about closing the pool and you were given the option to do that upon closing the Main form (ending the application). That's a one time thing.

In the above case, both hostgator and connectionpool would be local instead of global.

 

[jroriz]

BTW, if you application accesses the DB infrequently, you could just do away with the connection pool and just use the SQL object. It all depends on what the application does and how much you access the DB and how many clients will do this at the same time.

Sub RunQuery(query As String)
 
    Dim Hostgator As SQL
 
    Try
        Hostgator.Initialize2("com.mysql.cj.jdbc.Driver", "jdbc:mysql://mydomain.com.br/mydatabase", "user", "password")
    Catch
        Log(LastException)
        Hostgator.Close
        ExitApplication
    End Try

    Try
        Hostgator.BeginTransaction
        Hostgator.ExecNonQuery(query)
        Hostgator.TransactionSuccessful
    Catch
        Log(LastException)
    End Try
 
    Hostgator.Close

End Sub

Note: untested code, just yours w/slight modifications.

It is a distributed application.

Several restaurants use the application, which is waiting for an order to be confirmed by other application, and only then goes into the database and records 1 or 2 records.

But the database is remote.

The database does not have to be open all the time.

In this scenario is the connectionpool dispensable?

 

[OliverA]

Most likely. Connectionpool is mainly useful if your local application does many remote calls to the server. It can also be used for retrying when connection failure happens. But in your case it may be the wrong tool. One way to find out is to use the other/non-pooling approach.

 

[EnriqueGonzalez]

Most likely. Connectionpool is mainly useful if your local application does many remote calls to the server. It can also be used for retrying when connection failure happens. But in your case it may be the wrong tool. One way to find out is to use the other/non-pooling approach.

Non pooling approach for remote connections can cause problems, because if you lose connection with the server for one milisecond, you lose the conection completly, that is because Erel recommends the pool connection.

 

[OliverA]

because if you lose connection with the server for one milisecond, you lose the conection completly

Note: I hope someone chimes in on the correctness of this (my signature may apply in this case)

I'm pretty sure that the disconnect happens anyways unless you have a JDBC driver that supports reconnecting an existing connection to a DB that also supports that. Otherwise, once a connection is broken, it is broken. Pooling can re-establish another connection for you without having to manage that part. Pooling also allows the creation of a number of connections that are kept alive across which SQL statements/DB functions can be executed. This can help in high traffic environments by alleviating the SQL server with a bunch of create connection/drop connection requests.

In this one special case here (this thread), the pooling and multiple open connections to the DB may be causing issues. Therefore, open and closing connections allows the reuse of the server resources (sort of multiplexing a limited number of connections available on the server against a greater number of clients). I'm not recommending this solution for every case, just this one particular case. Plus if I'm going to open and close the connections with each call anyways, wrapping them in Catch/Try, then I might as well get rid of the complexity of pooling.

 

[Harris]

I use pool (as recommended by Erel) . Without it - had many issues... With it - no probs...

Here is how I do it in ABM (supplied by alwaysbusy). Never a problem - yet this is my own server - NOT Hostgator (who knows what wrenches they toss into the cogs).


Init MySQL - set driver and pool size ONCE.

Sub AppStart (Args() As String) ' in main sub
' change if you want to connect to a mySQL server using connection pool
DBM.InitializeMySQL("jdbc:mysql://localhost/harris?characterEncoding=utf8&useSSL=false", "mhwxxxxx", "b12xxxxx", 150) ' 150 is pool size

'  DBM code module

Sub InitializeMySQL(jdbcUrl As String ,login As String, password As String, poolSize As Int) 'ignore
    Try
        pool.Initialize("com.mysql.jdbc.Driver", jdbcUrl, login, password)
    Catch
        Log("Last Pool Init Except: " & LastException.Message)
    End Try

    ' change pool size...  default is 10 (or maybe less)
    Dim jo As JavaObject = pool
    jo.RunMethod("setMaxPoolSize", Array(poolSize))

'  The get and close methods for MySQL pooling

Sub GetSQL() As SQL
        Return pool.GetConnection      
End Sub

Sub CloseSQL(mySQL As SQL)
        mySQL.Close
End Sub


' ABM update method...

Sub SQLUpdate(SQL As SQL, Query As String) As Int
    Dim res As Int
    Try
        SQL.ExecNonQuery(Query)              
        res = 0
    Catch
        Log(" UPD ERR: "&LastException)      
        res = -99999999
    End Try          
    Return res
End Sub


End Sub

Typical call for a query (update and select)....

' update query....

Sub SetShift(pk As String , sid As Long)  

  Dim sql As SQL = DBM.GetSQL
  DBM.SQLUpdate(sql,"UPDATE loadmast SET sourceid = "&sid &" WHERE pk = "&pk)
'  Log(" setting shift id: "&sid&"  pk: "&pk)
  DBM.CloseSQL(sql)
   
End Sub

'  Simple Select query using ResultSet...

Sub GetDriverName(drv As Int) As String
  
    Dim SQL As SQL = DBM.GetSQL
    Dim res As ResultSet
    Dim nam As String = "Not Found"
    res = SQL.ExecQuery("Select * from emp where PK = "&drv)
      Do While res.NextRow
      nam = res.GetString("Last_name")&", "&res.GetString("First_name")
    Loop  
    res.Close
    DBM.CloseSQL(SQL)
    Return nam
End Sub

You can Try and Catch to your hearts content... which is always a good idea.
The point is - init once - open and close with every query - and get your own server and screw Hostgator - seems there lies the issue.

Thanks

 

[jroriz]

That was what hostgator sent me, and BLOCKED MY SITE with password!

Database requests remain open for a long time.

LOG:
USER: appfoodc_admin
DB: appfoodc_byappfood
STATE:
TIME: 87
COMMAND: Sleep
INFO:

Like this: Empty STATE and empty INFO.

Apparently the problem is not happening anymore, but at the beginning I did a lot of testing, and I think the problem was due to something I did wrong related to the connection to the database. There may have been some connection without closing.

Thank you all for the attention, and I conclude that in my particular case, I do not need to use the connectionpool because I make few accesses to the database, with simple queries and with few records and few users.