0

I have a MariaDB Galera cluster (v. 10.0.19) with 3 nodes. I want to increase the innodb_log_file_size (i.e. the size of the InnoDB redo log files) on one of the nodes to match what it is on the other two nodes, i.e. 750M.

On this particular node, just changing the variable in the .cnf file + restarting mysql does nothing. MariaDB appears to just ignore it, and there are no warnings or errors or anything in the MariaDB error log on the node. The size of the log files is stuck at 100M.

On the other two nodes I can change the value of this variable in the .cnf file and then do "service mysql restart", and the two InnoDB log files will resize accordingly. The size agrees with the result from

show global variables like 'innodb_log_file_size';

I'm using the same version of MariaDB Galera cluster on all 3 nodes, and the cluster and all 3 nodes are otherwise healthy as far as I can tell.

There was an issue previously with importing a backup with a large BLOB / TEXT value, and I do have the below in the error log, although this is a while ago now:


160408 15:09:55 [ERROR] InnoDB: The total blob data length (60192222) is greater than 10% of the total redo log size (209715200). Please increase total redo log size.
160408 15:09:56 [ERROR] Slave SQL: Could not execute Write_rows_v1 event on table confluencedev.PLUGINDATA; The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size., Error_code: 1118; Got error 139 "Too big row" from storage engine InnoDB, Error_code: 1030; handler error HA_ERR_TO_BIG_ROW; the event's master log FIRST, end_log_pos 60193176, Internal MariaDB error code: 1118
160408 15:09:56 [Warning] WSREP: RBR event 2 Write_rows_v1 apply warning: 139, 286800
160408 15:09:56 [Warning] WSREP: Failed to apply app buffer: seqno: 286800, status: 1
     at galera/src/trx_handle.cpp:apply():351
Retrying 2th time

And then:

160408 15:09:57 [ERROR] WSREP: Failed to apply trx 286800 4 times
160408 15:09:57 [ERROR] WSREP: Node consistency compromized, aborting...

The node died and it was resurrected with SST. During this process I got this:

160419  9:56:46 [Note] InnoDB: The log sequence numbers 15287730891 and 15287730891 in ibdata files do not match the log sequence number 25623829121 in the ib_logfiles!
160419  9:56:46 [Note] InnoDB: Database was not shutdown normally!
160419  9:56:46 [Note] InnoDB: Starting crash recovery.
160419  9:56:46 [Note] InnoDB: Reading tablespace information from the .ibd files...
160419  9:56:48 [Note] InnoDB: Restoring possible half-written data pages 
160419  9:56:48 [Note] InnoDB: from the doublewrite buffer...
160419  9:56:49 [Warning] InnoDB: Resizing redo log from 2*48000 to 2*6400 pages, LSN=25623829121
160419  9:56:49 [Warning] InnoDB: Starting to delete and rewrite log files.
160419  9:56:49 [Note] InnoDB: Setting log file ./ib_logfile101 size to 100 MB
InnoDB: Progress in MB: 100
160419  9:56:50 [Note] InnoDB: Setting log file ./ib_logfile1 size to 100 MB
InnoDB: Progress in MB: 100
160419  9:56:51 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
160419  9:56:51 [Warning] InnoDB: New log files created, LSN=25623829516
160419  9:56:51 [Note] InnoDB: 128 rollback segment(s) are active.
160419  9:56:51 [Note] InnoDB: Waiting for purge to start

innodb_log_file_size is stuck at 100M:

MariaDB [(none)]> show global variables like 'innodb_log_file_size';
+----------------------+-----------+
| Variable_name        | Value     |
+----------------------+-----------+
| innodb_log_file_size | 104857600 |
+----------------------+-----------+
1 row in set (0.00 sec)

Yet /etc/my.cnf.d/server.cnf defines the value as 750M:

[mariadb-10.0]
...
innodb_log_file_size=750M


What am I missing?

Edited to add: I've found that I'm able to force the innodb_log_file_size by giving it as a parameter on the command line:

sudo /sbin/service mysql start --innodb-log-file-size=786432000
  • Is that setting in the [mysqld] section? – Rick James May 10 '16 at 17:28
  • You have encountered the first of multiple potential problems with huge text/blob columns. Suggest you consider keeping them smaller or put them elsewhere or something else. – Rick James May 10 '16 at 17:29
  • The setting is in the [mariadb-10.0] section. (Just edited the question to specify this.) I wouldn't mind circumventing the problem somehow, but I have little control over the software / source code using this. – dbdemon May 10 '16 at 21:21
1

In my experience setting up MariaDB 10.1 cluster, this behavior happens due to an override cnf file.

I had the same problem, that changing a setting in my.cnf and restarting mysql but the change was ignored. I found on ubuntu there is /etc/mysql/conf.d/mariadb-enterprise.cnf which overrides many settings. I comment out in the override file the value I want to change and then the value I put in my.cnf is used.

1

Working solution for Centos 7:

vim /etc/my.cnf.d/server.cnf 

[mysqld]

innodb_log_file_size=100M #add or change desired parameter

systemctl stop mariadb

mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.old

mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.old

systemctl start mariadb

Thats all falks!

  • 1
    You need to set global innodb_fast_shutdown=0 (ref: innodb_fast_shutdown) before the shutdown to ensure the the ib_logfile* don't contain required recovery information. – danblack Oct 23 '18 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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