[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

SQLite Pragmas as discussed on IRC.

From: Markus Schaber <m.schaber_at_3s-software.com>
Date: Wed, 22 Feb 2012 11:34:32 +0000


To preserve the results of the discussion just taking place on IRC, here my (personal) summary:

- There should be a config option to include additional SQL statements on SQLite database creation. This could be used for fine-tuning like "PRAGMA page_size". This could be for both client (wc.db) and server (rep_cache.db).

- "svnadmin verify" should issue a "PRAGMA integrity_check" or "PRAGMA quick_check" for the rep_cache.db, as it seems to be a likely candidate for breakage. Danielsh gave "+1" on that one.

- Maybe "svn cleanup" could issue "PRAGMA integrity_check" or "PRAGMA quick_check" on the wc.db.

- SQLite already reuses free space in the wc.db, it just does not give it back to the OS by truncating the file by default.

- Issuing a "VACUUM" on "svn cleanup" to release unused space and reduce fragmentation seems not worth the slowdown (the complete wc.db is rewritten).

- "PRAGMA auto_vacuum" is a database creation time property which makes SQLite release most of the free space back to the OS at every end of transaction. The cost for this is (potentially) higher fragmentation and slowdown (due to moving of pages in the database file and additional backpointer metadata). This makes me hesitate to enable it by default, but users who want it could still use the above mentioned config option. :-)

Best regards

Markus Schaber

We software Automation.
3S-Smart Software Solutions GmbH
Markus Schaber | Developer
Memminger Str. 151 | 87439 Kempten | Germany | Tel. +49-831-54031-0 | Fax +49-831-54031-50
Email: m.schaber@3s-software.com | Web: http://www.3s-software.com 
CoDeSys internet forum: http://forum.3s-software.com
Download CoDeSys sample projects: http://www.3s-software.com/index.shtml?sample_projects
Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 
Received on 2012-02-22 12:35:14 CET

This is an archived mail posted to the Subversion Dev mailing list.