B4J Question Some questions about SQLite-databases (KVS2)

[schimanski]

I'm using as lot of small SQLite-databases with the keyvaluestore2-class in my project and it works fine in normal mode. But on every stress test of my messenger-app, I always get sqlite-exceptions:

ERROR - In Modul FileServer, Sub Handle: java.sql.SQLException: [SQLITE_IOERR] Some kind of disk I/O error occured (disk I/O error)
ERROR - In Modul IOSPushFirebase, Sub SendMessage: java.sql.SQLException: [SQLITE_CANTOPEN] Unable to open the database file (out of memory)
FATAL - In Modul Main, Sub AppStart Code 2: java.sql.SQLException: [SQLITE_CANTOPEN] Unable to open then database file (out of memory)
ERROR - In Modul IOSPushFirebase, Sub SendMessage: java.sql.SQLException: [SQLITE_IOERR] Some kind of disk I/O error occured (disk I/O error)

I think, that i don't use the databases in the right way. I have some questions about it to understand the reasons for the exceptions. Hope, someone could help me:

1.
I'm saving the members of the chats in small sqlite-databases. Every time, when a client sends a message to the server, the server reads the keyvaluestore in a local variabel and reads out the information to which member the message should send to. I use the following code (greatly simplifies):

Sub Push(msg As Map)
    Dim iosTokens As List
    iosTokens.Initialize

    Dim Kontaktgruppe As KeyValueStore2
    Kontaktgruppe.Initialize(File.DirApp, "Konferenzen/" &  "KG_" & msg.Get("konferenz") & ".db", False)
    
    For Each key As String In Kontaktgruppe.ListKeys
        Dim ID As String
        ID=Kontaktgruppe.Get(key)
        iosTokens.Add(ID)
    Next
    If iosTokens.Size > 0 Then CallSubDelayed3(iOSPushFirebase, "SendMessageTo", iosTokens, msg) 
End Sub

If there are a lot of messages, the code-modul with the Push-sub is called a lot of times shortly after each other. I think, that it is unproblematic because each time the database is stored in a lokal variable. Is that correct or is there a problem?

2.
The small databases are downloaded by the clients to get also the informations about the members of a chat. If I set the wal-mode to true, do I have to download all 3 database-files (file, file-shm and file-wal) or are all relevant informations in the main-file of the kvs?
3.
I think, that the main problem of my app is the use of the follwing code (greatly simplifies):

Private Sub SendMessage(Devices As List, msg As Map)
    .....
    Dim DeviceDownload As KeyValueStore2
        
    For i=0 To Devices.Size-1
        Dim LE As List = Kontakte.Get(Devices.Get(i))
        DeviceDownload.Initialize(File.DirApp, "Download_/" & LE.Get(0) & ".db", False)
    
        Dim FilesMap As Map
        FilesMap.Initialize
        If DeviceDownload.ContainsKey(Name) Then FilesMap=DeviceDownload.Get(Name)
        
        Dim Dateiname As String = msg.Get("filename")
        FilesMap.Put(Dateiname, msg.Get("daten"))
        DeviceDownload.Put(Name, FilesMap)
    Next
    ....
    Dim jg As JSONGenerator
    jg.Initialize(m)
    Job.PostString("https://fcm.googleapis.com/fcm/send", jg.ToString)
    Job.GetRequest.SetContentType("application/json;charset=UTF-8")
    Job.GetRequest.SetHeader("Authorization", "key=" & FirebasePushApiKey)
End Sub

The DeviceDownload-KVS are small databases for each user of the messenger, in which all messages which are send to will be saved to sync the client later with the server. It is important, that the files are small to make a fast synchronization with the server after starting the app. If the message is already on the client, the key will be removed out of the DeviceDownload-KVS. If there are a lot of messages, devicedownload for each user will be initialized a lot of times and the server writes to. I think, that this is the main problem and the reason for the exceptions above. What will be a realitic solution for this kind of problem without rewriting the whole app?

We use for each unit of 100 to 130 peoble one server. I think that there are no great requirements to the system....

Thanks for any help...

 

[OliverA]

The one problem you may (may) be having in your Push method is the issue of Push being called for the same "konferenz" multiple times in succession. Then it could occur that the DB is still open (via Initialize) and has another open (Initialize) request on top of that (your opening the same db file multiple times). Since this is on the server, you may want to open the DB's on demand and then keep them open (and use WAL mode to open them). You could use a map (with "konferenz" as a key) to store the SQL object pertaining to each open message board.

The FATAL error you are accounting may also be from the Push method. You are Initializing KVS2's, but you are never closing them.

 

[schimanski]

Thank you for your efforts!

So i have a thinking error. It is not possible to read the database multiple times in a local variabel because it is always the same resource, on which the system accesses to. If I close each sqlite-database after reading it out, it could be a little better, but it is not a flawless solution.

You could use a map (with "konferenz" as a key) to store the SQL object pertaining to each open message board.

Is it possible to make one bigger kvs2 instead of a map with the konferenz as key and open it once at the begining of starting the server and read the values out of this. In wal-mode, multiple readers should not be a problem. But first: I'm not sure if it is enough flexible for that what I want and the second question: I need a simple and fast solution to download the needed values for one konfernz by the client. Until now, each Konfernez was a separated sqlite-database which was simply downloaded by the client.

 

[OliverA]

