Discussing the innodb_log_block_size variable

Not a ground-breaking post here, but if you are interested in knowing more about the innodb_log_block_size variable, or if you use SSD cards and/or large InnoDB log files on ext4, then this is for you.

I’d read about it before briefly before, but didn’t give it too much thought until I ran across the following entry in an error log the other day:

InnoDB: Warning: innodb_log_block_size has been changed 
from default value 512. (###EXPERIMENTAL### operation)

This got me wanting to know more.

Basically, this variable changes the size of transaction log records. Generally, the default of 512 is a good value. However, it has been found that setting it to 4096 has been beneficial when using SSD cards. (Note that while it is possible to set this to a value other than 512 or 4096, those are currently the only 2 values that make sense to use.)

Also, it has been found that 4096 is the best setting if you run ext4 along with innodb_flush_method=ALL_O_DIRECT (note both innodb_log_block_size and ALL_O_DIRECT are specific to XtraDB/XtraDB+; read: MariaDB and Percona server).

What is ALL_O_DIRECT and when it is useful?

“ALL_O_DIRECT: use O_DIRECT to open both data and log files, and use fsync() to flush the data files but not the log files. This option is recommended when InnoDB log files are big (more than 8GB), otherwise there might be even a performance degradation.”

http://www.percona.com/…/innodb_io.html#innodb_flush_method

Thus if you’re running large InnoDB log files (8G+) on ext4, you may want to consider ALL_O_DIRECT, and because you’re on ext4, you should set innodb_log_block_size=4096, the default log-block-size in ext4, in order to avoid the unaligned AIO/DIO warnings.

Likewise, if running with SSD cards, you’d likely see a performance improvement with innodb_log_block_size=4096 also.

If interested, there is a bit more about the innodb_log_block_size option here:

https://mariadb.com/…variables/#innodb_log_block_size
http://www.percona.com/…/innodb_io_55.html#innodb_log_block_size

Hope this helps.

 

MariaDB Launches Ambassadors Program

It was announced today that the MariaDB Corporation is launching an Ambassadors Program on behalf of the MariaDB Foundation.

Colin Charles, Chief Evangelist at MariaDB Corporation said, “The MariaDB Ambassador Program is set up to recognize and support experienced contributors to the MariaDB and MySQL ecosystem who are responsible for representing, promoting and expanding the use of MariaDB and its ideals to the larger open source community and general public.”

He further added, “There are two distinct types of ambassadors, all of whom will be volunteers. Community Ambassadors will promote grassroots adoption in their specific region. Platform Ambassadors will contribute virtually through the creation of code, patches, features, and who lead MariaDB engineering efforts at their respective organizations. MariaDB Corporation will provide each Community Ambassador with funds to organize local MariaDB meetups and invite Platform Ambassadors to MariaDB Engineering meetings as guests.”

You can read the full press release here:

https://mariadb.com/news-events/press-releases/mariadb-launches-ambassadors-program

 

Disabling InnoDB in MySQL 5.6 and MariaDB 10.0

There are a few circumstances where one will not want to run with only MyISAM tables. In this case, it can be beneficial to completely disable InnoDB.

As InnoDB has become more prevalent, disabling it in MySQL requires a little more effort than before.

In MariaDB 10.0, you can still completely disable it as you have done in the past (just add the –skip-innodb option, specify default-storage-engine=MyISAM, and comment out other InnoDB options):

[mysqld]
skip-innodb
default-storage-engine=MyISAM

Alternatively, instead of –skip-innodb, you can instead use “innodb=OFF”:

[mysqld]
skip-innodb
default-storage-engine=MyISAM

In MySQL 5.6, the –skip-innodb option has been deprecated (though still currently works), and since InnoDB is the new “default” storage engine, you must set both “default-storage-engine” and the new “default-tmp-storage-engine” options to “MyISAM”. If you do not set the latter, you instance will not start, and the error log will simply complain that “Unknown/unsupported storage engine: InnoDB” and you won’t easily identify why:

2014-11-08 18:47:28 13524 [ERROR] Unknown/unsupported storage engine: InnoDB
2014-11-08 18:47:28 13524 [ERROR] Aborting

So, if you want to disable InnoDB in MySQL 5.6, you’ll need to specify the following:

default-storage-engine=MyISAM
default-tmp-storage-engine=MyISAM
innodb=OFF

For reference, this last part is documented in the manual here, but I felt a post was appropriate given the obscurity of this overall issue.

Also, if you disable InnoDB in MySQL, you may see an extraneous message in your error log, as described here. Also, in both MySQL and MariaDB, you’ll likely see a few strange messages after running mysql_upgrade, which I discuss in more detail here.

Hope this helps.

 

Resolving “Unknown storage engine ‘InnoDB'” Error when Running MyISAM-only and Upgrading to MySQL 5.6 or MariaDB 10.0

I know, what, MyISAM-only? Yes, it’s true, and it occurs more than you might think. 🙂

If you do run MyISAM-only, it’s common to have disabled InnoDB altogether (–skip-innodb) to avoid allocating any RAM to it, and to avoid unnecessary files required for backups, and so forth. (See this post if interested in disabling InnoDB in MySQL 5.6 and/or MariaDB 10.0.)

One improvement in MySQL 5.6 and MariaDB 10.0 is the addition of some system tables. The 2 that are in both are `innodb_table_stats` and `innodb_index_stats`, both of which have an engine type of InnoDB. (The 1 additional system table in MariaDB 10.0 is `gtid_slave_pos`, and the 3 additional system tables in MySQL 5.6 are `slave_master_info`, `slave_relay_log_info`, and `slave_worker_info`.)

The issue, with regards to running MyISAM-only, is that these new tables are of type InnoDB by default. So if you’re running MyISAM only, you’ll see some errors and warnings when running mysql_upgrade, and/or examining your error log.

When running mysql_upgrade, you’ll see entries like (in both MySQL and MariaDB):

mysql.innodb_index_stats
Error    : Unknown storage engine 'InnoDB'
error    : Corrupt
mysql.innodb_table_stats
Error    : Unknown storage engine 'InnoDB'
error    : Corrupt

On the other hand, if looking in your error log, you might see something like this in MySQL:

[Warning] Info table is not ready to be used. 
Table 'mysql.slave_master_info' cannot be opened.
[Warning] Info table is not ready to be used. 
Table 'mysql.slave_relay_log_info' cannot be opened.

Or this in MariaDB:

[Warning] Failed to load slave replication state from 
table mysql.gtid_slave_pos: 1286: Unknown storage engine 'InnoDB'

So what to do if you want to fix this?

If you do not want the new tables at all, you can simply delete the .frm and .ibd files. This is probably fine for the 2 innodb_*_stats tables, since you’re not using InnoDB. However, you may want the other tables, depending on your replication needs, so this might not be an option for them.

Thus if you want one or more of these tables, then you can re-create them as MyISAM tables using CREATE TABLE statements (I’ll provide current ones at the end). Alternatively, you can temporarily enable InnoDB, ALTER the tables from InnoDB to MyISAM using “ALTER TABLE `table_name` ENGINE=MyISAM”, then disable InnoDB again.

MariaDB 10.0.14 SHOW CREATE outputs:

CREATE TABLE `gtid_slave_pos` (
  `domain_id` int(10) unsigned NOT NULL,
  `sub_id` bigint(20) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `seq_no` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`domain_id`,`sub_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';
CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

MySQL 5.6.21 SHOW CREATE outputs:

CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE `slave_master_info` (
  `Master_id` int(10) unsigned NOT NULL,
  `Number_of_lines` int(10) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Host` text CHARACTER SET utf8 COLLATE utf8_bin,
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin,
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Port` int(10) unsigned NOT NULL,
  `Connect_retry` int(10) unsigned NOT NULL,
  `Enabled_ssl` tinyint(1) NOT NULL,
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_verify_servert_cert` tinyint(1) NOT NULL,
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Retry_count` bigint(20) unsigned NOT NULL,
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  PRIMARY KEY (`Master_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
CREATE TABLE `slave_relay_log_info` (
  `Master_id` int(10) unsigned NOT NULL,
  `Number_of_lines` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Sql_delay` int(11) NOT NULL,
  PRIMARY KEY (`Master_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

Hope this helps.

 

Stopping a Runaway Rollback due to Failing Mass Import or ALTER TABLE

I ran into this the other day, and while the solution is documented in the manual, it was kind of buried, so I thought I’d mention it here (and hopefully make it easier for others searching for this in the future).

In this specific instance, one was running a huge LOAD DATA (10 hours into it) and had to terminate the LOAD DATA command.

Terminating the command is one thing, but preventing InnoDB from trying to undo the rows that were inserted is another.

In this case, the LOAD DATA will easily re-create the table in question, so the existing table can be “thrown out”, so to speak. This, of course, is a *critical* requirement for this operation (i.e., that you can easily re-create the table – either from a load, backup, dump, etc.).

Per the manual:

“If you know that a given table is causing a crash on rollback, you can drop it. You can also use this to stop a runaway rollback caused by a failing mass import or ALTER TABLE: kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback.”

https://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

Then, once the table is dropped:

  1. stop mysql
  2. remove the innodb_force_recovery=3 from config file
  3. start mysql
  4. re-create the table/re-run ALTER TABLE

This will allow you to be back up where you started, aside from re-creating the table, in minutes, as opposed to who knows how long for the runaway rollback to complete (I’ve seen 24-30+ hours in more extreme cases).

Hope this helps.