#acl PaulHowarth:read,write,admin,revert,delete All:read === Monday 19th April 2010 === ==== Local Packages ==== * Updated `perl-DBM-Deep` to 1.0021, dropping the upstreamed test suite and POD spelling patches * Updated `perl-Test-MinimumVersion` to 0.101080 (more "comparison to `undef`" bugs fixed) * Updated `spamass-milter` to fix a regression introduced with the update for [[CVE:2010-1132|CVE-2010-1132]] in which the newly-added `popenv()` function spawned new processes that were never reaped, resulting in eventual resource starvation due to all of the zombie processes ([[RedHatBugzilla:583523|Bug #583523]], [[http://bugs.debian.org/575019|Debian Bug #575019]]) ==== Fedora Project ==== * Updated `spamass-milter` as per my local package ==== Bacula Database Check ==== Yesterday's scheduled backup didn't happen automatically so I decided to kick it off manually, which worked fine. The backup database seems to be growing at quite a rate and little, if anything, seems to be being pruned. Given this background, I decided to give `bacula`'s `dbcheck` database consistency check utility a whirl; I've not run this before even though I've been running `bacula` for years. Anyway, I fired it up like this: {{{ # dbcheck -c /etc/bacula/bacula-dir.conf -v -b -f }}} When it got to checking for orphaned `Path` entries it just seemed to hang, with one of the server's CPUs running `mysqld` at 100% for the best part of a day before I decided to kill `dbcheck` and restart `mysqld`. Googling around, it seemed that adding some indexes to some of the MySQL tables would improve things, so I did: {{{ # mysql -u root --password='my-secret-password' bacula Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.1.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE INDEX file_pathid_idx on File(PathId); mysql> CREATE INDEX file_filenameid_idx on File(FilenameId); mysql> CREATE INDEX job_filesetid_idx on Job(FileSetId); mysql> CREATE INDEX job_clientid_idx on Job(ClientId); mysql> quit }}} The first of these index creation tasks also seemed to be taking forever, but not taking up any CPU time. I noticed in this morning's `logwatch` report that `/var/lib/mysql` had reached 100% and it dawned on me that `mysqld` was probably waiting on disk space being available rather than being busy creating the index. I increased the size of `/var/lib/mysql` using `lvextend` and `resize2fs` and the index creation completed shortly afterwards. The remaining indexes were created in a matter of minutes for `File(FilenameId)` and seconds for the others. I then re-ran the `dbcheck` command and it ran interactively with virtually no delays of note, deleting over a million orphaned records. We'll see what effect, if any, that has on the catalog backup next time it runs. '''Update''' The catalog backup was not noticeably smaller after deleting the orphaned records. After updating to `bacula` 5.0.x, restores became horrendously slow, in particular the ''Building directory tree'' phase. It turned out that [[http://www.backupcentral.com/phpBB2/two-way-mirrors-of-external-mailing-lists-3/bacula-25/bacula-5-0-1-and-db-issues-please-share-your-experience-105156/|this was due to the extra indexes]]. Dropping the indexes on the `File` table again improved things to how they were with `bacula` 3.0.x: {{{ # mysql -u root --password='my-secret-password' bacula Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.1.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show indexes from File; +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | File | 0 | PRIMARY | 1 | FileId | A | 17596256 | NULL | NULL | | BTREE | | | File | 1 | file_pathid_idx | 1 | PathId | A | 144231 | NULL | NULL | | BTREE | | | File | 1 | file_filenameid_idx | 1 | FilenameId | A | 977569 | NULL | NULL | | BTREE | | | File | 1 | file_jobid_idx | 1 | JobId | A | 411 | NULL | NULL | | BTREE | | | File | 1 | file_jpf_idx | 1 | JobId | A | 411 | NULL | NULL | | BTREE | | | File | 1 | file_jpf_idx | 2 | FilenameId | A | 8798128 | NULL | NULL | | BTREE | | | File | 1 | file_jpf_idx | 3 | PathId | A | 17596256 | NULL | NULL | | BTREE | | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 7 rows in set (0.00 sec) mysql> drop index file_pathid_idx on File; drop index file_filenameid_idx on File; Query OK, 17596256 rows affected (2 min 26.90 sec) Records: 17596256 Duplicates: 0 Warnings: 0 mysql> drop index file_filenameid_idx on File; Query OK, 17596256 rows affected (2 min 26.48 sec) Records: 17596256 Duplicates: 0 Warnings: 0 mysql> show indexes from File; +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | File | 0 | PRIMARY | 1 | FileId | A | 17596256 | NULL | NULL | | BTREE | | | File | 1 | file_jobid_idx | 1 | JobId | A | 411 | NULL | NULL | | BTREE | | | File | 1 | file_jpf_idx | 1 | JobId | A | 411 | NULL | NULL | | BTREE | | | File | 1 | file_jpf_idx | 2 | FilenameId | A | 8798128 | NULL | NULL | | BTREE | | | File | 1 | file_jpf_idx | 3 | PathId | A | 17596256 | NULL | NULL | | BTREE | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 5 rows in set (0.00 sec) mysql> quit }}} So next time I want to run `dbcheck`, I'll add the indexes back and then remove them again afterwards. ----