12

I have a database of 50MB size in SQLite 3. All db objects are accessed through a web service. Is SQLite a good choice for an concurrent online usage of about 500 parallel users.

NOTE: Users will use same tables but not same rows. Each user can see/update/delete only his data.

Gad D Lord
  • 6,620
  • 12
  • 60
  • 106
  • 4
    What do you mean parallel? Online at the same time, or there'll be 500 file handles to the DB? – Novikov Oct 05 '10 at 20:59

4 Answers4

21

SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

(Source)

Nifle
  • 11,745
  • 10
  • 75
  • 100
  • 8
    Here is someone who really tried it http://stackoverflow.com/questions/54998/how-scalable-is-sqlite – Nifle Oct 05 '10 at 21:11
  • 3
    100K hits per day is 1.15 hits per second (3-4 assuming an 8 hour work day). A website with 500 concurrent users is going to be getting more like 100-500 hits per second. – tster Oct 05 '10 at 21:11
  • 1
    @tster - Sounds about right. I'm was just providing data, not offering an opinion. – Nifle Oct 05 '10 at 21:15
  • +1 for the link in your first comment. The OP should check that out. – tster Oct 05 '10 at 21:17
4

According to wikipedia SQLLite will fail a write if there are any concurrent accesses to the database. This alone makes me think it is not usable for 500 concurrent users.

Unless there are hardly any updates going on that is. I think 500 concurrent users is certainly enough to warrent a full blown DBMS.

Note, it looks like that wikipedia article is wrong (pretty common on wikipedia really). Anyways, if those 500 users are going to be updating a lot, I would still be weary of 500 concurrent users with 0 concurrent writes. While the numbers in the thread referenced by Nifle sound good, those are most likely from tests which are engineered to make sqllite look good. I doubt you will get the same milage with all queries, all table sizes, all cache states, etc.

tster
  • 17,883
  • 5
  • 53
  • 72
  • 3
    Sqlite will not fail if you enable the transactions and use correctly the locks. This is made correctly and transparently by a decent ORM but also it is a good practice. – alexroat Jun 12 '17 at 19:59
0

I run a few web servers on VPS and locally on Pi computers, these all run on Ubuntu Linux using MySQL or MariaDB

For web servers you must have transaction management to ensure writes from multiple users function properly

SqLite is intended for low usage, single instance database, you are likely to suffer data integrity problems, write failures and other issues with it

SqLite is NOT suitable for web servers

You will also have to learn about web security and issues such as cross site scripting and SQL injection attacks or you risk having you site trashed within days

I suggest you install a local web server suite, known as WAMP on Windows and LAMP on Linux meaning Windows(Linux), Apache, MySQL, PHP
PHPMyAdmin is a great tool for database management on web servers and is generally included with WAMP, LAMP setups
This is always a good starting point for anybody learning about web hosting

Peter Dunne
  • 59
  • 1
  • 3
-4

MySQL is a far better choice for web servers.
SQLite is not installed by default on most servers whereas MySQL is often installed.
MySQL has transaction management so it is suitable for multiple concurrent writes.

S.L. Barth is on codidact.com
  • 8,198
  • 71
  • 51
  • 66
Peter Dunne
  • 59
  • 1
  • 3
  • 4
    Obviously SQLite it's simpler that MySQL, postgresql, Oracle... It is just a 200kb library. the fact that sqlite it is not present you're wrong, infact the number of ports of sqlite is in fact superior than MySQL or other DB engines. The fact that is not installed by default is not relevant considering that every server requires a system admin that configure it with required software. That's IMHO. – alexroat Jun 12 '17 at 19:54