Alex Rivera | Logout

One big MySQL DB or a thousand little SQLite databases?

Asked 2009-01-23T14:38:51.200
16

I am working on a Web based organisation tool. I am not aiming the same market as the wonderful Basecamp, but let's say the way users and data interact look like the same.

I will have to deal with user customisation, file uploads and graphical tweaks. There is a fora for each account as well. And I'd like to provide a way to backup easily each account.

I have been thinking how to create a reasonable architecture and have been trained to use beautifully normalized data in a single (yet distributed if needed) MySQL DB. Recently I have been wondering : is it possible to think about using one SQLITE DB to store the data for each account and only use MYSQL for the general web site management ?

The pro :

  • backing up is straightforward : set version, zip, upload.
  • don't bother if each account use a massive fora : the mess is in one file for every one of them.
  • SQLITE is lightening fast, no expensive connection time...
  • Table scheme is much simplier : no need to make any distinction between account every times

The cons :

  • don't know if it's scalable
  • don't know if the hard drive will keep up
  • don't know if there is a way for SQLITE to not be stored in RAM since it would be quickly a disaster
  • lots of dir and subdirs : will this be ok ?
  • maintenance issue : upgrading the live site means upgrading all the db one by one
  • dev issue : setting a dev / pre prod / prod env will be quite hard
  • commom data will still require using mysql, so we would end end with 2 DB connections for each page, arg

More cons that pros, still, it makes me wonder (zepplin style).

What do you say ?

mysql sqlite architecture
Edit
Report

2 Answers

3

I came upon your post after the fact. I run a mediawiki hosting farm where each wiki has its own sqlite db. The main problem I see is that each database is being loaded independently and causes some memory hit. Besides that it works well for the backup processing and allows each wiki to have its own database. Since mediawiki wasn't set up to have multiple wikis, it meant that in mysql each wiki had to have its own set of tables with their own table prefixes. This really didn't work out, and sqlite was a great alternative.

If I were building a system from the ground up, I would have used mysql. Since I am using a prebuilt system and each user has their own silo, sqlite is working just fine.

answered 2009-12-03T22:18:05.337
0

You could look at CouchDB for massive concurrency.

answered 2009-02-27T20:15:14.197

Your Answer