What are limits of the SQLite Database?

[lance]

I've been testing this product and everyone that has been included in the testing, including myself, are very impressed with it.

Does anyone know what the limitations are of the included SQLite database (Max number of contacts, max size of the database, etc.)? Just need to know if the standard installation will suit most sites or if a MySQL setup will be needed too.

[kiwanja]

Hi Lance

Thanks for your message. We're really glad you're impressed with FrontlineSMS!

I'll get one of the developers to get back to you on the SQLite question. We are going to work on this in the new year to allow the software to scale to a much higher level and, again, can let you know about that.

Where are you thinking of using this/rolling this out? Curious about your "most sites" comment! =)

Ken

[Ben Whitaker]

You should find that it's absolute limits on contacts/messages etc will not be hit by any practical project that is dealing with less than 2 Billion contacts or 2 Billion messages.
2,147,483,647 to be precise.

You will probably start to hit disk space and search performance issues before the SQLite database engine refuses to take more messages, unless your machine is fairly high spec.

See this link for recommendations about the point at which you should consider moving to another database for anything other than convenience of working with other systems that have another favourite database.

To prevent the graphical user interface slowing to a crawl and running out of memory when the number of messages in the database gets large, you will notice that we have designed the message view screen to only show a fixed number of messages at a time and you can click the foward and backward buttons to go from one page to the next. Use the date limits, or filter by sender, keyword or group to quickly reduce the number of displayed messages and find the ones that you wish to see.

(for real techies, the official limits and warnings about SQLite are on the project website, on http://www.sqlite.org/limits.html )


MASSIVE PROJECTS:
If you are looking to do something that has to seriously scale to multi-million messages and users, then you should talk to the FrontlineSMS Development team, and get a server version from us, and switch to an external database on a dedicated machine. FrontlineSMS's internal architecture is already set up ready to convert into a large scale server, but the desktop version that you have now is not configured with that in mind.

You would also need to use an internet send and receive facility connected direct into a mobile network operator to cope with the higher message frequency, which will out-strip physical handset capabilities (the GSM network has a design limitation of 5 SMS per physical handset per minute, and there are also cell/base station limits that may cramp a national infrastructure project's simultaneous message rates - please come and have a chat if you are in these categories).

The internet send/recieve features should be available in the Desktop version of FrontlineSMS in Q1 2009 for people expecting large volumes of incoming messages - please drop us a line if you want to be alerted to this when it is released for your project, and to help us assess the demand for such features.

[lance]

I would be interested in learning more about this server version as we would like to run this as a process or something. Do you have any additional information on it (OS X and Windows versions)?

Also if I connect this to say a 16 SIM gateway and have all the gateway SIMS individual devices that can send, does the software send 5 to each exit point automatically to get around the 5 message/minute limitation or is there a way to tell the software to do this?

[kiwanja]

Hi Lance

I can answer the second part for you...

If you have multiple devices attached to FrontlineSMS, it will cycle through them (assuming they are configured to 'send'). So, if you had 16 devices it would send a message to all 16 in turn, and then return back to the first one, allowing you to send 5 x 16 messages per minute.

Hope that helps!

Ken

[Eric Barrett]

My organization, Transparency International Georgia (www.transparency.ge), is looking to monitor elections in March 2010 using SMS (population about 4.5 million) and, after reading your post, I would like to speak with someone in your FrontlineSMS Development team about what we would need ourselves, what we would need from our cell phone network and how to set it up to output information that can be used on our website in more-or-less real time. Any help you might give will be greatly appreciated. Thanks.

Eric Barrett
[e-mail]

[kiwanja]

Hi Eric

Any project or monitoring campaign on that scale would need you to work directly with a local operator or a messaging aggregator (such as Clickatell, for example). No standalone desktop tool running off an attached phone or modem will be able to handle these volumes of messages, I'm afraid.

Sorry we couldn't be any more help.

Ken

[Charles M]

Hi Ben,
Please keep me informed on the launch of the internet send/receive feature. Thanks.