We are currently using mysql. What is the comparison to opening a database with sqlite vs connecting to the daemon with mysql? Our current box has seen 300+ connections to the sql server at at once. Can we expect that having 300 databases open with sqlite wont be a problem?
SQLite and MySQL are at opposite ends of the spectrum when it comes to how multiple connections are managed. MySQL uses a central daemon that arbitrates all connections. By doing so, it can manage the connections on a relatively fine grained level, allowing multiple simultaneous readers and writers (as long as, I would assume, the writers aren't all scribbling on the same table). With this flexibility comes considerable complexity in that you have to administrate yet another service on the computer and manage connection information, etc...
SQLite takes the very simple approach of equating opening a database connection with opening a file. As such, it is extremely efficient in that no data has to pass "over the wire" and very simple in that there is no administrative overhead. There is significantly less overhead in opening a SQLite database file than there is in opening a client/server connection. Since there isn't a connection, there isn't really a notion of multiple connections either. Instead, SQLite allows multiple clients-- threads or processes-- to open the database file. SQLite arbitrates read/write access through the use of BSD level advisory locks.
As such, SQLite allows multiple simultaneous readers and only one writer. When a writer is actively writing to the database, it blocks all other readers.
Unless you redesign your application to not be focused on a multiple connection model, it is unlikely that you will see any benefit-- performance or otherwise-- to moving to SQLite.
I am assuming that your inquiry was related to performance issues that you are currently experiencing with MySQL? Can you give us an idea of how large of a working set (i.e. how much data is in active play, in general) you have, what the transaction rate is, and how large the overall data set is?
b.bum
