Hello
I have upgraded our backup server from mysql 5.0.77 to mysql 5.5.15. We are using bacula
as backup software, and all the info from backups is stored in a mysql database. Today I
have upgraded from mysql 5.0 to 5.5 using IUS repository RPMS and with mysql_upgrade
procedure, no problem so far. This backup systems hold the bacula daemon, the mysql
server and the backup of other 100 systems (Solaris/Linux/Windows)
Our server has 6 GB of ram, 1 quad Intel Xeon E5520 and 46 TB of raid-6 SATA disks (7200
rpm) connected to a Smart Array P812 controller & Red Hat Enterprise Linux 5.7 x64.
Our mysql has dozens of millions of lines, and we are using InnoDB as storage engine for
bacula internal data. We add hundred of thousands lines /day to our mysql (files are
incrementally backed up daily from our 100 servers). So, we have a 7-8 concurrent writes
(in different lines, of course) , and theorically we only read from mysql when we restore
from backup.
Daily we launch a cron job that executes an "optimize table" in each table of our
database to compact the database. It takes almost an hour. We are going to increase the
memory of the server from 6 to 12 GB in a couple of weeks, and I will change my.cnf to
reflect more memory. My actual my.cnf is attached below:
These are my questions:
- We have real slow storage (raid 6 SATA), but plenty CPU and ram . Should I enable
innodb compression to make this mysql faster?
- This system is IOPS-constrained for mysql (fine for backup, though). Should I add a SSD
only to hold mysql data?
- Any additional setting I should use to tune this mysql server?
my.cnf content:
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
innodb_flush_method=O_DIRECT
max_connections = 15
wait_timeout = 86400
port = 3306
socket = /var/lib/mysql/mysql.sock
key_buffer = 100M
max_allowed_packet = 2M
table_cache = 2048
sort_buffer_size = 16M
read_buffer_size = 16M
read_rnd_buffer_size = 12M
myisam_sort_buffer_size = 384M
query_cache_type=1
query_cache_size=32M
thread_cache_size = 16
query_cache_size = 250M
thread_concurrency = 6
tmp_table_size = 1024M
max_heap_table = 1024M
skip-federated
innodb_buffer_pool_size= 2500M
innodb_additional_mem_pool_size = 32M
[mysqldump]
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 1250M
sort_buffer_size = 384M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 1250M
sort_buffer_size = 384M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
Regards
Maria
