Larry Kubin wrote:
Hello everyone. I am interested in creating a PHP/SQLite powered
bulletin board system similar to phpBB. However, I have read that
SQLite is best suited for applications that are mainly read-only
(because it locks the database during writes). Do you think a SQLite
powered bulletin board is a bad idea? How would I go about handling
the case where two users are trying to write to the database
simultaneously?
The CVSTrac system on www.sqlite.org is backed by an SQLite
database (of course). Every single hit does a write to the
database. It gets 20K hits/day from 2K distinct IPs and runs
on the equivalent of a 150MHz machine with no problems at all.
It could easily handle more traffic. On a faster machine, it
could handle *lots* more traffic.
I've run tests on a workstation where an SQLite-backed website
was handling 10 to 20 hits per second (simulated load).
Use the busy handler on SQLite so that if one thread is writing,
all other threads simply wait their turn.
The trick is not to linger of your writes. Decide what you want
to write into the database, start the transaction, make your
update, and commit. You can make a big change in 10 or 20
milliseconds. What you should avoid doing is starting the
transaction, then doing a bunch of slow computations, then
writing the results and committing. Compute the results first,
before you start the transaction, so that your lock window is small.
If you want to accumulate a lot of results over time and store
them all atomically, write the results initially into a TEMP
table. Then copy the TEMP table contents into the main database
in a single (atomic) operation. Writing to a TEMP table does not
lock the database.
A good rule of thumb is that if your website is small enough
that it can be run off of a single webserver and you do not
need a load-sharing arrangement, then SQLite will probably meet
your needs. If you website traffic gets to be so much that
you are thinking about offloading the database onto a separate
processor or splitting the load between two or more machines,
then you should probably use a client/server database instead.
The best design would be to make the application generic so
that it could use either SQLite or a client/server database.
Then smaller sites could use SQLite and take advantage of
the reduce management and overhead it provides while larger
sites could use a client/server database for scalability.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
