Page 1 of 1
SQLite Database Limit
Posted: Tue Aug 28, 2012 3:04 pm
by 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!
Re: SQLite Database Limit
Posted: Tue Aug 28, 2012 3:22 pm
by 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.
Re: SQLite Database Limit
Posted: Wed Aug 29, 2012 11:38 am
by jamirokwai
wilbert wrote: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
Re: SQLite Database Limit
Posted: Sat Sep 08, 2012 1:37 am
by 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.
Re: SQLite Database Limit
Posted: Sat Sep 08, 2012 8:06 am
by jamirokwai
netmaestro wrote: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>
Re: SQLite Database Limit
Posted: Sat Sep 08, 2012 8:33 am
by 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