mysql innodb note

from http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html

1.InnoDB is a high-reliability and high-performance storage engine for MySQL. Starting with MySQL 5.5, it is the default MySQL storage engine.

2.Key advantages of InnoDB include:
       a.Its design follows the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.

       b.Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance.
 
      c.InnoDB tables arrange your data on disk to optimize common queries based on primary keys. Each InnoDB table has a primary key index called the clustered index that organizes the data to minimize I/O for primary key lookups.

      d.To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.

      e.You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement. For example, you can use a join operation to combine data from InnoDB and MEMORY tables in a single query.

      f.The InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions). This is different from, for example, MyISAM tables where each table is stored using separate files. InnoDB tables can be very large even on operating systems where file size is limited to 2GB.


3.Best Practices for InnoDB Tables
      a.Specify a primary key for every table using the most frequently queried column or columns, or an auto-increment value if there isn't an obvious primary key.

      b.Embrace the idea of joins, For fast join performance, define foreign keys on the join columns, and declare those columns with the same datatype in each table.

      c.Turn off autocommit. Bracket sets of related changes, logical units of work, with START TRANSACTION and COMMIT statements.

     d.Stop using LOCK TABLE statements. InnoDB can handle multiple sessions all reading and writing to the same table at once, without sacrificing reliability or high performance.

     e.Enable the innodb_file_per_table option to put the data and indexes for individual tables into separate files, instead of in a single giant system tablespace. (This setting is required to use some of the other features, such as table compression and fast truncation.)

     f.Evaluate whether your data and access patterns benefit from the new InnoDB table compression feature (ROW_FORMAT=COMPRESSED on the CREATE TABLE statement. You can compress InnoDB tables without sacrificing read/write capability.

    g.Run your server with the option --sql_mode=NO_ENGINE_SUBSTITUTION to prevent tables being created with a different storage engine if there is an issue with the one specified in the ENGINE= clause of CREATE TABLE.


4.Configuring InnoDB
     a.Two important disk-based resources managed by the InnoDB storage engine are its tablespace data files and its log files.

     b.Specifying the Location and Size for InnoDB Tablespace Files
    [mysqld]
    innodb_data_home_dir=/data/mysql/ibdata
    innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
 
    c.Determining the Maximum Memory Allocation for InnoDB
         innodb_buffer_pool_size
         + key_buffer_size
         + max_connections*   (sort_buffer_size+read_buffer_size+binlog_cache_size)
         + max_connections*2MB
         < 2G in 32bit linux

5.Tuning other mysqld server parameters.
[mysqld]
skip-external-locking
max_connections=200
read_buffer_size=1M
sort_buffer_size=1M
#
# Set key_buffer to 5 - 50% of your RAM depending on how much
# you use MyISAM tables, but keep key_buffer_size + InnoDB
# buffer pool size < 80% of your RAM
key_buffer_size=value

6.Using Per-Table Tablespaces
       a.By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because each table that is created when this setting is in effect has its own tablespace.
  
       b.Enabling and Disabling Multiple Tablespaces
       [mysqld]
       innodb_file_per_table
  
      c.With multiple tablespaces enabled, InnoDB stores each newly created table in its own tbl_name.ibd file in the appropriate database directory. Unlike the MyISAM storage engine, with its separate tbl_name.MYD and tbl_name.MYI files for indexes and data, InnoDB stores the data and the indexes together in a single .ibd file. The tbl_name.frm file is still created as usual.

      d.You can always access both tables in the system tablespace and tables in their own tablespaces, regardless of the file-per-table setting. To move a table from the system tablespace to its own tablespace, or vice versa, you can change the innodb_file_per_table setting and issue the command:
ALTER TABLE table_name ENGINE=InnoDB;

     e.Portability Considerations for .ibd Files
      You cannot freely move .ibd files between database directories as you can with MyISAM table files. The table definition stored in the InnoDB shared tablespace includes the database name. The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.
To move an .ibd file and the associated table from one database to another, use a RENAME TABLE statement:
RENAME TABLE db1.tbl_name TO db2.tbl_name;


7.Backing Up and Recovering an InnoDB Database
        a.hot backup:
            MySQL Enterprise Backup / Percona-xtrabackup
        b.cold backup:
              stop the server without errors,
              copy innodb data files(ibdata*, .ibd),

               innodb log files(ib_logfile file), all .frm files for innodb tables
        c.mysqldump --single-transaction --opt -c -e --default-character-set=utf8        
  

8.The InnoDB Transaction Model and Locking
          In the InnoDB transaction model, the goal is to combine the best properties of a multi-versioning database with traditional two-phase locking. InnoDB does locking on the row level and runs queries as nonlocking consistent reads by default, in the style of Oracle.

          In terms of the SQL:1992 transaction isolation levels, the default InnoDB level is REPEATABLE READ. InnoDB offers all four transaction isolation levels described by the SQL standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

9.InnoDB Lock Modes
      a. InnoDB implements standard row-level locking where there are two types of locks:
           (1).A shared (S) lock permits a transaction to read a row.
           (2).An exclusive (X) lock permits a transaction to update or delete a row.

      b.Additionally, InnoDB supports multiple granularity locking which permits coexistence of record locks and locks on entire tables. To make locking at multiple granularity levels practical, additional types of locks called intention locks are used. Intention locks are table locks in InnoDB.
           (1).Intention shared (IS):

                 Transaction T intends to set S locks on individual rows in table t.
           (2).Intention exclusive (IX):

                 Transaction T intends to set X locks on those rows.

      c.The intention locking protocol is as follows:
          (1).Before a transaction can acquire an S lock on a row in table t, it must first acquire an IS or stronger lock on t.
          (2).Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on t.


10.InnoDB Multi-Versioning
         InnoDB is a multi-versioned storage engine: it keeps information about old versions of changed rows, to support transactional features such as concurrency and rollback.

11.Optimizing for InnoDB Tables
        a.Once your data reaches a stable size, or a growing table has increased by tens or some hundreds of megabytes, consider using the OPTIMIZE TABLE statement to reorganize the table and compact any wasted space.

        b.In InnoDB, having a long PRIMARY KEY,wastes a lot of disk space.
The primary key value for a row is duplicated in all the secondary index records that point to the same row.

        c.Use the VARCHAR data type instead of CHAR to store variable-length strings or for columns with many NULL values.

        d.For tables that are big, or contain lots of repetitive text or numeric data, consider using COMPRESSED row format.
COMPACT row format is the default InnoDB format in MySQL 5.5.

  
12.Optimizing InnoDB Transaction Management
        a.The default MySQL setting AUTOCOMMIT=1 can impose performance limitations on a busy database server.

        b.Avoid performing rollbacks after inserting, updating, or deleting huge numbers of rows.If a big transaction is slowing down server performance, rolling it back can make the problem worse

        To minimize the chance of this issue occurring: increase the size of the buffer pool so that all the DML changes can be cached rather than immediately written to disk; set innodb_change_buffering=all so that update and delete operations are buffered in addition to inserts; and consider issuing COMMIT statements periodically during the big DML operation, possibly breaking a single delete or update into multiple statements that operate on smaller numbers of rows.

13.Optimizing InnoDB Logging
         a.Make your log files big, even as big as the buffer pool. When InnoDB has written the log files full, it must write the modified contents of the buffer pool to disk in a checkpoint. Small log files cause many unnecessary disk writes. Although historically big log files caused lengthy recovery times, recovery is now much faster and you can confidently use large log files.

        b.Make the log buffer quite large as well (on the order of 8MB).


14.Bulk Data Loading for InnoDB Tables
       a.When importing data into InnoDB, turn off autocommit mode, because it performs a log flush to disk for every insert.
           SET autocommit=0;
                  ... SQL import statements ...
           COMMIT;
       b.SET unique_checks=0;
                 ... SQL import statements ...
           SET unique_checks=1;
       c.SET foreign_key_checks=0;
                 ... SQL import statements ...
          SET foreign_key_checks=1;
       d.Use the multiple-row INSERT syntax to reduce communication overhead
       e.When doing bulk inserts into tables with auto-increment columns, set innodb_autoinc_lock_mode to 2 or 3 instead of the default value 1.


15.Optimizing InnoDB Queries
       To tune queries for InnoDB tables, create an appropriate set of indexes on each table.
        a.Because each InnoDB table has a primary key (whether you request one or not), specify a set of primary key columns for each table, columns that are used in the most important and time-critical queries.

        b.Do not specify too many or too long columns in the primary key, because these column values are duplicated in each secondary index.

        c.Do not create a separate secondary index for each column, because each query can only make use of one index.

        d.If an indexed column cannot contain any NULL values, declare it as NOT NULL when you create the table. The optimizer can better determine which index is most effective to use for a query, when it knows whether each column contains NULL values or not.

        e.If you often have recurring queries for tables that are not updated frequently, enable the query cache:
[mysqld]
query_cache_type = 1
query_cache_size = 10M


16.Optimizing InnoDB DDL Operations
        a.For DDL operations on tables and indexes (CREATE, ALTER, and DROP statements), the most significant aspect for InnoDB tables is that creating and dropping secondary indexes is much faster in MySQL 5.5 and higher

       b.“Fast index creation” makes it faster in some cases to drop an index before loading data into a table, then re-create the index after loading the data.

       c.Use TRUNCATE TABLE to empty a table, not DELETE FROM tbl_name. Foreign key constraints can make a TRUNCATE statement work like a regular DELETE statement, in which case a sequence of commands like DROP TABLE and CREATE TABLE might be fastest.

       d.Because the primary key is integral to the storage layout of each InnoDB table, and changing the definition of the primary key involves reorganizing the whole table, always set up the primary key as part of the CREATE TABLE statement, and plan ahead so that you do not need to ALTER or DROP the primary key afterward.


17.Optimizing InnoDB for Systems with Many Tables
       InnoDB computes index cardinality values for a table the first time that table is accessed after startup, instead of storing such values in the table. This step can take significant time on systems that partition the data into many tables. Since this overhead only applies to the initial table open operation, to “warm up” a table for later use, access it immediately after startup by issuing a statement such as SELECT 1 FROM tbl_name LIMIT 1.