Could try CloudKVS (https://www.b4x.com/android/forum/threads/b4x-cloudkvs-synchronized-key-value-store.63536/). Use the "konferenz" as the user field in CloudKVS. Then CloudKVS would only sync the relevant konferenz(es).

 

[schimanski]

I think, that this could be a solition. I read the thread and have a question about it: The data should only be changed by the clients, not directly from the server. What about reading data? Is it trouble free to read the data out of the cloudkvs from server-side? The data has to sync the clients, but the server also needs the values out if the store.
Thanks for help..

 

[OliverA]

1) Modify CloudKVS code for your purposes (merge server application code with CloudKVS)
2) Modify CloudKVS code to use MySQL (search forum, I think it has already been done). Your server application can then use MySQL client libraries to read, and if necessary, update data. This is not restricted to MySQL. You could change over to any database that handles multiple connections.
3) Make the server application another client of CloudKVS (can run on same machine, or if needed, on a separate machine).

 

[schimanski]

Thank you for answer. But I think, that CloudKVS is good enough without modifing it in such a costly way you told. I want to avoid, that it works during tests well, but under stress, it doesn't. If I understand you right, it is problematic to read the data out of the store from server side, isn't it?

 

[OliverA]

Out of the box, CloudKVS uses SQLite. WAL mode is enabled, but I would not let two programs access the same db file. Just use the client code (ClientKVS.bas) in your server side to get access to the information. You could always change the autorefresh timing in the ClientKVS module to check values more often.

 

[Erel]

See this tutorial about concurrent access to SQLite databases: https://www.b4x.com/android/forum/t...ent-access-to-sqlite-databases.39904/#content

 

[schimanski]

Thanks for your efforts. I have read everything about it, but I don't catch it:

Related to this,

No, it will cause a mess. You shouldn't modify the data from the server. You should instead create a client and set the values to Null. You can use a B4J client that runs on the same computer as the server.

it is not possible to modify the data from the server. But what about reading?

...but I would not let two programs access the same db file.

There is only one program of the server, which access the db, or?????

 

[Harris]

What will be a realitic solution for this kind of problem

My 2 pesos...

Try using MySQL (as per @OliverA ) on the server and jRDC2. It does not suffer the limitations of SQLite. Once mastered - which isn't a huge task - and you will thank yourself by eliminating the frustration. Quite frankly, your methods seem to be - and continue to be problematic. It is akin to using a MS Access DB - but pardon my use of such filthy choice of words...

If your devices are mobile, use the SQLite on the device only. This is how I do it without ever an issue.
Thousands of records - to and from the server hundreds of times per day - no problems, no worries.

 

[schimanski]

I think you are right, that my solution is problematic. That shows the stresstests....I wanted to avoid to use such a complex solution. I'm not familiar with mySQL and PHP. I wish there is an easier way...Thanks for your assessments.

 

[Erel]

How is PHP related here?

You have two options:
1. Use SQLite as described here:
https://www.b4x.com/android/forum/t...ent-access-to-sqlite-databases.39904/#content

2. Use a different database such as MySQL which can handle many concurrent clients.

 

[Harris]

I'm not familiar with mySQL

Well my friend, there is no time like the present to learn...
There was a time not so long ago I didn't know anything about it as well... Hell - I couldn't even spell MySQL!
And before that - I knew NOTHING about B4X (how to code - some say I still don't - including me). The power of learning (plus the need / desire) makes all things possible.

Pull up yer boots and strap on the search util... What you need to know has been asked and answered right here - often many times over - at nauseum.

In Erel's ETP sessions, he cites an example of searching a file to count the number of words used in the doc. It would be interesting to apply the same routine to this forum for same question asked...

 

[schimanski]

I'll do my very best

 

[OliverA]

There is only one program of the server, which access the db, or?????

1) CloudKVS
2) The program that reads that data synced by CloudKVS

Reading over WAL once more on SQLite's site, it looks like both CloudKVS and your program may be able to access the same database file so long as
1) Both programs run on the same computer In a virtualized environment, both programs need to run in the same VM.
2) Only one program writes (in this case CloudKVS) and the other program only reads from the file. In other words, both programs should be able to use B4J's SQL Objects InitializeSQLite method to open the same file. Your program then should only perform SELECT queries on the file, since it should only read from it. You would have to test this, since I have not.

More detail on WAL: (https://www.sqlite.org/wal.html)

 

[KMatle]

Maybe Sqlite isn't that optimal for heavy traffic on a webserver. Think about changing to MySQL. My opinion: Sqlite is good for solutions with e.g. 100 users or so (maybe a hundred more with light traffic). If your app grows quick I don't see SQlite anymore. MySQL (and similar) have much more options like load balancing, etc.

 

[keirS]

Maybe Sqlite isn't that optimal for heavy traffic on a webserver. Think about changing to MySQL. My opinion: Sqlite is good for solutions with e.g. 100 users or so (maybe a hundred more with light traffic). If your app grows quick I don't see SQlite anymore. MySQL (and similar) have much more options like load balancing, etc.

For a KVS Apache Ignite or Hazlecast would be a better bet. Ignite has a JDBC driver so is probably the better option.

 

[schimanski]

Mutch thanks for your efforts.

We have 100-150 users per one server, but the sqlite-dbs are not under such a high traffic because the databases are not changed every time, when the user uses the app.

As mentioned here,

http://www.sqlite.org/whentouse.html

I first read something about the sqlite facts: A database with up to 140 terabytes and an unlimeted number of readers under wal. So I think, it could work for me but it seems to be a border area.
I now began to studie Erels and KMatle tutorials about MySql to check the
costs. I will do some tests and call back...

 

[schimanski]

With your great support, I actually done an xampp-sql-server now, it is incredible! But seems to be something wrong with my table. I'm now looking here for further help:

https://www.b4x.com/android/forum/t...ized-key-value-store-mysql.69042/#post-565304