MySQL 5.6 has a great many new features, including, but certainly not limited to a number of performance improvements. However, besides the widely talked-about features such as InnoDB support for full text search, optimizer, performance schema improvements and GTID, there are also a few tiny improvements that nobody cared to mention.
One such feature is… …well, let me show you.
In the past almost-7-years that I’ve been with Percona, roughly 97.35% of my reports would include the following or similar instructions in them:
… in order to change the size of InnoDB transaction log files, you have to follow this procedure. Beware that if you don’t follow these instructions step-by-step, your server may not restart or rather it will start with InnoDB storage engine disabled and an error in the logs. So, here’s what you want to do:
1. change (or add) the following variable in my.cnf: innodb_log_file_size = XXXM
2. stop MySQL server
3. make sure you see a successful shutdown of InnoDB in the MySQL error log
4. move away old log files (at this point I have to double check where they are) by running the following command:
mv /var/lib/mysql/ib_logfile* /tmp/
5. start MySQL server – it should take a bit longer to start because it is going to be creating new transaction log files
6. When you’re positive that MySQL server has started successfully, you can remove the old log files:
rm /tmp/ib_logfile*
Occasionally, if I’m not careful enough picking the warning words, that would backfire with extra questions such as how safe this is, do they really need to do it etc. and yet in most cases increasing the size of transaction log is the best thing you can do for your InnoDB-backed MySQL server.
The Secret Feature
From now on, systems running MySQL 5.6 will make my life (and yours too) so much easier as the instructions can now be reduced to:
Change the innodb_log_file_size (which I will simply include together with other recommended changes) and restart MySQL server.
Automatic InnoDB transaction log file size change is how I would call it. You want to know how MySQL responds to this? Here’s how:
1 2 3 4 5 6 7 8 | 2013-08-20 13:57:37 5048 [Warning] InnoDB: Resizing redo log from 2*3072 to 2*32768 pages, LSN=1626007 2013-08-20 13:57:37 5048 [Warning] InnoDB: Starting to delete and rewrite log files. 2013-08-20 13:57:37 5048 [Note] InnoDB: Setting log file ./ib_logfile101 size to 512 MB InnoDB: Progress in MB: 100 200 300 400 500 2013-08-20 13:57:39 5048 [Note] InnoDB: Setting log file ./ib_logfile1 size to 512 MB InnoDB: Progress in MB: 100 200 300 400 500 2013-08-20 13:57:41 5048 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2013-08-20 13:57:41 5048 [Warning] InnoDB: New log files created, LSN=1626007 |
And it works both ways! Gotta love this simplicity. Tell me, what is your favorite MySQL 5.6 feature? Don’t worry, it doesn’t have to be secret. Unless you dare, of course.
I’m a Fan!
I accidentally ran into this by accident this morning. I already did a clean shutdown and forgot to move the ib_logfile*s … but it still worked. 🙂
And how are you did it?
Sorry, it was a typo. The real question is why still need to restart to change log configuration. This is prehistoric.
You are welcome for the feature. In fact, this was one of the peculiarities of InnoDB that catched my eye shortly after I started working on it approximately 10 years ago. And I was not the only one. See for example http://bugs.mysql.com/bug.php?id=13494 from 2005.
We made the log file size resizable in MySQL 5.6, because the old default redo log size was getting simply too small, causing unnecessarily frequent flushing of the buffer pool. There is a reason why the redo log file used to be small. The crash recovery speed has been improved a lot in 5.5 and 5.6. With older versions, applying the redo log would take much longer.
The reason why we require a server restart for resizing the redo log is a bit tricky. In fact, we basically do the equivalent of a ‘clean shutdown’ at server startup, and delete the old redo log files, and finally create new files. This has to work even if the system is repeatedly killed and restarted during the operation.
The log could theoretically be resized without restarting the server, but I believe that it would require a full log checkpoint (full flush of all dirty pages in the buffer pool) and quiescing all writing activity to quiesce (blocking any write operations). For a write-heavy system, this could mean a similar amount of downtime as a server restart.
Indeed this is a wonderful new feature!
One way it could go awry, however: if a site normally relies on a large log file size, but then the innodb_log_file_size line in the my.cnf is deleted or commented out. Then restart mysqld. The log file will resize to the default size, which may be inappropriately too small for the site.
Lines do sometimes mysteriously disappear from my.cnf, due to unaccounted user errors.
@Bill: you’re right, but before 5.6, MySQL would even refuse to start in such a scenario, which is worse in my opinion.
Yes, I also appreciate this feature a lot.
I was looking forward to trying out the read only mode in mysql 5.6 to read a database that was on a read only snapshot, didn’t get very far though, mysql kept crashing.. so had to resort to pulling the full data set to a writable volume which took some time.
[..]
2013-08-21 00:02:18 25286 [Note] InnoDB: Started in read only mode
[..]
InnoDB: Failing assertion: DICT_TF2_FLAG_IS_SET(index->table, DICT_TF2_TEMPORARY)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
00:02:22 UTC – mysqld got signal 6 ;
It may of been caused by me trying to load a mysql 5.5 db with a 5.6 binary(was trying 5.6 specifically for the read only option), I didn’t have time to investigate further.
@Stephane I disagree somewhat, refusing to restart would mean that you could catch that issue up front rather then having another layer of troubleshooting poor performance after the restart had implicitly resized the logs. Of course after restart (planned or unplanned) you should review the state of the error log for signs of issue.
@Bill & @Andrew – when you think of it, this can happen to pretty much any variable. innodb_buffer_pool_size disappears – no good, innodb_file_per_table – no good, innodb_flush_log_at_trx_commit – no good, innodb_thread_concurrency – no good, well you get the point. We’re just so used to innodb_log_file_size requiring extra steps now that we can’t accept the bright future :))
@Andrew: I should have clarified a bit more.
Before 5.5, MySQL would start with InnoDB disabled, which can lead to anything from weird application behavior to data corruption. And this may not be easy to diagnose. I’ve seen cases where restoring from a backup was the only solution to restart the server.
I thought 5.5 behaved the same way but by experimenting, I’ve found that it is much safer: the whole server refuses to start, which as you said makes troubleshooting quite easy.
Nate, if you upgrade to MySQL 5.6 without doing a slow shutdown in an earlier version first, you can get that kind of an an error during startup. I guess that the error has something to do with the crash recovery of “fast index creation” (introduced in the InnoDB Plugin) and “online ALTER TABLE” (introduced in MySQL 5.6).
help! I can’t change the innodb_log_file_size! my db import fails because of it…..
I have one instance of mysql 5.6 installed on a centos 5.6 virtual machine and im trying to import an old db for tests. but It fails when importing.. reading up, I found that it is probably failing because pf the innodb log file size.. but I can’t change it.
I’ve tried the steps above, the log says it shutdown without a problem (no errors)
2014-10-09 13:04:47 17451 [Note] /usr/sbin/mysqld: Normal shutdown
2014-10-09 13:04:47 17451 [Note] Giving 0 client threads a chance to die gracefully
2014-10-09 13:04:47 17451 [Note] Event Scheduler: Purging the queue. 0 events
2014-10-09 13:04:47 17451 [Note] Shutting down slave threads
2014-10-09 13:04:47 17451 [Note] Forcefully disconnecting 0 remaining clients
2014-10-09 13:04:47 17451 [Note] Binlog end
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘partition’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘ARCHIVE’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘BLACKHOLE’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘PERFORMANCE_SCHEMA’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_SYS_DATAFILES’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_SYS_TABLESPACES’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_SYS_FOREIGN_COLS’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_SYS_FOREIGN’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_SYS_FIELDS’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_SYS_COLUMNS’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_SYS_INDEXES’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_SYS_TABLESTATS’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_SYS_TABLES’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_FT_INDEX_TABLE’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_FT_INDEX_CACHE’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_FT_CONFIG’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_FT_BEING_DELETED’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_FT_DELETED’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_FT_DEFAULT_STOPWORD’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_METRICS’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_BUFFER_POOL_STATS’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE_LRU’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX_RESET’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_CMPMEM_RESET’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_CMPMEM’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_CMP_RESET’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_CMP’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_LOCK_WAITS’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_LOCKS’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘INNODB_TRX’
2014-10-09 13:04:47 17451 [Note] Shutting down plugin ‘InnoDB’
2014-10-09 13:04:47 17451 [Note] InnoDB: FTS optimize thread exiting.
2014-10-09 13:04:47 17451 [Note] InnoDB: Starting shutdown…
2014-10-09 13:04:49 17451 [Note] InnoDB: Shutdown completed; log sequence number 36443469334
2014-10-09 13:04:49 17451 [Note] Shutting down plugin ‘CSV’
2014-10-09 13:04:49 17451 [Note] Shutting down plugin ‘MEMORY’
2014-10-09 13:04:49 17451 [Note] Shutting down plugin ‘MyISAM’
2014-10-09 13:04:49 17451 [Note] Shutting down plugin ‘MRG_MYISAM’
2014-10-09 13:04:49 17451 [Note] Shutting down plugin ‘sha256_password’
2014-10-09 13:04:49 17451 [Note] Shutting down plugin ‘mysql_old_password’
2014-10-09 13:04:49 17451 [Note] Shutting down plugin ‘mysql_native_password’
2014-10-09 13:04:49 17451 [Note] Shutting down plugin ‘binlog’
2014-10-09 13:04:49 17451 [Note] /usr/sbin/mysqld: Shutdown complete
141009 13:04:50 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
(I tried removing then tried deleting the existing logs, ib_logfile0, ib_logfile1),
in /etc/my.conf I added the folllowing line:
innodb_log_file_size=64M
then restarted:
service mysqld start
Starting mysqld: [ OK ]
but the logs are exactly the same as before…
141009 13:05:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2014-10-09 13:05:09 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2014-10-09 13:05:09 18205 [Note] Plugin ‘FEDERATED’ is disabled.
2014-10-09 13:05:09 18205 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-10-09 13:05:09 18205 [Note] InnoDB: The InnoDB memory heap is disabled
2014-10-09 13:05:09 18205 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-10-09 13:05:09 18205 [Note] InnoDB: Memory barrier is not used
2014-10-09 13:05:09 18205 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-10-09 13:05:09 18205 [Note] InnoDB: Using Linux native AIO
2014-10-09 13:05:09 18205 [Note] InnoDB: Not using CPU crc32 instructions
2014-10-09 13:05:09 18205 [Note] InnoDB: Initializing buffer pool, size = 1.2G
2014-10-09 13:05:09 18205 [Note] InnoDB: Completed initialization of buffer pool
2014-10-09 13:05:10 18205 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2014-10-09 13:05:10 18205 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2014-10-09 13:05:10 18205 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2014-10-09 13:05:10 18205 [Warning] InnoDB: New log files created, LSN=36443469334
2014-10-09 13:05:10 18205 [Note] InnoDB: Highest supported file format is Barracuda.
2014-10-09 13:05:12 18205 [Note] InnoDB: 128 rollback segment(s) are active.
2014-10-09 13:05:12 18205 [Note] InnoDB: Waiting for purge to start
2014-10-09 13:05:12 18205 [Note] InnoDB: 5.6.21 started; log sequence number 36443469836
2014-10-09 13:05:12 18205 [Note] Server hostname (bind-address): ‘*’; port: 3306
2014-10-09 13:05:12 18205 [Note] IPv6 is not available.
2014-10-09 13:05:12 18205 [Note] – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
2014-10-09 13:05:12 18205 [Note] Server socket created on IP: ‘0.0.0.0’.
2014-10-09 13:05:12 18205 [Note] Event Scheduler: Loaded 0 events
2014-10-09 13:05:12 18205 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.6.21’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MySQL Community Server (GPL)
2014-10-09 13:05:19 18205 [Warning] IP address ‘10.109.59.177’ could not be resolved: Name or service not known
any help would be greatly appreciated as I’ve been stuck for days!
my.cnf
innodb_buffer_pool_size = 1280M
max_allowed_packet = 120M
[innodb]
innodb_log_file_size=64M
#innodb_log_files_in_group=4
innodb_file_per_table = 1
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_file_format_check = ON
sorry for the long post… Thanks!
Hi Michael,
Thanks for the post but this is not really the place to ask such a detailed question – plus this post is more then a year old. I suggest asking on the Percona peer-to-peer discussion forums for some help. https://www.percona.com/forums/questions-discussions/mysql-and-percona-server
Thanks Tom, but I solved it. apparently innodb_log_file_size=64M has to be placed under [mysql] not [innodb]
The log file resized after that.
Just in case some one else comes across this page..
This is also one of the good features of MySQL 5.6 that I really love. it save me few steps from how I change InnoDB log filesize. Hopefully, I can upgrade our remaining servers to this version ASAP.