Hello everyone, here we go for a new blog post and now treating about InnoDB inside MySQL 5.5 and as you should now, this new version uses InnoDB Plugin version 1.1 which one has a lot of new adjustable resources. What most caught my attention was the impressed way that users could adjust it to have a lot more performance than MyISAM, for example. It was benchmarked here.

At the beginning, when Oracle announced about the new default Storage Engine (InnoDB, bingo!), many users were scared and started to ask about why is that change really necessary. Many other users just nod heads for it and now we have a good proof of this necessity – to have more scale, securityand reliability.

Scale [Up] because the data could be compressed to use more memory and processor than disk (it avoid overhead), more transactions could be started concurrently and more CPU cores can be addressed as MySQL 5.5.4 is better prepared now to scale up to 32 cores. You can read about it accessing DimitriK’s (dim) Weblog.

Security is noted when you compare InnoDB with MyISAM because with InnoDB you will have good performance with safe and crash recovery, using transactions logs and data and indexes inside a tablespace, what will improve besides security, availability too.

I remember you to use new file format (innodb_file_format) configured as Barracuda in order to provide all new functionality to your environment. Unlike MyISAM, InnoDB has its own transaction logs which by default are created inside DATADIR (normally at /var/lib/mysql). If you specify no InnoDB configuration options, an auto-extending 10MB datafile named `ibdata1'and two 5MB log files named `ib_logfile0' and `ib_logfile1' in the MySQL data directory – DATADIR. As long as a transaction receives a COMMIT or a ROLLBACK, a checkpoint is created, transaction is registered or rolled back and the life goes on.

The InnoDB’s behavior at this point will depend on some interesting configurations: innodb_log_buffer_size (to maintain transactional data into the log buffer), innodb_max_dirty_pages_pct (the percentage of dirty pages can remain into the buffer pool), innodb_flush_log_at_trx_commit (the way data will be flushed to disc, it accepts values from 0 to 2), and innodb_flush_method, that may decide how to open files and flush all dirty pages have been modified since last flush process.

The InnoDB parameterization have been showing that we have lots of combination to have more performance in certain conditions.  When you’re about to restore large databases, it’s good to have innodb_flush_method=O_DSYNC although it will increase swap as much as possible. To have good performance on restoring a database make sure to disable unique and foreign key checks, configure autocommit appropriately and create a backup with “-e” option (when use mysqldump).

You can use the following variables to handle InnoDB configuration and behavior:

[mysqld]

# innodb file new features configuration
innodb_file_per_table = BARRACUDA # it will "turn on" all InnoDB Plugin new features
innodb_file_per_table = 1 # it will "turn on" a tablespace file per database table

# innodb log file configuration
innodb_log_group_home_dir=/var/log/mysql/innodb # where files will end up
innodb_log_files_in_group=8 # the amount of log files current instance will have
innodb_log_file_size=500M # the total of innodb_files_in_group * innodb_log_file_size can't be greater than or equal to 4096M - 4G

# innodb log buffer configuration - tinkling about a circle per created log file before flushing process
innodb_log_buffer_size=1024M # considering an environments with large transactions, making this variable large will save disk I/O, click here to know more how to calculate it better
innodb_flush_method=O_DIRECT # avoid OS Buffer Cache and too much RAM dedicated to it
#

Reliability cause these all features together to deliver good set of subsystems to have good performance, what can be achieved using innodb_file_per_table to create a tablespace file per table, less I/O in this case, the capacity to compress data using less space into tablespace segments, expends less extents and fit memory with more data.

A good touch, on broad terms is that InnoDB can be configured to use external disks as SAN or other machines to storage its structure and data. Using certain variables you can, for example, put InnoDB files on another disks to get more performance. These below variables will become it possible:

[mysqld]
innodb_data_home_dir  = /nfs1/innodb
innodb_data_file_path = /ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend

Following good practices, it is really important whether you can to separate data and transaction logs onto different disks.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.