SQLite Database Limit

[jamirokwai]

Hi there,

I try to find the limit for open databases using SQLite. On my MacBook Air, Mountain Lion, it is:

x86 (PB 4.61): 119, using about 32mb RAM
x64 (PB 4.7b1): 122, using about 52mb RAM

Anybody interested in helping here with Windows and Linux? Would be very nice of you
This code should create around 120 or so files inside the newly created folder.

Code: Select all

UseSQLiteDatabase()
CreateDirectory("test")
For i = 0 To 1000
  a = CreateFile(#PB_Any,"test/" + Str(i))
  b = OpenDatabase(#PB_Any,"test/" + Str(i),"","")
Next i

repeat
  delay(1) ; to get CPU-load and RAM
forever

Thanks in advance!

[wilbert]

You don't have to leave the file open.
Try this

Code: Select all

UseSQLiteDatabase()
CreateDirectory("test")
For i = 0 To 1000
  CreateFile(0,"test/" + Str(i))
  CloseFile(0)
  b = OpenDatabase(#PB_Any,"test/" + Str(i),"","")
Next i

Repeat
  Delay(1) ; to get CPU-load and RAM
ForEver

You'll see a lot more files.
After a little searching online ... there's a OS X limit of 256 for the number of open files by a single process.

[jamirokwai]

You don't have to leave the file open.
...
You'll see a lot more files.
After a little searching online ... there's a OS X limit of 256 for the number of open files by a single process.

Thanks, Wilbert.
Have to check how much time and CPU closing and opening of a database file takes. It's worth a look, as every user will get it's own file.
Probably, I won't use 1000 Databases but 25 Databases with 50 Users each, but this way, I have to deal with User-IDs.

Will need to think this through, again.

But I think, I found a bug or missing feature in either PB or Mac OS X
See http://www.purebasic.fr/english/viewtop ... 24&t=51095

[netmaestro]

You could consider the option of using one database and then create a table for each user in that database. You'd be using less OS resources that way and it seems a clean, simple approach.

[jamirokwai]

You could consider the option of using one database and then create a table for each user in that database. You'd be using less OS resources that way and it seems a clean, simple approach.

Yes, that's right. But every user will have at least 10 or more different tables associated. I probably will use one database per user, as I am thinking of restricting the server to 256 users - for historical reasons of the data I am using here

</secretmode>

[wilbert]

If you are only using your application on your own servers, you can alter the OS limits.
It requires super user access so you can't create a solution inside your app but you will be able to use more databases.
Just Google for something like
osx increase file handles