Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a couple questions for those more familiar. Most of my instances have been running Antelope despite having support for Barracuda.

I was looking to play around with some compresses innodb tables. My understanding is this is only available under the Barracuda format.

  1. I see innodb_file_format is dynamic so I can just switch over with out a bounce. Are there any implications of doing this I should be aware of. All I can tell is that means new tables or subsequently altered will be created with that format. Is this all correct?
  2. I was hoping to have to not go through and convert all my tables. Is is kosher to have antelope and barracude tables coexisting in the same tablespace? Even if it works are there any gotcha's to look out for?

From what I've read and gathered from my tests the answers are: Yes. Yes. I'm not sure.

Update

I've been running w/ some Dynamic and some Compressed tables in various instances since this post with out issue. Further I neglected to read http://dev.mysql.com/doc/refman/5.5/en/innodb-file-format-identifying.html at the time.

After you enable a given innodb_file_format, this change applies only to newly created tables rather than existing ones. If you do create a new table, the tablespace containing the table is tagged with the “earliest” or “simplest” file format that is required for the table's features. For example, if you enable file format Barracuda, and create a new table that is not compressed and does not use ROW_FORMAT=DYNAMIC, the new tablespace that contains the table is tagged as using file format Antelope.

So tables will be created as Antelope even if you allow Barracuda. The mixing is unavoidable unless you specify every table as row_format dynamic or a compressed table.

There is no indication you should do a complete dump and reload when introducing your first Barracuda table (such as is recommended when upgrading major versions of mysql)

share|improve this question

2 Answers 2

If you really want to play with InnoDB using the Barracuda format, you should mysqldump everything to something like /root/MySQLData.sql. That makes the data file format independent.

Use another MySQL instance with a fresh ibdata1 and innodb_file_per_table (optional, my personal preference). Change the file format with ibdata1 empty. Then, reload /root/MySQLData.sql and play with the data.

I have heard slight horror stories about PostgreSQL having to tweek settings to get a 8.2.4 database to work with 9.0.1 binaries. The same story could apply if we tried make both file formats reside in the same system tablespace (ibdata1) and/or .ibd file if we are aware of such settings.

As far as being kosher...

  • Nobody should store meat and dairy in the same refrigerator
  • Nobody should put a bull and an ass under the same yoke (Deuteronomy 22:10)
  • Nobody should store Antelope and Barracuda inside the same ibdata1

UPDATE 2013-07-05 14:26 EDT

I just answered this question in ServerFault : Setting MySQL INNODB Compression KEY_BLOCK_SIZE. This made me look back for any questions I answered in the DBA StackExchange had discussed the Barracuda format and I found this old post of mine. I will place the same information here...

According to the MySQL Documentation on InnoDB Compression for Barracuda

Compression and the InnoDB Buffer Pool

In a compressed InnoDB table, every compressed page (whether 1K, 2K, 4K or 8K) corresponds to an uncompressed page of 16K bytes. To access the data in a page, InnoDB reads the compressed page from disk if it is not already in the buffer pool, then uncompresses the page to its original 16K byte form. This section describes how InnoDB manages the buffer pool with respect to pages of compressed tables.

To minimize I/O and to reduce the need to uncompress a page, at times the buffer pool contains both the compressed and uncompressed form of a database page. To make room for other required database pages, InnoDB may “evict” from the buffer pool an uncompressed page, while leaving the compressed page in memory. Or, if a page has not been accessed in a while, the compressed form of the page may be written to disk, to free space for other data. Thus, at any given time, the buffer pool may contain both the compressed and uncompressed forms of the page, or only the compressed form of the page, or neither.

InnoDB keeps track of which pages to keep in memory and which to evict using a least-recently-used (LRU) list, so that “hot” or frequently accessed data tends to stay in memory. When compressed tables are accessed, InnoDB uses an adaptive LRU algorithm to achieve an appropriate balance of compressed and uncompressed pages in memory. This adaptive algorithm is sensitive to whether the system is running in an I/O-bound or CPU-bound manner. The goal is to avoid spending too much processing time uncompressing pages when the CPU is busy, and to avoid doing excess I/O when the CPU has spare cycles that can be used for uncompressing compressed pages (that may already be in memory). When the system is I/O-bound, the algorithm prefers to evict the uncompressed copy of a page rather than both copies, to make more room for other disk pages to become memory resident. When the system is CPU-bound, InnoDB prefers to evict both the compressed and uncompressed page, so that more memory can be used for “hot” pages and reducing the need to uncompress data in memory only in compressed form.

Notice that the InnoDB Buffer Pool has to load data pages and index pages read to fulfill queries. When reading a table and its indexes for the first time, the compressed page must be uncompressed to 16K. That means you will have twice as much data content in the buffer pool, the compressed and uncompressed data page.

If this duplication of data content is going on in the Buffer Pool, you need to increase innodb_buffer_pool_size by a small linear factor of the new compression rate. Here is how:

SCENARIO

  • You have a DB Server with a 8G Buffer Pool
  • You ran compression with key_block_size=8
    • 8 is 50.00% of 16
    • 50.00% of 8G is 4G
    • raise innodb_buffer_pool_size to 12G (8G + 4G)
  • You ran compression with key_block_size=4
    • 4 is 25.00% of 16
    • 25.00% of 8G is 2G
    • raise innodb_buffer_pool_size to 10G (8G + 2G)
  • You ran compression with key_block_size=2
    • 2 is 12.50% of 16
    • 12.50% of 8G is 1G
    • raise innodb_buffer_pool_size to 9G (8G + 1G)
  • You ran compression with key_block_size=1
    • 1 is 06.25% of 16
    • 06.25% of 8G is 0.5G (512M)
    • raise innodb_buffer_pool_size to 8704M (8G (8192M) + 512M)

