• Welcome, Guest. Please login.
 
September 22, 2019, 05:28:15 am

News:

Welcome to the SQLitening support forums!


What are the practical limits for SQLiteningServer

Started by Marty Francom, October 28, 2010, 05:38:16 am

Previous topic - Next topic

Marty Francom

October 28, 2010, 05:38:16 am Last Edit: October 28, 2010, 01:41:59 pm by Marty Francom
Are there any practical limits for the SQLiteningServer ?

I have a customer that wants to host on his server upto 100 SQLite databases. 
Each database will have a different name but have the same structure (same table names same field names)
All databases will reside in the same directory with the SQLiteningServer software.
The databases will be accessed remotely by up to several hundred users.
With heavy use of the server is there any chance that the server could return information to the wrong user?
Or, worse, post info to the wrong database.

What are the worst case senerios?   And how can I protect against problems?

With many databases being accessed an nearly the same time who might this effect performace of SQLitening?   

Are there any practical limits to the number of databases the SQLiteningServer can handle at any one time?

Fred Meier

October 29, 2010, 02:01:27 pm #1 Last Edit: October 29, 2010, 02:08:11 pm by Fred Meier
QuoteAre there any practical limits for the SQLiteningServer ?

SQLitening has no arbitrary limits.  Of course, every program that runs on
a machine with finite memory and disk space has limits of some kind.

Set http://www.sqlite.org/limits.html for SQLite limits.
QuoteWith heavy use of the server is there any chance that the server could return information to the wrong user?
Or, worse, post info to the wrong database.
No
QuoteAre there any practical limits to the number of databases the SQLiteningServer can handle at any one time?
No
QuoteWhat are the worst case senerios?   And how can I protect against problems?
With many databases being accessed an nearly the same time who might this effect performace of SQLitening?   
The large number of databases should not be a problem/concern. 
Performance is affected by the number of users, how often they make
requests, how long the requests take, speed of the network, and the
size(speed/memory) of the server.  Other users may have experience they
can share.  I would write a 'stress test' application that would simulate
your situation as close as possible to determine the performance.

sphinx

First of all....Hello all :)

I am impressed by SQLitening and would like to thank everyone helped in this great project especially Fred.

QuoteI would write a 'stress test' application that would simulate
your situation as close as possible to determine the performance.

Yes, please do so it will be very helpful.

I myself is a VB6 programmer, I have a very successful LIS (Lab Information System) for small to medium size medical laboratories and I am using MS ACCESS, it is excellent for Single user and good for multi user (up to 8 concurrent users). My LIS is being developed all the time and some Labs have more than 10 PCs and here comes my pain as my database program started to suffer from performance hit and database corruption from time to time!

I am leaving VB6 and right now I making the decision to use either PureBasic or PowerBasic along with Arctic Reports but more importantly SQLitening.

So I need to know SQLitening practical limits, although I am sure it will be far better than MS Access and it is more than enough for my project (No Lab will have more than 10-20 PCs at least the Labs I know).
I was thinking of PostgresSQL but SQLitening will do the job just fine without big installation files and easy or zero configuration for the server.

Thanks again Fred and I would like to know your (and everybody too) 2 cents.

Fred Meier

Hi sphinx -- welcome and thanks for the kind words.

When I said "I would write a 'stress test' application...." I really
should have said "If I was Marty, I would write a 'stress test' application....". 

It's my opinion that a true 'stress test' can only be run on the actual
target hardware.  Using the actual projected number of clients, network,
and server and doing the number of projected request per client per
minute. 

I can and will write a simple 'stress test' that runs multi processes on a single
computer to the localhost server and post the results.  This type of test
will stress the SQLiteing/Sqlite architecture but can not be used for any
real world performance requirements. 

sphinx

QuoteWhen I said "I would write a 'stress test' application...." I really
should have said "If I was Marty, I would write a 'stress test' application....".

