X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=docs%2Fmanuals%2Fen%2Fmain%2Fcatmaintenance.tex;fp=docs%2Fmanuals%2Fen%2Fmain%2Fcatmaintenance.tex;h=89500f1e7212e06e4e3b28842717aa1377848c13;hb=5609d1d500b51fb41a957c047ccb4d79db7aa8d4;hp=0b8f2bb43287908c3156ab5856e9e6d11be1b7b6;hpb=15e1ddc947744de053322dac28a0fa6782f264aa;p=bacula%2Fdocs diff --git a/docs/manuals/en/main/catmaintenance.tex b/docs/manuals/en/main/catmaintenance.tex index 0b8f2bb4..89500f1e 100644 --- a/docs/manuals/en/main/catmaintenance.tex +++ b/docs/manuals/en/main/catmaintenance.tex @@ -105,19 +105,19 @@ time you run a Job. \index[general]{Database!Compacting Your MySQL } \index[general]{Compacting Your MySQL Database } -Over time, as noted above, your database will tend to grow. I've noticed that -even though Bacula regularly prunes files, {\bf MySQL} does not effectively -use the space, and instead continues growing. To avoid this, from time to -time, you must compact your database. Normally, large commercial database such -as Oracle have commands that will compact a database to reclaim wasted file -space. MySQL has the {\bf OPTIMIZE TABLE} command that you can use, and SQLite -version 2.8.4 and greater has the {\bf VACUUM} command. We leave it to you to -explore the utility of the {\bf OPTIMIZE TABLE} command in MySQL. +Over time, as noted above, your database will tend to grow. I've noticed +that even though Bacula regularly prunes files, {\bf MySQL} does not +effectively use the space, and instead continues growing. To avoid this, +from time to time, you must compact your database. Normally, large +commercial database such as Oracle have commands that will compact a +database to reclaim wasted file space. MySQL has the {\bf OPTIMIZE TABLE} +command that you can use. We leave it to you to explore the utility of the +{\bf OPTIMIZE TABLE} command in MySQL. All database programs have some means of writing the database out in ASCII format and then reloading it. Doing so will re-create the database from scratch producing a compacted result, so below, we show you how you can do -this for MySQL, PostgreSQL and SQLite. +this for MySQL and PostgreSQL. For a {\bf MySQL} database, you could write the Bacula database as an ASCII file (bacula.sql) then reload it by doing the following: @@ -324,13 +324,6 @@ You may obtain significant performances by switching to the my-large.cnf or my-huge.cnf files that come with the MySQL source code. -For SQLite3, one significant factor in improving the performance is -to ensure that there is a "PRAGMA synchronous = NORMAL;" statement. -This reduces the number of times that the database flushes the in memory -cache to disk. There are other settings for this PRAGMA that can -give even further performance improvements at the risk of a database -corruption if your system crashes. - For PostgreSQL, you might want to consider turning fsync off. Of course doing so can cause corrupted databases in the event of a machine crash. There are many different ways that you can tune PostgreSQL, the @@ -442,29 +435,6 @@ CREATE INDEX Name on Filename (Name(255)); \normalsize -\subsection{SQLite Indexes} -On SQLite, you can check if you have the proper indexes by: - -\footnotesize -\begin{verbatim} -sqlite /bacula.db -select * from sqlite_master where type='index' and tbl_name='File'; -\end{verbatim} -\normalsize - -If the indexes are not present, especially the JobId index, you can -create them with the following commands: - -\footnotesize -\begin{verbatim} -sqlite /bacula.db -CREATE INDEX file_jobid_idx on File (JobId); -CREATE INDEX file_jfp_idx on File (JobId, PathId, FilenameId); -\end{verbatim} -\normalsize - - - \label{CompactingPostgres} \section{Compacting Your PostgreSQL Database} \index[general]{Database!Compacting Your PostgreSQL } @@ -509,51 +479,19 @@ Finally, you might want to look at the PostgreSQL documentation on this subject at \elink{http://www.postgresql.org/docs/8.1/interactive/maintenance.html}{http://www.postgresql.org/docs/8.1/interactive/maintenance.html}. -\section{Compacting Your SQLite Database} -\index[general]{Compacting Your SQLite Database } -\index[general]{Database!Compacting Your SQLite } - -First please read the previous section that explains why it is necessary to -compress a database. SQLite version 2.8.4 and greater have the {\bf Vacuum} -command for compacting the database. - -\footnotesize -\begin{verbatim} -cd {\bf working-directory} -echo 'vacuum;' | sqlite bacula.db -\end{verbatim} -\normalsize - -As an alternative, you can use the following commands, adapted to your system: - - -\footnotesize -\begin{verbatim} -cd {\bf working-directory} -echo '.dump' | sqlite bacula.db > bacula.sql -rm -f bacula.db -sqlite bacula.db < bacula.sql -rm -f bacula.sql -\end{verbatim} -\normalsize - -Where {\bf working-directory} is the directory that you specified in the -Director's configuration file. Note, in the case of SQLite, it is necessary to -completely delete (rm) the old database before creating a new compressed -version. - \section{Migrating from SQLite to MySQL or PostgreSQL} \index[general]{MySQL!Migrating from SQLite to } \index[general]{Migrating from SQLite to MySQL or PostgreSQL} -You may begin using Bacula with SQLite then later find that you want to switch -to MySQL or Postgres for any of a number of reasons: SQLite tends to use more -disk than MySQL; when the database is corrupted it is often more catastrophic -than with MySQL or PostgreSQL. Several users have succeeded in converting by -exporting the SQLite data and then processing it with Perl scripts prior to -putting it into MySQL or PostgreSQL. This is, however, not a simple process. -Scripts are available on bacula source distribution under -\texttt{examples/database}. +On some older Bacula you may begun using Bacula with SQLite then later find +that you want to switch to MySQL or Postgres for any of a number of +reasons: SQLite is no longer supported by Bacula; SQLite tends to use more +disk than MySQL; when the database is corrupted it is often more +catastrophic than with MySQL or PostgreSQL. Several users have succeeded in +converting by exporting the SQLite data and then processing it with Perl +scripts prior to putting it into MySQL or PostgreSQL. This is, however, not +a simple process. Scripts are available on bacula source distribution +under \texttt{examples/database}. \label{BackingUpBacula} \section{Backing Up Your Bacula Database} @@ -768,11 +706,3 @@ for each File that is saved, the database grows by approximately 150 bytes. This database has a total size of approximately 450 Megabytes. -If we were using SQLite, the determination of the total database size would be -much easier since it is a single file, but we would have less insight to the -size of the individual tables as we have in this case. - -Note, SQLite databases may be as much as 50\% larger than MySQL databases due -to the fact that all data is stored as ASCII strings. That is even binary -integers are stored as ASCII strings, and this seems to increase the space -needed.