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.
-------------------------------------------------------------------------
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/bacula-users
