Hello,
For the number of files in your backup, it is clear to me that either
you are running with modified indexes on your catalog (unlikely) or
your MySQL is not properly tuned. I am not a MySQL expert, but from
what you show below, the memory sizes you are giving it are probably
way too small. Be careful though tuning a DB requires experience or
at least getting advice, because giving too much memory to some of
the parameters can cause as many or more problems than not giving
enough.
As one user suggested, it would be best to upgrade to a newer
MySQL version, and to use one of the bigger MySQL sample conf
(my.cnf) files.
By the way, here with a machine not much bigger than yours I can
load in 10 Million files in less than a minute (if I remember right).
I'm using a nicely tuned Postgres DB though ...
Regards,
Kern
PS: If you do decide to switch to Postgres, which I am not sure I would
recommend until you try a my.cnf more suited to your needs, I recommend
at least version 8.4, but *far* better 9.0 or 9.1.
On 08/14/2013 07:31 PM, Simone Martina wrote:
> 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
------------------------------------------------------------------------------
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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
