How many storage engines used normally
How many storage engines used normally
eBay Memory Engine
MySQL storage engines
eBay Memory Engine
The deep study of different storage engines reveals key features why we use them in our databases and what role they play there.
MyISAM storage engine is the default and widely used due to lack of knowledge of understanding
the different storage engines, the InnoDB storage engine is the most popular engine used by
more established MySQL users and organizations. The primary reason is full transactional support.
Key Features of InnoDB:
Row level locking
Improved performance in locking and large core systems
Fully ACID compliant
Supports MVCC (Multi Version Concurrency Control) and four isolation levels
Dynamic control of additional variables
New row format
Supported by a commercial entity Innobase as the primary product
Primary key is a clustered index
Fast index creation
Supports foreign keys
Limitations of InnoDB:
Does not support full text indexes
Generally 2x–3x greater disk space requirements to MyISAM
Important Parameters of InnoDB:
ignore-builtin-innodb: In order to use the plugin version, you must disable the built in version.
Table level locking on DDL statements Owned by company with other commercial interests and competing products
Not as open as other open source offerings in the development roadmap.
plugin-load: list of plugins which includes the storage engine and INFORMATION_SCHEMA tables.
innodb_file_per_table: In order to use the new fi le formats you must also specify this parameter which changes the storage of tables from a common tablespace to per table tablespaces.
innodb_buffer_pool_size: Defi nes the amount of system memory allocated to InnoDB internal data storage.
innodb_log_file_size: Defi nes the amount of disk storage assigned for the
InnoDB transaction logs. The total disk size is innodb_log_file_size times innodb_log_files_in_group.
innodb_file_format: To use new DYNAMIC and COMPRESSED formats, you need to set this parameter to Barracuda.
innodb_fl ush_log_at_trx_commit: Defi nes how InnoDB should fl ush the transaction logs to disk. A change in this variable is used to increase performance; however, this decreases durability.
innodb_thread_concurrency: Defi nes the number of internal threads the InnoDB kernel
can use to manage transaction concurrency.
innodb_fl ush_method: Determines which system calls and options are used to fl ush data and log transactions.
which engine has greatest parameter:
Of all the storage engines InnoDB has the greatest number of parameters. For a full list of parameters go to http://dev.mysql.com/doc/refman/5.1/en/
innodb-parameters.html. InnoDB also has the most information on monitoring output via the SHOW ENGINE INNODB STATUS and SHOW GLOBAL STATUS LIKE ‘innodb%’ commands.
Best way to understand InnoDB Table Usage
By default an Innodb table is represented as two separate fi les in the file system located in the defined data directory for the MySQL instance. These fi les are:
table.frm: This is the table format defi nition file.
ibdata1: This is the default InnoDB tablespace for all tables.
In addition, InnoDB generally has two transaction log files that are critical for operation:
we can change the names and locations of the default names for InnoDB tablespace and log fi les via various confi guration options. comparison between InnoDB and MyISAM
As a developer you should be aware of specific differences between MyISAM and InnoDB. As mentioned, the primary reason to use InnoDB is transactional support. With this support comes a
number of other features — a signifi cant one is that of row-level locking. MyISAM, though very fast, suffers from table-level locking in a high write and read environment. This can be overcome by simply
changing tables to use InnoDB.
However, impacts to altering the storage engine can affect both functionality and performance of
your MySQL databases. A few important considerations for developers are: Increase in disk footprint Impact on disk storage due to Primary key type
Performance of COUNT(*)
No support for FULLTEXT indexes
Differences in SQL Query Execution Plan (QEP) The fi rst is disk footprint. InnoDB generally uses two to three times more disk space.
InnoDB also stores information in secondary indexes differently from MyISAM. Within the btree structure
of the secondary index, InnoDB stores the value you are indexing, and also stores the value of the primary key.
In MyISAM the index stores the value you are indexing and a pointer to the row of data
that includes the value of the primary key. This is very signifi cant when you have tables with largewidth
primary keys and your table has a lot of secondary indexes. An example is using a 40 byte,
3-column primary key column and having 19 indexes. By introducing a short 4-byte primary key, the
index disk footprint was reduced by 75 percent of the original size.
Optimizing SQL Using InnoDB:
The optimizer uses information about table indexes and the statistics of column
distribution to determine the best execution path. MySQL by default uses only one index per table
in an SQL query (with a few minor exceptions). When joining multiple tables, especially intersection
tables, only one index is used. When converting a table from MyISAM to InnoDB, you cannot assume
the same indexes will be used when your query is executed. It is important that you review the
Query Execution Plan (QEP) via the EXPLAIN syntax. Two differences that affect how data is stored
and retrieved have already been discussed.
InnoDB is that of SQL optimizations. Internally MySQL parses a SQL query and then determines via a cost-based optimizer the best means of satisfying the query
in the quickest time. With InnoDB data is now in primary key order, and this provides for sequential reading of data when using a primary key. The second point is that the value
of the primary key is stored in a secondary index, allowing for this value to be used in some situations as if it were a second indexed column of the index.
As with any changes in your application it is important that you test these changes under realistic
production conditions. Converting tables from MyISAM to InnoDB while changing application functionality
can result in worse performance when the MySQL environment is not tuned appropriately,
or particular situations including those discussed are not understood and considered as impacts.
Storage engines tutorials