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 < sureshkumar...@gmail.com > 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_ar...@gmx.com > 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.