My bad :(
You have to excuse me as English is not my native language!

Anyway, thanks for your reply and keep up the great work ;)

Fred Meier

I developed the stress test and ran it many times.  This test was run only
on my single computer -- no actual network.  This purpose of this test was
NOT to determine performance values but rather to test the integrity of
SQLiteningServer/SQlite under stress. 

I developed two program --- one was the Bot(robot) and the other was the
Controller.  The Controller would start N number of Bots and then wait
until they were requested to stop.  Each Bot would run as a seperate
process on my computer connected to the LocalHost SQlitengServer.  When a
Bot stops it would post its activity into a database .  After all the Bots
had posted their activity the Controller would verify the results. 

I ran the test many times with different numbers of Bots.  I even ran it
several times with 500 Bots.  That means there were 500 processes(clients)
with 500 concerrent connections to SQLiteServer.  Each Bot would Insert,
Update, Delete and Select records to/from a single database. 

The good news is --- SQLiteServer/SQLite produced valid results on EVERY
test, not a single failure. 

Below are the stats for a 100 Bot run.
QuoteNumber of Bots=100      Average Minutes Run=13.4
Messages=42613  Messages Per Minute=  3187.60832504883  Messages Per Second=  53.126805417480
Inserts=  6534  Inserts Per Minute=    488.76710853188  Inserts Per Second=    8.146118475531
Updates=  3310  Updates Per Minute=    247.60011160705  Updates Per Second=    4.126668526784
Deletes=  6077  Deletes Per Minute=    454.58183632510  Deletes Per Second=    7.576363938751
Selects= 26692  Selects Per Minute=   1996.65926858478  Selects Per Second=   33.277654476413


Below is the pseudo sorce code of the loop that each Bot executed.
   ' Loop until command to stop
   do

      ' Check for stop
      if slSelStr("Select Command from tblBotCtl") = "Stop" then
         slClose
         slDisconnect
         exit do
      end if

      ' Determine what kind of transaction to do
      select case long rnd(1, 10)
      case 1      ' Insert
         for llDo = 1 to gkInsertCount
            incr llInsertCount
            slExe iif$(llDo = 1, "Begin;", "") & slBuildInsertOrUpdate("tblStress", "Null" & $NUL & _
                                         format$(llProcessID) & $NUL & _
                                         format$(rnd(100, 199)) & $NUL & _
                                         format$(rnd(200, 299)) & $NUL & _
                                         format$(rnd(300, 399)) & $NUL & _
                                         "Null") & iif$(llDo = gkInsertCount, ";End", "")
            if llDo <> 1 then incr llMessageCount
         next

      case 2      ' Update
         incr llUpdateCount
         slExe slBuildInsertOrUpdate("tblStress", format$(llUpdateCount), _
                                                  "UCount", _
                                                  "ProcessID=" & format$(llProcessID))
         llRowsUpdatedCount += slGetChangeCount

      case 3      ' Delete
         incr llSelectCount
         lsA = slSelStr("Select RowID from tblStress where ProcessID=" & format$(llProcessID) & " Order by RowID")
         llRowsSelectedCount += axParseCount(lsA, $VT)
         for llDo = 1 to axParseCount(lsA, $VT)\2
            slExe "Delete from tblStress where RowID=" & parse$(lsA, $VT, llDo)
            incr llDeleteCount
            incr llMessageCount
         next

      case > 3      ' Select
         incr llSelectCount
         slSel "Select * from tblStress where ProcessID=" & format$(llProcessID)
         do while slGetRow
            incr llRowsSelectedCount
         loop

      end select

      ' Sleep some
      axDoEvents
   loop



sphinx

Coooool....thanks Fred for taking the time to do this test.

QuoteThe good news is --- SQLiteServer/SQLite produced valid results on EVERY
test, not a single failure. 

That's a relief. I can start using SQLitening without worries now ;)

No more 'Dot Not' or SQL Server bloatware!!!