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
