So I'm answering this question almost 4 years late:
InnoDB file formats were conceived at a time when InnoDB was independent of the MySQL Server (for example: MySQL 5.1 could run two different versions of InnoDB).
The reason why you would not want to run Barracuda (in 2012) is that it could reduce flexibility in downgrading MySQL (i.e. after a failed upgrade, you want to move back to a version that can not read a newer format). i.e. there should be no technical reasons why Antelope is better.
In MySQL 5.7 the innodb_file_format
option was deprecated. Since MySQL and InnoDB are no longer independent, and thus InnoDB can use the MySQL rules of upgrades and what backwards compatibility is required. No confusing setting required!
In MySQL 5.7, the default switched to Barracuda/DYNAMIC
. Since all currently supported releases of MySQL can read this format, it is safe to move away from Antelope and still offer downgrade.
On a MySQL 5.7 server, Antelope tables will be upgraded to Barracuda/DYNAMIC
on the next table rebuild (OPTIMIZE TABLE etc). That is unless they were specifically created with ROW_FORMAT=oldrowformat
.
In MySQL 8.0, the option innodb_file_format
is removed.
MySQL 5.7 also introduces the option innodb_default_row_format
, which defaults to DYNAMIC. This addresses the point in your update.
Your first query is asking for 20,000 rows. what is being victimized here ???
Your Innodb Buffer Pool and Log Files.
Because of SELECT * FROM history WHERE log LIKE '%a%' ORDER BY log ASC LIMIT 0, 20000;
, all the data pages for the history table is hogging InnoDB resources.
Since you are using Amazon RDS, you cannot resize the InnoDB Log Files. They are always 128M under all RDS MySQL models. Your buffer pool may or may not be full (You have 5882511360 set for it, which is 5610M or 5.4785G). Naturally, bigger model means more IOPs.
You can run this to see how full the buffer pool is
SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;
Another aspect I see is the log
field (TEXT). You are asking for an ORDER BY
on this big column.
You need to change the first query to throttle the SELECT. In other words, fetch smaller chunks of history.
If you cannot change the data or the query, there is nothing you can configure except to shift to m1.xlarge (Comes with 11922309120 as the Buffer Pool Size = 11370M = 11.1035G) although log file size would still be stuck at 128M.
Infrastructure aside, it is possible for SELECTs to block INSERTs, UPDATEs, and DELETEs in InnoDB.
I wrote about this before
Best Answer
I wrote a post 3 years ago that discusses this very subject : innodb_file_format Barracuda
In terms of storage, using Antelope and Barracuda is possible. However, in my post, I discuss a residual effect on the InnoDB Buffer Pool. You need a larger Buffer Pool because Barracuda compressed pages in the Buffer Pool have to be decompressed to the read in the Buffer Pool.
You should dig deeper through Percona's Performance Blog site for any additional insights.