On Friday 29 December 2006 02:02, Kern Sibbald wrote:
> On Friday 29 December 2006 06:19, John Jorgensen wrote:
> > Seeing this discussion about SQLite3 performance has finally
> > prompted me to share an observation that I made about a year ago,
> > when building bacula together with sqlite-3.3.6 for personal use
> > on my home machine.
> >
> > I believe that the "default_synchronous" PRAGMA that Kern
> > mentions in his reply has not been included in sqlite 3.*
> >
> > The "PRAGMA synchronous = ..." pragma still exists, but its
> > effect does not persist. I.e., you need to re-issue the pragma
> > every time you connect to the database, rather than specifying it
> > once when creating the database.
> >
> > So I put together a patch that adds to bacula's configure script
> > an option to specify a snippet of SQL code to execute on every
> > database connection, like this:
> >
> > configure ... --enable-extra-sqlite3-init="pragma synchronous=0;"
> > ...
> >
> > with the result that bacula-dir is compiled to issue that SQL
> > pragma every time it connects to the database.
> >
> > Since you can get the same effect by using an "sqlquery" in
> > bconsole to issue the pragma before running the job manually, it
> > could be that the proper way to accomplish what I did is by using
> > the python interpreter (which I have never tried using) to script
> > the sqlquery into every job, instead of by modifying the
> > bacula-dir binary.
> >
> > My experience is that
> >
> > PRAGMA synchronous = 0
> >
> > greatly improves performance, although turning off synchronous
> > writes completely like this demands either a reliable UPS or the
> > willingness to rebuild your catalog should the power fail in the
> > middle of a backup. "PRAGMA synchronous = 1" ("NORMAL") doesn't
> > help nearly as much, at least not on my machine. (The default
> > value for synchronous is 2, "FULL").
> >
> > I'll try to attach my patch, but I made it against the 1.38.11 sources,
> > so it won't be directly applicable to the current
> > development sources (and it includes some changes that aren't
> > strictly necessary to do the sqlite initialization, but which
> > reduced the warnings I was getting from the autoconf/automake
> > tool chain).
> >
> > >>>>> "kern" == Kern Sibbald <[EMAIL PROTECTED]> writes:
> > >> Is anyone out there using SQLite3? Does anyone have any
> > >> suggestions
>
> on how
>
> > >> we could change the default build defines or database creation
>
> scripts to
>
> > >> improve the performance?
> > >>
> > >>
> > >>
> > >> If there are any suggestions I'd appreciate getting them quickly
> > >> so
>
> that we
>
> > >> can incorporate them into 1.40.
> >
> > kern> When SQLite3 was first released, I looked into this and seems
> > to
>
> me that he
>
> > kern> handled synchronization differently. The two pragmas that I
> > saw
>
> (long ago)
>
> > kern> that made a difference were:
> >
> > kern> PRAGMA default_synchronous
> > kern> PRAGMA default_cache_size
>
> Thanks for the research and feedback.
>
> Could you look to see if there is some way to set the synchronous value to
> a default? I cannot imagine that the author of SQLite would not provide
> some way to do so.
>
> I did notice that the settings of the synchronous flag changed in 3.0, but
> after looking at the consequences of changing them from the default set by
> the author, I decided to leave that to the user (if I remember right).
>
> If there is absolutely no way to set the default value, then I would
> certainly considering code as you are suggesting, but if a way to set the
> default exists, as I am 99% sure there is, then I would much prefer to
> leave it to the user to choose the degree of risk he/she wants to take and
> to manage it directly with the database.
Kern,
This thread finally prompted me to do some research, since have been using
sqlite in production.
>From the Sqlite home page, documentation, Pragma command syntax:
PRAGMA default_synchronous;
This pragma was available in version 2.8 but was removed in version 3.0. It is
a dangerous pragma whose use is discouraged. To help dissuide users of
version 2.8 from employing this pragma, the documentation will not tell you
what it does.
For reference, from the documentation which came with my version of sqlite,
2.8.16:
PRAGMA default_synchronous;
PRAGMA default_synchronous = FULL; (2)
PRAGMA default_synchronous = NORMAL; (1)
PRAGMA default_synchronous = OFF; (0)
This pragma changes the synchronous mode persistently. Once changed, the mode
stays as set even if the database is closed and reopened. The synchronous
pragma does the same thing but only applies the setting to the current
session.
So, I'm not sure if this means that the default command does not exist in 3.x
or just that it is not documented. I'll try & search further as I have the
time.
HTH!
cmr
--
Debian 'Etch': Registered Linux User #241964
----
"More laws, less justice." -- Marcus Tullius Ciceroca, 42 BC
--------
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
