1. Xerial
2. A year or so, relatively heavily.
3.
* MAX_MMAP_SIZE is too small by a few hundred megabytes. It's safe to
set to around a terabyte.
* No way to effectively use multiple threads, even on a read-only
mmap'd database.
* Planner sometimes misses plans - does it need better STAT features
enabled?
4. What immediately comes to mind:
For writing:
* JournalMode MEMORY
* SynchronousMode OFF
* A couple of hundred meg of page cache
* Use quite big transactions, but it doesn't really matter where the
boundaries are. You can do 50K+ writes per transaction.
* Use Java-side locks.
* Any data which doesn't need to be accessible to the relational
engine should be serialized into a BLOB with Kryo and Snappy. Even
simple text fields.
For reading:
* mmap EVERYTHING
* JournalMode OFF
* It's MUCH faster to join in Java than in sqlite. If you're loading
data into memory, do an application-side hash-join.
* Wrap all your queries in a performance rig and log slow queries.
* Force the planner using the CROSS keyword for any query which is
ever slow.
* Sometimes the planner changes the query plan based on e.g. the size
of an IN-list.
* Use covering indexes, and use sqlite_analyzer to figure out which
ones are winning a space-time trade-off.
* The partial covering index optimizer doesn't always account for
constants, and we lose a bunch of space because we have to include the
constant values in the index.
Overall:
* Does anyone respond to issues?
We actually use nondeterministic transaction boundaries for writing
because it really doesn't matter where they are, it only matters that
you have one.
S.
On 04/18/2018 08:34 PM, Richard Hipp wrote:
Are you using SQLite with JDBC? If so, can you please answer a few
questions below?
You can post on this mailing list or send your reply directly to me.
1. Which JDBC are you using?
2. For how long have you been using it?
3. What issues you had with this driver?
4. What advise do you have for avoiding problems in this driver?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
