Those oversized, undersized variables defaults

June 9, 2010

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.

tags: ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

8 Comments to "Those oversized, undersized variables defaults"

  1. Verifying GROUP_CONCAT limit without using variables | wrote:

    [...] Those oversized, undersized variables defaults [...]

  2. Shantanu Oak wrote:

    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.

  3. Sheeri Cabral wrote:


    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'.

  4. shlomi wrote:

    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.

  5. Sheeri Cabral wrote:

    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.

  6. shlomi wrote:

    I agree only to some extent.
    Because, in my view, the defaults are really incompatible with the progress.
    Why not, then, make group_concat_,max_len be 7? Or innodb_buffer_pool_size be 2K?
    Reasonable defaults are important. I mean, poor defaults don;t make for good working-out-of-the-box experience.

  7. SQL: selecting top N records per group | wrote:

    [...] setting for group_concat_max_len (see this post). Actually it would be better to have a smaller value here, while you make sure it’s large [...]

  8. SQL: selecting top N records per group, another solution | wrote:

    [...] you still have to have a sufficient group_concat_max_len (see this post). You must have a large enough value to fit in the very long text you may be expecting in [...]

Leave Your Comment


Powered by Wordpress and MySQL. Theme by