MySQL 5.5: Improved manageability, efficiency for InnoDB

|원문| MySQL 5.5 Improved manageability, efficiency for InnoDB

MySQL 5.5: Improved manageability, efficiency for InnoDB

By Rob Young on Nov 30, 2010

MySQL 5.5: 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.

 MySQL5.5 에서 새로 구성되어진 InnoDB는 인터널 InnoDB 셋팅을 통해 사용자에게 더 편한 제어력을 제공하여 특수한 경우나 어플리케이션 작업들에 대해 성능, 크기(scale), 스토리지부분에  쉽게 모니터링, 조정(tune), 최적화(optimized)가 가능하다. 

Along these lines, some of the key advances and features available in MySQL 5.5 and InnoDB are:
 
아래는 MySQL5.5.와 InnoDB에서의 특징적인 향상점들과 특징들이다.

• 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.

더 빨라진 index 생성 - MySQL5.5는 인덱스를 생성(add)하고 삭제(drop) 할 때 모든 대상테이블의 데이터들을 copy 작업없이 진행한다. 이 것이 InnoDB 테이블에서 인덱스를 생성/ 삭제 에 더 빠른 속도를 제공할 수 있는 이유이다.

• 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).

효율적인 데이터압축(data compression) - 새로운 InnoDB 테이블 압축 옵션은 MySQL 데이터베이스가 요구하는 스토리지 크기를 상당히 줄였고,  I/O 처리량의 감소, 오버해드와 처리비용의 최소화로 어플리케이션 의 처리를 향상시켰다.
  사용자는 새로운 ROW_FORMAT 과 KEY_BLOCK_SIZE파라메터를 create table, alter table구문에 사용할 수 있다. 이는 데이터 페이지를 저장할 목적으로 사용되며 1K, 2K, 4K, 8K 또는 표준크기인 16K byte 의 압축레벨로 지정 가능하다. InnoDB는 또한 압축(compression)에 대한 새로운 INFORMATION_SCHEMA 테이블들을 제공한다. 그래서 사용자들이 최적의 능률을 위해 해당 테이블들을 이용해 모니터링, 조절할 수 있다.

• 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.

큰 object들과 다양한 길이의 컬럼들을 위한 효율적인 스토리지 옵션 - MySQL5.5 는 큰 object(large objects)들에 사용자가 create table구문에 ROW_FORMAT=DYNAMIC 또는 ROW_FORMAT=COMPRESSED를 사용해 스토리지 능률을 높일 수 있다. 이 옵션을 이용하면 긴 컬럼들은 off-page형태로 모두 저장되고 관련된 clustered index 레코드는 페이지과잉에 대해 20byte포인터밖에 포함하지 못한다.

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.

새로운 압축과 큰 object그리고 다양한 길이의 컬럼 스토리지 옵션들은 새로운 barracuda 파일 형식을 필요로 한다는 것을 명심해야 한다. 자세한 사항은 문서를 참조하라.

• New INFORMATION_SCHEMA tables - 7 new tables provide information specific to InnoDB compression and transaction locking.

새로운 INFORMATION_SCHEMA 테이블들 소개 - InnoDB압축과 트랜젝션 락에 관련 된 7개의 새로운 테이블들이 제공된다.

• INNODB_CMP - Contains status information on the efficiency and operations related to compressed tables.
• INNODB_CMP: 압축 된 테이블과 관련된 작동과 효능에 대한 상태정보 제공

• INNODB_CMP_RESET - Same as above, but reading from this table resets the reported statistics.
• INNODB_CMP_RESET: 위의 것과 같음. 하지만 리포트 된 통계자료를 reset하여 해당 테이블 정보를 가져옴
  
  
• INNODB_CMPMEM - Contains status information on the compressed pages that reside in the buffer pool.
• INNODB_CMPMEM: 버퍼풀에 머무는 압축된 페이지들에 대한 정보 제공
  
• INNODB_CMPMEM_RESET - Same as above, but reading from this table resets the reported statistics.
• INNODB_CMPMEM_RESET: 위의 것과 같음.  하지만 리포트 된 통계자료를 reset하여 테이블 정보를 가져옴
  
• 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_TRX: InnoDB로 현재 실행되는 모든 트랜잭션들에 대한 정보를 제공한다. 트랜잭션이 락을 기다리고 있는지의 여부, 트랜잭션이 시작된 시간, 트랜잭션이 실행되는 특정한 SQL문 에 대한 정보를 포함한다.
  
• 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_LOCKS: 블록화 된 트랜잭션들 각각의 행은 (a row for each blocked transaction) 트랜젝션이 요청한 락과 그리고 어떤 트랜잭션이 각각의 락에 대해 기다리고 있는지에 대한 정보를 제공한다
  
• INNODB_LOCK_WAITS - Contains information about transactions that are waiting for a specific lock.
• INNODB_LOCK_WAITS : 특정한 락에 대해 대기하고 있는 트랜젝션들의 정보를 제공한다

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!



|참고|http://dev.mysql.com/doc/refman/5.5/en/innodb-compression-internals.html

1. table compressed
   [MySQL 5.1 ver InnoDB Plugin 1.0부터 사용가능]
   : Disk와 memory 사이에 크기가 작은 데이터들이 전송되도록 하고, 메모리 공간 또한 작게 차지하도록 해 데이터의 처리 속도를 높이기 위한 용도로 사용. 인덱스까지 압축하는 기능을 제공하므로 secondary index에까지 효율성 제공.
 
2. compression 옵션이 가능한 환경

innodb_file_per_table = ON
innodb_file_format = ON (for "barracuda" datafile format)
barracuda data file 사용 (일반적으로 사용하는 datafile = Antelope)

3. 일반적인 innodb data page 크기: 16KB
4. InnoDB Plugin이 사용될 때 부터 테이블 압축(table compression) 기능 사용가능.

ROW_FORMAT=COMPRESSED, KEY_BLOCK_SIZE 또는 CREATE TABLE 과 ALTER TABLE 구문으로 지정가능

 

KEY_BLOCK_SIZE = 테이블에서 사용될 압축되는 page의 사이즈를 나타냄

                 크기: 1KB, 2KB, 4KB, 8KB, 16KB
ROW_FORMAT = 테이블의 형태 
             COMPRESSED,

5. compressed table 생성구문

 

CREATE TABLE name
(column1 INT PRIMARY KEY)
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=4;


 => 만약 ROW_FORMAT=COMPRESSED 만 기술해 주고, KEY_BLOCK_SIZE는 기술해 주지 않았다면 KEY_BLOCK_SIZE = 8K로 자동 지정됨

    KEY_BLOCK_SIZE를 지정해 준다면, ROW_FORMAT=COMPRESSED 는 지정해 주지 않아도 자동지정 됨.

 => KEY_BLOCK_SIZE=16 은 압축에 별 효능을 제공하지 못함(innodb default data page size이므로...) => 하지만 BLOB, VARCHAR, TEXT 컬럼들에는 도움을 줄 수 있다.

6. CREATE TABLE 이나 ALTER TABLE 구문을 이용해서 compressed 옵션을 줄 때 클러스터드 인덱스를 포함한 모든 인덱스들도 함께 
   압축된다. 

7. CREATE INDEX 구문에는 ROW_FORMAT 과 KEY_BLOCK_SIZE 가 속하지 않으므로 CREATE INDEX 구문에 기술 해 준다고 해도 인덱스에 압축옵션이 적용되지 않는다.(압축은 테이블단위에서 이루어진다)