MySQL 5.5: Improved manageability, efficiency for InnoDB

In my continuing blog series on MySQL 5.5 features (see performance/scale and replication entries) today I covering some of the new InnoDB manageability and efficiency options.  5.5, with the newly re-architected InnoDB, provides better user control over internal InnoDB settings so things like performance, scale and storage can easily be monitored, tuned and optimized for specific use cases and application loads.

Along these lines, some of the key advances and features available in MySQL 5.5 and InnoDB are:
 
  • Faster Index Creation - MySQL 5.5 can now add or drop indexes without copying the underlying data of the entire target table.  This improves the efficiency and speed of creating and dropping indexes on InnoDB tables.
  • Efficient Data Compression - New InnoDB table compression options significantly reduce the storage requirements for the MySQL database and improve application throughput by reducing I/O workload, all with minimal overhead and operating expense.  Users can specify new ROW_FORMAT and KEY_BLOCK_SIZE parameters in the CREATE TABLE and ALTER TABLE commands to store data pages in 1K, 2K, 4K, 8K or the standard 16K byte compression levels.  InnoDB also provides new INFORMATION_SCHEMA tables around compression so users can monitor and tune their implementations for optimal efficiency (see below). 
  • Efficient Storage Options for Large Objects and Variable-Length Columns - MySQL 5.5 improves storage efficiency of large objects by allowing users to create tables using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED.  With these options long column values are stored fully off-page, and the associated clustered index record contains only a 20-byte pointer to the overflow page.
It should be noted that the new compression and large object and variable length column storage options require the use of the new Barracuda file format, so you'll want to reference the docs to understand the benefits (and ramifications) of making that move.

  • New INFORMATION_SCHEMA tables - 7 new tables provide information specific to InnoDB compression and transaction locking.
  • INNODB_CMP - Contains status information on the efficiency and operations related to compressed tables.
  • INNODB_CMP_RESET - Same as above, but reading from this table resets the reported statistics.
  • INNODB_CMPMEM - Contains status information on the compressed pages that reside in the buffer pool.
  • INNODB_CMPMEM_RESET - Same as above, but reading from this table resets the reported statistics.
  • INNODB_TRX - Contains information about every transaction currently executing inside InnoDB, including whether the transaction is waiting for a lock, when the transaction started, and the particular SQL statement the transaction is executing.
  • INNODB_LOCKS - Contains a row for each blocked transaction that describes each lock the transaction has requested and what the transaction is waiting on.
  • INNODB_LOCK_WAITS - Contains information about transactions that are waiting for a specific lock.
Learn about all of the new MySQL 5.5 and InnoDB performance and scalability enhancements, including how to enable and implement them here.

As always, THANKS for reading and THANKS for your support of MySQL!
Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Get the latest updates on products, technology, news, events, webcasts, customers and more.

Twitter


Facebook

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
5
6
9
10
11
12
13
15
16
17
18
19
20
23
25
26
27
28
29
30
   
       
Today