Hi at all, recently I got troubles with the Bacula's DB (mysql 5.0.77). Trying to restore a file from a very huge backup (500GB and 846532 files) the InnoDB occupies one core for many hours before to let me select the target files.
During these days I've builded index for File, Filename and Path, as described here: http://www.bacula.org/en/dev-manual/main/main/Catalog_Maintenance.html#SECTION004292000000000000000 Now I got this: mysql> show index from Filename; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Filename | 0 | PRIMARY | 1 | FilenameId | A | 1560215 | NULL | NULL | | BTREE | | | Filename | 1 | Name | 1 | Name | A | 1560215 | 255 | NULL | | BTREE | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) mysql> show index from Path; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Path | 0 | PRIMARY | 1 | PathId | A | 194046 | NULL | NULL | | BTREE | | | Path | 1 | Path | 1 | Path | A | 194046 | 255 | NULL | | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) mysql> show index 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 | 39079621 | NULL | NULL | | BTREE | | | File | 1 | JobId | 1 | JobId | A | 18 | NULL | NULL | | BTREE | | | File | 1 | JobId_2 | 1 | JobId | A | 18 | NULL | NULL | | BTREE | | | File | 1 | JobId_2 | 2 | PathId | A | 2605308 | NULL | NULL | | BTREE | | | File | 1 | JobId_2 | 3 | FilenameId | A | 39079621 | NULL | NULL | | BTREE | | | File | 1 | idxPIchk | 1 | PathId | A | 167006 | NULL | NULL | | BTREE | | | File | 1 | file_jobid_idx | 1 | JobId | A | 18 | NULL | NULL | | BTREE | | | File | 1 | file_jpf_idx | 1 | JobId | A | 18 | NULL | NULL | | BTREE | | | File | 1 | file_jpf_idx | 2 | PathId | A | 4884952 | NULL | NULL | | BTREE | | | File | 1 | file_jpf_idx | 3 | FilenameId | A | 39079621 | NULL | NULL | | BTREE | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 10 rows in set (0.37 sec) mysql> Is it correct? Beside the index creation, my query still take much time, so I've tried to substitute the my.cnf config file with this one: [client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 8 datadir=/var/lib/mysql user=mysql old_passwords=1 innodb_buffer_pool_size = 1G skip-federated log-bin=mysql-bin server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid I got an HP Proliant DL360 G5, 4 CPU, 6GB of RAM: this server runs bacula-dir, mysql and nothing else. Where's my mistake? Could you suggest me some optimization. Thanks in advance. Simone Martina -- 01010011 01011001 01010011 Skylogic a EutelSat Company -- This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the Company. No employee or agent is authorized to conclude any binding agreement on behalf of this Company nor, through this latter, any of the Eutelsat Communication group with another party by email without express written confirmation by a duly authorized officer of the Company. The list of duly authorized officers and the scope of their powers is published on the Trade Register according to the national law of each affiliate. ------------------------------------------------------------------------------ Get 100% visibility into Java/.NET code with AppDynamics Lite! It's a free troubleshooting tool designed for production. Get down to code-level detail for bottlenecks, with <2% overhead. Download for free and get started troubleshooting in minutes. http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
