\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:
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
\normalsize
-\subsection{SQLite Indexes}
-On SQLite, you can check if you have the proper indexes by:
-
-\footnotesize
-\begin{verbatim}
-sqlite <path>/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 <path>/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 }
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}
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.
projects / bacula / docs / blobdiff