Some mysqld parameters are far from having reasonable defaults. Most notable are the engine-specific values, and in particular the InnoDB parameters.
Some of these variables have different defaults as of MySQL 5.4. innodb_buffer_pool_size, for example, is 128M on 5.4. innodb_log_file_size, however, has changed back and forth, as far as I understand, and is down to 5M again. These settings are still the same on 5.5.
I wish to present some not-so-obvious parameters which, in my opinion, have poor defaults, for reasons I will explain.
- group_concat_max_len: This parameter limits the maximum text length of a GROUP_CONCAT concatenation result. It defaults to 1024. I think this is a very low value. I have been using GROUP_CONCAT more and more, recently, to solve otherwise difficult problems. And in most cases, 1024 was just too low, resulting in silent (Argh!) truncating of the result, thus returning incorrect results. It is interesting to learn that the maximum value for this parameter is limited by max_packet_size. I would suggest, then, that this parameter should be altogether removed, and have the max_packet_size limitation as the only limitation. Otherwise, I’d like it to have a very large default value, in the order of a few MB.
- wait_timeout: Here’s a parameter whose default value is over permissive. wait_timeout enjoys an 8 hour default. I usually go for 5-10 minutes. I don’t see a point in letting idle connections waste resources for 8 hours. Applications which hold up such connections should be aware that they’re doing something wrong, in the form of a forced disconnection. Connection pools work beautifully with low settings, and can themselves do keepalives, if they choose to.
- sql_mode: I’ve discussed this in length before. My opinion unchanged.
- open_files_limit: What with the fact connections, threads, table descriptors, table file descriptors (depending on how you use InnoDB), temporary file tables — all are files on unix-like systems, and considering this is an inexpensive payment, I think open_files_limit should default to a few thousands. Why risk the crash of “too many open files”?
No setting will ever be perfect for everyone, I know. But there are those parameters which you automatically set values for when you do a new install. These should be at focus and their defaults change.
The worse thing about group_concat_max_len is that most of the programmers are not aware of it till the customers tell them about truncated results! This is one of the worst ‘silent’ killer.
Also:
innodb_file_per_table should default to being set on; it’s appropriate for probably 99% of the cases out there.
max_connections limit of 151 is ridiculous. The only reason to have max_connections is so you don’t crash due to trying to use too much memory. So instead of crashing you block access, which results in the same problem — customers don’t see data.
But really, these settings are easily found in a basic audit, and I’d almost rather have these so you can tell that a server hasn’t really been looked at, versus ‘it’s been looked at and the defaults are good’.
Hi Sheeri,
innodb_file_per_table: I agree, but some disagree.
“But really, these settings are easily found in a basic audit” — that’s my point exactly. If any basic audit should reveal that the defaults haven’t been changed and that they should be — then the problem is with the defaults.
The problem isn’t with the defaults, the problem is that people who don’t know what they’re doing are using the software…..
…which is OK, because one of the points of MySQL is to be easy to use, and that it’s accessible even if you don’t know what you’re doing. The problem is that in the interest of saving money and because things work “well enough” people don’t have the expertise they need.
It would be like going to a ‘learning’ doctor or dentist all the time — sure, you can save money, but if you have anything other than standard issues (ie, not just a regular healthy visit, shots, or an ‘easy’ issue like pink eye or the flu) then you want someone with experience.