List:General Discussion« Previous MessageNext Message »
From:Suresh Kuna Date:September 14 2011 1:19am
Subject:Re: Question about slow storage and InnoDB compression
View as plain text  
I would recommend to go for a 15K rpm SSD raid-10 to keep the mysql data and
add the Barracuda file format with innodb file per table settings, 3 to 4 GB
of innodb buffer pool depending the ratio of myisam v/s innodb in your db.
Check the current stats and reduce the tmp and heap table size to a lower
value, and reduce the remaining buffer's and cache as well.

On Tue, Sep 13, 2011 at 9:06 PM, Maria Arrea <maria_arrea@stripped> wrote:

> 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
>



-- 
Thanks
Suresh Kuna
MySQL DBA

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