Now that InnoDB is the default storage engine in MySQL, is it time to update the default configuration for the InnoDB log file size (innodb_log_file_size) setting?
In general, there are two settings that simply can’t be left at their historical defaults for a production installation. MySQL 5.5 increased the default buffer pool size to something more sane (128MB instead of 8MB), but the log file size remains at 5MB. That’s 10MB total, because there are two logs by default.
Is it time to update this? I think so. You simply can’t run a “real” server with 10MB of transaction logs. What’s a sane value? I’d say that something between 64MB and 256MB would be okay for a lot of entry-level workloads, but that would consume some disk space that might surprise people. Perhaps this is the reason that the default hasn’t been increased from 5MB. Regardless, I think that 64MB times two is okay as a default.
What do you think? Write your suggestion for a default log file size into the comments.
By the way, if you’re looking to choose a good log file size for your workload, an arbitrary number such as 64MB or 256MB isn’t great. You can get a good start by using the heuristic shown in this old blog post.
I think leaving it at 5MB each is what they should do, to make sure that you go ahead and change the default value.
If someone gets away with using a default of 64MB – Then any problems are temporarily hidden.
If you immediately run into problems, then you can fix them before the problems get any worse.
Hi Baron,
I completely agree with you and perhaps this value may change on the next version. As you mentioned, it’s almost impossible to run a MySQL Server instance in production with default values. Historically, MySQL has the behavior to maintain a new fresh installation – that one you’ve never have reconfigured – with low hardware consume.
I was surprised to find that I, as a fan and thorough reader of your High Performance MySQL book, was unaware of innodb_log_file_size until quite recently. I only read up on it when I spotted said log file being created every few seconds on one of my server. Setting it to a more sensible value reduced the rotation to daily but to my surprise, didn’t have a noticeable effect on speed.
Bob,
In the 3rd edition of High Performance MySQL, the configuration chapter basically starts and ends with “you need to set innodb_buffer_pool_size and innodb_log_file_size and not much else” 🙂
Baron, I think 256M would be good “default default” – reasonably reasonably good performance and reasonably fast recovery after changes in Innodb Plugin and MySQL 5.5
One thing which needs to be done with this change though is automatic resizing of log files. I think as Innodb starts it could do recovery using current log files and when if log files do not match the size specified it can re-create them so we do not get non starting MySQL.
Good touch Peter – if the InnoDB Transaction logs could simply be resized without to cause a crash on MySQL restart. it will be very good too. I think that a simple content’s copy from the old one to the new trx log may resolve it and MySQL can start without problems.
Yep, unless it had magic foo to resize, it’s just going to refuse to start as if the log file sizes don’t match, InnoDB won’t start 🙁
Baron, innodb_log_file_size is set to 64MB in the my-huge.cnf example file.
So, it seems that your question has perhaps an answer here.
Unfortunately, the example files such as my-huge.cnf were “huge” for 1998 and not for any period since then.
Amen. A “huge” server will have 4GB or more of log files.
I agree with Peter: online (perhaps automatic?) resizing is good idea.
I think the big problem with both these variables is that it’s so machine and use case dependent. I’ve seen small usage cases (such as running a forum with a hundred thousand posts) run very well with a small innodb_buffer_pool_size (say 128M). But on a dedicated database server, 128M is a joke.
Better would be a series of questions in the installation: is it a dedicated InnoDB box? Is it part of a LAMP (or the like) stack? What is the expected database size? What’s the expected number of writes per second? The responses to these questions could ballpark the values of these variables based on measured hardware resources.
Baron,
64M makes sense since it’s not much disk consumer, while allowing for reasonable flush rate in many installations. Moreover, it usually makes for fast recovery time, which I think is an important GOTCHA for a lot of people.
And, may I suggest this slightly less old method for calculating log file size?
I work with heavy-write MySQL servers and 2x2GB is not enough to avoid some “furious flushing” sometimes. I’d like to see a really adaptive checkpointing system in InnoDB… I’ve read everything I could on that (thanks Percona & Dimitri by the way) but still looking for the right setting.
Shlomi, I was concern by recovery time in 5.1.x too but today with 5.5.x it’s not a problem any more (thanks to InnoDB plugin actually), even with 2x2GB. It’s not only redo size logs dependant of course but on my dbs it’s ok.
I know my-huge.cnf file is obsolete, I just want to specify there isn’t an only one default value for this parameter.
Actually, the real question is : Who is using default value for production ?
@Cédric: to answer your last question: *plenty* 🙂
There are many start up companies with no real DBA, but rather great developers who have to work with many tools and don’t know the specifics of each.
I think that the defaults and the example my.cnf files should be updated. An innodb example my.cnf should also have innodb_file_per_table and innodb_buffer_pool_instances and it should say something about the Barracuda format.
And for the innodb_log_file_size there should be a comment about how to change it.
Peter, very good point!
I think also it’ll be great to have dynamical resize of REDO files via ALTER, etc..
Rgds,
-Dimitri
I agree with Peter Zaitsev: 256M is a good value. Nowadays disk space is cheap. When I worked at a greater internet company and MySQL 5.1 with InnoDB Plugin was released I set it to 256M. There were no problems. The included my.cnf from MySQL are not up to date, neither the values nor the structure.
@Shlomi : Plenty, yes, sure. That’s why Percona (and us) is here, no ? 🙂
I’m sure great developers can find this information very quickly.
If I have a look at Oracle documentation for Oracle databases, I can find these informations :
– The minimum size permitted for a redo log file is 4 MB
– The default size of online redo log files is operating system-dependent
– The default size for an Oracle managed log file is 100 MB
In fact, you can add the redo logs definition and size in the database creation order with Oracle.
And it’s possible to manage all the files with a logical interface.
So, Baron, perhaps you have to ask Oracle why they didn’t change this default value.
And bring to us some great example for configuration file 🙂
To clarify, I’m not intending to discuss the sample my.cnf files that ship with MySQL. I’m discussing the compiled-in defaults: what happens when you run MySQL with no my.cnf at all, or you have a my.cnf but it has no InnoDB settings at all?
I have seen way too many servers (dozens?) with 8MB buffer pools and 5MB log files due to this problem.
Baron, you’re right. I have been seeing lots of MySQL Server installations running with no option/configuration file, mainly when MySQL is installed via “.rpm” packages and it is running on Linux flavors such as CentOS, Red Hat or Fedora. Users just install MySQL and start using it.
Hi all.
If you consider that:
1.those built-in values are at least 7 years old (Baron correct me if I am wrong)
2.Disk size increased so much that 1.000.000 MB are not that uncommon
3.Memory size on a server. even if virtual, I doubt being less then 1GB
I do not see a single reason why the innodb_log_file_size should stay at 5MB (5/1.000.000 = 0.000005% of the average disk on a server)
Also the buffer_pool_size, 128MB = 12.5% of the average minimum(!) server memory size, it is really the minimum it should be considering that: (1) InnoDB is now default (2) The recommended 70%-80% of RAM is higher than that.
May be MySQL should include a post-installation script to review the vital parameters.
Baron,
You pose a great question, something that I think could be addressed with updates to all of the sample config files.
But for now – I agree with Peter, 256MB is a reasonable minimum to expect.
In the past I’ve referenced the old blog post for finding a good estimate for the log size, but what do you think about an update to that post speaking to large installations specifically? The post makes mention of larger log sizes in the gigabytes as generally a mistake. In the comments above, you mention that a huge server would have 4GB or more. Is the reference to 4GB a reflection of the rule of thumb?
In the past we tried to make the log as big as we could without going so large that crash recovery was too slow. This resulted in the log being smaller than we’d wish sometimes. But InnoDB crash recovery speed has improved greatly since then, and now it’s more practical to run with large log file sizes. 4GB is something I’ve seen here and there. By default in current versions of MySQL, it’s the largest size you can have. But Percona Server and MySQL 5.6 permit even larger logs, so for truly huge servers, 4GB isn’t the maximum that might need to be considered.
+1 for me to see it increased.
I agree that we see a lot of sites that are constrained because they use the defaults. They often don’t even use the provided my.cnf variants. Even Amazon RDS has some undersized defaults (the log file size is 128MB and it’s not configurable; also innodb_io_capacity=200 is too low).
But for the sake of Devil’s Advocacy, I’d comment that this thread is making some assumptions that may not be appropriate. For example:
(a) MySQL compiled-in defaults are for production servers that would have been in the realm of science fiction in 1998. But the majority of MySQL instances are probably installed on more modest hardware, for example as development instances, or just to evaluate. An out-of-the-box config that overwhelms the user’s hardware could hurt the XAMPPiness of MySQL.
(b) All MySQL instances are on dedicated database servers. I do encounter db servers that share resources with PHP or Tomcat or Memcached or other daemons. Even multiple MySQL instances. Granted, this is not such a big deal for log file size, which uses disk instead of RAM. But I wanted to make the point.
(c) Everyone uses the InnoDB plugin, and enjoys its 10x recovery time relative to non-plugin. In fact, I encounter many 5.1 instances that aren’t using the plugin. I also encounter instances of 5.0 which predate the plugin architecture. So a very large log file has some downside. Of course we agree they *should* use the plugin, but until they do (5.5), giving them a 256MB+ log file by default could lead to an unpleasant surprise in the event of crash recovery.
Bill, I agree on a) and b). On c), I would not propose changing anything for old versions — only for new versions going forward.
If we were going to do something for Percona Server, it would be in 5.5. Having it start new installations with larger log file sizes would be incredibly easy (we already have a patch for InnoDB in XtraBackup to ignore log file size differences).
@Smith I think that patch you mentioned is simply perfect…
@Baron Schwartz, sorry to be (a bit) off the topic !
@Everybody, give your opinion here : http://www.doodle.com/h92vpx2g6qdh6mwx
Baron,
There is an undocumented fact about the log_file size. Pasting the snippet (from log_calc_max_ages in storage/innobase/log/log0log.c )
===============
if (!success) {
fprintf(stderr,
“InnoDB: Error: ib_logfiles are too small”
” for innodb_thread_concurrency %lu.\n”
“InnoDB: The combined size of ib_logfiles”
” should be bigger than\n”
“InnoDB: 200 kB * innodb_thread_concurrency.\n”
“InnoDB: To get mysqld to start up, set”
” innodb_thread_concurrency in my.cnf\n”
“InnoDB: to a lower value, for example, to 8.”
” After an ERROR-FREE shutdown\n”
“InnoDB: of mysqld you can adjust the size of”
” ib_logfiles, as explained in\n”
“InnoDB: ” REFMAN “adding-and-removing.html\n”
“InnoDB: Cannot continue operation.”
” Calling exit(1).\n”,
(ulong)srv_thread_concurrency);
exit(1);
}
====================
so log file size combined must be > 200kb * innodb thread concurrency..
The rationale behind this is mentioned just above in comments —
==================================================
/* For each OS thread we must reserve so much free space in the
smallest log group that it can accommodate the log entries produced
by single query steps: running out of free log space is a serious
system error which requires rebooting the database. */
free = LOG_CHECKPOINT_FREE_PER_THREAD * (10 + srv_thread_concurrency)
+ LOG_CHECKPOINT_EXTRA_FREE;
======================================
with the constants being
#define LOG_CHECKPOINT_FREE_PER_THREAD (4 * UNIV_PAGE_SIZE)
#define LOG_CHECKPOINT_EXTRA_FREE (8 * UNIV_PAGE_SIZE)
The other restriction is documented though — Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group.
PS: While posting this comment, I realized that even though the thread concurrency is a dynamic variable, that function is not called again. So, I wonder what will happen if I bump thread concurrency from say 10 to 100 and all 100 start logging..
Since we are talking about variables, how about size in innodb_data_file_path .. isnt 10 MB low by today’s standards ?
But what can be more alarming are the silent assumptions made about these variables — say between log file size and log buffer size — the server may not crash or print a warning but we sure may see some anomalous behavior, and the relation between them may not always be linear, deducing which may require a source code cscope-ing 😉
Coming back to 256 M or higher sizes for MySQL log file size, I think the most appropriate one can be compile time build profile. Something like -DBUILD=large / -DBUILD=small to cmake. The reason why I am proposing this is because MySQL is also used on environments with smaller footprint like desktops — KDE for instance makes extensive use of MySQL, something like syslog-ng can log into a database and so on. This way distro builders/developers/DBAs can fit MySQL to their environments appropriately.
I agree it should be changed and would gladly welcome the ability to shrink/grow the log_file_size. I’d go with 128mb as a total-default value as a guess, erring on the side of being too small (and just adding more).
Tim
Raghavendra,
I did not know the bit you pasted above about the minimum log file size, and I bet the behavior is undefined. Patrick Crews might like to know about this 🙂
I don’t think that a 10MB default ibdata1 file is a problem, because it autoextends by default.
The autoextend behaviour is rather interesting, especially if you’re loading a lot of data into InnoDB and start from such a small file. I do believe that if you know data is going to be XMB and you set initial data file size to be much closer to X than 10MB is, you’ll end up with much better performance in loading data (and probably much better file layout on the file sytem for things that aren’t XFS).
I believe that the main problem is just to extend this file if you data is greater than 10MB. Someone here has some study that state the costs to extend InnoDB shared tablespace in this case?
Poll is closed : http://doodle.com/h92vpx2g6qdh6mwx