MORAL OF THE STORY : The InnoDB Buffer Pool just needs additional breathing room when handling compressed data and index pages.

share|improve this answer
    
+1 for offering feedback but no check as I'm still unconvinced the bull and the ass won't work well together. I can't find any good docs or benchmarks supporting or refuting the concept of their cohabitation :-\ Maybe my google skills are just weak this week. At anyrate, in the words of Bill O'Riley, I'm gonna do it live! (It's in a non mission critical db) Famous last words? I hope not, but we'll see. I'll report back after a while with a success or failure story in hopes others might learn from this experiment for better or worse. –  atxdba Mar 3 '12 at 15:51
    
Re: "Nobody should store Antelope and Barracuda inside the same ibdata1" According to tinyurl.com/d789zzw even if you allow Barracuda tables will still be created Antelope unless they need dynamic rows or compression. This reads that mixing is expected and perfectly fine. There is no mention to introduce a Barracuda table you should to a complete reload vs just doing an alter. –  atxdba Aug 22 '12 at 16:17
Just give a try!!!    

       mysql> select version();
        +------------+
        | version()  |
        +------------+
        | 5.5.21-log |
        +------------+
        1 row in set (0.00 sec)


mysql> show variables like "%innodb_file%";
 +--------------------------+----------+
   Variable_name            | Value    |
 +--------------------------+----------+
 | innodb_file_format       | Antelope |
 | innodb_file_format_check | ON       |
 | innodb_file_format_max   | Antelope |
 | innodb_file_per_table    | ON       |
 +--------------------------+----------+
 4 rows in set (0.00 sec)

mysql> SET GLOBAL innodb_file_format = barracuda;
     Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%innodb_file%";
   +--------------------------+-----------+
   | Variable_name            | Value     |
   +--------------------------+-----------+
   | innodb_file_format       | Barracuda |
   | innodb_file_format_check | ON        |
   | innodb_file_format_max   | Antelope  |
   | innodb_file_per_table    | ON        |
   +--------------------------+-----------+
   4 rows in set (0.00 sec)

  mysql> SET GLOBAL innodb_file_format_max = barracuda;
          Query OK, 0 rows affected (0.00 sec)

  mysql> show variables like "%innodb_file%";
       +--------------------------+-----------+
       | Variable_name            | Value     |
       +--------------------------+-----------+
       | innodb_file_format       | Barracuda |
       | innodb_file_format_check | ON        |
       | innodb_file_format_max   | Barracuda |
       | innodb_file_per_table    | ON        |
       +--------------------------+-----------+
        4 rows in set (0.00 sec)

    I had observed a single line logged in Error Log file :

   [root@dhcppc0 Desktop]# tail -1 /usr/local/mysql/data/dhcppc0.err
120402 11:26:52 [Info] InnoDB: the file format in the system tablespace is
now set to Barracuda.


After switching to barracuda file format, I could also access my Database 
 and tables without any error :

  mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | opentaps1          |
    | performance_schema |
    | test               |
    +--------------------+
    5 rows in set (0.00 sec)

    mysql> use opentaps1;
           Database changed
   mysql> select count(*) from product;
          +----------+
          | count(*) |
          +----------+
          |     3244 |
          +----------+
         1 row in set (0.42 sec)

    mysql> show engines;
 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name: 
Status: 
=====================================
120402 11:36:29 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18446744073709534037 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 12 1_second, 12 sleeps, 1 10_second, 2 background,
2 flush
srv_master_thread log flush and writes: 12
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5, signal count 5
Mutex spin waits 2, rounds 60, OS waits 2
RW-shared spins 3, rounds 90, OS waits 3
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter F01
Purge done for trx's n:o < 0 undo n:o < 0
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION F00, not started
MySQL thread id 1, OS thread handle 0x7f38309f9710, query id 28 localhost
root
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer
thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
554 OS file reads, 7 OS file writes, 7 OS fsyncs
-0.01 reads/s, 16384 avg bytes/read, -0.00 writes/s, -0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 15 buffer(s)
-0.15 hash searches/s, -0.12 non-hash searches/s
---
LOG
---
Log sequence number 221536390
Log flushed up to   221536390
Last checkpoint at  221536390
0 pending log writes, 0 pending chkp writes
10 log i/o's done, -0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 3476070
Buffer pool size   8192
Free buffers       7635
Database pages     542
Old database pages 220
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
-0.00 youngs/s, -0.00 non-youngs/s
Pages read 542, created 0, written 1
-0.01 reads/s, -0.00 creates/s, -0.00 writes/s
Buffer pool hit rate 980 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead -0.00/s, evicted without access -0.00/s, Random read ahead
-0.00/s
LRU len: 542, unzip_LRU len: 0
I/O sum[0]:cur[238], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2937, id 139879303665424, state: waiting for server
activity
Number of rows inserted 0, updated 0, deleted 0, read 3244
-0.00 inserts/s, -0.00 updates/s, -0.00 deletes/s, -0.18 reads/s
 ----------------------------
 END OF INNODB MONITOR OUTPUT
 ============================

 1 row in set (0.00 sec)
share|improve this answer
1  
Oh no, you can't use "Just give a try!!!". It's a registered TradeMark of @Rolando :) –  ypercube Apr 2 '12 at 6:38
    
@ ypercube i am aware of. Will come up with my own Trade mark in my upcoming posts :) –  Gopinath Apr 2 '12 at 6:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.