Am 20.02.2008 um 14:03 schrieb Mark Gilbert:
> Folks.
>
> Our application uses SQlite on Mac OSX. It is a central data hub for
> a larger infrastructure and manages data coming in from some clients,
> and requests for data from other clients, using our own XML based
> protocols via TCPIP.
>
> Its somewhat like a web server with a backend system supplying data
> presented to web clients.
>
> All client access requests in and out of the system are made on
> individual threads. Our approach for SQLite has been to open the
> database on our main thread, begin a transaction, then using a
> locking mechanism, hand the database connection to threads in turn,
> then once every 60 seconds we flush the connection by ending the
> transaction then closing the DB(and making a backup copy) and finally
> reopening the database.
>
> Whilst this approach seems to work reliably, I am concerned that it
> may not be the most efficient model, and in some cases we are seeing
> very heavy load on our application, where this technique could
> benefit from some optimisations, since the database access becomes a
> bottleneck.
>
> I am interested to learn from other SQLite developers:
>
> - Is there a faster way to flush to disk and backup the database ?
I don't think you'll get any faster flushing than just closing the
transaction, however for backup you don't have to close the database
connection, just start an immediate transaction to lock database write
access, then backup the database file and rollback the transaction:
sqlite3_exec("BEGIN IMMEDIATE");
backup_database();
sqlite3_exec("ROLLBACK");
However, don't expect too much improvement - opening and closing
database connections are done in a fraction of seconds, so this will
not a major speed boost if you do this once every minute ;-)
>
> - We don't currently prepare SQL statements in advance, would this
> technique benefit from somehow preparing statements *before* that
> thread gets the lock on the database ? Can we have multiple threads
> using the SAME database connection preparing SQL Queries at the same
> time (so long as only one is RUNNING the query at once ?).
With the latest sqlite, I believe you can even have multiple threads
access the same database connection. In this case, access will still
be serialized.
However, you can also have each thread create it's own database
connection, then access the database concurrently (well, with
limitations - there can always only be one writer thread). In this
case you can actually execute several statements in parallel on
different threads (i.e. different database connections).
See <http://sqlite.org/lockingv3.html> and
<http://www.sqlite.org/cvstrac/wiki?p=MultiThreading
> for more details.
>
> Its tempting to consider using a multi-user database instead, but for
> the most part SQLite is doing a great job, and we don't really think
> the complexity of something like mySQL is warranted. All access to
> the database is made from multiple threads in one application.
>
> Any other suggestions on ways we could optimize the way we interact
> with SQLite ?
If the majority of your requests are read requests, I'd go with the
multiple connections in parallel approach. In that case you may have
to manually serialize the write access or handle SQLITE_BUSY errors
correctly.
If the majority of requests are write requests, parallelization won't
help a lot due to sqlite's limitation of a single writer.
HTH,
</jum>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
