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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
