…. With the original InnoDB Antelope file format you have the choice of ROW_FORMAT=COMPACT and ROW_FORMAT=REDUNDANT where InnoDB stored the first 768… file format (available since InnoDB plugin 1.1 or MySQL 5.5) you can now leverage table compression by specifying ROW_FORMAT=COMPRESSED… an ALTER TABLE statement. As table compression is table specific, ROW_FORMAT and KEY_BLOCK_SIZE directives are passed via CREATE TABLE…
Post: How number of columns affects performance ?
September 28, 2009 By 12 Comments
… rows/sec ; 653MB/sec) t99v1 – 12.36 sec (1M rows/sec ; 93MB/sec) This shows there is surely the problem with dynamic row format table with many columns. But is it because of large number of columns or dynamic format on its… similarly to how indexes are stored. Summary: Beware of dynamic row format tables with many columns they might bite you with surprise…
Comment: Adventures in archiving
February 14, 2013 By 16 Comments
…; create table $table_dynamic like $table; alter table $table_dynamic row_format=dynamic; insert into $table_dynamic select * from $table limit 100000…; create table $table_01 like $table; alter table $table_01 row_format=compressed key_block_size=1; insert into $table_01 select…; create table $table_16 like $table; alter table $table_16 row_format=compressed key_block_size=16; insert into $table_16 select…
Post: Debugging problems with row based replication
May 6, 2010 By 12 Comments
MySQL 5.1 introduces row based binary logging. In fact, the default binary logging format in GA versions of MySQL 5.1 is ‘MIXED’ STATEMENT*; The binlog_format variable can still be… is to write binary logs in statement mode. The row-based format does offer advantages particularly if triggers or stored procedures are…
Post: How much space does empty Innodb table take ?
December 16, 2008 By 7 Comments
… one row: mysql> show table status like “test_innodb” \G *************************** 1. row *************************** Name: test_innodb Engine: InnoDB Version: 10 Row_format: Compact Rows: 1 Avg_row… like “test_innodb” \G *************************** 1. row *************************** Name: test_innodb Engine: InnoDB Version: 10 Row_format: Compact Rows: 1069 Avg_row_length: 199 Data_length: 212992…
Comment: Blob Storage in Innodb
February 10, 2010 By 13 Comments
… status like “comptest” \G *************************** 1. row *************************** Name: comptest Engine: InnoDB Version: 10 Row_format: Compact Rows: 1 Avg_row_length: 1589248 Data_length: 1589248 Max… status like “comptest” \G *************************** 1. row *************************** Name: comptest Engine: InnoDB Version: 10 Row_format: Compressed Rows: 1 Avg_row_length: 32768 Data_length: 32768 Max…
Post: Adventures in archiving
February 11, 2013 By 16 Comments
…_row` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dat` varchar(1000) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT… intentions of using compress() ) compressed_row (The row format being set to compressed, ensure you have innodb_file_format Barracuda as Antelope does not…
Post: Percona Server 5.5.15-21.0
August 31, 2011 By 10 Comments
….5.15, a Fixed Row Format (FRF) is still being used in the MEMORY storage engine. The fixed row format imposes restrictions on the type of columns as it assigns on advance a limited amount of memory per row. This…. This implementation is based on the Dynamic Row Format (DFR) introduced by the mysql-heap-dynamic-rows patch. DFR is used to store…
Comment: MySQL 5.0, 5.1 and Innodb Plugin CPU Efficiency
December 5, 2008 By 15 Comments
… to clarify that the Barracuda file format only ENABLES new features that require an on-disk format change. Unless you create tables that use compression, or unless your table takes advantage of the new DYNAMIC row format (where… difference in performance between enabling the default Antelope format and the new Barracuda format. I know you’re not using compression for…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… STATUS LIKE ‘t’\G *************************** 1. row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Compact Rows: 4195067 Avg_row_length: 29 Data_length: 125452288 Max… STATUS LIKE ‘t’\G *************************** 1. row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Compact Rows: 4195067 Avg_row_length: 29 Data_length: 125452288 Max…
