List:General Discussion« Previous MessageNext Message »
From:Suresh Kuna Date:September 15 2011 5:23am
Subject:Re: Question about slow storage and InnoDB compression
View as plain text  
" I am still benchmarking, but I see a 15-20% performance gain after
enabling compression using bacula gui (bat)."

This is a very good performance improvement and how much disk space did you
saved here ?

Can you do bench marking with 4kb and 8kb key_block_size as well to check
what suits your application. I saw there has been improvement in performance
by adjusting this one too.


On Wed, Sep 14, 2011 at 6:20 PM, Maria Arrea <maria_arrea@stripped> wrote:

> I have finally enabled compression:
>
>
>
> 
> +----------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+-----------------------------------------+---------+
>  | Name | Engine | Version | Row_format | Rows | Avg_row_length |
> Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
> Create_time | Update_time | Check_time | Collation | Checksum |
> Create_options | Comment |
>
> 
> +----------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+-----------------------------------------+---------+
>  | BaseFiles | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 16384 | 0 | 1
> | 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>  | CDImages | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | |
> 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>  | Client | InnoDB | 10 | Compressed | 46 | 356 | 16384 | 0 | 16384 | 0 |
> 53 | 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>  | Counters | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | |
> 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>  | Device | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | 1 |
> 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>  | File | InnoDB | 10 | Compressed | 106551231 | 129 | 13763608576 | 0 |
> 7449083904 | 7340032 | 516304137 | 2011-09-14 12:53:45 | | |
> latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
>  | FileSet | InnoDB | 10 | Compressed | 8 | 2048 | 16384 | 0 | 0 | 0 | 11 |
> 2011-09-14 11:26:17 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>  | Filename | InnoDB | 10 | Compressed | 39608549 | 62 | 2455764992 | 0 |
> 3063939072 | 4194304 | 49584798 | 2011-09-14 13:11:41 | | |
> latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
>  | Job | InnoDB | 10 | Compressed | 3499 | 454 | 1589248 | 0 | 212992 |
> 4194304 | 10200 | 2011-09-14 13:11:42 | | | latin1_swedish_ci | |
> row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
>  | JobHisto | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 |
> 0 | | 2011-09-14 11:42:30 | 2011-09-14 11:42:30 | 2011-09-14 11:42:30 |
> latin1_swedish_ci | | | |
>  | JobMedia | InnoDB | 10 | Compressed | 52788 | 69 | 3686400 | 0 | 2637824
> | 4194304 | 150064 | 2011-09-14 13:11:44 | | | latin1_swedish_ci | |
> row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
>  | Location | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | 1 |
> 2011-09-14 11:42:32 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>  | LocationLog | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024
> | 0 | 1 | 2011-09-14 11:42:32 | 2011-09-14 11:42:32 | | latin1_swedish_ci |
> | | |
>  | Log | InnoDB | 10 | Compressed | 31578 | 349 | 11026432 | 0 | 1589248 |
> 4194304 | 285940 | 2011-09-14 13:11:45 | | | latin1_swedish_ci | |
> row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
>  | Media | MyISAM | 10 | Dynamic | 39 | 142 | 5568 | 281474976710655 | 4096
> | 0 | 47 | 2011-09-14 11:42:33 | 2011-09-14 11:42:33 | 2011-09-14 11:42:33 |
> latin1_swedish_ci | | | |
>  | MediaType | InnoDB | 10 | Compressed | 1 | 16384 | 16384 | 0 | 0 | 0 | 2
> | 2011-09-14 11:42:33 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>  | Path | InnoDB | 10 | Compressed | 4681359 | 81 | 380452864 | 0 |
> 581959680 | 7340032 | 4527256 | 2011-09-14 13:13:23 | | | latin1_swedish_ci
> | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
>  | PathHierarchy | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 16384 | 0
> | | 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>  | PathVisibility | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 16384 |
> 0 | | 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>  | Pool | InnoDB | 10 | Compressed | 8 | 2048 | 16384 | 0 | 16384 | 0 | 9 |
> 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>  | Status | InnoDB | 10 | Compressed | 21 | 780 | 16384 | 0 | 0 | 0 | |
> 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>  | Storage | InnoDB | 10 | Compressed | 1 | 16384 | 16384 | 0 | 0 | 0 | 2 |
> 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>  | UnsavedFiles | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | 1
> | 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>  | Version | InnoDB | 10 | Compressed | 1 | 16384 | 16384 | 0 | 0 | 0 | |
> 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED
> KEY_BLOCK_SIZE=16 | |
>
> 
> +----------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+-----------------------------------------+---------+
>
>
>  I am still benchmarking, but I see a 15-20% performance gain after
> enabling compression using bacula gui (bat).
>
>  Regards
>
>  Maria
>
> ----- Original Message -----
> From: Maria Arrea
> Sent: 09/14/11 09:50 AM
> To: mysql@stripped
> Subject: Re: Question about slow storage and InnoDB compression
>
>  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.
>



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