Data compression

Data compression not only significantly reduces the storage required for the database, but also improves throughput by reducing the I/O workload, at a modest cost in processing overhead. The storage cost savings can be important, but the reduction in I/O costs can be even more valuable. There are three possible ways to achieve that

1. Use compress/uncompress function in the database
http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_compress

2. Innodb-plugin offers table compression
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-compression-background.html

3. Compress/uncompress data in client not in the database. This will offload more stuff – CPU and network. . This approach uses CPU cycles for compression and uncompression on the client machine rather than the database server, which might be appropriate for a distributed application with many clients, or where the client machine has spare CPU cycles.

We performed benchmarks (using table structre mentioned on my previous post: binary-log-synchronization) to evaluate the impact of using above mentioned data compression techniques (1 & 2 only), test data  was loaded using 8 parallel database sessions  into original table, compressed table and as well as into original table but using compress function to compress "content" column.

Table Format	Time to load 80k records	Data file size
Original Table	0m57.515s	1.6GB
Compressed Table: ROW_FORMAT =COMPRESSED	1m9.613s	648MB
Original Table using compress() function	0m35.883s	156MB

The results show that inserting data in compressed format (i.e. using compress() function) is the fastest approach and it gives better compression than using innodb compressed table. Using compress() function we have the control to compress only selected columns whereas innodb compressed table compress the entire table.

We also perfomed benchmarks to read data from original table, compressed table and original table but using uncompress() function (to uncompress compressed data), total 800k lookups were perfomed using id=<>

Table	Time to perform 800k lookups
Compressed Table: row_format= compressed	1m26.426s
Original Table using uncompress() function	1m26.197s
Original table	6m29.504s

The test results show that lookups performed againts compressed tables are at least 6 times faster than original table. The reason for which is that compressed table is much smaller than original table thus most of the data can fit into memory and hence reducing disk I/Os.

upgrading to innodb plugin

As of MySQL 5.1.38, the InnoDB Plugin is included in MySQL 5.1 releases, in addition to the built-in version of InnoDB that has been included in previous releases. So all we need to do is just enable it, that is, add following options into my.cnf file

[mysqld]
..
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so

for further readings visit here http://dev.mysql.com/doc/refman/5.1/en/news-5-1-38.html

Assuming you have succesfully enabled Innodb Plugin, now what? would you automatically get benefits from all new Innodb plugin features?
The answer is yes/no, because Innodb Plugin supports new file format 'Barracuda', which introduces two such new data structures: compressed
, and long variable-length columns stored off-page. By default, the InnoDB Plugin does not create tables in a format that is incompatible with the built-in InnoDB in MySQL.
This means you won't get these two features automatically until you create new tables using new file format or convert exisiting tables to new format.

Innodb Plugin offers lots of other features too and these features are enabled by default, also you will automatically get benefit from these features such as fast index creation and lots of performance and scalability enhancments,
for full details click here
here http://www.innodb.com/doc/innodb_plugin-1.0/innodb-performance.html

INNODB Full Table Scan

Full table scan is faster on MyISAM tables but it has been discourged to use with INNODB tables, the reason for which is that innodb buffer pool is not table scan resistant. A full table scan on INNODB can have the affects of evicting a large portion of the useful pages out of the INNODB buffer pool . However there are times when it is desirable to fill the buffer pool with full table scans. On other hand, if you use mysqldump to backup INNODB tables then you have experienced performance issues. Because it perfroms full table scans and thus blow out buffer pool cache.
INNOBASE has offered fix for it in innodb plugin 1.0.4

1) innodb_old_blocks_time
2) innodb_old_blocks_pct

1) Just before performing table scans and dumps to prevent eviction of a large portion of usefull pages from buffer pool. You can set this variable at runtime

mysql>SET GLOBAL innodb_old_blocks_time=1000;
.. perfrom table scans
mysql> SET GLOBAL inndob_old_blocks_time=0;


2) Similarly we can decide how much space should be available for old blocks, the default value for which is 37% (approximatley i.e. 3/8 of the pool). This behavior can be controlled with innodb_old_blocks_pct runtime variable. The value for this variable should be altered carefully, as you might have lots of unwanted old pages in buffer pool.

Solaris- InnoDB Plugin From Source

Since there isn't any binary innodb plugin available for Solaris10. So, I decided to share my experience of building innodb plugin from source on Solaris 10.


Assumption

1. Readers are assumed to have at least some basic Unix/Linux administrative skills.
2. Readers are assumed to have running copy of Solaris 10.
3. Readers are assumed to have backed up any critical data before the installation.

1. 1. Get the latest innodb plugin
http://www.innodb.com/innodb_plugin/download/

2. Get the latest MySQL 5.1 sources from
http://dev.mysql.com/downloads/mysql/5.1.html#source

3. Replace the contents of the mysql-5.1.N/storage/innobase/ directory
with the contents of this directory.
shell> cd mysql-5.1.N/storage
shell> mv innobase innobase-default
shell> tar xzf /path/to/innodb_plugin-1.0.N.tar.gz
shell> mv innodb_plugin-1.0.N innobase

4. Compile MySQL using the following configure line.
Note: * This uses Sun Studio Express (not gcc)
* --prefix=/opt/mysql/5.1.N is used, but you can change the
directories if you like. Also --localstatedir and --libexecdir
* Maybe it's good to change the --with-comment and
--with-server-suffix to say that this is your build using
the InnoDB Plugin.

5. Add the flags

CC=/path/to/SunStudioExpress/bin/cc

CXX=/path/to/SunStudioExpress/bin/CC

CFLAGS="-g -xO2 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=generic"

CXXFLAGS="-g -xO2 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all -features=no%except -xlibmil -xlibmopt -xtarget=generic"

6. export CC CXX CFLAGS CXXFLAGS

7. Optionally
make clean

8.

./configure --prefix=$PREFIX --localstatedir=$PREFIX/data \ --libexecdir=$PREFIX/bin \ --with-comment="MySQL Server with InnoDB Plugin" \ --with-server-suffix="-innodb-plugin" \ --enable-thread-safe-client --enable-local-infile \ --with-mysqld-libs=-lmtmalloc \ --with-pic --with-client-ldflags="-static" \ --with-mysqld-ldflags="-static" --with-zlib-dir=bundled \ --with-big-tables --with-ssl \ --with-plugins=partition,archive,blackhole,csv,federated,heap,innobase,myisam \ --with-extra-charsets=complex

9. Make

10. Make install.