List:General Discussion« Previous MessageNext Message »
From:Maria Arrea Date:September 13 2011 3:36pm
Subject:Question about slow storage and InnoDB compression
View as plain text  
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

Thread
Question about slow storage and InnoDB compressionMaria Arrea13 Sep
  • Re: Question about slow storage and InnoDB compressionSuresh Kuna14 Sep
Re: Question about slow storage and InnoDB compressionSuresh Kuna14 Sep
Re: Question about slow storage and InnoDB compressionMaria Arrea14 Sep
  • Re: Question about slow storage and InnoDB compressionReindl Harald14 Sep
Re: Question about slow storage and InnoDB compressionMaria Arrea14 Sep
  • Re: Question about slow storage and InnoDB compressionReindl Harald14 Sep
  • Re: Question about slow storage and InnoDB compressionSuresh Kuna15 Sep