List:General Discussion« Previous MessageNext Message »
From:Maria Arrea Date:September 14 2011 7:50am
Subject:Re: Question about slow storage and InnoDB compression
View as plain text  
The server hosting bacula and the database only has one kind of disk: SATA, maybe I should
buy a couple of SSD for mysql.

 I have read all your mails, and still not sure if I should enable innodb compression. My
ibfile is 50 GB, though.

 Regards

 Maria


 Questions:
 1) Why are you putting your MySQL data on the same volume as your Bacula backups? Bacula
does large sequential I/O and MySQL will do random I/O based on teh structure.





 What you want to do is:

 1) you have 5MB InnoDB Log Files, that's a major bottleneck. I would use at 256MB or
512MB x 2 InnoDB log files.
 2) dump and import the database using innodb_file_per_table so that optimization will
free up space..
 3) are you running Bacula on the server as well? If so, decrease the buffer pool to
1-2GB.. if not bump it up to to 3GB as you need some memory for bacula

 and 4, this is the most important one:
 How big is your MySQL data? Its not that big, I figure in the 80-100GB range. Get
yourself a pair of 240GB SSDs, mount it locally for MySQL.

 S



 On Tue, Sep 13, 2011 at 21:19, Suresh Kuna < sureshkumarilu@stripped > wrote:
 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
 -- The best compliment you could give Pythian for our service is a referral.

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